Migrating query acceleration tables to universal table spaces

This topic provides instructions for migrating query acceleration tables from a single segmented table space (SYSACCEL) to individual universal table spaces (UTSs).

Complete the following pre-migration steps:

1. Apply the PTF for APAR PI96858 (UI56078) and for APAR PI96859 (UI56897), or verify that these PTFs are applied.
   In a Db2 data sharing group, these PTFs must be applied on all members of the group.
2. After the PTFs for APAR PI96858 are applied, restart Db2 or, for a data sharing group, restart each member.
3. Verify that the following Db2 query acceleration tables are present in the DSNACCEL.SYSACCEL table space and that they are at same level as PI91620 (UI54293):
   • SYSACCEL.SYSACCELERATORS
   • SYSACCEL.SYSACCELERATEDTABLES
   • SYSACCEL.SYSACCELERATEDPACKAGES
   • SYSACCEL.SYSACCELERATEDTABLESAUTH

   Also, verify that column 13 of the SYSACCEL.SYSACCELERATEDTABLES table is the REMOTELOCATION column.
4. Drop, re-create, and rename any user-defined table spaces in the DSNACCEL database that are named SYSTSACC, SYSTSACT, SYSTSACP, or SYSTSATA.
   These names are reserved for the new UTSs in which the Db2 query acceleration tables will reside.
5. Drop any user-defined objects in table space DSNACCEL.SYSACCEL, and re-create them in another table space.

   When you run the DSNTIJAM job to migrate the query acceleration tables, the DSNACCEL.SYSACCEL table space and all of its objects will be dropped and re-created. However, only the following query acceleration tables and indexes will be re-created in the appropriate table space:

   • SYSACCEL.SYSACCELERATORS
   • SYSACCEL.SYSACCELERATEDTABLES
   • SYSACCEL.SYSACCELERATEDPACKAGES
   • SYSACCEL.SYSACCELERATEDTABLESAUTH
   • SYSACCEL.DSNACC01
   • SYSACCEL.DSNACT01
   • SYSACCEL.DSNACP01
   • SYSACCEL.DSNACP02
   • SYSACCEL.DSNATA01

   Therefore, if you created any user-defined objects in the DSNACCEL.SYSACCEL table space other than the ones in the previous list, you must drop and re-create them in another table space.

6. Rename any table that has the same name as the following tables.
   These names are reserved for the intermediate query acceleration tables that will be used during migration.

   • SYSACCEL.SYSACCELERATOR1
   • SYSACCEL.SYSACCELERATEDTABLE1
   • SYSACCEL.SYSACCELERATEDPACKAGE1
   • SYSACCEL.SYSACCELERATEDTABLESAUTH1
7. Rename any index that has the same name as the following indexes.
   These names are reserved for the indexes of the intermediate query acceleration tables that will be used during migration.

   • SYSACCEL.DSNACC1
   • SYSACCEL.DSNACT1
   • SYSACCEL.DSNACP1
   • SYSACCEL.DSNACP2
   • SYSACCEL.DSNATA1
8. Save any dependencies and authorization records for the following Db2 query acceleration objects so that you can re-create them after migration.
   These dependencies can include views, triggers, and masks, as well as authorizations, referential integrity definitions, check constraints, history versioning, and comments.
   • SYSACCEL.SYSACCELERATORS
   • SYSACCEL.DSNACC01
   • SYSACCEL.SYSACCELERATEDTABLES
   • SYSACCEL.DSNACT01
   • SYSACCEL.SYSACCELERATEDPACKAGES
   • SYSACCEL.DSNACP01
   • SYSACCEL.DSNACP02
   • SYSACCEL.SYSACCELERATEDTABLESAUTH
   • SYSACCEL.DSNATA01

   Additionally, if the DSN6SPRM.ABIND subsystem parameter is set to NO, identify all packages that are dependent on the DSNACCEL.SYSACCEL table space. You must explicitly rebind these packages after you complete the migration.

   Attention: Because the DSNACCEL.SYSACCEL table space will be dropped during migration, failure to complete this step prior to running DSNTIJAM can result in the loss of administrative control of the accelerator and render it unusable and not easily recoverable.
9. Set the Db2 DSN6SPRM.QUERY_ACCEL_OPTIONS subsystem parameter to a value of 98, which is a special serviceability option that indicates that the migration of the query acceleration tables is in progress. For a data sharing group, set this subsystem parameter to 98 for each member of the data sharing group. Setting this subsystem parameter to 98 eliminates interference from other Db2 workloads.
   1. Create a temporary copy of your current customized copy of the DSNTIJUZ job.
   2. Edit the temporary copy of DSNTIJUZ and set the QUERY_ACCEL_OPTIONS parameter to a value of 98. If necessary, remember to add a continuation character in column 72.
   3. Run the first two steps of the temporary DSNTIJUZ job.
   4. After the job completes, issue the -SET SYSPARM command, or stop and restart Db2 to enable this change.
   5. Delete the temporary copy of DSNTIJUZ.
10. Issue the STOP ACCEL command (all query offload), stop all query accelerator data maintenance (incremental update and stored procedure jobs), and disconnect Data Studio from the database. For a data sharing group, complete these steps for each member of the data sharing group.