DB2 date Error, Value treated as null

[Andal]

When inserting a date filed to database (DB2) it is giving error,

"At row 10, link "ipc_tgt_load", while processing column "LST_PYMT_DT"
Value treated as NULL
Attempt to convert String value "1900-01-01" to Date type unsuccessful"

and

"At row 10, link "ipc_tgt_load"
DBMS.CODE=SQLCODE=SQL_ERROR NATIVECODE=-99999
SQLSTATE=22005
At row 10, link "ipc_tgt_load"
Row rejected.
At row 10, link "ipc_tgt_load"
DBMS.CODE=SQLCODE=SQL_ERROR NATIVEERROR=-99999
SQLSTATE=00000"

Previously the "LST_PYMT_DT" was with timestamp datatype in the job, and i checked with the database it was with datatype "Date". So i changed in the job as date even then i am getting this error. So i removed the value coming from source and tried with @DATE and "1900-01-01" , still i am getting the error.

Note : we are in product development , the corresponding date filed is of "PSDATE" (peoplesoft user defined data type which will accept timestamp) in SQL server, In Oracle it is showing as DATE and in DB2 it is showing as DATE

[chulett]

From hanging out here, it seems that DB2 is the only stage that wants DATE fields in internal rather than the external format the others use. You should be able to search for various posts to this effect.

[elavenil]

Define the field datatype as 'varchar(10)' instead of date and it should work.

HTWH.

Regards
Saravanan

[Sainath.Srinivasan]

Use ds date.

[DS_MJ]

Try using "LST_PYMT_DT" column as CHAR and it should work.

DS_MJ

[Sainath.Srinivasan]

You need IConv

[DS_MJ]

You need IConv

I was talking that if after doing ICON conversion in transform using field type as date and if one wants to load to db2 using the DB2 UDB plugin directly and if the load fails then what worked for me was to set the type column on the db2 udb to char even though in the transform I have it as date and do the ICON conversion or whatever date conversion and the load was successful. I am sorry if I mislead.

MJ[/b]