DB2 Cancun (10.5 Fixpack 4) New Features - 14 November 2014 03:33

0. Contents

1. Column-organized tables enhancements

1.1 Shadow tables

This is against instance db2inst2 first setup parameters for shadow tables

Download CDC Components as per Installation requirements for IBM InfoSphere Change Data Capture for shadow tables

Preparation checklist

Add cdc users

Install Access Server

Post installation message

Before you connect to this Access Server installation, you must start Access 
Server and create the administration user account. See the installation guide 
for more information. You should also install the equivalent version of IBM 
InfoSphere Change Data Capture Management Console, if you haven't already done 
so, before connecting to Access Server.

Press Enter to exit the installer

Start the Access Server

cd /opt/IBM/InfoSphereChangeDataCapture/AccessServer/bin
nohup ./dmaccessserver &

Install Infosphere CDC for DB2 LUW

Install Interim Fix for Infosphere CDC for DB2 LUW

Install InfoSphere CDC Management Console

Configure InfoSphere CDC software for shadow tables

Setting up the InfoSphere CDC instance for shadow tables

Output to last command is

staging_store_disk_quota_gb=1
mirror_auto_restart_interval_minutes=2
maintain_replication_mqt_latency_table=true

Check nohup.out file

IBM InfoSphere Change Data Capture is running.

Setting up the datastore for shadow tables with commands for IBM InfoSphere CDC Access Server

Creating the SYSTOOLS.REPL_MQT_LATENCY table

  • As db2cdc1 on Linux
  • . /home/db2inst1/sqllib/db2profile
  • db2 connect to justdave
  • db2 "CALL SYSPROC.SYSINSTALLOBJECTS(’REPL_MQT’, ’C’,CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))"
  • db2 LIST TABLES FOR SCHEMA SYSTOOLS
    • Expected output 
Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
HMON_ATM_INFO                   SYSTOOLS        T     2014-11-11-14.08.20.253520
HMON_COLLECTION                 SYSTOOLS        T     2014-11-11-14.08.20.285709
POLICY                          SYSTOOLS        T     2014-11-11-14.08.19.955391
REPL_MQT_LATENCY                SYSTOOLS        T     2014-11-12-06.30.12.091701

  4 record(s) selected.

    Create a shadow table with a required primary key

  • db2 "create table justdave1.colt(i1 int not null,i2 int) organize by row"
  • db2 "alter table justdave1.colt add constraint t_pk primary key (i1)"
  • db2 "create table justdave1.colt_s as (select * from justdave1.colt) data initially deferred refresh deferred enable query optimization maintained by replication organize by column"
  • db2 "set integrity for justdave1.colt_s all immediate unchecked"
  • db2 "alter table justdave1.colt_s add constraint colt_s_ix primary key (i1)"
  • db2 "select substr(tabname,1,24) as tabname,substr(tabschema,1,24) as tabschema from syscat.tables where substr(property,23,1) = 'Y'"
    • Expected output 
TABNAME                  TABSCHEMA               
------------------------ ------------------------
COLT_S                   JUSTDAVE1               

  1 record(s) selected.

    Setting up replication for shadow tables with CHCCLP

    Replicating data to shadow tables with CHCCLP

    Expected output 

Monitors the replication state and latency of subscriptions.
Statistics requested at: Nov 12, 2014 7:55:37 AM

SUBSCRIPTION     STATE               SCHEDULED END    LATENCY THRESHOLD   SOURCE DATASTORE   TARGET DATASTORE   
---------------- ------------------- ---------------- ------------------- ------------------ ------------------ 
SHADOW1          Mirror Continuous                                        accdat1            accdat1

    Test replication

    1.2. Merge supported for column organized tables

    This requires dbm cfg INTRA_PARALLEL set to YES otherwise

    SQL1668N  The operation failed because the operation is not supported with 
this environment. Reason code: "5".
5        

         An attempt was made to use column-organized tables in an
         environment in which intrapartition parallelism is disabled.

    Also SORTHEAP and SHEAPTHRES_SHR need to be increased otherwise

    SQL0955C  Sort memory cannot be allocated to process the statement. Reason 
code = "3".  SQLSTATE=57011
3        

         Insufficient shared memory for queries on column-organized
         tables.

    Create 2 tables and merge

    1.3. Common table expression improvements for column organized tables

    1.4. Alter table add column for column organized tables

    Add a column!

    db2 "alter table a add e char(10)"
DB20000I  The SQL command completed successfully.
db2 "select * from a"

