<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html lang="en-us" xml:lang="en-us"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <meta name="security" content="public" /> <meta name="Robots" content="index,follow" /> <meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' /> <meta name="DC.Type" content="reference" /> <meta name="DC.Title" content="SQL CALL statement (stored procedures)" /> <meta name="abstract" content="The SQL CALL statement is not actually specific to distributed relational databases, but a discussion of it is included here because its main value is in distributing application logic and processing." /> <meta name="description" content="The SQL CALL statement is not actually specific to distributed relational databases, but a discussion of it is included here because its main value is in distributing application logic and processing." /> <meta name="DC.subject" content="stored procedure, definition, SQL CALL, remote procedure call, SQL statement, CALL, result sets" /> <meta name="keywords" content="stored procedure, definition, SQL CALL, remote procedure call, SQL statement, CALL, result sets" /> <meta name="DC.Relation" scheme="URI" content="rbal1sdd.htm" /> <meta name="DC.Relation" scheme="URI" content="rbal1sqlcalludb.htm" /> <meta name="DC.Relation" scheme="URI" content="../db2/rbafzmst02.htm" /> <meta name="DC.Relation" scheme="URI" content="../cl/addrpyle.htm" /> <meta name="DC.Relation" scheme="URI" content="../cl/chgjob.htm" /> <meta name="DC.Relation" scheme="URI" content="rbal1tdebug.htm" /> <meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" /> <meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" /> <meta name="DC.Format" content="XHTML" /> <meta name="DC.Identifier" content="rbal1stored" /> <meta name="DC.Language" content="en-us" /> <!-- All rights reserved. Licensed Materials Property of IBM --> <!-- US Government Users Restricted Rights --> <!-- Use, duplication or disclosure restricted by --> <!-- GSA ADP Schedule Contract with IBM Corp. --> <link rel="stylesheet" type="text/css" href="./ibmdita.css" /> <link rel="stylesheet" type="text/css" href="./ic.css" /> <title>SQL CALL statement (stored procedures)</title> </head> <body id="rbal1stored"><a name="rbal1stored"><!-- --></a> <!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script> <h1 class="topictitle1">SQL CALL statement (stored procedures)</h1> <div><p>The SQL CALL statement is not actually specific to distributed relational databases, but a discussion of it is included here because its main value is in distributing application logic and processing.</p> <div class="section"><p>Result sets can be generated in the stored procedure by opening one or more SQL cursors associated with SQL SELECT statements. In addition, a maximum of one array result set can also be returned. For more information about writing stored procedures that return result sets, see the descriptions of the SET RESULT SETS and CREATE PROCEDURE statements in the SQL reference topic.</p> </div> <div class="section"><p>The CALL statement provides a capability in a DRDA<sup>®</sup> environment much like the Remote Procedure Call (RPC) mechanism does in the Open Software Foundation (OSF) Distributed Computing Environment (DCE). In fact, an SQL CALL to a program on a remote relational database actually is a remote procedure call. This type of RPC has certain advantages; for instance, it does not require the compilation of interface definitions, nor does it require the creation of stub programs.</p> </div> <div class="section"><p>You might want to use SQL CALL, or <em>stored procedures</em>, as the technique is sometimes called, for the following reasons: </p> <ul><li>To reduce the number of message flows between the application requester (AR) and application server (AS) to perform a given function. If a set of SQL operations are to be run, it is more efficient for a program at the server to contain the statements and interconnecting logic.</li> <li>To allow native database operations to be performed at the remote location.</li> <li>To perform nondatabase operations (for example, sending messages or performing data queue operations) using SQL. <div class="note"><span class="notetitle">Note:</span> Unlike database operations, these operations are not protected by commitment control by the server.</div> </li> <li>To access server APIs on a remote server.</li> </ul> </div> <div class="section"><p>A stored procedure and application program can run in the same or different activation groups. It is recommended that the stored procedure be compiled with ACTGRP(*CALLER) specified to achieve consistency between the application program at the AR and the stored procedure at the AS. If the stored procedure is designed to return result sets, then you should not create it to run in a *NEW activation group. If you do, the cursors associated with the result sets might be prematurely closed when the procedure returns to the caller and the activation group is destroyed. </p> </div> <div class="section"><p>When a stored procedure is called that issues an inquiry message, the message is sent to the QSYSOPR message queue. The stored procedure waits for a response to the inquiry message. To have the stored procedure respond to the inquiry message, use the <span class="cmdname">Add Reply List Entry (ADDRPYLE)</span> command and specify *SYSRPYL on the INQMSGRPY parameter of the <span class="cmdname">Change Job (CHGJOB)</span> command in the stored procedure.</p> </div> <div class="section"><p>When a stored procedure and an application program run under different commitment definitions, the COMMIT and ROLLBACK statements in the application program only affect its own commitment definition. You must commit the changes in the stored procedure by other means.</p> </div> </div> <div> <ul class="ullinks"> <li class="ulchildlink"><strong><a href="rbal1sqlcalludb.htm">DB2 Universal Database for iSeries CALL considerations</a></strong><br /> Stored procedures written in C that are invoked on some platforms cannot use argc and argv as parameters (that is, they cannot be of type main()). This differs from <span class="keyword">iSeries™</span> stored procedures which must use argc and argv.</li> </ul> <div class="familylinks"> <div class="parentlink"><strong>Parent topic:</strong> <a href="rbal1sdd.htm" title="During the precompile process of a distributed DB2 Universal Database for iSeries application, the i5/OS licensed program might build SQL packages to be run on an application server (AS).">SQL specific to distributed relational database and SQL CALL</a></div> </div> <div class="relref"><strong>Related reference</strong><br /> <div><a href="rbal1tdebug.htm" title="Testing and debugging distributed SQL programs is similar to testing and debugging local SQL programs, but certain aspects of the process are different.">Testing and debugging</a></div> </div> <div class="relinfo"><strong>Related information</strong><br /> <div><a href="../db2/rbafzmst02.htm">SQL reference</a></div> <div><a href="../cl/addrpyle.htm">Add Reply List Entry (ADDRPYLE) command</a></div> <div><a href="../cl/chgjob.htm">Change Job (CHGJOB) command</a></div> </div> </div> </body> </html>