Hello for everyone, I have problems reading an AS/400 file using DB2 Connect. Follow is the configuration and table specifications in order that you can understand the situation:

DB2/400 v5R4
DB2 Enterprise in Linux with DB2 Connect both 9.7 version SP 3.

AS/400 Table call CUMST, this table has about 92 columns however, into this columns have two specific columns that cause the problem: CUSILV and CUSMAM both of them are defined as NUMERIC(1,0) and NOT NULL, for some reason the CUSILV and CUSMAN has ++ as value in some records, I realize a SQL query in the AS/400 and get the following output:

SELECT CUSCUN, CUSILV, CUSMAM FROM BACHIFILES.CUMST WHERE CUSCUN = 12345

The Ouput:

CUSCUN CUSILV CUSMAN
--------------- ------------- -----------------
12345 ++ +++

When I used DB2 LUW to query the same table using DB2 Connect I receive the followin error:

SQL0304N A value cannot be assigned to a host variable because the value is
not within the range of the host variable's data type. SQLSTATE=22003

My Question is: exist some SQL command in DB2 LUW in order to determine wich records in the AS/400 has the same problems??? I'm try using a COALESCE but the field is not null and has a value.

Please your help.

Best Regards

Gerardo B.

I couldn't understand. Why is it possible?

++ is not numeric and length may be 2.
How was it stored in NUMERIC(1,0)?

Really don't know how the table has that values inserted, I realized an update over a field that found with this error trying to put a NULL VALUE, Character and get a SQL error just the database must response in that case, then I update with a numeric value and the SQL works fine. The problems is that the table has about 2 millions of records and there is imposible to match wich fields had this problems inside.

Try to change te QCCSID in the AS/400 and in the DB2 Connect with no success. The AS/400 has the value 65535 for QCCSID stored at *SYSVAL, the DB2 Connect has the same value.

I'm investigating the problem....

Best Regards