Explain some new features worthy of DBA attention in DB2 9.7

BKJIA exclusive feature] First of all, this article is not an exhaustive list of new features in DB2 9.7, I just listed a very important list for DBA or developers.

Online scheme modification

Please see Burt's article on online solution modification at http://www.ibm.com/developerworks/data/library/techarticle/dm-0907db2outages/index.html

CGTT-create global temporary table

You can now create global temporary tables and never have to declare temporary tables in each session. Global temporary tables have the following advantages:

1.CGTT is controlled according to authorization

Views, indexes and triggers can be defined on CGTT

3.CGTT and DGTT can include LOB data types

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

Parameters in stored procedures can have default values.

In the call statement, you can directly use the default value and skip the parameter assignment.

Named parameters allow assignment by name, in any order.

SSL connection in DB2

DB2 allows SSL connections between Java clients and servers, but requires both client and server configurations to work properly.

Server-side configuration

Use iKeyman GUI tool to create a certificate database, which is the KeyStore file;

Import the server digital certificate you purchased from a certificate authority into the certificate database;

Set the following DBM configuration parameters;

◆ SSL_SVR_KEYDB: key storage file

◆ SSL_SVR_STASH: Stash file

◆ SSL_SVCENAME: SSL port

Alternatively, choose a cipher suite

◆ SSL_CIPHERSPECS: Allowed cipher suites

◆ SSL_VERSIONS: Allowed SSL / TLS versions

Enable instance SSL communication

db2set DB2COMM = SSL or db2set DB2COMM = SSL, TCPIP

Client configuration

Create a signing certificate database using the ikeyman GUI tool;

Import the server digital certificate you purchased from a certificate authority into the certificate database.

Modifying 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 does the SSL handshake work?

The client requests an SSL connection, listing its SSL version and supported cipher suites;

The server responds with a selected cipher suite;

The server sends its digital certificate to the client;

Client server authentication certificate server verification);

The client and server securely negotiate a session key;

The client and server securely exchange information using the key selected earlier.

XML enhancement

There are some enhancements in XML processing that are worth noting:

Full support for pureXML;

XML documents in XDA objects can also be compressed;

XML index online reorganization;

UDF can use XML columns;

XML columns can be used in MDC tables;

DECOMP from the query CLP command: In order to split the XML document, you can use DECOMPOSE XML DOCUMENTS IN <select_statement> XMLSCHEMA <xmlschemaname>;

DECOMP from the query stored procedure: sysproc.xdb_Decomp_XML_From_Query;

ADMIN_IS_INLINED and ADMIN_EST_INLINE_LENGTH can be used to know whether XML or LOB is inline or its length.

DECOMP example:

DECOMPOSE XMDOCUMENTS IN
'SELECT DOCID, SALESDOC
FROM ABC.SALESTAB '
XMLSCHEMA ABC.SALES
MESSAGES /home/myid/errors/errorreport.xml
CALXDB_DECOMP_XML_FROM_QUERY (
'ABC', 'SALES',
'SELECT DOCID, SALESDOC FROM ABC.SALESTAB',
0, 0, 0, NULL, NULL, 1,
: hnumInput,: hnumDecomposed,: herrorreportBuf);
Note: XDA = stores XML documents, and there are no inline objects in the underlying table.

CLPPLUS vs SQL * Plus

DB2 has a tool similar to Oracle's SQL * Plus, called CLPPlus. If you know how to use SQL * Plus, you will definitely use CLPPlus.

PL / SQL in DB2

This sounds like the most eye-catching thing. Imagine your feeling of using Oracle PL / SQL in DB2. Remember that Oracle has 20 years of PL / SQL development experience, so there is still a gap compared with Oracle, but I believe IBM will do better and better.

The PL / SQL compiler in DB2 is inherently not a shielding layer. It is not a way to convert PL / SQL to DB2 SQL PL, so you can debug PL / SQL at the code level.

