Requirements for Db2 for i

Capture	Hub	Integrate

This section describes the requirements, access privileges, and other features of HVR when using 'DB2 for i' for replication. For information about compatibility and supported versions of DB2 for i with HVR platforms, see Platform Compatibility Matrix.

For the Capabilities supported by HVR on DB2 for i, see Capabilities for DB2 for i.

For information about the supported data types and mapping of data types in source DBMS to the corresponding data types in target DBMS or file format, see Data Type Mapping.

ODBC Connection

HVR is not installed on the DB2 for i system itself but is instead installed on a Linux or Windows machine, from which it uses ODBC to connect to the DB2 for i system. HVR uses ODBC connection to read and write data to DB2 for i location.

The following are required for HVR to establish an ODBC connection to the DB2 for i system:

• On Linux:
  • IBM i Access Client Solutions ODBC Driver 64-bit
  • ODBC driver manager UnixODBC
• On Windows:
  • IBM i Access Client Solutions ODBC Driver

The IBM i Access Client Solutions ODBC Driver is available for download from IBM ESS Website (requires user authentication). Choose product-number '5770-SS1', and then choose package 'IBM i Access - Client Solutions' for your platform. For information about the supported ODBC driver version, refer to the HVR release notes (hvr.rel) available in hvr_home directory or the download page.

Firewall

If a Firewall is configured between the HVR capture machine and the IBM i-series, the following default ports need to be opened in order to be able to connect via ODBC from the capture to the IBM i-series:

The port numbers mentioned here are the default port numbers. To verify the default port numbers for the services names, use the command wrksrvtble on AS/400 console.

Location Connection

This section lists and describes the connection details required for creating DB2 for i location in HVR.

Hub

HVR allows you to create a hub database in DB2 for i. The hub database is a small database which HVR uses to control its replication activities. This database stores HVR catalog tables that hold all specifications of replication such as the names of the replicated databases, the list of replicated tables, and the replication direction.

Grants for Hub

To capture changes from a source database or to integrate changes into a target database, the following privileges are required:

• The User should have permission to create and drop HVR catalog tables

Grants

The User should have permissions to read the following system catalogs:

• qsys2.systables
• qsys2.syscolumns
• qsys2.systypes
• qsys2.syscst
• qsys2.syscstcol
• qsys2.sysindexes
• qsys2.syskeys
• sysibm.sysdummy1
• sysibm.sqlstatistics
• sysibmadm.system_value_info

According to IBM documentation, the tables and views in the catalogs are shipped with the SELECT privilege to PUBLIC. This privilege may be revoked and only the SELECT privilege is granted to individual users.

To grant the SELECT privilege on, for example, table columns in qsys2 schema, use the following statement:

grant select on qsys2.syscolumns to hvruser;

Capture

HVR supports capturing changes from DB2 for i location. This section describes the configuration requirements for capturing changes from DB2 for i location. For the list of supported DB2 for i versions, from which HVR can capture changes, see Capture changes from location in Capabilities.

Supported Object Types

HVR supports capturing from the following relational objects in Db2 for i:

• Tables
• Physical files
• Source files

Non-relational objects are not supported.

Log-Based Capture

HVR performs log-based capture from DB2 fo i location using the DISPLAY_JOURNAL table function.

