SQLSTATE=HY000, DBMS.CODE=-302

jayram99 · Post by **jayram99** » Fri Jan 13, 2006 3:40 pm

Hi All,

I am doing Lookup using ODBC Stage and I am getting the following error msg. Even though i got match ,i couldn't retrieve the data from Table.

The error is
****************************
SQLSTATE=HY000, DBMS.CODE=-302
DSD.BCIGetByKey call to SQLExecute failed.
*********
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver][DB2]Character data, right truncation occurred; for example, an update or insert value is a string that is too long for the column, or a datetime value cannot be assigned to a host variable, because it is too small.

********************************************
I tried by increasing the size and also changed all the fields to Varchar.
I am getting the same error msg.

Could someone help me to resolve the same.

Thanks & Regards,
Jayaram

chulett · Post by **chulett** » Fri Jan 13, 2006 5:10 pm

Not sure how anyone here could help. You've got the problem spelled out in the error message, perhaps another set of eyes onsite with you would help. Or try increasing the size... again.

jayram99 · Post by **jayram99** » Fri Jan 13, 2006 6:13 pm

Hi All,

Tried many ways. I think I am missing something in my Query.

These are the following i have taken care.

1 . Selected User Defined Query
2. 2 fields has key fields(If i use only 1 field,getting the perfect result)
3. Used the following query

SELECT
CUSTDBA.TPOL_CLNT_ADDR.ASCO_NO,
CUSTDBA.TPOL_CLNT_ADDR.DUN_BRADSTREET_NO,
CUSTDBA.TPOL_CLNT_ADDR.NAME_BLOK_VRBL_TX,
CUSTDBA.TPOL_CLNT_ADDR.INSRD_POSTAL_CD,
CUSTDBA.TPOL_CLNT_ADDR.ADDR_BLOK_VRBL_TX,
CUSTDBA.TPOL_CLNT_ADDR.HS_NO_BLOK_VRBL_TX,
CUSTDBA.TPOL_CLNT_ADDR.CITY_BLOK_VRBL_TX

FROM CUSTDBA.TPOL_CLNT_ADDR
WHERE
CUSTDBA.TPOL_CLNT_ADDR.INSRD_POSTAL_CD = ? AND
CUSTDBA.TPOL_CLNT_ADDR.DUN_BRADSTREET_NO = ?

GROUP BY
CUSTDBA.TPOL_CLNT_ADDR.ASCO_NO,
CUSTDBA.TPOL_CLNT_ADDR.DUN_BRADSTREET_NO,
CUSTDBA.TPOL_CLNT_ADDR.NAME_BLOK_VRBL_TX,
CUSTDBA.TPOL_CLNT_ADDR.INSRD_POSTAL_CD,
CUSTDBA.TPOL_CLNT_ADDR.ADDR_BLOK_VRBL_TX,
CUSTDBA.TPOL_CLNT_ADDR.HS_NO_BLOK_VRBL_TX,
CUSTDBA.TPOL_CLNT_ADDR.CITY_BLOK_VRBL_TX

3. In transformer , For Matching I am using Stage Variable.
Ex:If IsNull(Lkp_Candidates.ASCO_NO) then 0 else 1 (svMatch)- svMatch is Stage Variable Name.
4. I Need multiple rows so I selected Reference Link with multi row result set in Transformer Stage Properties
5. SQL Type matches for both column names and Host Variable.

-------------------------------------------------------------------------
In ODBC Stage,Is any particular way to write USER DEFINED QUERY for 2 or more key fields in the where clause.

Could someone pls. help me how to query a DB2 table through ODBC Stage to return many rows and for different conditions(Where Clause).

Thanks & Regards,
Jayram

ray.wurlod · Post by **ray.wurlod** » Fri Jan 13, 2006 11:56 pm

Start by getting your DBA to tell you what an error code -302 is in DB2.

vmcburney · Post by **vmcburney** » Sun Jan 15, 2006 4:45 pm

It might be error SQL0301 which is input host variable or argument not valid. This would indicate one of the two join key fields is not valid. Can you do a "View Data" on the source, this should run the query without the filter, if the view data works then the error is in one of your two join fields.[/quote]