ORDER BY statement generation.

Document ID : KB000014161
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

RCM always generates an UNLOAD with ORDER BY. For some migration purposes we don't need this order by that is CPU consuming and also causes longer elapsed times. In the generated worklist we can fix it by erasing the order by clause but in the Snapshot phase we don't have that ability.

Question:

Is there an option in RCM that would allow the control of this ORDER BY statement generation?
This should work in both the worklist and Snapshot worklist.

Environment:
Db2 for Z/OS
Answer:

There is a parm in hlq.CDBAPARM(MIGRATOR) called SORTUNLD which can turn ON or OFF the ORDER BY in the unload. See Additional Information below.

In the MIGRATOR member of hlq.CDBAPARM....add a SORTUNLD parm.

-------------------------------------------------------------------------
DSLIST     PTIPROD.RD190.PRD.CDBAPARM(MIGRATOR)
****** ***************************** Top of Data ******************************
000001 DEVTYPE (3390)                     /* DEVICE TYPE FOR THE MODEL     */
000002                                    /* UTILITIES SPACE CALCULATIONS. */
000003                                    /* SPECIFY 3380 OR 3390.         */
000004 PDSSHR  (NO)                       /* OUTPUT PDS USAGE.  SPECIFY    */
000005                                    /* YES IF THE ANALYSIS PDS WILL  */
000006                                    /* SHARED BY MULTIPLE USERS.     */
000007 SORTUNLD (NO)  <--------------------------------------ADD THIS LINE IN HERE (YES or NO)        
000008 /*------------------------------------------------------------------*/

Default setting generates code like this:
.DATA
  FASTUNLOAD
   UNLDDN          SYSREC
   LIMIT           0
   OUTPUT-FORMAT   LOAD
   INPUT-FORMAT    TABLE
   VSAM-BUFFERS    80
   SORTNUM         2
   SORTDEVT        SYSDA
   ESTIMATED-ROWS  1
   SQL-ACCESS      EXTENSION
   TRUNCATE        NO
   SELECT DEPT_ID, DEPT_DESC, DEPT_MGR, DEPT_ADDRESS, DEPT_ADDRESS_TWO
   , DEPT_ADDRESS_THREE
   FROM authid.TBDEPT
   NEWOBID 20
   ORDER BY DEPT_ID       <------The order by is generated.
   ;

After the SORTUNLD (NO)  is added, and note this would impact ALL users on ALL subsystems
that use this same hlq.CDBAPARM(MIGRATOR) member. No changes are required on the model.

.DATA
  FASTUNLOAD
   UNLDDN          SYSREC
   LIMIT           0
   OUTPUT-FORMAT   LOAD
   INPUT-FORMAT    TABLE
   VSAM-BUFFERS    80
   SORTNUM         2
   SORTDEVT        SYSDA
   ESTIMATED-ROWS  1
   SQL-ACCESS      EXTENSION
   TRUNCATE        NO
   SELECT DEPT_ID, DEPT_DESC, DEPT_MGR, DEPT_ADDRESS, DEPT_ADDRESS_TWO
   , DEPT_ADDRESS_THREE
   FROM authid.TBDEPT
   NEWOBID 20
   ;
.ENDDATA

 

This also impacts on the Snapshot Analysis(RECOVERY Analysis Report)  which is produced when using the Recovery          ===> Y analysis option.

Additional Information:

R18 Review Installation Related Changes  See section "Parmlib Member Changes, MIGRATOR Parmlib Member Changes (CA RC/Migrator™ for DB2 for z/OS)"

Sort Processing

Operational Considerations  See MIGRATOR Parmlib Options