• The user should have permission to select data from journal receivers. This can be achieved in two ways:

  1. Create a user profile (e.g. hvruser) and assign the special authority (*ALLOBJ). For this, run the following command from AS/400 console :

     CRTUSRPRF USRPRF(HVRUSER) SPCAUT(*ALLOBJ)
     

  2. If *ALLOBJ authority cannot be granted to the user (or if the user does not have *ALLOBJ authority), then separate access rights should be given on each journal. For this, run the following commands from AS/400 console.

     1. Create a user profile (e.g. hvruser) :

        CRTUSRPRF USRPRF(HVRUSER)
        

     2. Grant the authority *USE on object (e.g. HVR) to user :

        GRTOBJAUT OBJ(HVR) OBJTYPE(*LIB) USER(HVRUSER) AUT(*USE)
        

     3. Grant the authority *USE and *OBJEXIST on journal (e.g. HVR/QSQJRN) to user :

        GRTOBJAUT OBJ(HVR/QSQJRN) OBJTYPE(*JRN) USER(HVRUSER) AUT(*USE)
        
        GRTOBJAUT OBJ(HVR/QSQJRN) OBJTYPE(*JRN) USER(HVRUSER) AUT(*OBJEXIST)
        

     4. Grant the authority *USE on all journal receiver (e.g. HVR/*ALL) to user :

        GRTOBJAUT OBJ(HVR/*ALL) OBJTYPE(*JRNRCV) USER(HVRUSER) AUT(*USE)
        

• Tables grouped in the same HVR channel should be using the same journal (Capture /LogJournal)

• All changes made to the replicated tables should be fully written to the journal receivers

• IBM i Table attribute IMAGES should be set to *BOTH or *AFTER (supported only if ColumnProperties /CaptureFromRowId and /SurrogateKey are defined, since HVR 5.7.0/0)

• To enable these settings for each replicated table the journaling needs to be stopped and started again with the new settings. Example, for table TAB1_00001 in schema HVR:

  ENDJRNPF FILE(HVR/TAB1_00001) JRN(HVR/QSQJRN)
  STRJRNPF FILE(HVR/TAB1_00001) JRN(HVR/QSQJRN) IMAGES(*BOTH)
  

  or

  CHGJRNOBJ OBJ((HVR/*ALL *FILE)) ATR(*IMAGES) IMAGES(*BOTH)
  

• IBM i Journal attribute MINENTDTA should be set to *NONE

• IBM i Journal attribute RCVSIZOPT should contain either *MAXOPT3 or *MAXOPT2. When using *MAXOPT2, it is recommended to define action Capture /LogJournalSysSeq. Otherwise, if action Capture /LogJournalSysSeq is not defined and when the journal sequence numbers are reset, then HVR Initialize should be run with Transaction Files and Capture Time (option -or) to reset the capture start sequence, and if the target location is a database, also select option State Tables (option -os) to reset the target state tables. After executing HVR Initialize, optionally, run HVR Refresh to repair any changes from before the new capture start that were missed.

  Action Capture /LogJournalSysSeq requires FIXLENDTA to contain *SYSSEQ.

• The journal receivers should not be removed before HVR has been able to process the changes written in them.

• To enable these settings, run the following commands in the console. Example, for schema HVR running with *MAXOPT3:

  CHGJRN JRN(HVR/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT3)
  

• For running with *MAXOPT2 and Capture /LogJournalSysSeq:

  CHGJRN JRN(HVR/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT2) FIXLENDTA(*SYSSEQ)
  

• When Action Capture /IgnoreSessionName is used, the name of the user making a change should be logged. In that case, IBM i Journal attribute FIXLENDTA should contain *USR. Example, for schema HVR running with *MAXOPT3:

  CHGJRN JRN(HVR/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT3) FIXLENDTA(*USR)
  

• For running with *MAXOPT2 and Capture /LogJournalSysSeq:

  CHGJRN JRN(HVR/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT2) FIXLENDTA(*SYSSEQ *USR)
  

Supplemental Logging

To enable supplemental logging, the User should be either the owner of the replicated tables or have DBADM or SYSADM or SYSCTRL authority.

Table changes in DB2 for i are logged by journal receivers, which collect images of the table states. HVR supplemental logging requires *BOTH or *AFTER (supported only if ColumnProperties /CaptureFromRowId and /SurrogateKey are defined) to be selected when setting the required journal IMAGES attribute.

For HVR versions prior to 5.6.0/13 and 5.6.5/4, to simplify the process of setting supplemental imaging for capturing on DB2 for i, HVR provides two shell scripts (hvrsupplementalimage.qsh and suppl_log_template.qsh) available in hvr_home/lib/db2i directory. The script file hvrsupplementalimage.qsh must be installed/copied into the iSeries root directory on the DB2 for i machine where changes are captured. The script is invoked by the command HVR Initialize with Supplemental Logging parameter selected in the HVR GUI or using command hvrinit (option -ol). The script will turn on either *BOTH or *AFTER depending on the action ColumnProperties /CaptureFromRowId and /SurrogateKey defined. HVR Initialize will silently invoke this script via the SQL/QCMDEXC interface for all tables that must be captured. The script can return its exit code to the calling HVR Hub via SQL only. For that, HVR creates a table in schema HVR called hvr_supplementalimage_channel. If the hvruser does not have a table creation authority, then the hvr_config/files/suppl_log_sysdba.qsh script is created on the HVR Hub that can set all image settings without the need for table creation. The composite script is generated by inserting a list of schema table pairs into a template script (suppl_log_template.qsh) that is pulled from hvr_home/lib/db2i. The suppl_log_sysdba.qshell script may be transferred to the DB2 for i capture machine root directory and run there in QSHELL invoked by STRQSH command.

For HVR versions since 5.6.0/13 and 5.6.5/4, setting supplemental imaging for capturing on DB2 for i can be achieved without the need for external shell scripts. This method substantially speeds up initialization. However, HVR will create a table hvr_qshellio_channel in Db2i to report whether the journaling IMAGES attribute *BOTH or *AFTER is enabled/set. Note that the scripts (hvrsupplementalimage.qsh and suppl_log_template.qsh) remain dormant yet available to be used in a contingency.

The file hvrlogjournalpurge.qsh available in hvr_home/lib/db2i directory is a shell script for manually deleting the outdated journal receivers. This script file can be used when there is a storage space problem because of configuring the db2i system to not delete journal receivers automatically.

Integrate and Refresh Target

HVR supports integrating changes into DB2 for i location. This section describes the configuration requirements for integrating changes (using Integrate and refresh) into DB2 for i location. For the list of supported DB2 for i versions, into which HVR can integrate changes, see Integrate changes into location in Capabilities.

Pre-Requisites

• The current schema (default library) of User should exist

• Journaling should be enabled for the current schema (so that the table(s) created in the current schema are automatically journaled)

Grants to Integrate and Refresh Target

The User should have permission to read and change replicated tables

grant select, insert, update, delete on tbl to hvruser

Alternatively, run the following command from AS/400 console:

GRTOBJAUT OBJ(HVR/*ALL) OBJTYPE(*FILE) USER(HVRUSER) AUT(*CHANGE)

The User should have permission to use the current schema (default library) and to create and drop HVR state tables in it

grant createin, usage on schema current schema to hvruser

Alternatively, run the following command from AS/400 console:

  GRTOBJAUT OBJ(HVR) OBJTYPE(*CURLIB) USER(HVRUSER) AUT(*CHANGE)

Compare and Refresh Source

HVR supports compare and refresh (source location) into DB2 for i location. This section describes the configuration requirements for performing HVR Compare and HVR Refresh (source location) in DB2 for i location.

Grants for Compare and Refresh Source

The User should have permission to read replicated tables:

grant select on tbl to hvruser

Alternatively, run the following command from AS/400 console:

GRTOBJAUT OBJ(HVR/*ALL) OBJTYPE(*FILE) USER(HVRUSER) AUT(*USE)