IBM Support

PM99683: NEW FUNCTION ON DB2 11 FOR Z/OS

A fix is available

Subscribe

You can track all active APARs for this component.

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 number

    PM99683

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-10-22

  • Closed date

    2014-06-17

  • Last modified date

    2014-08-04

  • 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.



Document information

More support for: DB2 for z/OS

Software version: B10

Reference #: PM99683

Modified date: 04 August 2014


Translate this page: