Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Tips and Tricks

What to expect in V8 by sathyarams
Posted: 31 Dec 02

'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)


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

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


    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


    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


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


    Tables in a particular schema can be reorganized


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


    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


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


    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

    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

    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


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

Back to IBM: DB2 FAQ Index
Back to IBM: DB2 Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close