New Exciting Features in DB2 9.7

This is by no means an exhaustive list of new features in DB2 9.7. But, it is the list of features that are most important to me as a DBA or a developer.

Online Schema Changes

See Burt’s article on Online Schema changes.

CGTT – Created Global Temporary Table

Now, it is possible to create GLOBAL TEMPORARY TABLE so that they are in catalog instead of them only declarabale in a session. This has several benefits including – 1. CGTT under authorization controls, 2. Views, indexes and triggers can be defined on CGTT, 3. CGTT and DGTT can contain LOBS.

CREATE GLOBAL TEMPORARY TABLE DGTT_TABLE
(
   EMP_ID INTEGER,
   HIRE_DATE TIMESTAMP(3)‏
)‏

Parameter Markers

Parameters in a procedure can have default values.
You can skip parameters with default values in a CALL statement.
Named parameters allows values to be assigned by name in any order.

SSL Connection in DB2

DB2 allows to use SSL connection between a java client and server. Server and client configurations are required for SSL connection to take place.

Server Configuration

• Use the iKeyman GUI tool (comes with GSKit) to create a certificate database (a.k.a KeyStore file)
• Import the server digital certificate you purchased from a certificate authority (CA) into the certificate database
• Set the following DBM configuration parameters
  • SSL_SVR_KEYDB: Key store file
  • SSL_SVR_STASH: Stash file
  • SSL_SVCENAME: SSL port
• Optionally, select a ciphers suite
  • SSL_CIPHERSPECS: Allowed ciphers suite
  • SSL_VERSIONS: Allowed SSL/TLS versions
• Enable SSL communication for the instance
  • db2set DB2COMM=SSL or db2set DB2COMM=SSL,TCPIP

Client Configuration

• Use the ikeyman GUI tool to create a signer certificate database
• Import the public key of the certificate authority into the certificate database

Change in connection properties

      ....
      properties.put("sslConnection", "true");
      System.setProperty("javax.net.ssl.trustStore", "/home/db2inst1/client.jks");
      System.setProperty("javax.net.ssl.trustStorePassword", "myPassword");
      ....
      con = java.sql.DriverManager.getConnection(url, properties);

How SSL Handshake works?

• Client requests an SSL connection listing its SSL version and supported cipher suites
• Server responds with a selected cipher suite
• Server sends its digital certificate to the client
• Client verifies the validity of the server’s certificate (server authentication)
• Client and server securely negotiate a session key
• Client and server securely exchange information using the key selected above

XML Enhancements

Several new features for XML processing has been added. Few notables are:

• Full support of pureXML
• XML docs in XDA object can also be compressed
• Online reorg of XML indexes
• XML column can be used in UDFs (Not external UDF as you will still use XML as CLOB)
• Allow XML columns in a MDC table
• DECOMP from query CLP command : To shred XML documents. DECOMPOSE XML DOCUMENTS IN <select_statement>
  XMLSCHEMA <xmlschemaname>
• DECOMP from query Stored Procedure : sysproc.xdb_Decomp_XML_From_Query
• ADMIN_IS_INLINED and ADMIN_EST_INLINE_LENGTH functions can be used to know if XML or LOB is inlined and what is the length.

Examples of DECOMP:

DECOMPOSE XML DOCUMENTS IN 'SELECT DOCID, SALESDOC 
FROM ABC.SALESTAB' XMLSCHEMA ABC.SALES 
MESSAGES /home/myid/errors/errorreport.xml 

CALL XDB_DECOMP_XML_FROM_QUERY ('ABC', 'SALES', 
'SELECT DOCID, SALESDOC FROM ABC.SALESTAB',0, 0, 0, NULL, NULL, 1,  
:hnumInput, :hnumDecomposed, :herrorreportBuf);

Note: XDA = object where XML documents are stored that are not inlined in the base table

CLPPLUS vs SQL*Plus

DB2 has an equivalent of Oracle’s SQL*Plus which is known as CLPPlus. If you know how to use SQL*Plus, you will find it almost same as SQL*Plus to use.

PL/SQL in DB2

This sounds the most promising new feature of DB2 where you can use PL/SQL of Oracle in DB2. But, remember Oracle has more than 20 years of development in PL/SQL so there will still be some missing functionality but it will come in the product as time passes and based upon demands from people.

The PL/SQL compiler in DB2 is native and not some form of masking layer that will translate PL/SQL to DB2 SQL PL. This allows PL/SQL code to be profiled and source level debugging can be done.

List of DBMS packages supported:

• DBMS_OUTPUT
• UTL_FILE
• DBMS_ALERT
• DBMS_PIPE
• DBMS_JOB
• DBMS_LOB
• DBMS_SQL
• DBMS_UTILITY
• UTL_MAIL
• UTL_SMTP

List of PL/SQL Features supported:

