10-02-2012 05:51 AM
Our client runs on SAS 9.2 with DI Studio 4.2.
I am busy writing a transform in DI Studio to do optimized data loads between SAS, ORACLE and NETEZZA.
My loads between any combination of SAS and ORACLE tables work just fine using proc sqls with Oracle Hints [if applicable] for speed
I however cant seem to get my head around loading a Oracle table to Netezza using nzload [or any other form of optimization]
I have manged to load Oracle to Netezza using a proc sql with code in the lines of:
/* ======================= START CODE ============================== */
connect to ORACLE as orCon(&_input_connect); /* Connection to Oracle Table */
Insert into &_OUTPUT (CHAR_TYPE_CD,CHAR_VAL,LANGUAGE_CD,DESCR,VERSION) /* OUTPUT IS NETEZZA */
select * from connection to orCon
select CHAR_TYPE_CD,CHAR_VAL,LANGUAGE_CD,DESCR,VERSION from ODS_CCB.o2o_Delta_Tbl /* Select from Oracle Table */
/* ======================= END CODE ============================== */
But as you can see, no nzload or bulkloads or anything to speed it up.
I know that the new realese of DI Studio handles these optimized loads, but our clients is not keen to update.
So, can anyone help me with the syntax on getting large Oracle tables loaded into Netezza using some form of optimization [nzload]
10-02-2012 06:35 AM
Not from own experience, but from documentation (and guessing).
There are no NZLOAD option in SAS/ACCESS, my guess is that specifying BULKLOAD will call nzload?
The bulkload options can be set in the table loader options tab. It will the appear on the BASE option if chose to use PROC APPEND.
10-02-2012 10:25 AM
I would definitely throw this at SAS technical support. I have a feeling there's no way to use DIS to directly copy data from one non-SAS DBMS to another, but they might surprise you.
10-02-2012 10:33 AM
Depends on what you mean by directly.
If it's without passing data via a SAS engine, then it's matter of exporting Oracle data directly to nzload, and that should not be a question for SAS tech support.
If CBass is happy the original method presented, sweetenedd with some bulkload, it's straightforward. The flow needs only a table loader with some bulkload option in it.
10-02-2012 12:12 PM
I was assuming that the OP would benefit operationally from having data passed directly from Oracle to Netezza under the control of SAS DI; hence the suggestion of a Tech. Support request. If I understand the SAS engines, if a user wants to involve the engine then the data must come into SAS.
Please correct any inaccuracies!
10-02-2012 10:54 AM
I would recommend thinking outside of the box, by asking yourself the following questions
1. Do you need SAS to do anything to the Oracle data prior to loading it into Netezza?
if Yes, then keep the process within SAS and work with the available tools.
If No, then try to explore
- Using Oracle native tools for exporting/dumping the data into some sort of flat file that can be consume by Netezza.
- Using nzload command along with options and switches, that may not be available via DIS interface from a command prompt. Check this link from the Netezza Forum (The Netezza Community: how to load the data from oracle...)
The other question you need to ask yourself, is what to do after the data has been loaded?
- Do you need to update/create any Indices (if there are any)
- Do you need to refresh table statistics
These are functions that may not be available in SAS/ACCESS to Netezza !!
Just a thought,
10-03-2012 12:45 AM
Thanks for the suggestions thus far
As suggested by LinusH I wrote a porc append with bulkload options - it seems to work, but the performance is quite poor, but I am currently trying to tweak is to see if I can make is faster using the proc append
As for the suggestion by AhmedAl-Attarr – The request from my client was to create a custom transform in DI to handle these loads for so as to be able to re-use the loading logic at any time for any tables in the future, therefor I have to do it through DI studio – Initially I am just doing straight loads [thus no manipulation of the data] and for oracle to oracle or netezza to netezza loads I push the command down to the dbms [using pass through] thus no interface really with SAS – luckily Netezza does not use indexes or table stats as is distributes data on a hardware level
Lastly, @TomKari – as mentioned above, one can use pass through in SAS which pushes the command down to oracle/netezze to get executed by said DBMS, thus data does not necessarily have to come into SAS
So ideally I am trying to find a method to push a command down to oracle to read the data and push a command down to netezza to receive the data [thus excluding SAS from the processing] using bases sas code [saved in a custom transform]
If any new ideas pop up, please let me know – I will keep you updated on the progress
10-03-2012 04:44 AM
I just want to remind on that SQL statement execute ( ) by... is able to execute a subset of RDBMS routines.
As well, call system, or the X statement let you execute OS commands, such as shell calls to Oracle and Netezza exporters/loaders. They could be placed in your transformation code.