Db2 for i as Source

Capture

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

Capture Methods

HVR allows only the Log Journal Capture method for capturing (Capture) changes from Db2 for i:

Log Journal Capture

In this capture method (Capture_Method=SQL), HVR captures changes from Db2 for i using the DISPLAY_JOURNAL table function.

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

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

Following are the capture configuration parameters available in the HVR UI for Db2 for i location:

• SCHEMA / LIBRARY

  Schema or library of the Db2 for i journal (specified in the field JOURNAL). It is mandatory to specify a value in this parameter when creating a Db2 for i capture location. This parameter in the HVR UI corresponds to the location property DB2i_Log_Journal_Schema.

• JOURNAL

  Name of the Db2 for i journal from which data changes will be captured. It is mandatory to specify a value in this parameter when creating a Db2 for i capture location.
  A channel can only contain tables that share the same journal. To capture changes from tables associated with different journals, use separate channels for each journal. This parameter in the HVR UI corresponds to the location property DB2i_Log_Journal.

• Journal *SYSSEQ

  Capture changes from the journal using *SYSSEQ. To use this field, a value must be specified in the JOURNAL field. This parameter in the HVR UI corresponds to the location property DB2i_Log_Journal_SysSeq.

  Since HVR versions 6.2.0/2 and 6.1.0/64, it is not required to define this field or location property for capturing changes from the journal.

• SAP Source

  Capture changes from a Db2 for i database that is used by an SAP ECC system. For more information, see section Capturing from SAP Source below.

Privileges/Permissions for Capture

This section lists the permissions required for capturing changes from Db2 for i.

• The HVR database user should be granted select privilege to read the following system catalogs:

  grant select on qsys2.syscolumns to username;
  grant select on qsys2.syscolumns2 to username;
  

  qsys2.syscolumns2 contains information that is not present in qsys2.syscolumns.

  grant select on qsys2.syscst to username;
  grant select on qsys2.syscstcol to username;
  grant select on qsys2.sysindexes to username;
  grant select on qsys2.syskeys to username;
  grant select on qsys2.systables to username;
  grant select on qsys2.systypes to username;
  grant select on sysibm.sqlstatistics to username;
  grant select on sysibm.sysdummy1 to username;
  

  The following grant returns information about the installed Db2 for i PTF (patches).

  grant select on qsys2.group_ptf_info to username;
  

  Querying this catalog can be disabled by setting the Environment variable HVR_DB2I_USE_GROUP_PTF_INFO=0. When disabled, HVR will not use object filtering for Db2 for i versions 7.3 and 7.4.

  The following grant returns information about the system (e.g. default character set).

  • For HVR versions since 6.1.0/59 and HVR 6.1.5/10, use the following grant:

    grant select on qsys2.system_value_info to username;
    

  • For HVR versions up to 6.1.0/59 and HVR 6.1.5/10, use the following grant:

    grant select on sysibmadm.system_value_info to username;
    

  Querying the system_value_info catalog can be disabled by setting the Environment variable HVR_DB2I_USE_SYSTEM_VALUE_INFO=0. When disabled, HVR will assume that the default character set is EBCDIC 37.

  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 the select privilege granted to individual users.

• The HVR database user should have permissions to create files in QTEMP library:

  HVR requires this permission when collecting DATE and TIME information about physical files and the qsys2.syscolumns2 does not contain the DATE_FORMAT, DATE_SEPARATOR, TIME_FORMAT, and TIME_SEPARATOR columns. Using OUTFILE in QTEMP can be disabled by setting the environment variable HVR_DB2I_USE_QTEMP=0.

  • To create an OUTFILE, the HVR database user must have the *USE authority to the Create Physical File (CRTPF) command and *ADD authority to the library. To use an existing OUTFILE, the HVR database user must have object operational (*OBJOPR) and *ADD authority to the file.

  • The HVR database user must have object management (*OBJMGT) and delete (*DLT) authority if *REPLACE is specified for the OUTFILE member and the file member already exists.

  • The HVR database user must have the *OBJOPR authority for the file specified in the FILE parameter. In addition, among the libraries specified by the library qualifier, only the libraries for which the HVR database user has execute (*EXECUTE) authority are searched.

• The HVR database user should have permission to select data from journal receivers. This can be achieved by performing either of the following two methods:

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

  • If *ALLOBJ authority cannot be granted to the HVR database 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) for the HVR database user:

       CRTUSRPRF USRPRF(HVRUSER)
       

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

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

    3. Grant the authority *USE and *OBJEXIST on journal (e.g. HVR/QSQJRN) to the HVR database 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 the HVR database user:

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