A1          D1                   E         
----------- -------------------- ----------
          1 Item Y               -         
          2 Item Z               -         

  2 record(s) selected.

    1.5. Synopsis tables can now include CHAR and VARCHAR columns

    1.6. Adaptive Page-level compression for insert/import/ingest operations is now enabled by default

    1.7. Index access for SELECT statements that are executed with the cursor stability (CS) isolation level is now supported

    1.8. You can now create column-organized user-maintained materialized query tables (MQTs)

    1.9. Failed conversion from row to column organized can now be cancelled.

    Use db2convert -cancel

    1.10. On AIX® operating systems, the automatic NUMA awareness that is provided by setting the DB2_RESOURCE_POLICY registry variable to AUTOMATIC is enhanced.

    1.11. DECFLOAT is now supported as a column type on column-organized tables.

    alter table a add df0 decfloat
DB20000I  The SQL command completed successfully.
alter table a add  df1 decfloat(16)
DB20000I  The SQL command completed successfully.
alter table a add df2 decfloat(34)
DB20000I  The SQL command completed successfully.

    1.12. SQL Compatibility features provided by the DB2_COMPATIBILITY_VECTOR registry variable are now available for column-organized tables.

    db2set DB2_COMPATIBILITY_VECTOR=10
db2stop force
db2start
db2 terminate
db2 create database dave2
db2 connect to dave2
db2 "create table a (b number(5)) organize by column"
DB20000I  The SQL command completed successfully.
db2 "alter table a add c number"
DB20000I  The SQL command completed successfully.
db2 "alter table a add d number(8,2)"
DB20000I  The SQL command completed successfully.

    The number datatype is mapped as per the manual

    db2look -d dave2 -z DB2INST2 -tw a -e
CREATE TABLE "DB2INST2"."A"  (
		  "B" DECIMAL(5,0) , 
		  "C" DECFLOAT(16) , 
		  "D" DECIMAL(8,2) )   
		 IN "USERSPACE1"  
		 ORGANIZE BY COLUMN;

    2. HADR enhancements

    2.1. HADR databases now support column-organized tables.

    Check HADR setup

    db2pd -db dave -hadr | egrep "Member|HADR_SYNCMODE|HADR_STATE|PRIMARY_INSTANCE|STANDBY_INSTANCE"
Database Member 0 -- Database DAVE -- Active -- Up 0 days 00:07:02 -- Date 2014-09-07-12.32.01.038136
                        HADR_SYNCMODE = SYNC
                           HADR_STATE = PEER
                     PRIMARY_INSTANCE = db2inst2
                     STANDBY_INSTANCE = db2inst3

    Check tables on secondary

    db2 connect to dave
db2 "select substr(tabname,1,10) tabname from syscat.tables where tabschema='DB2INST2'"

TABNAME   
----------
A         
B         

  2 record(s) selected.

    On primary create column organized table c

    db2 "create table c (a1 int) ORGANIZE BY COLUMN"
DB20000I  The SQL command completed successfully.
db2 "insert into c values(1)"
DB20000I  The SQL command completed successfully.
db2 "grant select on c to db2inst3"
DB20000I  The SQL command completed successfully.
db2 "select * from c"

A1         
-----------
          1

  1 record(s) selected.

    On secondary instance check tables

    db2 "select substr(tabname,1,10) tabname from syscat.tables where tabschema='DB2INST2'"
SQL1224N  The database manager is not able to accept new requests, has 
terminated all requests in progress, or has terminated the specified request 
because of an error or a forced interrupt.  SQLSTATE=55032
db2 connect to dave

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.4
 SQL authorization ID   = DB2INST3
 Local database alias   = DAVE

[db2inst3@centos4 db2dump]$ db2 "select substr(tabname,1,10) tabname from syscat.tables where tabschema='DB2INST2'"

TABNAME   
----------
A         
B         
C         

  3 record(s) selected.

 db2 "select * from c"

A1         
-----------
SQL1773N  The statement or command failed because it requires functionality 
that is not supported on a read-enabled HADR standby database. Reason code = 
"7".

    Reason 7 is

    7        

         An attempt was made to query a column-organized table on an
         HADR standby database prior to DB2 Version 10.5 fix pack 4.

    This is 10.5 FP4

    db2level
DB21085I  This instance or install (instance name, where applicable: 
"db2inst3") uses "64" bits and DB2 code release "SQL10054" with level 
identifier "0605010E".
Informational tokens are "DB2 v10.5.0.4", "s140813", "IP23623", and Fix Pack 
"4".
Product is installed at "/opt/ibm/db2/V10.5.0.4".

