One of the nicest features of DB2 on the IBM i is the integrated journaling. This article shows you how to use SQL and journaling together.

Journaling has been an integral part of the DB2 database since long before it was called the DB2 database. In fact, journaling predates the IBM i and even the AS/400; journaling was introduced on the System/38 way back in the days of cave computers. Journaling provides a number of important capabilities, including commitment control and high availability, but in this article, I'm going to stick to one of the most basic features of journaling: change logging.

That Is What Journaling Means, After All

At its simplest, a journal is little more than a log of activity on a database. Note that I didn't say a database file; that's because a single journal can log the activity on any number of files at the same time. I'm not going to spend a lot of time on the management issues of journals; those are more at the system management level, and I want today's discussion to center on application-level debugging. Suffice to say, several questions need to be answered when devising your journaling strategy, including the number of files per journal, the type and frequency of journal receiver cycling, and the effects on the auxiliary storage pool (ASP). These decisions depend heavily on your business processes and even on your auditing requirements.

But if we assume for the purposes of this article that you indeed have journaling in place for your files, then I can show you a few handy techniques to use those journals to get information that can be invaluable when attempting to debug applications. This is also a nice way to learn what complex systems are doing when you perhaps don't have all the pertinent design documentation available to you. But first things first: let's have a quick review of journaling in DB2 for the IBM i.

Figure 1: This diagram explains the journaling object relationships in DB2 for the IBM i. (Click images to enlarge.)

Generally speaking, you journal one or more files to a journal using the STRJRNPF command. Prior to doing that, you must have set up the journal and attached a journal receiver. The names of the journal and journal receiver are entirely up to you and can reside in any library on the system. The journal entries are actually stored in the journal receiver; the journal is little more than a conduit from the database file(s) to the journal receiver. Each journal has exactly one journal receiver active at any time. At your discretion, you can detach a journal receiver and at the same time replace it with a new one; at that point, you can save and delete the detached receiver. You can also delete detached receivers that have not been saved; you will get an informational message when you try to do so, with the options C and I (the standard i5/OS version of "Are you sure?"). This is coming perilously close to the management topics I said I would avoid, so let's not delve any deeper into this particular area.

The primary reason I bring the receiver and journal relationship up at all is to note that, prior to being deleted, all of the receivers on the system belong to something called the "current chain" of receivers. More precisely, the current chain of receivers includes the current receiver and any immediately previous receivers that haven't been deleted. If you have four receivers besides the one currently attached and haven't deleted one from the middle of the list, then all five receivers make up the current chain, and the various journal processing commands can be told to scan all five as if they were a single receiver. Very nice indeed.

Using a Journal with SQL

On to the meat of the issue: using a journal with SQL. Why do this at all? Well, that's because the standard i5/OS tool for working with journal entries, the DSPJRN command, isn't exactly geared toward application developers. You can certainly use it, but the way it presents information (purely chronologically) doesn't lend itself to easy access. The technique I'm going to show you today is to use the OUTFILE option of the DSPJRN command to dump the data into a database file and then access that file via SQL.

As I said, the command is DSPJRN, and using the output file is relatively simple:

DSPJRN JRN(EGLWEB/JRN)

FILE((EGLWEB/CUSTMAST))

RCVRNG(*CURCHAIN)

OUTPUT(*OUTFILE)

OUTFILE(EGLWEB/CUSJRN1122)

ENTDTALEN(*CALC)

Assuming that the journal JRN is being used to journal at least the file CUSTMAST (both of which reside in library EGLWEB), then this command will create a database file named CUSJRN1122 in EGLWEB that contains all of the journal entries associated with that file from the chain of current receivers available on the machine. The ENDDTALEN option *CALC is used to make sure that data isn't truncated on larger files. After this command executes, you'll have a database file that you can look at with DFU or SQL. The fields in the generated file give you a wide range of information on the database activity. For example, the JOENTT field has a code indicating the type of operation; database operations include PT (put or write), DL (delete), and UB/UP (update before and update, the before and after images of a database update).

