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!
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
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
- 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
- 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.
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.
The DB2 HADR session was a great
review and I also got a few new gems about that feature:
– 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
- 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
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>
- 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.
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”
- 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.
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
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
- 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