10 Preparing the DB2 for z/OS Database for Oracle GoldenGate

Learn how to prepare your database and environment to support Oracle GoldenGate.

Topics:

• Preparing Tables for Processing
  
• Configuring a Database Connection
  
• Monitoring Processes
  
• Supporting Globalization Functions
  

Preparing Tables for Processing

You must perform the following tasks to prepare your tables for use in an Oracle GoldenGate environment.

• Disabling Triggers and Cascade Constraints
  
• Assigning Row Identifiers
  
• Handling ROWID Columns
  

Disabling Triggers and Cascade Constraints

Disable triggers, cascade delete constraints, and cascade update constraints on the target tables, or alter them to ignore changes made by the Oracle GoldenGate database user. Oracle GoldenGate replicates DML that results from a trigger or cascade constraint. If the same trigger or constraint gets activated on the target table, it becomes redundant because of the replicated version, and the database returns an error. Consider the following example, where the source tables are emp_src and salary_src and the target tables are emp_targ and salary_targ.

• A delete is issued for emp_src.

• It cascades a delete to salary_src.

• Oracle GoldenGate sends both deletes to the target.

• The parent delete arrives first and is applied to emp_targ.

• The parent delete cascades a delete to salary_targ.

• The cascaded delete from salary_src is applied to salary_targ.

• The row cannot be located because it was already deleted in step 5.

Assigning Row Identifiers

Oracle GoldenGate requires some form of unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

• How Oracle GoldenGate Determines the Kind of Row Identifier to Use
  
• Using KEYCOLS to Specify a Custom Key
  

How Oracle GoldenGate Determines the Kind of Row Identifier to Use

Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:

1. Primary key

2. First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.

3. If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are excluded from the Oracle GoldenGate configuration.

Note:

If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE clause.

Using KEYCOLS to Specify a Custom Key

If a table does not have one of the preceding types of row identifiers, or if you prefer those identifiers not to be used, you can define a substitute key if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. For more information, see Reference for Oracle GoldenGate.

Handling ROWID Columns

Any attempt to insert into a target table that includes a column with a data type of ROWID GENERATED ALWAYS (the default) will fail with the following ODBC error:

ODBC error: SQLSTATE 428C9 native database error -798. {DB2 FOR OS/390}{ODBC DRIVER}{DSN08015} DSNT408I SQLCODE = -798, ERROR: YOU CANNOT INSERT A VALUE INTO A COLUMN THAT IS DEFINED WITH THE OPTION GENERATED ALWAYS. COLUMN NAME ROWIDCOL.

You can do one of the following to prepare tables with ROWID columns to be processed by Oracle GoldenGate:

• Ensure that any ROWID columns in target tables are defined as GENERATED BY DEFAULT.

• If it is not possible to change the table definition, you can work around it with the following procedure.

To Work Around ROWID GENERATE ALWAYS:

1. For the source table, create an Extract TABLE statement, and use a COLSEXCEPT clause in that statement that excludes the ROWID column. For example:

   TABLE tab1, COLSEXCEPT (rowidcol);
   

   The COLSEXCEPT clause excludes the ROWID column from being captured and replicated to the target table.

2. For the target table, ensure that Replicat does not attempt to use the ROWID column as the key. This can be done in one of the following ways:

   • Specify a primary key in the target table definition.

   • If a key cannot be created, create a Replicat MAP parameter for the table, and use a KEYCOLS clause in that statement that contains any unique columns except for the ROWID column. Replicat will use those columns as a key. For example:

     MAP tab1, TARGET tab1, KEYCOLS (num, ckey);
     

For more information about KEYCOLS, see Assigning Row Identifiers.

Configuring a Database Connection

This section contains instructions for setting up the Extract and Replicat connections to a SQL Server database.

• Database Configuration for DB2 z/OS
  
• Database User for Oracle GoldenGate Processes
  
• Setting Initialization Parameters
  
• Specifying the Path to the Initialization File
  
• Ensuring ODBC Connection Compatibility
  