Supported DBMS packages:

DBMS_OUTPUT

UTL_FILE

DBMS_ALERT

DBMS_PIPE

DBMS_JOB

DBMS_LOB

DBMS_SQL

DBMS_UTILITY

UTL_MAIL

UTL_SMTP

Supported PL / SQL Features

All language logic support, such as IF, WHILE: =, etc .;

Exception-Try / catch processing;

User-defined exception

Constant variables-variables cannot be set;

Step through the query result set;

Single step debug cursor result set;

% TYPE-anchor data type;

% ROWTYPE-anchor row type;

BULK COLLECT / FETCH-aggregate the result set into an array;

FORALL-input array into SQL statement;

AUTOMONOUS transaction-execute a stored procedure in a separate TX;

Anonymous block

Scalar function

Stored procedure

package;

trigger;

Synonym on package.

Oracle types in DB2:

NUMBER
VARCHAR2-NULL = '', trailing white space sensitive collation
TIMESTAMP (n)-0 (date + time) <= N <= 12 date + time + picosecond)
DATE-year to second, sysdate
BOOLEAN
INDEX BY
VARRAY
Row Type
Ref Cursor-allows passing and predefined cursors
 
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
Oracle SQL manifest in DB2
CONNECT BY
(+)-join
DUAL
ROWNUM
NEXTVAL / CURRVAL
MINUS
Unnamed inline views
TRUNCATE table
Public synonym
CREATEd temp table
Segregation of Duties

SYSADM's days as a God in DB2 are gone, and now the division of roles is very clear.

With the database-level SECADM, the DBADM assigned to SYSADM will definitely be removed;

SECADM can now be assigned to ROLE and GROUP;

SECADM can now grant / cancel database and object certifications;

DBADM can be created this way, so it cannot see the data, nor can it perform authorization / revocation authorization;

DBADM cannot obtain indirectly granted permissions;

Created new permissions, such as EXPLAIN, DATAACCESS, ACCESSCTRL, SQLADM, WLMADM.

Automatic storage

Added some new automatic storage features, which are worth paying attention to:

When the existing storage path is filled, you can add a storage path online. If you want to stripe data across 4 storage paths to obtain high parallelism, you can use the ALTER TABLESPACE myts REBALANCE command to rebalance the data. Use the ALTER TABLESPACE myts REDUCE command to reduce the high-water mark;

You can use ALTER DATABASE DROP STORAGE PATH ON 'path1' to delete a storage path. The storage path is modified to DROP PENDING state. All table spaces are marked as "basic storage path has been deleted." When all table spaces that use it pass After ALTER TABLESPACE ts1 REBALANCE is rebalanced, the DROP PENDING storage path is removed;

The ALTER TABLESPACE ts1 REDUCE command will free up the trapped space and will not allow high water mark to be lower than the previous DB2 version;

You can convert a non-automated storage database into an automated storage database:

◆ ALTER DATABASE ADD STORAGE supports non-automatic storage database;

◆ The above command adds a storage pool to the database;

◆ It allows the creation of new table spaces using automated storage.

You can convert non-automated storage DMS table spaces into automated storage table spaces:

◆ ALTER TABLESPACE… MANAGED BY AUTOMATIC STORAGE;

◆ New growth from the new storage path is added to the database;

◆ Old containers can be removed using DROP or REBLANCE.

With automated storage, you can perform a REDIRECTED RESTORE from the old DMS to the new tablespace.

Online table move

Online table moves can be used in many environments:

When you want to reorganize or redistribute online;

When you want to do online table compression;

Change page size online;

Online conversion to large table space;

Move data / index / long data to a new / different tablespace;

Add or remove columns, modify column data types, expand columns;

Increase / modify MDC size, range partition or partition key.

Online table migration is done through the system stored procedure ADMIN_MOVE_TABLE (). This stored procedure allows data to be migrated from an existing table to a new table object. After the data is moved, using SELECT, INSERT, UPDATE and DELETE operations on the source table is still valid. .

