Specifying options for the Migrate Access Path Statistics job

Perform this procedure to use the initial panels of the Migrate Access Path Statistics function to specify processing options for the job.

Before you begin

Because the Update job updates the Db2 catalog's statistics, you might want to back up the statistics before you update them. Use one of the following methods to back up the Db2 catalog statistics:

Before running the Update statements, run Migrate Access Path Statistics, using the target subsystem as the target and source. The product preserves these original catalog statistics in the Update file. Use this file to restore the values to their original state.
Run a DSN1COPY of DSNDB06. This action provides a point-in-time backup of your Db2 catalog.

Warning

Migrate Access Path Statistics uses the UPDATE data set, which is a sequential data set. Be careful not to overwrite any data in this data set that might contain statistics that you want to save.

To specify options for the Migrate Access Path Statistics job

On the SQL Explorer main menu, type 6 (for Migrate Access Path Statistics).
Change the SSID locations, if necessary, and press Enter.The Migrate Access Path Statistics Specification panel is displayed.
Warning
Important
You cannot edit the Process Mode, because Migrate Access Path Statistics jobs run in batch mode.
(BMC.DB2.SPE2504)
(BMC.DB2.SPE2404)
PSSPM010 --------- Migrate Access Path Statistics Specification ---------------
Command ===>

Process Mode   B (B=Batch)
From SSID . . DEDR               To SSID . . DHZB
From Location DEBF               To Location DIY

Database and Table Space Name to Migrate (wildcards may be used)

Database.Tablespace PSSQX91.PSSS0391

(Optional) Translate Current Object Names to New Names (press F1 for Help)

Current Value                                  New Value
--------------------------------------------- ------------------------------
DB Name . . . PSSQX91                         TSSQX91
TS Name . . . PSSS0391                        TSSS0391
TB Creator . . PSS                             TSS
TB Name . . . PSST*_D91S03                    TSST*_D91S03
IX Creator . . PSS                             TSS
IX Name . . . PSSX0%_D91S03T01                TSSX0%_D91S03T01

    Additional Options
Increase/Decrease   I (I/D)
Percentage          100
Warning
Important
- In this example, the extract process uses DDF from subsystem DEDR to connect to and extract statistics from DEBF. Similarly, the update process uses DDF from subsystem DHZB to connect to and update DIY with the statistics from DEBF.
- In the JCL that SQL Explorer generates, the From Location and To Location fields appear as DDFLOC SYSIN cards.
In the From SSID field, type the subsystem name (the source subsystem) from which you want to migrate access path statistics.
In the To SSID field, type the name of the subsystem (the target subsystem) to which you want to migrate access path statistics.The target subsystem can be on a version of Db2 that is different from the source subsystem.
WarningSome content is unavailable due to permissions.
In the From Location field, type the name of the DDF location from which subsystem access path statistics is migrated.
In the To Location field, type the name of the DDF location to which access path statistics is migrated.
Warning
Important
The To Location and From Location fields represent the DDF location names defined in the DDF table SYSIBM.LOCATIONS.
- The To Location is the DDF location name defined in the SYSIBM.LOCATIONS table associated with the To SSID subsystem.
- The From Location is the DDF location name defined in the SYSIBM.LOCATIONS associated with the From SSID subsystem.
These parameters let you extract or update Db2 statistics from the defined Db2 subsystems to the specified locations using Db2. The fields are optional. You can specify them separately with either or both fields left blank .
In the Database and Table Space Name to Migrate field, type the name of the table space that you want to migrate, in databaseName.tableSpaceName format.The Wildcard characters for Migrate Access Path Statistics table shows the wildcard characters you can use in this field:
Wildcard characters for Migrate Access Path Statistics
Wildcard character
Matches
! (Exclamation mark)
(BMC.DB2.SPE2504)
Any single character
% (percent sign)* (asterisk)
A string of zero or more characters
(BMC.DB2.SPE2504)
Warning
Important
The single character wildcard has been changed from ‘_’ (underscore) to ‘!’ (exclamation mark), introducing a backward incompatibility. This change may cause existing batch jobs to produce different results. To revert the single character wildcard indicator back to ‘_’ (underscore), apply PTF BQU5792.
(Optional) If the target object names are different from the source object names, apply them at Translate Current Object Names to New Names.Specify a Current Value for each of the source object names and a New Value for each of the target object names. You can specify values for the following objects:
- DB Name (database name)
- TS Name (table space name)
- TB Creator (table creator)
- (BMC.DB2.SPE2504) TB Name (table name)
- IX Creator (index creator)
- IX Name (index name)
  You can use the wildcard characters shown in the Wildcard characters for Migrate Access Path Statistics table.
  Warning
  Important
  The objects that you want to update must already exist on the target subsystem. If the objects do not exist, the Update job issues the following message: The following row not found for update.
  If the object name includes a literal question mark, enclose the question mark in a double set of quotation marks, and enclose the object name in quotation marks. For example, to represent table creator RDAKG?, type "RDAKG""?""" (without spaces or commas) in the TBCREATOR field.
  Action item/Note:
  In version 12, Migrate Stats Extract and Object Name Translations occur in the same step. In version 13, however, the Migrate Stats Extract occurs first, followed by the Object Name Translation step (PSSMIGRU). Therefore, it is important that you regenerate the JCL for Migrate Stats for version 13 to behave the same as version 12.
  The Migrate Access Path Statistics Specification panel allows for only one set of pattern translation input parameters. You can manually insert additional translation sets by editing the Migrate Access Path Statistics JCL. For more information, see Specification-of-patterns-for-translating-object-names.
