Iqbal Goralwalla, Principal Consultant at Triton Consulting, was again invited to present on the DB2Night Show webinar. A staggering 100% of the audience who watched said they learned something from Iqbal’s presentation on the subject of DB2 LUW 9.7 Fixpack "Pearls". We share these pearls of wisdom with you below, on the new commands and features which are invaluable but could easily be missed.
db2dart
Given Iqbal’s previous DB2Night Show webinar was on db2dart, he thought it apt to start with this feature. Fixpack 5 has enhancements to make db2dart more flexible and run faster. It’s now possible to inspect more than one table at a time with the /T parameter and the /OI option. Similarly, it is possible to inspect more than one tablespace at a time with the /TSC and /TS parameters and the /TSI option. This helps improve performance when inspecting tables.
The /QCK option improvements include skipping unnecessary steps by taking in numeric values that perform a different operation for each value, enabling DB2dart to operate faster and offer superior performance.
Storage Reclamation
Prior to DB2 9.7, releasing unused disk space for reuse was difficult due the tablespace High Water Mark (HWM). HWM is the highest numbered page that is currently allocated in a DMS tablespace. HWM could be much higher than the number of pages currently in use due to dropped tables or delete activity, offline table reorg using same tablespace, index reorg using "allow read or write access, etc. In DB2 9.7, there are no more HWM headaches for a DBA. Reclaimable storage can be used to return unused storage to the system for reuse. This is possible using the REDUCE MAX or LOWER HIGH WATER MARK options of the ALTER TABLESPACE command. Reclaiming storage is an online operation. Obviously it’s recommended to do this during periods of low activity.
Restore Privileges (also known as SECADM Musing)
In DB2 9.7, if a database backup taken by one user (say db2inst1) is restored on another system by a different user (say db2inst2), then one can expect a surprising SQL0551N error when db2inst2 attempts to access any tables in the restored database. Any attempt to grant permissions to the tables by db2inst1 also results in SQL0551N. The reason for this is that after the restore, db2inst1 and not db2inst2 is the SECADM. It so happens that in DB2 9.7, SECADM authority is required for security administration and it is the only authority that provides the ability to grant and revoke all authorities and privileges. This can be solved by using the DB2 registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITIES. If this is set to ON, and you are restoring to a new or existing database, then you will be granted SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities. Restoring production databases in a development/test environment is a very common operation necessary in many organisations – Scott Hayes, CEO of DBI and host of DB2Night Show described this point as ‘a nice find and a real pearl for Iqbal!’
Customising Application Connections
Another valuable pearl is the CONNECT_PROC database configuration parameter. This enables you to customise application environments by executing a stored procedure and the end of the connection process. Some examples include setting lock time out for a certain user/time frame, set isolation levels for certain users/time fram Note that all new connections will inherit the change. Existing connections use previous values. Therefore, this pearl should be used wisely and with care! However, one can be smart about the change by specifying user/time.
DB2 Caem – DB2 Capture Activity Event Monitor Data Tool
This tool uses an activity event monitor to capture detailed diagnostic and runtime information about one or more statements, then extracts and formats the information including section actuals which distinguishes it from other tools like db2exfmt.
db2pd
db2pd continues to be Iqbal’s favourite "free" tool! There are enhancements in almost every fixpack. With the tablespaces and trackmodstate option in Fixpack 5, you can perform intelligent backups. The feature shows which tablespaces have changed since the last backup. Load operations are now easier to troubleshoot with db2pd and it’s also simpler to determine if catalog database partitions have failed in DPF environments using the recovery option.
Other notable options in early DB2 9.7 fixpacks include wlocks (monitor applications with locks that are in lock wait mode), apinfo (capture detailed runtime information for a specific application or for all applications), reorgs index (display progress about index reorganizations, enhanced to display progress about index reorganizations for partitioned indexes as well), and runstats (display progress on table and index RUNSTATS operations)
List Aggregation Function
A common question is can we collapse rows of string columns into comma separated lists (for example, list all employees in a department)? In Fixpack 4, the LISTAGG function can be used to aggregate strings directly.
Fuzzy Joins
This is now available in DB2. A new feature for the LIKE predicate that makes it possible to use a column reference as pattern expression. This means the LIKE predicate can be used as a fuzzy join predicate.
LASTUSED
If you’ve ever been suspicious that your database has objects that have never been used, it’s now easy to be certain using last referenced date support for Tables, MQTs, Table Partitions, Indexes and Packages. Use the LASTUSED column in SYSCAT.DATAPARTITIONS, SYSCAT.INDEXES, SYSCAT.PACKAGES, or SYSCAT.TABLES to ensure good housekeeping and clean up your database.
HADR – SUPERASYNC
Introduced in Fixpack 5, the SUPERASYNC synchronisation mode of HADR enables transactions to never be blocked or experience elongated response times due to network interruptions or congestion. Transactions are processed quicker than in any other HADR synchronisation mode. Log gap between the primary and the standby databases might be relatively larger.
Iqbal signed off by reminding the webinar attendees that the ‘pearls’ are polished in later fixpacks and therefore it’s best practice to be on the most recent fixpack. He also stated that DB2 9.7 fixpack pearls continue to be available in DB2 10. To access the webinar and hear Iqbal’s recommendations in detail click here.