topic Re: Create a table in ODBC which require secondary qualifier in Administration and Deployment

Create a table in ODBC which require secondary qualifier

Dataanalyst313 — Fri, 11 Jul 2014 14:57:49 GMT

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

Re: Create a table in ODBC which require secondary qualifier

jakarman — Fri, 11 Jul 2014 15:25:17 GMT

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

Re: Create a table in ODBC which require secondary qualifier

Dataanalyst313 — Fri, 11 Jul 2014 15:33:05 GMT

Thanks Jaap for the quick response!

Can you provide a example with the appropiate syntax?

This is a new concept for me.

Re: Create a table in ODBC which require secondary qualifier

jakarman — Fri, 11 Jul 2014 15:54:58 GMT

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)