RT_STATUS structure for deleting invocation ids

[clarcombe]

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

[kduke]

Hacking is the only way to get these strutures. The API is the only approved way to read these strutures. Very few APIs update these strutures.

[ray.wurlod]

In the RT_STATUSnnn table the invocation ID is part of the key.

Code: Select all

SELECT @ID FMT '60L' FROM RT_STATUSnnn;

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.

Code: Select all

SELECT @ID FMT '46L', F1, F2 FROM RT_STATUSnnn USING DICT VOC;

will get you started.

[clarcombe]

Thanks.

I will try that when I get back to the office next week

[clarcombe]

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

• VOC GFMEntetereal.011044
  F1 INVOCATION
  F5 DSD.RUN_59536_13921

However, 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 ?

[ArndW]

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.

[clarcombe]

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.

[ArndW]

It would be in the RT_STATUS file, where the key contains the instance name and the start date is in column 3.

[clarcombe]

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

[ArndW]

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%';

[kumar_s]

HI,
You can directly use TIMESTAMP as key word like

SELECT TIMESTAMP FROM RT_LOGnnn or SELECT F3 FROM RT_LOGnnn DICT VOC

to get the timestamp of each id.

[ArndW]

Yes, you can do that. But that isn't what Colin is asking. He is looking for the instance information.

[nikhilanshuman]

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

• VOC GFMEntetereal.011044
  F1 INVOCATION
  F5 DSD.RUN_59536_13921

However, 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 ?

Hi,

F1 returns the jobname with invocation id.Using the function DSGetJobInfo you can get the start time of the jobs.

[chulett]

Holy thread necromancy, Batman.

[kduke]

Zip, zoom, pow. The Riddler is down.