Candidate list for the "NOT LOGGED" attribute

Larry Kirkpatrick

Candidate list for the "NOT LOGGED" attribute
I have done some research into the current log RBA that we have and where it is going. I have a history of this for the past (approximately) three years and note that if the current rate of logging is maintained, we will probably not need to reset the RBA for about 15 years. However, we have been experiencing an increase in logging over that past three years that concerns me. If I fit a curve onto the increase in logging (probably, the worst case scenario), the forcasted timetable for resetting the RBA becomes five years instead of 15.

With all that being said, I am looking for tables that legitimately should be using the "NOT LOGGED" option. Unfortunately, I have found several tables that should be using the LOAD utility (with LOG NO) to populate them, but it is hard to teach application people new tricks. Basically, I am talking about tables that are periodically repopulated with a mass delete followed by a mass insert process that is logged.

I share with you a query that I am using to get a list of candidates for the "NOT LOGGED" option. From what I can tell, this query should deliver an initial list of tables that you can investigate for using the "NOT LOGGED" option. Any thoughts or advise would be appreciated (and yes, I already know that using the LOAD utility would be preferable).

SELECT SUBSTR(
STRIP(A.TBCREATOR, TRAILING) CONCAT '.'
CONCAT STRIP(A.TBNAME, TRAILING) , 1, 30 )
AS TBNAME,
INTEGER(MAX(X.TOTALENTRIES))
AS IX_ENTRIES
FROM SYSIBM.SYSINDEXSPACESTATS X,
SYSIBM.SYSINDEXES A
WHERE (X.REORGMASSDELETE > +0
OR X.STATSMASSDELETE > +0)
AND X.TOTALENTRIES > 2000
AND X.DBNAME = A.DBNAME
AND X.INDEXSPACE = A.INDEXSPACE
AND NOT EXISTS (
SELECT 1 FROM SYSIBM.SYSTABLES TB1,
SYSIBM.SYSTABLESPACE TS
WHERE TB1.CREATOR =
A.TBCREATOR
AND TB1.NAME =
A.TBNAME
AND TB1.DBNAME =
TS.DBNAME
AND TB1.TSNAME =
TS.NAME
AND TS.LOG = 'N')
GROUP BY
SUBSTR(
STRIP(A.TBCREATOR, TRAILING) CONCAT '.'
CONCAT STRIP(A.TBNAME, TRAILING) , 1, 30 )
ORDER BY IX_ENTRIES DESC
WITH UR ;


Larry Kirkpatrick
Mutual of Omaha

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv