Finding tablespaces not copied in n days

This tip gives you a list of tablespaces in a DB2 subsystem that hasn't been copied in a certain number of days. It gives the date of the latest copy if one exists, or '*NO COPY*' if there is no image copy.

For recovery purposes, it's important for a DBA to be able to determine which production tablespaces haven't been copied recently or perhaps haven't been copied at all. I've seen different SQL pieces put forward to do this, but they never seem to take into account all circumstances.

The simplest way to start is to look at SYSIBM.SYSCOPY, and list all tablespaces in which the latest image copy date is less than today's date minus n days. For example:

 SELECT DBNAME,TSNAME,MAX(TIMESTAMP)
      FROM
          SYSIBM.SYSCOPY
              WHERE ICTYPE = 'F'
      GROUP BY DBNAME,TSNAME
      HAVING MAX(TIMESTAMP) < CURRENT TIMESTAMP - 7 DAYS

The main flaw here is that a tablespace which has never been copied (and presumably you want to know about) does not appear in this list.

So we have to do a left outer join with SYSIBM.SYSTABLESPACE, so that we get a row for every tablespace:

 SELECT A.DBNAME,A.NAME,C.ITIMESTAMP
      FROM
          SYSIBM.SYSTABLESPACE A
      LEFT OUTER JOIN
         (
          SELECT DBNAME,TSNAME,MAX(TIMESTAMP) AS ITIMESTAMP
              FROM
          SYSIBM.SYSCOPY
              WHERE ICTYPE = 'F'
              GROUP BY DBNAME,TSNAME
         ) C
          ON   A.DBNAME = C.DBNAME
          AND A.NAME = C.TSNAME
      WHERE C.ITIMESTAMP IS NULL
          OR C.ITIMESTAMP < CURRENT TIMESTAMP - 7 DAYS

But we're forgetting about partitioned tablespaces. Some partitions may have been copied, while others haven't. To take this into account, we use SYSIBM.SYSTABLEPART instead of SYSIBM.SYSTABLESPACE and use PARTITION from that and DSNUM from SYSCOPY. This gives us a row for every non-partitioned tablespace and a row for every partition of a partitioned tablespace:

 SELECT A.DBNAME,A.TSNAME,A.PARTITION,C.ITIMESTAMP
      FROM
          SYSIBM.SYSTABLEPART A
      LEFT OUTER JOIN
         (
          SELECT DBNAME,TSNAME,DSNUM,MAX(TIMESTAMP) AS ITIMESTAMP
              FROM
          SYSIBM.SYSCOPY
              WHERE ICTYPE = 'F'
              GROUP BY DBNAME,TSNAME,DSNUM
         ) C
          ON   A.DBNAME = C.DBNAME
          AND A.TSNAME = C.TSNAME
          AND A.PARTITION = C.DSNUM
      WHERE C.ITIMESTAMP IS NULL
          OR C.ITIMESTAMP < CURRENT TIMESTAMP - 7 DAYS

The problem with this query is that a partition may be copied singly or as part of the whole tablespace, so a satisfying entry in SYSCOPY may have DSNUM equal to PARTITION from SYSTABLEPART, or equal to zero. This query takes care of that, but using an OR condition in an outer join is only available in DB2 V6 or later:

 SELECT A.DBNAME,A.TSNAME,A.PARTITION,MAX(C.ITIMESTAMP) AS TS
      FROM
          SYSIBM.SYSTABLEPART A
      LEFT OUTER JOIN
         (
          SELECT DBNAME,TSNAME,DSNUM,MAX(TIMESTAMP) AS ITIMESTAMP
              FROM
          SYSIBM.SYSCOPY
              WHERE ICTYPE = 'F'
              GROUP BY DBNAME,TSNAME,DSNUM
         ) C
          ON   A.DBNAME = C.DBNAME
          AND A.TSNAME = C.TSNAME
          AND (A.PARTITION = C.DSNUM
               OR (A.PARTITION <> 0 AND C.DSNUM = 0))
      GROUP BY A.DBNAME,A.TSNAME,A.PARTITION
      HAVING MAX(C.ITIMESTAMP) IS NULL
          OR MAX(C.ITIMESTAMP) < CURRENT TIMESTAMP - 7 DAYS

