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.
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.
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
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.