Datetime field overflow error
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
Datetime field overflow error
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.
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.
Re: Datetime field overflow error
That may be because when you give DSJOBSTARTTIMESTAMP as a timestamp, the data will be stored in datastage default timestamp format ieprmuttireddy wrote: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.
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
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
use TimeStampToString(DSJOBTIMESTAMP,"%yyyy-%mm-%dd %hh:%nn:%ss") in the transformer, that would helpprmuttireddy wrote: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.
Thats a px function and of no use in a server job.ajith wrote: use TimeStampToString(DSJOBTIMESTAMP,"%yyyy-%mm-%dd %hh:%nn:%ss") in the transformer, that would help
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
oops, I apologise for the mistake,Thanks to DSGuru for pointing that outDSguru2B wrote:Thats a px function and of no use in a server job.ajith wrote: use TimeStampToString(DSJOBTIMESTAMP,"%yyyy-%mm-%dd %hh:%nn:%ss") in the transformer, that would help
prmuttireddy, how d ...
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
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Re: Datetime field overflow error
What is your target database? Is the target field expecting a date with timestamp or just the date?prmuttireddy wrote: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.
If its just the date, then did you try using 'DSJobStartDate' instead of 'DSJobStartTimestamp'.
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Instead of just saying this
Can you post the error/warning messages from the DS Director?
Thanks,
Whale.
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).prmuttireddy wrote:also,not work out.
Can you post the error/warning messages from the DS Director?
Thanks,
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
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.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Participant
- Posts: 30
- Joined: Tue Jun 20, 2006 10:22 pm