View figure.A stored procedure is a user-written application program that is compiled and stored at the server. When the database manager is running in multiple user mode, local applications or remote DRDA applications can invoke the stored procedure. Since the SQL statements issued by a stored procedure are local to the server, they do not incur the high network costs of distributed statements. Instead, a single network send and receive operation is used to invoke a series of SQL statements contained in the stored procedure.
There are several other benefits that can be gained through the use of stored procedures, including:
In DB2 Server for VSE & VM, all stored procedures are fenced, which means that they are separated from the database manager with respect to execution and memory usage. This is necessary to ensure that a stored procedure does not
A fenced implementation is achieved through the use of stored procedure servers. An stored procedure server is an application requester that is local to the database manager and is used to execute the stored procedure. A fenced implementation is achieved as follows:
Note that a stored procedure server must be dedicated to a single database server.
The stored procedure handler is a DB2 Server for VSE & VM supplied utility, called ARISPRC, that interfaces between the database manager and the stored procedure. It runs in the stored procedure server and does the following:
The group clause of the CREATE PSERVER statement makes it possible to define groups of stored procedure servers. This is useful if
The group option gives the database administrator flexibility in defining the enviroment and is useful for system tuning.
The SERVGROUP column in SYSTEM.SYSROUTINES is cross-referenced with the SERVGROUP column in SYSTEM.SYSPSERVERS to establish the server that is to be used for a stored procedure.
In DB2 Server for VM, the following requirements exist:
where userid is the VM ID of the stored procedure server virtual machine. This enables the database manager to request the services of the stored procedure server. Note that this can also be enabled by putting an IUCV ALLOW statement in the CP directory of the stored procedure server virtual machine. However, the first method requires the database machine to have explicit access to the stored procedure server machine, and the second method allows any machine to connect to the stored procedure server machine. Since the stored procedure server must be dedicated to a single database machine, the first method is preferred.
This directory control statement causes CP to start CMS in the stored procedure server virtual machine.
This directory control statement indicates the number of IUCV and APPC/VM connections allowed for the virtual machine. Unless a stored procedure that runs on the server does work that requires additional connections, setting nnnn to 1 is sufficient.
The following is a sample profile:
'GLOBALV INIT' /* The following three lines have to be in PSERVER's PROFILE EXEC
'SET SERVER ON'
'SET FULLSCREEN OFF'
'SET AUTOREAD OFF'
'SET CMSTYPE HT'
'SET IMSG OFF'
'SET LANGUAGE AMENG (ADD ARI USER'
'CP SET RUN ON' /* This prevents CP READ upon */
/* RECONNECTing to userid. */
'CP TERM MODE VM' /* Accept CMS commands. */
'GLOBAL LOADLIB SCEERUN' /* LAODLIB FOR LE PROGRAMS */
'CP LINK SQLMACH 195 195 RR' /*Link to database product disk */
'ACCESS 195 Q' /*Access as Q disk */
'EXEC SQLINIT DB(SQLMACH)' /*Initialize as normal AR */
IF RC <> 0 THEN DO
SAY 'SQLINIT FAILED WITH RETURN CODE = ' RC
'TELL SQLMACH SQLINIT FAILED IN PSERVER'
'#CP LOGOFF'
END
ELSE
SAY 'PSERVER INITIALIZATION COMPLETED.'
|
Note: the PROFILE EXEC should not contain any commands that require console input, or put the ID into VM READ.
:nick.SQLSVR01 :module.ARISPRC
:list.SQLMACH
|
The fields in the $SERVER$ NAMES files represent the following:
The name of the private resource. This is VM machine name of the stored procedure server virtual machine.
The user IDs of the users that are authorized to access the private resource. This is the VM machine name of the database server virtual machine. Since stored procedure servers must be dedicated to a single database, only one name can be specified here. The stored procedure handler will not start if more than one name is specifed.
The name of the stored procedure handler, ARISPRC.
For complete details on setting up the machine, see "Managing Private Resources: in the VM/ESA Connectivity Planning, Administration, and Operation manual".
In DB2 Server for VSE, the following requirements exist:
// ASSGN SYS098,SYSPCH |
The following is a sample JCL to start up the DB2 Server for VSE |7.1.0 database:
// JOB Start DB2 for VSE in multiple user mode with Stored Procedure Support // EXEC PROC=ARIS71PL // EXEC PROC=ARIS71DB // ASSGN SYS098,SYSPCH // EXEC PGM=ARISQLDS,SIZE=AUTO,PARM='DBNAME=SAMPLE_DB' |
Note: You will need to customize |ARIS71PL, ARIS71DB to work with your local VSE/ESA environment.
. $$ PUN CLASS=6,DISP=I,JNM=SQLSVR01
* $$ LST CLASS=V,DISP=D,DEST=(,SYSID01)
// JOB SQLSVR01
// OPTION NODUMP,NOSYSDUMP
// ASSGN SYS098,SYSPCH
// LIBDEF *,SEARCH=(CMPLR22.SCEEBASE,CMPLR22.LEVSEBC,
CMPLR22.SCEECICS,PRD2.DB2710)
ON $RC > 0 GOTO END
// EXEC PGM=ARISPRC,SIZE=1M
/.END
/*
/&
|
Note: For the PUNCH card, you must use . $$ PUN instead of * $$ PUN. Also, you must make sure that all your stored procedure phases are in the search path defined in this JCL so that stored procedure server handler phase, ARISPRC, to be able to find and load the stored procedure.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * L I N K PSERVER component * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * // OPTION CATAL INCLUDE ARISLKHZ // EXEC LNKEDT,PARM='MSHP' /* |
Note: ARISLKHZ is a the linkbook used to linkedit the stored procedure server phase, and it is a member of your production library.
When a stored procedure is running, the stored procedure server in which it is executing is dedicated to it. Since no other stored procedure can execute in that server until the current one finishes, it is not possible to execute multiple stored procedures concurrently with a single stored procedure server. However, multiple stored procedures can execute concurrently if multiple stored procedure servers are defined.
The CREATE PROCEDURE statement must be used to define a stored procedure before it can be used. The CREATE PROCEDURE puts the definition of the procedure into the catalog tables SYSTEM.SYSROUTINES and SYSTEM.SYSPARMS. See DB2 Server for VSE & VM SQL Reference for the definitions of these tables as well as more details on the CREATE PROCEDURE statement. A third catalog table, SYSTEM.SYSPSERVERS, is used to identify the stored procedure servers. See DB2 for VSE & VM SQL Reference for the definition of this table. When the database manager is started, DB2 Server for VSE & VM loads the contents of these tables into cached structures. The cached structures contain the information from the catalog tables, as well as information about the run time status of stored procedures and stored procedure servers. When the database manager is started, the status of a stored procedure defaults to STARTED, and the status of a stored procedure server defaults to STOPPED. The database manager then issues a START PSERVER command for any stored procedure server for which the AUTOSTART value of the corresponding row in SYSTEM.SYSPSERVERS is Y. When the START PSERVER command completes, the status of that stored procedure server is STARTING. When an SQL CALL statement is issued, the database manager uses the cached information to determine the server at which this stored procedure will run. To determine the server, DB2 Server for VSE & VM does the following. Note that when these steps make reference to the catalog tables, it is the cached information from the tables that they are referring to.
If none of the servers in the group can be used, and the group checked was not the default group, DB2 Server for VSE & VM checks whether the stored procedure can run in the default group. Servers in the default group are indicated by a value of NULL for the SERVGROUP column in SYSTEM.SYSPSERVERS. If the DEFSERV column in SYSTEM.SYSROUTINES contains a 'Y' or is NULL, then that procedure can run in a server in the default group. If no servers are available in SERVGROUP, or if SERVGROUP in SYSTEM.SYSROUTINES is blank, and the procedure can run in the default group, DB2 Server for VSE & VM will attempt to run the stored procedure in one of the default servers. The process it uses to find a server in the default group is the same as the one it used to look for a server in a specific group, as described earlier.
If none of the servers at which it can run are available, the stored procedure waits for a free server. If more than one stored procedure is waiting for the same server, the one that has been waiting the longest will be invoked when the server is available.
Figure 57 shows how DB2 Server for VSE & VM resolves the server name. Note that in order to illustrate the process used, the figure shows cached data. Not all of the columns shown are in the corresponding catalog table.
Figure 57. How DB2 Server for VSE & VM Determines the Stored Procedure Server to Use
| View figure. |
The following steps are involved in Figure 57:
A stored procedure server can be in several different states: STARTED, STARTING, STOPPING or STOPPED. Each state reflects/indicates the availability of the server and whether or not its definition can be altered or dropped. The START and STOP PSERVER operator commands can be used to change the state of a stored procedure server. An incoming SQL CALL request can also change the state of a stored procedure server, but only in certain situations, as will be described later. Following is a description of how each state is achieved and the functions that can be performed on the server for each of these states.
This is the default state for all stored procedure servers when the database manager is started. It is also achieved when a procedure server is stopped using the STOP PSERVER operator command. This state has two conditions that determine whether certain functions, such as an SQL CALL implicitly starting the server, are allowed. These two conditions are IMPLICIT and NOIMPLICIT. At startup, all stored procedure servers are STOPPED with IMPLICIT. Here is a summary of the differences between the two:
A stored procedure server will always be stopped if a severe error is encountered during the execution of a stored procedure. The IMPLICIT/NOIMPLICIT condition is not changed, unless a connection time-out has occurred, as mentioned above.
A server can only achieve this state if it was executing a stored procedure at the time the STOP PSERVER command was issued. When the state is "stopping", the database manager know that once the current SQL CALL requests concludes, the stored procedure server must be stopped. The stored procedure server will remain available as long as the IMPLICIT condition is true.
In this state, the server is ready to start executing an SQL CALL request. It has been allocated a communication block but it is not yet connected. The database manager will establish the connection once an SQL CALL statement requests to use the server. If the connection is successful, the status will be changed to started. If it fails, the server will be stopped. The IMPLICIT/NOIMPLICIT condition will remain unchanged. If the connection times out, as mentioned earlier, the server will be stopped with the NOIMPLICIT condition true. A server in this state can also be stopped by issuing the STOP PSERVER operator command.
In this state, the stored procedure server is connected to the database server and is either executing an SQL CALL statement, or is ready to execute an SQL CALL. This state can only be reached if an SQL CALL statement requested to use the server. The START PSERVER operator command will not promote a stored procedure server to this state as it does not establish the physical connection with the database server. The STOP PSERVER command demotes the server to a status of STOPPED and severs its connection if it is not currently executing an SQL CALL request. If the server is in use, the STOP PSERVER command will demote it to a status of STOPPING. The database server will complete the "stopping" sequence once the SQL CALL request concludes, by severing the stored procedure server's connection, freeing its communication block and changing the status to STOPPED. The IMPLICIT/NOIMPLICIT conditions will be dictated by the last STOP PSERVER operator command issued against the server.
For more information on the STOP and START PSERVER commands see the DB2 Server for VSE & VM Operation manual. For more information on the SQL CALL statement see the following manuals:
The following describes how to remove or alter a stored procedure server:
To remove a stored procedure server group, follow the steps above for each stored procedure server in the group. It is possible to remove all the servers in a group only if as there are no stored procedures defined that can use that server group. If this is the case, it is not be possible to remove the last procedure server in the group. Any stored procedures that run in the group must be moved to another group (by using the ALTER PROCEDURE satatement and specifying the SERVER GROUP clause) or dropped before the last stored procedure server in the group can be dropped.
Before a stored procedure can be invoked, it must be
If a stored procedure load module or phase is modified, a STOP PROC command must be issued, followed by a START PROC command, so that the database manager will cause the stored procedure server to load the new copy of the stored procedure load module or phase into memory.
After these steps are complete, the user that created the package associated with the stored procedure is able to GRANT RUN authority on the package to other users, allowing them to issue the SQL CALL statement to run the stored procedure.
The following describes how to remove or alter a stored procedure:
Note: There is a special case where issuing the STOP PROC ACTION REJECT command will not suffice to allow altering or dropping a stored procedure definition. If the procedure is still running in a stored procedure server, you will not be allowed to alter or drop its definition, even if the status is STOP-REJ. The execution of an SQL ALTER or DROP PROCEDURE command will return with SQLCODE -15000. You can use the SHOW PSERVER and SHOW PROC operator commands to monitor the procedure's progress before you try to alter or drop its definition.
This parameter serves two purposes:
A value of 0 means that no PTIMEOUT is in effect. The default for PTIMEOUT is 180.
Specify the number of times a stored procedure is allowed to terminate abnormally, after which a STOP PROC ACTION REJECT is performed against the procedure and all subsequent SQL CALL statements are rejected. Note that a time-out that occurs while waiting for a stored procedure server to be assigned for an SQL CALL statement is not included in this count. The default, 0, means that the first abend of a stored procedure causes SQL CALLs to that procedure to be rejected. For production systems, you should accept the default.
Figure 58 shows the interactions between the database manager, the stored procedure server, the stored procedure handler, and the stored procedure itself. The figure does not show the definition of the stored procedure server or of the stored procedure itself; it is assumed that this has already been done.
Figure 58. Stored Procedure Environment
 View figure. |
The database manager hooks the agent used by the requester that issued the SQL CALL to the selected stored procedure server. The database manager must maintain the CONNECT information for the original requester as well, in order to return the result of the SQL CALL statement.
The database manager saves its environment in preparation for receiving and processing requests from the stored procedure. Finally it sends a request to the stored procedure handler to invoke the stored procedure.