Release Notes

15.12 Chapter 10. Capture and Apply for OS/390

In Chapter 10, the following paragraphs are updated:

15.12.1 Prerequisites for DB2 DataPropagator for OS/390

You must have DB2 for OS/390 Version 5, DB2 for OS/390 Version 6, or DB2 for OS/390 Version 7 to run DB2 DataPropagator for OS/390 Version 7 (V7).

15.12.2 UNICODE and ASCII Encoding Schemes on OS/390

DB2 DataPropagator for OS/390 V7 supports UNICODE and ASCII encoding schemes. To exploit the new encoding schemes, you must have DB2 for OS/390 V7 and you must manually create or convert your DB2 DataPropagator source, target, and control tables as described in the following sections. However, your existing replication environment will work with DB2 DataPropagator for OS/390 V7 even if you do not modify any encoding schemes.

15.12.2.1 Choosing an Encoding Scheme

If your source, CD, and target tables use the same encoding scheme, you can minimize the need for data conversions in your replication environment. When you choose encoding schemes for the tables, follow the single CCSID rule: Character data in a table space can be encoded in ASCII, UNICODE, or EBCDIC. All tables within a table space must use the same encoding scheme. The encoding scheme of all the tables in an SQL statement must be the same. Also, all tables that you use in views and joins must use the same encoding scheme.

If you do not follow the single CCSID rule, DB2 will detect the violation and return SQLCODE -873 during bind or execution. Which tables should be ASCII or UNICODE depends on your client/server configuration. Specifically, follow these rules when you choose encoding schemes for the tables:

• Source or target tables on DB2 for OS/390 can be EBCDIC, ASCII, or UNICODE. They can be copied from or to tables that have the same or different encoding scheme in any supported DBMS (DB2 family, or non-DB2 with DataJoiner).
• On a DB2 for OS/390 source server, all CD, UOW, register, and prune control tables on the same server must use the same encoding scheme. To ensure this consistency, always specify the encoding scheme explicitly.
• All the control tables (ASN.IBMSNAP_SUBS_xxxx) on the same control server must use the same encoding scheme.
• Other control tables can use any encoding scheme; however, it is recommended that the ASN.IBMSNAP_CRITSEC table remain EBCDIC.

15.12.2.2 Setting Encoding Schemes

To specify the proper encoding scheme for tables, modify the SQL that is used to generate the tables:

• Create new source and target tables with the proper encoding scheme, or change the encoding schemes of the existing target and source tables. It is recommended that you stop the Capture and Apply programs before you change the encoding scheme of existing tables, and afterwards that you cold start the Capture program and restart the Apply program. To change the encoding scheme of existing tables:
  1. Use the Reorg utility to copy the existing table.
  2. Drop the existing table.
  3. Re-create the table specifying the new encoding scheme.
  4. Use the Load utility to load the old data into the new table.

  See the DB2 Universal Database for OS/390 Utility Guide and Reference for more information on the Load and Reorg utilities.

• Create new control tables with the proper encoding scheme or modify the encoding scheme for existing ones.

  DPCNTL.MVS is shipped with DB2 for OS/390 in sqllib\samples\repl and it contains several CREATE TABLE statements that create the control tables. For those tables that need to be ASCII or UNICODE (for example, ASN.IBMSNAP_REGISTER and ASN.IBMSNAP_PRUNCNTL), add the CCSID ASCII or CCSID UNICODE keyword, as shown in the following example.

  CREATE TABLE ASN.IBMSNAP_PRUNCNTL (    
    TARGET_SERVER      CHAR( 18)              NOT NULL,
    TARGET_OWNER       CHAR( 18)              NOT NULL,
    TARGET_TABLE       CHAR( 18)              NOT NULL,
    SYNCHTIME          TIMESTAMP,
    SYNCHPOINT         CHAR( 10)              FOR BIT DATA,
    SOURCE_OWNER       CHAR( 18)              NOT NULL,
    SOURCE_TABLE       CHAR( 18)              NOT NULL,
    SOURCE_VIEW_QUAL   SMALLINT               NOT NULL,
    APPLY_QUAL         CHAR( 18)              NOT NULL,
    SET_NAME           CHAR( 18)              NOT NULL,
    CNTL_SERVER        CHAR( 18)              NOT NULL,
    TARGET_STRUCTURE   SMALLINT               NOT NULL,
    CNTL_ALIAS         CHAR(  8)
          )  CCSID UNICODE
             DATA CAPTURE CHANGES
             IN TSSNAP02;
  

  To modify existing control tables and CD tables, use the Reorg and Load utilities.

• When you create new replication sources or subscription sets, modify the SQL file generated by the administration tool to specify the proper encoding scheme. The SQL has several CREATE TABLE statements that are used to create the CD and target tables for the replication source and subscription set, respectively. Add the keyword CCSID ASCII or CCSID UNICODE where appropriate. For example:

  CREATE TABLE   user1.cdtable1 (
    employee_name   varchar,
    employee_age    decimal  
      )  CCSID UNICODE; 
  

  The DB2 UDB for OS/390 SQL Reference contains more information about CCSID.