PI96859: IBM DB2 ANALYTICS ACCELERATOR CATALOG UTS MIGRATION

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• IBM Db2 Analytics Accelerator catalog UTS migration.
  This is the forward fit of APAR PI91468 from Db2 11 to Db2 12.
  IDAAV7R1/K
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users of Db2 12 for z/OS who use                         *
  * IBM Db2 Analytics Accelerator                                *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * (1) Revise sample job DSNTIJAS to                            *
  *     create Db2 query acceleration                            *
  *     tables in their own universal                            *
  *     table space (UTS).                                       *
  * (2) Provide sample job DSNTIJAM to                           *
  *     migrate existing Db2 query                               *
  *     acceleration tables from a                               *
  *     single segmented table space to                          *
  *     individual UTSs.                                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  APAR PI96858 provides IBM Db2 Analytics Accelerator
  support to tolerate Db2 query acceleration table setup in the
  old segmented table space or the new universal table spaces
  (UTS).  Use of individual table spaces improves performance
  and solves the timeout issues during concurrent query
  acceleration database accesses with a long running job.
  
  This APAR, PI96859, modifies DSNTIJAS, the job for creating
  the Db2 query acceleration database, to create each query
  acceleration table in its own UTS rather than all query
  acceleration tables in a single segmented table space.
  
  It also provides a new job, DSNTIJAM, that can be used to
  migrate the tables in an existing Db2 query acceleration
  database from a single segmented table space to individual
  UTSs.
  

Problem conclusion

Temporary fix

Comments

• This APAR modifies the existing sample job DSNTIJAS
  to create Db2 query acceleration tables in their own
  universal table space (UTS).
  
  To facilitate the migration of existing Db2 query
  acceleration tables from a single segmented table space
  to individual UTSs, this APAR provides a sample job,
  DSNTIJAM, that drives a Db2 REXX exec called DSNTATA
  to provide the migration.
  
  This APAR is logically dependent on PI96858, which provides
  IBM Db2 Analytics Accelerator support to tolerate
  Db2 query acceleration table setup in the old segmented
  table space or the new universal (UTS) table spaces.
  
  The following actions are required before the PTF for this
  APAR is applied:
  o Non-data sharing group:
    o PTF for APAR PI96858 (UI56078) must be applied
    o Db2 is restarted after PTF UI56078 is applied
  o Data sharing group:
    o PTF for APAR PI96858 (UI56078) must be applied on all
      members of the data sharing group
    o Each member is restarted after PTF UI56078 is applied on
      the member
  
  -------------------------------------------------------------
  Introducing job DSNTIJAM and REXX exec DSNTATA
  -------------------------------------------------------------
  DSNTATA migrates the following Db2 query acceleration tables
  from the SYSACCEL table space to their own UTS:
    ----------------------------------------
    |           Table           |  UTS     |
    ----------------------------------------
    | SYSACCELERATORS           | SYSTSACC |
    | SYSACCELERATEDTABLES      | SYSTSACT |
    | SYSACCELERATEDPACKAGES    | SYSTSACP |
    | SYSACCELERATEDTABLESAUTH  | SYSTSATA |
    ----------------------------------------
  
  The job prolog of DSNTIJAM explains how to customize it for
  use on your system.
  
  Job DSNTIJAM contains a single job step that calls Db2 REXX
  exec DSNTATA, which accepts eight parameters:
  o Db2 Subsystem ID
  o Authorization ID to be used to run DSNTATA
  o Buffer pool settings in the CREATE TABLESPACE statements
  o Buffer pool settings in the CREATE INDEX statements
  o Storage group settings in the CREATE TABLESPACE statements
  o Storage group settings in the CREATE INDEX statements
  o Restrict On Drop:
    Valid values are:
    o Y: RESTRICT ON DROP applies to the new Db2 query
         acceleration tables which reside in their own UTS
    o N: RESTRICT ON DROP does not apply to the new Db2 query
         acceleration tables which reside in their own UTS
  o Preview Mode:
    Valid values are:
    o Y: No changes are processed. Generated SQL statements are
         outputted to the SYSTSPRT DD.
    o N: Changes are processed. Processed SQL statements are
         outputted to the SYSTSPRT DD.
  
  Example call:
    DSNTATA +
      DSN SYSADM BP0 BP0 SYSDEFLT SYSDEFLT Y Y
  
  When the migration of the Db2 query acceleration tables
  completed successfully, RC=0 is returned by DSNTIJAM/DSNTATA
  and the following messages are outputted to the DSNTATA
  SYSTSPRT DD:
    DSNTATA completed successfully...
    *** End   RC=0
  Otherwise, a return code greater than zero is returned
  by DSNTIJAM/DSNTATA and depending on the type of error,
  the following sample messages are outputted to the
  DSNTATA SYSTSPRT DD:
  o Input parameter error:
    DSNTATA Error: The argument for parameter 7,
                   RESTRICT_ON_DROP, must be Y or N.
    ABORT: unexpected parameter setting.
    *** End   RC=8
  o SQL error:
    DSNTATA SQL Error: SQL statement - setAuthID: SET CURRENT
                       SQLID
    SQLCODE =-805
    SQLERRMC=STLEC1..DSNREXX.0E4C9F5F03F5F2F5;DSNREXX;03
    SQLERRP =DSNXEPM
    SQLERRD =-251,0,0,-1,0,0
    SQLWARN = , , , , , , , , , ,
    SQLSTATE=51002
    ABORT and ROLLBACK: unexpected SQLCODE.
    *** End   RC=8
  
  To find out which package needs to rebound after the UTS
  migration, run the following query:
  SELECT * FROM SYSIBM.SYSPACKDEP WHERE BNAME LIKE 'SYSACCEL%';
  
  Note that if the post-migration steps described in the HOLD text
  are not completed, ABEND04E RC00C90101 in DSNICUBC ERQUAL5005
  can occur for other running workloads.  Re-run the workload
  after all post-migration steps are done.
  
  ADDITIONAL KEYWORDS:
  IDAAV4R1/K IDAAV5R1/K IDAAV7R1/K
  

APAR Information

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

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

  UI56897

Modules/Macros

• DSNTIJAM DSNTIJAS DSNTATA
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI56897

     UP18/07/13 P F807

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.