• All language logic supported like IF, WHILE := etc
• EXCEPTION – Try/catch handling
• User Defined Exceptions – Define conditions with or without SQLCODEs
• Constant variables – Variables that cannot be set
• FOR over range – Step through numbers
• FOR over SELECT – Step through result set of query
• FOR over cursor – Step through result set of cursor
• %TYPE – Anchored scalar data types
• %ROWTYPE – Anchored row types
• BULK COLLECT/FETCH – Aggregate result set into array
• FORALL – Pipe array into SQL statement
• AUTOMONOUS transaction – Executes a procedure in an independent TX
• Anonymous block
• Scalar function
• Procedure
• Package
• Trigger
• SYNONYM ON PACKAGE

List of Oracle Types in DB2

• NUMBER
• VARCHAR2 – NULL = ”, trailing blank sensitive collation
• TIMESTAMP(n) – 0 (date + time) <= N <= 12 (date + time + picoseconds)
• DATE – Year to seconds, SYSDATE
• BOOLEAN – In procedural code
• INDEX BY – In procedural code
• VARRAY – In procedural code
• Row Type – In procedural code
• Ref Cursor – Allows passing, and predefining of cursors

Partial List of important Oracle functions in DB2

• TO_CHAR, TO_DATE, TO_TIMESTAMP, TO_NUMBER, TO_CLOB
• EXTRACT, ADD_MONTHS, …
• INITCAP, RPAD, LPAD, INSTR, REVERSE, …
• DECODE, NVL, LEAST, GREATEST, BITAND

List of Oracle SQL in DB2

• CONNECT BY
• (+)-join
• DUAL
• ROWNUM
• NEXTVAL/CURRVAL
• MINUS
• Unnamed inline views
• TRUNCATE table
• Public synonym
• CREATEd temp table

Separation of duties

Gone are the days when SYSADM used to be GOD in DB2. Not any more with separation of duties in place now.

• With the advent of SECADM (database level), implicit DBADM has been removed from SYSADM
• SECADM can be granted to ROLE and GROUPS now
• SECADM can now grant/revoke database and object auths
• DBADM can be setup in such a fashion so that it can not see data and do grant/revoke
• DBADM does not get secondary grants implicitly
• New Authorities are created such as EXPLAIN, DATAACCESS, ACCESSCTRL, SQLADM, WLMADM

Automatic Storage

Several new features are added to automatic storage in DB2. Few notable are:

• You can alter a database to add additional storage paths but they are used when existing storage paths become full. What if you want to strip data across all 4 storage paths immediately to gain parallelism. You use ALTER TABLESPACE myts REBALANCE to rebalance the data. You can use ALTER TABLESPACE myts REDUCE command to reduce the high water mark.
• You can DROP a storage path by using ALTER DATABASE DROP STORAGE PATH ON ‘path1’. The storage path is changed to DROP PENDING and all table spaces are marked as An underlying storage Path(s) has been dropped. The DROP PENDING storage path is removed when all table spaces using it are rebalanced using ALTER TABLESPACE ts1 REBALANCE
• ALTER TABLESPACE ts1 REDUCE command will free trapped spaces which did not allow high water mark to come down in earlier DB2 versions.
• You can convert NON AUTOMATIC STORAGE database to AUTOMATIC STORAGE (AS) database.
  • ALTER DATABASE ADD STORAGE is supported on non-AS databases
  • Above command adds a storage pool for the database
  • This allows creation of new table spaces with automatic storage
• You can convert existing non-AS DMS table space to an AS table space
  • ALTER TABLESPACE … MANAGED BY AUTOMATIC STORAGE
  • The new growth comes from new storage paths added to the database
  • Old containers can be removed via DROP or REBLANCE.
• REDIRECTED RESTORE can be done from old DMS to new table space with automatic storage.

ONLINE TABLE MOVE

The online table move can be used in several circumstances as mentioned below:

• You want to do an ONLINE REORG or REDISTRIBUTE
• Want to do ONLINE TABLE compression
• Change pagesize ONLINE
• Online conversion to LARGE table space
• Move data/index/long data to new/different tablespaces
• Add or remove column, change column data type, enlarge columns
• Add / change MDC dimensions, range partitioning or partitioning key

The ONLINE TABLE move is accomplished by system stored procedure ADMIN_MOVE_TABLE(). This stored procedure allows to move data from an existing table to a new table object. When the data is moved, access to the source table is still available for SELECT, INSERT, UPDATE and DELETE operations.

This stored procedure has 4 phases.

1. INIT: Select copy index, create triggers, create target and staging tables. Triggers are created to capture changes to the source table.
2. COPY: Row by row copy (over index) of rows from source to target. LOAD may also be used optionally.
3. REPLAY: Rows captured in the staging table are re-copied from the source table. It may require multiple iterations, depending on transaction volume.
4. SWAP: Ready target table for swap – create indexes, statistics, optional REORG, etc. Source table is locked in share mode for final replay. Source table is renamed, target table renamed to original source name.

COMPACT MDC Tables

The MDC tables in DB2 are very unique and normally they do not require REORG if no DELETE are happening. The DELETE operation can make a MDC table sparse and how to get those unused pages back to the table space so that they can be used by other tables.

