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.

 

Advertisements

Slow crash recovery in DB2 due to replaying load pending operations

Slow crash recovery in DB2 due to replaying load pending operations

https://www.ibm.com/developerworks/community/blogs/IMSupport/entry/75_ways_to_demystify_db2_19_techtip_slow_crash_recovery_in_db2_due_to_replaying_load_pending_operations?lang=en_us


DB2 LUW Highlights

Error “Namekey conflict with lock-file /tmp/SQLDIR.LK0. Trying another lock” in db2diag log file

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

DB2 Commands do not work after a version upgrade

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


DB2 licensing checks

DB2 License compliance report returns a VIOLATION status against a certain feature

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

Run license compliant report

db2licm -g /tmp/report.out ; cat /tmp/report.out

LIC1440I License compliance report generated successfully.

License Compliance Report
DB2 Enterprise Server Edition In compliance

DB2 Database Partitioning: “Not used”
DB2 Performance Optimization ESE: “Not used”
DB2 Storage Optimization: “Violation”
DB2 Advanced Access Control: “Not used”
DB2 Geodetic Data Management: “Not used”
IBM Homogeneous Replication ESE: “Not used”

So the DB2 Storage Optimization feature is in “VIOLATION”

Check if violation item is licensed

db2licm -l
Product name: “DB2 Enterprise Server Edition”
License type: “CPU Option”
Expiry date: “Permanent”
Product identifier: “db2ese”
Version information: “9.7”
Enforcement policy: “Soft Stop”
Features:
DB2 Performance Optimization ESE: “Not licensed”
DB2 Storage Optimization: “Not licensed”
DB2 Advanced Access Control: “Not licensed”
DB2 Geodetic Data Management: “Not licensed”
IBM Homogeneous Replication ESE: “Not licensed”

It would mean that the license for the feature in “Violation” has not been applied.

Run queries to check what is using the features in violation

For the DB2 Advanced Access Control Feature:

Check for tables that use label based access control (LBAC).
Run the following command against every database in every instance in the DB2 copy:

SELECT TABSCHEMA, TABNAME
FROM SYSCAT.TABLES
WHERE SECPOLICYID>0

For the DB2 Performance Optimization Feature:

Check whether there are any materialized query tables.
Run the following command against every database in every instance in the DB2 copy:

    SELECT OWNER, TABNAME
FROM SYSCAT.TABLES WHERE TYPE=’S’

Check whether there are any multidimensional cluster tables.
Run the following command against every database in every instance in the DB2 copy:

SELECT A.TABSCHEMA, A.TABNAME, A.INDNAME, A.INDSCHEMA
FROM SYSCAT.INDEXES A, SYSCAT.TABLES B
WHERE (A.TABNAME=B.TABNAME AND A.TABSCHEMA=B.TABSCHEMA)
AND A.INDEXTYPE=’BLOK’

Check whether any of your instances use query parallelism (also known as interquery parallelism).
Run the following command once in each instance in the DB2 copy:

    SELECT NAME, VALUE
FROM SYSIBMADM.DBMCFG
WHERE NAME IN (‘intra_parallel’)

Check if connection concentrator is enabled. Run the following command against every instance in the DB2 copy:

    db2 get dbm cfg

This command displays the current values of database manager configuration parameters including
MAX_CONNECTIONS and MAX_COORDAGENTS. If the value of the MAX_CONNECTIONS is greater than the
value of the MAX_COORDAGENTS then connection concentrator is enabled. If you are not using
DB2 Enterprise Server Edition, DB2 Advanced Enterprise Server Edition, or DB2 Connect™ Server
products, ensure that connection concentrator is disabled. This is because connection concentrator
is only supported for DB2 Enterprise Server Edition, DB2 Advanced Enterprise Server Edition, or
DB2 Connect Server products.

For the DB2 Storage Optimization Feature:

Check if any tables have row level compression enabled.
Run the following command against every database in every instance in the DB2 copy:

SELECT TABSCHEMA, TABNAME
FROM SYSCAT.TABLES
WHERE COMPRESSION IN (‘R’, ‘B’)

Check if any indexes have compression enabled.
Run the following command against every database in every instance in the DB2 copy:

SELECT TABSCHEMA, TABNAME, INDNAME, COMPRESSION
FROM SYSCAT.INDEXES
WHERE COMPRESSION = ‘Y’

Check if any compression dictionary still exists for a table that has row level compression deactivated.
Run the following command against every database in every instance in the DB2 copy:

SELECT TABSCHEMA, TABNAME
FROM SYSIBMADM.ADMINTABINFO
WHERE DICTIONARY_SIZE <> 0 OR XML_DICTIONARY_SIZE <> 0

Note: This query might be resource intensive and might take a long time to run.
Run this query only if Storage Optimization license violations are being reported
even though there are no tables that have row level compression enabled.


DB2 IDUG Prague dinners!

For today (Sunday) dinner register to CA Technologies DB2 User Day http://www.ca.com/gb/lpg/idug-2014/user-day.aspx

For all the dinners http://www.idug.org/p/cm/ld/fid=535


DB2 10.5 Fixpack 4 known issues.

1 known issue – db2 update db cfg error http://www-01.ibm.com/support/docview.wss?uid=swg21686677&myns=swgimgmt&mynp=OCSSEPGG&mync=E


IBM Data Server Manager – Beta

Just signed up for IBM Data Server Manager Beta

https://www.ibm.com/developerworks/community/blogs/ibmdsm/entry/announcement?lang=en