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
AS400 CREATE TABLE via ODBC returning errors
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 40
- Joined: Thu Mar 30, 2006 6:30 am
- Location: Near Geneva, Switzerland
- Contact:
AS400 CREATE TABLE via ODBC returning errors
Wade Walker
-
- Premium Member
- Posts: 40
- Joined: Thu Mar 30, 2006 6:30 am
- Location: Near Geneva, Switzerland
- Contact:
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
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
Wade Walker
-
- Premium Member
- Posts: 40
- Joined: Thu Mar 30, 2006 6:30 am
- Location: Near Geneva, Switzerland
- Contact:
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
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
Wade Walker