IBM Support

Asnclp command fails with SQL Error -20054 when executing “CREATE CONTROL TABLES FOR APPLY SERVER;”

Troubleshooting


Problem

Asnclp fails with SQL Error -20054 when creating the replication control tables for QApply

Symptom

Asnclp command fails with SQL Error -20054 when executing “CREATE CONTROL TABLES FOR APPLY SERVER;”

ErrorReport :
DB2 SQL Error: SQLCODE=-20054, SQLSTATE=55019, SQLERRMC=lob options prohibited with blocknonlogged enabled, DRIVER=3.65.77
ASN1954E ASNCLP : Command failed.

This is seen when the database configuration parameter BLOCKNONLOGGED is set as 'YES'

Cause

When executing “CREATE CONTROL TABLES FOR APPLY SERVER;”, asnclp creates several Q Apply Control tables in the Q Apply Server.
Two of the tables created are IBMQREP_EXCEPTIONS and IBMQREP_ROLLBACK_R.
These tables have a CLOB column called TEXT which is defined as "NOT LOGGED" -
"TEXT" CLOB(32768) NOT LOGGED NOT COMPACT

As per IBM Knowledge Center for “BLOCKNONLOGGED”
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0055068.html?cp=SSEPGG_10.5.0%2F2-4-4-9-13&lang=en
-------------------------------------------------------------------------------------------------------------------------------------------------------
If blocknonlogged is set to YES, then the CREATE TABLE and ALTER TABLE statements fail if any of the following situations exist.
. The NOT LOGGED INITIALLY parameter is specified.
. The NOT LOGGED parameter is specified for a LOB column.
. A CLOB, DBCLOB, or BLOB column is defined as not logged.
-------------------------------------------------------------------------------------------------------------------------------------------------------

So if the database configuration BLOCKNONLOGGED is set to YES, asnclp command will fail with SQL Error -20054.

Diagnosing The Problem

Please use
"db2 get db cfg for <applydbname> |grep BLOCKNONLOGGED"
to check if it is set to 'YES'

Resolving The Problem

Please execute
"db2 update db cfg for <applydbname> USING BLOCKNONLOGGED NO"
then after the parameter takes effect run asnclp to create the control tables.

*** Please review
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.config.doc/doc/r0055068.html?cp=SSEPGG_10.5.0%2F2-4-4-9-13&lang=en
before making the change.

[{"Product":{"code":"SSDP5R","label":"InfoSphere Replication Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Tools","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1.3;10.2;10.2.1.0;11.3.0.0;11.3.3.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSDP5R","label":"InfoSphere Replication Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21665378

Page Feedback