DB2 UDB Loading Error
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 251
- Joined: Tue Dec 02, 2008 3:09 pm
DB2 UDB Loading Error
Hi,
Actually i am trying to load the data from sequential file into db2 database using DB2 UDB stage. But i am trying to view data i am getting the following error:Actually when i use auto generated sql and try to view data i can view it but with user define sql i am getting this error as mention below
[0001]DB2_UDB_Loading_Job..DB2_UDB_API_1: [IBM][CLI Driver][DB2] SQL0206N "ORCHESTRATE.CONTACT_ID" is not valid in the context where it is used. SQLSTATE=42703
[0002]
DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'INSERT INTO OEDCOD.POC_CONTACT (CONTACT_ID,CONTACT_VALUE,MAINT_APP,MAINT_USER,CREATE_TS,LAST_UPDATED) VALUES (ORCHESTRATE.CONTACT_ID,ORCHESTRATE.CONTACT_VALUE,ORCHESTRATE.MAINT_APP,ORCHESTRATE.MAINT_USER,ORCHESTRATE.CREATE_TS,ORCHESTRATE.LAST_UPDATED)'. See following DB2 message for details.
DB2_UDB_Loading_Job..DB2_UDB_API_1.DSLink1: DSP.Open GCI $DSP.Open error -100.
Please help me out
thank you
Actually i am trying to load the data from sequential file into db2 database using DB2 UDB stage. But i am trying to view data i am getting the following error:Actually when i use auto generated sql and try to view data i can view it but with user define sql i am getting this error as mention below
[0001]DB2_UDB_Loading_Job..DB2_UDB_API_1: [IBM][CLI Driver][DB2] SQL0206N "ORCHESTRATE.CONTACT_ID" is not valid in the context where it is used. SQLSTATE=42703
[0002]
DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'INSERT INTO OEDCOD.POC_CONTACT (CONTACT_ID,CONTACT_VALUE,MAINT_APP,MAINT_USER,CREATE_TS,LAST_UPDATED) VALUES (ORCHESTRATE.CONTACT_ID,ORCHESTRATE.CONTACT_VALUE,ORCHESTRATE.MAINT_APP,ORCHESTRATE.MAINT_USER,ORCHESTRATE.CREATE_TS,ORCHESTRATE.LAST_UPDATED)'. See following DB2 message for details.
DB2_UDB_Loading_Job..DB2_UDB_API_1.DSLink1: DSP.Open GCI $DSP.Open error -100.
Please help me out
thank you
-
- Participant
- Posts: 251
- Joined: Tue Dec 02, 2008 3:09 pm
But when i am trying to run the job with auto generated sql job is getting aborted and giving the message
[0001]DB2_UDB_Loading_Job..DB2_UDB_API_1: [IBM][CLI Driver][DB2] SQL0206N "ORCHESTRATE.CONTACT_ID" is not valid in the context where it is used. SQLSTATE=42703
[0002]
DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'INSERT INTO OEDCOD.POC_CONTACT (CONTACT_ID,CONTACT_VALUE,MAINT_APP,MAINT_USER,CREATE_TS,LAST_UPDATED) VALUES (ORCHESTRATE.CONTACT_ID,ORCHESTRATE.CONTACT_VALUE,ORCHESTRATE.MAINT_APP,ORCHESTRATE.MAINT_USER,ORCHESTRATE.CREATE_TS,ORCHESTRATE.LAST_UPDATED)'. See following DB2 message for details.
DB2_UDB_Loading_Job..DB2_UDB_API_1.DSLink1: DSP.Open GCI $DSP.Open error -100.
[0001]DB2_UDB_Loading_Job..DB2_UDB_API_1: [IBM][CLI Driver][DB2] SQL0206N "ORCHESTRATE.CONTACT_ID" is not valid in the context where it is used. SQLSTATE=42703
[0002]
DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'INSERT INTO OEDCOD.POC_CONTACT (CONTACT_ID,CONTACT_VALUE,MAINT_APP,MAINT_USER,CREATE_TS,LAST_UPDATED) VALUES (ORCHESTRATE.CONTACT_ID,ORCHESTRATE.CONTACT_VALUE,ORCHESTRATE.MAINT_APP,ORCHESTRATE.MAINT_USER,ORCHESTRATE.CREATE_TS,ORCHESTRATE.LAST_UPDATED)'. See following DB2 message for details.
DB2_UDB_Loading_Job..DB2_UDB_API_1.DSLink1: DSP.Open GCI $DSP.Open error -100.
-
- Participant
- Posts: 251
- Joined: Tue Dec 02, 2008 3:09 pm
hey chulett sorry i have pasted the view data error in earlier post. when i am running the job here is the error get generated
APT_CombinedOperatorController,0: Failure during execution of operator logic.
APT_CombinedOperatorController,0: Fatal Error: Fatal: This SQL statement did not contain the same number of parameters as columns on this link. This usually happens with user-defined SQL.
node_node1: Player 1 terminated unexpectedly.
main_program: APT_PMsectionLeader(1, node1), player 1 - Unexpected exit status 1.
APT_CombinedOperatorController,0: Failure during execution of operator logic.
APT_CombinedOperatorController,0: Fatal Error: Fatal: This SQL statement did not contain the same number of parameters as columns on this link. This usually happens with user-defined SQL.
node_node1: Player 1 terminated unexpectedly.
main_program: APT_PMsectionLeader(1, node1), player 1 - Unexpected exit status 1.
-
- Participant
- Posts: 251
- Joined: Tue Dec 02, 2008 3:09 pm
I have search in forum for $dsp.open error -100 they said this error might cause because of invalid sql or invalid column name or invalid datatypes i have check all the above requirement they are fine but still i am unable to view the job as well as i am unable to run the job also i mean job is getting aborted. can any one help me out!!!
Thank you
Thank you
So... this error... is this with your user-defined sql or is this with the auto generated sql? I'm lost which one is which right now. If this is indeed user-defined, what exactly happens when you use auto-generated and View Data? Run the job?suneelchallagali wrote:hey chulett sorry i have pasted the view data error in earlier post. when i am running the job here is the error get generated
APT_CombinedOperatorController,0: Failure during execution of operator logic.
APT_CombinedOperatorController,0: Fatal Error: Fatal: This SQL statement did not contain the same number of parameters as columns on this link. This usually happens with user-defined SQL.
node_node1: Player 1 terminated unexpectedly.
main_program: APT_PMsectionLeader(1, node1), player 1 - Unexpected exit status 1.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 251
- Joined: Tue Dec 02, 2008 3:09 pm
Chulett the error which I posted in previous is for user define SQL where I am unable to view the data from view and unable to run the job but when I am using auto generated SQL I am able to view the data but unable to run the job. For both user define SQL and auto generated SQL options job is getting aborted and displaying the error as I mention in previous post
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Check which SQL was actually used at runtime. When you switch to generated SQL the user-defined SQL is not discarded but remains a link property. Under some circumstances (not sure exactly what they are) DataStage can use the stored user-defined SQL.
The workaround, if that is happening, is to replace the database stage and/or re-load the columns from an imported table definition.
The workaround, if that is happening, is to replace the database stage and/or re-load the columns from an imported table definition.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 251
- Joined: Tue Dec 02, 2008 3:09 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3
- Joined: Thu Sep 10, 2009 8:38 am
- Location: Cape Town
-
- Participant
- Posts: 3
- Joined: Thu Sep 10, 2009 8:38 am
- Location: Cape Town
-
- Participant
- Posts: 251
- Joined: Tue Dec 02, 2008 3:09 pm
Hi i have try to insert the record using user-define sql but when i am trying to run the job. Job is getting aborted and displaying error stating that
[0001]DB2_UDB_Test_Job2..DB2_UDB_API_2: [IBM][CLI Driver][DB2] SQL0206N "ORCHESTRATE.CONTACT_TYPE_CD" is not valid in the context where it is used. SQLSTATE=42703
[0002]
DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'INSERT INTO OEDCOD.CONTACT_TYPE (CONTACT_TYPE_CD,NAME,DESCRIPTION,ACTIVE_FLG,MAINT_DTM,MAINT_USERID) VALUES (ORCHESTRATE.CONTACT_TYPE_CD,ORCHESTRATE.NAME,ORCHESTRATE.DESCRIPTION,ORCHESTRATE.ACTIVE_FLG,ORCHESTRATE.MAINT_DTM,ORCHESTRATE.MAINT_USERID)'. See following DB2 message for details.
DB2_UDB_Test_Job2..DB2_UDB_API_2.DSLink1: DSP.Open GCI $DSP.Open error -100.
[0001]DB2_UDB_Test_Job2..DB2_UDB_API_2: [IBM][CLI Driver][DB2] SQL0206N "ORCHESTRATE.CONTACT_TYPE_CD" is not valid in the context where it is used. SQLSTATE=42703
[0002]
DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'INSERT INTO OEDCOD.CONTACT_TYPE (CONTACT_TYPE_CD,NAME,DESCRIPTION,ACTIVE_FLG,MAINT_DTM,MAINT_USERID) VALUES (ORCHESTRATE.CONTACT_TYPE_CD,ORCHESTRATE.NAME,ORCHESTRATE.DESCRIPTION,ORCHESTRATE.ACTIVE_FLG,ORCHESTRATE.MAINT_DTM,ORCHESTRATE.MAINT_USERID)'. See following DB2 message for details.
DB2_UDB_Test_Job2..DB2_UDB_API_2.DSLink1: DSP.Open GCI $DSP.Open error -100.
-
- Participant
- Posts: 251
- Joined: Tue Dec 02, 2008 3:09 pm
ok chulett i wont use user define sql but how to run the job. I am unable to understand what exact problem is able to view the data for auto generated sql but unable to run the job. when i actually the db2 database which i am loading is remote database and it is on main frames. I have catalog the database with client database i am able to view the data and retrieve the data from db2 database.
additional information:
Parameters set in environment variables
APT_DB2INSATNCE_HOME: C:\Documents and setting \all user\application data\ibm\db2\db2copy\db22
ATP_DBNAME:TSYITA0D Remote database
only these variable has been set.
additional information:
Parameters set in environment variables
APT_DB2INSATNCE_HOME: C:\Documents and setting \all user\application data\ibm\db2\db2copy\db22
ATP_DBNAME:TSYITA0D Remote database
only these variable has been set.