How to Load Oracle to Netezza

Reply
New Contributor
Posts: 3

How to Load Oracle to Netezza

Hi Everyone

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 ============================== */

proc sql;

   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 */

   );

quit;

/* ======================= 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]

Thanks

Cor Basson

Super User
Posts: 5,424

Re: How to Load Oracle to Netezza

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.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003181092.htm

Data never sleeps
PROC Star
Posts: 1,167

Re: How to Load Oracle to Netezza

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.

Tom

Super User
Posts: 5,424

Re: How to Load Oracle to Netezza

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.

Data never sleeps
PROC Star
Posts: 1,167

Re: How to Load Oracle to Netezza

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!

Tom

Regular Contributor
Posts: 216

Re: How to Load Oracle to Netezza

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,

Ahmed

New Contributor
Posts: 3

Re: How to Load Oracle to Netezza

Hi Guys

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

Cheers

Cor

Super User
Posts: 5,424

Re: How to Load Oracle to Netezza

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.

Data never sleeps
Ask a Question
Discussion stats
  • 7 replies
  • 1877 views
  • 0 likes
  • 4 in conversation