DB2 9 – some interesting features

The recently announced DB2 9 – don’t call it Version 9 or you won’t sound like you know what you’re talking about – has some excellent new features and I’d like to highlight some of them in this blog.

DB2 9 is meant to improve on Version 8 in areas such as performance, scalability, and security – although you would expect any vendor to promise their new software does these three things. The big deal is XML and accessing data, which I blogged about at the end of July. In this blog I want to talk about some of the other features.

First off, let’s be clear, DB2 9 runs on Linux, Unix, Windows, (LUW) and z/OS. So here are some of the things that make DB2 9 stand out – and which you might not have noticed in the original announcement letter.

There are new methods for copying and moving schemas and changing the ownership of a table. For example, to copy a single schema within a database, use the SYSPROC.ADMIN_COPY_SCHEMA procedure. To copy multiple schemas within a database, use the SYSPROC.ADMIN_COPY_SCHEMA procedure multiple times. To copy/move a single or many schemas from one database to another, use the db2move utility with the -co option.

There is a new option with the CREATE DATABASE command that allows users NOT to automatically grant the select privilege on certain objects to public. This can be done using the RESTRICTIVE parameter. For example:

>db2 create db testdb restrictive

Using the new RESTRICTIVE parameter should make a database more secure and remove the need to issue the revoke from PUBLIC commands.

DB2 9 for LUW provides a set of administrative views that can be used in conjunction with the table functions (from V8). While not replacing these table functions, they can offer an easier way of accessing the underlying information. There are 59 administrative views – for some users have to be connected to a specific database and others they don’t.

DB2 9 for LUW has 227 registry variables. (Note: DB2 V9 FP10 had 216). There are 14 new registry variables and three have gone – DB2_STATVIEW, DB2_USE_LATCH_TRACKING, and DB2SYSPLEX_SERVER. The introduction of the table function to display the registry variables has made it easier to keep track of these values.

DB2 V8 let users drop a column using the ALTOBJ procedure. DB2 9 provides the ALTER TABLE command. Users can continues using ALTOBJ, but should code ALTER TABLE on all new commands.

For db2pd there are three new parameters: -pages, -memblocks, and -fmp (that makes 25 in total). -pages displays the pages in the buffer pools; -memblocks displays information about the memory pools; and -fmp displays information about a process in which fenced routines are executed.

There is also a new command, db2pdcfg, which is used with the -catch parameter that allows users to issue other commands from a batch file once an event has occurred, for example a timeout.

There we are – just a smattering of what IBM has put into DB2 9. My particular thanks to C Leonard for drawing my attention to them.

BTW: anyone wishing to contact me directly can get me on TrevorE@xephon.com.