1 System Requirements and Preinstallation Instructions

This chapter contains the requirements for the system and database resources that support Oracle GoldenGate.

This chapter contains the following sections:

1.1 Overview of Oracle GoldenGate for IBM DB2 for i

With Oracle GoldenGate for IBM DB2 for i, you can replicate data to and from similar or dissimilar supported DB2 for i versions, or you can replicate data between a DB2 for i database and a database of another type. Oracle GoldenGate for DB2 for i supports the filtering, mapping, and transformation of data unless otherwise noted in this documentation.

Oracle GoldenGate for DB2 for i runs directly on a DB2 for i source system to capture data from the transaction journals for replication to a target system. To apply data to a target DB2 for i database, Oracle GoldenGate can run directly on the DB2 for i target system or on a remote Windows or Linux system. If installed on a remote system, Replicat delivers the data by means of an ODBC connection, and no Oracle GoldenGate software is installed on the DB2 for i target.

Note:

The DB2 for i platform uses one or more journals to keep a record of transaction change data. For consistency of terminology in the supporting administrative and reference Oracle GoldenGate documentation, the terms "log" or "transaction log" may be used interchangeably with the term "journal" where the use of the term "journal" is not explicitly required. For a list of other Oracle GoldenGate documentation, see "Related Documents"

1.2 Verifying Certification and System Requirements

Make sure that you are installing your product on a supported hardware or software configuration. For more information, see the certification document for your release on the Oracle Fusion Middleware Supported System Configurations page.

Oracle has tested and verified the performance of your product on all certified systems and environments; whenever new certifications occur, they are added to the proper certification document right away. New certifications can occur at any time, and for this reason the certification documents are kept outside of the documentation libraries and are available on Oracle Technology Network.

1.3 Operating System Requirements

This section outlines the requirements to support Oracle GoldenGate on the local operating system.

1.3.1 General Requirements

  • Portable Application Solution Environment (PASE) must be installed on the system.

  • OpenSSH is recommended to be installed on the system. OpenSSH is part of the IBM Portable Utilities licensed program and allows SSH terminal access to the system in the same manner as other Linux system.

  • The IBM DB2 for i Program temporary fixes (PTFs) that are required by release for Oracle GoldenGate are detailed in the following tables:

    IBM i7.1 Group PTF Level Name Notes
    SF99710 13037 Cumulative PTF Other required PTF: 5770SS1, SI51060

    Check with command: DSPPTF LICPGM(5770SS1) SELECT(SI51060)

    SF99701 26 DB2 for i  
    SF99709 99 Group HIPER  
    SF99367 7 TCP/IP  
    SF99572 12 JAVA Java agent requires product 5761JV1 option 12 (Java SE 6 64-bit)

    IBM i6.1 Group PTF Level Name Notes
    SF99610 13058 Cumulative PTF Other required PTF: 5761SS1, SI51061Check with command: DSPPTF LICPGM(5761SS1) SELECT(SI51061)
    SF99601 30 DB2 for i  
    SF99609 153 Group HIPER  
    SF99354 15 TCP/IP  
    SF99562 24 JAVA Java agent requires product 5761JV1 option 12 (Java SE 6 64-bit)

    IBM OS/400 V5R4 Group PTF Level Name Notes
    SF99540 12094 Cumulative PTF Other required PTF: 5722SS1, SI50721,

    Check with command: DSPPTF LICPGM(5722SS1) SELECT(SI50721)

    If using the DB2 for i native command interface for OGG PASE_MAXSHR64 is set automatically so this message does not occur in that case

    SF99504 33 DB2 for i  
    SF99539 198 Group HIPER  
    SF99315 22 TCP/IP  
    SF99291 33 JAVA Java agent requires product 5761JV1 option 10 (Java Developer Kit)

    These required PTFs are the levels at which Oracle GoldenGate has been tested against for the 11g R2 and 12c releases. In order to check the group PTF levels, you must use the WRKPTFGRP command from a 5250 terminal session and check for the specific PTFs with the commands shown in the preceding tables. The specific extra PTFs must be at least temporarily applied.

