topic Re: SAS Access to Netezza - Creating Datasets in SAS Programming

SAS Access to Netezza - Creating Datasets

gra_in_aus — Wed, 18 Jun 2014 06:23:48 GMT

Hi All,

Trying to create datasets/tables on DEV database using data from PROD database all in the Netezza Platform. I have found it takes a long time (hours) for a small data set. If I downloaded the data from PROD into to SAS and bulkloaded it back to the DEV database runs very fast.

Confused why there would be a difference? I would have thought that processing the data from DEV to PROD in Netezza would be quicker than downloading and uploading the data.

e.g.

data ndev.tmp /*Netezza DEV DB*/;

set nprod.small_table; /*Netezza PROD DB*/

run;

Many thanks

Re: SAS Access to Netezza - Creating Datasets

gra_in_aus — Wed, 18 Jun 2014 06:59:03 GMT

I keep getting the following error message in SAS 9.3 log:

ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR: Cross

Database Access not supported for this type of command.

tried differing ways proc sql, data step and proc summary to see if it made a difference... to no avail... don't know if it is a SAS or Netezza issue that is preventing data being written between databases on Netezza using SAS 9.3.

Thanks

Re: SAS Access to Netezza - Creating Datasets

jakarman — Wed, 18 Jun 2014 11:01:13 GMT

Using standard sas sql to netezza you can be trapped by a lot of caveats.

Using sas/access netezza can require a lot of knowledge of the impact what is happening.

Updating a database is different to reading. Bulkloading is the fastest because a lot of checks are bypassed.

The automatic copying prod to develop has to copy records into sas and back. Sometimes cursor operations and locking can cause a lot of delay.