Current timestamp in DB2 Stage

[DaleK]

hello all,

I have a job that is pulling data from Oracle and moving it to DB2.
the job has a Oracle 9i plugin ---> Transformer --> db2 plugin.

One column is the update timestamp column. I would like to have the DB2 timestamp populated in this column. I can do an update via spufi using the "current timestamp" variable with no problems. However, when I try populating the column in the transformer with Current timestamp i get an error.

When the column is defined as a timestamp I get the following error "Attempt to convert String value "CURRENT TIMESTAMP" to Timestamp type unsuccessful"

when I change the sql type to Char I get the following error.
"-181 THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE"

can this me done?

[ogmios]

The easiest solution if you want to use the "current timestamp" on the DB2 side is to:
* not include the timestamp column in datastage at all
* for inserts: have your DBA make the timestamp column: "generated by default as current timestamp" in the DDL (or generated always whatever you prefer)
* for updates: make a database "trigger on update" that sets the column to current timestamp

If you want to use the current timestamp in the transformer you have to use @TIME or DateCurrentDateTime() or a similar transform, and you may have to coach its output in the right format. A transformer does not use SQL.

Ogmios

[ray.wurlod]

Another approach is to use user-defined SQL and specify the constant CURRENT_TIMESTAMP (assuming that's its name in DB2) in the appropriate column position in the INSERT statement.

[DaleK]

Thanks ogmios and ray,

The database trigger is what we want as developers, but our DBAs don't allow triggers because of the overhead.

They have excepted the solution of me passing the DataStage servers timestamp when the row is generated as a solution. I created a routine that takes the current server time and puts in the DB2 format. However, how do I get the micorseconds. While I don't think this is a big deal for this project, I would like to know that.

Once again thanks for your help. I'm going to try your solutions just to test out the different options for future challenges.

Have great day
Dale

[ogmios]

When generating it from DataStage you don't get the microseconds. You should be lucky to be able to get seconds as smallest measurement in DataStage.

Ogmios

[ray.wurlod]

Can't you do someuser-defined SQL like:

Code: Select all

INSERT INTO table (c1, c2, c3, c4, c5)
VALUES ('?', '?', CURRENT_TIMESTAMP, '?', ?);

Your job only provides values for c1, c2, c4 and c5.