CA Log Analyzer UNDO file UPDATE statements incomplete for GENERATED ALWAYS TIMESTAMP column.

book

Article ID: 131327

calendar_today

Updated On:

Products

CA-24X7 High-Availability Manager for DB2 for z/OS CA-Batch Processor Compile QQF CA Data Compressor for DB2 for z/OS Data Navigator for DB2 UDB for z/OS CA-DB Delivery for DB2 CA Unicenter NSM CA Log Compress DBA for DB2 Guide Online CA InfoRefiner Advantage InfoRefiner Advantage InfoRefiner Maint Upgrade CA InfoTransport Advantage InfoTransport Maint Upgrade Online Reorg for DB2 for z/OS CA RC/Update for DB2 for z/OS Query Analyzer RI Editor for DB2 for z/OS DB2 TOOLS- DATABASE MISC

Issue/Introduction

Creating UNDO SQL file using CA Log Analyzer for Db2 for z/OS (PLA).

The UNDO SQL file has incomplete UPDATE statements as in the 
following example where the SET contains no value:

UPDATE creator.tablename 
SET 
WHERE COL1 = 12345678
AND COL2 = 123456


The table DDL shows that DATA CAPTURE CHANGES in enabled.

One item in common where the SET COLUMN = VALUE is not generated
is after a column was ALTER added with the following definition: 

,COL3 TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL 
GENERATED ALWAYS 
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP 

Environment

Release:
Component: PLA

Resolution

Analysis shows that an UPDATE of the table replacing column(s) with
the same value(s) already in the row is causing Db2 to modify the
GENERATED ALWAYS ROW CHANGE TIMESTAMP column but nothing
else is logged which PLA can utilize to format the UPDATE DML.

PLA is working as designed in this scenario.  Updating a row
with the exact column data already present in the row would not appear
to be a common occurrence.  Recommendation is to comment out
the SQL statements with the empty SET clause to resolve.