DB2 Connector error:EXECUTE or OPEN stat is large

informdaya@gmail.com · Mon Sep 09, 2013 10:00 am

Hi All,
I facing a strange issue in DataStage.
My job fails due to following error

[IIS-CONN-DB2-000004] DB2 function SQLExecute failed: SQLSTATE = 22001: Native Error Code = -302: Msg = [IBM][CLI Driver][DB2/LINUXX8664] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use. SQLSTATE=22001

This is happen during when i ran my job in main sequence[4 other jobs rans in parallel].

When i ran only the particular sequence for this job,it works fine. I talked sysdba about this thing, they said there is nothing wrong at that time.

Do you have any clue,how i can resolve this issue?

ArndW · Post by **ArndW** » Mon Sep 09, 2013 10:18 am

Do you have a BEFORE or AFTER SQL in that stage?

chulett · Post by **chulett** » Mon Sep 09, 2013 10:23 am

Or EXECUTE or OPEN?

ArndW · Post by **ArndW** » Mon Sep 09, 2013 11:31 am

Lol, been living in the SQL-Server world too long.

informdaya@gmail.com · Mon Sep 09, 2013 12:13 pm

Dear All,
there is no after or before statement and there is no exceute statement also. Its an insert statement on a truncated table. Please note if i ran it manually only this subsequence and its work fine

ArndW · Post by **ArndW** » Tue Sep 10, 2013 2:02 am

Are you performing the truncate in your stage?

informdaya@gmail.com · Tue Sep 10, 2013 1:26 pm

Hi ArndW,

Yes . It is a staging table. I am using before job sub routine and calling a unix script to truncate table . After running the job i am facing this issue. I am following same rule other 30 tables and it is working fine. Same unix script is used for all other jobs. If i run it manully it is working fine but if i schedule it iam getting this error DB2 function SQLExecute failed: SQLSTATE = 22001: Native Error Code = -302: Msg = [IBM][CLI Driver][DB2/LINUXX8664] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use. SQLSTATE=22001.. Please help.

ArndW · Post by **ArndW** » Wed Sep 11, 2013 2:11 am

If you turn on OSH_ECHO, OSH_EXPLAIN and OSH_DUMP, I believe that one of them will echo the actual SQL command sent to the connector and looking at that might shed more light on the issue.

informdaya@gmail.com · Fri Sep 13, 2013 9:45 am

The surprising fact of that particular subsequencer is if you run it manually,it works fine,
but you schedule it only that sub sequencer it fails also.

chulett · Post by **chulett** » Fri Sep 13, 2013 10:09 am

It you haven't done so already, you really should involve your official support provider.

chandra.shekhar@tcs.com · Sun Sep 15, 2013 3:22 am

This error normally occurs when you try to store a value in a column whose datatype is lesser than the original value.
Also whenever your data contains junk value and if its length is greater than the column datatype, you will get the above said error.
The only solution for this sort of error is to increase the column datatype size.
This type of error normally occurs is String fields, specifically in columns with descriptive value like NAME, ADDRESS, LOCATION, DESCRIPT etc.

You might have a column where you store descriptive data.
First you have to identify the column then ask your DBA to increase the datatype length, then modify the job accordingly and rerun.

Kindly check the below link
http://pic.dhe.ibm.com/infocenter/dzich ... 2Fn302.htm

informdaya@gmail.com · Tue Sep 17, 2013 12:57 pm

Hi Chandra,
I also thinked that way only.
Now the only question remaining if i run this jon manually,it works fine.
If i scheduled it,then only it fails. I tried with different DB2 stages,but no improvement.