DSXchange

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?

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

Or EXECUTE or OPEN?

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

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

Are you performing the truncate in your stage?

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.

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.

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.

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

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

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.