(Optional) Type any character in the space beside the Additional Options field to select additional Migrate Access Path Statistics options.For more information, see Setting-Migrate-Access-Path-Statistics-options.

Wildcard character	Matches
! (Exclamation mark) (BMC.DB2.SPE2504)	Any single character
% (percent sign)* (asterisk)	A string of zero or more characters

(BMC.DB2.SPE2404) (Optional) You can also use the following fields under Additional Options:

Field	Description
Increase/Decrease	Specify whether to increase (I or i) or decrease (D or d) the statistics values of the number of records and pages to be migrated.
Percentage	Specify the percentage by which to increase or decrease the statistics value to migrate. For increase, you can specify a value from 0 through 999. For decrease, you can specify a value from 0 through 100.

Press Enter to display the Batch Job panel.For information about completing this panel, see Specifying-JCL-options-in-batch-mode.
Press Enter to view the generated JCL.Generated JCL for Migrate Access Path Statistics shows an example of the sample generated JCL.
Warning
Important
If any of the data sets do not exist, the Allocate Data Set panel is displayed. For instructions, see Allocating-a-data-set-for-a-specific-job.
(BMC.DB2.SPE2404) (BMC.DB2.SPE2504)
//RDAGXR2M JOB (PDOM,1605A), 'MIGRATE STATS',REGION=0K,
//             MSGCLASS=X,CLASS=A,NOTIFY=&SYSUID
/*ROUTE XEQ BMCPLX1
/*JOBPARM SYSAFF=DB2A
//*-----------------------------------------------------------------
//*        MEMBER: PSSSMIGR
//*        EXECUTION OF MIGRATE ACCESS PATH STATISTICS
//*-----------------------------------------------------------------
//MIGRATE EXEC PGM=PSSAWK,REGION=0M,
// PARM='-f SRC(PSSMIEXT) -v SSID=DEDR -v PLAN=GXRALIAS'
//STEPLIB DD DISP=SHR,DSN=BMCPERF.LOAD
//         DD DISP=SHR,DSN=CSGI.SASC.V700C.LINKLIB
//         DD DISP=SHR,DSN=SYS3.DEDR.DSNEXIT
//         DD DISP=SHR,DSN=SYS2.DSNLOAD
//ABNLIGNR DD DUMMY
//SYSOUT   DD SYSOUT=*
//STDOUT   DD SYSOUT=*
//SRC      DD DISP=SHR,DSN=BMCPERF.CLIST
//UPDATE   DD DISP=SHR,DSN=RDAGXR1.SQLXPLR.UPDATE
//SYSTERM DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD DISP=SHR,DSN=RDAGXR2.SQLXPLR.SYSPRINT(SQLX0049)
//SYSIN    DD *
DDFLOC DEBF
MIGSTATS TABLESPACE PSSQX91.PSSS0391
INCLUDE (CS,RT)
/*
//*
//*-----------------------------------------------------------------*/
//*--THE FOLLOWING JCL IS FURNISHED FOR THE SECOND JOB.            -*/
//*--MODIFY THE JOBCARD AND SUBMIT AFTER THE MIGRATE STEP          -*/
//*--COMPLETES.                                                    -*/
//
//*
//INSTPROC   PROC PVTOSSID=DHZB
//*-----------------------------------------------------------------
//*    THIS STEP DELETES THE SPECIFIED FILE IF IT EXISTS, ELSE
//*    CREATES ONE AND DELETES THE FILE.
//*-----------------------------------------------------------------
//MODDEL EXEC PGM=IEFBR14
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSDUMP DD SYSOUT=*
//DDNM1   DD DSN=RDAGXR1.SQLXPLR.UPDATE.&PVTOSSID,
//        DISP=(MOD,DELETE,DELETE),
//        UNIT=SYSALLDA,DCB=(RECFM=VB,LRECL=4726,BLKSIZE=27998),
//        SPACE=(4104,(1000,1000),RLSE)
//*-----------------------------------------------------------------
//*    MEMBER: PSSMIGRU
//*    APPLYING RULES FOR STATS EXTRACTED,SPECIFIC TO TARGET Db2
//*    SUBSYSTEMS.
//*    * * * NOTE * * *
//*-----------------------------------------------------------------
//RULE EXEC PGM=PSSMIGRU,REGION=0M
//STEPLIB DD DISP=SHR,DSN=PSED1.BASE.LINKLIB
//         DD DISP=SHR,DSN=PSSD1.BASE.LINKLIB
//         DD DISP=SHR,DSN=SCC.TEST1211.LOAD
//         DD DISP=SHR,DSN=CSGI.MAINVIEW.BMCPSWD
//         DD DISP=SHR,DSN=DOM.V11R2ALL.LOAD2
//         DD DISP=SHR,DSN=CSG.DEDR.DSNEXIT
//         DD DISP=SHR,DSN=CSGI.DB2V12M.DSNLOAD
//SYSOUT   DD SYSOUT=*
//STDOUT   DD SYSOUT=*
//EXTRACT DD DISP=SHR,DSN=RDAGXR1.SQLXPLR.UPDATE
//UPDATE   DD DSN=RDAGXR1.SQLXPLR.UPDATE.&PVTOSSID,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSALLDA,DCB=(RECFM=VB,LRECL=4726,BLKSIZE=27998),
//            SPACE=(4104,(1000,1000),RLSE)
//SYSTERM DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
DBNAME PSSQX91 TSSQX91
TSNAME PSSS0391 TSSS0391
TBCREATOR PSS TSS
TBNAME PSST*_D91S03 TSST*_D91S03
IXCREATOR PSS TSS
IXNAME PSSX0%_D91S03T01 TSSX0%_D91S03T01
INCDEC I
PERCENTAGE 100
/*
//*-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
//*                   * * * NOTE * * *
//*        VERIFY THAT THE DSNEXIT AND DSNLOAD LIBRARIES
//*        IN THE STEPLIB BELOW ARE FOR THE SSID IN THE
//*        PARM STRING JUST BELOW. MODIFY AS NEEDED.
//*-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
//UPDATE EXEC PGM=PSSAWK,REGION=0M,
// PARM='-f SRC(PSSMIUPD) -v SSID=&PVTOSSID -v PLAN=GXRALIAS'
//STEPLIB DD DISP=SHR,DSN=PSED1.BASE.LINKLIB
//         DD DISP=SHR,DSN=PSSD1.BASE.LINKLIB
//         DD DISP=SHR,DSN=SCC.TEST1211.LOAD
//         DD DISP=SHR,DSN=CSGI.MAINVIEW.BMCPSWD
//         DD DISP=SHR,DSN=DOM.V11R2ALL.LOAD2
//         DD DISP=SHR,DSN=CSG.DHZB.DSNEXIT
//         DD DISP=SHR,DSN=CSGI.DB2V12M.DSNLOAD
//ABNLIGNR DD DUMMY
//SYSTERM DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//STDOUT   DD SYSOUT=*
//SRC      DD DISP=SHR,DSN=PSSD1.DBENG.PSSCLIB
//         DD DISP=SHR,DSN=PSED1.DBENG.PSECLIB
//         DD DISP=SHR,DSN=PSSD1.DBBASE.PSSCLIB
//         DD DISP=SHR,DSN=PSED1.DBBASE.PSECLIB
//UPDATE   DD DISP=SHR,DSN=RDAGXR1.SQLXPLR.UPDATE.&PVTOSSID
//SYSPRINT DD DISP=SHR,DSN=RDAGXR2.SQLXPLR.SYSPRINT(SQLX0050)
//SYSIN    DD *
DDFLOC DIY
MIGSTATS UPDATE ALL
COMMIT NOERROR
COMMITFREQ TS
ONERROR QUIT
RETRIES 5
DELETE NO
STATSTIME CURRENT
/*
//           PEND               //*END OF PROCEDURE
//STEP1      EXEC INSTPROC
//*-----------------------------------------------------------------
//*    NOTE :
//* INVOKE INSTPROC BY SUPPLYING THE TARGET Db2 SUBSYSTEM WHERE
//* WE NEED TO UPDATE STATISTICS.
//*
//* USE THE BELOW EXAMPLE, IT CAN BE REPEATED FOR AS MANY TARGET
//* SSID'S AS NEEDED.
//* a.CHANGE THE STEPNAME AND GIVE PVTOSSID=NAME OF TARGET SSID TO BE
//*    UPDATED.
//* b.USE STEPNAME.RULE.SYSIN AND PROVIDE UNIQUE SET OF RULES IN THE
//*    SYSIN PARAMETER
//* c.USE UPDATE.SYSPRINT OVERRIDE TO PROVIDE UNIQUE MEMBER NAME FOR
//*    EACH TARGET DB2'S TO BE UPDATED.
//*-----------------------------------------------------------------
//*STEP2      EXEC INSTPROC,PVTOSSID=DLYG
//*RULE.SYSIN DD *
//*UPDATE.SYSPRINT DD DISP=SHR,DSN=RDAGXR2.SQLXPLR.SYSPRINT(SQLX0050)
Warning
Important
- SQL Explorer generates a DDFLOC card as the first SYSIN card. The DDFLOC card uses the From Location and To Location values entered on the Migrate Access Path Statistics Specification panel.
- The UPDATE step includes the reference to target subsystem. You should still revise the Route and Jobparm Sysaff cards.

Specifying options for the Migrate Access Path Statistics job

Before you begin

To specify options for the Migrate Access Path Statistics job

BMC AMI SQL Explorer for Db2 13.1

On this page