This stored procedure is divided into 4 steps:

1.Init

Select copy index, create trigger, create target and temporary table. The purpose of triggers is to capture changes on the source table.

2.Copy

Copy line by line from the source table to the target table. You can also use LOAD.

3. Replay

Re-copying rows captured from the source table to the temporary table may require multiple round trips to repeat the operation.

4.Exchange

The target table for exchange is ready, indexes, etc. have been created, the source table is locked in shared mode for the last replay, then the source table is renamed, and then the target table is renamed to the source table name.

Compressed MDC table

The MDC table in DB2 is a bit special. Generally, if there is no delete operation, it usually does not need to be reorganized. The delete operation makes the MDC table sparse. How to recycle those unused pages back to the table space so that other tables can be used? Just use the following command:

REORG TABLE mdctable RECLAIM EXTENTS ONLY
The above command is very efficient because it does not actually reorganize, except that unused blocks are marked as unallocated in the block mapping of the MDC table.

Inline LOB

Compress space through inline LOBs, such as:

CREATE TABLE… EMP_PHOTO BLOB (10MB) INLINE LENGTH 20000;
ALTER TABLE… ALTER COLUMN EMP_PHOTO SET INLINE LENGTH 20000;
The portion of the LOB that is longer than the inline length is stored on the LOB table space, but the other Will be stored separately inline, there can be mixed inline or non-inline LOBs in the table.

Even if no inline length is set, DB2 can inline the LOB, which occurs when the true length of a LOB is less than the length of its descriptor.

Local range partition index

DB2 has a global index on range partitioned tables, and now has a local range partitioned index to insert and retrieve data more quickly.

C: \> clpplus db2admin / password @ localhost: 50001 / SAMPLE
Database Connection Information
   
Hostname = localhost
Database server = DB2 / NT SQL09070
SQauthorization 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 perform partition-level index reorganizations and partition-level table reorganizations, but RUNSTATS still runs at the table level.

When you make an ATTACH, the required index has been created and the connection to the main table is very fast, otherwise it will automatically create the index, and DETACH will quickly cancel the partition link from the main table, making it a regular table.

New current commit quarantine

It is just a variant of cursor stability isolation. The logger does not block readers. Oracle is based on snapshots, while DB2 is based on logs.

The new CC isolation level is the new default isolation level, replacing the old CS isolation level. DB2 uses the current full lock to avoid the current commit isolation level, so no application changes are required when moving from Oracle to DB2. CC is based on logs For data, DB2 first goes to the log buffer to find the data, because the update transaction is still active, so the data may still stay in the log buffer, otherwise you must find the log file on the disk. This type of lock does not need to be returned. Rolling segment, but Oracle needs it, because DB2 uses log cache records or log files on disk.

Statement concentrator

Do you remember that Oracle used the CURSOR_SHARING = YES parameter to avoid recompiling SQL statements? Do you remember that this approach caused you a headache by causing the optimizer to generate non-optimal execution plans? Due to its serious performance problems, many people using Oracle particularly hate this feature, but DB2 also implements this feature, but you can use the REOPT parameter in STATIC or DYNAMIC statements to avoid non-optimal execution plans.

Temporary table compression

If a deep compression license is obtained, the temporary table is automatically compressed without requiring the user to perform additional operations.

Index compression

You can now compress the indexes in DB2 by adding the COMPRESS YES parameter to the CREATE INDEX or ALTER INDEX command.

Query compressed index space savings estimates

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 is saved after compressing the index? After compressing the index, re-run the above query to get the real space savings.

ALTER INDEX index1 COMPRESS YES
REORG INDEXES ON TABLE t1
RUNSTATS ON TABLE t1
Original source: http://www.db2ude.com/?q=node/128

Original name: New Exciting Features in DB2 9.7

Author: Vikram Khatri