db2Dean’s 2009 IOD Highlights

20 November 2009

The Information on Demand (IOD) conference was great again this year.  In addition to being able to renew my deep and meaningful relationship with Marilyn Monroe I learned lots of great things about DB2, especially the newest versions.  In this month’s article I’ll list the variety of random gems that I found.  There was much more information than what I can show here, but I have still included several useful items.  By the way, I was on the IOD site yesterday and noticed that most of the presentations are there now.  If the one that you wanted was not there before, it is probably on the conference web site now.  If you would like to have someone bring any of these presentations to you, just let me know.  I look forward to seeing you and Marilyn again at next year’s conference!

DB2 Connect

One of the coolest things that I learned at the conference is that if you use DB2 Connect or the JDBC driver to connect to DB2 on z/OS or i5/OS then you can access non-DB2 data through stored procedures.  Anything that you can program in a stored procedure you can access through DB2 Connect by calling stored procedures from your application.  For example, on z/OS you can access VSAM, CICS, IMS, and MQ data by calling stored procedures that you have written running in DB2/zOS that call the appropriate functions.  This makes it very easy to access these data sources for small to moderate volume applications.  For high-volume applications IBM provides other tools.

In DB2 Connect v9.7 and its related JDBC driver you can also consume industry standard Web Services.  Again on any flavor of DB2 databases there are functions that you can call from stored procedures.  Some functions provided allow you to call web services.  Conversely, you can use DB2 Connect or your appropriately licensed JDBC driver to easily allow stored procedures on DB2/i and DB2/z to be called from web services.  You can even use IBM’s free Data Studio tool to create those web services that call your stored procedures.

I also learned that the connection concentration feature has been available through the JDBC driver since a later fix pack of DB2 Connect v8.2 for connections to DB2/zOS!  This means that you no longer need to run a DB2 Connect server to concentrate a large number of connections into a few static connections to your DB2/z database server!  Unfortunately, this feature is not yet available for DB2/i

DB2 V9.7

Several presentations discussed the new features of DB2 v9.7.  Here is a smattering of interesting ones:

• Most applications written for Oracle Databases can now run directly against DB2 databases using the new SQL Compatibility feature.
• Index, Temporary Tables, LOB and XML compression added
• Replicate Compressed tables
• ADMIN_GET_INDEX_COMPRESS_INFO to estimate index compression savings
• Allows Tablespaces to be converted to Automatic Storage
• Scan Sharing that allows multiple independent queries to use the same set of pages being read by the prefetchers.
• ADMIN_MOVE_TABLE stored procedure allows you to move a table from one tablespace to another while continuing full read and write access to it.
• Local Indexes for Range Partitioned tables
• WLM Priority Aging – Define time thresholds for multiple classes.  EXAMPLE: Query starts in class with most resources after a defined amount of time moves to medium class and finally put in the dog class.
• Large and temporary tablespaces can now be even larger
• Statement compression allows statements that vary only by the literals in the predicate to be consolidated into the same statement in the package cache. 

Coming in V9.7 FP1

• HADR will allow a Read-only Standby
• New lightweight event monitor
• New Administrative Views
• Db2updv7   -- used to make these views available after a version or fix pack upgrade.

Administrative Views

At this presentation I got a better understanding of how the table functions and administrative views work under the covers.  For more information about what you can do please see my previous DB2 Administrative Views article.  Chris Eaton gave several examples of using the views in the presentation and I have added them to the end of my DB2 Administrative Views article as well.  One more interesting thing to note is that since administrative views are built on table functions, it is more efficient to use the table function if you are using a “where clause” to only get some information returned.  This is because when you use a view, all rows from the table function are returned to an intermediate result set and then the view just picks the ones you specify in your where clause.  If you use the table function directly, then you only extract the rows you need in the first place. 

HADR

The DB2 HADR session was a great review and I also got a few new gems about that feature:

• BLOCKNONLOGGED – Always set this parameter to YES in HADR environments!  It prevents users from updating data in NOT LOGGED and NOT LOGGED INITIALLY transactions and certain types of loads.  This is very important because when someone uses one of these types of transactions it makes the entire tablespace on the HADR secondary unusable and no one will know until you need to fail over.  The new DB2 v9.7 “truncate table” command works fine without causing problems for HADR.  This DB CFG parameter was introduced in DB2 v9.5 fix pack 4 and V9.7 GA.
• LOGINDEXBUILD – Always turn this parameter on for HADR systems so that new indexes that you create on the primary get replicated to the secondary.
• In V9.7 fp1 you will be able to use the secondary in read only mode to off load reporting.  NOTE WELL: you may need to license more DB2 if you use that feature.
• Improvements have been made to Automatic Client Reroute as of V9.5 fp1.  ACR allows DB2 Clients to find the secondary without catalog extra nodes or using a virtual IP address.
• DB2 v9.5 introduced the db2haicu facility to set up automatic fail over of HADR databases without having to know how to configure Tivoli System Automation.
• Tivoli System Automation is not automatically installed on Windows systems because most people use the free MSCS that they get with Windows. 

