Loading Time in DB2

shyam_etl · Post by **shyam_etl** » Thu Sep 08, 2005 10:10 pm

Hi all,

I have an issue loading Time into a DB2 table field ( sql type 'Time' length 8 ).

When i use Oconv(@time, "MTS"), the database wouldnt accept the return value identifing it as a string.

To my utter surprise,The same derivation Oconv(@time, "MTS") for a Timestamp field ( length 26) returns the Timestamp in 'YYYY-MM-DD HH:MI:SS.XXXXXX' where X represent the micro seconds.

But if the job gets aborted for watever reasons, the status for the timestamp field in the log is only the time in hh:mi:ss format ( not the whole timestamp)

Does this mean, the same return value of a function, would be interpreted diffently by fields having different datatypes?

Could someone help me how to handle Time in DB2,

Cheers,
Shyam

ray.wurlod · Post by **ray.wurlod** » Thu Sep 08, 2005 11:54 pm

A search of the forum will reveal that the DB2 stage expects DataStage internal format. Try @TIME by itself.

Otherwise, use the current time constant in DB2 SQL.

shyam_etl · Post by **shyam_etl** » Sun Sep 11, 2005 5:19 pm

ray.wurlod wrote:A search of the forum will reveal that the DB2 stage expects DataStage internal format. Try @TIME by itself.

Otherwise, use the current time constant in DB2 SQL.

I forgot to mention that am using ODBC stage instead of DB2 stage. The datastage internal '@time' function wouldnt work.

Could you brief more on using the current time constant in DB2 SQL?

ray.wurlod · Post by **ray.wurlod** » Sun Sep 11, 2005 9:21 pm

In each database there are constants for current date and current time. Their names vary. I don't have the DB2 ones in my head. Names like SYSDATE or CURRENT_TIME are the kind of thing I have in mind. But I don't have enough (unpaid) time to research them for you.