Last week I talked about the basics of evaluating DB2 for z/OS and LUW compression. DB2 compression saves a huge amount of disk storage space and improves performance by eliminating I/O within your system. This week we talk about the performance implications of some other table space settings. If your database tables suffer performance problems, have bad SQL access paths and need constant reorgs, there are a number of table space settings that can help you avoid reorgs, minimize outages and help your overall application performance.
First, table and index utility reorgs are one of the most expensive processes that can happen within any database environment. Unfortunately, a number of shops blindly perform weekly or monthly table reorgs on their most critical and busiest tables. This is totally unnecessary and a huge impediment to overall performance. The related downtime for these tables reorgs and the monitoring of the utility jobs are an extra burden on a mainframe already running at 100% utilization, a problem to schedule around application processing and an extra off-hours monitoring hassle for an over worked staff.
By examining your environment, you can save resources and CPU. With the correct settings you can also improve performance by properly allocating the table and index spaces. So many times these settings are only roughly estimated leading to unnecessary performance issues and the constant weekly or monthly reorg treadmill. With proper planning of your table sizing and the proper parameters on your table spaces, these table reorgs can be avoided completely or done less frequently, saving significant CPU utility costs and application downtime.
Using the first query below, you can examine the amount of space that is allocated for a table space and the amount of storage space that is actually used. By looking at the query results you can quickly understand the space allocation, the number of secondary extents and where your table spaces are under or over allocated. The query uses less than 80% active pages as its criteria for analysis and can be adjusted for your environment. The NBR_OF_EXTENTS can show a negative number indicating that all the primary quantity allocation is not yet utilized. Table spaces with large numbers of extents should have their primary and secondary allocations adjusted to minimize any extents after their next reorg.
This query also examines the amount of space that is used up by dropped tables through the PERCDROP column. This is usually zero because most table spaces commonly support only one table. If your environment has previously been defined with multiple tables and then one was dropped, this query will let you know.
–Query1 – Find Table spaces with less than 80% active pages within DB2 z/OS.
Not valid for <1 track table spaces
SELECT DBNAME, TSNAME, PARTITION, CARDF,
PERCACTIVE, PERCDROP, PQTY, SQTY,
(SPACEF/4) AS SPACE_4K,
(((SPACEF/4) – PQTY) / SQTY) AS NBR_OF_EXTENTS
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME LIKE ‘database criteria%’
AND PERCACTIVE < .80
AND SQTY >1
ORDER BY DBNAME, TSNAME, PARTITION
WITH UR;
Query1’s results also shows all the table spaces that have no rows in them. This is especially important information for PeopleSoft, SAP and Seibel systems, to insure that only the tables that are storing data are pre-defined. Look to redefine these empty table spaces with DEFINE(NO) so they will not be allocated until they are referenced.
Next week I will discuss the research, properties and impact of PCTFREE and FREEPAGE on your database table space and index performance.
Leave a Reply