MainframeSupport ønsker dig god påske. Næste tip udkommer mandag den 24. marts 2008
MainframeSupport wishes you a happy easter. The next tip will be published monday the 24th of March 2008.
One of the most neglected and at the same time most interesting tables in the DB2 catalog is in my view SYSIBM.SYSCOPY. Each time a tablespace is changed in a way that will have influence on a recovery to be performed later it is recorded in SYSCOPY. It means that every run of an image copy, reorg, load or recover is recorded here. The columns of SYSCOPY are described in an appendix in SQL reference.
The most important column is ICTYPE which contains a code for which type of registration has been carried out. The column TIMESTAMP tells when the registration took place. The columns ICDATE and ICTIME holds the same information as TIMESTAMP. Combined with the columns DBNAME and TSNAME (and DSNUM) ICTYPE and TIMESTAMP can tell you a lot about what is going on and when in your DB2 system. Equally important or even more important it tells you a lot about which activities might be missing.
Let me give you some practical examples of the use of SYSCOPY. Lately I have used SYSCOPY to find out who emptied a table on various occations. It was emptied by a LOAD REPLACE with dummy input which is recorded in SYSCOPY. Another example is to find out if there are any tables not being backed up by an IMAGE COPY. If you have an agreement with a facility management company on how, when and what needs to be reorganised and image copied then SYSCOPY is the place where you can follow up and see if the agreement is followed. If you need to find out what a table contained on a certain point in time you will find the necessary image copies in SYSCOPY. When you have opened your eyes for the possibilities in SYSCOPY there is almost no limits to the number of vital informations you can find in this table.
Unfortunately trees do not grow into the heaven (a danish saying?). If your installation uses products from BMC, Computer Associates or another third party vendor they might not update SYSCOPY when they run their utilities. Another detail is that SYSCOPY with high likelihood only contains data a little while back in time. It depends on how often and how your installation runs the MODIFY RECOVERY utility. This utility simply removes registrations from SYSCOPY using criterias you may control. Typically operations and/or DBA has decided the frequency of removals.
Happy news in version 7 of DB2 was that SYSCOPY was extended with columns JOBNAME and AUTHID. JOBNAME contains the name of the job which carried out the recorded utility and AUTHID contains the userid of who ran the job. Before version 7 it could be a pretty tough job to find out which job and who carried out a utility, but this is now much easier.