grant select on qsys2.journal_receiver_info to <em>username</em>;
grant select on qsys2.syscst to <em>username</em>;
grant select on qsys2.syscstcol to <em>username</em>;
grant select on qsys2.sysindexes to <em>username</em>;
grant select on qsys2.syskeys to <em>username</em>;
grant select on qsys2.systables to <em>username</em>;
grant select on qsys2.systypes to <em>username</em>;
grant select on sysibm.sqlstatistics to <em>username</em>;
grant select on sysibm.sysdummy1 to <em>username</em>;

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

grant select on qsys2.group_ptf_info to <em>username</em>;

The following grant returns information about the system (e.g. default character set). - For DSS versions since 6.1.0/59 and DSS 6.1.5/10, use the following grant: shell grant select on qsys2.system_value_info to <em>username</em>; - For DSS versions up to 6.1.0/59 and DSS 6.1.5/10, use the following grant: shell grant select on sysibmadm.system_value_info to <em>username</em>;

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

DSS 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 DSS_DB2I_USE_QTEMP=0.

• To create an OUTFILE, the DSS 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 DSS database user must have object operational (*OBJOPR) and *ADD authority to the file.

• The DSS 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 DSS 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 DSS database user has execute (*EXECUTE) authority are searched.

• The DSS 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. DSSUSER) and assign the special authority (*ALLOBJ). For this, run the following command from AS/400 console :

    CRTUSRPRF USRPRF(<em>DSSUSER</em>) SPCAUT(*ALLOBJ)
    

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

       CRTUSRPRF USRPRF(<em>DSSUSER</em>)
       

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

       GRTOBJAUT OBJ(<em>DSS</em>) OBJTYPE(*LIB) USER(<em>DSSUSER</em>) AUT(*USE)
       

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

       GRTOBJAUT OBJ(<em>DSS</em>/QSQJRN) OBJTYPE(*JRN) USER(<em>DSSUSER</em>) AUT(*USE)
       
       GRTOBJAUT OBJ(<em>DSS</em>/QSQJRN) OBJTYPE(*JRN) USER(<em>DSSUSER</em>) AUT(*OBJEXIST)
       

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

       GRTOBJAUT OBJ(<em>DSS</em>/*ALL) OBJTYPE(*JRNRCV) USER(<em>DSSUSER</em>) 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 DSS:

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

    or

    CHGJRNOBJ OBJ((DSS/*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 DSS 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 (dssactivate -or) to reset the capture start sequence. Additionally, if the target location is a database, select option State Tables (dssactivate -os) to reset the target state tables. After executing Activate Replication, you can optionally run Refresh Data (dssrefresh) 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 *DSS* running with **\*MAXOPT3**.

    ```shell
    CHGJRN JRN(DSS/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT3)
    ```

-   Example: Schema *DSS* running with **\*MAXOPT3** and action [**Capture**](/docs/dss6-action-reference-capture) with parameter [**IgnoreSessionName**](/docs/dss6-action-reference-capture#ignoresessionnames) is defined.  
    The IBM i journal attribute **FIXLENDTA** should contain **\*USR** (to log the name of the user making change).

    ```shell
    CHGJRN JRN(DSS/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT3) FIXLENDTA(*USR)
    ```

-   Example: Schema *DSS* running with **\*MAXOPT2**.  
    For DSS versions since 6.2.0/2 and 6.1.0/64.
    ```shell
    CHGJRN JRN(DSS/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT2)
    ```
    
    For DSS versions up to 6.2.0/1 and 6.1.0/63, the location property **Journal with \*SYSSEQ** ([**DB2i_Log_Journal_SysSeq**](/docs/dss6-property-reference-location-properties#db2ilogjournalsysseq)) should be defined and the IBM i journal attribute **FIXLENDTA** should contain **\*SYSSEQ**.
    ```shell
    CHGJRN JRN(DSS/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT2) FIXLENDTA(*SYSSEQ)
    ```

-   Example: Schema *DSS* running with **\*MAXOPT2** and action [**Capture**](/docs/dss6-action-reference-capture) with parameter [**IgnoreSessionName**](/docs/dss6-action-reference-capture#ignoresessionnames) is defined.  
    For DSS versions since 6.2.0/2 and 6.1.0/64, the IBM i journal attribute **FIXLENDTA** should contain **\*USR**.
    ```shell
    CHGJRN JRN(DSS/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT2) FIXLENDTA(*USR)
    ```
    
    For DSS versions up to 6.2.0/1 and 6.1.0/63, the location property **Journal with \*SYSSEQ** ([**DB2i_Log_Journal_SysSeq**](/docs/dss6-property-reference-location-properties#db2ilogjournalsysseq)) should be defined and the IBM i journal attribute **FIXLENDTA** should contain both **\*SYSSEQ** and **\*USR**.
    ```shell
    CHGJRN JRN(DSS/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. DSS 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.

DSS provides a shell script dsssupplementalimage.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 DSS_HOME/dbms/db2i/dsssupplementalimage.qsh file to the iSeries root directory. The script is invoked when Activate Replication (dssactivate) 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 dsssupplementalimage.qsh script via the SQL/QCMDEXC interface for all tables that must be captured. The script can return its exit code to the calling DSS Hub via SQL only. For that DSS creates a table in schema DSS called dss_supplementalimage__channel_. If the DSS database user does not have a table creation authority, then the suppl_log_sysdba.qsh script file is created in the DSS_CONFIG/hubs/hub/channels/channel_name/locs/location_name/initsql directory on the DSS 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 DSS_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

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

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 Data Types Conversion

<b>Since</b> 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 DSS'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 DSS Repository type ansidate.

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

• DATS

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

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, DSS captures changes from all members.

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

Compare and Refresh from Db2 for i

DSS 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 DSS database user must be granted the following privilege to read from the Db2 for i database:

  grant select on <em>tbl</em> to user <em>dssuser </em>
  

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

  GRTOBJAUT OBJ(DSS/*ALL) OBJTYPE(*FILE) USER(<em>DSSUSER</em>) AUT(*USE)