Self Tuning Memory Manager (STMM)

Robert Donaldson who is a Database Architect at a company that does very high-end DB2 processing gave this presentation about his use of STMM at his company.  Sam Lightstone from the IBM Lab also gave a description of the feature.  Although STMM has been around since DB2 v9.1 and I’ve read a lot about it I learned some new things at this superb presentation.

• The STMM uses economic modeling to determine the greatest cost/benefit of memory changes combined with control theory to prevent oscillation.
• STMM keeps a detailed log of what it is doing.  You can parse that file using the parseSTMMLogFile.pl script and put the output into a spreadsheet and graph what STMM is doing.
• STMM is useful even on well tuned databases, because it constantly tunes memory for the current workload and not a static average workload like manual tuning must do.
• When creating a new database on a large system and using automatically sized bufferpools, go ahead and tell DB2 to start with a larger bufferpool size so that initial performance is not too bad.  Here is an example:

CREATE BUFFERPOOL DEANS_BP SIZE 655360 AUTOMATIC PAGESIZE 4K

• Consider using the DB2 Workload Manager instead of using fixed sized bufferpools to ensure certain bufferpools get priority.

DB2 9.7 Concurrency

DB2 v9.7 added the Currently Committed semantic to the Cursor Stability isolation level.  If you enable this feature and you try to read a row that is locked by an update transaction, DB2 will retrieve the original version from the log buffer and keep going instead of waiting for the lock to be released.  This can significantly improve performance in some applications that experience a high lock wait time.

• To see how frequently your database is looking at the logs for currently committed transaction execute the “db2pd –d <dbname> –logs” command. 
• Set the registry variable “DB2_SKIPINSERTED=ON” in v9.7 and earlier versions to allow queries to skip rows that have been inserted but not committed yet.
• Set the registry variable “DB2_SKIPDELETED=ON” in v9.7 and earlier versions to allow queries to skip rows that have been deleted but not committed yet.
• Set the registry variable “DB2_CAPTURE_LOCKTIMEOUT=ON” starting in v8 fp16, v9.1 fp4, v9.5 and v9.7 to report detailed lock timeout information in files in the diaglog path.
• If you need some transactions to use the old locking behavior, then you can set the “WAIT FOR OUTCOME” connection in JDBC and CLI drivers.

DB2 Audit

Starting in DB2 v9.5, the audit functionality was significantly improved.  One of the major improvements involves allowing more specificity in what gets audited, so that you can get all information that you want without having to collect too much extraneous data.  For individual databases, objects or individuals within a database you can collect everything from just who is connecting all the way to collecting all queries launched against the database. 

• User with SECADM authority now creates audit policy objects in a database using the “CREATE AUDIT POLICY” statements.
• Stored procedures are provided for SECADM to extract audit data.
• The db2audit command is used for several audit actions including starting and stopping auditing, and showing audit configuration.
• Trusted Context should be used when auditing so that you know who is really using your database.
• Audit records are written to the active audit log file and that log should be archived regularly using the SYSPROC.AUDIT_ARCHIVE stored procedure.
• Archived files can be exported to delimited files using other stored procedures.  Those delimited files can be imported into spread sheets or database tables used for reporting.  There is also a table function to view the archive logs using SQL.

DB2 9.7 Upgrade

If you are one of my customers and would like a presentation about upgrading to DB2 v9.7, please contact me.  Here are a few interesting facts from the presentation:

• There is an upgrade manual and a website devoted to upgrading.
• You should convert all type-1 indexes to type-2 indexes before upgrading because type-1 indexes are no longer supported.
• Direct upgrade from v8 and all later versions are supported.
• The db2ckupgrade command replaces the old db2chkmig command of prior releases.
• When using V9.7, you should double your LOCKLIST size unless you are not using STMM which will increase it for you.
• DB2 v9.7 will consume somewhat more log space because additional information is now kept in the logs to allow the currently committed semantic.

***

I was exposed to much more information than what is in this short list, but as usual attending this conference was like drinking from a fire hose.  I need to continue to review the presentations to absorb more.  This list just contains some of the items that I saw that I found particularly useful. 

HOME | Search