Today’s session will cover: Introduction to the Catalog and Directory describing the various tables and structures and the relationship between the catalog and directory Details on the structure of the various tablespaces that are part of the directory Operational considerations related to directory structures Utility considerations for backup, recovery, and reorganization
• Metadata for DB2 objects defined in the subsystem
• Updated by DB2 in response to certain SQL statements, command, and utilities
• Accessible using standard SQL
Default DATABASE DSNDB04
• Database for tablespaces defined with no IN DATABASE specification
Default DATABASE DSNnnnnn
• Implicitly defined databases supported in DB2 9
Presenter
Presentation Notes
There are 5 databases that can be considered DB2 system databases, including: DSNDB06 – the home of the catalog, which is basically metadata for all things in the DB2 subsystem. Updated by DB2 processes like DDL, commands, and utilities. Most important, the catalog can be read using standard SQL; DSNDB01 – This is the directory, a repository for run time components. Populated by DDL, bind operations, and some real-time events; DB2 WORK database (user named) – Repository for workfiles used by multiple DB2 processes. DB2 9 changed the way workfiles are managed by combining the TEMP and WORK pagesets into a single database. DSNDB04 – This is still a default database for an explicit tablespace with no IN DATABASE specification for. DSNnnnnn - Is a new option in DB2 9 where DB2 will define an implicit database where no explicit information is provided for database, tablespace, etc. This feature in DB2 will also create other DB2 objects to service the table, including indexes to support primary keys, unique constraints, and others.
Most of us consider the DB2 catalog first because it’s the DB2 system database we can access using SQL. The catalog includes 28+ tablespaces, 100+ tables, and numerous indexes and such. The catalog maintains the details on: Object definitions Authorizations Plans & Packages DB2 Statistics Communications Database
• Metadata for all DB2 objects• Populated/updated in response to certain SQL,
commands, and utilities• Accessible via SQL DML statements• Examples of a few of the more important catalog tables
• These particular tables are related to the contents of the DB2 Directory
Presenter
Presentation Notes
This chart drills into more detail on the catalog tablespaces/tables in general and begins to discuss the catalog tables most closely related to the DB2 directory. As noted, the catalog is metadata for mostly everything in DB2. It’s populated/updated by various activities including: SQL – DDL operations (CREATE, ALTER, DROP) Commands – BIND Plan and Package constructs Utilities – COPY, for example Three catalog tablespaces are of most consideration related to the DB2 Directory. These tablespaces are SYSDBASE, SYSPLAN, and SYSPKAGE. As shown, there are multiple tables in each of these tablespaces we need to consider when looking at the DB2 directory. SYSDBASE records information about DB2 objects. Some of these entries are written directly to the DB2 Directory tablespace DBD01. Plan and package information from the catalog is recorded in the SCT02 and SPT01 directory tablespaces.
Database DescriptorsA run-time operational structure that identifies all DB2 objects created in a specific databaseInternal structure not visible to the “naked” eye
Skeleton Cursor TableOperational run-time structure with access path information about a DBRM-based plan
SYSLGRNX – Recovery Log RangesProvides details by DB2 object about log ranges required for recovery processing
DB2 In-flight Utilities RegisterContains entries about DB2 utilities currently running or abnormally terminated/stopped
Skeleton Package TableOperational run-time structure with access path information about packages
Presenter
Presentation Notes
This chart introduces the DB2 Directory structures. These structures are operational run-time repositories and are not visible to the ‘naked’ eye. As shown, there are 5 directory structures of concern: DBD01 – contains descriptions of DB2 objects by database; SCT02 – contains the skeleton cursors related to DBRM-based plans; SPT01 – describes the skeleton packages related for packages; SYSLGRNX – details about DB2 log ranges required for recovery of DB2 objects; SYSUTILX – maintains entries for DB2 utilities that are currently executing or have been stopped or abnormally terminated.
• Internal DB2 system tables that describe:• DB2 Data Structure• How the data is stored• How DB2 can access the data
• Internal formats not accessible via DML•Populated/Updated by DDL, bind processes, and utility
operations•VSAM datasets allocated by installation job DSNTIJIN
Presenter
Presentation Notes
This goes into more details on the DSNDB01 database where the directory objects are stored. In this example, a –DISPLAY DATABASE command was done for DSNDB01, returning a list of the tablespaces and indexspaces for this database. There’s some additional discussion about these various system tablespaces. The directory entries could be considered compiled run-time information that’s used to provide higher performance in an operational environment. Otherwise, DB2 could just get the definitions for some of these things from the catalog. This is especially true of the DBD01, SCT02, and SPT01 tablespaces. As mentioned, you can’t access this information using SQL.
Directory ObjectsLinkage to Operational Structures
Database Descriptors
DBD01
Skeleton Cursor Tables
SCT02
Skeleton Package Tables
SPT01
Log Ranges
SYSLGRNX
System Utilities
SYSUTILX
Presenter
Presentation Notes
Several of the DB2 directory tablespaces are closely related to operational components in DB2. In the DBM1 address space, various pools (storage areas) are allocated. Directory information is copied into these pools at run time. The performance goal is to keep these directory structures in DB2 storage as long as required to avoid reloading them from disk. More on that later. The DBD01 contains the database descriptors (DBDs) that are loaded into the DBDPOOL (above the bar). SCT02 contains the skeleton cursor tables that are loaded into the EDMPOOL (above the bar). SPT01 contains the skeleton package tables loaded into the EDMPOOL (above the bar). The CTs (cursor tables) and PTs (package tables) are operational copies of the SKCT and SKPT for each active user of any of these structures. With DB2 9 more of the CT and PT control blocks were moved above the bar into 64-bit addressing. There are still portions of the CT and PT below the bar and some customers still experience constraints in this area, in some cases limiting the concurrent work that can be done in a busy system.
What is the DBD?- Records the complete description of all objects
defined within a single database- Complex hierarchical network of OBDs chained
together - Each OBD is identified by a unique OBID (Object
Identifier)Synchronized with DB2 catalog if everything is rightNo SQL access but information in the DB2 catalog (see table)
- Access by DB2 internal processes- DISPLAY DATABASE shows lots of detail
Create or updated as a result of DDL (CREATE, DROP, ALTER) or utility operations
Objects Type OBID Name Catalog Table
Database DBD DBID SYSDATABASE
Table Space File Page set
OBIDPSID
SYSTABLESPACESYSTABLESPACE
Table Record OBID SYSTABLES
Index space Page set ISOBID SYSINDEXES
Index Fan set OBID SYSINDEXES
RI Relationship Fan set RELOBID1 RELOBID2
SYSRELS SYSRELS
Auxiliary Relationship
OBID AUXRELOBID SYSAUXRELS
SYSDATABASE
SYSTABLESPACE
Presenter
Presentation Notes
This chart begins to drill into more detail on DBD01. This tablespace stores descriptions of all the databases (DBDs) defined in the DB2 system. All the tablespaces, tables, indexspaces, and indexspaces. There are some additional structures there as well that we will talk about. What is the DBD? A CREATE DATABASE creates an entry in the DBD01 tablespace as well as the SYSIBM.SYSDATABASE catalog tables. The directory entry is assigned a DBID that uniquely identifies this database. In the example we see a DBID of 287 for this database. As objects are created in this database, entries are added to the DBD. Different object types have different identifiers. Table spaces, tables, and indexes all have object identifiers (OBIDs). Objects with underlying page sets have other identifiers. The table space includes a PSID (page set ID) while the index space has ISOBID value. The fourth column of the table shows the corresponding identifier in the catalog. There are two other Object types related to RI relationships and Auxiliary relationships as well.
1. At CREATE DATABASE DBDR is created to hold this initial parent record. Access to the DBDR is via a hash. Most of the record is free space.
2. As objects are created entries (OBDs) are created in the record.
3. If tablespace or indexspace OBDs are dropped the space is immediately available for reuse
Table OBD space is not immediately reused4. If the DBDR fills up DBDRs (Child
Records) are created to hold the new OBDs.
DBDR – Parent Record
DBDR – Child Record
DBDR – Child Record
DBDR – Parent Record
DBDR – Child Record
DBDR – Child Record
Presenter
Presentation Notes
This chart shows a bit about the DBD is actually managed. First, this tablespace using a hashing technique to determine the location of the DBD. Initially, a parent record is created. Most of the record is initially free space. As objects are created in the DBD entries (OBDs) are added to the parent record. If the parent record is full a child record is chained off of the parent record to hold the new entries. If table space or index space OBDs are dropped the space can be immediately reused. Not so a table OBD. More on that later.
Large DBDs can create operational problems - Large DBDs impact concurrency and logging- DDL against the DBD will lock out concurrent
DDL activity and will also lock out dynamic SQL activity and some utility processing
- Frequent changes to a large DBD will require more logging and increased I/O
Space in the DBD for a dropped table is not immediately reusable
- If you drop a table in a multi-table tablespace a reorganization is required to make this space available for subsequent DDL
DBDs
DBDPOOL in storage
DBDs
DBDs
DBDs
DBDsDBDs
DBDs
› How many objects in the DBD?–Maximum concurrency use 1
• Extra Administrative overhead–Multiple sources of design
recommendations
Presenter
Presentation Notes
This chart discusses some operational considerations related to the DBD Pool itself and ties back to the size of the DBD on disk. Large DBDs can impact concurrency and logging. DDL against a DBD will lock out other concurrent DDL activity and may impact or lock out dynamic SQL activity and some utility processing. Also, changes to a DBD cause the entire DBD to be logged. As mentioned on the previous chart, DBD space can usually be reused immediately; however a dropped table in a multi-table table space can’t be immediately be re-used. A tablespace reorganization will free up the DBD space for that table. There is frequently discussion about the optimum number of objects to define in a database (DBD). Opinions vary widely. For maximum concurrency you could limit to a very small number. This would be an administrative pain and probably not provide much value. On the other hand, 30,000 objects (OBDs) in a single database could be excessive for some of the reasons mentioned earlier. This chart also shows a picture of a DBD in memory. In this case we see a number of DBDs listed and show the number of ODIDs in the DBD, # of pages for the DBD, total bytes and free space in the DBD.
COPY - As normal, DBD01 can and should be copied on a regular basis- DSNDB01.DBD01 copies are not recorded in SYSCOPY, instead they are
recorded on the log- Incremental copy is not supportedMODIFY RECOVERY
- Eliminates entries in SYSCOPY, SYSLGRNX, and DBD01QUIESCE
- Like the COPY, information about Quiesce is written to the logRECOVER
- Catalog and Directory objects must be recovered in a specific sequenceDocumented in multiple places
– For directory objects1. DSNDB01.SYSUTILX and it’s indexes3. DSNDB01.DBD01 (no indexes)6. DSNDB01.SYSLGRNX and it’s indexes
» Other directory objects come later
Presenter
Presentation Notes
Utility considerations for the DBD01 tablespace are as follows: COPY – DBD01 should be copied on a regular basis. DBD01 copies are not recorded in the SYSIBM.SYSCOPY table. They are instead recorded on the DB2 log. This is also true of SYSCOPY itself. MODIFY RECOVERY – Is used to eliminate entries in DBD01. QUIESCE – can be run against DBD01 but the quiesce point is recorded on the log RECOVER – trick here is the directory and catalog must be recovered in specific sequences if recovering multiple objects, as might be the case in a DR situation. In the example, SYSUTILX must be recovered first. DBD01 will be the 3rd object recovered in this scenario.
REORG when required- Schedule with reorganizations of SYSDB06.SYDBASE- Image copy should be taken prior to running the reorganization
Remember image copy information required for recovery is not written to SYSCOPY but to the log
REPAIR DBD- Inconsistencies between DBDs and the catalog can occur
This stuff can be scary- Resolution process
1. Run DSN1CHKR against DBD01 – Scans for broken links, broken hash chains, and orphan records
2. Start the space in UT (utility status)3. Run REPAIR DBD with TEST to identify any inconsistencies between
directory and catalog4. If inconsistencies are found use the REPAIR DBD DIAGNOSE and the
REPAIR DBD REBUILD options– Good time to be on the phone with the DB2 support team
Presenter
Presentation Notes
More on utility considerations for DBD01. REORG – Should be run when required. Best to schedule this with you reorganization of SYSDB06.SYSDBASE. Image copies should be take prior to doing the reorganization just in case. REPAIR DBD – Inconsistencies can occur between DBD01 and the DB2 catalog and can be a challenge to resolve. The steps to resolve: Run DSN1CHKR against DBD01 to identify broken links, hash chains and identify orphan records that may exist. Run REPAIR DBD TEST to identify inconsistencies between the DB2 catalog and directory Use the REPAIR DBD DIAGNOSE and REBUILD options resynchronize rebuild the directory using the catalog.
Structures containing operational access path information about DBRM-based plans - Packages is the recommended approach- Anybody still using DBRM-based plans?
Skeleton Cursor Table (SKCT) -- Internal form of SQL statements contained in an application - Created and updated using BIND and REBIND PLAN command variations- Deleted using FREE PLAN command
Loaded into EDM pool at plan execution- Concurrent plan user gets their own copy called the CT (Cursor Table)- DIRECTORY/CATALOG Relationship
SYSDBRMSYSPLANDEP
SYSSTMTSYSPLANAUTH
SYSPLAN
Plan Metadata
BoundPlan
SCT02
Presenter
Presentation Notes
This section discusses the Skeleton Cursor Tables. SCT01 contains entries for DB2 plans, specifically the access path information for the SQL reference the plan. This is only true of a DBRM-based plan. Hopefully, most of you are using packages. Some of you may still used DBRM-based plans. In DB2 V10 that will be a problem as DBRM based plans are no longer supported. Can’t say more than that since DB2 V10 is not generally available. You will hear much more about DB2 V10 during the IDUG North America conference. Entries in the SKCT are created/updated/delete by DB2 commands BIND, REBIND PLAN, and FREE PLAN. A Skeleton Cursor (SKCT) is loaded into the EDMPOOL (above the bar) when a particular plan is referenced and the SKCT is not already in the EDMPOOL. This is an important metric to watch. You want the SKCT to stay in DBM1 virtual storage to avoid loading the SKCT from disk. Each individual user gets a working copy (CT) of the SKCT. The last chart shows the relationship between the SCT02 directory table space and the DB2 catalog.
Pageset DSNDB01.SCT02Table SYSIBM.SCT02Accessed through one unique index- DSNSCT02 (SCTNAME,SCTSEC,SCTSEQ)
Plans stored in one or more SCTR(s)- Header and multiple sections SCTR – Skeleton Cursor Table
Parent Record
DM RecordPrefix – 6 bytes
SCTR record prefix18 bytes Skeleton Cursor Data
•Length Indicator•Plan Name•Section/Sequence #
Presenter
Presentation Notes
This chart introduces the storage implementation for the SCT02 directory table. There is a table in this tablespace but it’s not really visible through SQL. Access to these SKCTs is done through a single index (DSNSCT02). Generally this tablespace is straightforward. For package
Structures containing operational access path information about plans and packagesSPT01 – Skeleton Package Table (SKPT)- Internal form of SQL statements contained in a package - Created and updated using BIND and REBIND PACKAGE command variations- Deleted using FREE PACKAGE command- Maximize size of 64GB
Plan user gets their own copy called the PT (Cursor Table)DIRECTORY/CATALOG Relationship
BoundPackages
SYSPACKAGE
SYSPACKLIST
SYSPACKSTMT
SYSPACKDEP
SYSPACKAUTH
Catalog Package Metadata
SYSPKSYSTEM
SYSPLSYSTEM
SPT01
Presenter
Presentation Notes
SPT01 is the far more interesting table space than SCT02. Most of your should be using packages and SPT01 contains the skeleton package tables for your application. The SPTs contain the internal form of your SQL statements along with access path information for statically bound statements. Skeleton package tables are updated as the result of BIND and REBIND PACKAGE statements with all their variations. Today, there is a maximum size of 64GB for SPT01. More on this topic for the upcoming Finally, the graphic shows the relationship between SPT01 and the various catalog tables containing related data.
Pageset DSNDB01.SPT01Table SYSIBM.SPT01Accessed through two unique indexes- DSNSPT01 (SPTPID,SPTSEC,SPTSEQ)- DSNSPT02 (version, SPTID, SPTSEC, SPTSEQ)
Package stored in one or more SPTRsDB2 9 Plan Stability feature can dramatically increase the size of this pageset
SPTR – Skeleton Package Table Parent Record
DM RecordPrefix – 6 bytes
SPTR record prefixVariable Length Skeleton Package Data
•Collection ID (VARCHAR 128)•Package Name (VARCHAR 128)•SKPT Section Info (6 bytes)
•Body (VARCHAR 7500)•Package Version ID (VARCHAR 64)
Presenter
Presentation Notes
This chart shows the layout of the data in the SPT01 tablespace. The Skeleton Package is access through one of the two indexes (DSNSPT01, DSNSPT02) shown on the slide. The actual SKPT is stored into one or more of the SPTR (Skeleton Package Table Records) DB2 9 Plan Stability feature will increase the number of entries for a given package. More on that later.
Packages should be your standard for all applications
- Reduces overall space required in the EDMPOOL
- DB2 10 will deprecate DBRM-based plansSPT01 will likely be your largest directory table- DB2 9 Plan Stability feature could triple the size
of SPT01APAR PK80375 enables compression
– Apply maintenance – Set COMPRESS_SPT01=YES in DSN6SPRM– Reorganize
Compression ratios as high as 60 – 80%Proactively manage the contents of SPT01 to
eliminate outdated packages- Package information is in catalog and can be
used to identify packages that should be freed
SKCT
EDMPOOL
PT
PT
PT
SKPTSKPT
PT
Presenter
Presentation Notes
Packages should be your standard for DB2 applications . IBM has already disclosed that DBRM-based plans will be deprecated in the next DB2 release. SPT01 will likely be your largest directory table space and you most difficult to manage. IBM introduced a new features after DB2 9 became available to allow you to save previous versions of the access path for a specific package. You can save the immediately previous copy or the original copy you started with. You are then allowed to fallback to a previous version if the current access path isn’t working for you. This is all controlled using new bind parameters. Information about these other ‘copies’ of the package is maintained in SPT01. If you decide to turn this on DB2 subsystem wide you will see a tripling in the size of the SPT01 table space. APAR PK80372 enables compression of SPT01. You can apply the APAR and then reorg the tablespace to implement compression. Using DEGREE (ANY) in your bind package means that DB2 will maintain two copies of the access path in SPT01. This will increase the size of SPT01 as well. The last bullet on the chart is just discussing a general rule that you clean up packages that are no longer valid or being used. This can help free up space in SPT01. The chart on this page show the operational view of the EDMPOOL and is highlighting the PTs that are currently loaded in the EDMPOOL.
COPY- Create backups of SCT02 and SPT01 on a regular basis
REORG- Use SHRLEVEL REFERENCE for directory objects- Make image copies before and after the reorganization- When should you REORG?
Reorg these tablespaces when the associated catalog tables are reorganized– DSNDB01.SPT01 when DSNDB06.SYSPKAGE is reorganized– DSNDB01.SCT02 when DSNDB06.SYSPLAN is reorganized
For these two spaces use the same metrics you might for applications tablespaces and indexspaces
– PERCDROP in SYSIBM.SYSTABLESPART– LEAFNEAR/LEAFFAR in SYSIBM.SYSINDEXPART – NEARINDREF/FARINDREF in SYSIBM.SYSTABLEPART– CLUSTERRATIO (not an exhaustive list)
Presenter
Presentation Notes
Utility considerations for SCT02 and SPT01 directory tablespaces. COPY – Copy them on a regular basis as you would the other catalog and directory spaces. REORG – Image copies before and after a reorganization. Schedule your reorganizations at the same time you reorganize your associated catalog table space. For SPT01 the related tablespace is DSNDB06.SYSPKAGE and for SCT02 is DSNDB06.SYSPLAN. The last section here is just saying you can use the ‘normal’ statistics indicators to determine a reorg is indicated. These statistics are on the catalog tables.
Physical size of the pagesets for these structures are all calculated during DB2 installation (DSNTIPD) using values you plug into a formula- New PLANMGMT options that can dramatically increase the size of SPT01- Resizing these directory objects can be done by:
REORG with UNLOAD PAUSEIDCAMS DELETE/DEFINEREORG with UNLOAD CONTINUE
- DFDSS or IDCAMS can be used standalone as well
Operational structures like the DBD Pool and EDM Pool are controlled by DSNZPARM values and these are also calculated during initial installMonitor the statistics related to how often these structures have to be loaded from disk into the different pools- Look at hit ratios and if too low consider increasing the size of the pools- Shooting for high hit ratios (80% or higher) for DBDs, SKPTs, SKCTs
Presenter
Presentation Notes
This chart discusses some performance considerations for DBD01, SCT02, and SPT01 Physical sizes of these directory table spaces are calculated during DB2 installation. The installation panel DSTIPD uses your input to determine the size of the catalog, directory, and log datasets. If you turn on PLANMGMT (Plan Stability) in your DB2 subsystem you may run into issues with the size calculated at initial installation. Increasing the size of SCT02 and SPT01 can be done using the REORG utility or you could use DFDSS or IDCAMS to increase the size. From an operational point of view, it’s more important to monitor the EDMPOOL to make sure they are performance well. These sizes are also calculated during installation but can be changed. Monitor the hit ratios for your DBD, PT, and CT pools to make sure the number of loads from disk are small. You want to avoid reloading these structures from disk. If this is a problem modify your DSNZPARMs to increase the size of these structures. All of these structures are above the 2GB bar.
Stores recovery log ranges that record the time an index space (COPY YES) or table space opened for updatesSYSLGRNX – System Log Range Table- Records intervals on the DB2 log where updates for an object may occur
When a DB2 object is opened for update and subsequently closed that interval is recorded
- Primary goal is to minimize amount of log data that must be processed for a recovery
- Part of recovery assets required to complete the recovery processDB2 active and archive log datasets that map to SYSLGRNX log rangesImage copy datasets required
Presenter
Presentation Notes
SYSLGRNX has been around in DB2 for many years. The objective is to record the ranges of log records that are necessary to recover a specific DB2 object. This can limit to amount of data that must be processed in the event of a recovery operation. The System Log Range Table records intervals on the DB2 log where an object is opened for update. When the object is closed that interval is completed and externalized to SYSLGRNX
Pageset DSNDB01.SYSLGRNXTable name SYSIBM.SYSLGRNXAccessed through two indexes- DSNLLX01 (LGRDBID,LGRPSID,LGRPART,LGRMEMB, LGRSLRSN)- DSNLLX02 (LGRDBID,LGRPSID,LGRSLRSN)
Entries stored in a fixed length record
Log Range Entries
DM RecordPrefix – 6 bytes
ModificationDate/Time
Starting/StoppingRBAs
Starting/StoppingLRSNs
DBID/OBIDIdentifier
Presenter
Presentation Notes
This chart shows the technical implementation of the SYSLGRNX table records created for each log range for a specific tablespace or partition . There are two indexes on SYSIBM.SYSLGRNX used to access this table.
Use the RECOVERY option of the REPORT utility to see the log range entries for a specific objectAn example:
Presenter
Presentation Notes
This is the report provided by the IBM REPORT RECOVER utility. This is the only way to see the entries in the SYSLGRNGX table for specific objects. In this example, we see the SYSLGRNG records for a specific tablespace DBUV0101.TPUV0101. In this case we can see that we’re looking at a partitioned tablespace. Notice we have START and STOP RBA columns. These generally apply to local recoveries. The START and STOP LRSN columns are there datasharing environments.
Entries will grow over time and the table will become quite largeUse the MODIFY RECOVERY to manage SYSLGRNX entries - Deletes SYSLGRNX and SYSCOPY rows from a single partition or an entire
tablespace (DSNUM option)- SYSLGRNX rows where there are no SYSCOPY rows - Recovery rows for indexes- Also reclaims space in the DBD
Sample Utility Control Statement- Deletes SYSCOPY and SYSLGRNX records written before Sept 10,
2002
Presenter
Presentation Notes
SYSLGRNX can grow over time and become very large if not cleaned up periodically. Use the MODIFY RECOVERY to manage SYSLGRNX entries. This utility will: Delete SYSLGRNX and SYSCOPY rows for the entire tablespace or for a single partition, Eliminate SYSLGRNX rows for which there are not SYSCOPY rows, Delete recovery rows for indexes, Reclaim space n the DBD for tables that have been dropped. The last bullet describes the Modify Recovery syntax used to delete rows for a specific object in a given range.
Stores status information about DB2 utilities that are active or stoppedHere’s what happens
1. Utility begins and is registered in the SYSUTIL table2. As the utility moves from phase to phase a new record is added for each new
utility execution step.3. When the utility completes all the corresponding entries in the SYSUTIL table
are deleted.4. If the utility stops during execution all the records remain till the problem is
resolved
Presenter
Presentation Notes
SYSUTILX is a DB2 utilities register. When a utility starts an entry is placed in SYSUTILX recording that status of the utility. As the utility moves from phase to phase a new record is added for each new utility execution step. When the utility completes normally all rows are removed. If the utility stops execution the records will remain until the problem is resolved.
DISPLAY UTILITY command is the easiest way to look at what utilities are running in your system.- DISPLAY UTILITY (UTILID) or * or partial key
Output will vary based on the type of utility process is runningAn example:
Presenter
Presentation Notes
The only way to see the utility status is using the DISPLAY UTILITY command using the syntax described in bullet one of the chart. O can display a specific UTILID, use a partial key, or specify * to see all utilities current executing or stopped. The box at the bottom of the chart shows the output of the –DISPLAY UTILITY command. In this case we see an UNLOAD utility that has stopped.
Unique characteristics for this directory tablespace- One of three catalog/directory objects where copies are not recorded in SYSCOPY but
on the logCopy considerations- To copy DSNDB01.SYSUTILX the copy must be the only utility in the job step- A SHRLEVEL REFERENCE Copy requires no other utilities running in that data
sharing group- An “exclusive” utility that can interrupt other tasks that may be running
No reorganizations possibleMust be the first tablespace recovered if recovering multiple catalog/directory objects- Recovery is not restartable so SYSUTILX must be re-initialized - May require manual resolution of objects that are in “restricted” status because
of a “stopped” utility
Presenter
Presentation Notes
SYSUTILX has a few operational considerations. First, this directory tablespace is one of those where the copy information is not recorded in the SYSCOPY table. Information about these copies are written to the DB2 log. A recovery of this tablespace would require accessing the log to acquire the information. No reorganization are possible. I you must recover multiple catalog/directory objects then SYSUTILX must be recovered first. Many customers don’t plan to recover the SYSUTILX. This just reinitialize the tablespace if there is a problem. If this occurs manual resolution of objects that in a restricted status may be required if they were ‘stopped’ .
DB2 10 Impact on Catalog and DirectoryA Brand New World
Catalog and Directory restructured- No more links (and no more DSN1CHKR)- Partition by growth tablespaces- Improved concurrency and decrease deadlocks and timeouts for DDL, utilities, and
bind processes- Online Reorg - DB2 Managed using SMS- Row level locking- DB2 managed RI
Other Changes- Select from SYSLGRNX- SPT01 Compression Removed
Presenter
Presentation Notes
IBM has already announced much of the content of DB2 10, expected late this calendar year. This chart doesn’t provide a lot of detail on what’s being done but there will be much more detail as we get closer to GA. This information was extracted from publicly available IBM presentations. There will be major impacts to the Catalog/Directory. Generally the DB2 catalog and directory objects become like any other DB2 application object is managed by DB2. This means: No more links; therefore, no more DSN1CHKR, PBG (Partition by Growth) tablespaces used for catalog/directory objects, Reduced concurrency issues like deadlocks and timeouts for DDL, utilities, DML, and bind processes, Online reorg will be available, DB2 managed RI. You will also be able to SELECT from the SYSLGRNX table directly. Finalize, the move to PBG tablespaces will eliminate the issue with >64GB size limitations. So the compression possible in DB2 8 and 9 will be eliminated.
This slide is designed to be an opening or closing slide. This will allow the presenter to have a presentation cued up in slideshow mode without being on the title slide. The audience can take their seats, leave, or have open discussion with this slide up.