• Specifying the Number of Connection Threads
  

Database Configuration for DB2 z/OS

No special DB2 z/OS database settings are required for Oracle GoldenGate.

Database User for Oracle GoldenGate Processes

Oracle GoldenGate requires a database user account. Create this account and assign privileges according to the following guidelines.

Assign the DB2 privileges listed in Table 10-1 to the user by which Extract and Replicat will be running. These are in addition to any permissions that DB2 ODBC requires. All Extract privileges apply to initial-load and log-based Extract processes, except where noted. The following authorities can be provided by granting either SYSCTRL or DBADM plus SQLADM authority to the user running the Oracle GoldenGate processes.

Table 10-1 Privileges Needed by Oracle GoldenGate for DB2 z/OS

User privilege	Extract	Replicat
MONITOR2 (does not apply to initial-load Extract)	X
SELECT ON the following SYSIBM tables: SYSTABLES SYSCOLUMNS SYSTABLEPART SYSKEYS SYSINDEXES SYSCOLAUTH SYSDATABASE SYSFOREIGNKEYS SYSPARMS SYSRELS SYSROUTINES SYSSYNONYMS SYSTABAUTH SYSAUXRELS	X	X
SELECT on source tablesFoot 1	X
INSERT, UPDATE, DELETE on target tables		X
CREATE TABLEFoot 2		X
EXECUTE on ODBC plan (default is DSNACLI)	X
Privileges required by SQLEXEC procedures or queries that you will be using.Foot 3	X	X

^Footnote 1

SELECT on source tables required only if tables contain LOB columns, or for an initial-load Extract, if used.

^Footnote 2

Required if using ADD CHECKPOINTTABLE in GGSCI to use the database checkpoint feature.

^Footnote 3

SQLEXEC enables stored procedures and queries to be executed by an Oracle GoldenGate process.

Setting Initialization Parameters

The following DB2 for z/OS initialization parameters apply to Oracle GoldenGate and must be set correctly before starting Oracle GoldenGate processes.

• MVSDEFAULTSSID: set to the DB2 subsystem.

• LOCATION: set to the DB2 location name as stored in the DB2 Boot Strap Dataset.

• MVSATTACHTYPE: set to RRSAF (Recoverable Resource Manager Services Attachment Facility) or CAF (Call Attachment Facility). IBM recommends using RRSAF.

• MULTICONTEXT: set to 1 if using RRSAF.

• PLANNAME: set to the DB2 plan. The default plan name is DSNACLI.

Do not use the CURRENTAPPENSCH initialization parameter (keyword).

Note:

When using the CAF attachment type, you must use the Oracle GoldenGate DBOPTIONS parameter with the NOCATALOGCONNECT option in the parameter file of any Extract or Replicat process that connects to DB2. This parameter disables the usual attempt by Oracle GoldenGate to obtain a second thread for the DB2 catalog. Otherwise, you will receive error messages, such as: ODBC operation failed: Couldn't connect to data source for catalog queries.

Specifying the Path to the Initialization File

Specify the ODBC initialization file by setting the DSNAOINI environment variable in the z/OS UNIX profile, as in the following example:

export DSNAOINI="/etc/odbc810.ini"

Ensuring ODBC Connection Compatibility

To ensure that you configure the DB2 ODBC initialization file correctly, follow the guidelines in the DB2 UDB for z/OS ODBC Guide and Reference manual. One important consideration is the coding of the open and close square brackets (the [ character and the ] character). The square bracket characters are "variant" characters that are encoded differently in different coded character set identifiers (CCSID), but must be of the IBM-1047 CCSID in the ODBC initialization file. DB2 ODBC does not recognize brackets of any other CCSID. Note the following:

• The first (or open) bracket must use the hexadecimal characters X'AD' (0xAD).

• The second (or close) bracket must use the hexadecimal characters X'BD' (0xBD).

To set the correct code for square brackets, use any of the following methods.

• Use the hex command in OEDIT and change the hex code for each character appropriately.

