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?
Current timestamp in DB2 Stage
Moderators: chulett, rschirm, roy
Re: Current timestamp in DB2 Stage
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
* 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.
![Laughing :lol:](./images/smilies/icon_lol.gif)
Ogmios
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Can't you do someuser-defined SQL like:
Your job only provides values for c1, c2, c4 and c5.
Code: Select all
INSERT INTO table (c1, c2, c3, c4, c5)
VALUES ('?', '?', CURRENT_TIMESTAMP, '?', ?);
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.