[:)][:)][:)][:)][:)]
Hi my friends.
In the first topic I didn't wanted to start a discussion about if DataStage engine is good or bad, or any other thing.
I just want to access the DS engine tables, in order to build a process that lets me clear the director screen. In the director screen, I hace a lot of jobs run, with different invocation ids.
I need to analyze each one, and the jobs that are finished fine, I need to delete it from the system. But the jobs aborted or with any problem, I need to analyze what happened.
For example, here is a director screen:
MasterJob.p01 Finished
MasterJob.p02 Finished
MasterJob.p03 Finished (see log)
MasterJob.p04 Aborted
MasterJob.p05 Finished
MasterJob.p06 Aborted
MasterJob.p07 Finished
....
With these examples, I need to delete from the system MasterJob.p01, MasterJob.p02, MasterJob.p03, MasterJob.p05, MasterJob.p07. Jobs MasterJob.p04 and MasterJob.p06 must be analyzed to see what the problem was.
The solution of re-compiling a jobs does not fit here, because it will delete all occurrences of a job, and I must not to lose the history if there is any aborted job.
This is the main reason to access the DS engine. As Ray said, with the Reporting Assistant I can extract a lot of information about the jobs.
By the way: in the Reporting Assistant, where is the column number column, within a structure ? I mean, how do you know the order of columns within a structure ?
Thanks to all.
Guillermo P. Barsky
Buenos Aires - Argentina
DataStage, Universe and TCL (2)
Moderators: chulett, rschirm, roy
Guillermo
I gave you the code to delete jobs last week. Here is the link:
http://www.tools4datastage.com/forum/to ... C_ID=84977
If you have a problem with this code then post it.
As far as Reporting Assistant the order is the order in the Designer. Most of the time the column name is more important. The order is very important in SQL statements because they only put 80 characters in the field at a time which is adjustable but the whole SQL statement will not fit in one record. This is in DSProperties in both cases.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
I gave you the code to delete jobs last week. Here is the link:
http://www.tools4datastage.com/forum/to ... C_ID=84977
If you have a problem with this code then post it.
As far as Reporting Assistant the order is the order in the Designer. Most of the time the column name is more important. The order is very important in SQL statements because they only put 80 characters in the field at a time which is adjustable but the whole SQL statement will not fit in one record. This is in DSProperties in both cases.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Guillermo
I do not have PX so you will have to debug this. I would imagine that MasterJob.p02 has a record in DS_JOBS. If it does then my code will delete the DS_JOBS record and then all the hash files associated with this job. The way I understand it works is the MasterJob is copied to MasterJob.p02 and all the other dependent jobs. Let me know if that is true. If not then it has to be stored in a similar fashion in a related file to DS_JOBS. I would also imagine that job type in field 40 of DS_JOBOBJECTS has something that specifies that this is not the original job but a copy. Based on this field I could clean up all these jobs.
Why don't you just hire me. I could remotely fix this in a few minutes. Cost less than you learning all this stuff. You can read my code later and figure it out. There are lots of people which should be doing this. If I could do in 2 hours which may take you 2 days to do then unless I cost 8 times more than you cost then it is a good deal to hire me or Ray or Ken to remotely fix some of these issues. Setup a VPN and send us the VPN client and we are good to go. Sometimes all we need is an export file.
You are getting a lot of free advice. You need to fill in the gaps or hire one of us.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
I do not have PX so you will have to debug this. I would imagine that MasterJob.p02 has a record in DS_JOBS. If it does then my code will delete the DS_JOBS record and then all the hash files associated with this job. The way I understand it works is the MasterJob is copied to MasterJob.p02 and all the other dependent jobs. Let me know if that is true. If not then it has to be stored in a similar fashion in a related file to DS_JOBS. I would also imagine that job type in field 40 of DS_JOBOBJECTS has something that specifies that this is not the original job but a copy. Based on this field I could clean up all these jobs.
Why don't you just hire me. I could remotely fix this in a few minutes. Cost less than you learning all this stuff. You can read my code later and figure it out. There are lots of people which should be doing this. If I could do in 2 hours which may take you 2 days to do then unless I cost 8 times more than you cost then it is a good deal to hire me or Ray or Ken to remotely fix some of these issues. Setup a VPN and send us the VPN client and we are good to go. Sometimes all we need is an export file.
You are getting a lot of free advice. You need to fill in the gaps or hire one of us.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
[:)][:)][:)][:)][:)]
Kim:
Thanks for your explanation.
Listing the DS_JOBS table, I cannot see the invocation id in the Jobname column.
For making the deal, I need to have the estimations (in USD). You can send this to gbarsky@osde.com.ar.
Besides, with this work I want all the structure of the tables of the DataStage engine.
If this job is possible, I will be happy to do it.
Thanks, and I wait for your answer.
[:)][:)][:)][:)][:)]
Guillermo P. Barsky
Buenos Aires - Argentina
Kim:
Thanks for your explanation.
Listing the DS_JOBS table, I cannot see the invocation id in the Jobname column.
For making the deal, I need to have the estimations (in USD). You can send this to gbarsky@osde.com.ar.
Besides, with this work I want all the structure of the tables of the DataStage engine.
If this job is possible, I will be happy to do it.
Thanks, and I wait for your answer.
[:)][:)][:)][:)][:)]
Guillermo P. Barsky
Buenos Aires - Argentina
Guillermo
Buy DwNav and I will include it but I need to know do they show up when you run this at TCL:
LIST DS_JOBS LIKE MasterJob...
If they are not in DS_JOBS then I need to do some discovery work.
My guess is they are stored in RT_STATUS, RT_CONFIG or DS_JOBOBJECTS, JROOT.
I need to find those invocation ids and where they are stored. I will be shocked if they are not store in DS_JOBS. You need to edit these records plus:
ED DS_JOBS MasterJob
They maybe stored in a field in DS_JOBS and not complete records.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Buy DwNav and I will include it but I need to know do they show up when you run this at TCL:
LIST DS_JOBS LIKE MasterJob...
If they are not in DS_JOBS then I need to do some discovery work.
My guess is they are stored in RT_STATUS, RT_CONFIG or DS_JOBOBJECTS, JROOT.
I need to find those invocation ids and where they are stored. I will be shocked if they are not store in DS_JOBS. You need to edit these records plus:
ED DS_JOBS MasterJob
They maybe stored in a field in DS_JOBS and not complete records.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This is multiple instances of server jobs, not PX.
There is nothing in the design-time information (the DS_... tables) except an indicator that the job is multi-instance capable.
The only location where the multi-instance capability has an impact in the repository is in the run-time tables (the RT_...) tables.
I have created an IsMultiInstance function (returns true/false) to test this information, however this does not solve gpbarsky's original problem. This problem will require a selective deletion from the RT_LOGnn table for the job, as events from all instances of a job are stored in that job's log; they are filtered by a WHERE clause when displayed in Director log view.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
There is nothing in the design-time information (the DS_... tables) except an indicator that the job is multi-instance capable.
The only location where the multi-instance capability has an impact in the repository is in the run-time tables (the RT_...) tables.
I have created an IsMultiInstance function (returns true/false) to test this information, however this does not solve gpbarsky's original problem. This problem will require a selective deletion from the RT_LOGnn table for the job, as events from all instances of a job are stored in that job's log; they are filtered by a WHERE clause when displayed in Director log view.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Ray
How does it know what invocation ids are out there? Where is that stored? It would have to store them somewhere. Are you saying they all use the same RT_LOG file? Does that mean they share RT_CONFIG and RT_STATUS as well?
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
How does it know what invocation ids are out there? Where is that stored? It would have to store them somewhere. Are you saying they all use the same RT_LOG file? Does that mean they share RT_CONFIG and RT_STATUS as well?
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
-
vmcburney
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
All log messages go against the main job, when you run an instance Director creates a view of that log which just has the messages for that instance. As Ray says this isn't a standalone log. You'll notice when you recompile the main job all the instances in Director disappear. When you then rerun an instance it reappears with all its previous logs intact. There must be a field on each log message that indicates which instances it belongs to.
Vincent McBurney
Data Integration Services
www.intramatix.com
Vincent McBurney
Data Integration Services
www.intramatix.com
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In answer to Kim's question, there are no invocation IDs "out there" until run time (or schedule time), when the invocation ID is provided.
Yes, there is a field in one of the RT_CONFIGnn records that notes the invocation ID, and in the log event record as well. Were I to reveal details (which I have determined by hacking), Ascential legal would become upset.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Yes, there is a field in one of the RT_CONFIGnn records that notes the invocation ID, and in the log event record as well. Were I to reveal details (which I have determined by hacking), Ascential legal would become upset.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Hi All,
I was thinking, if the invocation id is in the log entries, why not extract which invocation id's failed and extract the logs for those instances, after extraction you can analize them to death[:o)].
also you can do whatever you want to the job now, even compile it since you saved the info you wanted [:)]
I Hope This Helps (thinking.. hmm a new acronim IHTH???),
Roy R.
I was thinking, if the invocation id is in the log entries, why not extract which invocation id's failed and extract the logs for those instances, after extraction you can analize them to death[:o)].
also you can do whatever you want to the job now, even compile it since you saved the info you wanted [:)]
I Hope This Helps (thinking.. hmm a new acronim IHTH???),
Roy R.
Guillermo
If you edit the RT_CONFIGnn file associated with your MasterJob then you should see the invocation ids. It is probably in the record with the same name as the job name. This maybe as easy as deleting the id in this field. Somehow they share the same log file.
These fields are probably multivalued which is difficult to explain. All files in Universe are treated the same. They all have a key which is called item id. A record is called an item. A record is stored as one long string. Each field is separated by field marks (@FM) or attribute marks (@AM) char(254). A field is also called an attribute. The fields are numbered 1,2,3 and so on. Field 3 has 2 @FM in front of it. Rec is the same as field(Rec, @FM, 2). Think of Rec as similar to Rec(3). The first is a dynamic array. The second is a dimensioned array. A multivalued field can contain more than one value or an array. To access field 3 second multivalue then Rec is how to read or replace its value.
Lets say field 10 has the ids. You would need to loop through each invocation id and get its status and delete it from the field if its status is ok. Here is the code. If the invocation id does not have the job name included but needs to be included.
$INCLUDE DSINCLUDE JOBCONTROL.H
JobName = "MasterJob"
open "DS_JOBS" to DsJobs else stop
read JobRec from DsJobs, JobName else stop
JobNo = JobRec
RtConfigFile = "RT_CONFIG" : JobNo
open RtConfigFile to RtConfig else stop
read RtConfigRec from RtConfig, JobName else stop
NoOfIds = dcount(RtConfigRec, @VM)
for i = NoOfIds to 1 step -1
id = RtConfigRec
InvocationJobName = JobName : "." : id
RunHandle = DSAttachJob(JobName, DSJ.ERRNONE)
JStat = DSGetJobInfo(RunHandle, DSJ.JOBSTATUS)
begin case
case JStat = 3 ; * Aborted
del RtConfigRec
case JStat = 96 ; * Aborted
del RtConfigRec
case JStat = 97; * Stopped
del RtConfigRec
end case
JStat = DSDetachJob(RunHandle)
next i
This should get you close. I cannot compile or test this you will have to. I do not have access to PX.
To edit all the records in RT_CONFIGnn. If nn for MasterJob is 33 then:
ED RT_CONFIG33 *
Help in the editor is ? or HELP ED before you get into the editor. "EX" will exit or "X" will exit all.
Send me the final code. I think I earned it.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
If you edit the RT_CONFIGnn file associated with your MasterJob then you should see the invocation ids. It is probably in the record with the same name as the job name. This maybe as easy as deleting the id in this field. Somehow they share the same log file.
These fields are probably multivalued which is difficult to explain. All files in Universe are treated the same. They all have a key which is called item id. A record is called an item. A record is stored as one long string. Each field is separated by field marks (@FM) or attribute marks (@AM) char(254). A field is also called an attribute. The fields are numbered 1,2,3 and so on. Field 3 has 2 @FM in front of it. Rec is the same as field(Rec, @FM, 2). Think of Rec as similar to Rec(3). The first is a dynamic array. The second is a dimensioned array. A multivalued field can contain more than one value or an array. To access field 3 second multivalue then Rec is how to read or replace its value.
Lets say field 10 has the ids. You would need to loop through each invocation id and get its status and delete it from the field if its status is ok. Here is the code. If the invocation id does not have the job name included but needs to be included.
$INCLUDE DSINCLUDE JOBCONTROL.H
JobName = "MasterJob"
open "DS_JOBS" to DsJobs else stop
read JobRec from DsJobs, JobName else stop
JobNo = JobRec
RtConfigFile = "RT_CONFIG" : JobNo
open RtConfigFile to RtConfig else stop
read RtConfigRec from RtConfig, JobName else stop
NoOfIds = dcount(RtConfigRec, @VM)
for i = NoOfIds to 1 step -1
id = RtConfigRec
InvocationJobName = JobName : "." : id
RunHandle = DSAttachJob(JobName, DSJ.ERRNONE)
JStat = DSGetJobInfo(RunHandle, DSJ.JOBSTATUS)
begin case
case JStat = 3 ; * Aborted
del RtConfigRec
case JStat = 96 ; * Aborted
del RtConfigRec
case JStat = 97; * Stopped
del RtConfigRec
end case
JStat = DSDetachJob(RunHandle)
next i
This should get you close. I cannot compile or test this you will have to. I do not have access to PX.
To edit all the records in RT_CONFIGnn. If nn for MasterJob is 33 then:
ED RT_CONFIG33 *
Help in the editor is ? or HELP ED before you get into the editor. "EX" will exit or "X" will exit all.
Send me the final code. I think I earned it.
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Guillermo
I did it wrong you want to del all the other statuses not the aborted ones. So change the case to this
begin case
case aborted1
null
case aborted2
null
case stopped
null
case @true
del Rec
end case
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
I did it wrong you want to del all the other statuses not the aborted ones. So change the case to this
begin case
case aborted1
null
case aborted2
null
case stopped
null
case @true
del Rec
end case
Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Kim:
Here is my code. JobName is a parameter to the job, and its supplied value for the run is "MasterJob".
---------------------------------------------------------------------
$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
*
OPEN "DS_JOBS" TO DsJobs ELSE STOP;
READ JobRec FROM DsJobs, JobName ELSE STOP;
*
CALL DSLogInfo("JobRec-":JobRec:".","DATA");
*
JobNo = JobRec;
RtConfigFile = "RT_CONFIG" : JobNo;
CALL DSLogInfo("RtConfigFile-":RtConfigFile:".","DATA");
*
OPEN RtConfigFile TO RtConfig ELSE CALL DSLogFatal("Error al abrir. Status-":Status(),"MSG");
READ RtConfigRec FROM RtConfig, JobName ELSE CALL DSLogFatal("Error al leer. Status-":Status(),"MSG");
NoOfIds = dcount(RtConfigRec, @VM);
CALL DSLogInfo("NoOfIds-":NoOfIds:".","DATA");
FOR i = NoOfIds TO 1 STEP -1
CALL DSLogInfo("Entro 1.","DATA");
id = RtConfigRec
CALL DSLogInfo("id ":i:"-":id,"DATA");
InvocationJobName = JobName : "." : id
CALL DSLogInfo("INVOCATION JOB NAME===>":InvocationJobName:".","DATA");
**RunHandle = DSAttachJob(JobName, DSJ.ERRNONE)
**JStat = DSGetJobInfo(RunHandle, DSJ.JOBSTATUS)
**begin case
** case JStat = 3 ; * Aborted
** del RtConfigRec
** case JStat = 96 ; * Aborted
** del RtConfigRec
** case JStat = 97; * Stopped
** del RtConfigRec
**end case
**JStat = DSDetachJob(RunHandle)
NEXT i
---------------------------------------------------------------------
Previous to delete records, I am displaying them.
Let me tell you that the value of NoOfIds is 1, and the value of RtConfigRec is "V0". And the InvocationJobName is "MasterJob.V0".
Am I doing something wrong ? What is "V0" ? I suppossed that I would find a list in this field.
Thanks in advance.
Guillermo P. Barsky
Buenos Aires - Argentina
Here is my code. JobName is a parameter to the job, and its supplied value for the run is "MasterJob".
---------------------------------------------------------------------
$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
*
OPEN "DS_JOBS" TO DsJobs ELSE STOP;
READ JobRec FROM DsJobs, JobName ELSE STOP;
*
CALL DSLogInfo("JobRec-":JobRec:".","DATA");
*
JobNo = JobRec;
RtConfigFile = "RT_CONFIG" : JobNo;
CALL DSLogInfo("RtConfigFile-":RtConfigFile:".","DATA");
*
OPEN RtConfigFile TO RtConfig ELSE CALL DSLogFatal("Error al abrir. Status-":Status(),"MSG");
READ RtConfigRec FROM RtConfig, JobName ELSE CALL DSLogFatal("Error al leer. Status-":Status(),"MSG");
NoOfIds = dcount(RtConfigRec, @VM);
CALL DSLogInfo("NoOfIds-":NoOfIds:".","DATA");
FOR i = NoOfIds TO 1 STEP -1
CALL DSLogInfo("Entro 1.","DATA");
id = RtConfigRec
CALL DSLogInfo("id ":i:"-":id,"DATA");
InvocationJobName = JobName : "." : id
CALL DSLogInfo("INVOCATION JOB NAME===>":InvocationJobName:".","DATA");
**RunHandle = DSAttachJob(JobName, DSJ.ERRNONE)
**JStat = DSGetJobInfo(RunHandle, DSJ.JOBSTATUS)
**begin case
** case JStat = 3 ; * Aborted
** del RtConfigRec
** case JStat = 96 ; * Aborted
** del RtConfigRec
** case JStat = 97; * Stopped
** del RtConfigRec
**end case
**JStat = DSDetachJob(RunHandle)
NEXT i
---------------------------------------------------------------------
Previous to delete records, I am displaying them.
Let me tell you that the value of NoOfIds is 1, and the value of RtConfigRec is "V0". And the InvocationJobName is "MasterJob.V0".
Am I doing something wrong ? What is "V0" ? I suppossed that I would find a list in this field.
Thanks in advance.
Guillermo P. Barsky
Buenos Aires - Argentina
Guillermo
Thanks. It looks like you did not remove the multivalues of the run ids. Is that the next step? How do you get the statuses of each run id? How do you know which ones aborted?
Kim.
Kim Duke
DsWebMon - Monitor over the web
www.Duke-Consulting.com
Thanks. It looks like you did not remove the multivalues of the run ids. Is that the next step? How do you get the statuses of each run id? How do you know which ones aborted?
Kim.
Kim Duke
DsWebMon - Monitor over the web
www.Duke-Consulting.com