How to write COBOL Program in DB2 stored procedure

I want to add more points on how to write COBOL program in DB2 stored procedures. The functionality of below stored procedures is:

  • Searches the catalog table SYSROUTINES for a row matching the input parameters from the client program. The two input parameters contain values for NAME and SCHEMA.
  • Searches the DB2® catalog table SYSTABLES for all tables in which the value of CREATOR matches the value of input parameter SCHEMA. The stored procedure uses a cursor to return the table names.

Here procedure name is : GETPRML

Passing two input values and getting two output values. Usually, IBM Data studio will be used to develop stored procedures. Once a stored procedure is created in DB2, it can be accessed by different operating systems. Ex:-Mainframe-Zo/s, iseries (AS400), Windows, Linux etc.,. Because, Stored procedure is a database object, and it does not specific to any particular operating system.

  • Language: It is COBOL. Since we are writing stored procedure in COBOL
  • Deterministic: This is an instruction to DB2, how it responses output values. Read more…
  • Reads SQL Data: It means it does not modify SQL data. It only reads SQL data. Read more… 
  • External name: This definition tells it is for external stored procedure
  • COLLID : This is collection id. Related to DBRM of this procedure bound to a package collection id.
  • ASUTIME: Total amount of processor time
  • Parameter Style:  General- Passing input parameters. Read more..
  • Stay Resident: If stored procedure to remain in memory after procedure ends.
  • RUN Options: You need to pass run time options. Read more…
  • Program type: This can be MAIN (main program) or SUB (sub-program)
  • Security: This means security at DB2 level
  • Result sets: Specifies the maximum number of query result sets that the procedure can return. The default is DYNAMIC RESULT SETS 0, which indicates that the procedure can return no result sets. The value of integer must be between 0 and 32767.

Commit on return: NO/YES, if DB2 to issue commit after procedure ends.

Related: Powerful steps to create Stored procedure