Using these fields, you can select date and time ranges, look for specific programs or users, and in general have a surgically precise way of seeing exactly what happened to the file at any given point. Many categories of entries are stored—from management entries identifying when journaling was started or stopped, all the way to the record-level entries showing adds, updates, and deletes.

The good news is that the record-level entries include the actual data. The bad news is that the data is stored as a single-character field (named JOESD, where ESD stands for entry-specific data). So when you look at the data, you'll simply see an undifferentiated string of data.

Let me show you an actual example. Using the file in the example above, I turned on journaling and then added and subsequently updated a record. If I use STRSQL to display the data, I'd see something like this:

ENTRY    SEQUENCE   CODE  TYPE  DATE       TIME   JOB         USER

LENGTH   NUMBER                                   NAME        NAME

127          2    D     JF   112209  201,422   PLUW041     JDP

127          3    F     JM   112209  201,423   PLUW041     JDP

159          4    F     OP   112209  201,433   PLUW041     JDP

159          5    F     OP   112209  201,435   PLUW041     JDP

352          6    R     UP   112209  201,448   PLUW041     JDP

155          7    F     CL   112209  201,449   PLUW041     JDP

159          8    F     OP   112209  201,452   PLUW041     JDP

155          9    F     CL   112209  201,458   PLUW041     JDP

159         10    F     OP   112209  201,458   PLUW041     JDP

352         11    R     PT   112209  201,529   PLUW041     JDP

155         12    F     CL   112209  201,529   PLUW041     JDP

155         13    F     CL   112209  201,531   PLUW041     JDP

125         14    F     EJ   112209  201,604   PLUW041     JDP

125         15    D     EF   112209  201,604   PLUW041     JDP

127         16    D     JF   112209  201,620   PLUW041     JDP

The entry-specific data would look like this:

SPECIFIC

DATA

00

00

CUSTMAST  EGLWEB    CUSTMAST  I

CUSTMAST  EGLWEB    CUSTMAST  IOUD

001987The Lefthand Store            7334 Prairie

CUSTMAST  EGLWEB    CUSTMAST

CUSTMAST  EGLWEB    CUSTMAST   OU

CUSTMAST  EGLWEB    CUSTMAST

CUSTMAST  EGLWEB    CUSTMAST   OU

987654A New Customer                900 Maple Ave

CUSTMAST  EGLWEB    CUSTMAST

CUSTMAST  EGLWEB    CUSTMAST

While some of the entries don't make a lot of sense, you can see what looks like database data in some of the entries. Those are the entries with record-level entry types like UP and PT. So, one of the first things you learn when extracting data is to specify the entry type when selecting the data, limiting the query to things like UP and DL. Actually, if you want to see all record-level activity, the journal makes it a little easier, because they all have a common value of "R" in the Journal Code field, JOCODE.

But even then the number of records can be overwhelming. The problem then is to restrict the query even further to records for a specific field value. In that case, you use the SUBSTR scalar function. Here's an example:

SELECT * FROM CUSJRN1122

WHERE SUBSTR(JOESD,1,6) = '987654'

This will return all entries where the first six characters of the JOESD field match the customer number 987654. You need to know the exact position and length of the field in the database record, but you can get that easily enough from DSPFFD or a third-party tool. The first columns look like this:

ENTRY         SEQUENCE   CODE  TYPE

LENGTH        NUMBER

352              11    R     PT

352              18    R     UB

352              19    R     UP

Note the PT for the add (put) and the UB and UP for the before and after images. Further into the record, you'll see this:

Reynolds                 NC

Reynolds                 NC

Greensboro               NC 27406

The record was added with Reynolds, NC, and subsequently changed to Greensboro, NC, with the ZIP code entered. Accessing packed data requires the use of the HEX scalar. If you want to check for matches, the LIKE keyword is very powerful. And if you want to see packed fields, the HEX scalar allows you to create the appropriate search values. Not only that, but the entry fields tell you when the activity occurred, what job and user performed the activity, and which program actually executed the database action. That's a lot of very powerful information when you're trying to debug an ailing application.

This is just a first step into accessing journal data through SQL, and once you start using it, I'm sure you'll find a wide variety of ways to use the tool that I haven't even imagined.