DSXchange

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.

How is it "not serving the purpose"?

I would use Advanced Find in the Designer client. Created/modified filters are available in this tool.

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.

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?

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.

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.

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.

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.

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.