How to get the create timestamp for any job

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
pratheeksha
Participant
Posts: 6
Joined: Wed Feb 27, 2013 1:02 am

How to get the create timestamp for any job

Post by pratheeksha »

I have a requirement to get the list of all the jobs created newly / modified in 2017 in the production Datastage server.
There is an option in the operational console which gives the create date for each job. As the number of jobs are huge, I had written a datastage jobs which hits DSODB tables and gets the result.

SELECT JE.JOBNAME
, JE.PROJECTNAME
, JE.JOBID
, JR.CREATIONTIMESTAMP
FROM dsodb.JOBEXEC JE,
dsodb.JOBRUN JR
WHERE JE.JOBID = JR.JOBID
AND JE.PROJECTNAME='XXX'

But this query is not serving the purpose!
Could anyone help to find the create timestamp for the jobs?

Note : Fast track tool is not set up in the Project to use that.
Pratheeksha
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

How is it "not serving the purpose"?
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

I would use Advanced Find in the Designer client. Created/modified filters are available in this tool.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Note that creation timestamp and last modified timestamp are two different fields and also just because the last modified timestamp is newer does not necessarily mean that the job was modified. It could simply indicate that the same existing job design was imported and overwritten.
Choose a job you love, and you will never have to work a day in your life. - Confucius
pratheeksha
Participant
Posts: 6
Joined: Wed Feb 27, 2013 1:02 am

Post by pratheeksha »

chulett wrote:How is it "not serving the purpose"?
Craig,
Looks like JOBID is not the right key column for the joining condition.

JOBID SK A surrogate primary key to identify each specific executable version of a job that has been run.

In my output file, the job names are repeating multiple time though they were not modified so many times.

Could you please help fixing the query?
Pratheeksha
pratheeksha
Participant
Posts: 6
Joined: Wed Feb 27, 2013 1:02 am

Post by pratheeksha »

qt_ky wrote:Note that creation timestamp and last modified timestamp are two different fields and also just because the last modified timestamp is newer does not necessarily mean that the job was modified. It could simply indicate that the same existing job design was imported and overwritten.
I agree with you.
In my case, it is the production environment and hence no jobs can be moved to production without any modification. Hence, I need the data for the modified jobs.
Pratheeksha
pratheeksha
Participant
Posts: 6
Joined: Wed Feb 27, 2013 1:02 am

Post by pratheeksha »

ray.wurlod wrote:I would use Advanced Find in the Designer client. Created/modified filters are available in this tool. ...
Ray,

As the number of jobs are HUGE, we decided to go for re-usable solution which hardly require any manual intervention.

Any help in fixing the query would be much appreciated.
Pratheeksha
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I'm not familiar with that database, it wasn't available when I was doing DataStage work. If you are getting duplicates, perhaps you can simply do a group by on the job name and take the minimum timestamp? Sounds like it may not be the creation time but rather the first time it was run... perhaps that would be close enough. I'll have to defer to others on how to do this for reals, however, things like what table(s) you should actually be using. :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Perhaps, with regards to Ray's suggestion, you could coordinate something with your DBA to trace the process on the database side to capture the generated SQL the Advanced Find is running. Then tailor it to your needs.
-craig

"You can never have too many knives" -- Logan Nine Fingers
YaleM4208
Participant
Posts: 11
Joined: Tue Dec 09, 2014 2:44 pm

Post by YaleM4208 »

See if this works. We use it to audit developer activity in our environments. It's tailored to DB2 LUW and v11.3. The table name was similar in previous versions.

Code: Select all

SELECT 
  DSNAMESPACE_XMETA as DSProject, 
  CATEGORY_XMETA as DSFolder, 
  NAME_XMETA as DSJobName, 
  XMETA_CREATED_BY_USER_XMETA,
  (TIMESTAMP('01/01/1970', '00:00:00') + (XMETA_CREATION_TIMESTAMP_XMETA / 1000) SECONDS) - 8 HOURS AS CREATION_TIMESTAMP_XMETA,
  XMETA_MODIFIED_BY_USER_XMETA,
  (TIMESTAMP('01/01/1970', '00:00:00') + (XMETA_MODIFICATION_TIMESTAMP_XMETA / 1000) SECONDS) - 8 HOURS AS MODIFICATION_TIMESTAMP_XMETA,
  SHORTDESCRIPTION_XMETA,
  JOBTYPE_XMETA
FROM XMETA.DATASTAGEX_DSJOBDEF
Post Reply