Run premigration queries (DSNTIJPM)
Before you migrate to Db2 12, run premigration queries on the Db2 11 catalog.
Procedure
To run premigration queries:
- Copy the updated job DSNTIJPM from Db2 12 to Db2 11.
- Customize job DSNTIJPM as described in the job prolog.
- Run job DSNTIJPM.
Results
Job DSNTIJPM generates reports about conditions that require action before or during the migration process. Such conditions include obsolete object formats, missing required objects, packages that must be rebound before migration, and dependencies that are affected by the migration process. For detailed information about the contents of the reports for migration to Db2 12, see the report descriptions in job DSNTIJPM.
Premigration job DSNTIJPM for migration to Db2 12
The content of the DSNTIJPM premigration job is presented here for informational purposes only. Use the actual job that is supplied with the Db2 12 product code when you complete this task.
//*********************************************************************
//* NAME = DSNTIJPM
//*
//* DESCRIPTIVE NAME = DB2 INSTALL JOB
//*
//* Licensed Materials - Property of IBM
//* 5650-DB2
//* (C) COPYRIGHT 1982, 2016 IBM Corp. All Rights Reserved.
//*
//* STATUS = Version 12
//*
//* FUNCTION = RUN THIS JOB ON DB2 VERSION 11 PRIOR TO MIGRATION TO
//* DB2 VERSION 12. IT QUERIES THE DB2 CATALOG TO IDENTIFY
//* CONDITIONS THAT WILL NEED TO BE REMEDIED BEFORE YOU
//* ATTEMPT TO MIGRATE TO DB2 VERSION 12 OR THAT NEED TO BE
//* ATTENDED TO AFTER YOU HAVE MIGRATED TO DB2 VERSION 12.
//*
//* PSEUDOCODE =
//* DSNTJU04 STEP EXECUTE DSNJU004
//* DSNTGEN STEP COPY DSNTPMQ EXEC TO A TEMP PDS
//* DSNTPMQ STEP CALL DSNTPMQ TO GENERATE PREMIGRATION REPORTS
//*
//* NOTES = THE JCL REQUIRES DB2 REXX IN THE DB2 ENVIRONMENT YOU
//* ARE MIGRATING FROM.
//*
//* THE FOLLOWING MANUAL TAILORING IS REQUIRED:
//* 1. ADD A VALID JOB CARD.
//* 2. LOCATE AND CHANGE ALL OCCURRENCES OF THE FOLLOWING
//* STRINGS AS INDICATED:
//* A. '!BSDSNAME!' TO THE NAME OF THE BOOTSTRAP DATA
//* SET OF THE DB2 SUBSYSTEM YOU ARE PREPARING TO
//* MIGRATE. ENSURE THAT THE BSDS NAME SPECIFIED
//* IS NOT THE BSDS OF A SUBSYSTEM THAT HAS BEEN
//* QUIESCED. ALSO ENSURE THAT THE SUBSYSTEM'S
//* DSNMSTR ADDRESS SPACE ALLOCATES THE BSDS
//* FOR REUSE (DISP=SHR), NOT EXCLUSIVE USE
//* (DISP=OLD).
//* B. '!DSN!' TO THE NAME OF THE DB2 SUBSYSTEM YOU ARE
//* PREPARING TO MIGRATE.
//* C. 'DSN!!0' TO THE PREFIX OF THE DB2 TARGET
//* LIBRARIES FOR THE DB2 SUBSYSTEM YOU ARE
//* PREPARING TO MIGRATE.
//* D. '!SYSADM!' TO THE AUTHORIZATION ID TO BE USED
//* BY DB2 QUERIES IN THIS JOB.
//* 3. In job step DSNTJU04, the SYSPRINT DD allocates
//* a data set for the print log map output. If
//* necessary, increase the space allocated to the
//* SYSPRINT DD.
//*
//* CHANGE ACTIVITY =
//* 2013/10/18 Update for DB2 12 pl160_inst1 pl160 n17984
//* 2014/01/27 Add REPORT19 (extraneous text in PI09041
//* SYSIBM.SYSTRIGGERS TEXT column)
//* 2014/02/22 Convert REPORT04 (user-defined indexes PI11316 DM1800
//* on the DB2 catalog that reside on user-managed
//* storage) to report all user-defined indexes on
//* the DB2 catalog that are not in the DB2 catalog
//* space. Discontinue REPORT05 (user-defined
//* indexes on the DB2 catalog that reside on DB2-
//* managed storage).
//* 2014/10/03 Update REPORT08 to mention s2791_inst1 n17831 s2791
//* INVALIDONLY option of FREE PACKAGE
//* 2014/08/18 Single-phase migration s21938_inst1 s21938
//* 2014/10/29 Remove plan-oriented reports d174783 s21938
//* and reclaim unused report numbers 175140
//* 2014/08/26 Add REPORT14 (re:SYSPROC.DSNUTILU) s20128_inst1 s20128
//* 2014/11/20 Add REPORT15 and REPORT16 ss22919_inst1 ss22919
//* 2015/01/14 Adjust REPORT15 d175788 ss22919
//* 2015/04/04 Fix typos in prolog text d175972
//* 2015/04/27 Fix REPORT06 FETCH d177742
//* 2015/07/23 Remove REPORT14 (re:SYSPROC.DSNUTILU) d179171 DP1891
//* and rename REPORT15 to REPORT14 179294 s25173
//* and rename REPORT16 to REPORT15
//* 2015/09/19 Add report for obsolete RLSTs dp1887_inst dp1887
//* 2015/10/20 Add report for invalidated plans td180959
//* 2015/08/24 Add report for UNLOAD privilege s20166_inst2 s20166
//* 2015/11/16 Update EXPLAIN table query 181358
//* 2015/12/26 Note ADMIN_EXPLAIN_MAINT in report 3 177631
//* 2016/01/29 IFCID 403->404 in Report 18 182269
//* 2016/03/03 Add report for SQLCODE -905 00C900E3 pi55114
//* 2017/07/17 Remove HV4 HV5 HV6 from output of REPORT07 pi81016
//* 2017/11/10 Add REPORT20 (tables that have an OBID of 1) PI88793
//* 2017/11/10 Add REPORT21 (foreign key constraints that PI87444
//* have a Db2 catalog table as a parent)
//* 2017/12/15 Add REPORT22 (expression-based indexes PI91332
//* created in Db2 9)
//* 2018/01/24 Fix REPORT08 query PI91667
//* 2018/10/11 Add additional info in REPORT22 comment PH03421
//* 2019/02/04 Add REPORT23 (native SQL procedures and PH07660
//* compiled SQL scalar functions created in Db2 9
//* 2019/04/29 Add REPORT24 (active log data sets greater PH11514
//* than 4 GB in size)
//* 2019/09/05 Add instruction in prolog for user to PH12364
//* increase the space allocated to the
//* SYSPRINT DD in job step DSNTJU04 if
//* necessary
//* generateReport24:
//* - Remove trailing blanks from active log
//* data set name
//* - Do not abort if LISTCAT failed or
//* HI-A-RBA cannot be determined
//* 2020/03/19 Change REPORT08 informational text PH20086
//* 2023/04/21 Update the description of REPORT16 PH53833
//* 2023/08/07 Add RELBOUND to REPORT08 PH54787
//*
//*********************************************************************
//*
//JOBLIB DD DSN=DSN!!0.SDSNEXIT,DISP=SHR
// DD DSN=DSN!!0.SDSNLOAD,DISP=SHR
//*
//*
//DSNTJU04 EXEC PGM=DSNJU004
//GROUP DD DISP=SHR,DSN=!BSDSNAME!
//SYSPRINT DD DSN=&&TEMPPDS2(JU004PRT),
// DISP=(,PASS),
// UNIT=SYSDA,SPACE=(TRK,(25,5,5),RLSE)
//SYSIN DD *
MEMBER *
//*
//DSNTGEN EXEC PGM=IEBGENER,COND=(4,LT)
//SYSIN DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSUT2 DD DSN=&&TEMPPDS(DSNTPMQ),
// DISP=(,PASS),
// UNIT=SYSDA,SPACE=(TRK,(1,1,1),RLSE),
// DCB=(RECFM=FB,LRECL=80)
//SYSUT1 DD *
/** REXX ** DSNTPMQ ************************************************/
PARSE UPPER ARG ssid authid
ssid = STRIP(ssid)
authid = STRIP(authid)
high_rc = 0
CALL validateArgs ssid,
authid
ADDRESS TSO "SUBCOM DSNREXX" /* HOST CMD ENV AVAILABLE? */
IF rc THEN /* NO, LET'S MAKE ONE */
S_rc = RXSUBCOM('ADD','DSNREXX','DSNREXX') /* ADD HOST CMD ENV */
ADDRESS DSNREXX "CONNECT" ssid
IF SQLCODE \= 0 THEN CALL errsqlca 'CONNECT'
CALL setAuthId authid
CALL generateReport01
CALL generateReport02
CALL generateReport03
CALL generateReport04
CALL generateReport05
CALL generateReport06
CALL generateReport07
CALL generateReport08
CALL generateReport09
CALL generateReport10
CALL generateReport11
CALL generateReport12
CALL generateReport13
CALL generateReport14
CALL generateReport15
CALL generateReport16
CALL generateReport17
CALL generateReport18
CALL generateReport19
CALL generateReport20
CALL generateReport21
CALL generateReport22
CALL generateReport23
CALL generateReport24
ADDRESS DSNREXX "EXECSQL COMMIT"
ADDRESS DSNREXX "DISCONNECT"
EXIT Finis() /* end DSNTPMQ */
/*==================================================================*/
/* Begin subroutines */
/*==================================================================*/
validateArgs: PROCEDURE
/*****************************************************************/
/* Print a welcome message and then validate the parameters */
/*****************************************************************/
PARSE ARG ssid /* DB2 Subsystem ID */,
authid /* Authorization ID for work */
CALL IntMsg,
'DSNTPMQ entry:'
CALL IntMsg,
' Subsystem ID ............................................. ',
ssid
CALL IntMsg,
' Authorization ID ......................................... ',
authid
CALL IntMsg ''
digits = "0123456789"
dquote = '"'
letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
nationals = "@#$"
quote = "'"
underscore = "_"
/******************************************************************
* Validate the argument for parameter 1, ssid *
* Rules: 1-4 characters starting with A-Z or @, #, or $. *
* Remaining characters must be A-Z, 0-9, @, #, or $ *
******************************************************************/
IF (LENGTH(ssid) < 1 | LENGTH(ssid) > 4),
| (VERIFY(ssid, letters||nationals) = 1),
| (VERIFY(ssid, letters||digits||nationals) > 0) THEN
DO
CALL IntMsg,
'Error: The argument for parameter 1, SUBSYSTEM ',
'ID, must be 1 to 4 char-'
CALL IntMsg,
' acters and begin with A-Z, @, #, or $. ',
'The other characters must'
CALL IntMsg,
' be A-Z, 0-9, @, #, or $.'
CALL abort 54
END
/******************************************************************
* Validate the argument for parameter 2, authid *
* Rules: 1-8 characters starting with A-Z or @, #, or $. *
* Remaining characters must be A-Z, 0-9, @, #, or $ *
******************************************************************/
IF (LENGTH(authid) < 1 | LENGTH(authid) > 8),
| (VERIFY(authid, letters||nationals) = 1),
| (VERIFY(authid, letters||digits||nationals) > 0) THEN
DO
CALL IntMsg,
'Error: The argument for parameter 2, AUTHORIZA',
'TION ID, must be 1 to 8'
CALL IntMsg,
' characters and begin with A-Z, @, #, or $.',
' The other characters'
CALL IntMsg,
' must be A-Z, 0-9, @, #, or $.'
CALL abort 54
END
RETURN /* end validateArgs */
setAuthId: PROCEDURE
/*****************************************************************/
/* Set the current authorization id */
/*****************************************************************/
PARSE ARG authid /* Authorization id to use */
SQLSTMT = "SET CURRENT SQLID = '" || authid || "'"
ADDRESS DSNREXX EXECSQL SQLSTMT
IF sqlcode <> 0 THEN
CALL errsqlca "SET CURRENT SQLID"
RETURN /* end setAuthId */
generateReport01: PROCEDURE
/*****************************************************************/
/* Generates premigration report 1: */
/* Reports existence of the previous-release sample database */
/*****************************************************************/
"EXECIO 0 DISKW REPORT01 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT01 OPEN rc=' rc
TEMP.1 = "=== REPORT 1" COPIES('=',59)
TEMP.2 = "= Existence of the DB2 11 sample database ",
" ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The following lists the DB2 11",
"sample database, DSN8D11A. This ="
TEMP.5 = "= database is needed for verifying",
"migration to DB2 12. If DSN8D11A ="
TEMP.6 = "= is not listed below, you are",
"recommended to recreate it before ="
TEMP.7 = "= commencing migration to DB2 12. ",
"Run DB2 11 IVP job DSNTEJ1 to ="
TEMP.8 = "= recreate it. You should also run",
"one or more of the phase 2 and ="
TEMP.9 = "= phase 3 IVP jobs in DB2 11. See",
"the DB2 11 Installation Guide, the ="
TEMP.10 = "= chapter on Verifying with Sample",
"Applications for more information. ="
TEMP.11 = COPIES('=',72)
TEMP.12 = "NAME"
TEMP.13 = "----------"
'EXECIO' 13 'DISKW REPORT01 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT01 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT= "SELECT '""' || NAME || '""'",
"FROM SYSIBM.SYSDATABASE",
"WHERE NAME IN( 'DSN8D11A' )"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1 INTO :TEMP"
DO WHILE SQLCODE = 0
TEMP.1 = TEMP
'EXECIO' 1 'DISKW REPORT01 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT01 rc=' rc
address DSNREXX "EXECSQL FETCH C1 INTO :TEMP"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP.
'EXECIO 0 DISKW REPORT01 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT01 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 1 completed'
RETURN /* end generateReport01 */
generateReport02: PROCEDURE
/*****************************************************************/
/* Generates premigration report 2: */
/* Report simple table spaces */
/*****************************************************************/
"EXECIO 0 DISKW REPORT02 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT02 OPEN rc=' rc
TEMP.1 = "=== REPORT 2" COPIES('=',59)
TEMP.2 = "= Existence of simple table spaces ",
" ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= Each table space listed below is a simple",
"table space, meaning that ="
TEMP.5 = "= it is neither partitioned nor segmented. ",
"Although DB2 12 can use ="
TEMP.6 = "= existing simple table spaces and allows you",
"to alter data, update ="
TEMP.7 = "= data, or retrieve data from them, it does not",
"allow you to create ="
TEMP.8 = "= them. If you accidentally drop a simple table",
"space in 12, you ="
TEMP.9 = "= will be unable to recreate it. Therefore, before",
"migrating to 12, ="
TEMP.10 = "= convert each table space listed below to a preferred",
"type of table ="
TEMP.11 = "= space. ",
" ="
TEMP.12 = COPIES('=',72)
TEMP.13 = "DBNAME.TSNAME"
TEMP.14 = COPIES('-',49)
'EXECIO' 14 'DISKW REPORT02 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT02 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT STRIP(TS.DBNAME)",
"|| '.'",
"|| STRIP(TS.NAME)",
"FROM SYSIBM.SYSTABLESPACE TS",
"WHERE TS.PARTITIONS = 0",
"AND TS.SEGSIZE = 0",
"AND TS.TYPE <> 'O'",
"AND TS.DBNAME NOT IN ('DSNDB01', 'DSNDB06')",
"AND TS.DBNAME NOT IN",
"( SELECT DB.NAME",
"FROM SYSIBM.SYSDATABASE DB",
"WHERE DB.TYPE = 'W'",
")",
"AND TS.DBNAME NOT LIKE 'DSN8%'",
"ORDER BY TS.DBNAME, TS.NAME"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1"
DO WHILE SQLCODE = 0
TEMP.1 = HV1
'EXECIO' 1 'DISKW REPORT02 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT02 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1
'EXECIO 0 DISKW REPORT02 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT02 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 2 completed'
RETURN /* end GenerateReport02 */
generateReport03: PROCEDURE
/*****************************************************************/
/* Generates premigration report 3: */
/* Reports explain tables that are not in current-release fmt */
/*****************************************************************/
"EXECIO 0 DISKW Report03 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT03 OPEN rc=' rc
TEMP.1 = "=== REPORT 3" COPIES('=',59)
TEMP.2 = "= EXPLAIN tables that need to be updated to",
"DB2 11 format ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The EXPLAIN tables listed below are not in",
"current-release format, ="
TEMP.5 = "= meaning that one or more columns available",
"in the current release ="
TEMP.6 = "= (DB2 11) is missing or has a different length",
"than found. Use of ="
TEMP.7 = "= such EXPLAIN tables is deprecated and support",
"for them may be ="
TEMP.8 = "= removed in a future release of DB2. Use DB2",
"sample job DSNTIJXA or ="
TEMP.9 = "= DB2-supplied stored procedure",
"SYSPROC.ADMIN_EXPLAIN_MAINT to alter ="
TEMP.10 = "= all tables in a specified schema to current",
"release format. ="
TEMP.11 = COPIES('=',72)
TEMP.12 = "CREATOR.NAME" COPIES(' ',244)
TEMP.13 = COPIES('-',257)
'EXECIO' 13 'DISKW REPORT03 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT03 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
/*****************************************************************/
/* The following CTE consists of 2 parts separated by UNIONs: */
/* Part 1: Finds EXPLAIN tables that have missing columns */
/* Part 2: Finds EXPLAIN tables that have unexpected column lens */
/*****************************************************************/
SQLSTMT =,
"WITH OLD_FORMAT_EXPLAIN_TABLES (FULL_NAME) AS",
"( SELECT STRIP(TBCREATOR) || '.' || STRIP(TBNAME)",
"FROM SYSIBM.SYSCOLUMNS",
"WHERE TBNAME IN( 'DSN_COLDIST_TABLE'",
", 'DSN_DETCOST_TABLE'",
", 'DSN_FILTER_TABLE'",
", 'DSN_FUNCTION_TABLE'",
", 'DSN_KEYTGTDIST_TABLE'",
", 'DSN_PGRANGE_TABLE'",
", 'DSN_PGROUP_TABLE'",
", 'DSN_PREDICAT_TABLE'",
", 'DSN_PREDICATE_SELECTIVITY'",
", 'DSN_PROFILE_HISTORY'",
", 'DSN_PROFILE_TABLE'",
", 'DSN_PTASK_TABLE'",
", 'DSN_QUERY_TABLE'",
", 'DSN_QUERYINFO_TABLE'",
", 'DSN_SORT_TABLE'",
", 'DSN_SORTKEY_TABLE'",
", 'DSN_STAT_FEEDBACK'",
", 'DSN_STATEMENT_CACHE_TABLE'",
", 'DSN_STATEMNT_TABLE'",
", 'DSN_STRUCT_TABLE'",
", 'DSN_VIEWREF_TABLE'",
", 'DSN_VIRTUAL_INDEXES'",
", 'DSN_VIRTUAL_KEYTARGETS'",
", 'PLAN_TABLE'",
")",
"GROUP BY TBCREATOR, TBNAME",
"HAVING (TBNAME = 'DSN_COLDIST_TABLE'",
"AND MAX(COLNO) < 21)",
"OR (TBNAME = 'DSN_DETCOST_TABLE'",
"AND MAX(COLNO) < 136)",
"OR (TBNAME = 'DSN_FILTER_TABLE'",
"AND MAX(COLNO) < 18)",
"OR (TBNAME = 'DSN_FUNCTION_TABLE'",
"AND MAX(COLNO) < 20)",
"OR (TBNAME = 'DSN_KEYTGTDIST_TABLE'",
"AND MAX(COLNO) < 21)",
"OR (TBNAME = 'DSN_PGRANGE_TABLE'",
"AND MAX(COLNO) < 15)",
"OR (TBNAME = 'DSN_PGROUP_TABLE'",
"AND MAX(COLNO) < 42)",
"OR (TBNAME = 'DSN_PREDICAT_TABLE'",
"AND MAX(COLNO) < 42)",
"OR (TBNAME = 'DSN_PREDICATE_SELECTIVITY'",
"AND MAX(COLNO) < 16)",
"OR (TBNAME = 'DSN_PROFILE_HISTORY'",
"AND MAX(COLNO) < 15)",
"OR (TBNAME = 'DSN_PROFILE_TABLE'",
"AND MAX(COLNO) < 15)",
"OR (TBNAME = 'DSN_PTASK_TABLE'",
"AND MAX(COLNO) < 25)",
"OR (TBNAME = 'DSN_QUERY_TABLE'",
"AND MAX(COLNO) < 16)",
"OR (TBNAME = 'DSN_QUERYINFO_TABLE'",
"AND MAX(COLNO) < 18)",
"OR (TBNAME = 'DSN_SORT_TABLE'",
"AND MAX(COLNO) < 16)",
"OR (TBNAME = 'DSN_SORTKEY_TABLE'",
"AND MAX(COLNO) < 21)",
"OR (TBNAME = 'DSN_STATEMENT_CACHE_TABLE'",
"AND MAX(COLNO) < 79)",
"OR (TBNAME = 'DSN_STATEMNT_TABLE'",
"AND MAX(COLNO) < 16)",
"OR (TBNAME = 'DSN_STRUCT_TABLE'",
"AND MAX(COLNO) < 20)",
"OR (TBNAME = 'DSN_VIEWREF_TABLE'",
"AND MAX(COLNO) < 13)",
"OR (TBNAME = 'DSN_VIRTUAL_INDEXES'",
"AND MAX(COLNO) < 150)",
"OR (TBNAME = 'DSN_VIRTUAL_KEYTARGETS'",
"AND MAX(COLNO) < 16)",
"OR (TBNAME = 'PLAN_TABLE'",
"AND MAX(COLNO) < 66)",
"UNION",
"SELECT STRIP(TBCREATOR) || '.' || STRIP(TBNAME)",
"FROM SYSIBM.SYSCOLUMNS",
"WHERE TBNAME IN( 'DSN_DETCOST_TABLE'",
", 'DSN_FILTER_TABLE'",
", 'DSN_FUNCTION_TABLE'",
", 'DSN_PGRANGE_TABLE'",
", 'DSN_PGROUP_TABLE'",
", 'DSN_PREDICAT_TABLE'",
", 'DSN_PROFILE_HISTORY'",
", 'DSN_PROFILE_TABLE'",
", 'DSN_PTASK_TABLE'",
", 'DSN_QUERY_TABLE'",
", 'DSN_SORT_TABLE'",
", 'DSN_SORTKEY_TABLE'",
", 'DSN_STATEMENT_CACHE_TABLE'",
", 'DSN_STATEMENT_RUNTIME_INFO'",
", 'DSN_STATEMNT_TABLE'",
", 'DSN_STRUCT_TABLE'",
", 'DSN_VIEWREF_TABLE'",
", 'DSN_VIRTUAL_INDEXES'",
", 'PLAN_TABLE'",
")",
"AND ( (NAME = 'ACCESSCREATOR' AND LENGTH <> 128)",
"OR (NAME = 'ACCESSNAME' AND LENGTH <> 128)",
"OR (NAME = 'APPLNAME' AND LENGTH <> 24)",
"OR (NAME = 'COLLID' AND LENGTH <> 128)",
"OR (NAME = 'CORRELATION_NAME' AND LENGTH <> 128)",
"OR (NAME = 'CREATOR' AND LENGTH <> 128)",
"OR (NAME = 'FUNCTION_NAME' AND LENGTH <> 128)",
"OR (NAME = 'FUNCTION_TEXT' AND LENGTH <> 1500)",
"OR (NAME = 'GROUP_MEMBER' AND LENGTH <> 24)",
"OR (NAME = 'HINTUSED' AND LENGTH <> 128)",
"OR (NAME = 'OPTHINT' AND LENGTH <> 128)",
"OR (NAME = 'PATH' AND LENGTH <> 2048)",
"OR (NAME = 'PLANNAME' AND LENGTH <> 24)",
"OR (NAME = 'PROGNAME' AND LENGTH <> 128)",
"OR (NAME = 'REMARKS' AND LENGTH <> 762)",
"OR (NAME = 'SCHEMA_NAME' AND LENGTH <> 128)",
"OR (NAME = 'SPEC_FUNC_NAME' AND LENGTH <> 128)",
"OR (NAME = 'TNAME' AND LENGTH <> 128)",
"OR (NAME = 'VERSION' AND LENGTH <> 122)",
"OR (NAME = 'VIEW_CREATOR' AND LENGTH <> 128)",
"OR (NAME = 'VIEW_NAME' AND LENGTH <> 128)",
")",
")",
"SELECT DISTINCT(FULL_NAME)",
"FROM OLD_FORMAT_EXPLAIN_TABLES",
"ORDER BY FULL_NAME"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1 INTO :HV1"
DO WHILE SQLCODE = 0
TEMP.1 = HV1
'EXECIO' 1 'DISKW REPORT03 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT03 rc=' rc
address DSNREXX "EXECSQL FETCH C1 INTO :HV1"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1
'EXECIO 0 DISKW REPORT03 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT03 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 3 completed'
RETURN /* end generateReport03 */
generateReport04: PROCEDURE
/*****************************************************************/
/* Generates premigration report 4: */
/* Reports user-defined indexes on the DB2 catalog that */
/* do not reside in the DB2 catalog space. DM1800*/
/*****************************************************************/
"EXECIO 0 DISKW REPORT04 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT04 OPEN rc=' rc
TEMP.1 = "=== REPORT 4" COPIES('=',59)
TEMP.2 = "= User indexes on the DB2 catalog that",
"are not in the DB2 catalog space="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The indexes listed below are user-defined",
"indexes on the DB2 catalog ="
TEMP.5 = "= that do not reside in the data space used",
"by the DB2 catalog and ="
TEMP.6 = "= directory. IBM recommends that, before you",
"begin migration to the ="
TEMP.7 = "= next release of DB2, you drop and recreate",
"these indexes to move ="
TEMP.8 = "= them into the DB2 catalog space.",
" ="
TEMP.9 = COPIES('=',72)
TEMP.10 = "CREATOR.NAME"
TEMP.11 = COPIES('-',257)
'EXECIO' 11 'DISKW REPORT04 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT04 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT RTRIM(IX.CREATOR)",
", RTRIM(IX.NAME)",
"FROM SYSIBM.SYSINDEXES IX",
", SYSIBM.SYSINDEXPART IXP",
"WHERE IX.CREATOR = IXP.IXCREATOR",
"AND IX.NAME = IXP.IXNAME",
"AND IX.DBID = 6",
"AND ( IX.OBID BETWEEN 400 AND 599",
"OR IX.OBID BETWEEN 900 AND 1699",
")",
"AND IXP.STORNAME <> '00000001'",
"ORDER BY IX.CREATOR, IX.NAME"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2"
DO WHILE SQLCODE = 0
TEMP.1 = HV1 || "." || HV2
'EXECIO' 1 'DISKW REPORT04 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT04 rc=' rc
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV2
'EXECIO 0 DISKW REPORT04 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT04 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 4 completed'
RETURN /* end generateReport04 */
generateReport05: PROCEDURE
/*****************************************************************/
/* Generates premigration report 5: */
/* Reports package copies that are not support in DB2 12 */
/*****************************************************************/
"EXECIO 0 DISKW REPORT05 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT05 OPEN rc=' rc
TEMP.1 = "=== REPORT 5" COPIES('=',59)
TEMP.2 = "= Package copies that are not supported after",
"migration ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The following is a list of package copies",
"that were last bound prior ="
TEMP.5 = "= to DB2 10. DB2 12 will not execute package",
"copies bound prior to DB2 ="
TEMP.6 = "= 10. If ABIND is YES or COEXIST, upon initial",
"execution of the CURRENT="
TEMP.7 = "= package copy, DB2 12 will automatically rebind",
"the package. If ABIND ="
TEMP.8 = "= is set to 'NO', DB2 12 will return SQLCODE -908",
"(SQLSTATE 23510) for ="
TEMP.9 = "= all attempts to use any such package until it is",
"explicitly rebound. ="
TEMP.10 = "= To avoid the autobinds or outages from SQLCODE",
"-908, you should ="
TEMP.11 = "= rebind CURRENT package copies that will be invalid",
"before migration ="
TEMP.12 = "= to DB2 12. DB2 12 will block a REBIND SWITCH to",
"a package copy that ="
TEMP.13 = "= is invalid or was bound before DB2 10. After",
"migration and before ="
TEMP.14 = "= initial REBIND on DB2 12, you can remove inactive,",
"invalid package ="
TEMP.15 = "= copies using FREE PACKAGE PLANMGMTSCOPE(INACTIVE)",
"INVALIDONLY(YES). ="
TEMP.16 = "= By freeing inactive, invalid package copies before",
"the initial ="
TEMP.17 = "= REBIND on DB2 12, the ORIGINAL slot can be populated",
"with a copy ="
TEMP.18 = "= that is valid and more recent. ",
" ="
TEMP.19 = COPIES('=',72)
TEMP.20 = "Last Used Copy Collection-ID.Package-name.(Version)"
TEMP.21 = "---------- --------" COPIES('-',382)
'EXECIO' 21 'DISKW REPORT05 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT05 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
" SELECT LASTUSED ",
" , 'CURRENT ' AS PKGCOPY ",
" , RTRIM(COLLID) || '.' || ",
" RTRIM(NAME) || '.(' || ",
" RTRIM(VERSION) || ')' ",
" AS RESULT ",
" FROM SYSIBM.SYSPACKAGE ",
" WHERE RELBOUND <= 'M' ",
" UNION ALL ",
" SELECT LASTUSED ",
" , CASE WHEN COPYID = 1 THEN 'PREVIOUS' ",
" WHEN COPYID = 2 THEN 'ORIGINAL' ",
" ELSE 'ERROR' ",
" END AS PKGCOPY ",
" , RTRIM(COLLID) || '.' || ",
" RTRIM(NAME) || '.(' || ",
" RTRIM(VERSION) || ')' ",
" AS RESULT ",
" FROM SYSIBM.SYSPACKCOPY ",
" WHERE RELBOUND <= 'M' ",
"ORDER BY RESULT, PKGCOPY ",
" FOR FETCH ONLY WITH UR "
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2, :HV3"
DO WHILE SQLCODE = 0
TEMP.1 = HV1 HV2 HV3
'EXECIO' 1 'DISKW REPORT05 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT05 rc=' rc
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2, :HV3"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV2 HV3
'EXECIO 0 DISKW REPORT05 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT05 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 5 completed'
RETURN /* end generateReport05 */
generateReport06: PROCEDURE
/*****************************************************************/
/* Generates premigration report 6: */
/* Reports packages that are autobind candidates in DB2 12 */
/*****************************************************************/
"EXECIO 0 DISKW REPORT06 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT06 OPEN rc=' rc
TEMP.1 = "=== REPORT 6" COPIES('=',59)
TEMP.2 = "= Packages that are not supported",
"after migration to DB2 12 ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The following is a list of packages",
"that were last bound prior to ="
TEMP.5 = "= DB2 10. Such packages are not",
"supported after migration to DB2 12 ="
TEMP.6 = "= and will be automatically rebound",
"the first time used if the ABIND ="
TEMP.7 = "= subsystem parameter is set to 'YES'",
"or 'COEXIST'. If ABIND is set ="
TEMP.8 = "= to 'NO', DB2 12 will return SQLCODE",
"-908 (SQLSTATE 23510) for all ="
TEMP.9 = "= attempts to use any such package",
"until it is explicitly rebound. ="
TEMP.10 = "= To avoid the autobinds or outages",
"from SQLCODE -908, you should ="
TEMP.11 = "= rebind these packages before you",
"begin migration to DB2 12. ="
TEMP.12 = COPIES('=',72)
TEMP.13 = "Valid Operative LastUsed PLANMGMT APREUSE",
"Collection-ID.Package-name.Hex-contoken"
TEMP.14 = "----- --------- ---------- -------- -------",
COPIES('-',278)
'EXECIO' 14 'DISKW REPORT06 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT06 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
" SELECT RPAD(VALID, 5, ' ') ",
" , RPAD(OPERATIVE, 9, ' ') ",
" , LASTUSED ",
" , CASE PLANMGMT ",
" WHEN 'B' THEN 'BASIC ' ",
" WHEN 'E' THEN 'EXTENDED' ",
" ELSE 'OFF ' ",
" END ",
" , CASE APREUSE ",
" WHEN 'E' THEN 'ERROR ' ",
" WHEN 'W' THEN 'WARN ' ",
" ELSE 'NONE ' ",
" END ",
" , RTRIM( '""' || RTRIM(COLLID) || '""' ) ",
" || '.'",
" || RTRIM( '""' || RTRIM(NAME) || '""' ) ",
" || '.'",
" || HEX(CONTOKEN) ",
" AS RESULT",
" FROM SYSIBM.SYSPACKAGE ",
" WHERE RELBOUND IN( ' ', 'K', 'L', 'M' ) ",
"ORDER BY RESULT "
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2, :HV3, :HV4,",
":HV5, :HV6"
DO WHILE SQLCODE = 0
TEMP.1 = HV1 HV2 HV3 HV4 HV5 HV6
'EXECIO' 1 'DISKW REPORT06 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT06 rc=' rc
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2, :HV3, :HV4,",
":HV5, :HV6"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV2 HV3 HV4 HV5 HV6
'EXECIO 0 DISKW REPORT06 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT06 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 6 completed'
RETURN /* end generateReport06 */
generateReport07: PROCEDURE
/*****************************************************************/
/* Generates premigration report 7: */
/* Reports plans that are autobind candidates in DB2 12 */
/*****************************************************************/
"EXECIO 0 DISKW REPORT07 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT07 OPEN rc=' rc
TEMP.1 = "=== REPORT 7" COPIES('=',59)
TEMP.2 = "= Plans that are not supported",
"after migration to DB2 12 ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The following is a list of plans",
"that were last bound prior to ="
TEMP.5 = "= DB2 10. Such plans are not",
"supported after migration to DB2 12 ="
TEMP.6 = "= and will be automatically rebound",
"the first time used if the ABIND ="
TEMP.7 = "= subsystem parameter is set to 'YES'",
"or 'COEXIST'. If ABIND is set ="
TEMP.8 = "= to 'NO', DB2 12 will return SQLCODE",
"-908 (SQLSTATE 23510) for all ="
TEMP.9 = "= attempts to use any such plan",
"until it is explicitly rebound. ="
TEMP.10 = "= To avoid the autobinds or outages",
"from SQLCODE -908, you should ="
TEMP.11 = "= rebind these plans before you",
"begin migration to DB2 12. ="
TEMP.12 = COPIES('=',72)
TEMP.13 = "Valid Operative Plan-Name/Creator"
TEMP.14 = "----- ---------" COPIES('-',261)
'EXECIO' 14 'DISKW REPORT07 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT07 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
" SELECT RPAD(VALID, 5, ' ') ",
" , RPAD(OPERATIVE, 9, ' ') ",
" , RTRIM( '""' || RTRIM(NAME) || '""' )",
" || '/'",
" || RTRIM( '""' || RTRIM(CREATOR) || '""' )",
" AS RESULT",
" FROM SYSIBM.SYSPLAN ",
" WHERE RELBOUND IN( ' ', 'K', 'L', 'M' ) ",
"ORDER BY RESULT "
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2, :HV3"
DO WHILE SQLCODE = 0
TEMP.1 = HV1 HV2 HV3
'EXECIO' 1 'DISKW REPORT07 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT06 rc=' rc
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2, :HV3"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV2 HV3
'EXECIO 0 DISKW REPORT07 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT07 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 7 completed'
RETURN /* end generateReport07 */
generateReport08: PROCEDURE
/*****************************************************************/
/* Generates premigration report 8: */
/* Report package dependencies on Db2 catalog and directory */
/* objects affected by migration to Db2 12 */
/*****************************************************************/
"EXECIO 0 DISKW REPORT08 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT08 OPEN rc=' rc
TEMP.1 = "=== REPORT 8" COPIES('=',59)
TEMP.2 = "= Packages that will be invalidated",
"during migration to Db2 12 ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The packages listed below will be",
"invalidated by job DSNTIJTC during ="
TEMP.5 = "= migration to Db2 12 because they",
"contain references (as indicated) ="
TEMP.6 = "= to one or more of the Db2 catalog",
"and directory objects that are ="
TEMP.7 = "= affected by migration processing.",
" ="
TEMP.8 = "= ",
" ="
TEMP.9 = "= Db2 will automatically rebind the",
"identified (invalidated) packages ="
TEMP.10 = "= on next use provided the ABIND",
"subsystem parameter is set to ="
TEMP.11 = "= 'COEXIST' or 'YES'. If ABIND is set",
"to 'NO', Db2 will return ="
TEMP.12 = "= SQLCODE -908 (SQLSTATE 23510)",
"for each attempt to use the package ="
TEMP.13 = "= until it is explicitly rebound. ",
" ="
TEMP.14 = "= ",
" ="
TEMP.15 = "= Recommendation: Explicitly REBIND the",
"identified (invalidated) ="
TEMP.16 = "= packages after migration",
"to Db2 12 to avoid ="
TEMP.17 = "= automatic rebind",
"processing. ="
TEMP.18 = COPIES('=',72)
TEMP.19 = "Reference ",
"Collection-ID.Package-name.Hex-contoken"
TEMP.20 = "------------------" COPIES('-',265)
'EXECIO' 20 'DISKW REPORT08 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT08 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT DISTINCT SUBSTR(STRIP(BNAME) || ' ', 1, 18)",
", STRIP(DCOLLID) || '.' || STRIP(DNAME) || '.' ||",
"HEX(DCONTOKEN)",
"FROM SYSIBM.SYSPACKDEP,SYSIBM.SYSPACKAGE",
"WHERE ( BTYPE = 'I'",
"AND BQUALIFIER = 'SYSIBM'",
"AND BNAME IN ( 'DSNOTX01', 'DSNATX02' )",
"AND DNAME = NAME",
"AND DCONTOKEN = CONTOKEN",
"AND RELBOUND < 'Q'",
")",
"OR ( BTYPE = 'T'",
"AND BQUALIFIER = 'SYSIBM'",
"AND BNAME IN( 'SYSCONTROLS', 'SYSENVIRONMENT'",
", 'SYSINDEXES', 'SYSKEYS'",
", 'SYSPACKAGE', 'SYSPACKCOPY'",
", 'SYSROUTINES', 'SYSTABLES'",
", 'SYSTRIGGERS'",
")",
"AND DNAME = NAME",
"AND DCONTOKEN = CONTOKEN",
"AND RELBOUND < 'Q'",
")",
"ORDER BY 1, 2"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2"
DO WHILE SQLCODE = 0
TEMP.1 = HV1 || ' ' || HV2
'EXECIO' 1 'DISKW REPORT08 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT08 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1
'EXECIO 0 DISKW REPORT08 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT08 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 8 completed'
RETURN /* end generateReport08 */
generateReport09: PROCEDURE
/*****************************************************************/
/* Generates premigration report 9: */
/* Report problem with catalog table space version numbers. */
/*****************************************************************/
"EXECIO 0 DISKW REPORT09 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT09 OPEN rc=' rc
TEMP.1 = "=== REPORT 9" COPIES('=',59)
TEMP.2 = "= DB2 table spaces with unexpected versioning",
" ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= Following is a list of DB2 catalog",
"table spaces for which the oldest ="
TEMP.5 = "= version is greater than the current",
"version. Such table spaces may ="
TEMP.6 = "= cause migration to DB2 12 to fail",
"during execution of job DSNTIJTC ="
TEMP.7 = "= (catalog tailoring). Before beginning",
"your migration use MODIFY ="
TEMP.8 = "= RECOVERY followed by REORG to correct",
"the versioning of all these ="
TEMP.9 = "= table spaces. See the Utility Guide for",
"information about these ="
TEMP.10 = "= utilities. ",
" ="
TEMP.11 = COPIES('=',72)
TEMP.12 = "CREATOR.NAME.OLDEST_VERSION.CURRENT_VERSION"
TEMP.13 = COPIES('-',72)
'EXECIO' 13 'DISKW REPORT09 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT09 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
" SELECT STRIP(CREATOR),",
" SUBSTR(NAME, 1, 8),",
" OLDEST_VERSION,",
" CURRENT_VERSION",
" FROM SYSIBM.SYSTABLESPACE",
" WHERE DBID = 6",
" AND (CURRENT_VERSION < OLDEST_VERSION)"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4"
DO WHILE SQLCODE = 0
TEMP.1 = HV1 || '.' || HV2 || '.' || HV3 || '.' || HV4
'EXECIO' 1 'DISKW REPORT09 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT09 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1
'EXECIO 0 DISKW REPORT09 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT09 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 9 completed'
RETURN /* end generateReport09 */
generateReport10: PROCEDURE
/*****************************************************************/
/* Generates premigration report 10: */
/* Report inconsistent version numbers in the DB2 catalog */
/*****************************************************************/
"EXECIO 0 DISKW REPORT10 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT10 OPEN rc=' rc
TEMP.1 = "=== REPORT 10" COPIES('=',58)
TEMP.2 = "= DB2 tables with unexpected versioning ",
" ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= Below is a list of tables which have",
"inconsistent version numbers in ="
TEMP.5 = "= the DB2 catalog. The inconsistencies",
"do not affect normal DB2 ="
TEMP.6 = "= operations but may interfere with",
"migration processing. Therefore, ="
TEMP.7 = "= before beginning migration to DB2",
"12, contact IBM Service for ="
TEMP.8 = "= assistance to get them resolved.",
" ="
TEMP.9 = COPIES('=',72)
'EXECIO' 9 'DISKW REPORT10 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT10 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT TB.CREATOR",
", TB.NAME",
", TB.DBNAME",
", TB.TSNAME",
", TB.DBID",
", TB.OBID",
", TB.VERSION",
", TS.OBID",
", TS.PSID",
", TS.OLDEST_VERSION",
", TS.CURRENT_VERSION",
"FROM SYSIBM.SYSTABLES TB",
", SYSIBM.SYSTABLESPACE TS",
"WHERE TB.DBNAME = TS.DBNAME",
"AND TB.TSNAME = TS.NAME",
"AND TB.TYPE = 'T'",
"AND TS.OLDEST_VERSION = 0",
"AND TS.CURRENT_VERSION = 0",
"AND TB.VERSION <> 0"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4,",
":HV5, :HV6, :HV7, :HV8,",
":HV9, :HV10, :HV11"
DO WHILE SQLCODE = 0
TEMP.1 = COPIES('-',148)
TEMP.2 = "TB.CREATOR :" HV1
TEMP.3 = "TB.NAME :" HV2
TEMP.4 = "TB.DBNAME :" HV3
TEMP.5 = "TB.TSNAME :" HV4
TEMP.6 = "TB.DBID :" HV5
TEMP.7 = "TB.OBID :" HV6
TEMP.8 = "TB.VERSION :" HV7
TEMP.9 = "TS.OBID :" HV8
TEMP.10 = "TS.PSID :" HV9
TEMP.11 = "TS.OLDEST_VERSION :" HV10
TEMP.12 = "TS.CURRENT_VERSION:" HV11
'EXECIO' 12 'DISKW REPORT10 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT10 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4,",
":HV5, :HV6, :HV7, :HV8,",
":HV9, :HV10, :HV11"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1
'EXECIO 0 DISKW REPORT10 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT10 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 10 completed'
RETURN /* end generateReport10 */
generateReport11: PROCEDURE
/*****************************************************************/
/* Generates premigration report 11: */
/* Report DB2 system page discrepancies for SYSTABLES */
/*****************************************************************/
"EXECIO 0 DISKW REPORT11 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT11 OPEN rc=' rc
TEMP.1 = "=== REPORT 11" COPIES('=',58)
TEMP.2 = "= DB2 system page discrepancies for",
"SYSTABLES ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= Queries on the following table",
"spaces may fail with SQLCODE -904, ="
TEMP.5 = "= reason code 00C900E3, because of",
"internal difference in data that ="
TEMP.6 = "= is stored for the SYSIBM.SYSTABLES",
"table in the DB2 system pages. ="
TEMP.7 = "= If any rows are listed below then,",
"before beginning migration to ="
TEMP.8 = "= DB2 12, take the following actions:",
" ="
TEMP.9 = "= 1- REORG the DSNDB06.SYSALTER",
"catalog table space ="
TEMP.10 = "= 2- Run MODIFY RECOVERY for",
"DSNDB06.SYSALTER as follows: ="
TEMP.11 = "= MODIFY RECOVERY TABLESPACE",
"DSNDB06.SYSALTER DELETE AGE(*) ="
TEMP.12 = "= 3- Take an image copy of the",
"DSNDB06.SYSALTER table space ="
TEMP.13 = "= 4- Run REPAIR CATALOG TABLESPACE",
"DSNDB06.SYSALTER TEST and ="
TEMP.14 = "= ensure that the retulting",
"return code is zero. ="
TEMP.15 = COPIES('=',72)
TEMP.16 = "dbname.tsname Last REORG or LOAD REPLACE",
" Oldest Version Last ALTER DATABASE"
TEMP.17 = "----------------- --------------------------",
" -------------- --------------------------"
'EXECIO' 17 'DISKW REPORT11 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT11 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT STRIP(P.DBNAME)",
", STRIP(P.TSNAME)",
", P.REORG_LR_TS",
", P.OLDEST_VERSION",
", S.ALTEREDTS",
"FROM SYSIBM.SYSTABLEPART P",
", SYSIBM.SYSTABLES S",
"WHERE S.DBNAME = P.DBNAME",
"AND S.TSNAME = P.TSNAME",
"AND S.ALTEREDTS > P.REORG_LR_TS",
"AND S.DBNAME = 'DSNDB06'",
"AND S.TSNAME = 'SYSALTER'",
"AND P.OLDEST_VERSION = 0"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4, :HV5"
DO WHILE SQLCODE = 0
TEMP.1 = SUBSTR( (HV1 || '.' || HV2), 1, 17 ) || ' ',
|| HV3 || ' ',
|| SUBSTR( HV4, 1, 14 ) || ' ',
|| HV5
'EXECIO' 1 'DISKW REPORT11 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT11 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4, :HV5"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV2 HV3 HV4 HV5
'EXECIO 0 DISKW REPORT11 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT11 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 11 completed'
RETURN /* end generateReport11 */
generateReport12: PROCEDURE
/*****************************************************************/
/* Generates premigration report 12: */
/* Report orphaned rows in SYSCOPY and SYSOBDS */
/*****************************************************************/
"EXECIO 0 DISKW REPORT12 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT12 OPEN rc=' rc
TEMP.1 = "=== REPORT 12" COPIES('=',58)
TEMP.2 = "= Orphaned rows in DB2 catalog tables SYSIBM.SYSCOPY",
"& SYSIBM.SYSOBDS ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= Listed below are the row IDs of",
"orphaned rows in DB2 catalog tables ="
TEMP.5 = "= SYSIBM.SYSCOPY and SYSIBM.SYSOBDS. ",
"These rows were left during ="
TEMP.6 = "= DB2 10 enabling new-function mode",
"processing by a problem that was ="
TEMP.7 = "= subsequently fixed by APAR PM41956",
"/ PTF UK70176. The orphaned rows ="
TEMP.8 = "= do not affect normal DB2 operations",
"but may interfere with migration ="
TEMP.9 = "= processing. Therefore, before",
"beginning migration to DB2 12, you ="
TEMP.10 = "= need to remove each entry listed below",
"by running the following ="
TEMP.11 = "= with <rid> replaced by the indicated",
"ROWID value: ="
TEMP.12 = "= * for the SYSCOPY rows returned: ",
" ="
TEMP.13 = "= REPAIR OBJECT ",
" ="
TEMP.14 = "= LOCATE TABLESPACE DSNDB06.SYSCOPY",
"RID X'<rid>' DELETE ="
TEMP.15 = "= * for the SYSOBDS rows returned: ",
" ="
TEMP.16 = "= REPAIR OBJECT ",
" ="
TEMP.17 = "= LOCATE TABLESPACE DSNDB06.SYSALTER",
"RID X'<rid>' DELETE ="
TEMP.18 = COPIES('=',72)
TEMP.19 = "TABLE ROWID"
TEMP.20 = "------- ----------"
'EXECIO' 20 'DISKW REPORT12 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT12 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
" SELECT 'SYSCOPY', RIGHT(HEX(RID(C)), 10)",
" FROM SYSIBM.SYSCOPY C",
" WHERE C.TSNAME = 'DSNVTH01'",
" AND C.DBNAME = 'DSNDB06'",
" AND NOT EXISTS(SELECT *",
" FROM SYSIBM.SYSINDEXES I",
" WHERE I.CREATOR = 'SYSIBM'",
" AND I.NAME = 'DSNVTH01'",
" )",
" UNION",
" SELECT 'SYSOBDS', RIGHT(HEX(RID(O)), 10)",
" FROM SYSIBM.SYSOBDS O",
" WHERE O.DBID = 6 AND O.OBDTYPE = 'R'",
" AND NOT EXISTS (SELECT *",
" FROM SYSIBM.SYSTABLESPACE T",
" WHERE T.DBID = O.DBID",
" AND T.PSID = O.PSID",
" )",
"ORDER BY 1, 2"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2"
DO WHILE SQLCODE = 0
TEMP.1 = HV1 || ' ' || HV2
'EXECIO' 1 'DISKW REPORT12 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT12 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1
'EXECIO 0 DISKW REPORT12 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT12 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 12 completed'
RETURN /* end generateReport12 */
generateReport13: PROCEDURE
/*****************************************************************/
/* Generates premigration report 13: */
/* Report orphaned rows in SYSTABSTATS */
/*****************************************************************/
"EXECIO 0 DISKW REPORT13 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT13 OPEN rc=' rc
TEMP.1 = "=== REPORT 13" COPIES('=',58)
TEMP.2 = "= Orphaned rows in DB2 catalog table",
"SYSIBM.SYSTABSTATS ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= Listed below are orphaned rows in",
"catalog table SYSIBM.SYSTABSTATS. ="
TEMP.5 = "= These rows do not affect normal",
"DB2 operations but may interfere ="
TEMP.6 = "= with migration processing. Therefore,",
"after reviewing these rows, ="
TEMP.7 = "= delete them before beginning",
"migration to DB2 12. For example: ="
TEMP.8 = "= DELETE FROM SYSIBM.SYSTABSTATS A",
" ="
TEMP.9 = "= WHERE NOT EXISTS ( SELECT 1 FROM",
"SYSIBM.SYSTABLEPART B ="
TEMP.10 = "= WHERE",
"A.DBNAME = B.DBNAME ="
TEMP.11 = "= AND",
"A.TSNAME = B.TSNAME ="
TEMP.12 = "= AND",
"A.PARTITION = B.PARTITION ) ="
TEMP.13 = COPIES('=',72)
TEMP.14 = "OWNER.NAME.PARTITION"
TEMP.15 = COPIES('-',264)
'EXECIO' 15 'DISKW REPORT13 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT13 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT A.OWNER",
", A.NAME",
", A.PARTITION",
"FROM SYSIBM.SYSTABSTATS A",
"WHERE NOT EXISTS ( SELECT 1 FROM SYSIBM.SYSTABLEPART B",
"WHERE A.DBNAME = B.DBNAME",
"AND A.TSNAME = B.TSNAME",
"AND A.PARTITION = B.PARTITION",
")",
"ORDER BY A.OWNER, A.NAME, A.PARTITION"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3"
DO WHILE SQLCODE = 0
TEMP.1 = HV1 || '.' || HV2 || '.' || HV3
'EXECIO' 1 'DISKW REPORT13 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT13 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1
'EXECIO 0 DISKW REPORT13 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT13 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 13 completed'
RETURN /* end generateReport13 */
generateReport14: PROCEDURE
/*****************************************************************/
/* Generates premigration report 14: */
/* Report orphaned rows in SYSCOLAUTH */
/*****************************************************************/
"EXECIO 0 DISKW REPORT14 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT14 OPEN rc=' rc
TEMP.1 = "=== REPORT 14" COPIES('=',58)
TEMP.2 = "= Orphaned rows in DB2 catalog table",
"SYSIBM.SYSCOLAUTH ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= Listed below are orphaned rows in",
"catalog table SYSIBM.SYSCOLAUTH, ="
TEMP.5 = "= which records the UPDATE or REFERENCES",
"privileges that are held by ="
TEMP.6 = "= users on individual columns of a table",
"or view. These rows do not ="
TEMP.7 = "= affect normal DB2 operations but may",
"interfere with migration ="
TEMP.8 = "= processing. Therefore, before",
"beginning migration to DB2 12, you ="
TEMP.9 = "= need to remove them using the",
"REPAIR utility. The following example ="
TEMP.10 = "= SQL shows how to generate a",
"REPAIR control statement that you can ="
TEMP.11 = "= use to delete all of the orphaned",
"rows: ="
TEMP.12 = "= ",
" ="
TEMP.13 = "= SELECT 'REPAIR OBJECT LOG YES' ",
" ="
TEMP.14 = "= FROM SYSIBM.SYSDUMMY1 ",
" ="
TEMP.15 = "= UNION ALL ",
" ="
TEMP.16 = "= SELECT DISTINCT ",
" ="
TEMP.17 = "= ' LOCATE",
"TABLESPACE DSNDB06.SYSTSFAU ' ="
TEMP.18 = "= || 'RID(X''' ||",
"RIGHT(HEX(RID(A)),10) || ''') DELETE' ="
TEMP.19 = "= FROM SYSIBM.SYSCOLAUTH A",
" ="
TEMP.20 = "= WHERE NOT EXISTS (SELECT 1 FROM",
"SYSIBM.SYSCOLUMNS B ="
TEMP.21 = "= WHERE A.CREATOR",
" = B.TBCREATOR ="
TEMP.22 = "= AND A.TNAME",
" = B.TBNAME ="
TEMP.23 = "= AND A.COLNAME",
" = B.NAME ="
TEMP.24 = "= ) ",
" ="
TEMP.25 = "= ORDER BY 1 DESC ",
" ="
TEMP.26 = "= ",
" ="
TEMP.27 = "= After running the REPAIR operation, review",
"the output and also check ="
TEMP.28 = "= the status of table space DSNDB06.SYSTSFAU",
"for CHECK PENDING status. ="
TEMP.29 = COPIES('=',72)
'EXECIO' 29 'DISKW REPORT14 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT14 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT RIGHT(HEX(RID(A)), 10)",
", A.GRANTOR",
", A.GRANTEE",
", CASE A.GRANTEETYPE",
"WHEN ' ' THEN 'AUTH ID'",
"WHEN 'L' THEN 'ROLE'",
"WHEN 'P' THEN 'PLAN OR PACKAGE'",
"ELSE 'OTHER'",
"END",
", A.CREATOR",
", A.TNAME",
", A.COLNAME",
", A.COLLID",
", HEX(A.CONTOKEN)",
", CASE A.PRIVILEGE",
"WHEN ' ' THEN 'UPDATE'",
"WHEN 'R' THEN 'REFERENCES'",
"ELSE 'OTHER'",
"END",
", A.GRANTEDTS",
", CASE GRANTORTYPE",
"WHEN ' ' THEN 'AUTH ID'",
"WHEN 'L' THEN 'ROLE'",
"ELSE 'OTHER'",
"END",
"FROM SYSIBM.SYSCOLAUTH A",
"WHERE NOT EXISTS ( SELECT 1 FROM SYSIBM.SYSCOLUMNS B",
"WHERE A.CREATOR = B.TBCREATOR",
"AND A.TNAME = B.TBNAME",
"AND A.COLNAME = B.NAME",
")",
"ORDER BY A.CREATOR, A.TNAME, A.COLNAME"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4,",
":HV5, :HV6, :HV7, :HV8,",
":HV9, :HV10, :HV11, :HV12"
DO WHILE SQLCODE = 0
TEMP.1 = COPIES('-',141)
TEMP.2 = "RID :" HV1
TEMP.3 = "GRANTOR :" HV2
TEMP.4 = "GRANTEE :" HV3
TEMP.5 = "GRANTEETYPE:" HV4
TEMP.6 = "CREATOR :" HV5
TEMP.7 = "TNAME :" HV6
TEMP.8 = "COLNAME :" HV7
TEMP.9 = "COLLID :" HV8
TEMP.10 = "CONTOKEN :" HV9
TEMP.11 = "PRIVILEGE :" HV10
TEMP.12 = "GRANTEDTS :" HV11
TEMP.13 = "GRANTORTYPE:" HV12
'EXECIO' 13 'DISKW REPORT14 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT14 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4,",
":HV5, :HV6, :HV7, :HV8,",
":HV9, :HV10, :HV11, :HV12"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1
'EXECIO 0 DISKW REPORT14 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT14 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 14 completed'
RETURN /* end generateReport14 */
generateReport15: PROCEDURE
/*****************************************************************/
/* Generates premigration report 15: */
/* Report extraneous text in SYSIBM.SYSTRIGGERS.TEXT */
/*****************************************************************/
"EXECIO 0 DISKW REPORT15 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT15 OPEN rc=' rc
TEMP.1 = "=== REPORT 15" COPIES('=',58)
TEMP.2 = "= Extraneous text in TEXT column of",
"SYSIBM.SYSTRIGGERS ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= For each trigger listed below, the",
"TEXT column of SYSIBM.SYSTRIGGERS ="
TEMP.5 = "= contains additional or extraneous SQL",
"text after the CREATE TRIGGER ="
TEMP.6 = "= statement. Such triggers cannot be",
"rebuilt by the REPAIR DBD REBUILD ="
TEMP.7 = "= utility after you migrate to DB2 10. ",
"In response, drop and recreate ="
TEMP.8 = "= these triggers before you migrate to",
"DB2 12. Note: The extraneous ="
TEMP.9 = "= SQL text can result when a create",
"trigger statement that contains a ="
TEMP.10 = "= BEGIN ATOMIC clause is improperly",
"terminated by a semicolon instead ="
TEMP.11 = "= of an alternative statement terminator.",
" If you recreate a trigger ="
TEMP.12 = "= using the CREATE TRIGGER statement in",
"the TEXT column of SYSIBM.- ="
TEMP.13 = "= SYSTRIGGERS, be sure to exclude the",
"extraneous text. If the CREATE ="
TEMP.14 = "= TRIGGER statement processes with",
"SQLCODE +098, drop the trigger and ="
TEMP.15 = "= reinspect the statement for improper",
"use of the semicolon. ="
TEMP.16 = COPIES('=',72)
TEMP.17 = "Schema.Name"
TEMP.18 = COPIES('-',257)
'EXECIO' 18 'DISKW REPORT15 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT15 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT RTRIM(SCHEMA) || '.' || RTRIM(NAME)",
"FROM SYSIBM.SYSTRIGGERS",
"WHERE TEXT LIKE '%CREATE TRIGGER % BEGIN ATOMIC %;%END%; %'",
"ORDER BY SCHEMA, NAME"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1"
DO WHILE SQLCODE = 0
TEMP.1 = HV1
'EXECIO' 1 'DISKW REPORT15 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT15 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1
'EXECIO 0 DISKW REPORT15 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT15 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 15 completed'
RETURN /* end generateReport15 */
generateReport16: PROCEDURE
/*****************************************************************/
/* Generates premigration report 16: */
/* Report Unicode columns in EBCDIC tables */
/*****************************************************************/
"EXECIO 0 DISKW REPORT16 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT16 OPEN rc=' rc
TEMP.1 = "=== REPORT 16" COPIES('=',58)
TEMP.2 = "= Unicode columns in EBCDIC tables ",
" ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The columns listed below are Unicode columns",
"that were defined in ="
TEMP.5 = "= EBCDIC-encoded tables before V12R1M500",
"activation. ="
TEMP.6 = "= These columns are subject to the Db2 11",
"restrictions. ="
TEMP.7 = "= With Db2 function level V12R1M500,",
"newly-defined Unicode columns in ="
TEMP.8 = "= EBCDIC tables are no longer subject to those",
"restrictions. Also, ="
TEMP.9 = "= support for Unicode columns that were defined",
"in EBCDIC tables prior ="
TEMP.10 = "= to V12R1M500 activation will be",
"deprecated in the future. ="
TEMP.11 = "= Therefore, use the ALTER TABLE ALTER COLUMN",
"SET DATA TYPE statement ="
TEMP.12 = "= to convert those columns as soon as possible.",
" ="
TEMP.13 = "= ",
" ="
TEMP.14 = "= For a Db2 11 column definition like: ",
" ="
TEMP.15 = "= column-name VARBIN(length) CCSID 1208 ",
" ="
TEMP.16 = "= use: ",
" ="
TEMP.17 = "= ALTER TABLE creator.table-name ",
" ="
TEMP.18 = "= ALTER COLUMN column-name ",
" ="
TEMP.19 = "= SET DATA TYPE VARCHAR(length) ",
" ="
TEMP.20 = "= ",
" ="
TEMP.21 = "= For a Db2 11 column definition like: ",
" ="
TEMP.22 = "= column-name VARBIN(length) CCSID 1200 ",
" ="
TEMP.23 = "= use: ",
" ="
TEMP.24 = "= ALTER TABLE creator.table-name ",
" ="
TEMP.25 = "= ALTER COLUMN column-name ",
" ="
TEMP.26 = "= SET DATA TYPE VARGRAPHIC(length) ",
" ="
TEMP.27 = "= ",
" ="
TEMP.28 = "= where the data type and data length must be the",
"same data type and ="
TEMP.29 = "= data length as the existing column, i.e. the",
"data type and data ="
TEMP.30 = "= length cannot be altered. ",
" ="
TEMP.31 = "= ",
" ="
TEMP.32 = "= Note: ALTER TABLE cannot proceed if there are",
"other conditions that ="
TEMP.33 = "= exist to prevent the column from being",
"altered. ="
TEMP.34 = "= For example, an index is defined on the",
"column, the table is ="
TEMP.35 = "= a clone table, the table is a history",
"table, the table is a ="
TEMP.36 = "= system-period temporal table, etc. Those",
"conditions must be ="
TEMP.37 = "= removed before the base table can be",
"altered. ="
TEMP.38 = "= ",
" ="
TEMP.39 = COPIES('=',72)
'EXECIO' 39 'DISKW REPORT16 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT16 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT RTRIM(TBCREATOR) || '.' || RTRIM(TBNAME)",
", RTRIM(NAME)",
", RTRIM(COLTYPE)",
", LENGTH",
", CCSID",
"FROM SYSIBM.SYSCOLUMNS",
"WHERE CCSID IN( 1200, 1208 )",
"AND COLTYPE = 'VARBIN'",
"ORDER BY 1"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
HV1_OLD = ''
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4, :HV5"
DO WHILE SQLCODE = 0
IF HV1 \= HV1_OLD THEN,
/*************************************************************/
/* If the creator.table name changes, add a report break */
/*************************************************************/
DO
IF HV1_OLD \= '' THEN,
/*********************************************************/
/* Insert a blank line ahead of the break (except for */
/* the first auth type) */
/*********************************************************/
DO
TEMP.1 = COPIES('-',140)
'EXECIO' 1 'DISKW REPORT16 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT16 rc=' rc
END
TEMP.1 = 'CREATOR.TABLE-NAME :' HV1
'EXECIO' 1 'DISKW REPORT16 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT16 rc=' rc
HV1_OLD = HV1
END
TEMP.1 = 'COLUMN-NAME :' HV2
TEMP.2 = 'COLUMN-TYPE(LENGTH):' HV3 || '(',
|| HV4 || ') CCSID ',
|| HV5
TEMP.3 = ''
'EXECIO' 3 'DISKW REPORT16 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT16 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4, :HV5"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV1_OLD HV2 HV3 HV4 HV5
'EXECIO 0 DISKW REPORT16 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT16 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 16 completed'
RETURN /* end generateReport16 */
generateReport17: PROCEDURE
/*****************************************************************/
/* Generates premigration report 17: */
/* Report indexes on Unicode columns in EBCDIC tables */
/*****************************************************************/
"EXECIO 0 DISKW REPORT17 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT17 OPEN rc=' rc
TEMP.1 = "=== REPORT 17" COPIES('=',58)
TEMP.2 = "= Indexes on Unicode columns in EBCDIC tables",
" ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The indexes listed below are indexes that were",
"defined on Unicode ="
TEMP.5 = "= columns in EBCDIC tables that were defined",
"prior to DB2 12 new ="
TEMP.6 = "= function availability. After activating new",
"function in DB2 12, ="
TEMP.7 = "= you need to drop those indexes before you can",
"alter the column in ="
TEMP.8 = "= order to lift the DB2 11 restrictions that are",
"currently imposed ="
TEMP.9 = "= on the column. After the DB2 11 restrictions",
"are lifted from ="
TEMP.10 = "= the column, you can then recreate the index.",
" ="
TEMP.11 = COPIES('=',72)
'EXECIO' 11 'DISKW REPORT17 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT17 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT RTRIM(I.CREATOR) || '.' || RTRIM(I.NAME)",
", RTRIM(I.TBCREATOR) || '.' || RTRIM(I.TBNAME)",
"FROM SYSIBM.SYSINDEXES I",
", SYSIBM.SYSKEYS K",
", SYSIBM.SYSCOLUMNS C",
"WHERE I.NAME = K.IXNAME",
"AND I.CREATOR = K.IXCREATOR",
"AND I.TBNAME = C.TBNAME",
"AND I.TBCREATOR = C.TBCREATOR",
"AND K.COLNO = C.COLNO",
"AND C.CCSID IN (1200, 1208)",
"AND C.COLTYPE = 'VARBIN'",
"GROUP BY RTRIM(I.CREATOR) || '.' || RTRIM(I.NAME)",
", RTRIM(I.TBCREATOR) || '.' || RTRIM(I.TBNAME)"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2"
DO WHILE SQLCODE = 0
TEMP.1 = 'CREATOR.INDEX-NAME:' HV1
TEMP.2 = 'CREATOR.TABLE-NAME:' HV2
TEMP.3 = ''
'EXECIO' 3 'DISKW REPORT17 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT17 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV2
'EXECIO 0 DISKW REPORT17 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT17 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 17 completed'
RETURN /* end generateReport17 */
generateReport18: PROCEDURE
/*****************************************************************/
/* Generates premigration report 18: */
/* Report obsolete RLSTs */
/*****************************************************************/
"EXECIO 0 DISKW REPORT18 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT18 OPEN rc=' rc
TEMP.1 = "=== REPORT 18" COPIES('=',58)
TEMP.2 = "= Old-format RLSTs that need to be updated",
"before DB2 12 migration ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The Resource Limit Specification Tables",
"(RLSTs) listed below are not ="
TEMP.5 = "= in a format that is supported in DB2 12. ",
"In DB2 12, RLSTs must have ="
TEMP.6 = "= 11 columns and the AUTHID, RLFCOLLN,",
"and RLFPKG columns must each be ="
TEMP.7 = "= defined as VARCHAR(128). Before migrating",
"to DB2 12, alter these ="
TEMP.8 = "= tables to comply with this format: ",
" ="
TEMP.9 = "= ",
" ="
TEMP.10 = "= CREATE TABLE DSNRLSTxx ",
" ="
TEMP.11 = "= ( AUTHID VARCHAR(128) NOT",
"NULL WITH DEFAULT ="
TEMP.12 = "= , PLANNAME CHAR(8) NOT",
"NULL WITH DEFAULT ="
TEMP.13 = "= , ASUTIME INTEGER, ",
" ="
TEMP.14 = "= , LUNAME CHAR(8) NOT",
"NULL WITH DEFAULT ="
TEMP.15 = "= , RLFFUNC CHAR(1) NOT",
"NULL WITH DEFAULT ="
TEMP.16 = "= , RLFBIND CHAR(1) NOT",
"NULL WITH DEFAULT ="
TEMP.17 = "= , RLFCOLLN VARCHAR(128) NOT",
"NULL WITH DEFAULT ="
TEMP.18 = "= , RLFPKG VARCHAR(128) NOT",
"NULL WITH DEFAULT ="
TEMP.19 = "= , RLFASUERR INTEGER ",
" ="
TEMP.20 = "= , RLFASUWARN INTEGER ",
" ="
TEMP.21 = "= , RLF_CATEGORY_B CHAR(1) NOT",
"NULL WITH DEFAULT ="
TEMP.22 = "= ) ",
" ="
TEMP.23 = COPIES('=',72)
TEMP.24 = "RLST CREATOR.NAME"
TEMP.25 = COPIES('-',72)
'EXECIO' 25 'DISKW REPORT18 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT18 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"WITH OLD_FORMAT_RLST_TABLES (FULL_NAME) AS",
"( /***** detecting v2r3 rlst table format *****/",
"SELECT STRIP(TBCREATOR) || '.' || STRIP(TBNAME)",
"FROM SYSIBM.SYSCOLUMNS",
"WHERE TBNAME LIKE 'DSNRLST__'",
"GROUP BY TBCREATOR, TBNAME",
"HAVING (MAX(COLNO) < 11)",
"UNION",
"/***** detecting v6/7 rlst table format *****/",
"SELECT STRIP(TBCREATOR) || '.' || STRIP(TBNAME)",
"FROM SYSIBM.SYSCOLUMNS",
"WHERE TBNAME LIKE 'DSNRLST__'",
"AND( (NAME = 'AUTHID' AND LENGTH <> 128)",
"OR (NAME = 'RLFCOLLN' AND LENGTH <> 128)",
"OR (NAME = 'RLFPKG' AND LENGTH <> 128)",
")",
")",
"/***** select from expression *****/",
"SELECT DISTINCT(FULL_NAME)",
"FROM OLD_FORMAT_RLST_TABLES",
"ORDER BY FULL_NAME"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1"
DO WHILE SQLCODE = 0
TEMP.1 = HV1
'EXECIO' 1 'DISKW REPORT18 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT18 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1
'EXECIO 0 DISKW REPORT18 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT18 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 18 completed'
RETURN /* end generateReport18 */
generateReport19: PROCEDURE
/*****************************************************************/
/* Generates premigration report 19: */
/* Report process for converting to UNLOAD privilege */
/*****************************************************************/
"EXECIO 0 DISKW REPORT19 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT19 OPEN rc=' rc
TEMP.1 = "=== REPORT 19" COPIES('=',58)
TEMP.2 = "= Authorization change for the DB2 UNLOAD utility",
" ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= DB2 12 introduces a new authorization privilege,",
"UNLOAD, that ="
TEMP.5 = "= replaces the SELECT privilege for controlling who",
"can use the DB2 ="
TEMP.6 = "= UNLOAD utility to unload data from a table. You",
"can grant the UNLOAD ="
TEMP.7 = "= privilege to a user prior to activation of DB2 12",
"new function but ="
TEMP.8 = "= it does not become effective until new function is",
"activated. To ="
TEMP.9 = "= avoid disrupting users of the UNLOAD utility,",
"follow these steps ="
TEMP.10 = "= when migrating to DB2 12: ",
" ="
TEMP.11 = "= (1) Set the DB2 AUTH_COMPATIBILITY parameter to",
"include option ="
TEMP.12 = "= SELECT_FOR_UNLOAD. This option disables",
"UNLOAD privilege ="
TEMP.13 = "= checking and retains the traditional use of",
"the SELECT ="
TEMP.14 = "= privilege for UNLOAD. ",
" ="
TEMP.15 = "= (2) Activate serviceability trace IFCID 404 to",
"audit the usage of ="
TEMP.16 = "= the SELECT privilege for UNLOAD utility",
"access. ="
TEMP.17 = "= (3) Grant the UNLOAD privilege to users on",
"objects identified by ="
TEMP.18 = "= the IFCID 404 trace. IDs that are granted",
"the UNLOAD privilege ="
TEMP.19 = "= will continue to use SELECT access for",
"UNLOAD utility access ="
TEMP.20 = "= until you remove the SELECT_FOR_UNLOAD",
"option from the DB2 ="
TEMP.21 = "= AUTH_COMPATIBILITY parameter. However,",
"these IDs will no longer ="
TEMP.22 = "= be flagged by IFCID 404. ",
" ="
TEMP.23 = "= (4) After the access switchover has been",
"completed, remove the ="
TEMP.24 = "= SELECT_FOR_UNLOAD option from the DB2",
"AUTH_COMPATIBILITY ="
TEMP.25 = "= parameter to enable use of the UNLOAD",
"privilege for UNLOAD ="
TEMP.26 = "= utility access. ",
" ="
TEMP.27 = "= ",
" ="
TEMP.28 = "= NOTE: Because SELECT authorization can be",
"granted for many reasons ="
TEMP.29 = "= other than access to the UNLOAD utility,",
"this report does not ="
TEMP.30 = "= include a list of IDs that have SELECT",
"authorization. It is ="
TEMP.31 = "= advisory only. ",
" ="
TEMP.32 = COPIES('=',72)
'EXECIO' 32 'DISKW REPORT19 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT19 rc=' rc
drop TEMP.
'EXECIO 0 DISKW REPORT19 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT19 CLOSE rc=' rc
CALL IntMsg,
' Report 19 completed'
RETURN /* end generateReport19 */
generateReport20: PROCEDURE
/*****************************************************************/
/* Generates premigration report 20: */
/* Report tables that have an OBID of 1 */
/*****************************************************************/
"EXECIO 0 DISKW REPORT20 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT20 OPEN rc=' rc
TEMP.1 = "=== REPORT 20" COPIES('=',58)
TEMP.2 = "= Tables that have an OBID of 1 ",
" ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= Following is a list of tables that were created",
"with an OBID of 1. ="
TEMP.5 = "= Use of OBID 1 in the CREATE TABLE statement was",
"disallowed by APAR ="
TEMP.6 = "= PM61352. This APAR also directed that all",
"existing tables having an ="
TEMP.7 = "= OBID of 1 be dropped and recreated with a",
"different OBID. ="
TEMP.8 = "= ",
" ="
TEMP.9 = "= A subsequent APAR, PI88793, enforces the OBID",
"restriction during: ="
TEMP.10 = "= - conversion from non-UTS to UTS table space",
"types ="
TEMP.11 = "= - table space versioning",
" ="
TEMP.12 = "= - adding a check constraint to a table",
" ="
TEMP.13 = "= - creating or altering an index to include",
"a row change timestamp ="
TEMP.14 = "= column ",
" ="
TEMP.15 = "= - creating an index with the DEFINE NO",
"option ="
TEMP.16 = "= Such requests will fail with SQLCODE -736 for",
"tables that have an ="
TEMP.17 = "= OBID of 1. To avoid this error, you need to",
"drop each table listed ="
TEMP.18 = "= below and recreate it with a different OBID.",
" ="
TEMP.19 = "= ",
" ="
TEMP.20 = "= You need to complete this work before beginning",
"migration to the ="
TEMP.21 = "= next release of Db2. ",
" ="
TEMP.22 = COPIES('=',72)
TEMP.23 = "CREATOR.NAME"
TEMP.24 = COPIES('-',257)
'EXECIO' 24 'DISKW REPORT20 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT20 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT STRIP(CREATOR)",
", STRIP(NAME)",
"FROM SYSIBM.SYSTABLES",
"WHERE OBID = 1",
"AND ( TYPE = 'T'",
"OR TYPE = 'M'",
"OR TYPE = 'H'",
"OR TYPE = 'R' )"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2"
DO WHILE SQLCODE = 0
TEMP.1 = HV1 || "." || HV2
'EXECIO' 1 'DISKW REPORT20 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT20 rc=' rc
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV2
'EXECIO 0 DISKW REPORT20 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT20 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 20 completed'
RETURN /* end generateReport20 */
generateReport21: PROCEDURE
/*****************************************************************/
/* Generates premigration report 21: */
/* Report foreign key constraints that have a Db2 catalog */
/* table as a parent */
/*****************************************************************/
"EXECIO 0 DISKW REPORT21 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT21 OPEN rc=' rc
TEMP.1 = "=== REPORT 21" COPIES('=',58)
TEMP.2 = "= Foreign key constraints that have a Db2 catalog",
"table as a parent ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= Following is a list of foreign key constraints",
"that have a Db2 ="
TEMP.5 = "= catalog table as a parent. Such constraints",
"can cause failures when ="
TEMP.6 = "= used by the LOAD DATA utility or by processing",
"of a DROP TABLESPACE ="
TEMP.7 = "= request. Executing other statements or utilities",
"that use the ="
TEMP.8 = "= constraint may result in unexpected behavior.",
" APAR PI87444 modifies ="
TEMP.9 = "= Db2 to prevent establishing such relationships",
"but does not resolve ="
TEMP.10 = "= existing foreign key constraints where a catalog",
"table is the parent.="
TEMP.11 = "= These foreign key constraints, listed below, need",
"to be dropped. ="
TEMP.12 = "= ",
" ="
TEMP.13 = "= To avoid Db2 catalog migration errors,",
"you need to remove them ="
TEMP.14 = "= before beginning migration to the next",
"release of Db2. ="
TEMP.15 = COPIES('=',72)
'EXECIO' 15 'DISKW REPORT21 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT21 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT STRIP(REL.RELNAME) AS CONSTRAINT_NAME",
", STRIP(TAB_CHILD.CREATOR) AS CHILD_TABLECREATOR",
", STRIP(TAB_CHILD.NAME) AS CHILD_TABLENAME",
", STRIP(TAB_PARENT.CREATOR) AS PARENT_TABLECREATOR",
", STRIP(TAB_PARENT.NAME) AS PARENT_TABLENAME",
"FROM SYSIBM.SYSTABLES TAB_CHILD",
", SYSIBM.SYSRELS REL",
", SYSIBM.SYSTABLES TAB_PARENT",
"WHERE TAB_CHILD.CREATOR = REL.CREATOR",
"AND TAB_CHILD.NAME = REL.TBNAME",
"AND TAB_CHILD.DBID <> 6",
"AND TAB_PARENT.CREATOR = REL.REFTBCREATOR",
"AND TAB_PARENT.NAME = REL.REFTBNAME",
"AND TAB_PARENT.DBID = 6"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4, :HV5"
DO WHILE SQLCODE = 0
TEMP.1 = COPIES('-',150)
TEMP.2 = "CONSTRAINT NAME :" HV1
TEMP.3 = "CHILD TABLE CREATOR :" HV2
TEMP.4 = "CHILD TABLE NAME :" HV3
TEMP.5 = "PARENT TABLE CREATOR:" HV4
TEMP.6 = "PARENT TABLE NAME :" HV5
'EXECIO' 6 'DISKW REPORT21 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT21 rc=' rc
address DSNREXX "EXECSQL FETCH C1",
"INTO :HV1, :HV2, :HV3, :HV4, :HV5"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV2 HV3 HV4 HV5
'EXECIO 0 DISKW REPORT21 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT21 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 21 completed'
RETURN /* end generateReport21 */
generateReport22: PROCEDURE
/*****************************************************************/
/* Generates premigration report 22: */
/* Report expression-based indexes created in Db2 9 */
/*****************************************************************/
"EXECIO 0 DISKW REPORT22 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT22 OPEN rc=' rc
TEMP.1 = "=== REPORT 22" COPIES('=',58)
TEMP.2 = "= Indexes created in Db2 9 for z/OS that",
"are expression-based indexes ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= Following is a list of indexes that were created as",
" ="
TEMP.5 = "= expression-based indexes in Db2 9.",
" ="
TEMP.6 = "= ",
" ="
TEMP.7 = "= If an expression-based index was created in Db2 9,",
"its runtime ="
TEMP.8 = "= structure cannot be executed on Db2 12, thus Db2 12",
"will implicitly ="
TEMP.9 = "= alter regenerate the index when it is referenced",
"in an SQL statement ="
TEMP.10 = "= as an access path. To avoid the implicit alter",
"regenerate activity ="
TEMP.11 = "= for DML applications that run on Db2 12 later,",
"you must explicitly ="
TEMP.12 = "= alter regenerate each expression-based index",
"listed in the report ="
TEMP.13 = "= below. ",
" ="
TEMP.14 = "= ",
" ="
TEMP.15 = "= If you expect to run applications on Db2 11 data",
"sharing members ="
TEMP.16 = "= during migration to Db2 12, alter regenerate these",
"indexes before ="
TEMP.17 = "= you begin migration to Db2 12. Otherwise, complete",
"the migration to ="
TEMP.18 = "= Db2 12 first. Then explicitly alter regenerate the",
"expression-based ="
TEMP.19 = "= indexes listed below, after migration to Db2 12 is",
"complete. ="
TEMP.20 = "= ",
" ="
TEMP.21 = "= If you alter regenerate the indexes in Db2 12, you",
"can check the ="
TEMP.22 = "= SYSINDEXES.REGENERATETS value to confirm that the",
"index regeneration ="
TEMP.23 = "= succeeded. ",
" ="
TEMP.24 = "= ",
" ="
TEMP.25 = "= The following example SQL shows how to alter",
"regenerate an index: ="
TEMP.26 = "= ALTER INDEX index-name REGENERATE",
" ="
TEMP.27 = "= ",
" ="
TEMP.28 = "= After running the alter index regenerate operation,",
"check the status ="
TEMP.29 = "= of the index for REBUILD-pending (RBDP) status.",
" This operation ="
TEMP.30 = "= might also invalidate packages that depend on",
"the index, or packages ="
TEMP.31 = "= that depend on related objects through cascading",
"effects. ="
TEMP.32 = "= ",
" ="
TEMP.33 = "= Note: REPORT22 continues to identify all",
"expression-based indexes ="
TEMP.34 = "= created in Db2 9, regardless of whether",
"the alter index ="
TEMP.35 = "= regenerate is complete.",
" ="
TEMP.36 = COPIES('=',72)
TEMP.37 = "CREATOR.NAME"
TEMP.38 = COPIES('-',257)
'EXECIO' 38 'DISKW REPORT22 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT22 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT STRIP(CREATOR)",
", STRIP(NAME)",
"FROM SYSIBM.SYSINDEXES",
"WHERE (IX_EXTENSION_TYPE = 'S'",
"OR IX_EXTENSION_TYPE = 'T')",
"AND RELCREATED = 'M'"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2"
DO WHILE SQLCODE = 0
TEMP.1 = HV1 || "." || HV2
'EXECIO' 1 'DISKW REPORT22 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT22 rc=' rc
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV2
'EXECIO 0 DISKW REPORT22 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT22 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 22 completed'
RETURN /* end generateReport22 */
generateReport23: PROCEDURE
/*****************************************************************/
/* Generates premigration report 23: */
/* Report native SQL procedures and compiled SQL scalar */
/* functions created in V9 */
/*****************************************************************/
"EXECIO 0 DISKW REPORT23 (OPEN"
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT23 OPEN rc=' rc
TEMP.1 = "=== REPORT 23" COPIES('=',58)
TEMP.2 = "= Native SQL procedures and compiled",
"SQL scalar functions created in ="
TEMP.3 = "= Db2 9 for z/OS ",
" ="
TEMP.4 = COPIES('=',72)
TEMP.5 = "= Following is a list of native SQL procedures and",
"compiled SQL ="
TEMP.6 = "= scalar functions created in Db2 9 for z/OS.",
" ="
TEMP.7 = "= ",
" ="
TEMP.8 = "= If a native SQL procedure or a compiled SQL",
"scalar function was ="
TEMP.9 = "= created in Db2 9 and runs on Db2 12, Db2 will",
"implicitly alter ="
TEMP.10 = "= regenerate the routine. To avoid the implicit",
"alter regenerate ="
TEMP.11 = "= activity, you must explicity alter regenerate",
"each routine listed ="
TEMP.12 = "= in the report below. ",
" ="
TEMP.13 = "= ",
" ="
TEMP.14 = "= If you expect to run these routines on Db2 11",
"data sharing members ="
TEMP.15 = "= during migration to Db2 12, alter regenerate",
"these routines before ="
TEMP.16 = "= you begin migration to Db2 12. Otherwise, complete",
"the migration to ="
TEMP.17 = "= Db2 12 first. Then explicitly alter regenerate the",
"routines listed ="
TEMP.18 = "= below, after migration to Db2 12 is complete.",
" ="
TEMP.19 = "= ",
" ="
TEMP.20 = "= If you alter regenerate the routines in Db2 12, you",
"can check the ="
TEMP.21 = "= SYSROUTINES.REGENERATETS value to confirm that the",
"routine ="
TEMP.22 = "= regeneration succeeded. ",
" ="
TEMP.23 = "= ",
" ="
TEMP.24 = "= The following example SQL shows how to alter",
"regenerate a native ="
TEMP.25 = "= SQL procedure: ",
" ="
TEMP.26 = "= ALTER PROCEDURE schema.name ",
" ="
TEMP.27 = "= REGENERATE VERSION version-id ",
" ="
TEMP.28 = "= ",
" ="
TEMP.29 = "= The following example SQL shows how to alter",
"regenerate a compiled ="
TEMP.30 = "= SQL scalar function: ",
" ="
TEMP.31 = "= ALTER SPECIFIC FUNCTION schema.specificname",
" ="
TEMP.32 = "= REGENERATE VERSION version-id ",
" ="
TEMP.33 = "= ",
" ="
TEMP.34 = "= Note: REPORT23 continues to identify all native",
"SQL procedures and ="
TEMP.35 = "= compiled SQL scalar functions created in",
"Db2 9, regardless ="
TEMP.36 = "= of whether the alter routine regenerate is",
"already done. ="
TEMP.37 = COPIES('=',72)
'EXECIO' 37 'DISKW REPORT23 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT23 rc=' rc
SQLSTMT = "DECLARE C1 CURSOR FOR S1"
address DSNREXX EXECSQL SQLSTMT
if sqlcode <> 0 then call errsqlca 'DECLARE C1 CURSOR FOR S1'
SQLSTMT =,
"SELECT CASE ROUTINETYPE",
" WHEN 'F' THEN 'FUNCTION'",
" ELSE 'PROCEDURE'",
" END",
" , RTRIM(SCHEMA) || '.' || RTRIM(NAME)",
" , RTRIM(SCHEMA) || '.' || RTRIM(SPECIFICNAME)",
" , RTRIM(VERSION)",
" FROM SYSIBM.SYSROUTINES",
" WHERE RELCREATED = 'M'",
" AND (ORIGIN = 'N'",
" OR (ORIGIN = 'Q' AND INLINE='N'))",
" ORDER BY ROUTINETYPE, SCHEMA, SPECIFICNAME"
address DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT"
if sqlcode<>0 then call errsqlca "PREPARE S1 FROM :SQLSTMT"
address DSNREXX "EXECSQL OPEN C1"
if sqlcode<>0 then call errsqlca "OPEN C1"
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2, :HV3, :HV4"
DO WHILE SQLCODE = 0
TEMP.1 = COPIES('-',278)
TEMP.2 = "ROUTINETYPE :" HV1
IF HV1 = 'PROCEDURE' THEN
TEMP.3 = "SCHEMA.NAME :" HV2
ELSE
TEMP.3 = "SCHEMA.SPECIFICNAME:" HV3
TEMP.4 = "VERSION-ID :" HV4
'EXECIO' 4 'DISKW REPORT23 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT23 rc=' rc
address DSNREXX "EXECSQL FETCH C1 INTO :HV1, :HV2, :HV3, :HV4"
END
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP. HV1 HV2 HV3 HV4
'EXECIO 0 DISKW REPORT23 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT23 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
CALL IntMsg,
' Report 23 completed'
RETURN /* end generateReport23 */
generateReport24: PROCEDURE
/*****************************************************************/
/* Generates premigration report 24: */
/* Report active log data sets greater than 4 GB in size */
/*****************************************************************/
ctr = 0
ctr2 = 0
numLogs = 0
haveActiveLogCopy1 = false
haveActiveLogCopy2 = false
haveData = false
haveAllocation = false
haveHiARba = false
mbrnam = " "
grpnam = " "
EOF=0;
NUMERIC DIGITS 18
'EXECIO 0 DISKW REPORT24 (OPEN'
if rc<>0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT24 OPEN rc=' rc
TEMP.1 = "=== REPORT 24" COPIES('=',58)
TEMP.2 = "= Existence of oversized active log data sets ",
" ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The data sets listed below are active log data",
"sets for this Db2 ="
TEMP.5 = "= subsystem or data sharing group that are defined",
"with more than 4 GB ="
TEMP.6 = "= of space, or the size of the active log data set",
"cannot be ="
TEMP.7 = "= determined.",
" ="
TEMP.8 = "= ",
" ="
TEMP.9 = "= Based on the value of the LISTCAT_RESULT column",
"in this report, ="
TEMP.10 = "= proceed as follows:",
" ="
TEMP.11 = "= ",
" ="
TEMP.12 = "= 1. If the LISTCAT_RESULT column value is",
"'RC=0, Greater than 4 GB': ="
TEMP.13 = "= The LISTCAT command performed on the active",
"log data set ="
TEMP.14 = "= completed with RC=0 and this data set is",
"identified as an ="
TEMP.15 = "= oversized data set that is defined with",
"more than 4 GB of space. ="
TEMP.16 = "= The space above 4 GB is ignored by Db2 11",
"but causes Db2 12 to ="
TEMP.17 = "= fail startup before new function activation.",
"Therefore, before ="
TEMP.18 = "= migrating to Db2 12, stop Db2 and use the",
"REPRO command to copy ="
TEMP.19 = "= these oversized data sets to new data sets",
"that are defined with ="
TEMP.20 = "= a size of 4 GB or less.",
" ="
TEMP.21 = "= ",
" ="
TEMP.22 = "= When defining the new active log data sets,",
"ensure that the ="
TEMP.23 = "= primary quantity does not exceed the",
"following values, and a ="
TEMP.24 = "= secondary quantity of 0.",
" ="
TEMP.25 = "= ",
" ="
TEMP.26 = "= For 3390-9 volumes:",
" ="
TEMP.27 = "= 87375 tracks, if allocated in units of",
"tracks ="
TEMP.28 = "= 5825 cylinders, if allocated in units of",
"cylinders ="
TEMP.29 = "= ",
" ="
TEMP.30 = "= For 3390-A volumes:",
" ="
TEMP.31 = "= 5817 cylinders. 3390-A volumes are extended",
"address volumes ="
TEMP.32 = "= (EAV), for which the space allocation unit",
"can be 21 ="
TEMP.33 = "= cylinders, instead of one cylinder.",
" ="
TEMP.34 = "= ",
" ="
TEMP.35 = "= After the REPRO operation is complete, use the",
"LISTCAT command to ="
TEMP.36 = "= verify that the high allocated RBA of each new",
"data set does not ="
TEMP.37 = "= exceed 4 GB. Rename the new log data sets to",
"their old data set ="
TEMP.38 = "= names then start Db2 with the new active log",
"data sets before ="
TEMP.39 = "= migrating to Db2 12.",
" ="
TEMP.40 = "= ",
" ="
TEMP.41 = "= Attention: After new function activation, Db2",
"12 supports an ="
TEMP.42 = "= active log data set size of up to 768",
"GB of space. ="
TEMP.43 = "= Special setup is required for data",
"sets that are ="
TEMP.44 = "= defined with more than 4 GB. See the",
"'Active log ="
TEMP.45 = "= data sets storage requirements'",
"section of the ="
TEMP.46 = "= Db2 12 for z/OS Installation and",
"Migration Guide for ="
TEMP.47 = "= more information about using active",
"log data sets in ="
TEMP.48 = "= Db2 12 after activation of new",
"function. ="
TEMP.49 = "= ",
" ="
TEMP.50 = "= Sample REPRO JCL: ",
" ="
TEMP.51 = "= //STEP1 EXEC PGM=IDCAMS ",
" ="
TEMP.52 = "= //SYSPRINT DD SYSOUT=* ",
" ="
TEMP.53 = "= //SYSUDUMP DD SYSOUT=* ",
" ="
TEMP.54 = "= //INDS DD DSN=old_dsname,DISP=SHR ",
" ="
TEMP.55 = "= //SYSIN DD * ",
" ="
TEMP.56 = "= REPRO - ",
" ="
TEMP.57 = "= INFILE(INDS) - ",
" ="
TEMP.58 = "= OUTDATASET(new_dsname) ",
" ="
TEMP.59 = "= //* ",
" ="
TEMP.60 = "= ",
" ="
TEMP.61 = "= Sample LISTCAT JCL: ",
" ="
TEMP.62 = "= //STEP1 EXEC PGM=IDCAMS ",
" ="
TEMP.63 = "= //SYSPRINT DD SYSOUT=* ",
" ="
TEMP.64 = "= //SYSUDUMP DD SYSOUT=* ",
" ="
TEMP.65 = "= //SYSIN DD * ",
" ="
TEMP.66 = "= LISTCAT ENTRIES(new_dsname) - ",
" ="
TEMP.67 = "= DATA - ",
" ="
TEMP.68 = "= ALLOCATION ",
" ="
TEMP.69 = "= //* ",
" ="
TEMP.70 = "= ",
" ="
TEMP.71 = "= Sample ALTER JCL to rename a new data set to its",
" ="
TEMP.72 = "= old data set name:",
" ="
TEMP.73 = "= //STEP1 EXEC PGM=IDCAMS ",
" ="
TEMP.74 = "= //SYSPRINT DD SYSOUT=* ",
" ="
TEMP.75 = "= //SYSUDUMP DD SYSOUT=* ",
" ="
TEMP.76 = "= //SYSIN DD * ",
" ="
TEMP.77 = "= ALTER - ",
" ="
TEMP.78 = "= old_dsname - ",
" ="
TEMP.79 = "= NEWNAME(save_dsname) ",
" ="
TEMP.80 = "= ALTER - ",
" ="
TEMP.81 = "= old_dsname.DATA - ",
" ="
TEMP.82 = "= NEWNAME(save_dsname.DATA) ",
" ="
TEMP.83 = "= ",
" ="
TEMP.84 = "= ALTER - ",
" ="
TEMP.85 = "= new_dsname - ",
" ="
TEMP.86 = "= NEWNAME(old_dsname) ",
" ="
TEMP.87 = "= ALTER - ",
" ="
TEMP.88 = "= new_dsname.DATA - ",
" ="
TEMP.89 = "= NEWNAME(old_dsname.DATA) ",
" ="
TEMP.90 = "= //* ",
" ="
TEMP.91 = "= ",
" ="
TEMP.92 = "= 2. If the LISTCAT_RESULT column value is not",
"RC=0 ="
TEMP.93 = "= or is 'RC=0, HI-A-RBA Not Found':",
" ="
TEMP.94 = "= The LISTCAT command for the active log data",
"set did not ="
TEMP.95 = "= complete with RC=0 or it completed with RC=0",
"but the HI-A-RBA ="
TEMP.96 = "= of the data set is not found in the LISTCAT",
"output. Therefore, ="
TEMP.97 = "= it cannot be determined if the active log data",
"set is an ="
TEMP.98 = "= oversized data set with a size greater than",
"4 GB. ="
TEMP.99 = "= ",
" ="
TEMP.100 = "= If the LISTCAT command did not complete",
"successfully, the ="
TEMP.101 = "= LISTCAT return code is shown in the",
"corresponding LISTCAT_RESULT ="
TEMP.102 = "= column.",
" ="
TEMP.103 = "= ",
" ="
TEMP.104 = "= Otherwise, if the LISTCAT command",
"completed successfully ="
TEMP.105 = "= but the HI-A-RBA is not found in the LISTCAT",
"output, the message ="
TEMP.106 = "= 'RC=0, HI-A-RBA Not Found' is shown in the",
"corresponding ="
TEMP.107 = "= LISTCAT_RESULT column.",
" ="
TEMP.108 = "= ",
" ="
TEMP.109 = "= In both cases, run a LISTCAT command",
"on the active log data ="
TEMP.110 = "= set to determine the cause of the error.",
" ="
TEMP.111 = "= ",
" ="
TEMP.112 = COPIES('=',72)
TEMP.113 = "GROUP MEMBER ",
"ACTIVE LOG DATA SET NAME ",
"LISTCAT_RESULT"
TEMP.114 = "======== ========",
"============================================",
"=============="
'EXECIO' 114 'DISKW REPORT24 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT24 rc=' rc
/*****************************************************************/
/* Process DSNJU004 output to get the names of the active log */
/* data sets (copy 1 and copy2) for all members, as well as */
/* their respective group and member names. */
/*****************************************************************/
'EXECIO 0 DISKR JU004PRT (FINIS'
if rc <> 0 then call Dsn_Error 'Read_source_dsn',
, 'EXECIO DISKR JU004PRT CLOSE rc=' rc
'EXECIO 0 DISKR JU004PRT (OPEN'
if rc <> 0 then call Dsn_Error 'Read_source_dsn',
, 'EXECIO DISKR JU004PRT OPEN rc=' rc
do until EOF /* read loop */
/* Set all possible compound variables whose names begin with */
/* stem TEMP to nulls */
TEMP. = ''
'EXECIO 100 DISKR JU004PRT (OPEN STEM TEMP.' /* block fetch */
if rc <> 0 then
do
EOF=1;
if rc <> 2 then call Dsn_Error 'Read_source_dsn',
, 'EXECIO DISKR JU004PRT rc=' rc
end /* EOF */
/***************************************************************/
/* Get the active log data set names and their respective */
/* group and member names from the DSNJU004 report. */
/* Save the info in the stem variables LOG.n.DSN, LOG.n.GROUP, */
/* and LOG.n.MEMBER. */
/***************************************************************/
do ctr=1 to TEMP.0;
if INDEX(TEMP.ctr, ,
'LOG MAP OF THE BSDS DATA SET BELONGING TO MEMBER') ,
> 0 then
do
parse var TEMP.ctr . "'" mbrnam "'" . "'" grpnam "'"
haveActiveLogCopy1 = false
haveActiveLogCopy2 = false
end
if INDEX(TEMP.ctr, 'ACTIVE LOG COPY 1 DATA SETS') > 0 then
haveActiveLogCopy1 = true
if INDEX(TEMP.ctr, 'ARCHIVE LOG COPY 1 DATA SETS') > 0 then
haveActiveLogCopy1 = false
if INDEX(TEMP.ctr, 'ACTIVE LOG COPY 2 DATA SETS') > 0 then
haveActiveLogCopy2 = true
if INDEX(TEMP.ctr, 'ARCHIVE LOG COPY 2 DATA SETS') > 0 then
haveActiveLogCopy2 = false
if LENGTH(TEMP.ctr) > 66 then
do
if ((haveActiveLogCopy1 = true & ,
INDEX(TEMP.ctr, 'DSN=', 63) > 0) | ,
(haveActiveLogCopy2 = true & ,
INDEX(TEMP.ctr, 'DSN=', 63) > 0)) then
do
ctr2 = ctr2 + 1
startPos = INDEX(TEMP.ctr, 'DSN=') + 4
LOG.ctr2.GROUP = grpnam
LOG.ctr2.MEMBER = mbrnam
LOG.ctr2.DSN = STRIP(SUBSTR(TEMP.ctr, startPos))
LOG.ctr2.ALLOC = 0
LOG.ctr2.LCATRES = "RC=0"
end
end
end /* do ctr loop */
end /* end: until EOF fetch loop */
'EXECIO 0 DISKR JU004PRT (FINIS'
if rc <> 0 then call Dsn_Error 'Read_source_dsn',
, 'EXECIO DISKR JU004PRT CLOSE rc=' rc
numLogs = ctr2
/*****************************************************************/
/* For each active log data set, issue LISTCAT command and */
/* get the size of each active log data set from the HI-A-RBA */
/* setting in the LISTCAT report. */
/*****************************************************************/
do ctr2=1 to numLogs;
TEMP. = ''
haveData = false
haveAllocation = false
haveHiARba = false
x="OUTTRAP"('TEMP.')
Address TSO "LISTCAT ENTRIES('"LOG.ctr2.DSN"') DATA ALLOCATION"
x="OUTTRAP"('OFF')
LOG.ctr2.LCATRES = "RC="rc
if rc <> 0 then
iterate
/***************************************************************/
/* Parse the LISTCAT report to extract high allocated RBA. */
/***************************************************************/
do ctr=1 to TEMP.0;
if INDEX(TEMP.ctr, 'DATA ------- ') > 0 then
haveData = true
if haveData = true & INDEX(TEMP.ctr, 'ALLOCATION') > 0 then
haveAllocation = true
if haveData = true & haveAllocation = true & ,
INDEX(TEMP.ctr, 'HI-A-RBA-') > 0 then
do
/*********************************************************/
/* Extract the high allocated RBA value from the */
/* HI-A-RBA setting in the LISTCAT report */
/* Example HI-A-RBA setting: HI-A-RBA--------20643840 */
/*********************************************************/
haveHiARba = true
startPos = INDEX(TEMP.ctr, 'HI-A-RBA-') + 8
hiARba = SUBSTR(TEMP.ctr, startPos)
hiARbaLen = LENGTH(hiARba)
rbapos = 1
do while rbapos <= hiARbaLen
rbaCurVAL = SUBSTR(hiARba, rbapos, 1)
if VERIFY(rbaCurVal, "0123456789") = 0 then
do
LOG.ctr2.ALLOC = SUBSTR(hiARba, rbapos)
LEAVE /* skip parsing rest of HI-A-RBA setting */
end /* VERIFY */
rbapos = rbapos + 1
end /* do while */
LEAVE /* skip the rest of the LISTCAT report */
end /* HI-A-RBA found */
end /* do ctr loop (scan LISTCAT report) */
if haveHiARba = false then
LOG.ctr2.LCATRES = "RC=0, HI-A-RBA Not Found"
end /* do ctr2 loop (process all active log data sets) */
/*****************************************************************/
/* Print active log data sets with size over 4 GB or */
/* whose size cannot be determined. */
/*****************************************************************/
do ctr2=1 to numLogs;
if (LOG.ctr2.ALLOC > 4294967295),
| (LOG.ctr2.LCATRES <> "RC=0") then
do
if (LOG.ctr2.ALLOC > 4294967295) then
LOG.ctr2.LCATRES = "RC=0, Greater than 4 GB"
TEMP.1 = LOG.ctr2.GROUP LOG.ctr2.MEMBER ,
LEFT(LOG.ctr2.DSN, 44) LOG.ctr2.LCATRES
'EXECIO' 1 'DISKW REPORT24 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT24 rc=' rc
end
end /* do ctr2 loop */
drop TEMP. LOG. /* release block */
'EXECIO 0 DISKW REPORT24 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT24 CLOSE rc=' rc
CALL IntMsg,
' Report 24 completed'
RETURN /* end generateReport24 */
IntMsg: PROCEDURE expose debug ownmsg.; TRACE O
PARSE ARG msg
SAY msg
RETURN /* end IntMsg */
/********************************************************************/
/* Got a dataset allocation or I/O error, print out message & abort.*/
/********************************************************************/
Dsn_Error:
if debug<3 then trace O
/* "ARG"(1) is the code section name, "ARG"(2) the event text. */
call IntMsg 'I/O error occurred in "'"ARG"(1)'". Event:' "ARG"(2)
call Abort 23
return /* end Dsn_Error */
/********************************************************************/
/* Got a failure SQLCODE, write diagnostics to result set and abort.*/
/********************************************************************/
ERRSQLCA:
IF debug<3 THEN TRACE O
CALL IntMsg '** Error SQL statement -' "ARG"(1)
/* If this is a CREATE, show the statement we are working with. */
IF "WORD"("ARG"(1),1) = 'CREATE' THEN DO
CALL IntMsg 'SQL statement follows:'
CALL IntMsg sqlstmt
END
CALL IntMsg 'SQLCODE ='SQLCODE
CALL IntMsg 'SQLERRMC='||"TRANSLATE"(SQLERRMC,';','FF'x)
CALL IntMsg 'SQLERRP ='SQLERRP
CALL IntMsg 'SQLERRD ='SQLERRD.1','SQLERRD.2','SQLERRD.3,
||','SQLERRD.4','SQLERRD.5','SQLERRD.6
CALL IntMsg 'SQLWARN ='SQLWARN.0','SQLWARN.1','SQLWARN.2,
||','SQLWARN.3','SQLWARN.4','SQLWARN.5,
||','SQLWARN.6','SQLWARN.7','SQLWARN.8,
||','SQLWARN.9','SQLWARN.10
CALL IntMsg 'SQLSTATE='SQLSTATE
ADDRESS DSNREXX "EXECSQL ROLLBACK"
CALL Abort 27
RETURN /* end ERRSQLCA */
/********************************************************************/
/* Got a bad enough error to stop processing. Put an error message */
/* into the result set and terminate with RC>=8. */
/********************************************************************/
Abort:
IF aborting=1 THEN RETURN; ELSE aborting=1
IF debug<3 THEN TRACE O
IF debug>0 THEN SAY 'Subroutine: Abort'
PARSE ARG errorid .
SELECT /* errorid */
WHEN errorid=23 THEN DO
CALL IntMsg 'Aborting due to I/O errors.'
high_rc = 8
END
WHEN errorid=27 THEN DO
CALL IntMsg 'Aborting due to unexpected SQLCODE.'
high_rc = 8
END
WHEN errorid=44 THEN DO
CALL IntMsg 'Aborting due to more table spaces requested than',
|| 'could be added.'
high_rc = 8
END
WHEN errorid=54 THEN DO
CALL IntMsg 'Aborting due to unexpected parameter setting.'
high_rc = 8
END
OTHERWISE DO
CALL IntMsg 'ABORT: invalid error id' errorid
high_rc = 999
END
END /* SELECT errorid */
IF "WORDPOS"( high_rc, '0 4 8 999' ) > 0 /* An expected value ?..*/
THEN high_rc = 8 /* Yes, normal abort rc=8 */
ELSE high_rc = 999 /* No, force internal err */
EXIT Finis() /* end Abort */
/********************************************************************/
/* Do clean up processing and exit. Put all internally generated */
/* messages into global temp table for result set. Open the result */
/* set cursor. Set the highest return code. Close all files and */
/* exit */
/********************************************************************/
Finis:
SAY '*** End RC='high_rc
RETURN high_rc /* normal end of DSNTPMQ */
//*
//DSNTPMQ EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)
//SYSEXEC DD DSN=&&TEMPPDS,DISP=(OLD,DELETE)
//JU004PRT DD DSN=&&TEMPPDS2(JU004PRT),DISP=(OLD,DELETE)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSNTPMQ !DSN! !SYSADM!
//REPORT01 DD SYSOUT=*
//REPORT02 DD SYSOUT=*
//REPORT03 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=274)
//REPORT04 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=274)
//REPORT05 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=402)
//REPORT06 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=322)
//REPORT07 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=277)
//REPORT08 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=284)
//REPORT09 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=72)
//REPORT10 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=148)
//REPORT11 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=89)
//REPORT12 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=72)
//REPORT13 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=264)
//REPORT14 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=141)
//REPORT15 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=274)
//REPORT16 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=274)
//REPORT17 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=274)
//REPORT18 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=274)
//REPORT19 DD SYSOUT=*
//REPORT20 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=257)
//REPORT21 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=150)
//REPORT22 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=257)
//REPORT23 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=278)
//REPORT24 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=87)
//*