• Use the iconv utility to convert the ODBC initialization file. For example, to convert from CCSID IBM-037 to IBM-1047, use the following command:

  iconv -f IBM-037 -t IBM-1047 ODBC.ini > ODBC-1047.ini 

  mv ODBC-1047.ini ODBC.ini 
  

• Change your terminal emulator or terminal configuration to use CCSID IBM-1047 when you create or alter the file.

Specifying the Number of Connection Threads

Every Oracle GoldenGate process makes a database connection. Depending on the number of processes that you will be using and the number of other DB2 connections that you expect, you might need to adjust the following DB2 system parameters on the DSNTIPE DB2 Thread Management Panel:

• MAX USERS (macro DSN6SYSP CTHREAD)

• MAX TSO CONNECT (macro DSN6SYSP IDFORE)

• MAX BATCH CONNECT (macro DSN6SYSP IDBACK)

If using RRSAF, allow:

• Two DB2 threads per process for each of the following:

  • Extract

  • Replicat

  • The GGSCI command DBLOGIN (logs into the database)

  • DEFGEN utility (generates data definitions for column mapping)

• One extra DB2 thread for Extract for IFI calls.

• One extra DB2 thread for each SQLEXEC parameter statement that will be issued by each Extract and Replicat process. For more information about SQLEXEC, see the Reference for Oracle GoldenGate.

If using CAF, there can be only one thread per Oracle GoldenGate process.

Monitoring Processes

These sections provide information about monitoring Oracle GoldenGate with z/OS system facilities.

• Interpreting Statistics for Update Operations
  

Interpreting Statistics for Update Operations

The actual number of DML operations that are executed on the DB2 database might not match the number of extracted DML operations that are reported by Oracle GoldenGate. DB2 does not log update statements if they do not physically change a row, so Oracle GoldenGate cannot detect them or include them in statistics.

Supporting Globalization Functions

Oracle GoldenGate provides globalization support and you should take into consideration when using this support.

• Replicating From a Source that Contains Both ASCII and EBCDIC
  
• Specifying Multi-Byte Characters in Object Names
  

Replicating From a Source that Contains Both ASCII and EBCDIC

When replicating to or from a DB2 source system to a target that has a different character set, some consideration must be given to the encoding of the character data on the DB2 source if it contains a mix of ASCII and EBCDIC data. Character set conversion by any given Replicat requires source data to be in a single character set.

The source character set is specified in the trail header. Thus, the Oracle GoldenGate trail can contain either ASCII or EBCDIC data, but not both. Unicode tables are processed without any special configuration and are exempt from the one-character set requirement.

With respect to a source that contains both character encoding types, you have the following options:

• You can use one Extract for all of your tables, and have it write the character data to the trail as either ASCII or as EBCDIC.

• You can use different Extracts: one Extract to write the ASCII character data to a trail, and another Extract to write the EBCDIC character data to a different trail. You then associate each trail with its own data pump process and Replicat process, so that the two data streams are processed separately.

To output the correct character set in either of those scenarios, use the TRAILCHARSETASCII and TRAILCHARSETEBCDIC parameters. The default is TRAILCHARSETEBCDIC. Without these parameters, ASCII and EBCDIC data are written to the trail as-is. When using these parameters, note the following:

• If used on a single-byte DB2 subsystem, these parameters cause Extract to convert all of the character data to either the ASCII or EBCDIC single-byte CCSID of the subsystem to which Extract is connected, depending on which parameter is used (except for Unicode, which is processed as-is).

• If used on a multi-byte DB2 subsystem, these parameters cause Extract to capture only ASCII or EBCDIC tables (and Unicode). Character data is written in either the ASCII or EBCDIC mixed CCSID (depending on the parameter used) of the DB2 z/OS subsystem to which Extract is connected.

Specifying Multi-Byte Characters in Object Names

If the name of a schema, table, column, or stored procedure in a parameter file contains a multi-byte character, the name must be double-quoted. For more information about specifying object names, see Administering Oracle GoldenGate.