Note that I've had to take another MAX of the timestamp--this is because for a partition of a tablespace, I may have two rows--one for the partition number, and one for partition zero.

To get a print of '*NO COPY*' instead of just dashes for null values which you get in SPUFI, I wrap another select with a VALUE function around the outside:

 SELECT DBNAME,TSNAME,PARTITION,VALUE(CHAR(TS),'*NO COPY*')
 FROM
    (
     SELECT A.DBNAME,A.TSNAME,A.PARTITION,MAX(C.ITIMESTAMP) AS TS
          FROM
              SYSIBM.SYSTABLEPART A
          LEFT OUTER JOIN
             (
              SELECT DBNAME,TSNAME,DSNUM,MAX(TIMESTAMP) AS ITIMESTAMP
                  FROM
              SYSIBM.SYSCOPY
                  WHERE ICTYPE = 'F'
                  GROUP BY DBNAME,TSNAME,DSNUM
             ) C
              ON   A.DBNAME = C.DBNAME
              AND A.TSNAME = C.TSNAME
              AND (A.PARTITION = C.DSNUM
                   OR (A.PARTITION <> 0 AND C.DSNUM = 0))
          GROUP BY A.DBNAME,A.TSNAME,A.PARTITION
          HAVING MAX(C.ITIMESTAMP) IS NULL
              OR MAX(C.ITIMESTAMP) < CURRENT TIMESTAMP - 7 DAYS
    ) D

This query gives the result we want, but if you run it in SPUFI or DSNTEP2, you will find you get loads of warnings--this is because the MAX function is finding NULLs for tablespaces or partitions which don't have an image copy. The result is still correct, but the warnings are a nuisance.

To get around that, the MAX must be moved inside the outer join. This is done by applying MAX to an inner join between SYSTABLEPART and SYSCOPY, and then taking the outer join between SYSTABLEPART and the result:

 SELECT H.DBNAME,H.TSNAME,H.PARTITION,VALUE(CHAR(D.TS),'*NO COPY*')
     AS LASTCOPY
     FROM SYSIBM.SYSTABLEPART H
 LEFT OUTER JOIN
    (
     SELECT A.DBNAME,A.TSNAME,A.PARTITION,MAX(C.ITIMESTAMP) AS TS
         FROM SYSIBM.SYSTABLEPART A
     INNER JOIN
         (SELECT DBNAME,TSNAME,DSNUM,MAX(TIMESTAMP) AS ITIMESTAMP
            FROM SYSIBM.SYSCOPY WHERE ICTYPE = 'F'
            GROUP BY DBNAME,TSNAME,DSNUM
         ) C
     ON A.DBNAME = C.DBNAME
       AND A.TSNAME = C.TSNAME
       AND (A.PARTITION  = C.DSNUM
            OR (A.PARTITION  <> 0 AND C.DSNUM = 0)
           )
     GROUP BY A.DBNAME,A.TSNAME,A.PARTITION
    ) D
  ON    H.DBNAME = D.DBNAME
    AND H.TSNAME = D.TSNAME
    AND H.PARTITION  = D.PARTITION
 WHERE D.TS IS NULL
   OR D.TS <  CURRENT TIMESTAMP - 7 DAYS
   ORDER BY DBNAME,TSNAME,PARTITION
 WITH UR

I've added WITH UR at the end, which you should always do on these queries.

Depending on where you issue this from, the result looks something like:

    +--------------------------------------------------------------+
    |  DBNAME  |  TSNAME  | PARTITION | LASTCOPY                   |
    +--------------------------------------------------------------+
  1_| DADMSDCH | SADCRGPD |         0 | 1991-09-05-18.44.45.791409 |
  2_| DADMSDCH | SADIFCON |         0 | 1991-09-05-18.44.49.577022 |
  3_| DADMSDCH | SADPJREG |         0 | *NO COPY*                  |
  4_| DADMSDCH | SADSYSUB |         0 | 1991-09-05-18.16.55.748894 |
  5_| DAIACCNT | SAIADNUM |         0 | 1997-07-04-23.10.12.128062 |
  6_| DAIACCNT | SAIBPTRN |         0 | 1994-03-04-00.18.27.255489 |
  7_| DAIACCNT | SAICSHID |         0 | 1997-07-04-23.10.05.901498 |

For More Information


This was first published in May 2001

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Back to top