1.3.2 Memory Requirements

The amount of memory that is required for Oracle GoldenGate depends on the amount of data being processed, the number of Oracle GoldenGate processes running, the amount of main storage (RAM, or physical memory) available to Oracle GoldenGate, and the amount of auxiliary storage (disk space, available as shared memory segments) that is available to Oracle GoldenGate for caching transaction data that exceeds available physical memory.

The amount of main storage that is used by Oracle GoldenGate is controlled by the operating system, not the Oracle GoldenGate processes. The Oracle GoldenGate cache manager takes advantage of the memory management functions of the operating system to ensure that the Oracle GoldenGate processes work in a sustained and efficient manner.

On the DB2 for i platform, to provide enough shared memory segments to the Oracle GoldenGate cache manager, the recommended setting for the PASE_MAXSHR64 environment variable is a value of 513 (128GB) or higher. If you use the DB2 for i native Oracle GoldenGate commands, PASE_MAXSHR64 is set to provide 128GB of shared memory segments to the cache manager automatically. If not using the DB2 for i native commands, you can set this environment variable before starting the DB2 for i PASE session. For more information about evaluating Oracle GoldenGate memory requirements, see the CACHEMGR parameter in Reference for Oracle GoldenGate for Windows and UNIX.

Note:

If PASE_MAXSHR64 is not set, you may encounter a warning message stating that the virtual memory is less than the recommended amount. Unless you have very large long-running transactions or a very large number of concurrent transactions, you may ignore this message.

1.3.3 Disk Requirements

This section outlines the disk requirements for Oracle GoldenGate.

  • To determine the size of the Oracle GoldenGate download file, view the Size column before downloading your selected build from Oracle Software Delivery Cloud. The value shown is the size of the files in compressed form. The size of the expanded Oracle GoldenGate installation directory will be significantly larger on disk. For more information, see Section 2.2, "Understanding and Obtaining the Oracle GoldenGate Distribution"

  • Allow sufficient disk space for virtual memory. The default set by the Oracle GoldenGate cache manager is 64 GB on 64-bit systems. See Section 1.3.2, "Memory Requirements" for additional information about memory management.

  • An additional 1 GB of disk space on any system that hosts Oracle GoldenGate trails, which are files that contain the working data. You may need more or less than this amount, because the space that is consumed by the trails depends on the volume of data that will be processed. See the guidelines for sizing trails in Administering Oracle GoldenGate for Windows and UNIX.

1.3.4 Network Requirements

Oracle GoldenGate requires the following network resources.

  • Configure the system to use TCP/IP services, including DNS. Oracle GoldenGate supports IPv4 and IPv6 and can operate in a system that supports one or both of these protocols.

  • Configure the network with the host names or IP addresses of all systems that will be hosting Oracle GoldenGate processes and to which Oracle GoldenGate will be connecting. Host names are easier to use.

  • Oracle GoldenGate requires some unreserved and unrestricted TCP/IP ports, the number of which depends on the number and types of processes in your configuration. See the Administering Oracle GoldenGate for Windows and UNIX for details on how to configure the Manager process to handle the required ports.

  • Keep a record of the ports that you assigned to Oracle GoldenGate. You will specify them with parameters when configuring the Manager process.

  • Configure your firewalls to accept connections through the Oracle GoldenGate ports.

1.3.5 Oracle GoldenGate Security Privileges

This section outlines the security privileges that Oracle GoldenGate requires on a source DB2 for i system and on a Windows or Linux target system.

1.3.5.1 Oracle GoldenGate Security Privileges on an DB2 for i Source System

The person who installs Oracle GoldenGate must have read and write privileges on the Oracle GoldenGate installation directory, because steps will be performed to create some sub-folders and run some programs. This person also must have authority to the RSTOBJ command, plus the ability to create a library if desired. For ease of installation, it is recommended that the user installing the product has *ALLOBJ authority

On an DB2 for i source system, the Manager and Extract processes are active. The DEFGEN utility also may be active if you are replicating data to a dissimilar target system. On an DB2 for i target system, the Replicat process is active unless you install Replicat on a remote Windows or Linux system. All processes run on both systems in a bidirectional configuration.

