Page 1 of 1
Best way to populate jobstart datetime to Oracle -DS Macro
Posted: Fri Jan 06, 2006 3:23 pm
by satish_valavala
I need to populate job start date_time (YYYY-MM-DD HH24:MM:SS, but not micro secs) to one of my oracle table field (of Oracle Date data type).
We may use DS macros DSJobStartDate:" ": DSJobStartTime
Or we can substring Macro DSJobStartTimestamp to pick date_time.
My question is which the best/efficient way to do this and reason for it.
I appreciate for your response
Thanks
Satish
Posted: Fri Jan 06, 2006 3:26 pm
by chulett
Create a Stage Variable.
Put Macro(s) in the Initial Value field.
Leave the Derivation of the Stage Variable blank.
Use in your output link.
Posted: Fri Jan 06, 2006 3:42 pm
by satish_valavala
Thanks Craig Hulett. I am curious to know the reson for it.
Thanks
Posted: Fri Jan 06, 2006 3:46 pm
by chulett
The date and time the job started doesn't change, so use the
Initial Value of the stage variable to capture it
once instead of on each row. The blank derivation of the stage variable means it retains its value over the course of the job. Then you just simply reference this value for each row going into Oracle.
Fast.
Posted: Fri Jan 06, 2006 4:56 pm
by ray.wurlod
Rather than the separate macros, why not use for stage variable initialization
Code: Select all
DSGetJobInfo(DSJ.ME, DSJ.JOBSTARTTIMESTAMP)
(There may even be a DSJobStartTimeStamp macro - I don't have access to DataStage right now.)
Posted: Fri Jan 06, 2006 5:02 pm
by chulett
Yes, there is a DSJobStartTimestamp macro: "date and time when the job started on the server in the form YYYY-MM-DD HH:MM:SS".