AS400 CREATE TABLE via ODBC returning errors

wwalker · Post by **wwalker** » Thu Jun 14, 2007 1:43 am

I have an interesting problem.

I am trying to drop, then create/recreate a table in AS400 from a flat file. This should be a very simple job.

We make the connection through ODBC to AS400, and pass user name, pwd, odbc dsn, Environment, library and table name as parameters

Example DDL:

CREATE TABLE
#AS400_SCHEMA##ENVIRONMENT#.PURS#SAP_SITE##YEAR_MONTH#
(FIELD1 CHAR(3) NOT NULL);

It seems that with AS400, some tools accept the use of "." to separate library from table, and some accept "/".

In DataStage, I am getting these results:

"." separator:

"Fully-qualified table names are not supported in DDL statements ["<library>/<table>" ]. Using Un-qualified name."

--strange that the error message is specifying the "/", when the "." was used...

"/" separator :

SQLSTATE=S1000, DBMS.CODE=-5016
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for iSeries and AS/400]
Unknown error: SQLCODE -5016

OR this error:

SQLSTATE=37000, DBMS.CODE=-104
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for iSeries and AS/400]
ILLEGAL SYMBOL ; VALID SYMBOLS ARE <END-OF-STATEMENT>

Also, when using the "/", the DS DDL puts the library/tablename in quotes like this:

CREATE TABLE
"BICF00D/PURS8167"
(FIELD1 CHAR(3) NOT NULL)

This is blocking the development, and I am rapidly running out of ideas....Any of you bright minds out there or AS400 gurus have some insight?

FURTHER TO THIS...

It appears that attempting to DROP TABLE if the table does not exist in AS400 returns errors....any ideas how to circumvent this limitation?

thanks

W

gsbrown · Post by **gsbrown** » Thu Jun 14, 2007 11:41 am

We work with DB2 on AS/400 and all of our ODBC stages have to have these settings to work properly. Try this:

Quote character: 000
Schema delimiters: .

The 000 will specify that you don't want to use quotes and the . should be the correct <schema>.<table> delimiter.

wwalker · Post by **wwalker** » Wed Jun 20, 2007 1:30 am

Thanks for the comment...tried it, and at least I didn't get my table name in quotes...however, still have the same problem:

Fully-qualified table names are not supported in DDL statements [BICF00D/PURS8167]. Using Un-qualified name.

And also am still getting the same error, though the SQL is very simple, and apparently is correct! :

SQLSTATE=37000, DBMS.CODE=-104
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for iSeries and AS/400]ILLEGAL SYMBOL ; VALID SYMBOLS ARE <END-OF-STATEMENT>|

ALSO - even when I do tests with generated code in DS, I am getting this error:

SQLSTATE=S1000, DBMS.CODE=-5016
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for iSeries and AS/400]Unknown error: SQLCODE -5016|

Does anyone have some suggestions?

thanks

w

gsbrown · Post by **gsbrown** » Wed Jun 20, 2007 7:08 am

Fully-qualified table names are not supported in DDL statements

Did you try dropping the schema name? You shouldn't need that since it's specified in your ODBC settings which schema your pointing to.

wwalker · Post by **wwalker** » Wed Jun 20, 2007 7:19 am

When I receive that warning, it means that DS has automatically dropped the library name from the query..and when it does this, I get:

EXTPTP003createtabletests2..ODBC_34.LK_Create_Table: DSD.BCIOpenW call to SQLExecDirect failed.
CREATE TABLE PURS8167(...<Field names>...)
SQLSTATE=S1000, DBMS.CODE=-551
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for iSeries and AS/400]QSYS.DATAST DOES NOT HAVE PRIVILEGE TO PERFORM OPERATION USER ON THIS OBJECT (null)

Through my ODBC bind, I have access to more than one library...so if that is dropped, it is ambiguous...and perhaps is trying to create a table in an undefined, or general library...

Thanks for your ideas!

wade