Run premigration queries (DSNTIJPM)
Before you migrate to Db2 13, run premigration queries on the Db2 12 catalog.
Before you begin
Complete the premigration activities and activate function level 510 in Db2 12, as described in Verify Db2 13 premigration activities and activate function level 510 in Db2 12. The DSNTIJPE job that you ran for that task is functionally equivalent to the DSNTIJPM. However, depending on when you activated function level 510, it is possible that more release incompatibilities were added since.
Important: To identify native SQL routines that need to be regenerated for use in Db2 13, apply the PTF for APAR PH57086 before you run job DSNTIJPE in Db2 12 or DSNTIPM in Db2 13. See PH57086.
Procedure
To run premigration queries:
- Copy the updated job DSNTIJPM from Db2 13 to Db2 12.
- 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 13, see the report descriptions in job DSNTIJPM.
Premigration job DSNTIJPM for migration to Db2 13
The content of the DSNTIJPM premigration job is presented here for informational purposes only. Use the actual job that is supplied with the Db2 13 product code when you complete this task.
//*********************************************************************
//* NAME = DSNTIJPM
//*
//* DESCRIPTIVE NAME = DB2 INSTALL JOB
//*
//* Licensed Materials - Property of IBM
//* 5698-DB2
//* COPYRIGHT IBM Corp 1982, 2022
//*
//* STATUS = Version 13
//*
//* FUNCTION = RUN THIS JOB ON DB2 12 PRIOR TO:
//* (1) ACTIVATION OF DB2 12 FUNCTION LEVEL V12R1M510
//* (2) MIGRATION TO DB2 13
//*
//* IT QUERIES THE DB2 CATALOG TO IDENTIFY CONDITIONS THAT
//* WILL NEED TO BE REMEDIED BEFORE YOU ATTEMPT TO MIGRATE
//* TO DB2 13 OR ATTEMPT TO ACTIVATE DB2 12 FUNCTION LEVEL
//* V12R1M510, OR CONDITIONS THAT NEED TO BE ATTENDED TO
//* AFTER YOU HAVE MIGRATED TO DB2 13.
//*
//* THERE ARE TWO FORMS OF OUTPUT FROM THIS JOB:
//* (1) REPORTS THAT IDENTIFY CONDITIONS ON DB2 12
//* THAT NEED TO BE REMEDIED BEFORE YOU COMMENCE
//* MIGRATION TO DB2 13 OR ATTEMPT TO ACTIVATE DB2 12
//* FUNCTION LEVEL V12R1M510, OR CONDITIONS THAT
//* NEED TO BE ATTENDED TO AFTER YOU HAVE MIGRATED
//* TO DB2 13.
//*
//* (2) A DATA SET OF 'REBIND PACKAGE' DSN SUBCOMMANDS THAT
//* CAN BE INPUT TO DSN TO REBIND PACKAGES THAT NEED
//* TO BE REBOUND TO AVOID AUTOMATIC REBIND WHEN THE
//* PACKAGES ARE EXECUTED IN DB2 13. THIS DATA SET
//* IS ALLOCATED TO THE REBIND08 DD CARD. REVIEW THE
//* CONTENTS OF THIS DATA SET CAREFULLY PRIOR TO
//* PROCESSING IT.
//*
//* PSEUDOCODE =
//* DELETE STEP REMOVE DATA SETS CREATED BY PREVIOUS RUNS
//* 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. '!SYSADM!' TO THE AUTHORIZATION ID TO BE USED
//* BY DB2 QUERIES IN THIS JOB.
//* 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.
//*
//* CHANGE ACTIVITY =
//* 2021/02/21 PH33727 Created for Db2 12
//* 2021/05/25 PH36927 Fix REPORT01 to look for DSN8D12A
//* 2022/08/29 PH48911 Remove REPORT22
//* 2022/12/01 PH48053 Add Db2 zAI EXPLAIN table columns
//* 2023/04/13 PH53833 Update the description in REPORT16
//*
//*********************************************************************
//*
//JOBLIB DD DSN=DSN!!0.SDSNEXIT,DISP=SHR
// DD DSN=DSN!!0.SDSNLOAD,DISP=SHR
//*
//DELETE EXEC PGM=IEFBR14
//REBIND08 DD DSN=DSN!!0.DSNTIJPM.REBIND08,
// DISP=(MOD,DELETE),
// UNIT=SYSDA,SPACE=(TRK,0)
//*
//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 ssid
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
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 12 sample database ",
" ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The following lists the Db2 12",
"sample database, DSN8D12A. This ="
TEMP.5 = "= database is needed for verifying",
"migration to Db2 13. If DSN8D12A ="
TEMP.6 = "= is not listed below, you are",
"recommended to recreate it before ="
TEMP.7 = "= commencing migration to Db2 13. ",
"Run Db2 12 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 12. See",
"the Db2 12 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( 'DSN8D12A' )"
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 13 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 13, you ="
TEMP.9 = "= will be unable to recreate it. Therefore, before",
"migrating to 13, ="
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 12 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 12) 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) < 23)",
"OR (TBNAME = 'DSN_DETCOST_TABLE'",
"AND MAX(COLNO) < 139)",
"OR (TBNAME = 'DSN_FILTER_TABLE'",
"AND MAX(COLNO) < 20)",
"OR (TBNAME = 'DSN_FUNCTION_TABLE'",
"AND MAX(COLNO) < 22)",
"OR (TBNAME = 'DSN_KEYTGTDIST_TABLE'",
"AND MAX(COLNO) < 23)",
"OR (TBNAME = 'DSN_PGRANGE_TABLE'",
"AND MAX(COLNO) < 17)",
"OR (TBNAME = 'DSN_PGROUP_TABLE'",
"AND MAX(COLNO) < 44)",
"OR (TBNAME = 'DSN_PREDICAT_TABLE'",
"AND MAX(COLNO) < 44)",
"OR (TBNAME = 'DSN_PREDICATE_SELECTIVITY'",
"AND MAX(COLNO) < 17)",
"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) < 27)",
"OR (TBNAME = 'DSN_QUERY_TABLE'",
"AND MAX(COLNO) < 16)",
"OR (TBNAME = 'DSN_QUERYINFO_TABLE'",
"AND MAX(COLNO) < 19)",
"OR (TBNAME = 'DSN_SORT_TABLE'",
"AND MAX(COLNO) < 18)",
"OR (TBNAME = 'DSN_SORTKEY_TABLE'",
"AND MAX(COLNO) < 23)",
"OR (TBNAME = 'DSN_STATEMENT_CACHE_TABLE'",
"AND MAX(COLNO) < 104)",
"OR (TBNAME = 'DSN_STATEMNT_TABLE'",
"AND MAX(COLNO) < 34)",
"OR (TBNAME = 'DSN_STRUCT_TABLE'",
"AND MAX(COLNO) < 22)",
"OR (TBNAME = 'DSN_VIEWREF_TABLE'",
"AND MAX(COLNO) < 14)",
"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) < 68)",
"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 supported in Db2 13 */
/*****************************************************************/
"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 11. Db2 13 will not execute package",
"copies bound prior to Db2 ="
TEMP.6 = "= 11. If ABIND is YES or COEXIST, upon initial",
"execution of the CURRENT="
TEMP.7 = "= package copy, Db2 13 will automatically rebind",
"the package. If ABIND ="
TEMP.8 = "= is set to 'NO', Db2 13 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 13. Db2 13 will block a REBIND SWITCH to",
"a package copy that ="
TEMP.13 = "= is invalid or was bound before Db2 11. After",
"migration and before ="
TEMP.14 = "= initial REBIND on Db2 13, 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 13, 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 NOT IN ('P','Q','R') ",
" 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 NOT IN ('P','Q','R') ",
"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 13 */
/*****************************************************************/
"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 13 ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The following is a list of packages",
"that were last bound prior to ="
TEMP.5 = "= Db2 11. Such packages are not",
"supported after migration to Db2 13 ="
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 13 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 13. ="
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 NOT IN ('P','Q','R') ",
"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 13 */
/*****************************************************************/
"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 13 ="
TEMP.3 = COPIES('=',72)
TEMP.4 = "= The following is a list of plans",
"that were last bound prior to ="
TEMP.5 = "= Db2 11. Such plans are not",
"supported after migration to Db2 13 ="
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 13 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 13. ="
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 NOT IN ('P','Q','R') ",
"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: */
/* Reports packages to be rebound prior to migrate to */
/* release 13 or V12R1M510 activation */
/*****************************************************************/
PARSE ARG ssid /* Db2 Subsystem ID */
rowcount = 0
"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 need to be rebound prior",
"to activation of function level ="
TEMP.3 = "= V12R1M510 and prior to migration",
"to Db2 13. ="
TEMP.4 = COPIES('=',72)
TEMP.5 = "= The following is a list of the packages",
"that need to be rebound ="
TEMP.6 = "= before you attempt to activate function",
"level V12R1M510 or migration ="
TEMP.7 = "= to Db2 13. The listed packages were",
"used within 18 months and were ="
TEMP.8 = "= bound prior to Db2 11. REBIND commands",
"are listed in the data set ="
TEMP.9 = "= allocated to the REBIND08 DD card. ",
" ="
TEMP.10 = COPIES('=',72)
TEMP.11 = "Collection-ID.Package-name.(Version)"
TEMP.12 = COPIES('-',324)
'EXECIO' 12 'DISKW REPORT08 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT08 rc=' rc
TEMP.1 = "DSN S("ssid")"
'EXECIO' 1 'DISKW REBIND08 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REBIND08 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(COLLID)",
",STRIP(NAME)",
",STRIP(VERSION)",
",TYPE",
"FROM SYSIBM.SYSPACKAGE",
"WHERE LASTUSED >= DATE(DAYS(CURRENT DATE) - 548)",
" AND RELBOUND NOT IN ('P','Q','R')",
" AND VALID <> 'N'",
" AND OPERATIVE <> 'N'"
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 :PKCOLLID",
",:PKNAM",
",:PKVER",
",:PKTYPE"
DO WHILE SQLCODE = 0
rowcount = rowcount + 1
TEMP.1 = PKCOLLID || "." || PKNAM || ".(" || PKVER || ")"
'EXECIO' 1 'DISKW REPORT08 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT08 rc=' rc
PKID = PKCOLLID || '.' || PKNAM
IF LENGTH(PKVER) <> 0 THEN
PKID = PKID || '.(' || PKVER || ')'
n = 0
DO WHILE LENGTH(PKID) > 0
n = n + 1
IF n = 1 THEN
DO
IF PKTYPE <> 'T' THEN
DO
TEMP.n = "REBIND PACKAGE("
END
ELSE
DO
TEMP.n = "REBIND TRIGGER-"
n = n + 1
TEMP.n = " PACKAGE("
END
END
ELSE
TEMP.n = " "
IF LENGTH(PKID) > 56 THEN
DO
CHOP = SUBSTR(PKID,1,56)
PKID = SUBSTR(PKID,57)
TEMP.n = TEMP.n || CHOP || "+"
END
ELSE
DO
TEMP.n = TEMP.n || PKID || ")"
PKID = ""
END
END /* DO WHILE LENGTH(PKID) > 0 */
'EXECIO' n 'DISKW REBIND08 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REBIND08 rc=' rc
address DSNREXX "EXECSQL FETCH C1 INTO :PKCOLLID",
",:PKNAM",
",:PKVER",
",:PKTYPE"
END /* WHILE SQLCODE = 0 */
if sqlcode<>100 then call errsqlca "FETCH FROM C1"
drop TEMP.
'EXECIO 0 DISKW REPORT08 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT08 CLOSE rc=' rc
'EXECIO 0 DISKW REBIND08 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REBIND08 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
if rowcount = 0 then
do
call IntMsg,
' Report 8 completed'
end
else
do
call IntMsg,
' There are packages that need to be rebound.'
high_rc = 8
end
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 the DSNTIJTC job to fail. Before",
"running the DSNTIJTC job use ="
TEMP.7 = "= MODIFY RECOVERY followed by REORG to",
"correct the versioning of all ="
TEMP.8 = "= these table spaces. See the Utility",
"Guide for information about ="
TEMP.9 = "= these utilities. ",
" ="
TEMP.10 = COPIES('=',72)
TEMP.11 = "CREATOR.NAME.OLDEST_VERSION.CURRENT_VERSION"
TEMP.12 = COPIES('-',72)
'EXECIO' 12 '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",
"the DSNTIJTC job. Therefore, ="
TEMP.7 = "= before running the DSNTIJTC job,",
"contact IBM Service for assistance ="
TEMP.8 = "= 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 running the DSNTIJTC job, ="
TEMP.8 = "= 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 the ="
TEMP.9 = "= DSNTIJTC job. Therefore, before",
"running the DSNTIJTC job, you need ="
TEMP.10 = "= to remove each entry listed below by",
"running the following with ="
TEMP.11 = "= <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 the DSNTIJTC job. Therefore,",
"after reviewing these rows, delete ="
TEMP.7 = "= them before running the DSNTIJTC",
"job. 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 the DSNTIJTC job ="
TEMP.8 = "= . Therefore, before running the",
"DSNTIJTC job, you need to remove ="
TEMP.9 = "= them using the REPAIR utility.",
"The following example SQL shows how ="
TEMP.10 = "= to generate a REPAIR control statement",
"that you can use to delete ="
TEMP.11 = "= 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. In response, drop and",
"recreate these triggers to remove ="
TEMP.8 = "= extraneous SQL text. Note: The",
"extraneous SQL text can result when a ="
TEMP.9 = "= CREATE TRIGGER statement that contains",
"a BEGIN ATOMIC clause is ="
TEMP.10 = "= improperly terminated by a semicolon",
"instead of an alternative ="
TEMP.11 = "= statement terminator. If you recreate",
"a trigger using the CREATE ="
TEMP.12 = "= TRIGGER statement in the TEXT column",
"of SYSIBM.SYSTRIGGERS, be sure ="
TEMP.13 = "= to exclude the extraneous text. If",
"the CREATE TRIGGER statement ="
TEMP.14 = "= processes with SQLCODE +908, drop",
"the trigger and reinspect the ="
TEMP.15 = "= 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 V12R1M500 ="
TEMP.6 = "= activation. ",
" ="
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 13 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 13. ",
"In Db2 13, 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 13, 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 native SQL procedures and compiled SQL scalar */
/* functions created in V9 */
/*****************************************************************/
"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 = "= 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 13, 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 12",
"data sharing members ="
TEMP.15 = "= during migration to Db2 13, alter regenerate",
"these routines before ="
TEMP.16 = "= you begin migration to Db2 13. Otherwise, complete",
"the migration to ="
TEMP.17 = "= Db2 13 first. Then explicitly alter regenerate the",
"routines listed ="
TEMP.18 = "= below, after migration to Db2 13 is complete.",
" ="
TEMP.19 = "= ",
" ="
TEMP.20 = "= If you alter regenerate the routines in Db2 13, 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: REPORT19 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 REPORT19 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT19 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 IN ('M','O'),
" 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 REPORT19 (STEM TEMP.'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT19 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 REPORT19 (FINIS'
if rc <> 0 then call Dsn_Error 'Write_source_dsn',
, 'EXECIO DISKW REPORT19 CLOSE rc=' rc
address DSNREXX "EXECSQL CLOSE C1"
if sqlcode<>0 then call errsqlca "CLOSE C1"
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 */
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)
//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=324)
//REBIND08 DD DSN=DSN!!0.DSNTIJPM.REBIND08,
// DISP=(,CATLG),
// UNIT=SYSDA,SPACE=(1024,(10,10),RLSE),
// DCB=(RECFM=FB,LRECL=80)
//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=*,DCB=(RECFM=FB,LRECL=278)
//REPORT20 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=257)
//REPORT21 DD SYSOUT=*,DCB=(RECFM=FB,LRECL=150)
//*