Managing DB2 for i5/OS schemas and journals

Many System i™ developers are starting to use SQL to create their database objects instead of the proprietary non-SQL interfaces. This article will show you how to use a schema with IBM® DB2® for i5/OS® to group related database objects. DB2 for i5/OS was previously named DB2 UDB for iSeries. This article, formerly entitled "Managing DB2 UDB for iSeries Schemas and Journals", has been updated for DB2 for i5/OS.

Kent Milligan (kmill@us.ibm.com), DB2 Technology Specialist, IBM

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.



10 July 2007 (First published 08 May 2003)

Introduction

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
OpsNav picture of empty schema

The library

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.


Journals and journal receiver objects

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
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 (for instance, 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.


Moving a schema

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:

  1. CL command on the source system: SAVLIB MYSCHEMA ACCPTH(*YES)
  2. CL command on target system: CLRLIB MYSCHEMA
  3. 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.

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

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

  3. CL commands:
    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.

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

  5. Save the newly configured schema to backup media, so that you don't have to do these configuration steps again!

Conclusion

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.

Instructions for pre-V5R4 releases

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:

  1. 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).

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

  3. CL command for each table:
    STRJNRPF TRG/DEPT TRG/QSQJRN IMAGES(*BOTH) OMTJRNE(*OPNCLO) 
    STRJRNPF TRG/EMPLOYEE TRG/QSQJRN IMAGES(*BOTH) OMTJRNE(*OPNCLO)

    The STRJRNPF currently does not support an *ALL option like the ENDJRNPF command. 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.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, IBM i
ArticleID=13320
ArticleTitle=Managing DB2 for i5/OS schemas and journals
publish-date=07102007