PH04538: START TRACE AUDTPLCY TURNS ON IFCID143, 144, 145 FOR TABLES DEFINED WITH AUDIT CLAUSE

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• When an audit policy that specifies an EXECUTE category is
  started, it starts IFCIDs 143, 144, and 145. Any table that is
  defined with the AUDIT clause (CHANGES, ALL) will also be
  audited. The IFCIDS 143, 144, and 145 are written for both the
  tables defined in SYSIBM.SYSAUDITPOLICIES and the tables
  specified with AUDIT clause. The result is additional
  audit records are recorded for tables defined with the AUDIT
  clause.
  
  For a table audited through audit policy, additional information
  is recorded in the trace record, such as statement ID. Plus,
  every statement in the transaction is audited, not just the
  first one.
  
  Example for INSERT and UPDATE:
  
  CREATE TABLE "IBM"."TBL1_NO_AP"
    (  COL1                SMALLINT     NOT NULL
      ,COL2                  CHAR(80)     NOT NULL
    , PRIMARY KEY  (COL1 )  )
    IN "IBM"."IBMTS"
    AUDIT CHANGES ;
  
  CREATE TABLE "IBM"."TBL1_AP"
    (  COL1                SMALLINT     NOT NULL
      ,COL2                  CHAR(80)     NOT NULL
    , PRIMARY KEY  (COL1 )  )
    IN "IBM"."IBMTS"
    AUDIT NONE;
  
  INSERT INTO SYSIBM.SYSAUDITPOLICIES(AUDITPOLICYNAME,
  OBJECTSCHEMA,
        OBJECTNAME, OBJECTTYPE, EXECUTE)
    VALUES('AUD1','IBM','TBL1_AP',' ','A');
  
  -START TRACE(AU) AUDTPLCY(AUD1)
  
  RESULTS:
  
  For an insert into a table defined as AUDIT NONE and defined in
  the SYSAUDIT Policy table, the IFCID143 record is generated
  with the RBA, STMT ID fields populated:
  
  143 AUDIT FIRST    INSJOB
       WRITE         NETWORKID:  USIBMSY   LUNAME:  SYEC1DB2
                     LUWSEQ:     1
                     DATABASE: 278       LOGRBA:
                     X'000000000000A99B0510'
                     PAGE SET: 2         OBID  :      3
                     STMT ID :                    2
  
  For an update of a table defined with AUDIT CHANGES and not in
  SYSAUDIT Policy table, the IFCID143 record is generated, but not
  all of the fields are populated:
  
  143 AUDIT FIRST    UPDJOB
       WRITE         NETWORKID:  USIBMSY   LUNAME:  SYEC1DB2
                     LUWSEQ:     1
                     DATABASE: 279       LOGRBA:
                     X'00000000000000000000'
                     PAGE SET: 2         OBID  :      3
                     STMT ID :                    0
  
  
  
  Keywords:
  
  IFCID143
  IFCID144
  IFCID145
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 users of IFCID 142, 143, 145                         *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * SQL Objects did not qualify correctly                        *
  * with a mixture of traces using                               *
  * AUDTPLCY as an option and the use of                         *
  * DDL AUDIT options.                                           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  For START TRACE, the AUDTPLCY option was added back in
  version 10 so that policies can be applied for AUDIT traces.
  This feature was an extension to IFCID0142, IFCID0143,
  IFCID0144 and IFCID0145 which AUDITs SQL objects that have
  AUDIT(CHANGES) or AUDIT(ALL) stated in the objects definition.
  .
  The mixture of the two enhancements caused AUDIT traces to
  UNION ALL the qualifiers. For instance, if the object matched
  a policy or the object definition, a trace record would be
  written.
  .
  Note: In PH04538, IFCID 144 (first read of audited object)
  will not be modified at this time and will be in a future
  APAR.
  

Problem conclusion

• DB2 has been modified to honor both trace types as separate
  qualifiers.
  

Temporary fix

Comments

APAR Information

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

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

  UI68652 UI68654

Modules/Macros

• DSNWVCSP DSNXOIWS DSNXOTR3 DSNISRTI DSNXIATB DSNXOACR DSNWVCST
  DSNXICTB DSNXOOP  DSNXIDTB DSNXOUA
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI68652

     UP20/04/03 P F004

• RC10 PSY UI68654

     UP20/04/03 P F004

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.