As System i developers begin to use SQL to create their database objects, many i5/OS developers are struggling with how best to manage the schema and journal objects that are interwoven into the i5/OS SQL interface. In addition, IBM DB2 developers from other platforms (for example DB2 for AIX) face similar challenges when working with System i for the first time, since i5/OS journaling is different from the logging mechanisms used in other DB2 servers.
On DB2 for i5/OS, a schema is used to group related database objects. A DB2 for i5/OS schema is actually a collection of DB2 objects and i5/OS objects. In fact, DB2 for i5/OS actually called the schema object a collection on older releases. When the CREATE SCHEMA statement is executed, the following objects are created (see Figure 1):
This collection of objects in the schema provides the container for storing related DB2 objects and the journal objects needed for enabling recovery of database changes to these DB2 objects.
Figure 1. iSeries Navigator view of an empty schema
The library is the logical "container" for the objects and is where the objects are stored. DB2 object names have to be unique within this container. These filtered DB2 catalog views created as part of the schema are a set of views that describe tables, views, indexes, procedures, functions, triggers, and constraints. These views are built over the base set of catalog tables in libraries QSYS and QSYS2 and only include information on objects contained in that schema.
DB2 for i5/OS logs changes to a table through a process called journaling. The i5/OS
journal records database object changes by sending information to the journal receiver. Thus, a journal receiver is
analogous to a DB2 log file. When a table is created in the schema, it is automatically journaled to the journal
object created by DB2 for i5/OS during execution of the
CREATE SCHEMA statement.
Even though DB2 automatically starts journaling for the table object, it is the user's responsibility to manage the journal and journal receiver objects. As you can imagine, these journal receiver objects that contain the database changes can become quite large, so ignoring the auto-created journal receivers is not an option unless you have unlimited disk space. However, the journal receiver objects should not be deleted arbitrarily to save disk space. In addition, even though journaling can be stopped for a table, it is not recommended since applications accessing non-journaled objects are unable to specify an isolation level and the applications cannot issue commit and rollback.
Most i5/OS customers use their journal receivers as a core part of their database backup and recovery process. One approach would be to save a complete copy of the table backup media once a week and then save the changes to the table (for example the journal receiver) on a nightly basis to back up media, and then repeat this process every week. Once the journal receiver is backed up, the journal receiver object can be deleted. Find more information about the proper steps for saving and deleting journal receiver objects in the Backup and Recovery Guide in the IBM System i Information Center. Visit the online version of this IBM System i Information Center.
Figure 2. Journal interface
You will also notice that multiple journal receiver objects will appear in the DB2 for i5/OS schema over time.
That is because DB2 for i5/OS creates the journal object with the system-managed receiver option
MNGRCV(*YES) on the CHGJRN CL command).
The graphical journal management interface in Figure 2 denotes a system-managed journal receiver
with the System radio button selected in the "Receivers managed by:" section. With this option specified,
DB2 automatically creates a new journal receiver each time the system is restarted (such as when IPLing a system) and whenever the
attached receiver reaches its size threshold. The current journal receiver is detached and a new one is created. It is easier to backup a journal receiver when it is detached. Also, the receiver must be detached before it can be
deleted. So you can see how this option makes it easier to manage journal receivers.
Journal cleanup can also be simplified by selecting the "Delete Receiver When No Longer Needed" radio
button (see Figure 2) or by specifying
DLTRCV(*YES) ) on the CHGJRN CL command).
This system-managed option allows i5/OS to automatically delete journal receivers that are no longer associated with
an active database transaction. Before using this option, the Backup and Recovery Guide should be consulted to
ensure that this option does not impact your system backup process. Managing journal performance is a topic outside of
the scope of this article, but you can reference Striving for Optimal Journal Performance (SG24-6486, IBM Redbooks).
Since DB2 for i5/OS has no concept of tablespaces and automatically stripes and balances DB2 objects across disks, the journal and journal receiver objects are going to be the only schema objects that require space management from an administrator's perspective. The only other space administration task is making sure that there's enough disk space available on the system.
The journal and journal receiver objects automatically created by DB2 are also key components that need to be addressed when moving a schema to a new system or to a different schema (for instance, moving from test to production). When a table is moved to a new schema (by way of a copy or restore operation), the table will still be associated with the journal in the original schema -- it does not automatically start using the journal in the new schema. Thus, resetting the journal for a DB2 table is a necessary step when moving a table from one schema to another.
The preferred method for moving a schema to a different schema or system is re-running the original SQL creation script. If the SQL creation script is not available, it can be reverse-engineered with iSeries Navigator using the "Generate SQL" task. Just right-click on the schema object and select the "Generate SQL" task to build an SQL creation script for all of the objects in the schema. Since the table objects are created in the new schema, they will be automatically journaled to the new journal.
Many times recreating all of the DB2 objects is not feasible since the source objects contain a large amount of data. An alternative method to use in this situation is the i5/OS save and restore commands.
If the schema name is the same on the source and target systems, then the following steps can be used:
- CL command on the source system: SAVLIB MYSCHEMA ACCPTH(*YES)
- CL command on target system: CLRLIB MYSCHEMA
- CL command on target system: RSTLIB MYSCHEMA OPTION(*ALL)
DB2 objects in the restored schema, MYSCHEMA, will be automatically journaled since the journal and journal receiver objects have the same name on the source and target system.
When the schema name is not the same, additional steps are needed to ensure the DB2 objects are journaled correctly after the restore operation. If I have a schema named SRC with two tables, DEPARTMENT and EMPLOYEE, then here are steps that can be utilized to move schema SRC into schema TRG using the save and restore method.
- CL command: SAVLIB SRC ACCPTH(*YES)
Remember the i5/OS container for a schema is a library.
The ACCPTH(*YES)option saves the actual index tree object if any indexes exist in the schema - that will eliminate indexes having to be rebuilt on the restore operation.
- SQL: CREATE SCHEMA TRG
This will create the target schema object with auto-created journal and system catalog views.
Note: If the journal object was altered away from its default settings with the CHGJRN CL command or iSeries Navigator. Those customizations would need to be performed on the journal object in the TRG schema.
CRTDTAARA DTAARA(TRG/QDFTJRN) TYPE(*CHAR) LEN(40) VALUE('TRG QSQJRN ')
CHGDTAARA DTAARA(TRG/QDFTJRN (21 20)) VALUE('*ALL *RSTOVRJRN')
The QDFTJRN data area is a special data area that is automatically processed by the restore command. The restore command consults the contents of this data area to determine if any special journal processing has been requested. Without this data area, the restore command will not perform any journal processing. The result is that the DB2 objects will no longer be journaled in the new schema since by default they are still associated with the journal in the original schema. The *RSTOVRJRN value directs the restore command to journal the DB2 objects to the specified journal (TRG/QSQJRN) after each object is restored. Support for the QDFTJRN data area was added in V5R4 of i5/OS. You can find more details on the QDFTJRN data area in the iSeries Information Center.
- CL command: RSTLIB SRC OPTION(*NEW) RSTLIB(TRG)
The *NEW option will only restore the SRC objects that do not already exist in the TRG schema. This type of restore essentially restores everything but the objects automatically created by DB2 (journal, journal receiver, and catalog views). Due to the contents of the QDFTJRN data area, the DB2 objects are properly journaled after the restore operation completes.
Note: If logical file or index objects in the schema have been explicitly journaled by the user, then journaling on those objects would have to be ended and restarted after the restore with the ENDJRNAP and STRJRNAP CL commands.
- Save the newly configured schema to backup media, so that you don't have to do these
configuration steps again!
You now should have a clear understanding of the objects that make up a DB2 for i5/OS SQL Schema object (library, catalog views, journal, and journal receiver) and how these i5/OS objects relate to one another. In addition, the awareness of DB2 for i5/OS journaling compared to logging on other servers should simplify the management of journal receiver objects - especially the options that enable system-management of the journals.
Most importantly, you should also be more productive in your management of DB2 for i5/OS over the long run by understanding the preferred methodology for moving a schema and ensuring that the journal configurations are properly maintained during this migration.
On i5/OS releases prior to V5R4 the QDFTJRN data area is not supported. Thus, the following steps must be performed manually to ensure the DB2 objects are properly journaled after the restore operation:
- Record a list of the DB2 objects currently journaled in TRG.
Since the i5/OS journal CL commands only accept the short DB2 object identifiers, the short name for a DB2 for i5/OS will need to be recorded in this step (see the DB2 for i5/OS long and short identifiers article).
- CL command: ENDJRNPF *ALL SRC/QSQJRN
DB2 objects in the new schema, TRG, are currently associated with the journal in the original schema. Eliminate this association by ending journaling for all tables. If schema SRC does not exist on the system where schema TRG was restored, then the restore operation will end the journal association with the original schema. Thus, the ENDJRNPF *ALL command is not needed when schemas SRC and TRG reside on different systems.
- CL command for each table:
STRJNRPF TRG/DEPT TRG/QSQJRN IMAGES(*BOTH) OMTJRNE(*OPNCLO) STRJRNPF TRG/EMPLOYEE TRG/QSQJRN IMAGES(*BOTH) OMTJRNE(*OPNCLO)
STRJRNPFcurrently does not support an
*ALLoption like the
ENDJRNPFcommand. So the command needs to be executed for each DB2 table object to associate these tables with the journal and journal receiver objects in schema TRG.
Kent Milligan is a Senior DB2 for i consultant in IBM ISV Solutions Enablement for the IBM i platform. After graduating from the University of Iowa in 1989, Kent spent the first eight years of his IBM career as a member of the DB2 development team in Rochester. He speaks and writes regularly on various DB2 for i relational database topics.