DB2 LUW Setting up Shadow Tables

Just heard someone at IDUG EMEA 2016 asking about how to setup DB2 LUW shadow tables.

This will depend upon DB2 Version,you also need a compatible CDC (Infosphere Replication) version,check via Finding System Requirements for IBM Software Products.

The steps are at http://www.smooth1.co.uk/db2/cancun.html#1.1

NOTE: This was written when the feature first came out DB2 LUW 10.5 FP4.

CDC Components to download were:

  • InfoSphere CDC for DB2 for LUW
  • InfoSphere CDC Access Server
  • InfoSphere CDC Management Console

Finding System Requirements for IBM Software Products.

IBM has a  Software Product Compatibility Reports facility.

http://www-969.ibm.com/software/reports/compatibility/clarity/softwareReqsForProduct.html


SQL1227N during replaying db2look -m output

 

http://www-01.ibm.com/support/docview.wss?uid=swg21991415

db2look -m -r

The “-r” option is used to generate db2look output without runstats command output. However, current db2look code cannot reset SYSSTAT.COLUMNS and SYSSTAT.INDEXES when -r is applied. As a result, SQL1227N sometimes occur when applying the mimic when COLCARD etc, and CARD for the table is inconsistent. In db2look up to version in V10.5 please use db2look -m without -r option or insert resetting SYSSTAT.COLUMNS and SYSSTAT.INDEXES manually in db2look output with “-m” and “-r”. This limitation will be changed in releases later than V10.5. In most cases it should not cause SQL1227N error, but still the error can occur when the statistics on the source system is inconsistent. Here are examples:
Why COLCARD is sometimes bigger than CARD after RUNSTATS?
http://www.ibm.com/support/docview.wss?uid=swg21985376

Runstats may update unexpected HIGH2KEY and LOW2KEY statistic values which may cause SQL1227N
http://www.ibm.com/support/docview.wss?uid=swg21979066

As documented in above technotes, you can bypass the error by setting following undocumented registry variable even if the statistics are inconsistent:

db2set DB2_STATISTICS=USCC:0
recycle the instance

This registry variable can be used from 9.7FP9/10.1FP4/10.5.

 


DB2 LUW 11.1

DB2 11.1 has been announced with a download date of June 15 2016

Potential new features are:

Core workloads

Enterprise Encryption supports Centralized Key Managers (KMIP)

Support for Encryption to use  KMIP 1.1 with a Centralized Key Manager

– update dbm cfg using keystore_type kmip

– update dbm cfg using keystore location /home/justdave/isklm.cfg

Config file contains host, port number etc.

Technology Preview of support for Hardware Security Modules

Simpler Deployment

Greater Availability

Zero Data Loss HADR

More online management

More platforms supported

Power Linux (LE) supported

Virtualization for RDMA (x86) supported

Very Large Database Performance

Faster throughput

Better upgrades

DB2 9.7 and higher can be directly upgraded to DB2 11.1 (support for upgrades from 3 releases back)

Online upgrades with no need for an offline backup.

A recovery procedure including roll-forward thtough a database upgrade exists. This does not include DPF and requires upgrading from DB2 10.5 FP7 or higher.

Steamlined HADR upgrade

HADR environments can be upgraded with need to re-initialized the secondary. This does not apply to Purescale and requires upgrading from DB2 10.5 FP7 or higher.

Warehousing workloads

MPP supports BLU

DPF renamed to MPP

Faster ELT/ETL Performance

More Query Workloads Optimized

More functions supported

Generated Columns

RCAC

OLAP+BLU Performance increased

Multi-Lingual SQL Advances

Postgres syntax

Support for European Languages

Codepage 819

 


DB2 10.5 Fixpack 6 is out

DB2 10.5 Fixpack 6 is out

http://www-01.ibm.com/support/docview.wss?uid=swg24040522

“Fix Pack 6 does not contain new enhancements, and there were no changes to the existing functionality. This fix pack and future fix packs will deliver only APAR and security vulnerability fixes.”


Managing GSKit Master Keys

http://www.ibm.com/developerworks/data/library/techarticle/dm-1504-master-encrypted-keys/index.html

Managing GSKit Master Keys


How do you enable a JDBC trace for a Java Stored Procedure or Java UDF?

http://www-01.ibm.com/support/docview.wss?uid=swg21884054&myns=swgimgmt&mynp=OCSSEPGG&mync=E&cm_sp=swgimgmt-_-OCSSEPGG-_-E

How do you enable a JDBC trace for a Java Stored Procedure or Java UDF?

Question

How do you enable a JDBC trace for a Java Stored Procedure or Java UDF?
Answer

Issue the following command on the database server:

db2set DB2_JVM_STARTARGS=”-Ddb2.jcc.override.traceLevel=-1 -Ddb2.jcc.override.traceFile=/tmp/jdbc_trace -Ddb2.jcc.override.traceFileAppend=true”

… where /tmp/jdbc_trace can be any path/file name that has write privilege by the DB2 fenced user id.

If the database manager configuration parameter KEEPFENCED is set to YES, then this command will require the DB2 instance to be recycled:

db2stop
db2start

To remove this parameter issue the command:

db2set DB2_JVM_STARTARGS=

… and then restart the DB2 instance again (assuming KEEPFENCED=YES is used).