Architecting, installing and maintaining your SAS environment

Create a table in ODBC which require secondary qualifier

Reply
New Contributor
Posts: 3

Create a table in ODBC which require secondary qualifier

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

Valued Guide
Posts: 3,208

Re: Create a table in ODBC which require secondary qualifier

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

---->-- ja karman --<-----
New Contributor
Posts: 3

Re: Create a table in ODBC which require secondary qualifier

Thanks Jaap for the quick response!

Can you provide a example with the appropiate syntax?

This is a new concept for me.

Valued Guide
Posts: 3,208

Re: Create a table in ODBC which require secondary qualifier

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 --<-----
Ask a Question
Discussion stats
  • 3 replies
  • 483 views
  • 0 likes
  • 2 in conversation