Error while processing data externally

[Dreamer]

Hi,

When I'm trying to run following code:

OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog DBIDIRECTEXEC;

LIBNAME Ntzlib NETEZZA  DATABASE=ntdb  SERVER=ntserv  AUTHDOMAIN="netezza" readbuff=1000 bulkunload=yes;

PROC SQL;

SELECT count(*) from Ntzlib.NETEZZA_TBL; quit;

I'm getting following error:

ERROR: CLI open cursor error: ERROR:  CREATE EXTERNAL TABLE: permission denied.

PS: Actually I'm getting a very slow sql performance while working with Netezza or any other external DBMS. I'm trying to optimize this by SQL processing at Data base side.

[jakarman]

You have a lot of parameters already in place there.

The message you are getting is an indication for an authorization issue. Please check that all with simple small datasets.

Reading data, using temporary data, updating/maintaining your personal or project data at the Netezza environment.  

As you having a slow SQL performance as original issue I would like to go back for doing the investigation: why?

- With the Access/Netezza interface SAS should optimize the SQL to run it all possible at NETEZAA, That is implicit pass through.

- Interface options There are parameters for:

  a/  best in-database processing pass/through

  b/  bulk retrieval copying all needed data to SAS. This will cause a lot of transport overhead.

  c/  bulk-loading adding new data to Netezza

There is no holy grail combination for all. What works  for one can be very bad for another. You are most likely needing several templates for each type of workload. The bad news on this: this is commony not well understood. Not by users, Netezaa support and your sas support.

You have to try to organize that all.

The netezza access reference: SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

in database technology (more advanced on top of access): In-Database Technology

user guidelines: http://www.sas.com/partners/directory/ibm/NetezzaDWAppliances-withSAS.pdf

notes:

39282 - Cross-database joins are not supported in SAS/ACCESS® Interface to Netezza

50926 - SAS/ACCESS® Interface to Netezza LIBNAME does not support Netezza Server 7.0.3 and later

46129 - Using SAS/ACCESS® Interface to Netezza to write a SAS® data set to a new table in the Netezz...

For your count(*) request there you should cooperated with you netezza DBA. There can be netezza admin tables having that information ready (metadata). It should perform split second without any need to read some data from you table. 

[Dreamer · Accepted solution]

Thanks Jaap, I'll go though your links & suggestions.

But issue was with "bulkunload=yes".

I removed this option and ow its working fine with an acceptable performance.

But I wonder why this was an issue. Because I thought it is the fastest way to retrieve large numbers of rows from a Netezza table.

I'll read more about this in documentation. But for the time being removing this option from my libname statement worked for me.

[jakarman]

Yep, in this case you did not want the bulkunload as the resulting information (count) is small.
It could have caused laoding all data to SAS and counting there. Your intention was counting by Netezza. When this is the effect it just have proved the need for different set of settings.