We know that stored procedure can be called from either COBOL /PLI programs. Once you issue a CALL statement there is an internal process to execute it in DB2 server. Here is the use of stored procedure before you proceed further.
Process Step: 1
- The workstation application uses the SQL CONNECT statement to create a conversation with DB2.
- DB2 creates a DB2 thread to process SQL requests.
- The SQL statement CALL tells the DB2 server that the application is going to run a stored procedure. The calling application provides the necessary parameters.
- The plan for the client application contains information from catalog table SYSIBM.SYSROUTINES about stored procedure A.
DB2 passes information about the request to the stored procedures address space, and the stored procedure begins execution
Process Step: 2
- The stored procedure executes SQL statements. DB2 verifies that the owner of the package or plan containing the SQL statement CALL has EXECUTE authority for the package associated with the DB2 stored procedure.
- One of the SQL statements opens a cursor that has been declared WITH RETURN. This causes a result set to be returned to the workstation application when the procedure ends.
- Any SQLCODE that is issued within an external stored procedure is not returned to the workstation application in the SQLCA (as the result of the CALL statement).
- If an error is not encountered, the stored procedure assigns values to the output parameters and exits.
Process Step-3
- Control returns to the DB2 stored procedures address space, and from there to the DB2 system. If the stored procedure definition contains COMMIT ON RETURN NO, DB2 does not commit or roll back any changes from the SQL in the stored procedure until the calling program executes an explicit COMMIT or ROLLBACK statement.
- If the stored procedure definition contains COMMIT ON RETURN YES, and the stored procedure executed successfully, DB2 commits all changes. The COMMIT statement closes the cursor unless it is declared with the WITH HOLD option.
Related Posts
Srinimf 
You must be logged in to post a comment.