db2 get dbm cfg | grep CUR_EFF
 Current effective arch level         (CUR_EFF_ARCH_LVL) = V:10 R:5 M:0 F:4 I:0 SB:0
 Current effective code level         (CUR_EFF_CODE_LVL) = V:10 R:5 M:0 F:4 I:0 SB:0

    Read-On-Standby is not currently supported for HADR with BLU Acceleration

    2.2. If you are using Internet Protocol version 6 (IPV6) adapters, you can now configure HADR with the db2haicu command.

    2.3. Three new monitoring fields are added to the db2pd command and the MON_GET_HADR table function.

    db2pd new fields

    db2pd -db dave -hadr | egrep "HEARTBEAT_MISSED|HEARTBEAT_EXPECTED|STANDBY_ERROR_TIME"
                     HEARTBEAT_MISSED = 0
                   HEARTBEAT_EXPECTED = 2
                   STANDBY_ERROR_TIME = NULL

    MON_GET_HADR table function new fields

    db2 "select HADR_ROLE,HADR_SYNCMODE,HADR_STATE,HEARTBEAT_MISSED,HEARTBEAT_EXPECTED,STANDBY_ERROR_TIME from TABLE(MON_GET_HADR(-1))"

HADR_ROLE     HADR_SYNCMODE HADR_STATE              HEARTBEAT_MISSED HEARTBEAT_EXPECTED STANDBY_ERROR_TIME        
------------- ------------- ----------------------- ---------------- ------------------ --------------------------
PRIMARY       SYNC          PEER                                   0                 16 -                         

  1 record(s) selected.

    3. pureScale enhancements

    3.1. DB2 pureScale Feature on a Transmission Control Protocol/Internet Protocol over Ethernet (TCP/IP) network without special remote direct memory access (RDMA) capable adapters.

    First install FreeNas on a VM - FreeNAS

    Thanks go to Installing FreeNAS on VMware Workstation

    On the Linux VM enable iSCSI Initiator

    Thanks go to Configure iSCSI Initiator.

    3.2. A geographically dispersed DB2 pureScale cluster (GDPC) environment is extended to support a remote direct memory access (RDMA) over Converged Ethernet (RoCE) network.

    3.3. On supported Linux operating systems, a DB2 pureScale environment can now be run on a set of virtual machines.

    3.4. Purescale backup and recovery operations are extended to include incremental backups.

    3.5. In a DB2 pureScale environment, the DB2 Spatial Extender is now supported.

    3.6. When applying a fix pack in a DB2 pureScale environment, if GPFS™, Tivoli SA MP, or RSCT were already manually installed or updated manually, you can force the installFixPack command to overwrite the already installed version.

    3.7. Inplace (online) table reorganization is now supported in a DB2 pureScale environment..

    3.8. Federated two-phase commit is now supported in a DB2 pureScale environment.

    3.9. Purescale - a new db2iupgrade parameter, -g, upgrades all the members and cluster caching facilities (CFs) at the same time, in parallel.

    4. SQL compatibility enhancement and multicultural support enhancement

    4.1. Handling of multibyte characters in character and graphic string data types is improved by allowing data type lengths to be specified as a number of string units.

    Note that CODEUNITS32 is not supported for column organized tables

    db2 "alter table a add w1 char(10 octets)"
DB20000I  The SQL command completed successfully.
db2 "alter table a add w2 char(10 CODEUNITS32)"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL1666N  The table definition statement failed because some functionality was 
specified in the table definition that is not supported with the table type.  
Unsupported functionality: "CHAR CODEUNITS32".  SQLSTATE=42613
db2 "?" SQL1666N
This message is returned when an attempt is made to create or alter a
column-organized table with functionality that is not supported with
column-organized tables.
db2 "create table c (w2 char(10 CODEUNITS32)) organize by row"
DB20000I  The SQL command completed successfully.
db2 "alter table c add w3 vargraphic(10 CODEUNITS16)"
DB20000I  The SQL command completed successfully.

    4.2. For successfully matching SQL statements, three new inexact statement matching rules are now supported.

    5. Monitoring enhancements

    5.1. New monitor elements monitor the use of sort heap memory to help you with its sizing and allocation.

    5.2. New monitor elements assist with aggregation of package cache monitor data and detection of plan changes.

    stmtid - The hash key value that identifies normalized statement text that is associated with a section. Semantic content such as the function path and current schema are not part of the statement identifier.

    planid - The planid monitor element tracks important performance sensitive aspects of the access plan. Such aspects include the list and layout of access plan operators, identifiers of the objects that are being accessed, the number of each type of predicate for each operator, and performance sensitive operator arguments.

    semantic_env_id - This hash value is computed over the default schema and function path elements in the compilation environment.

    A value of 1 means the default schema and function path were not used during the compilation of the statement. The function path is treated as not being used if only functions in the SYSIBM schema are accessed and SYSIBM is the first entry in the function path.

    db2 "select distinct semantic_env_id,count(*) from TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2)) group by semantic_env_id"

