DB2 Stored Procedures

Duration:   2 days

Participants:   Application developers and DBAs who will be calling or coding stored procedures in the DB2 z/OS environment.

Objectives:   Upon successful completion of this course you will be able to: -  Describe the environment, architecture and setup for implementing DB2 stored procedures. -  Write stored procedures in COBOL, and describe how to write stored procedures in SQL/PSM, Java, REXX, and C. -  Write client applications that invoke stored procedures. -  Prepare, run, test, and troubleshoot stored procedures. -  Describe additional client requestors. -  Describe non-DB2 resources that stored procedures can access.

Overview:   This course teaches writing stored procedures (SPs) in COBOL and in the simpler SQL/PSM, how to invoke (call) SPs, how to setup a SP in the DB2 for z/OS environment, troubleshooting, and an understanding of the architecture involved.

Prerequisites:   Working knowledge of COBOL, SQL, TSO, and some JCL. Knowledge of JAVA, C, REXX is helpful but not required.

Format:   Lecture and discussion with hands-on exercises.

Topic Outline:   -  Concepts and Features         Stored Procedure uses (SQL and non-DB2 uses)         Stored procedures environment         Setup requirements and installation considerations         SPAS versus WLM address spaces         Client/Server logic flow         Advantages over embedding SQL in client programs -  Defining a Stored Procedure         Create Procedures         SYSIBM.SYSROUTINES         Without results sets         With NULLs         With results sets         For non-DB2 stored procedures -  Stored Procedures that Do Not Return Results Sets         Client/Server logic         Examples              COBOL stored procedures              COBOL client              JAVA client         Effect on Logic of WITH NULLS         Program preparations              Steps and JCL for COBOL stored procedures              Steps and JCL for COBOL client -  Commands Useful for Stored Procedures         DB2I commands for diagnosing         -DISPLAY -  Stored Procedures Returning Results Sets         Client/Server logic overview         Examples              COBOL stored procedures              COBOL client              JAVA client         Writing the stored procedure              DECLARE CURSOR changes              DESCRIBE PROCEDURE         Writing the COBOL client              Define locator variables              ASSOCIATE LOCATORS for each result set              ALLOCATE CURSOR for each cursor              DESCRIBE CURSOR for each cursor              FETCH              New SQL CODEs for client -  Two-Phase Commit -  GLOBAL TEMPORARY TABLES         Definition         Uses         Creating an instance              OPEN              SELECT              INSERT              DELETE         Not logged -  SQL TEMPORARY Tables         Contrast with persistent tables         Uses -  Additional Programming Language Support         SQL Procedures (formerly PSM/SQL)              Example              Statements         REXX              Example         Stored Procedure Builder         SQLJ Support         Java Support -  Support         Websites         Books         Manuals -  Support Tables (Catalog and Non-Catalog)         SYSIBM.SYSPSM         SYSIBM.SYSPSMOPTS         SYSIBM.SYSPSMOUT         SYSIBM.SYSROUTINES -  COMMIT and ROLLBACK         Where and when -  Architecture         WLM         RRSAF         SPAS         Application Environment

Mainframe List       Database List       Course List       Site Map       Home