DB2 UDB Loading Error

suneelchallagali · Post by **suneelchallagali** » Mon Jan 18, 2010 2:05 pm

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

chulett · Post by **chulett** » Mon Jan 18, 2010 2:07 pm

So, what's wrong with using the auto-generated sql?

suneelchallagali · Post by **suneelchallagali** » Mon Jan 18, 2010 2:19 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.

suneelchallagali · Post by **suneelchallagali** » Mon Jan 18, 2010 2:27 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.

suneelchallagali · Post by **suneelchallagali** » Mon Jan 18, 2010 5:27 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

chulett · Post by **chulett** » Mon Jan 18, 2010 5:46 pm

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?

suneelchallagali · Post by **suneelchallagali** » Mon Jan 18, 2010 7:26 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

ray.wurlod · Post by **ray.wurlod** » Mon Jan 18, 2010 8:11 pm

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.

suneelchallagali · Post by **suneelchallagali** » Mon Jan 18, 2010 8:36 pm

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.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 18, 2010 8:58 pm

I'd try just replacing the column metadata first (delete and load). If that doesn't work, try replacing the stage.

peterzehwork · Post by **peterzehwork** » Tue Jan 19, 2010 1:12 am

Try again the SQL Insert however without specifying the column which is defined as autogenerated Id. It gets populated by the Db.

peterzehwork · Post by **peterzehwork** » Tue Jan 19, 2010 1:13 am

Try again the SQL Insert however without specifying the column which is defined as autogenerated Id. It gets populated by the Db.

suneelchallagali · Post by **suneelchallagali** » Tue Jan 19, 2010 8:49 am

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.

chulett · Post by **chulett** » Tue Jan 19, 2010 9:02 am

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.

suneelchallagali · Post by **suneelchallagali** » Tue Jan 19, 2010 9:33 am

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.