Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted July 18, 2016

Is Your DB2 Subsystem Healthy?

By Lockwood Lyon

Each new release of DB2 for z/OS contains "autonomic" features that allow the subsystem to diagnose potential issues in real-time. These include the gathering of data distribution statistics in real-time, the ability to reorg databases based on performance criteria, and other functions included in the new DB2 Autonomics Director.

However, it is still possible for performance and data availability to deteriorate due to a variety of things, including lack of proper referential integrity support, excessive package versions and potential security holes.

This article presents simple SQL statements  that the database administrator (DBA) can execute against the DB2 catalog to determine if your DB2 subsystem suffers from common maladies, and what the DBA can do to fix or mitigate potential problems.

A Word on Addressing System Issues

Unlike most application tablespaces, the DB2 system tablespaces are usually constantly in-use.  Taking a system tablespace off-line for backup or performance tuning cannot be done at these times!  The best practice is to define one or more maintenance windows during the production day (or week, or month), during which the system tablespaces may need to be unavailable for short periods of time. Often these times are in the early morning hours during a weekend when application activity is minimal. DBAs can use the job scheduling system to ensure that application jobs and queries are not running during this time, thus allowing for regular tune-ups and maintenance.

System Tablespace Issues

DB2 system tablespaces are used to store control information about the DB2 subsystem, including lists of user tables, indexes, and so forth. As more and more application data tables and indexes are created, these system tablespaces can become disorganized. One of the indicators of this issue is dataset extents. As tablespace sizes increase, DB2 allocates additional datasets called extents to contain the new information.

Modern disk data technologies such as redundant disk arrays and increased data cache sizes have reduced the severity of having tablespace extents spread across multiple files and disk volumes. Still, there remains some minor delays inherent in searching these files. For example, when an ad hoc SQL query arrives from an application DB2 must analyze the query, including determining what tables and columns are accessed. To do this, DB2 must search the DB2 system tablespaces for table and column names. In a system where there are potentially thousands of such queries arriving every hour, even very minor search delays can add up.

Use the following query to determine if any DB2 system tablespaces are defined with more than fifty extents.  If so, consider redefining the primary and secondary space allocations and executing the Reorg utility.

    SELECT DBNAME, TSNAME, PARTITION, EXTENTS                
    FROM  SYSIBM.SYSTABLEPART               
    WHERE  DBNAME LIKE 'DSN%'             
    AND  EXTENTS > 50     
    ORDER BY EXTENTS DESC                   
     

Another common system tablespace performance issue is recovery time.  All IT enterprises have disaster recovery plans that specify the maximum time that it takes to recover a DB2 subsystem after an outage or disaster. Typically this is on the order of an hour.  In order to recover DB2 system tablespaces, the DBA must perform frequent backups. These can take the form of bulk data copies to magnetic tape, real-time backups to parallel disk files, or even “hot” backups across a network to a disaster recovery site.

The worst case is when a disk drive totally fails and any real-time backups are incomplete or unavailable. In that case, the DBA must recover a DB2 system tablespace by loading the backup file then using the Recover utility to process saved log records (also sent to the recovery site). To prepare for this, the DBA executes the Copy utility for all or most of the system tablespaces on a regular basis. 

These copies can be a full, off-line copy, which requires that no activity is updating the tablespaces.  Typical activities include creating tables and indexes. Alternatively, the copies can be made during activity that updates the tablespaces. These copies take longer to make, and require more time to process during a recovery. (The actual recovery process is beyond the scope of this article. Interested readers should reference the appropriate DB2 Administration Guide for their DB2 version and reference the chapter on system recovery.)

To determine if any DB2 system tablespaces have not had recent backup copies made, execute the following query.

    SELECT  TPT.DBNAME, TPT.TSNAME, TPT.PARTITION        
    FROM  SYSIBM.SYSTABLEPART   TPT         
    WHERE  TPT.DBNAME LIKE 'DSN%'          
    AND NOT EXISTS        
      (SELECT  1  FROM  SYSIBM.SYSCOPY   CPY         
        WHERE CPY.DBNAME = TPT.DBNAME      
          AND CPY.TSNAME = TPT.TSNAME      
          AND  ( CPY.DSNUM = TPT.PARTITION  OR  CPY.DSNUM = 0  )                   
          AND  CPY.ICTYPE IN ('F', 'I')    
          AND  CPY.TIMESTAMP > (CURRENT TIMESTAMP - 30 DAYS)  )
     ORDER BY  1, 2, 3     
        
 

