Wednesday 13 February 2013

More fun n' games with DB2 - yet another (l)user error - SQL0355N The column "DESCRIPTION", as defined, is too large to be logged. SQLSTATE=42993

So I'm seeing: -
...
CREATE TABLE BPM_TASK_INDEX ( "TASK_ID" DECIMAL(12,0) NOT NULL, "MAJOR_EVENT_DATETIME" TIMESTAMP, "MINOR_EVENT_DATETIME" TIMESTAMP, "TASK_DATA" BLOB(2G), "DELETED_DATETIME" TIMESTAMP )
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0355N  The column "TASK_DATA", as defined, is too large to be logged.  
SQLSTATE=42993

...

...
CREATE TABLE LSW_UCA ( "UCA_ID" CHAR(36) NOT NULL, "PROCESS_REF" CHAR(36) NOT NULL, "SCHED_TYPE" DECIMAL(12,0), "SCHED_EVENT_NAME" VARCHAR( 320), "QUEUE" DECIMAL(12,0), "IS_ENABLED" CHAR(1), "SHARED_SYMBOL_TABLE_ID" DECIMAL(12,0), "MONTH_LIST" VARCHAR( 96), "FREQ_TYPE" DECIMAL(12,0), "DAY_LIST" VARCHAR( 312), "HOUR_LIST" VARCHAR( 192), "MINUTE_LIST" VARCHAR( 480), "NAME" VARCHAR( 256) NOT NULL, "DESCRIPTION" CLOB(2G), "UCA_EVENT_TYPE_REF" DECIMAL(2,0), "VARIABLE_REF" CHAR(36), "IMPLEMENTATION_TYPE" VARCHAR( 320), "GUID" VARCHAR( 512) NOT NULL, "VERSION_ID" CHAR(36) NOT NULL, "LAST_MODIFIED" TIMESTAMP NOT NULL, "LAST_MODIFIED_BY_USER_ID" DECIMAL(12,0) NOT NULL )
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0355N  The column "DESCRIPTION", as defined, is too large to be logged.  
SQLSTATE=42993

...

...
CREATE TABLE LSW_USER_ATTRIBUTE_DEF ( "USER_ATTRIBUTE_ID" CHAR(36) NOT NULL, "CLASS_REF" CHAR(36) NOT NULL, "STORAGE_SOURCE" DECIMAL(12,0) DEFAULT 0 NOT NULL, "STORAGE_PROVIDER" DECIMAL(12,0), "STORAGE_PROVIDER_ATTRIBUTE" VARCHAR( 1020), "STORAGE_SERVICE" DECIMAL(12,0), "VALUES_SOURCE" DECIMAL(12,0), "VALUES_SERVICE" DECIMAL(12,0), "NAME" VARCHAR( 256) NOT NULL, "DESCRIPTION" CLOB(2G), "GUID" VARCHAR( 512) NOT NULL, "VERSION_ID" CHAR(36) NOT NULL, "LAST_MODIFIED" TIMESTAMP NOT NULL, "LAST_MODIFIED_BY_USER_ID" DECIMAL(12,0) NOT NULL )
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0355N  The column "DESCRIPTION", as defined, is too large to be logged.  
SQLSTATE=42993

etc. when creating database tables for Process Server via: -

$ db2 -tvf createTable_ProcessServer.sql

as part of an IBM BPM Advanced 8.0.1 installation.

A quick Google brings me to this: -


which says, in part: -

I performed many installations of IBM BPM7.5, 8.0 and the following are the best practices i learnt the hard way.

1. Install the DB4 (FP4) that comes bundled with the installables

2. DB2 installations creates a OS user bpmadmin(by default). Once the WAS platform is installed via a windows admin login, i re-login as bpmadmin and create the profiles using PMT. I figured out this to be the best way to get quick results.

I've installed on windows 32/64 bit m/cs and created/augmented profiles using PMT without any problems following the above approach. Also, i could install Lombardi 7.2, BPM 7.5 and BPM 8.0 on the same m/c successfully.

BTW, i came across the perfDW message bus corruption issues (perf db getting corrupt), but that will be a separate thread topic :-).

I checked the version of DB2 on my server: -

$ db2level

DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL09070" 
with level identifier "08010107".
Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXAMD6497", and Fix 
Pack "0".
Product is installed at "/opt/ibm/db2/V9.7".


You can guess the rest - IBM BPM 8.0.1 requires DB2 9.7.0.5 or later, almost certainly for a very good reason :-)

I upgraded DB2 to 9.7.0.5: -

( as db2inst1 )

$ db2stop

02/13/2013 15:06:53     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

( as dasusr1 )

$ db2admin stop

SQL4410W  The DB2 Administration Server is not active.

( as root )

$ mkdir /tmp/db2
$ cd /tmp/db2
$ tar xvzf /mnt/hgfs/DaveHay/Software/DB2V9/Linux/v9.7fp5_linuxx64_universal_fixpack.tar.gz 

universal/
universal/db2/
universal/db2/linuxamd64/
universal/db2/linuxamd64/utilities/
universal/db2/linuxamd64/utilities/db2IdentifyType1/
universal/db2/linuxamd64/utilities/db2IdentifyType1/bin/
universal/db2/linuxamd64/utilities/db2IdentifyType1/bin/db2fupdt
universal/db2/linuxamd64/utilities/db2IdentifyType1/bin/db2IdentifyType1_v8_32
universal/db2/linuxamd64/utilities/db2IdentifyType1/bin/disp_msg
universal/db2/linuxamd64/utilities/db2IdentifyType1/bin/db2IdentifyType1_v9_64
universal/db2/linuxamd64/utilities/db2IdentifyType1/bin/db2IdentifyType1_v8_64
universal/db2/linuxamd64/utilities/db2IdentifyType1/bin/db2IdentifyType1_v9_32
universal/db2/linuxamd64/utilities/db2IdentifyType1/bin/db2langdir

