Lesser Known Features of DB2 v9.1
21 July 2008
By now you have probably heard of the major new features introduced in DB2 v9.1 such as pureXML, Deep Compression, Self Tuning Memory Manager, Automatic Storage, LBAC and Table Partitioning. However, DB2 v9.1 also included many new features and changes that are quite useful on a day to day basis. In this document I’ll present a brief summary of the ones that I think are important, but get less press. All of the new features including the ones listed here are described in the What’s New manual for DB2 v9.1. I highly recommend looking it over for the full list of new features as well as for reading more about the features that interest you most. If you would like more information about any of these major or minor features, please write me at firstname.lastname@example.org
Administration Through SQL: There are several new ADMINISTRATION routines that allow you to administer your database using SQL. For example you can now reorganize a table using SQL.
Large Tablespaces: In DB2 V9 you should start creating most of your general purpose DMS tablespaces as LARGE instead of REGULAR. CREATE LARGE TABLESPACE allows your tablespaces to grow much larger, allows more than 255 rows per page and positions you to use all new tablespace features in future releases.
Install in any Directory: You can now choose where to install DB2 and can have multiple copies with different fix packs. This differs from the “alternate fix pack” feature of previous versions. The “db2ps” command can be used to show all of the various installations.
Exception Collection: The “db2cos” script will now automatically be run whenever the instance experiences a panic, trap, exception, or segmentation violation. This information will allow support to more quickly diagnose problems in your environment on the off chance that you experience one of these conditions. These “features” really lie mostly in the realm of Mr. Gate’s and Mr. Ellison’s products, but we provide this script in case something else causes a problem for DB2!
Backup and Recovery
Restart Interrupted Recovery. If your RECOVER DATABASE command failed in the roll forward phase you can now often restart it where it left off rather than starting the whole thing over.
Create Redirected Restore Script from Backup File: You can now run a command against a backup file to generate all of the SET TABLESPACE commands that are needed when you want to restore a database to a new location. This is especially useful when you don’t know the names of all of the table spaces or there are many tablespaces in the database.
Restore Database from only Tablespace Backups: Prior to DB2 v9.1 you could not just take a set of tablespace level backups and restore them. You needed a to start with a full database level backup, because certain small, but necessary files were only backed up in a database level backup. Now those files are placed into tablespace level backups as well.
Performance and Tuning
Statistics on Views: You can now tell DB2 to collect statistics on views to make queries on those views run faster
Automatic Runstats on by Default: When you create a new database in DB2 v9.1 Automatic Statistics collection will be turned on by default for all tables.
Create DB with no Public Privileges: The CREATE DATABASE command now has an option called RESTRICT. When used, this option will create the database without granting any access to PUBLIC. Without this option PUBLIC has some privileges including CONNECT.
SECADM Authority: A new authority has been added called SECADM that has several security privileges granted to it.
Move Copy and Rename Improvements:
Copy Database Schemas and Create Model Schemas: Using the “db2move –co” command or the ADMIN_COPY_SCHEMA you can copy a schema and its related objects from one database to another. You use a model schema as a template for creating new schemas.
Change Ownership of Objects: Using the “TRANSFER OWNERSHIP” SQL command you can now change the owner of many objects including tables.
Load Utility Improvements
Load from a Script: The load utility now allows you to specify the SOURCEUSEREXIT option that allows you to load from a script or executable rather than a file. This can be faster than creating the file first.
Load Partitions Directly: You can now tell the load utility to place partitioned data into the correct partitions with out an external utility such as autoloader.
No Longer Available
Text and AVI Extenders: Gone
Alternate Fix Packs: Replaced by the “Install in any Directory” functionality described above.
Auto Loader: The functionality of this utility is now in the load utility