• IBM i Table attribute IMAGES should be set to *BOTH or *AFTER

  • 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 attributes MINENTDTA and RCVSIZOPT should be set as follows:

  • Attribute MINENTDTA should be set to *NONE

  • Attribute RCVSIZOPT should be set to *MAXOPT3 or *MAXOPT2.

    • For HVR versions up to 6.2.0/1 and 6.1.0/63, when using *MAXOPT2, it is recommended to define the location property Journal with *SYSSEQ (DB2i_Log_Journal_SysSeq).
      If it is not defined and the journal sequence numbers are reset, you must run Activate with option Capture Time and Transaction Files (hvractivate -or) to reset the capture start sequence. Additionally, if the target location is a database, select option State Tables (hvractivate -os) to reset the target state tables. After executing Activate Replication, you can optionally run Refresh Data (hvrrefresh) to repair any changes from before the new capture start that were missed.
    • Location property Journal with *SYSSEQ (DB2i_Log_Journal_SysSeq) requires journal attribute FIXLENDTA to contain *SYSSEQ.
    • Action Capture with parameter IgnoreSessionName requires journal attribute FIXLENDTA to contain *USR.

  To apply these settings, execute one of the following commands in the console, depending on whether you are using *MAXOPT3 or *MAXOPT2:

  • Example: Schema HVR running with *MAXOPT3.

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

  • Example: Schema HVR running with *MAXOPT3 and action Capture with parameter IgnoreSessionName is defined.
    The IBM i journal attribute FIXLENDTA should contain *USR (to log the name of the user making change).

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

  • Example: Schema HVR running with *MAXOPT2.
    For HVR versions since 6.2.0/2 and 6.1.0/64.

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

    For HVR versions up to 6.2.0/1 and 6.1.0/63, the location property Journal with *SYSSEQ (DB2i_Log_Journal_SysSeq) should be defined and the IBM i journal attribute FIXLENDTA should contain *SYSSEQ.

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

  • Example: Schema HVR running with *MAXOPT2 and action Capture with parameter IgnoreSessionName is defined.
    For HVR versions since 6.2.0/2 and 6.1.0/64, the IBM i journal attribute FIXLENDTA should contain *USR.

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

    For HVR versions up to 6.2.0/1 and 6.1.0/63, the location property Journal with *SYSSEQ (DB2i_Log_Journal_SysSeq) should be defined and the IBM i journal attribute FIXLENDTA should contain both *SYSSEQ and *USR.

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

Supplemental Logging

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 action ColumnProperties is defined with parameters CaptureFromRowId and SurrogateKey) to be selected when setting the required journal image attribute.

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

HVR provides a shell script hvrsupplementalimage.qsh to simplify the process of setting supplemental imaging for capturing from Db2 for i. The script needs to be installed on the Db2 for i machine where changes are captured.

To install the script, copy the HVR_HOME/dbms/db2i/hvrsupplementalimage.qsh file to the iSeries root directory. The script is invoked when Activate Replication (hvractivate) is run with option Supplemental Logging (option -ol). The script will turn on either *BOTH or *AFTER depending on the action ColumnProperties defined with parameters CaptureFromRowId and SurrogateKey.

Activate Replication will silently invoke the hvrsupplementalimage.qsh 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 HVR database user does not have a table creation authority, then the suppl_log_sysdba.qsh script file is created in the HVR_CONFIG/hubs/hub/channels/channel_name/locs/location_name/initsql directory on the HVR Hub, which 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 that is pulled from HVR_HOME/dbms/db2i. Transfer the suppl_log_sysdba.qsh script to the root directory of the Db2 for i capture machine and execute it there using QSHELL invoked by the STRQSH command.

Capturing from SAP Source

HVR allows you to capture changes from a Db2 for i database which is used by an SAP ECC system. To enable capture using SAP dictionary, the location property SAP Source (SAP_Source_Schema) must be defined while creating a location or by editing the existing location's source and target properties. Then while adding tables to a channel, the Table Selection dialog will display the SAP tables defined in the SAP dictionaries.

For Usage-based Subscription, an additional SAP Unpack license is required to unpack the cluster and pool tables from the SAP database. Contact Fivetran Technical Support to obtain the necessary SAP Unpack license. For the Consumption-based model, a separate license is NOT required.

When SAP pool, cluster, and long text (STXL) tables are added to a channel using the Table Selection dialog, the following actions are automatically defined:

• TableProperties with parameters PackedInside, CoerceErrorPolicy, and CoerceErrorType

  • For each container (pool/cluster) table a separate action TableProperties is defined.
  • This action is not defined for long text (STXL) tables.

• Transform with parameter SapUnpack

  Irrespective of the number of tables, only a single action Transform is defined.

SAP columns are non-nullable by default. They will be described as nullable in the HVR Hub's repository and thus as nullable in the target. This is valid for the non-key columns. Key columns will remain non-nullable.

SAP Data Types Conversion

Since v6.1.0/7

This option enables conversion/mapping of SAP specific data types (available in SAP dictionary meta-data) in source location to corresponding data type in the target location. The SAP specific data type will be localized with the source DB's data type and then mapped to HVR's Repository data type. For example, if you have an SAP system on Db2 for i, the DATS data type will be localized as Db2 for i Date type, and then it is mapped to HVR Repository type ansidate.

This feature is supported for the following SAP specific data types:

• DATS

If the SAP Data Types Conversion option is NOT selected, SAP specific data types are mapped to various other HVR Repositry data types. For more information, see Data Type Mapping for SAP NetWeaver (or SAP dictionary).

If the SAP Data Types Conversion option is selected during location creation, HVR will automatically define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

If the SAP Source (SAP_Source_Schema) location property/option is selected during location creation, by default, the SAP Data Types Conversion option also gets selected.

However, to enable SAP data type conversion for an existing location, select the SAP Data Types Conversion option by editing the location's source and target properties and then manually define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

Capturing from Members

By default, HVR captures changes from all members.

Since version 6.2.0/2, HVR allows you to capture changes only from the first member. To enable this, you must define the environment variable HVR_DB2I_MULTI_MEMBER_ALL. You can define this environment variable using the action Environment with parameters Name=HVR_DB2I_MULTI_MEMBER_ALL and Value=0. However, if a table in the HVR channel is dropped from the database, you must also delete it from the HVR channel. Failing to do so will result in the error F_JD22D3: DBMS error [SQL0443 - Member *FIRST not found in file filename in libraryname].

Compare and Refresh from Db2 for i

HVR allows you to perform only Compare and Refresh from Db2 for i database (without using Capture). This section describes the configuration requirements for performing only Compare and Refresh from Db2 for i database.

Grants for Compare and Refresh from Db2 for i

This section lists the grants required for performing only Compare and Refresh from Db2 for i database.

• The HVR database user must be granted the following privilege to read from the Db2 for i database:

  grant select on tbl to user hvruser 
  

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

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