The Oracle GoldenGate processes must be assigned a user profile account that is dedicated to Oracle GoldenGate and cannot be used by any other program. One user profile can be used by all of the Oracle GoldenGate processes. This profile need only be granted permission to the objects that Oracle GoldenGate will be operating upon. If specific change data is not to be seen by Oracle GoldenGate, do not include it in any of the journals that the Oracle GoldenGate user profile is allowed to access. All Oracle GoldenGate processes must have privileges to read, write, and delete files and directories within the Oracle GoldenGate installation directory.

The Manager process must have privileges to control all other Oracle GoldenGate processes (DB2 for i *JOBCTL authority).

Assign *USE authority to all objects on the system that the Extract user profile must have access to. Assign *CHANGE authority to all objects on the system that the Replicat user profile must have access to. This can be accomplished by either granting *ALLOBJ authority to the user, or by setting the individual authority to the objects (FILE, LIBRARY and JOURNAL objects) that the user must access. This includes the objects in the QSYS2 library where the SQL catalog resides. These authorities must be granted through the native DB2 for i interface through a 5250 terminal session or through the DB2 for i Operations Navigator product available from IBM.

The Extract and Replicat database user profiles must be specified with the USERID parameter when you configure the parameter files (see Chapter 4, "Configuring Oracle GoldenGate for DB2 for i") and in the DBLOGIN command prior to issuing any GGSCI commands that interact with the database.

1.3.5.2 Oracle GoldenGate Security Privileges on a Windows or Linux System

The person who installs Oracle GoldenGate must have read and write privileges on the Oracle GoldenGate installation directory, because steps will be performed to create some sub-folders and run some programs. On Windows, the person who installs Oracle GoldenGate must log in as Administrator.

On a Windows or Linux system, Manager, Replicat, and Collector (program name is "server") are active. Manager controls the other processes and interacts with Collector to receive incoming data, while Replicat applies data to the target DB2 for i database through ODBC.

Oracle GoldenGate processes must be assigned a user account that is dedicated to Oracle GoldenGate and cannot be used by any other program. One user account can be used by all of the Oracle GoldenGate processes. This account must have privileges to read, write, and delete files and directories within the Oracle GoldenGate installation directory.

If the Extract user profile does not have the required authority, Extract will log the following errors and stop.

[SC=-1224:SQL1224N A database agent could not be started to service a request, or was terminated as a result of a database system shutdown or a force command.SQL STATE 55032: The CONNECT statement is invalid, because the database manager was stopped after this application was started]

The user profile must be specified with the USERID parameter when you configure the parameter files (see Chapter 4, "Configuring Oracle GoldenGate for DB2 for i") and in the DBLOGIN command prior to issuing any GGSCI commands that interact with the database.

1.4 Supported DB2 for i Data Types

Oracle GoldenGate supports all DB2 for i data types, except those listed in Non-Supported DB2 for i Data Types.

Limitations of support

Extract fully supports the capture and apply of TIMESTAMP(0) through TIMESTAMP(6). Extract also captures TIMESTAMP(7) through TIMESTAMP(12), but it truncates the data to microseconds (maximum of six digits of fractional time) and issues a warning to the error log. Replicat truncates timestamp data from other sources to microseconds when applying it to TIMESTAMP(7) through TIMESTAMP(12) in a DB2 for i target.

Oracle GoldenGate supports timestamp data from 0001/01/03:00:00:00.000000 to 9999/12/31:23:59:59.999999. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the time zone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit.

1.5 Non-Supported DB2 for i Data Types

Oracle GoldenGate does not support the following IBM for i data types.

  • XML

  • DATALINK

  • DECFLOAT

  • User-defined types

1.6 Supported Objects and Operations for DB2 for i

