Reducing Joblogsize after Purge?

DSUser2000 · Post by **DSUser2000** » Fri Dec 18, 2009 7:49 am

Is it possible to reduce the size of the joblog after purging log entries (I've purged all entries older than 30 days)? Perhaps by some kind of Universe Database command like "Optimize Database" or so (I already know the "RT_LOGXXX" table which I need via uvsh)?
I know that it's possible to delete the job and create it new but I don't want to loose all entries...

chulett · Post by **chulett** » Fri Dec 18, 2009 7:59 am

They're Type30 dynamic hashed files, so that purge should automatically be reducing the log's size as well. Are you certain that it is not?

DSUser2000 · Post by **DSUser2000** » Fri Dec 18, 2009 8:15 am

We've built a job which lists logfiles which get too huge. The job is a MultipleInstance job which got > 1GB. After deleting entries older than 30 days via the director, the size stayed almost exactly the same (the older log entries are really deleted): 1207MB before, 1206MB now. The deletions made should be definately more than 1MB...normally it should be about the half the size or smaller.

chulett · Post by **chulett** » Fri Dec 18, 2009 8:39 am

Multi-instance jobs are a little trickier - where did you do the purge from, the 'base' job or from one / all of the invocation ids? How long had it been since the last '30 days' purge?

DSUser2000 · Post by **DSUser2000** » Fri Dec 18, 2009 9:06 am

I've done the purge from the base job. In the instance logs, the option to clear the log is greyed out...
The log exists since mid october (job was created then), so it's been 2 months now. Thus, I though that deleting 30 days would reduce the size by about the half...

chulett · Post by **chulett** » Fri Dec 18, 2009 10:24 am

True, forgot you can only do it from the main job. Did you get a count of log entries before and after? That should confirm the amount of records that were actually deleted.

chulett · Post by **chulett** » Fri Dec 18, 2009 10:57 am

Check out the RESIZE command. Not sure the purge affects the OVER.30 and DATA.30 goes down in Page Size increments but resize should get both, I do believe.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 18, 2009 3:12 pm

Transactional DELETE does not free up disk space - it remains within the hashed file structure for re-use.

Commands CLEAR.FILE and RESIZE are non-transactional and do free up disk space. An appropriate syntax is

Code: Select all

RESIZE RT_LOGnnn * * *

This does not alter any of the hashed file's tuning parameters, but does free up disk space.

If disk space is at a premium in the project directory, add a USING clause to the RESIZE command to use a different file system as temporary workspace.

Code: Select all

RESIZE RT_LOGnnn * * * USING /usr/tmp

DSUser2000 · Post by **DSUser2000** » Mon Dec 21, 2009 3:03 am

uvsh "RESIZE RT_LOGxxxx *"
worked so far and recovered about 200MB.
Analyze.File before resize:

File name .................. RT_LOGxxxx
Pathname ................... RT_LOGxxxx
File type .................. DYNAMIC
NLS Character Set Mapping .. NONE
Hashing Algorithm .......... GENERAL
No. of groups (modulus) .... 6544 current ( minimum 1 )
Large record size .......... 1628 bytes
Group size ................. 2048 bytes
Load factors ............... 80% (split), 50% (merge) and 50% (actual)
Total size ................. 1265258496 bytes

After:

File name .................. RT_LOGxxxx
Pathname ................... RT_LOGxxxx
File type .................. DYNAMIC
NLS Character Set Mapping .. NONE
Hashing Algorithm .......... GENERAL
No. of groups (modulus) .... 4040 current ( minimum 1 )
Large record size .......... 1628 bytes
Group size ................. 2048 bytes
Load factors ............... 80% (split), 50% (merge) and 80% (actual)
Total size ................. 1088081920 bytes

I think the reason why not more was recovered is this load factor. I think I'll have to live with that and just delete more...

chulett · Post by **chulett** » Mon Dec 21, 2009 7:26 am

There should be three stars after the resize, not sure what difference it makes however. Please mark this as Resolved using the button at the top of the page.