How to Acquire Last Run Date of a job (using dssh)

[csuttl]

Hello All,

At my AIX command prompt, I would like to execute a dssh command passing it a SQL statement that will retrieve several things including the last run date of each job and the user who ran the job. I don't know which table to query for the "last ran" info...

So basically I would like a query similar to this that includes the last ran date and user in it:
dssh "SELECT INSTANCE, MODIFIER, DTM, DTC FROM DS_AUDIT"

My ultimate goal is to clean up our repository of jobs. I want to identify jobs that haven't been used in the past x days. Then I want to show that list to the users in a report and let them manually choose what of their own stuff to clean up. We're talking about 100+ jobs and I really don't want to go through it manually (ie, the gui's). For reasons beyond my control, we work on a monthly project schedule. Each month we create a new project and export jobs from old proj and import them into the new one. Eventually old projects roll off and are deleted. Many users create test jobs that get caught up in this import/export happy funball. I want to run a comb through it and pick out the jobs that aren't used so our export files will be smaller and the production environment will be cleaner. Hopefully one day, we'll get development out of production but that is another story also beyond my control....

Any help on dssh commands to generate a report like this would be greatly appreciated. Thanks, Chris

[ray.wurlod]

Welcome aboard. :D

DS_AUDIT does not keep the last run date or, indeed, any run-time statistics. DS_AUDIT records changes to design-time objects.

Indeed, dssh is not the command you require.

You can either interrogate each job separately using dsjob -jobinfo or you can inspect the date/time modified of one of the Repository objects that are modified at run time, such as RT_LOGnnn or RT_STATUSnnn. For example:

Code: Select all

find $PROJDIR -name 'RT_STATUS*' -atime +30 -print

You could use dssh to interrogate each separate RT_STATUSnnn or RT_LOGnnn table, but this would involve using undocumented structures that are changing in the next version (assuming that it didn't actually come out yesterday, as scheduled!).

[csuttl]

Thank you Ray,

Yes, I had considered those options but I wasn't confident that they were the best way. Since you mention them as well, I must be on the right track there. The dsjob option would definitely work, I was just looking for one nice query joining multiple tables. But I'm left with a question or two - In the Director when I see a date and time for "Last Ran", from where is that info being pulled? I figured that somewhere in the Director code it was just querying some table (other than DS_AUDIT of course). When you say that I could use dssh to interrogate RT_LOGnnn and RT_STATUSnnn, what would I query for? I had examined them but not seen any "last ran" info. And if I went in that direction, how would I know which RT_STATUSnnn to query for which job? I don't mean to keep pressing for a SQL solution but I don't see us changing versions for a while, plus I just want to understand how this works! :D

Thanks again,
Chris

[ray.wurlod]

The Last Ran timestamp in Director comes from RT_STATUSnnn table, from the job record therein. Here, nnn is the job number mapped from DS_JOBS, but you can not do that (mapping to a table name) in SQL.

This is what Director has to do, but it does so programmatically, looping through the job names in the currently selected category.

You could also acquire Last Ran from the log table (RT_LOGnnn) by searching backwards from the most recent entry until you found an entry whose text included the word "Finished" or "Aborted". But you still need to map the job number from the job name.

Code: Select all

SELECT TIMESTAMP FROM RT_LOGnnn WHERE (FULL.TEXT LIKE '%Finished%' OR FULL.TEXT LIKE '%Aborted%') AND @ID = ( SELECT MAX(@ID) FROM RT_LOGnnn WHERE (FULL.TEXT LIKE '%Finished%' OR FULL.TEXT LIKE '%Aborted%') )

[csuttl]

Many Thanks Ray! I really appreciate the help. I will mark this topic as resolved.

Thanks,
Chris

[Abhijeet1980]

Well you might need something of this sort.

I used Shell Programming in order to find out the Job Paramters passed.
Hope this might be of any help to you.

uvsh "SELECT FULL.TEXT FMT '100T' FROM RT_LOG$JOB_NO WHERE FULL.TEXT LIKE 'Starting%' AND TIMESTAMP = (SELECT MAX(TIMESTAMP) FROM RT_LOG$JOB_NO WHERE FULL.TEXT LIKE 'Starting%');" > RT_LOG.txt

[ray.wurlod]

Surely dsjob -lognewest and dsjob -logdetail would have been easier!