Where to find job schedule info in xmeta DB?

[val]

Hi all,

I'm trying to write a report to analyze DataStage job schedule information, and need a little help understanding where and how the data are stored.

We have a bunch of DS Server jobs scheduled in Director. From time to time we change a job's schedule, and occasionally we forget to change it back. So I want to run a daily report that compares the current schedule to a previous one, in order to detect changes. We'd run it when we know the schedule is 'correct' and store the results as a baseline. Then we'd run it on a daily basis and compare the day's results to the baseline, and highlight the differences, e.g. "Job X was originally scheduled to run daily at 10PM, and it's currently scheduled to run daily at 6PM".

I'm guessing that when I schedule a job in Director, the parameters are stored somewhere in the XMETA database. But there are 781 tables in that DB -- 31 with 'scheduling' in the name -- and no FK relationships (in SQL Server, which is our platform). Can anyone help me understand how the schedule information is stored, and what tables I need to join to get at the human-readable values for job name and scheduled times?

Alternately, can/should I use a DataStage utility to dump this info? I'm not picky, and don't need to spend time writing a bunch of SQL if I can call a util with appropriate options -- but I couldn't find anything that fits the bill. Ideally, I would like to run this from the command-line, so it can be automated.

I'm guessing I'll want the report to list results by project, then by job name, then the schedule entries for that job. But I'll take whatever I can get -- any help appreciated!

Thanks,

Val

[ray.wurlod]

You are the pathfinder here - IBM has chosen not to document the structure of XMETA. Please post your findings.

[val]

heh, fair enough, back to doing it the hard way ;-) Hopefully I'll be back with some info -- send a search party if I don't return in a week or so!

[deanwalker]

You can see that information in the RT_CONFIGnnn file, or export the executable and it is visible in the DSX too.

[val]

You can see that information in the RT_CONFIGnnn file, or export the executable and it is visible in the DSX too.

Dean, Can you please explain a bit more? On my system (DS 8.0 on Windows),

Code: Select all

%INSTALL_ROOT%\IBM\InformationServer\Server\Projects\%PROJECTNAME%\RT_CONFIGnnn

are directories, not files, and contain DATA.30 and OVER.30 files. I understand these to be job-specific hashed files containing run-time job config info. Are you saying the job schedule info is contained in these files? How can I view the contents of these files? And, how can I programatically read them, looking for the schedule info? When I try to open them into a Hashed File stage in DS, DS doesn't list the RT_CONFIGnnn directories when I try to browse to the directory path.

I can see the schedule info in the DSX -- thanks, I wouldn't have thought of looking there! -- but it's very inefficient for me to pull the info from DSXs. I want to run this report daily; to get changed schedule info, I'd have to export each job to DSX every day for 100+ jobs. As far as I know, I can only do this export via Designer, not the command-line, so I can't automate it (is this correct, or is there a command-line utility that will export jobs to DSX?)

Thanks for the tips! If I can get an efficient way of reading the data from the files, I'll walk the directory and pull from there. In the meantime, I'm (slowly) trying to reverse-engineed the XMETA DB...

- Val

[ray.wurlod]

You can see that information in the RT_CONFIGnnn file, or export the executable and it is visible in the DSX too.

This is news to me. Can you please elaborate?

[val]

I can verify Dean's assertion that the info is in the exported DSX files -- if you export the executables along with the job design.

I created a dead-simple Server job in Designer, saved, compiled, ran, scheduled it in Director, and then went back to Designer and exported the job with executables to a DSX file. The resulting DSX has a section like this:

Code: Select all

BEGIN DSEXECJOB
   Identifier "test"
   DateModified "1899-12-30"
   TimeModified "00.00.01"
   BEGIN DSRECORD
      Identifier "JOBPARAMINSTS"
      F1 "N*"
      F2 "N*"
   END DSRECORD
   BEGIN DSRECORD
      Identifier "JOBSCHEDINSTS"
      F1 "20:00\VEvery M&T&W&Th&F&S&Su\V97"
      F2 "21:00\VEvery M&T&W&Th&F&S&Su\V97"
   END DSRECORD

The last few lines corrspond to the schedule entries I made to this job thru Director.

As I mentioned to Dean, this is interesting but not efficient for my purpose -- I'd prefer not to (even programatically) export every job from every project every night and then parse the resulting DSX files to find this info; that seems like overkill. And, of course, if schedule info is getting exported into the DSX, it's coming from some other storage location -- I want to find that location.

