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.
