3 Steps: How Stored Procedure works in DB2

3 Steps work flow of Stored procedures
3 Steps work flow of Stored procedures

We know that stored procedure can be called from either COBOL /PLI programs. Once you issue CALL statement there is a process to execute it in DB2 server.

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

Step1

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.

Step2

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.
    Step3
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.