I'm more curious about if and how the data are stored in the RT_CONFIGnnn files, and if there's an easy and reliable way to read and parse those files. I can't find any info on the structure of DATA.30, nor c an I get DataStage to see them from a Hashed File stage.

[ray.wurlod]

You will be able to do that once Dean advises whereabouts in the RT_CONFIGnnn record the schedule information resides.

[deanwalker]

The information that is in the DSX of an executable job for scheduling is in the record "JOBSCHEDINSTS" of RT_CONFIGnnn.
And the parameters are in JOBPARAMINSTS.
The records are not present if the job has not been scheduled.

I can see these records using "CT RT_CONFIGnnn JOBSCHEDINSTS" when logged into the project from a DS Engine shell, and I can write a UV Basic program to show it too, but it has been a while since I wrote any DataStage
If I point my HashedFile at RT_CONFIGnnn, give it 2 columns, and just select records "LIKE JOB...", I can see the information when I ViewData.
HTH

[ray.wurlod]

So you can use a Hashed File stage (or UniVerse stage) if you know the job number. Point the stage at RT_CONFIGnnn (where nnn is the job number), and set up a table definition that supports multi-valued fields (so that you get the Position, Depth and Association columns in the Columns grid). In the Selection tab specify

Code: Select all

@ID = 'JOBSCHEDINSTS'

We now await Dean to inform us of the structure of the JOBSCHEDINSTS record so we can design the job to parse it.

[val]

Well, I couldn't find much scheduling info in the XMETA DB. I was able to find job names and their corresponding project and path, but if there's any other job schedule info in the other tables, it's encoded in hex. But I don't think it's there at all; I ran a DB trace while scheduling and unscheduling a job in Director, and the only DB activity I saw retrievesd the job name, path and project (I assume for display in Director's GUI), but I didn't' see any schedule info written or read. So that's a bust.

I also didn't get anywhere trying to read the RT_CONFIGnnn files into DataStage; I haven't worked with the hashed file stages much, and was stuck pretty quickly because I didn't know what details to provide the wizard.

But, thanks very much to Dean for the hints, I think I can get what I need via the DataStage shell. I'm in the process of writing a bash script that will

Code: Select all

navigate to the DS Server/Projects directory,
for each project directory, 
    list Server jobs (all our jobs have Server job wrappers; they're the ones that get scheduled):
    SELECT JOBNO, FROM DS_JOBS WHERE JOBTYPEIND = '2' SUPPRESS COLUMN HEADING NOPAGE COUNT.SUP;
    
    for each job number $JOBNO in the result,
        query the corresponding RT_CONFIGnnn file, where nnn = $JOBNO, and dump schedule info:
        CT RT_CONFIGnnn JOBSCHEDINSTS

I'll then sort and store this info, and compare it to the previous run, highlighting changes. First try will be just to diff the output against the previous output. If my audience (DS admins) doesn't like the diff output, I'll work on something prettier. Once I have the data from the queries above, the rest is easy.

Thanks to both of you for the advice!

One last question: Is there any way to pass to dssh a script file? I'm currently controlling dssh from bash by issuing

Code: Select all

dssh "SELECT JOBNO ... ;"
dssh "CT RT_CONFIG97 ...;"

I'd rather put all my dssh commands into a file, and then pass that file as a param to dssh. But I can't find any help on dssh itself, what options it takes, and particularly how to pass it a file. I suppose I could redirect its input like

Code: Select all

dssh < myscriptfile

where myscriptfile contains e.g.

Code: Select all

"SELECT JOBNO ... ;CT RT_CONFIG97 ...;"

Can you point me to docs or discussion on how to control dssh from bash, or feed it a script file? Thanks!

- Val

[deanwalker]

There is no scheduling info in XMETA, this info is in the executable job (RT_CONFIGnn), so is in the DS Server Project.

I don't think you can drive dssh from a file, but the same logic in a UV BASIC program would be reasonably simple, or use the dsjob api to get the lists of jobs and scheduling info.

[ray.wurlod]

There IS scheduling information somewhere in XMETA. I infer this because many products (not just DataStage) can use "the IFS schedule in IBM Information Server".

[val]

The information that is in the DSX of an executable job for scheduling is in the record "JOBSCHEDINSTS" of RT_CONFIGnnn.
And the parameters are in JOBPARAMINSTS.
The records are not present if the job has not been scheduled.

Dean, I wonder if you can share any more insight. After some off-and-on hacking, I'm able to get the schedule info for each job in each project on my server. But, after developing for a while on our dev server, I finally got suspicious about why I was getting back schedule info for so many jobs. I'd query all the projects, and get back a bunch of job schedule instances saying jobs were set to run every night, or once a week, etc.

In our dev environment, we generally run jobs directly, and only schedule them from time to time to run some tests. So I checked in Director, and the jobs in question were not currently scheduled! I'd query job # 40, and get

Code: Select all

     JOBSCHEDINSTS
0001 10:52?Today?40

But in Director, the job is not scheduled. I scheduled the job in Director, setting it to run tomorrow at 5 PM, and ran the DSSH query again. Now I get

Code: Select all

     JOBSCHEDINSTS
0001 17:00?Next 1?40

I unscheduled the job, re-ran the query, and still get

Code: Select all

     JOBSCHEDINSTS
0001 17:00?Next 1?40

So, it looks like the data in RTCONFIGnn is most-recently-configured schedule info, but the job is not necessarily currently scheduled.

Do you know where there's a flag that indicates if the schedule is on or off? Without that, all this is useless -- I don't just need to know when a job is meant to run, but also IF it is meant to run!

Thanks!

[val]

OK, I finally got it. Thanks to a tip from IBM support, I found:

1) the job schedule information in RT_CONFIGnn files is not reliable, as explained above -- you can't tell whether a schedule is active or not, and