universal/doc/sk_SK/release.txt
universal/doc/it_IT/
universal/doc/it_IT/release.txt
universal/db2ls
universal/installFixPack
universal/db2ckupgrade


cd universal/
./installFixPack 

DBI1073E  The -b <baseInstallPathOfDB2> is required for the installer
      script installFixPack.


Enter full path name for the install directory -

------------------------------------------------
/opt/ibm/db2/V9.7
DBI1017I  installFixPack is updating the DB2 product(s) installed in
      location /opt/ibm/db2/V9.7.


DB2 installation is being initialized.

 Total number of tasks to be performed: 39 
Total estimated time for all tasks to be performed: 1432 

Task #38 end 

Task #39 start
Description: Updating existing DB2 instances 
Estimated time 60 second(s) 
Task #39 end 

The execution completed successfully.

For more information see the DB2 installation log at
"/tmp/installFixPack.log.35600".


$ /opt/ibm/db2/V9.7/instance/db2iupdt db2inst1

DBI1070I  Program db2iupdt completed successfully.

( as db2inst1 )

$ db2level

DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL09075" 
with level identifier "08060107".
Informational tokens are "DB2 v9.7.0.5", "s111017", "IP23292", and Fix Pack 
"5".
Product is installed at "/opt/ibm/db2/V9.7".


$ db2start

02/13/2013 16:22:10     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

$ db2 -tvf createDatabase.sql

create database BPMDB automatic storage yes  using codeset UTF-8 territory US pagesize 32768

connect to BPMDB

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = BPMDB


grant dbadm on database to user db2inst1
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0554N  An authorization ID cannot grant a privilege or authority to itself. 
SQLSTATE=42502

UPDATE DB CFG FOR BPMDB USING LOGFILSIZ 4096 DEFERRED
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

UPDATE DB CFG FOR BPMDB USING LOGSECOND 64 DEFERRED
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

connect reset
DB20000I  The SQL command completed successfully.

$ db2 connect to bpmdb

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.5
 SQL authorization ID   = DB2INST1
 Local database alias   = BPMDB


$ db2 -tvf createTable_ProcessServer.sql

CREATE TABLE LSW_METRIC ( "METRIC_ID" CHAR(36) NOT NULL, "UNIT" DECIMAL(12,0) NOT NULL, "ROLLUP_METRIC_REF" CHAR(36), "ROLLUP_MULTIPLIER" DECIMAL(20,8), "XML_DATA" BLOB(2G), "NAME" VARCHAR( 256) NOT NULL, "DESCRIPTION" CLOB(2G), "GUID" VARCHAR( 512) NOT NULL, "VERSION_ID" CHAR(36) NOT NULL, "LAST_MODIFIED" TIMESTAMP NOT NULL, "LAST_MODIFIED_BY_USER_ID" DECIMAL(12,0) NOT NULL )
DB20000I  The SQL command completed successfully.

ALTER TABLE LSW_METRIC ADD CONSTRAINT "NAME" CHECK (CHARACTER_LENGTH("NAME",CODEUNITS16) <= 64)  ENFORCED ENABLE QUERY OPTIMIZATION
DB20000I  The SQL command completed successfully.

ALTER TABLE LSW_METRIC ADD CONSTRAINT "GUID" CHECK (CHARACTER_LENGTH("GUID",CODEUNITS16) <= 128)  ENFORCED ENABLE QUERY OPTIMIZATION
DB20000I  The SQL command completed successfully.

CREATE TABLE LSW_SLA ( "SLA_ID" CHAR(36) NOT NULL, "ITEM_TYPES" DECIMAL(12,0) NOT NULL, "XML_DATA" BLOB(2G) NOT NULL, "PARTICIPANT_REF" CHAR(36), "NAME" VARCHAR( 256) NOT NULL, "DESCRIPTION" CLOB(2G), "GUID" VARCHAR( 512) NOT NULL, "VERSION_ID" CHAR(36) NOT NULL, "LAST_MODIFIED" TIMESTAMP NOT NULL, "LAST_MODIFIED_BY_USER_ID" DECIMAL(12,0) NOT NULL )
DB20000I  The SQL command completed successfully.

ALTER TABLE LSW_SLA ADD CONSTRAINT "NAME" CHECK (CHARACTER_LENGTH("NAME",CODEUNITS16) <= 64)  ENFORCED ENABLE QUERY OPTIMIZATION
DB20000I  The SQL command completed successfully.

...
INSERT INTO LSW_USR_GRP_MEM_XREF("USER_ID", "GROUP_ID") VALUES (9, 4)
DB20000I  The SQL command completed successfully.

INSERT INTO LSW_USR_GRP_MEM_XREF("USER_ID", "GROUP_ID") VALUES (9, 11)
DB20000I  The SQL command completed successfully.

INSERT INTO LSW_USR_GRP_MEM_XREF("USER_ID", "GROUP_ID") VALUES (9, 15)
DB20000I  The SQL command completed successfully.

INSERT INTO LSW_USR_GRP_MEM_XREF("USER_ID", "GROUP_ID") VALUES (9, 16)
DB20000I  The SQL command completed successfully.


PS I dropped the database and recreated to ensure consistency, and avoid duplicate object errors.

So, the moral of the story, get your fix packs right …..

No comments:

Visual Studio Code - Wow 🙀

Why did I not know that I can merely hit [cmd] [p]  to bring up a search box allowing me to search my project e.g. a repo cloned from GitHub...