AS400 CREATE TABLE via ODBC returning errors
Posted: 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
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