DSXchange

We recently moved from DB2 (Mainframe) to Oracle 9i for one of our database. Since DB2 date datatype represents YYYY-MM-DD, in datastage the corresponding field was char(10). As part of the move to Oracle from DB2 we needed to change a lot of jobs to accomodate oracle date value (YYYY-MM-DD HH:MM:SS), except in couple of jobs where the datatype for a date field was left at char(10).

In the development environment no jobs aborted inspite of this anomaly. In the pre-production the jobs did not abort either. However, in production these 2 jobs aborted with the error "OCI has fetched tuncated data".

On analyzing the issue, we found that in development the NLS_DATE_FORMAT (from V$Parameter) was set to YYYY-MM-DD. So the char(10) defined in the 2 datastage jobs was long enough to hold the data. In production, NLS_DATE_FORMAT was set to YYYY-MM-DD HH:MI:SS. We chnaged to SQL statement to get the first 10 characters to make the jobs work.

My question is - I checked the NLS_DATE_FORMAT value in pre-production and it was same as in production. Yet the jobs never failed in pre-prod. What other areas I need to look at to find the reason for jobs "NOT FAILING" in pre-production.

I will appreciate you help. Thanks.

Bhusan

It's worth the time to go thru all OCI stages and correct the previous CHAR workaround for the DB2 CLI date issue. The Oracle OCI stages will generate SQL with TO_DATE and TO_CHAR functions to put data into YYYY-MM-DD YYYY:MM:SS format automagically.

Get away from the residual impact of using DB2 CLI and embrace the greatness of the Oracle stage.

Welcome to the Dark Side! Have a cookie.

Thanks for the reply.

Now that the changes have been done and I am into the situation where the issue has been fixed and taken care of, all I am being asked to do is "root cause analysis". I would appreciate if I can get some help to determine the cause of different behavior in my pre-production environment.

Thanks again.

Where/how did you check the NLS_DATE_FORMAT?