System Index Issues

DB2 system indexes can suffer from the same performance issues as user application table indexes. These issues were covered in a previous article.  (See “Is Your Database Healthy?”)  I urge readers to reference the queries there.  The only difference will be to specify predicates like  DBNAME LIKE 'DSN%'   to have the query reference system indexes.

In addition to performance-related issues, DB2 system indexes are referenced even more frequently than their corresponding tablespaces.  As noted earlier, DB2 must process ad hoc queries by confirming correct table and column names. To do this, DB2 searches the system tables, and the fastest way to access them is through their indexes.  DB2 searches SysTables by table name, SysIndexes by index name, SysColumns by column name, and so forth. Each of these searches utilizes an index on one or more columns of the table.

In addition to searches, many applications invoke searches of the DB2 system tables. These are usually network-based or so-called “distributed” applications written in object oriented languages. In many cases these applications do not contain raw SQL statements; instead, they construct SQL dynamically and receive and process the results the same way. This requires the application to execute SQL against the DB2 system tables.

For example, an application that needs to retrieve customer information from the Customer table will query the DB2 system tables for column attributes such as column data type (character? numeric) and length.  This allows the DBA or database designer to make minor database changes without forcing massive changes to applications.  Need to expand the Customer Name from 20 bytes to 32 bytes?  Not an issue, as distributed applications will query the DB2 system tables to find out the current length, and store and process it accordingly.

In order to manage access and performance, how many applications access the DB2 system tablespaces and indexes?  While ad hoc applications must be listed and dealt with manually, applications whose plans and packages are stored in DB2 will allow the DBA to see which are accessing the DB2 system.

To determine the most frequently used DB2 system indexes, execute the following queries.  Armed with this information, devote your attention to performance tuning of the indexes at the top of the lists.

    SELECT   CREATOR  , NAME   ,  COUNT(*)  AS  #_PLN                  
    , UQ  AS UNIQUE , #_LF  as NUM_LEAF 
    , LV  AS NUM_LVL , BPOOL    , "1K_CRD"   , FL_CRD              
    FROM  ( SELECT  IDX.CREATOR           
     , SUBSTR(IDX.NAME,1,8)  AS  NAME  
     , IDX.UNIQUERULE   AS  UQ   , IDX.NLEAF  AS  #_LF  
     , IDX.NLEVELS      AS  LV   , IDX.BPOOL           
     , INTEGER(IDX.FIRSTKEYCARDF)  AS  "1K_CRD"                
     , INTEGER(IDX.FULLKEYCARDF)   AS  FL_CRD                  
    FROM  SYSIBM.SYSINDEXES  IDX                  
         ,SYSIBM.SYSPLANDEP  PDP                 
    WHERE  PDP.BNAME    = IDX.NAME                 
    AND  PDP.BCREATOR = IDX.CREATOR         
    AND  PDP.BTYPE      = 'I'               
    AND IDX.DBNAME LIKE 'DSN%' )  AS  IDX_PLN        
    GROUP BY  IDX_PLN.CREATOR  , IDX_PLN.NAME   , IDX_PLN.UQ          
       , IDX_PLN.#_LF   , IDX_PLN.LV     , IDX_PLN.BPOOL       
       , IDX_PLN."1K_CRD"  , IDX_PLN.FL_CRD      
    HAVING   COUNT(*) > 150        
    ORDER BY  3 DESC                  
 
 
 
 
    SELECT   IDX.CREATOR , IDX.NAME , COUNT(*)      AS #_PKGS               
    FROM   SYSIBM.SYSINDEXES  IDX                  
          ,SYSIBM.SYSPACKDEP  PKD                 
    WHERE PKD.BNAME      = IDX.NAME               
    AND  PKD.BQUALIFIER = IDX.CREATOR       
    AND  PKD.BTYPE      = 'I'               
    AND IDX.DBNAME LIKE 'DSN%'          
    GROUP BY    IDX.CREATOR , IDX.NAME            
    HAVING  COUNT(*) > 150        
    ORDER BY        3 DESC                  