Oracle GoldenGate supports the following DB2 for i objects and operations.

  • Only the default DB2 for i database that is identified by the system name (in upper case) is supported. Named databases on other independent auxiliary storage pools (IASPs) are not supported.

  • Extraction and replication of DML operations .

  • Tables with the maximum row length supported by the database.

  • Tables that contain up to the maximum number of columns that is supported by the database, up to the maximum supported column size.

  • DELETE FROM with no WHERE clause SQL statements and Clear Physical File Member (CLRPFM)

  • Base tables underlying Materialized Query Tables, but not the MQTs themselves. The target database automatically maintains the content of the MQT based on the changes that are applied to the base tables by Replicat.

  • Both Library (Native) names including members, and SQL names are allowed.

  • Partitioned tables

  • Limitations on Automatic Heartbeat Table support are as follows:

    • The ADD HEARTBEATTABLE command creates a new file called ogghbfreq¿ in the Oracle GoldenGate build folder. Do not delete this file because the pase heartbeat program reads the frequency values from it.

    • There is an extra executable in the Oracle GoldenGate build folder named ogghb.

    • An extra process named ogghb starts running from the time the ADD HEARTBEATTABLE command is given and runs until you disable the heartbeat with the DELETE HEARTBEATTABLE command. This process automatically restarts even if it is killed. To remove this process from the system, use the DELETE HEARTBEATTABLE command.

    • When using the ALTER HEARTBEATTABLE command to change the heartbeat frequency with the PURGE_FREQUENCY or RETENTION_TIME options, it takes approximately 60 + older 'frequency') seconds to be implemented.

    • There is an initial delay of 30 seconds between ADD HEARTBEATTABLE and the first record is updated in the heartbeat seed table.

1.7 Non-Supported Objects and Operations for DB2 for i

Oracle GoldenGate does not support the following objects or operations for DB2 for i.

  • DDL operations

  • Schema, table or column names that have trailing spaces.

  • Multiple instances of a database

  • The Multi-Journal feature does not support multi-journal sync of a transaction across multiple journals.

1.8 Oracle GoldenGate Parameters Not Supported for DB2 for i

This section lists some of the Oracle GoldenGate configuration parameters that are not supported for the DB2 for i platform. For full descriptions of Oracle GoldenGate parameters and the databases they support, see Reference for Oracle GoldenGate for Windows and UNIX.


BATCHSQL (not supported on V5R4 and i6.1 only)
BR
ASCIITOEBCDIC and EBCDICTOASCII
BINARYCHARS
LOBMEMORY
TRAILCHARSETEBCDIC
Any encryption options that use AES encryption

1.9 Supported Object Naming Conventions

Oracle GoldenGate supports SQL naming conventions and also supports native file system names in the format of library/file(member).