REORG TABLE mdctable RECLAIM EXTENTS ONLY

The above command is very fast as it is really not a reorg but marks empty blocks as unallocated in MDC table’s block map and in table space SMPs.

Inline LOBS

Take advantage of compression by inlining LOBS. This is how this can be done.

CREATE TABLE … EMP_PHOTO BLOB(10MB) INLINE LENGTH 20000;
ALTER TABLE … ALTER COLUMN EMP_PHOTO SET INLINE LENGTH 20000;

LOBs greater than inline length will be stored on LOBS table space but others will be stored inline alone with regular table data in the data page thus making it eligible for the table compression. There can be a mix of inline or non-inline LOBS in a table.

DB2 can still put LOBS inline even if no INLINE LENGTH is set. It will happen when a LOB’s actual length is less than the size of its descriptor.

Local Range Partition Indexes

DB2 had global indexes on range partition tables. Now, you can have local indexes on range partition tables leading to faster Roll-in and Roll-out of the data.

C:\>clpplus db2admin/password@localhost:50001/SAMPLE
Database Connection Information

Hostname = localhost
Database server = DB2/NT  SQL09070
SQL authorization ID = db2admin
Local database alias = SAMPLE
Port = 50001

CLPPlus: Version 1.0
Copyright (c) 2009, IBM CORPORATION.  All rights reserved.

SQL> create tablespace tbsp1;
DB250000I: The command completed successfully.
SQL> create tablespace tbsp2;
DB250000I: The command completed successfully.
SQL> create tablespace tbsp3;
DB250000I: The command completed successfully.
SQL> create tablespace tbsp4;
DB250000I: The command completed successfully.
SQL> create tablespace tbsp5;
DB250000I: The command completed successfully.
SQL> create tablespace tbsp6;
DB250000I: The command completed successfully.

SQL> CREATE TABLE TAB1(c1 INT)
  2  PARTITION BY RANGE (c1)
  3  (STARTING FROM (1) ENDING (50) IN tbsp1 INDEX IN tbsp2,
  4  ENDING(100) IN tbsp3 INDEX IN tbsp4,
  5  ENDING(150) IN tbsp4 INDEX IN tbsp6);

DB250000I: The command completed successfully.
   
SQL> CREATE INDEX IDX1 ON TAB1(c1) PARTITIONED;
DB250000I: The command completed successfully.

You can do partition level INDEX REORG, partition level TABLE REORG but RUNSTATS is still operates on TABLE level.

When you do an ATTACH and required indexes are already created, it is a very quick linked-in to the main table otherwise indexes will be created automatically. DETACH unlinks the partition from the main table quickly and it becomes just a regular table.

New Currently Committed (CC) Isolation

It is just a variation of CURSOR STABILITY (CS) isolation level of DB2 where writers do not block readers. It is just to please people who are very fond of Oracle’s snapshot based concurrency model. Oracle implements the theory of writers not blocking readers with snapshot technology whereas DB2 uses regular LOGS to implement this.

The CC isolation level is the new DEFAULT isolation level instead of old CS isolation level. DB2 can do full lock avoidance with CURRENTLY COMMITTED isolation level thus requiring no application level change in the application if porting from Oracle to DB2. CC is based on data available on the logs. DB2 first goes to the log buffer to find the data. Since the updating transaction is still active, probably the data is still in the log buffers. Otherwise, it goes to the log files on disk. This type of lock avoidance does not need ROLLBACK SEGMENTS as required by Oracle since DB2 uses LOG BUFFER records or at most going back to LOGS on disks.

Statement Concentrator

Do you remember that Oracle used CURSOR_SHARING=YES to convert literals into parameter markers to avoid repeated compilation of SQL statements? Do you also remember lots of pain associated with this approach to have sub-optimal plan generated by Oracle optimizer? I remember people hated this feature in Oracle due to serious performance problems but DB2 has implemented this feature with some serious thought to avoid sub-optimal plan which can be avoided with the use of REOPT parameter for STATIC or DYNAMIC statements. Consider using DB2 Statement Concentrator with REOPT for applications that are notorious to use literals across the board.

TEMP TABLE Compression

The temp tables are compressed automatically if deep compression is licenced. There is no additional step required by the user to use the TEMP TABLE compression feature.

Index Compression

You can now compress indexes in DB2 and you do it by adding COMPRESS YES in CREATE INDEX or ALTER INDEX syntax.

Query to estimate compression index savings:

SELECT index_name, pages_saved_percent, compress_attr, index_compressed 	
FROM TABLE SYSPROC.ADMIN_GET_INDEX_COMPRESS_INFO
('T', 'myschema', 'T1', '', '')) AS T

How much space was saved by using index compression?

After compressing index, rerun above query to find out actual saving.

ALTER INDEX index1 COMPRESS YES
REORG INDEXES ON TABLE t1
RUNSTATS ON TABLE t1