Requirements for DB2 for i

From HVR
Jump to: navigation, search

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.

DB2 for i
Capture Hub Integrate
Icon-Yes.png Icon-Yes.png Icon-Yes.png

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. The following are required for HVR to establish an ODBC connection to the DB2 for i system:

Linux
  • IBM i Access Client Solutions ODBC Driver 64-bit
  • ODBC driver manager UnixODBC 2.3.1
Windows
  • 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.

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:

PC Function Service name i-series Port non-SSL SSL Port
Server mapper as-svrmap 449 449
License Management as-central 8470 9470
RPC/DPC (Remote command) as-rmtcmd 8475 9475
Sign-On Verification as-signon 8476 9476
Database Access as-database 8471 9471
Note: 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 your AS400 console.

Location Connection

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

SC-Hvr-Location db2i.png
Field Description
Database Connection
System The hostname or ip-address of the DB2 for i system.
  Example: 192.168.1.135
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.
  Example: hvruser
Password The password of the User to connect HVR to the Named Database in DB2 for i.
Linux
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.
  Example: /opt/unixdbx-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]
  Example: /opt/unixdbx-2.3.1/etc
ODBC Driver The user defined (installed) ODBC driver to connect HVR to the DB2 for i system.


Hub

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

Grants

  • The User should have permission to read the DB2 for i system catalogs
grant select on system_catalog 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.

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. Example, 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. Example, 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)

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.

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 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 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

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