For native (system) names, Oracle GoldenGate supports the normal DB2 for i naming rules for wildcarding, which allows *ALL or a partial name with a trailing asterisk (*) wildcard. For example:

  • library/*all(*all)

  • library/a*(a*)

  • library/abcde*

Oracle GoldenGate does not allow wildcarding for library names.

The member name is optional and may be left off. In that case, data for all of the members will be extracted, but only the library and file names will be captured and included in the records that are written to the trail. The result is that the data will appear to have come from only one member on the source, and you should be aware that this could cause integrity conflicts on the target if there are duplicate keys across members. To include the member name in the trail records, include the member explicitly or though a wildcarded member specification.

For SQL names, only the first member in the underlying native file is extracted in accordance with the normal operation of SQL on an DB2 for i system. For SQL names, Oracle GoldenGate supports the wildcarding of table names, but not schema names. For instructions on wildcarding SQL names, see Administering Oracle GoldenGate for Windows and UNIX.

1.10 Supported Character Sets

In all prior releases of the DB2 for i, Extract all text data was converted to Unicode. Either UTF-8 for single and multi-byte CCSIDs or UTF-16 for double byte CCSIDs. For SQL, that means all non-binary CHAR, VARCHAR and CLOB data would be converted to UTF-8 and all GRAPHIC, VARGRAPHIC and DBCLOB data would be converted to UTF-16 for the trail, either for initial loads or "normal" extracts. This is still true if using a trail format in Extract that is lower than 12.2.

The behavior of defgen prior to version 12.2 for versions that had a column charset column, was to indicate -1 which represented that the column characters set was the default for columns that used character sets. The trail header would then be used to indicate the character set for the column data which was always set to UTF-8/UTF16.

Starting with Oracle GoldenGate Version 12.2 the default behavior has changed to allow character sets that are supported by Oracle GoldenGate conversions to pass through unchanged. This change automatically will reduce the CPU consumption and increase the throughput rate of extracts proportional to the amount of text data in the records being processed. If a CCSID is found in the extract that Oracle GoldenGate cannot convert, the extract will default to its original behavior for that column and convert the text data to the appropriate Unicode charset as it did in prior releases. This ensures that any replicat that processes a trail from the new extract is capable of handling the text data from the extract.

If the original behavior of the IBM i extract is desired then the keyword TRAILCHARSETUNICODE must be added to the extract prm file. This will cause all text data to be converted to unicode as it was in the prior releases of Oracle GoldenGate for DB2 for i. ALternatively it is possible to selectively revert to the old conversion behavior at the specific object or even column level by using the keyword COLCHARSET on the table definition.

Examples in a typical prm file for a "normal" (non-initial load) extract:

TRAILCHARSETUNICODE

- all text data in objects included in all table statements will be converted to Unicode

- if this keyword is not included the extract will not convert text data by default

.

table <schema>.<table>, COLCHARSET(ALL, UTF-8)

or

table <schema>.<table>, charset(UTF-8)

- all text data in objects that match this specific table statement will be converted to Unicode (double byte columns will be UTF-16)

.

table <schema>.<table>, COLCHARSET(TXTCOL4, UTF-8)

- only TXTCOL4 will be converted to UTF-8 all other text data will pass through unchanged

.

Note:

Due to how the IBM for i PASE database layer functions as well as Oracle GoldenGate's internal processing, there are certain situations where Unicode conversion is still required.

.

- Initial Load extracts will automatically convert all data to Unicode and indicate Unicode data in the columns]

- Extracts that use a trail format that is prior to the Oracle GoldenGate 12.2, the extracts will automatically fall back to converting text data to Unicode

- table specifications that include any column functions, or SQLEXEC, FETCHCOLUMNS or FETCHMODCOLS will require that either TRAILCHARSETUNICODE is specified or the specific tables or columns are changed to include the COLCHARSET modifier. This is true if replicat is using column functions as well. In that case the extract table(s) that map to the replicat tables must be sent as Unicode.

.

For defgen TRAILCHARSETUNICODE is not supported since defgen does not generate a trail, but if you are using TRAILCHARSETUNICODE or overriding the column charset(s) and are using a defs file on replicat then defgen must also specify the equivalent column charset overrides:

.

table <schema>.<table>, COLCHARSET(ALL, UTF-8)

or

table <schema>.<table>, charset(UTF-8)

- all text data in objects that match this specific table statement will be converted to Unicode (double byte columns will be UTF-16)

.

table <schema>.<table>, COLCHARSET(TXTCOL4, UTF-8)

- only TXTCOL4 will be converted to UTF-8 all other text data will pass through unchanged

Also note that in Oracle GoldenGate version 12.2, the default behavior is to have column metadata included in the trail data inline with the operation data as required. This means that definitions files are no longer needed by Oracle GoldenGate replication and will be ignored.

If however the Oracle GoldenGate Replicats or pumps indicate that they choose to override this behavior and use the definitions files, or a prior trail format level is used, the defgen definitions files will need to be recreated. In this case, care must be taken to ensure that the definitions match what extract is writing to the trails. Therefore any TRAILCHARSETUNICODE keywords, CHARSET or COLCHARSET modifiers that exist in the extract prm file must also exist in the defgen prm file that matches the extract.

ASSUMETARGETDEFS OVERRIDE in Replicat also requires that the extract use TRAILCHARSETUNICODE or the equivalent COLCHARSET modifiers on the tables in the extract parameter file. This is due to replicat still connecting to the database with a Unicode connection and therefore internally currently treats all text fields as Unicode.