Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

138 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone

Figure 8-5 SQL trigger body

8.1.5 Getting information about triggers

To determine which triggers are registered for a specific table, you can use one of the

following methods:

򐂰 CL command Display File description DSPFD

Using the CL command DSPFD with option TYPE = *TRG will list all triggers and their

characteristics.

The following example shows how all triggers on the Order Detail file can be listed.

DSPFD FILE(ORDERDTL)

TYPE(*TRG)

򐂰 Querying the system tables

SYSTRIGGERS Provides all information about the triggers

SYSTRIGDEP Provides all information about dependencies between files used in

SQL triggers

SYSTRIGCOL Provides information about columns used in SQL triggers

SYSTRIGUPD Provides information about updated columns used in SQL triggers.

For more information on triggers refer to the redbook Stored Procedures, Triggers and User

Defined Functions in DB2 UDB for iSeries, SG24-6503.

8.2 Stored procedures

Stored procedures are programs or service programs containing a specific signature, so that

they can be called from any SQL interface, such as interactive and embedded SQL, ODBC,

and JDBC.

Chapter 8. Externalizing data access 139

In contrast to triggers, which are directly linked to the database tables, stored procedures

must be called explicitly by using the SQL CALL statement. When a stored procedure is

called, it is embedded in the call stack and executed. If the stored procedure ends, either

normally or abnormally, the control is returned to the caller. In this way it is possible to

interchange parameter information between caller and stored procedure.

Stored procedures can be called locally (on the same system where the application runs) or

remotely on a different system. They are the easiest way to perform a remote call and to

distribute the execution logic of an application program.

Stored procedures offer a number of powerful advantages for distributed application

development. These include the following;

򐂰 Common business functions can be encapsulated in stored procedures and made

universally accessible, promoting code re-use and consistency, and providing support for

object-oriented application design.

򐂰 Performance can be significantly improved for distributed applications that require several

SQL calls to be made by the client against a remote database. Instead of multiple trips

across the network for each of these requests, they can be combined and executed within

a stored procedure so only one single call is needed. This performance improvement can

also create subsequent benefits in reducing lock contention.

򐂰 Security can be enhanced, as developers are only able to work with the stored procedure

input and output parameters, and are prevented from viewing or altering the underlying

code that implements the business function. Stored procedures can help to control the

access to database objects.

There are two types of stored procedures:

򐂰 External stored procedures

򐂰 SQL stored procedures

8.2.1 External stored procedures

External stored procedures are programs or service programs written in an HLL with a unique

signature to be called from any SQL interface, like embedded SQL, ODBC, JDBC, etc.

Programs do not need to be registered, as long as you do not want to overload the

procedures (look at “Procedure signature and overloading” on page 148). They can be called

directly by the SQL interfaces using the SQL command CALL.

Programs or service programs are registered as stored procedures by using the SQL

command CREATE PROCEDURE.

The activation group of the program or service program is inherited to the stored procedure,

which means that if your program runs in a named activation group, the stored procedure

uses the same activation group.

Note: Since Release V5R3M0, procedures in service programs without return value can

be registered as stored procedures.

To register procedures in service programs, the option EXTERNAL NAME in the CREATE

PROCEDURE statement (look at “SQL statement CREATE PROCEDURE” on page 144),

must include the procedure’s entry point (procedure name).