Datetime field overflow error

[prmuttireddy]

Hi,

I have been using the following scenario.
DRS stage(source)---->Trans Stage---->DRS stage(target)

In Target table i have one filed as Maritus_DT and data type is Date.I have changed that filed data type to Timestamp.But that field not exists in the Source.so i have given the default value as "DSJOBSTARTTIMESTAMP"
but while i was running it has been giving the error as following.

Serv_hr89_epm89_dep_ben_eff_ins_after_upd..TRNAddSubRecVal: [IBM][CLI Driver] CLI0114E Datetime field overflow. SQLSTATE=22008

How to solve this one.Please help me.

Adavanced thanks.

Regards,
Reddy.

[ajith]

Hi,

I have been using the following scenario.
DRS stage(source)---->Trans Stage---->DRS stage(target)

In Target table i have one filed as Maritus_DT and data type is Date.I have changed that filed data type to Timestamp.But that field not exists in the Source.so i have given the default value as "DSJOBSTARTTIMESTAMP"
but while i was running it has been giving the error as following.

Serv_hr89_epm89_dep_ben_eff_ins_after_upd..TRNAddSubRecVal: [IBM][CLI Driver] CLI0114E Datetime field overflow. SQLSTATE=22008

How to solve this one.Please help me.

Adavanced thanks.

Regards,
Reddy.

That may be because when you give DSJOBSTARTTIMESTAMP as a timestamp, the data will be stored in datastage default timestamp format ie
yyyy-mm-dd hh:nn:ss

now if your database expect the insert values in some other format you have to explicitly convert DSJOBSTARTTIMESTAMP to that format

you can use TimestamptoString function to achieve that ..

If you go to the DRS and check the generated query you will be able to understand the format in which that expects the data

Hope that helps,
Ajith

[prmuttireddy]

Hi Ajit,

Thanks for your reply.But i am not getting solution.In source generated query for Timestamp it has used as
TO_CHAR(source fieldname, 'YYYY-MM-DD HH24:MI:SS').
But in this case how to keep the conversion for that default DSJOBTIMESTAMP value.

Regards,
Reddy.

[ajith]

Hi Ajit,

Thanks for your reply.But i am not getting solution.In source generated query for Timestamp it has used as
TO_CHAR(source fieldname, 'YYYY-MM-DD HH24:MI:SS').
But in this case how to keep the conversion for that default DSJOBTIMESTAMP value.

Regards,
Reddy.

use TimeStampToString(DSJOBTIMESTAMP,"%yyyy-%mm-%dd %hh:%nn:%ss") in the transformer, that would help

[DSguru2B]

use TimeStampToString(DSJOBTIMESTAMP,"%yyyy-%mm-%dd %hh:%nn:%ss") in the transformer, that would help

Thats a px function and of no use in a server job.
prmuttireddy, how did you change the date field to timestamp. Changing just in DataStage is not enough. YOu have to change it in the database table. The date field requires a field of length 10 and your passing it a timestamp. Try passing just DSJobStartDate.

[ajith]

use TimeStampToString(DSJOBTIMESTAMP,"%yyyy-%mm-%dd %hh:%nn:%ss") in the transformer, that would help

Thats a px function and of no use in a server job.
prmuttireddy, how d ...

oops, I apologise for the mistake,Thanks to DSGuru for pointing that out

for me under
routines->sdk->Date
there is a function
called DateTimeStampToOraOCIWithTime(DSJOBTIMESTAMP)
similarly there are ones for ODBC and SYbase too
try with that.

really sorry for the inconvenience caused

[I_Server_Whale]

Hi,


In Target table i have one filed as Maritus_DT and data type is Date.I have changed that filed data type to Timestamp.But that field not exists in the Source.so i have given the default value as "DSJOBSTARTTIMESTAMP"
but while i was running it has been giving the error as following.

What is your target database? Is the target field expecting a date with timestamp or just the date?

If its just the date, then did you try using 'DSJobStartDate' instead of 'DSJobStartTimestamp'.

Whale.

[prmuttireddy]

Hi,

Target database is DB2(DRS stage).

I have changed to date to timestap datatype and i have given the DSJOBSTARTTIMESTAP as default value.But it is not working.

regards,
Reddy.

[I_Server_Whale]

Hi,

What is the datatype of the field in the DB2 database? Did you try 'DSJobStartDate' macro?

Whale.

[prmuttireddy]

I changed to Timestamp.Actual the Datatype in DB2 is DATE.Even i tried using DSJOBSTARTDATE also,not work out.

[I_Server_Whale]

Instead of just saying this

also,not work out.

It is always advised to provide more detail about your problem, more importantly the error messages that you are encountering when running the job(s).

Can you post the error/warning messages from the DS Director?

Thanks,
Whale.

[prmuttireddy]

I already mentioned,ok no problem..
here i am sending the warning........

Serv_hr89_epm89_dep_ben_eff_ins_after_upd..TRNAddSubRecVal: [IBM][CLI Driver] CLI0114E Datetime field overflow. SQLSTATE=22008

Regards,
Reddy.

[DSguru2B]

I believe DB2 requires date in internal format. Try ICONV(DSJobStartDate,"D-YMD")

[I_Server_Whale]

Although a little diversion from the actual problem, I'm wondering the reason behind the use of a DRS stage to load a DB2 table.

Why aren't you using the DB2_UDB_API stage instead? Any specific reasons other than the DB switching capability of DRS stage?

Try using the DB2 API stage.

Whale.

[prmuttireddy]

Hi,

Thanks to all.I resolved it.
we can use Either two ways.

DSJobStartDate : " 00:00:00"

(OR)

ICONV(DSJobStartDate,"D-YMD")

Regards,
Reddy.