Conversion of string to Date

sandy · Post by **sandy** » Thu Sep 15, 2005 3:08 am

Hi All,

I have a requirement where I need to set a date column(of datatype date) of a target table in DB2/UDB with the value of a job parameter (Start_date). When I use the following derivation for mapping the job parameter into the column I am facing errors and nothing got inserted into the table, although the jobs finishes successfully :

Oconv(IConv(Effective_Date,"D-YMD[4,2,2]"),"D-YMD[4,2,2]")

The warning messages in job log are :

while processing column "START_DATE"
Value treated as NULL
Attempt to convert String value "2005-03-31" to Date type unsuccessful.

J1..TBL: [IBM][CLI Driver] CLI0112E Error in assignment. SQLSTATE=22005

J1..TBL: [IBM][CLI Driver][DB2/AIX64] SQL0902C A system error (reason code = "13") occurred. Subsequent SQL statements cannot be processed. SQLSTATE=58005

Please help me out if anyone has already faced and sorted out this issue.

Thanks in Advance

PhilHibbs · Post by **PhilHibbs** » Thu Sep 15, 2005 3:19 am

When providing a data to a DataStage component, you usually provide it in internal format, so get rid of the OCONV.

sandy · Post by **sandy** » Thu Sep 15, 2005 3:31 am

Thanks Phil for that correction. I got rid of OConv in the derivation and ran the job again. This time I did not get the first warning in my previous post but still got the second and the third one.

Also I noticed in the director log that the computed value for START_DATE was incorrect.
The log displayed START_DATE = 005-03-31 instead of 2005-03-31. Where did the "2" of 2005 go!!!!!?? Is this the problem for records not going in this time too!!?? Any ideas/suggestions?

Thanks

PhilHibbs · Post by **PhilHibbs** » Thu Sep 15, 2005 3:42 am

sandy wrote:Also I noticed in the director log that the computed value for START_DATE was incorrect.
The log displayed START_DATE = 005-03-31 instead of 2005-03-31. Where did the "2" of 2005 go!!!!!?? Is this the problem for records not going in this time too!!?? Any ideas/suggestions?
Thanks

So what is the value of the job parameter (Start_date)? How does that parameter relate to the code you posted, as it is not mentioned?

A bit of general advice on tracking down problems:

1. Drop a new Sequential File on the canvas and drag a link out to it from the transformer
2. Give it a name and a filename like 'debug.csv'
3. Map all input values to it
4. Create fields for all Stage Variables and map those
5. Copy the important output fields into the debug link
6. Make copies of the important output fields (and SVs) in the debug link, and put bits of the field derivations into them.
7. Run the job, and look at what's in the debug.csv file.

Phil Hibbs.

elavenil · Post by **elavenil** » Thu Sep 15, 2005 3:58 am

Hi,

Define the column as varchar in the target though the column's data type is date in the database and map the parameter. Then you should be able to load the data into the table without the problem.

HTWH.

Regards
Saravanan

elavenil · Post by **elavenil** » Thu Sep 15, 2005 4:00 am

Hi,

Did not specify the data type and format of the parameter in my previous reply. The datatype of the parameter is string and format is 'YYYY-MM-DD'.

HTWH.

Regards
Saravanan

sandy · Post by **sandy** » Thu Sep 15, 2005 5:12 am

Saravanan, I tried ur suggestion. Did not work and am getting the same error as before.

Phil, the parameter value is 2005-03-31 and I want to map this job parameter in the START_DATE date column in the target DB2/UDB table.

PhilHibbs · Post by **PhilHibbs** » Thu Sep 15, 2005 7:03 am

sandy wrote:Phil, the parameter value is 2005-03-31 and I want to map this job parameter in the START_DATE date column in the target DB2/UDB table.

Your code says IConv(Effective_Date,"D-YMD[4,2,2]") - is Effective_Date the parameter? Have you written a debug file like I suggested?

sandy · Post by **sandy** » Thu Sep 15, 2005 7:35 am

Yes you are right Phil. Effective_Date is the job parameter. I did try as you suggested to write into a sequential file. This works fine.

I guess the problem is when its being written into the DB2/UDB target table. Is there any option available with DS server jobs where we can specify to convert a string datatype to Date format acceptable by DB2/UDB?

Regards,

elavenil · Post by **elavenil** » Thu Sep 15, 2005 10:59 am

Hi Sandy,

Created a test job and load the data into a DB2 table using DB2 API stage. Defined a parameter as rundate (2005-09-11) with the data type 'string' and map this rundate in the transformer and it loads the data into DB2 table. This column defined as 'Date' data type in DB2 table.

It works fine.

Suggest you to use insert command using DB2 SQL statement and check the Date format in the DB side.

HTWH.

Regards
Saravanan

sandy · Post by **sandy** » Thu Sep 22, 2005 3:42 am

Hi Saravanan,

Thanks for your help. The problem was with some other field of the DB table defined as not null. This field was not part of the job and hence a null was trying to get into this field.

However, the date fields when defined as varchar(10) in DS, the job runs fine.

Thanks a lot for your help.!!