Page 1 of 2
DB2 UDB Loading Error
Posted: Mon Jan 18, 2010 2:05 pm
by suneelchallagali
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
Posted: Mon Jan 18, 2010 2:07 pm
by chulett
So, what's wrong with using the auto-generated sql?
Posted: Mon Jan 18, 2010 2:19 pm
by suneelchallagali
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.
Posted: Mon Jan 18, 2010 2:27 pm
by suneelchallagali
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.
Posted: Mon Jan 18, 2010 5:27 pm
by suneelchallagali
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
Posted: Mon Jan 18, 2010 5:46 pm
by chulett
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.
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?
Posted: Mon Jan 18, 2010 7:26 pm
by suneelchallagali
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
Posted: Mon Jan 18, 2010 8:11 pm
by ray.wurlod
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.
Posted: Mon Jan 18, 2010 8:36 pm
by suneelchallagali
thank for your suggestion ray. I had Checked it even though i am using generated sql it is taking user define sql. so now do i need to re-create the job or remove the db2 udb stage and replace it again.
Posted: Mon Jan 18, 2010 8:58 pm
by ray.wurlod
I'd try just replacing the column metadata first (delete and load). If that doesn't work, try replacing the stage.
Posted: Tue Jan 19, 2010 1:12 am
by peterzehwork
Try again the SQL Insert however without specifying the column which is defined as autogenerated Id. It gets populated by the Db.
Posted: Tue Jan 19, 2010 1:13 am
by peterzehwork
Try again the SQL Insert however without specifying the column which is defined as autogenerated Id. It gets populated by the Db.
Posted: Tue Jan 19, 2010 8:49 am
by suneelchallagali
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.
Posted: Tue Jan 19, 2010 9:02 am
by chulett
When I've seen things like this, deleting and recreating the stage from scratch has resolved the weirdness. Do not set up any user-defined sql.
Posted: Tue Jan 19, 2010 9:33 am
by suneelchallagali
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.