SEMANTIC_ENV_ID      2          
-------------------- -----------
                   1          53
 1973084603141892647          11
 3748832650439057064           1

  3 record(s) selected.
db2 "select SECTION_TYPE,PACKAGE_SCHEMA,PACKAGE_NAME from TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2)) where semantic_env_id=3748832650439057064"

SECTION_TYPE PACKAGE_SCHEMA                                                                                                                   PACKAGE_NAME                                                                                                                    
------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
S            NULLID                                                                                                                           DB2L2K1R                                                                                                                        

  1 record(s) selected.

    5.3. New monitor elements identify statements in the package cache that had a suboptimal compilation.

    db2 "select distinct PREP_WARNING,PREP_WARNING_REASON from TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2))"

PREP_WARNING PREP_WARNING_REASON
------------ -------------------
           0                   0

  1 record(s) selected.

    5.4. The new built-in routine MON_GET_SECTION helps to retrieve and store a copy of the executable section for further investigation.

    The MON_GET_SECTION table function returns a copy of a section for a dynamic or static SQL statement from the package cache. The section that is returned can be saved for future analysis. For example, when you investigate a plan change for a statement, the saved section can be passed as input to the EXPLAIN_FROM_DATA stored procedure to examine the previous access plan.

    5.5. The new built-in routine MON_GET_SECTION_OBJECT helps to identify sections that access an object or set of objects, and helps explore which objects a section depends on.

    As part of your investigation of a poorly performing statement, you want to examine which objects are used in the access plan for the statement.

     db2 "select EXECUTABLE_ID from TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2)) where semantic_env_id=3748832650439057064"

EXECUTABLE_ID                                                      
-------------------------------------------------------------------
x'0100000000000000AD0100000000000007000000010020140830161428497724'

  1 record(s) selected.

db2 "SELECT DISTINCT OBJECT_TYPE, substr(OBJECT_SCHEMA,1,20) OBJECT_SCHEMA, substr(OBJECT_MODULE,1,20) OBJECT_MODULE, substr(OBJECT_NAME,1,20) OBJECT_NAME FROM TABLE (MON_GET_SECTION_OBJECT(x'0100000000000000AD0100000000000007000000010020140830161428497724',NULL, NULL, NULL, NULL, -2))"

OBJECT_TYPE      OBJECT_SCHEMA        OBJECT_MODULE        OBJECT_NAME         
---------------- -------------------- -------------------- --------------------
TA               SYSIBM               -                    SYSTABLES           

  1 record(s) selected.

    6. Application interface enhancements

    6.1. The CLI driver that is included with this fix pack contains several enhancements.

    6.2. The IBM Data Server Provider for .NET that is included with this fix pack contains several enhancements.

    6.3. Use of array variables in INSERT, UPDATE, and DELETE statements are supported by embedded SQL applications.

    6.4. You can skip local code page conversion for the graphic data types in embedded SQL application with the SkipLocalCPConversionForWcharConvert IBM data server driver configuration keyword.

    6.5. JSON (Java™ Script Object Notation) DB2 NoSQL capabilities are enhanced with new security features, new flexible data types for the _ID field, capped collections, new aggregate functions, and better control through various administration changes.

    7. Other miscellaneous enhancements

    7.1. On supported Linux and AIX operating systems, a non-root installation of DB2 database server product supports thin server instance environment.

    7.2. The db2look command supports two new parameters -createdb and -printdbcfg.

    db2look -d dave -createdb
CREATE DATABASE DAVE
	AUTOMATIC STORAGE YES
	ON '/home/db2inst2'
	DBPATH ON '/home/db2inst2/'
	USING CODESET UTF-8 TERRITORY US
	COLLATE USING IDENTITY
	PAGESIZE 4096
	DFT_EXTENT_SZ 32

	CATALOG TABLESPACE MANAGED BY AUTOMATIC STORAGE 
	 EXTENTSIZE 4
	 
	 NO FILE SYSTEM CACHING 
	 AUTORESIZE YES 
	 INITIALSIZE 32 M 
	 MAXSIZE NONE 


	TEMPORARY TABLESPACE MANAGED BY AUTOMATIC STORAGE 
	 EXTENTSIZE 32
	 
	 FILE SYSTEM CACHING 


	USER TABLESPACE MANAGED BY AUTOMATIC STORAGE 
	 EXTENTSIZE 32
	 
	 NO FILE SYSTEM CACHING 
	 AUTORESIZE YES 
	 INITIALSIZE 32 M 
	 MAXSIZE NONE 
