Requirements for DB2 for i
This section describes the requirements, access privileges, and other features of HVR when using 'DB2 for i' for replication. For information about the capabilities supported by HVR on DB2 for i, see Capabilities for DB2 for i.
For information about compatibility and supported versions of DB2 for i with HVR platforms, see Platform Compatibility Matrix.
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. The following are required for HVR to establish an ODBC connection to the DB2 for i system:
- IBM i Access Client Solutions ODBC Driver 64-bit
- ODBC driver manager UnixODBC 2.3.1
- IBM i Access Client Solutions ODBC Driver 13.64.11.00
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.
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:
|PC Function||Service name i-series||Port non-SSL||SSL Port|
|RPC/DPC (Remote command)||as-rmtcmd||8475||9475|
This section lists and describes the connection details required for creating DB2 for i location in HVR.
|System||The hostname or ip-address of the DB2 for i system. |
|Named Database||The named database in DB2 for i. It could be on another (independent) auxiliary storage pool (IASP). The user-profile's default setting will be used when no value is specified. Specifying *SYSBAS will connect a user to the SYSBAS database.|
|User||The username to connect HVR to the Named Database in DB2 for i. |
|Password||The password of the User to connect HVR to the Named Database in DB2 for i.|
|Driver Manager Library||The optional directory path where the ODBC Driver Manager Library is installed. For a default installation, the ODBC Driver Manager Library is available at /usr/lib64 and does not need to specified. When UnixODBC is installed in for example /opt/unixodbc-2.3.1 this would be /opt/unixodbc-2.3.1/lib. |
|ODBCSYSINI||The directory path where odbc.ini and odbcinst.ini files are located. For a default installation, these files are available at /etc and does not need to be specified. When UnixODBC is installed in for example /opt/unixodbc-2.3.1 this would be /opt/unixodbc-2.3.1/etc. The odbcinst.ini file should contain information about the IBM i Access Client Solutions ODBC Driver under the heading [IBM i Access ODBC Driver 64-bit] |
|ODBC Driver||The user defined (installed) ODBC driver to connect HVR to the DB2 for i system.|
HVR allows you to create 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 source database or to integrate changes into target database, the following privileges are required:
- The User should have permission to create and drop HVR catalog tables
- The User should have permission to read the DB2 for i system catalogs
grant select on system_catalog to hvruser
HVR supports capturing changes from DB2 for i.
Log based Capture
- The User should have permission to select data from journal receivers
select x.* from table ( display_journal (...) ) AS x
- 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
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)
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 use Action Capture /LogJournalSysSeq otherwise, when journal sequence numbers are reset, hvrinit should be run to reset the capture start sequence and, optionally, hvrrefresh could be used 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. E.g. for schema HVR running with *MAXOPT3:
CHGJRN JRN(HVR/JRN0001) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT3)
For running with *MAXOPT2 and Capture /LogJournalSysSeq:
CHGJRN JRN(HVR/JRN0001) 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
E.g. for schema HVR running with *MAXOPT3:
CHGJRN JRN(HVR/JRN0001) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT3) FIXLENDTA(*USR)
For running with *MAXOPT2 and Capture /LogJournalSysSeq:
CHGJRN JRN(HVR/JRN0001) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT2) FIXLENDTA(*SYSSEQ *USR)
Grants for Compare and Refresh Source
- The User should have permission to read replicated tables
grant select on tbl to hvruser
Grants for Integrate and Refresh Target
- The User should have permission to read and change replicated tables
grant select, insert, update, delete on tbl to hvruser
- The User should have permission to create and drop HVR state tables
- The current schema of User should have journaling enabled and tables created in that schema should automatically be journaled