RT_STATUS structure for deleting invocation ids
Moderators: chulett, rschirm, roy
RT_STATUS structure for deleting invocation ids
I have been challenged with the task of writing a routine to clear out the invocation entries from RT_STATUS and RT_LOG for any entry older than a specific date.
1) This is fine for RT_LOG entries as there is a timestamp but how can I do this for RT_STATUS
2) I would lurrvve to get the structure for all the tables. How can I do this ?
TIA
1) This is fine for RT_LOG entries as there is a timestamp but how can I do this for RT_STATUS
2) I would lurrvve to get the structure for all the tables. How can I do this ?
TIA
Colin Larcombe
-------------------
Certified IBM Infosphere Datastage Developer
-------------------
Certified IBM Infosphere Datastage Developer
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In the RT_STATUSnnn table the invocation ID is part of the key.
So much for identifying the records, what are they? There are records for jobs, active stages and resources - you have to make sure that you get them all. Start with investigating the record type.
will get you started.
Code: Select all
SELECT @ID FMT '60L' FROM RT_STATUSnnn;
Code: Select all
SELECT @ID FMT '46L', F1, F2 FROM RT_STATUSnnn USING DICT VOC;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
The plot thickens....
The customer wants to ensure that only the instances pertaining to the log purge settings are removed. i.e. I remove only the instances RT_STATUS older than 5 days. RT_LOG entries have already been removed using the Auto Purge
Using Ray's modified code I have generated these results from RT_STATUS
SELECT @ID FMT '46L', F1,F5 FROM RT_STATUS27 USING DICT VOC WHERE F1 ='INVOCATION'
Which gives me
The customer wants to ensure that only the instances pertaining to the log purge settings are removed. i.e. I remove only the instances RT_STATUS older than 5 days. RT_LOG entries have already been removed using the Auto Purge
Using Ray's modified code I have generated these results from RT_STATUS
SELECT @ID FMT '46L', F1,F5 FROM RT_STATUS27 USING DICT VOC WHERE F1 ='INVOCATION'
Which gives me
- VOC GFMEntetereal.011044
F1 INVOCATION
F5 DSD.RUN_59536_13921
Colin Larcombe
-------------------
Certified IBM Infosphere Datastage Developer
-------------------
Certified IBM Infosphere Datastage Developer
Colin,
the purge settings are in the log file in the record //PURGE.SETTINGS. You would need to read this record, decide if the log's settings are for {n} days or {n} runs and then go through all of the RT_LOG files records and remove only those that are applicable. I assume that this is a bit more complex that you had originally hoped. I just finished writing some code that does this for all log files on a system, on the DEVelopment server it removed 16Gb of extraneous log information; so it was worth the effort. I can't post the code as it is no longer mine, though.
the purge settings are in the log file in the record //PURGE.SETTINGS. You would need to read this record, decide if the log's settings are for {n} days or {n} runs and then go through all of the RT_LOG files records and remove only those that are applicable. I assume that this is a bit more complex that you had originally hoped. I just finished writing some code that does this for all log files on a system, on the DEVelopment server it removed 16Gb of extraneous log information; so it was worth the effort. I can't post the code as it is no longer mine, though.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Arnd,
Thanks for the response.
I am not so worried about picking up the purge settings as the actual logs have been purged.
What I need is the date associated with the instance/invocation. It is shown in Director but I have no idea which date this is in the Universe tables.
Thanks for the response.
I am not so worried about picking up the purge settings as the actual logs have been purged.
What I need is the date associated with the instance/invocation. It is shown in Director but I have no idea which date this is in the Universe tables.
Colin Larcombe
-------------------
Certified IBM Infosphere Datastage Developer
-------------------
Certified IBM Infosphere Datastage Developer
It would be in the RT_STATUS file, where the key contains the instance name and the start date is in column 3.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
I tried the SQL
SELECT @ID FMT '46L', F1, F2,F3,F4,F5,F7 FROM RT_STATUS27 USING DICT VOC WHERE F1 ='INVOCATION'
But F3 gives me 1
It is certainly true that the 3rd field in RT_LOG is the timestamp.
If I am wrong could you please give me the SQL required
Thanks
SELECT @ID FMT '46L', F1, F2,F3,F4,F5,F7 FROM RT_STATUS27 USING DICT VOC WHERE F1 ='INVOCATION'
But F3 gives me 1
It is certainly true that the 3rd field in RT_LOG is the timestamp.
If I am wrong could you please give me the SQL required
Thanks
Colin Larcombe
-------------------
Certified IBM Infosphere Datastage Developer
-------------------
Certified IBM Infosphere Datastage Developer
F1 needs to be "JOB" and the invocation ID is part of the record ID.
Code: Select all
SELECT F3 FROM RT_STATUS7 USING DICT VOC WHERE F1 = 'JOB' AND @ID LIKE '%instance%';
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Yes, you can do that. But that isn't what Colin is asking. He is looking for the instance information.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 58
- Joined: Tue Nov 17, 2009 3:38 am
clarcombe wrote:The plot thickens....
The customer wants to ensure that only the instances pertaining to the log purge settings are removed. i.e. I remove only the instances RT_STATUS older than 5 days. RT_LOG entries have already been removed using the Auto Purge
Using Ray's modified code I have generated these results from RT_STATUS
SELECT @ID FMT '46L', F1,F5 FROM RT_STATUS27 USING DICT VOC WHERE F1 ='INVOCATION'
Which gives meHowever, there is no way to access the date in order to delete on this,but I can see this date in Director. Does anyone know how this can be achieved ?
- VOC GFMEntetereal.011044
F1 INVOCATION
F5 DSD.RUN_59536_13921
Hi,
F1 returns the jobname with invocation id.Using the function DSGetJobInfo you can get the start time of the jobs.
Nikhil