PM99683: NEW FUNCTION ON DB2 11 FOR Z/OS

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• New function on DB2 11 for z/OS.                     (DN1760)
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 11 for z/OS users who want to use new    *
  *                 auditing capabilities                        *
  ****************************************************************
  * PROBLEM DESCRIPTION: PM99683 provides preconditioning code   *
  *                      for integrated auditing support to      *
  *                      allow for automatic tracking of some    *
  *                      audit information including who         *
  *                      modified the data in the table and      *
  *                      what SQL operation modified the data    *
  *                      in the table.                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  APAR PM99683 and APAR PI15298:
  The PTFs for PM99683 (the preconditioning APAR), PI15298 (the
  enabling APAR), and PI15666 (the LOAD utility feature APAR)
  deliver integrated auditing support using non-deterministic
  generated expression columns to allow for automatic tracking
  of some audit information including:
  a. who modified the data in the table
  b. what SQL operation modified the data in the table
  
  For this DB2 auditing support, system-period temporal tables
  can be defined with non-deterministic expression columns.  The
  extensions are also useful for non-temporal applications that
  want to record auditing data.
  
  Here is an example of how to use the feature:
  
  1. create system-period temporal table STT
  CREATE TABLE STT
  (POLICY_ID CHAR(4) NOT NULL ,
   COVERAGE INT NOT NULL ,
   SQLID VARCHAR(8) GENERATED ALWAYS AS (CURRENT SQLID),
   DCOP CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION),
   SYS_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS
   AS ROW BEGIN,
   SYS_END TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
   TRANS_ID TIMESTAMP(12) GENERATED ALWAYS
   AS TRANSACTION START ID ,
   PERIOD SYSTEM_TIME(SYS_START,SYS_END));
  
  2. create history table STT_HIST
  CREATE TABLE STT_HIST
  (POLICY_ID CHAR(4) NOT NULL ,
   COVERAGE INT NOT NULL ,
   SQLID VARCHAR(8) ,
   DCOP CHAR(1) ,
   SYS_START TIMESTAMP(12) NOT NULL,
   SYS_END TIMESTAMP(12) NOT NULL,
   TRANS_ID TIMESTAMP(12));
  
  3. enable system versioning by building the link between STT
  and STT_HIST
  ALTER TABLE STT ADD VERSIONING USE HISTORY TABLE STT_HIST
  ON DELETE ADD EXTRA ROW ;
  
  Assume that on Jan. 1, 2014, user1 inserts a row:
  SET CURRENT SQLID = 'USER1';
  INSERT INTO STT (policy_id,coverage) VALUES ('A123', 12000);
  COMMIT;
  
  After the insert, the tables then contain the following:
  STT
  ('A123',12000,'USER1','I','2014-01-01...','9999-12-30...',...)
  
  STT_HIST
  empty
  
  Later, on Feb. 15, 2014, user2 updates the row:
  SET CURRENT SQLID = 'USER2';
  UPDATE STT SET coverage = coverage + 1000;
  COMMIT;
  
  Results in:
  STT
  ('A123',13000,'USER2','U','2014-02-15...','9999-12-30...',...)
  
  STT_HIST
  ('A123',12000,'USER1','I','2014-01-01...','2014-02-15...',...)
  
  On Apr. 30, 2014, user3 deletes the row:
  SET CURRENT SQLID = 'USER3';
  DELETE FROM STT;
  COMMIT;
  
  Results in:
  STT
  empty
  
  STT_HIST
  ('A123',12000,'USER1','I','2014-01-01...','2014-02-15...',...)
  ('A123',13000,'USER2','U','2014-02-15...','2014-04-30...',...)
  ('A123',13000,'USER3','D','2014-04-30...','2014-04-30...',...)
  
  In STT_HIST table, row 1 records the history resulting from the
  update statement issued by user2; row 2 records the history
  resulting from the delete statement issued by user3; row 3
  records additional information about the delete statement
  itself, storing the id of the user that initiated the delete
  operation. The 3rd row is recorded in the history table because
  the new ON DELETE ADD EXTRA ROW clause was specified in the
  definition of the system-period temporal table.  Notice that
  the values of the history table columns that correspond to
  the row-begin and row-end columns both have the same value,
  reflecting the time of the deletion.
  
  Note that row 2 and row 3 are identical for user data
  (policy_id and coverage).  The difference is in the auditing
  columns - the new generated expression columns which record
  who initiated the action, and which data change operation the
  row represents. A select from the system-period temporal
  table with explicit FOR SYSTEM_TIME period specifications
  will not return row 3 from history table.
  
  Given query, row 1 and row 2, but not row3, will be returned:
  SELECT * FROM STT
  FOR SYSTEM_TIME FROM '2001-09-15-12.00.00.000000'
                    TO '2099-09-15-12.00.00.000000';
  
  
  
  APAR PI15666:
  PI15666 is a LOAD utility APAR that requires the fixes of
  PM99683 and PI15298.  The PTFs for PM99683, PI15298, and
  PI15666 enable new LOAD OVERRIDE option.  Please refer to
  PI15666 for more details.
  

Problem conclusion

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI18913

Modules/Macros

• DSNGDADC DSNGDART DSNGDBRT DSNGDVAR DSNGDVRT
  DSNHSM6C DSNIBNRF DSNICMT2 DSNIDALC DSNIDLER DSNILREP DSNIMRST
  DSNINUKY DSNIRBLD DSNIRELK DSNIRLPG DSNIRSVP DSNISRTI DSNITDLE
  DSNIZMOS DSNTIAM  DSNTIA1  DSNUEDRT DSNUEXDB DSNUGDDF DSNUGPPF
  DSNUGPRT DSNURFTB DSNURNFL DSNUROFL DSNURUFL DSNURURT DSNURWBG
  DSNURWUF DSNUUGLD DSNUULGL DSNUUUCA DSNXEADT DSNXESQL DSNXIAB3
  DSNXIALC DSNXIATB DSNXICB1 DSNXICB3 DSNXIEPL DSNXISB5 DSNXOADT
  DSNXOCAS DSNXOCL  DSNXOCT  DSNXOFF  DSNXOIN  DSNXOMD  DSNXOMRG
  DSNXOMTX DSNXONV  DSNXOP0  DSNXORSO DSNXOTDA DSNXOVD  DSNXTFS
  DSNXTGS  DSNXTSTG HDBBB10J
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI18913

     UP14/07/04 P F407

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.