StarQuest Technical Documents

SQDR Plus: Tips for Db2 for i (iSeries) Journaling

Last Update:8 June 2022
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL032

Abstract

When performing incremental replication from a Db2 for i (IBM iSeries) database, tables to be replicated must be journaled, preferably as *BOTH rather than *AFTER. This technical document contains information about viewing and changing the journaling characteristics of a table using a terminal session, IBM Navigator for i, i Access Client Solutions, or SQDR Control Center..

See Tips for Db2 for i (iSeries) Replication for additional hints.

Contents:

• Viewing and changing journaling characteristics of a table
  • Using a terminal session
  • Using SQDR Control Center
  • Using IBM Navigator for i (Heritage)
  • Using i Access Client Solutions

Viewing and changing journaling characteristics of a table

Tables replicated from Db2 for i should be journaled, preferably as *BOTH rather than *AFTER. It is often desirable to view the journaling characteristics (not journaled, *AFTER, or *BOTH) of a table. It may also be desirable to see which journaled tables are associated with a particular journal - for instance, to determine which backlogged subscriptions may be preventing the deletion of journal receivers, or to associate a subscription if an agent indicates an authority problem accessing a journal.

Using a terminal session

• To display tables associated with a journal:

WRKJRNA library/journal
e.g.
WRKJRNA MYLIB/MYJRN
then choose F19=Display journaled objects

• To display the journal associated with a table:

WRKOBJ library/table
choose 8=Display description
or DSPOBJD library/table OBJTYPE(*FILE) DETAIL(*FULL)
and page down 4 screens until you see:

Journaling information:
Currently journaled . . . . . . . . : YES
Current or last journal . . . . . . : QSQJRN
Library . . . . . . . . . . . . . : QAUSER
Journal images . . . . . . . . . . . : *BOTH

or
Journaling information:
Currently journaled . . . . . . . . : NO

• To start or end journaling, use one of the following methods:
  • Start Journal Physical File (STRJRNPF) CL command e.g.
    STRJRNPF FILE(MYLIB/MYTAB) JRN(MYJRNLIB/QSQJRN) IMAGES(*BOTH)
    
  • Create and populate a data area named QDFTJRN in the library
  • Start Journal Library (STRJRNLIB) CL command e.g.
    STRJRNLIB LIB(MYLIB) JRN(MYJRNLIB/QSQJRN) INHRULES((*ALL *ALLOPR *INCLUDE *BOTH))
    

You must create the journal and journal receivers if they do not exist. The latter two methods affect only new files (tables) created in the library (schema); refer to the IBM i documentation for details.

• To change from *AFTER to *BOTH, use one of the following methods.
  • CHGJRNOBJ OBJ((MYLIB/MYTABLE *FILE)) ATR(*IMAGES) IMAGES(*BOTH)
    CHGJRNOBJ command requires an *EXCLRD lock on the object.
  • Use ENDJRNPF and STRJRNPF.
    This option requires you to keep track of all attributes you have specified before the ENDJRNPF.

 

Using SQDR Control Center

If you select the Plus symbol to Add a Publication and search for the desired schema and/or tables, the list of tables will show the journaling information; you can cancel the Add Publication operation once you have obtained the desired information.

You can also display the journal information of any or all subscribed or published tables by selecting the checkbox next to the tables of interest and select View Metadata in the Resources menu.

Using IBM Navigator for i (Heritage)

IBM Navigator for i is the web-based console interface available in recent versions of IBM. The example uses the "Heritage Navigator", which is typically disabled on systems with recent (May 2022 & later) PTF packages installed. This function is not available in "New Navigator", but is accessible in i Access Client Solutions.

Point a browser to http://systemname:2001 and log on.

Navigate to the table, right-click and select Journaling from the drop-down menu. This will display whether the table is journaled, the name and library of the journal, and whether or not *BOTH is being used.

If the table is not journaled, you can start journaling from this screen, specifying the name and library of the journal.

If the table is journaled as *AFTER, the setting Journal images before change will be unchecked; you can change journaling to *BOTH by selecting this checkbox.

Using i Access Client Solutions

Navigate to the table, right-click and select Journaling from the drop-down menu. This will display whether the table is journaled, the name and library of the journal, and whether or not *BOTH is being used.

If the table is not journaled, you can start journaling from this screen, specifying the name and library of the journal.

If the table is journaled as *AFTER, the setting Journal images before change will be unchecked; you can change journaling to *BOTH by selecting this checkbox.