DB2 Timestamp Format

[JDionne]

I am haveing the worst time formating a date to go into my DB2 UDB server. This is a date out of the server: Aug 21, 2003 2:13:44 PM
This is what Im outputing in DS: Mar 01 2004 00:00:00
using the dirivation OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2,]"), "DMDY[A3,2,4,]") :" " : Oconv("00:00:00","MTHS")

and im getting the error: EXP1LoadStgTbl..Transformer_115: [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

where am I off in my format?
Jim

[vinnz]

DB2 stores timestamps with microseconds ie 2000-07-31-10.30.15.610208

You can try inserting by formatting it into YYYY-MM-DD-hh.mm.ss.zzzzzz

If its user defined SQL then you can also use the DB2 timestamp functions.
To convert a character string to a date or time value, you can use:

Code: Select all

TIMESTAMP ('2004-05-18-12.00.00.000000') 
TIMESTAMP ('2004-05-18 12:00:00') 

[/code]

[ogmios]

Are you using an ODBC stage or a DB2 stage to connect to your target?
Is you're source an ODBC stage or a DB2 stage?

Ogmios

[JDionne]

Are you using an ODBC stage or a DB2 stage to connect to your target?
Is you're source an ODBC stage or a DB2 stage?

Ogmios

Im using the db2 stage and my source is a sequencial file
Jim

[ogmios]

The DB2 stage as a target wants to receive the "internal format" for dates. For only a date I use the following in a transformer:

Code: Select all

Iconv(In.Date, "D4-YMD[4,2,2]")

when the input is something as 2004-05-18

I don't find an example in my jobs for a timestamp conversion, but using the written months (MAR instead of 3) has never worked for me.

Ogmios

P.S. Just remebered something, I think you can get away with just formatting your field in the right format for timestamps as in the mail of vinnz.

[JDionne]

AGGGGGGGG
not haveing a good day. So what Im doing is wrong obviously. Can any one tell me what formate DB2 wants to see a timestamp in so that I can formate it in that way. I have looked at the data and what I see is aparently something that DB2 displays but not what it actualy stores. Any help would be great!!
Jim

[JDionne]

Ok here is the update. I am running the following code:
OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2,]"), "D4-MDY[2,2,4]") : " " : oconv(00:00:00, "MTS")
getting the following error from the Director: EXP1LoadStgTbl..Transformer_115: [IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

any ideas?
Jim

[ogmios]

I had some time to make a test job.

As a first put the following in your transformer that links to the timestamp in DB2:

Code: Select all

"2004-05-24" : " " : Oconv("00:00:00","MTHS")

So you in fact you actually hardcode the timestamp. Now run the job, if it works do the following, else write back with what you get as output.

I'm assuming your input column is of the format YYYY-MM-DD (if it's not you can easily replace that part):

Code: Select all

In.Date : " " : Oconv("00:00:00","MTHS")

Where In.Date is your column (as varchar e.g.). If this works you just have to make sure you get the output for the date part in the right format as a string (by using Iconv/Oconv combos).

Ogmios

[JDionne]

AGGGG
I used this line:

Code: Select all

"2004-05-24" : " " : Oconv("00:00:00","MTHS")

and it worked, so that means there is a problem with my oconv inconv.
this is my code:

Code: Select all

OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2,]"), "D4-MDY[2,2,4]")  

so whats up with this?
Jim

I had some time to make a test job.

As a first put the following in your transformer that links to the timestamp in DB2:

Code: Select all

"2004-05-24" : " " : Oconv("00:00:00","MTHS")

So you in fact you actually hardcode the timestamp. Now run the job, if it works do the following, else write back with what you get as output.

I'm assuming your input column is of the format YYYY-MM-DD (if it's not you can easily replace that part):

Code: Select all

In.Date : " " : Oconv("00:00:00","MTHS")

Where In.Date is your column (as varchar e.g.). If this works you just have to make sure you get the output for the date part in the right format as a string (by using Iconv/Oconv combos).

Ogmios

[ogmios]

The extra comma e.g. in "D-YMD[4,2,2,]" at the end.

Reroute the output of your transformer to a sequential file and look in the sequential file what you find there (take the same column types as in the database)

Ogmios

[JDionne]

Removeing the coma and sending the out put to a flat file gets me the output of: 03-01-2004 12:00:00am
using the following code: OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2]"), "D-MDY[2,2,4]") : " " : oconv(00:00:00, "MTHS")
PS. removing the comma did not alow for a sucessfull load.
Jim

The extra comma e.g. in "D-YMD[4,2,2,]" at the end.

Reroute the output of your transformer to a sequential file and look in the sequential file what you find there (take the same column types as in the database)

Ogmios

[ogmios]

The "am" is not good in time, DB2 is very picky on its input. Try "MTS" for the time part.

Ogmios

[JDionne]

The "am" is not good in time, DB2 is very picky on its input. Try "MTS" for the time part.

Ogmios

out put of code:
03-01-2004 00:00:00

code: OCONV(ICONV(Exp_Stg_Load.VDATE, "D-YMD[4,2,2]"), "D-MDY[2,2,4]") : " " : oconv(00:00:00, "MTS")

Still failed
Jim

[ogmios]

The date part needs to be YYYY-MM-DD unless your DBA change local settings. I'm getting "foggy" after programming datastage jobs for 8 hours.

Ogmios

[JDionne]

The date part needs to be YYYY-MM-DD unless your DBA change local settings. I'm getting "foggy" after programming datastage jobs for 8 hours.

Ogmios

ohh the world is complete!!!! the bit of knowlage that i needed, the DB2 Date Format!!!!!!!!
you are a god man
thanx
Jim