Potential Referential Integrity Issues

Supporting referential integrity is part of the DBA’s job.  (For an introduction to this issue, see, “Referential Integrity: Best Practices for IBM DB2”).  If your database design contains parent tables, child tables, and foreign key relationships, then DB2 is required to enforce table foreign key values. 

For example, consider an Order table containing orders, and a Line Item table containing line items for the orders. Implementing referential integrity means creating a primary key (say, Order Number) in the Order table and forcing it to be unique.  Next, rows in the Line Item table must contain the Order Number, and it must be valid; that is, it must already exist in the Order table.

Automating verification of these key values is best accomplished by defining indexes. Usually this is done by creating an index on the child table foreign key columns and a unique index on the parent table primary key columns. This then allows DB2 to verify whether primary keys exist, and to enforce rules when parent table rows are deleted.

Question for the DBA:  During database design, when primary and foreign keys were created and defined, were indexes defined for all the appropriate columns?  And, if changes in the design were made at a later date, were appropriate indexes changed, added, or deleted?

Execute the following query to determine whether no indexes exist for all foreign key relationships.  If so, a review of the database design is indicated.  (My apologies for the length of this query; there really isn’t a shorter way to write it. Also note that this query can be limited to specific databases using the predicate   AND DBNAME LIKE 'XXXXX%')

SELECT X.REFTBNAME    AS PARENT_TBL  ,X.TBNAME  AS CHILD_TBL           
   ,X.RELNAME  ,SUBSTR(IXNAME,1,8)   AS FK_IXNAME           
   ,DIGITS(DECIMAL(F.COLSEQ,2))   AS SQ  ,F.COLNAME        
   ,VALUE(SUBSTR(' 123456789ABCDEF',COWS - BULLS + 1,1),' ') AS BAD_SEQ             
 FROM (SELECT  R.CREATOR, R.TBNAME, R.RELNAME, COLCOUNT, R.REFTBNAME  
     FROM SYSIBM.SYSRELS R       WHERE R.CREATOR IN   
        (SELECT CREATOR FROM SYSIBM.SYSTABLES 
          WHERE TYPE = 'T' AND DBNAME LIKE 'XXXXX%')             
       AND R.REFTBCREATOR IN               
        (SELECT CREATOR   FROM SYSIBM.SYSTABLES           
          WHERE TYPE = 'T'  AND DBNAME LIKE 'XXXXX%')  ) AS X               
 FULL JOIN              
  (SELECT F.CREATOR, F.TBNAME, F.RELNAME, K.IXNAME,               
    I.INDEXTYPE, COUNT(*)   AS  COWS,     
    SUM((F.COLSEQ/K.COLSEQ) * (K.COLSEQ/F.COLSEQ)) AS  BULLS,    
    MAX(K.COLSEQ)   AS  MAXSEQ    
     FROM   SYSIBM.SYSFOREIGNKEYS  F      
           ,SYSIBM.SYSKEYS         K      
           ,SYSIBM.SYSINDEXES      I      
    WHERE   F.CREATOR  IN  (SELECT CREATOR FROM SYSIBM.SYSTABLES      
             WHERE TYPE = 'T' AND DBNAME LIKE 'XXXXX%')             
      AND  F.CREATOR    = I.TBCREATOR  AND  F.TBNAME     = I.TBNAME       
      AND  K.IXCREATOR  = I.CREATOR    AND  K.IXNAME     = I.NAME         
      AND  F.COLNO      = K.COLNO        
    GROUP BY            
        F.CREATOR, F.TBNAME, F.RELNAME, K.IXNAME, I.INDEXTYPE  
    HAVING   MIN(K.COLSEQ) = 1     ) AS Y               
  ON  Y.CREATOR  =  X.CREATOR    AND Y.TBNAME   =  X.TBNAME              
  AND Y.RELNAME  =  X.RELNAME    AND Y.COWS     =  X.COLCOUNT            
  AND Y.MAXSEQ   =  X.COLCOUNT            