;

db2look -d dave -printdbcfg 

-- The db2look command generates the UPDATE DB CFG statements
-- to replicate the database configuration parameters based on
-- the current values in the source database.
-- For the configuration parameters which support AUTOMATIC,
-- you need to add AUTOMATIC to the end
-- if you want the DB2 database to automatically adjust them.

--UPDATE DB CFG FOR DAVE USING ALT_COLLATE      ;

UPDATE DB CFG FOR DAVE USING STMT_CONC          OFF ;

UPDATE DB CFG FOR DAVE USING DISCOVER_DB        ENABLE ;

UPDATE DB CFG FOR DAVE USING DFT_QUERYOPT       5 ;
UPDATE DB CFG FOR DAVE USING DFT_DEGREE         1 ;
UPDATE DB CFG FOR DAVE USING DFT_SQLMATHWARN    NO ;
UPDATE DB CFG FOR DAVE USING DFT_REFRESH_AGE    0 ;
UPDATE DB CFG FOR DAVE USING DFT_MTTB_TYPES     SYSTEM ;
...
--UPDATE DB CFG FOR DAVE USING SMTP_SERVER         ;
--UPDATE DB CFG FOR DAVE USING SQL_CCFLAGS         ;
UPDATE DB CFG FOR DAVE USING SECTION_ACTUALS    NONE ;
--UPDATE DB CFG FOR DAVE USING CONNECT_PROC        ;
UPDATE DB CFG FOR DAVE USING SYSTIME_PERIOD_ADJ NO ;
UPDATE DB CFG FOR DAVE USING LOG_DDL_STMTS      NO ;
UPDATE DB CFG FOR DAVE USING LOG_APPL_INFO      NO ;
UPDATE DB CFG FOR DAVE USING DFT_SCHEMAS_DCC    NO ;

    7.3. You can now obtain the details of all statement handles that were allocated by the CLI driver prior to enabling a DB2 trace.

    7.4. You can now format the DB2 trace that is taken with the -cli option to a directory, where separate trace files are created for each thread id in a process.

    7.5. The db2cli command adds a -displaylic option to display license information.

    Not valid for LUW!

    db2cli validate -database dave:localhost.localdomain:50001 -connect -user db2inst2 -passwd XXX -displaylic
...
-DisplayLic or -UseServerLic option is not applicable for LUW/Informix server.

    7.6. The db2cli command allows testing the connection to DB2 for z/OS or DB2 for i server with the server-side DB2 connect license when the -useserverlic option is specified.

    7.7. The db2cli command allows testing the data source name (DSN) connection with the ODBC driver manager when the -odbcdsn option is specified..

    7.8. The db2audit command has been enhanced with a new syslog parameter.

    db2audit configure scope sysadmin status both
db2audit start
db2audit describe
db2 update dbm cfg   using svcename db2c_db2inst2
db2audit flush
db2audit stop
db2audit archive to /tmp/z
db2audit extract syslog kern.info from files /tmp/z/db2audit.instance.log.0.20140830203913

    As root
    

    grep info /etc/rsyslog.conf | tail -1
*.info;mail.none;authpriv.none;cron.none                /var/log/messages
grep -i update /var/log/messages
Aug 30 20:44:40 localhost db2audit: timestamp=2014-08-30-20.39.12.136294; category=SYSADMIN; audit event=UPDATE_DBM_CFG; event correlator=4; event status=1362; database=DAVE2; authid=DB2INST2; application id=*LOCAL.DB2.140830231136; application name=db2bp; instance name=db2inst2; hostname=localhost.localdomain;

    

    7.9. Additional EXPLAIN arguments are now supported.
    

    7.10. Enhanced encryption is now possible with the adherence to NIST SP 800-131A.
    

    To comply with NIST SP 800-131A, DB2 V10.5 Fix Pack 4:
    


    7.11. You can now ensure that all database backups are always encrypted.
    

    Two new database configuration parameters
    

    db2 get db cfg for dave | grep -i encr
 Encryption Library for Backup                 (ENCRLIB) = 
 Encryption Options for Backup                (ENCROPTS) = 


    7.12. The performance of SQL Procedural Language (SQL PL) and SQL/PL language execution has been enhanced.
    

    7.13. Additional operating systems are supported.
    

    7.14. Translation from DB2 Information Centre to IBM Knowledge Centre is complete.