What to expect in V8

'Tip of the iceburg' on what to expect in DB2 Version 8

Terminology Changes:

Long Tablespace Large Tablespace
Online index Reorganization Online Index Defragmentation
Country Code Territoty Code
Online Index Reorganization(New)
Materialized Query Table (New)


Availability

Online REORG:


ò Applications can access the table when the table is being REORGed.
ò Can be paused and resumed

Limitation: Type 2 Indexes on the Table and no extended indexes

Online LOAD:
ò Lock at the table level
ò Pre-exisiting data can be quried when using the option READ ACCESS in the LOAD Command
ò LOCK WITH FORCE option releases locks held on the table by other applicatios
ò Using the Filetype CURSOR allows a select query to be specified as source without the necessity to EXPORT data to a file for LOADing
ò LOAD QUERY provides information on the status of the tablespace
ò
Limitation: Type 2 Indexes required on the Table


Online configuration Changes:

ò A number of parameters can be changed without the need to disconnecting the applications. (using IMMEDIATE option)
NOT all configuration parameter are online.
ò The configuration changes can be deferred to the time of next application restart (using DEFERRED Option)
ò Certain parameters can be configured as AUTOMATIC to allow DB2 to decide on the optimum values (eg. Instance_mem, maxappls)


Online Bufferpool Maintenance:

ò The addition of a new bufferpools or alteration of existing bufferpools can be done without the need to stop/start the database
ò The memory of a dropped bufferpool is made available immediately

DMS Containers Maintenance:
ò DMS containers can be added, existing ones removed or altered when the applications are using the data in the tablespaces

Index renaming

 Indexes can be renamed using the RENAME INDEX Command

Maintenance
Flush Package Cache

 Invalidates the SQL Statements in the package cache
 Very useful if parameters influencing the access path of a query are changed dynamically

Logging:

 The db cfg parameter mirrorlogpath defines the location of the second set of log files when dual logging is used
 Maximum log space is increased to 256 GB(from 32 GB)
 Infinite active logging can be activated by setting logsecond to û1
 blk_log_dsk_full has been made a dbm cfg

Backup/Recovery:

 A backup taken on a system with one code page can be restored to a system with a different code page
 When recovering tablespaces, log files that are not required are skipped
 Point-in-Time Recovery command can be issued by specifying a local time

Administration Notification Log:

 A new log for use by system and database administrators useful in problem determination
 Level of messages can be controlled by NOTIFYLEVEL

Multiple Service Levels
 Multiple Service Levels(Fixpacks) of DB2 can run simultaneously on the same server

Version ID of packages:

 Multiple Versions of the same package can co-exist on the database

QUIESCE:

 Database/Instance can be quiesced in maintanence mode providing administrators to perform maintanence activities on the system
 UNQUIESCE Command

REORGCHK:

 Tables in a particular schema can be reorganized

RUNSTATS:


Null and Default Compression:

 VALUE COMPRESSION clause in the CREATE TABLE Statement in an efficient format
 COMPRESS SYSTEM DEFAULT option stores the system default values in a compressed format
These measures save disk space especially for large tables

AUTOCONFIGURE Command:

 This command can be used to get recommendations for optimum configuration parameters, bufferpool sizing etc
 This option can also be used as a part of the CREATE DATABASE Command

DAS

 Is no longer an instance but a separate server process that supports TCPIP communication

Documentation

 HTML Documentation is a separately installable feature
 Regular Updates of HTML and PDF documentation

INSPECT Command:

 Can be used to check the architectural integrity of the database when the database is online

Applications and Performance
UNION ALL Views:

 Possible to insert into UNION ALL Views

Limitation : ôPartitioning KeyÆ cannot be updated


JDBC Drivers :
 Support for Type 4 JDBC Drivers
 Type 3 JDBC Driver is deprecated but supported in this version
 Type 2 driver is J2EE Certified

Multidimensional Clustering:

 The data can be clustered on more than one column
 Data and Index Maintanence is easier

Block Based Bufferpools:

 Multiple pages are read from the disk into the Bufferpool

Threading of Java UDFs and Stored Procedures:

 Thread-based model results in tremendous performance improvement in sites running numerous routines
 Routines can also be defined as thread-safe



New Tools:

 Storage Management Tool
 Health Monitor
 Configuration Assistant Replaces the Client Configuration Assistant
 Development Centre Replaces Stored Procedure Builder


INSTEAD OF Triggers:

 Updatable Views (INSERT, DELETE and UPDATE) using INSTEAD OF Triggers
 INSTEAD OF triggers are always FOR EACH ROW Triggers and can be created only on views
 Fired for update on any column

Limitation:
 Cannot have a WHEN clause in triggered action
 Cannot be created on a Symmetric View
 INSTEAD OF triggers on Updatable Cursors will fail

Informational Constraints:
 Not enforced by the database manager
 Used in Query Rewrite to Improve Performance

CLI LOAD Functionality:

 CLI provides a new interface to issue LOAD from within a CLI program

Declared Global Temporary Tables:
 Indexes can be created on these tables
 RUNSTATS can be done to improve performance
 Undo logging to support the rollback of data changes

Web Services
 DB2 can be accessed as a WebService provider
 Can be used through DB2Æs document access definition extension(DADx)

XML Support:

 Schema Validation user defined function
 REC2XML and COLLATTVAL functions

Type 2 Indexes:

 Type 2 indexes improve concurrenct bt avoiding next-key locking
 Type 2 index can be on a table column greater then 255 bytes in length
 Type 2 indexes cannot be mixed with type 1 indexes in a table
 All new indexes built in V8 are of type 2 except on tables which have type 1 indexes(type 1 indexes and type 2 indexes cannot be mixed in a table)
 To convert Type1 indexes to type 2 , REORG INDEXES can be used
 INSPECT command can be used to find out the type of index defined on a table



Monitoring

Event Monitor:
 Event Monitors can write the monitored data directly into tables
 Provided more detailed information about the SQL Statements involved in a deadlock

Snapshot Monitor:

 Can now be retrieved using new table functions which allows to use the features of SQL to filter and join data