Dataloading between two DB2 databases

[anilkona]

I am trying to load data between two databases. Both are DB2 databases and the table structure is exactly same. The ETL map is very straighforward.

This particular ETL makes use of Dynamic RDBS stage for source and target.

When I execute the map, I am getting the following error.

[IBM][CLI Driver][DB2/AIX64] SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007

I am able to execute an insert statement outside of ETL fine with proper date values. I have also checked the date value in source database and it looks fine to me.

Any ideas?

Thanks in advance,
-Anil

[ArndW]

Anil,

what is the string date value on the row that is generating the error? Typically date data-types are pretty finicky in their formatting. You have a DB2 datetime which is like an Oracle date (but not a timestamp) but you might have the YMD or MDY order incorrect (in that row)

[kris]

Hi Anil

[IBM][CLI Driver][DB2/AIX64] SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007

-Anil

DB2 supports automatic conversion and entry of data info into the database in both MM-DD-YYYY and YYYY-MM-DD formats.

Make sure in your job, the date value is in right format. May be if you can write records to a file and see what are the value you have exactly before you hit the table.......If it is already in internal date format then DATE.TAGE transform should work to convert into format (YYYY-MM-DD).

Hope this will help.

Kris~

[anilkona]

I think there is a problem with DRS stage in 7.1R1 release.

If I change the type of SQL data type in the source and target stages to TIMESTAMP, it works fine.

But the data type is defined as DATE in database for both source and target tables.

Does anyone know of problems with DRS and 7.1R1?

Thanks,
-Anil