Falling back to Version 10.1 with ARCH_LEVEL 1001

After you migrate the Q Capture and Q Apply control tables to Version 10.2.1 with ARCH_LEVEL 1021, you might need to fall back to the previous ARCH_LEVEL of 1001.

Before you begin

Version 10.1 replication APAR PM96954 must be installed for fallback from 1021 to work

About this task

To fall back to the Version 10.1 ARCH_LEVEL of 1001, you update the ARCH_LEVEL and COMPATIBILITY columns in the IBMQREP_CAPPARMS table from 1021 back to 1001. For the Q Capture control tables, you also need to convert any existing log sequence number (LSN) data in the IBMQREP_TABVERSION and IBMQREP_COLVERSION tables back to 10-byte length from the 16-byte length that is used in V10.2.1.

Note: For the Q Capture control tables, falling back to a level that is previous to ARCH_LEVEL 1001 is not supported. For the Q Apply control tables, you can fall back to ARCH_LEVEL 1001 or older levels.

Procedure

Shut down the Q Capture and Q Apply tasks.

Follow these steps to run the SASNSAMP(ASNQFALL) sample job to fall back to architecture level 1001:

Locate the sample in the SASNSAMP partitioned data set. The sample includes SQL statements to fall back for both the Q Capture and Q Apply control tables.
Add a valid job card.
Edit the sample to replace the following names:
- !DSN! to the name of your DB2® subsystem
- DSNTIA!! to the plan name for DSNTIAD on your DB2 subsystem (recommended value: DSNTIA91)
- DSN!!0 to the prefix of the target library for DB2
- !CSH to the name of your Q Capture schema
- !ASH to the name of your Q Apply schema
Run the statements that pertain to the Q Capture control tables. These statements start with //MIGQCAP2 EXEC PGM=IKJEFT01,DYNAMNBR=20 and end with these lines:
```
    WHERE SUBSTR(LSN,16,1)  > X'00'  AND LENGTH(LSN) = 16;

COMMIT;
```
Run the statements that pertain to the Q Apply control tables. These statements start with //MIGQAPP2 EXEC PGM=IKJEFT01,DYNAMNBR=20 and end with these lines:
```
UPDATE !ASH.IBMQREP_APPLYPARMS SET ARCH_LEVEL = '1001';

COMMIT;
```

Optional: If you created the TEXT column in the IBMQREP_EXCEPTIONS table with a VARCHAR data type in Version 10.2.1, you must drop and recreate the table with the TEXT column as a CLOB data type in Version 10.1. You can use the following SQL statements to create the 10.1 version. First make the following changes:

!APPTSF to your chosen name for the table space
!APPDB to to the name of the database that contains the Q Apply control tables
!ASH to the schema of the Q Apply control tables

CREATE TABLESPACE !APPTSF IN !APPDB USING STOGROUP SYSDEFLT
  PRIQTY  960 SECQTY  9600 SEGSIZE 4      FREEPAGE 00 PCTFREE 10
  LOCKSIZE ROW
  LOCKMAX    0 CLOSE NO COMPRESS NO  CCSID EBCDIC;

CREATE LOB TABLESPACE LXTIBMQR IN !APPDB   LOG NO;

CREATE TABLE !ASH.IBMQREP_EXCEPTIONS
( EXCEPTION_TIME   TIMESTAMP NOT NULL WITH DEFAULT,
 RECVQ             VARCHAR(48) NOT NULL,
 SRC_COMMIT_LSN    VARCHAR(48) FOR BIT DATA NOT NULL,
 SRC_TRANS_TIME    TIMESTAMP NOT NULL,
 SUBNAME           VARCHAR(132) NOT NULL,
 REASON            CHAR(12) NOT NULL,
 SQLCODE           INTEGER,
 SQLSTATE          CHAR(5),
 SQLERRMC          VARCHAR(70) FOR BIT DATA,
 OPERATION         VARCHAR(18) NOT NULL,
 TEXT              CLOB(32768) NOT NULL,
 IS_APPLIED        CHAR(1) NOT NULL,
 CONFLICT_RULE     CHAR(1),
 SRC_TRANS_ID      CHAR(48) FOR BIT DATA,
 SRC_INTENTSEQ     VARCHAR(16) FOR BIT DATA ,
 AUTHID            VARCHAR(128),
 AUTHTOKEN         VARCHAR(30),
 PLANNAME          VARCHAR(8))
 VOLATILE CARDINALITY  IN !APPDB.!APPTSF;

CREATE AUXILIARY TABLE !ASH.XTIBMQREP_EXCEPTI
 IN !APPDB.LXTIBMQR
 STORES !ASH.IBMQREP_EXCEPTIONS COLUMN TEXT;

CREATE INDEX !ASH.EXCEPTIX ON !ASH.IBMQREP_EXCEPTIONS
( EXCEPTION_TIME ASC ,RECVQ   ASC ,IS_APPLIED  ASC)
 USING STOGROUP SYSDEFLT  PRIQTY -1 SECQTY -1
 ERASE NO  FREEPAGE 5    CLOSE NO COPY NO COMPRESS NO ;

Optional: After falling back, if you want to start the Q Capture program in warm mode to avoid a new load of target tables, make sure that Q Replication V10.1 APAR PM94614 is installed. This APAR enables the V10.1 Q Capture program to handle the 16-byte restart LSN that was generated before the fallback operation.
If you cannot install this APAR, you can avoid a cold start and new load of target tables by starting Q Capture with the lsn and maxcmtseq parameters to specify a point in the log from which to begin reading after fallback. You will need to manually change the 16-byte restart LSN to 10 bytes by following these steps:
1. Initialize the first two bytes of the 10-byte LSN container to x0000.
2. Copy six bytes of the 16-byte LSN starting with byte 1 to byte 2 of the 10-byte LSN.
3. Copy two bytes of the 16-byte LSN starting with byte 14 to byte 8 of the 10-byte LSN.
For example, this 16-byte LSN from a DB2 data-sharing environment, 00cc:8d02:f43a:5d00:0000:0000:0000:0001, would be changed to 0000:cc8d:02f4:3a5d:0001.