BookmarkSubscribeRSS Feed
Dataanalyst313
Calcite | Level 5

With a text editor I can write SQL to create a table on the server; however, there is a secondary qualifier ( IN STATEMENT) which must be attached to the query.

example of text editor SQL:

---ConnectString=ODBC;DSN=DB2AAAA

  create TABLE  ANY_TABLE

  (MEM_ID (6) Not Null)

  IN X2222221.X2222222

;  

I need to integrate X2222221.X2222222 in my DBLOAD statement:

proc dbload dbms=ODBC data=OUTSAS.TEST;  
DSN =
'db2aaaa'
uid=
'anyid';
pwd=
'anypassword';

TABLE = test;

load ;

run;

Without the statement I receive the error:

ERROR: CLI execute error: [IBM][CLI Driver][DB2] SQL0551N  "anyid" does not have the

privilege to perform operation "CREATE TABLESPACE" on object "BP20".  SQLSTATE=42501

Any help would be greatly appreciated

3 REPLIES 3
jakarman
Barite | Level 11

you are messing the schema concept (sometimes database). The first fixed name to access tables is that one. 

---->-- ja karman --<-----
Dataanalyst313
Calcite | Level 5

Thanks Jaap for the quick response!

Can you provide a example with the appropiate syntax?

This is a new concept for me.

jakarman
Barite | Level 11

With the ODBC this definition has been put hidden in the odbc.ini file. With Oracle it is the schema option, with DB2 the SSID.

SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (odbc start).

The connection options can be coded along with SAS statements, this is a list: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (libname odbdc)

Samples are there when you click. See the Schema as the first part of that two level naming with tables at databases.

As you are needing two different llibname with each an other schema (dsn) the effect will be SAS is copying all data to SAS and executing it there.
To prevent that do something with DB-session binding or use explicit SQL pass through.

Your error could be indicating you have no access to create tables in that dedicated area.
Often the DBA is reserving changing table structures as his task (Data Definition Language) 

---->-- ja karman --<-----

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1256 views
  • 0 likes
  • 2 in conversation