2) on both Unix and Windows, when you schedule a job, DataStage Director in turn creates schedule entries with the OS-level job scheduler. On Unix, that's at and cron:

at -lv will show any jobs that are scheduled to run once, crontab -l will show jobs that are scheduled to repeat

The output will look like:

Code: Select all

$ at -lv 
======================== 
dsadm.1214946001.a      Tue Jul  1 17:00:01 EDT 2008 
======================== 
/opt/IBM/InformationServer/Server/Projects/support_us/dsr_sched.sh /opt/IBM/InformationServer/Server/Projects/support_us /opt/IBM/InformationServer/Server/DSEngine sqlcode_test 0/50/1/0/0 1 >/dev/null 2>&1 

$ crontab -l 
00 18 * * 2 /opt/IBM/InformationServer/Server/Projects/support_us/dsr_sched.sh /opt/IBM/InformationServer/Server/Projects/support_us /opt/IBM/InformationServer/Server/DSEngine testOracle 0/50/1/0/0 1 >/dev/null 2>&1 #DSjobDS DSJ.support_us.97.1 

On Windows, the jobs become entries in the Scheduled Tasks control panel. When you look at them in the GUI, you can't tell much -- they have entries named At118 and such, and the Run command shows the beginning of the command but you can't see the end of it, where the DataStage job name would be. But you can get a dump from the command line:

Code: Select all

c:\> schtasks /query /v /fo list
HostName:                             NBCDPDSTG10
TaskName:                             At322
Next Run Time:                        8:00:00 AM, 7/2/2008
Status:
Logon Mode:                           Interactive/Background
Last Run Time:                        8:00:00 AM, 7/1/2008
Last Result:                          0
Creator:                              SYSTEM
Schedule:                             At 8:00 AM every Mon, Tue, Wed, Thu, Fri, Sat, Sun of every week, starting 1/11/2008
Task To Run:                          vmdsr_sched.exe D:\IBM\InformationServer\Server\Projects\SAP_I
nterface_HR SEQ_I_HR_0031b_GA_TIH 0/50/1/0/0 1
Start In:                             N/A
Comment:                              Created by NetScheduleJobAdd.
Scheduled Task State:                 Enabled
Scheduled Type:                       Weekly
Start Time:                           8:00:00 AM
Start Date:                           1/11/2008
End Date:                             N/A
Days:                                 SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAY
Months:                               N/A
Run As User:                          Could not be retrieved from the task scheduler database
Delete Task If Not Rescheduled:       Enabled
Stop Task If Runs X Hours and X Mins: 72:0
Repeat: Every:                        Disabled
Repeat: Until: Time:                  Disabled
Repeat: Until: Duration:              Disabled
Repeat: Stop If Still Running:        Disabled
Idle Time:                            Disabled
Power Management:                     Disabled

The arguments to the /fo (format) switch are 'table', 'list', and 'csv'. You can get info on all the options with

Code: Select all

c:\> schtasks /query /?

For once, I like the Windows utility better than the Unix equivalent -- it has nice output format options, and (with the verbose switch) lots of info, including the next run time, last run time and last result, so you can tell if a job actually ran when it was supposed to.

Thanks to all for the advice. Now to rewrite my script....