07-11-2014 10:57 AM
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:
create TABLE ANY_TABLE
(MEM_ID (6) Not Null)
I need to integrate X2222221.X2222222 in my DBLOAD statement:
proc dbload dbms=ODBC data=OUTSAS.TEST;
DSN = 'db2aaaa'
TABLE = test;
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
07-11-2014 11:54 AM
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.
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)