RC/Migrator ORDER BY statement generation during UNLOAD
Article ID: 14161
Updated On:
Products
Issue/Introduction
RC/Migrator for Db2 for z/OS (RCM) always generates an UNLOAD with ORDER BY. For some migration purposes we do not 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.
Is there an option in RC/Migrator that would allow the control of the ORDER BY statement generation?
This should work in both the worklist and Snapshot worklist.
Resolution
There is a parm in hlq.CDBAPARM(MIGRATOR) called SORTUNLD which can turn ON or OFF the ORDER BY in the unload.
In the MIGRATOR member of hlq.CDBAPARM....add a SORTUNLD parm.
ORDER BY for UNLOAD (SORTUNLD(NO|YES))
Specify N to suppress unnecessary ORDER BY clauses in the UNLOAD control statements when unloading a table. Use of this option can prevent sort work outages and can reduce CPU consumption and elapsed time when sorted data is not needed. Set this option to N to avoid sort processing for large tables. When set to Y, the UNLOAD control statements include ORDER BY.
-------------------------------------------------------------------------
DSLIST hlq.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 col1, col2, col3
FROM authid.table
NEWOBID nn
ORDER BY col1 <------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 col1, col2, col3
FROM authid.table
NEWOBID nn
;
.ENDDATA
This also impacts the Snapshot Analysis(RECOVERY Analysis Report) which is produced when using the Recovery ===> Y analysis option.
Additional Information
Feedback
