RPGIV @ Work

A unique site for RPG and System i Lovers


Hi, this site will provide all what you need in System i and RPG developments.

My Name is Chamara Withanachchi, System i Expert and RPG Developer. And in the field for last 11 years.

I hope you will find lot of valuable information from this site

QIBM_QDB_OPEN Print E-mail
User Rating: / 1
Written by Chamara Withanachchi   

In i5/OS V5R3, support for QIBM_QDB_OPEN, the Open Database File-exit point, was added. This exit point gives you the ability to call a program whenever a database table on the system is opened. After the exit point has been properly set up, the exit program is called when any job on the system issues a request to open a physical file, logical file, SQL table or SQL view. This action occurs regardless of the origin of the open request; this includes programming interfaces (such as Record Level Access [RLA] and SQL), as well as nonprogramming interfaces (such as IBM iSeries™ Navigator, IBM Query/400, IBM Data File Utility [DFU] and the IBM i5/OS® Open Query File [OPNQRYF] and Display Physical File Member [DSPPFM] commands). When the database Open and resulting invocation occurs, the exit program runs in the same job that issued the Open request and can actually set a return code value to reject the Open request.

The exit point invokes the exit program when a full Open is requested. A full Open request results in the creation of an open data path (ODP). The ODP provides a direct path from the program to the table so that the program can issue input and output operations to access data in the table. If you use the exit point, it is important that you understand when a full Open occurs. The timing of this event is dependent on several conditions, including the kind of database access you request: RLA or SQL.

Record Level Access

RLA (also referred to as “native I/O”) is the traditional way that high level language (HLL) programs (like RPG and COBOL) access the contents of files. Many programs today still use RLA to access the data. The full Open in RLA occurs when the program issues the Open operation or method, not during the actual attempt to access the data (such as a READ or CHAIN operation).

When an RPG program uses RLA, tables are defined in the program’s File specification section (F-spec). The point at which the Open actually occurs depends on whether the USROPN keyword is specified in the F-spec. If it is not, an implicit full Open occurs when the program is first invoked. If the USROPN keyword is specified, the program must explicitly open the table using the OPEN operation or %OPEN built-in function. In either case (implicit or explicit open), the value that the RPG program assigns to the last record (LR) indicator dictates the table-open behavior of subsequent calls of that program. If LR is turned on, the tables are closed before the program ends and a full Open occurs in each subsequent call to the program. If the program does not turn on LR, all tables remain open for subsequent calls to that program, relieving the burden of full Opens. In this condition, the ODP is maintained and the program is in re-entrant mode.

Another type of open with RLA is “shared Open,” a technique used to share a file’s ODP among multiple programs in the same job or routing step. One program issues the full open for the file and all other programs in that job can use the same ODP for that file, thus saving system resources such as processor, memory and disk. A shared Open is controlled either by specifying the SHARE(*YES) parameter in the override or by creating file commands prior to opening the file. Shared Opens are commonly used in applications that invoke the OPNQRYF command.

SQL Access

HLL programs and remote interfaces (such as ODBC, JDBC and ADO.NET) can use SQL to access tables. Obviously, an Open occurs when the SQL OPEN statement is used on a cursor. Not-so-obvious Opens take place when the SELECT INTO, INSERT, DELETE and UPDATE statements are started. As with all SQL statements, these Opens are carried out by the database manager, which internally opens the tables for the application. During the initial execution of the Open cursor or other SQL statement, the database manager performs a full Open. The table is closed when the cursor is closed or the statement has finished processing. If that same statement is run a second time within the same job or connection, the process is repeated and another full Open occurs. After this second execution, the database manager does not actually close the table; it is left open for subsequent executions of the statement within the same job or connection. During the third execution of the statement, the database manager does not have to perform the full Open because the table is already open. This is known as a “pseudo Open” and, at this point, the statement is in a state commonly referred to as “ODP reuse mode.” Pseudo Opens are similar in characteristics and behavior to shared Opens in native I/O operations.

<Previous   Next>