A cursor also provides the capability to select records. The arithmetic and character-string mapping functions are used to provide this selection. A typical place to specify such record selection is the WHERE clause of an SQL statement (arithmetic expressions are not available in DDS). By restricting a user’s authority to cursors (i.e., to access paths) that select only certain records, you can prevent the user from viewing the records not included. This combination of authority and selection is used to enforce database security.

Two segments make up a cursor: a base segment and an associated space. The base segment contains two sets of addresses to identify the data spaces and the data-space indexes the cursor can use. Up to 32 data spaces and up to 32 data-space indexes can be identified. The only case where more than one data-space index may be required is that of a join-logical file (not an SQL view). The base segment also contains the mapping code and the selection code the cursor uses. The associated space of the cursor contains the member’s text and attributes. The member-level links are maintained by the OS/400 component of the database.

Now that we have looked at each of the three main system objects that support the database, we are ready to see how a user accesses database files in the AS/400.

The User’s Path to Data

All user database accesses must come through OS/400 and the MI. Only SLIC has direct access to the data. From a user’s perspective, accessing an OS/400 database file is accomplished with an open file operation. We will see that this function is accomplished at the MI with an ACTIVATE CURSOR instruction. Likewise, when a user wants to close a file, there is a DEACTIVATE CURSOR instruction at the MI.

When accessing a data space, the user can specify several open-file command options. These include the operation (read, write, update, or delete) and the number of records. When a cursor spans multiple data spaces, the user may define a subset of these data spaces with which to work. This subset definition is specified at file creation time with a CRTLF command, but this definition may be overridden at run time with an OVRDBF (Override with Database File) command before a file is opened. How long to wait for a record, if the record is locked, can also be defined by the user. This, too, is specified at file creation time, but it can be overridden before the file is opened.

The user can access the data space in either a random or a sequential mode. In the sequential-access mode, multiple pages can be transferred into main memory from the disk with a single operation, called a bring. The user specifies the size of the bring by using the number-of-records option. This is done in the OVRDBF command or in the OPNQRYF (Open Query File) command. In the random-access mode, usually one page is brought. The random mode is available only if the data space has an index. When a data-space index is used, the database code in SLIC uses a look-ahead scheme to bring in the next logical page in the index.

Because a cursor is needed to access data in a data space, MI instructions exist to provide user access by opening the cursor (ACTIVATE CURSOR) and to close the cursor (DEACTIVATE CURSOR) when the user is finished. These functions of activating and deactivating a cursor at the MI are equivalent to opening and closing a file in OS/400. The associated space of an activated cursor contains the open data path (ODP) information for the member that is open.

Executing an ACTIVATE CURSOR instruction in the MI causes the cursor to be attached to the process that activated it. Later, we look at another MI object called the process control block. A process is a unit of work in the system, and each process has a process control block. The cursor is attached to this process control block. If the process activates more than one cursor, a doubly linked list of the cursors is chained to the process control block. Further, no other process can now use these cursors. If more than one user wants to share the same cursor, a clone of the cursor has to be created. This cloning operation occurs when a cursor is activated.

This brings us to the subject of permanent and temporary cursors. A permanent cursor is associated with each file member, and every file member can have one and only one permanent cursor. When a clone is created, it is a temporary cursor. If a cursor is activated to provide an ODP to a file member and some other process has already activated the same cursor, a temporary clone of the cursor is created. The mapping code and the selection code are not kept in a temporary cursor. Addresses in the temporary cursor point back to a permanent cursor that contains these codes. The reason for this approach is to save space.

OS/400 has a convention that it always makes a temporary copy of the permanent cursor, using the CRTDUPOBJ (Create Duplicate Object) instruction, and then it activates only the temporary cursors. In this way, a permanent cursor can be a representation of the file member. Other than this convention, no member object exists at the MI. Further, all ODPs are temporary cursors. Again, this arrangement is the result of an OS/400 convention and is not a restriction of the MI.

SLIC Journaling

Earlier in this chapter, we described the database journaling function. The basic functions for logging changes that occur in the database are provided below the MI. Two MI system objects provide this support: journal ports and journal spaces. A journal port manages the journal definition, and the journal space is the container for the journal entries. These two system objects support the OS/400 journal and journal receiver objects, respectively. Notice, once again, the name change at the MI boundary.

A journal port, like so many other system objects, has two segments. The base segment contains addresses to the objects being journaled. It also contains the addresses of the current journal spaces. The OS/400 portion of the database uses the associated space segment.

A journal space object is a special MI system object that can have many segments. The base segment contains the address of the journal port. It also contains the addresses of up to 120 journal data segments. A journal data segment is another type of segment that can be part of a journal-space system object. The journal entries are stored in the journal data segments.

The journal entries themselves are variable in length. Each entry contains a length field; a sequence number; a type field; time and date stamps; user, program, and job identifications; and the information being journaled. It is important to note that these entries cannot be updated or deleted. The purpose of journaling is simply to keep copies of changes to the database in case a recovery is required.