FULL JOIN               
  (SELECT CREATOR, TBNAME, RELNAME, COLNAME, COLSEQ            
     FROM SYSIBM.SYSFOREIGNKEYS           
    WHERE CREATOR  IN  
      (SELECT CREATOR FROM SYSIBM.SYSTABLES        
          WHERE TYPE = 'T' AND DBNAME LIKE 'XXXXX%')  )     AS  F          
  ON   F.CREATOR =  X.CREATOR   AND  F.TBNAME  =  X.TBNAME              
  AND  F.RELNAME =  X.RELNAME             
    WHERE   X.TBNAME IS NOT NULL                
  ORDER BY   PARENT_TBL,  CHILD_TBL, RELNAME,  SQ               
            
 

Potential Security and Audit Issues

Many DB2 subsystems have existed for a decade or more. During that time, multiple versions of DB2 have come and gone, several third-party tools have been implemented and upgraded, and many applications may have come and gone. During that time, it is possible that certain authorization ids such as users, DBAs, or application ids retain unneeded access to controlled DB2 information.

The best practice is to determine if any ids still retain such unneeded or powerful access and to revoke that access.

Execute the following query to determine if any id currently has one of the system access permissions.  We include here the system administrator, system operator and system control permissions. Other permissions exist. (Readers are urged to reference the appropriate DB2 Administration Guide chapter on system security for these additional permissions.)  Once you obtain the list, have it reviewed by a security audit team to determine if these ids really require such system-level access.

    SELECT DISTINCT GRANTEE, 'SYSADM'       
    FROM  SYSIBM.SYSUSERAUTH                
    WHERE  SYSADMAUTH <> ' '                
    UNION ALL             
    SELECT DISTINCT GRANTEE, 'SYSOPR'       
    FROM  SYSIBM.SYSUSERAUTH                
    WHERE  SYSOPRAUTH <> ' '                
    UNION ALL             
    SELECT DISTINCT GRANTEE, 'SYSCTRL'      
    FROM  SYSIBM.SYSUSERAUTH                
    WHERE  SYSCTRLAUTH <> ' '               
    ORDER BY 1, 2         
 

Another common security issue is creating tables that any authorized user or application can access. This may be done for common control tables such as a list of US state abbreviations. While such tables are not a security issue per se, they create audit issues. It is possible that such tables can change over time, either by adding additional columns or additional rows. These changes may or may not result in the table now containing confidential data.

The best practice is to secure these tables using your preferred security tool. Typically, these tools allow the DBA or security administrator to assign a group name that can access a table, and then adding user and application ids to that group.  In this way, at any time the administrator can determine from the security system exactly what applications and users have access to which tables.

Execute the following query to determine if any tables have their access set to Public.  If so, these tables should be reviewed and their access changed to conform to your security and audit requirements.

    SELECT   TCREATOR, TTNAME      
    FROM  SYSIBM.SYSTABAUTH                 
    WHERE  GRANTEE LIKE 'PUBLIC%'           
    ORDER BY 1, 2         
 

Summary

Every DB2 subsystem is different, and subsystems grow and morph in usage and data content over time.  The queries presented here can be used by the DBA in a regular reporting scheme to monitor the quality of certain aspects of your DB2. Such a regular review has many uses:

  • Expand it to include queries that are relevant to the subsystems you manage;
  • Use it as a training tool for new DBAs;
  • Forward it to your audit and security teams when needed for their review, and ensure that you make any additions or changes that are recommended;
  • Finally, use it as a way to proactively make your system perform better.

* Disclaimer. This information is based on research, techniques, and documentation available at the time of creation. It is therefore subject to change at any time without notice or warning. The following queries are presented “as-is”, without any warranty either expressed or implied.  Please also note that these queries access DB2 catalog tables containing performance and data distribution statistics. It is assumed that the DB2 support staff has procedures in place (such as the RunStats utility) to keep these statistics up-to-date.

See all articles by Lockwood Lyon



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.