DB2 for z/OS - Stored Procedures

( Duration: 3 Days )

The DB2 for z/OS Stored Procedures training course is designed to provide the database application designer or application programmer with the knowledge to recognize when an IBM stored procedure is the correct solution for an application. Attendees will utilize the facilities of the IBM Data Studio, create COBOL stored procedures, and be able to troubleshoot stored procedures using IBM Data Studio.

By attending DB2 for z/OS Stored Procedures workshop, attendees will learn to:

  • Use RUNSTATS to accelerate the performance of a given SQL access
  • Employ DSN1COPY, REPAIR, and DIAGNOSE for DBA tasks which are not recovery-oriented
  • Use parallel index build with the LOAD, REORG and REBUILD INDEX utilities
  • Load partitions in parallel
  • Determine the appropriate LOAD options to use while considering concurrency and performance requirements
  • Choose appropriate options with REORG and UNLOAD to achieve optimal performance and availability
  • Provide appropriate LISTDEF, TEMPLATE, and OPTIONS utility control statements for use in DB2 utilities

  • Discuss relational database concepts
  • Code SQL
  • Understand the basics of a programming language

This course is for Database Application Designers and Database Application Programmers supporting DB2 stored procedures would all benefit from attending this course.



Introduction to DB2 Stored Procedures

  • Describe what a stored procedure is
  • Determine when a stored procedure should be used
  • Describe execution and application flow using stored procedures
  • Have a basic understanding of z/OS components that are required to run stored procedures
  • Describe DB2 interfaces for controlling and defining stored procedures
  • Locate stored procedures-related DB2 Catalog information

Debugging Stored Procedures written in Legacy Programming Languages (Debug Tool for z/OS)

  • Describe Debug Tool for z/OS
  • Identify the capabilities of Debug Tool for z/OS
  • Use Debug Tool for z/OS: DB2 for z/OS stored procedures and Invokers of stored procedures

Writing COBOL Stored Procedures

  • Understand the stored procedure parameter types and styles
  • Use stored procedure performance and build options: Program Type SUB/MAIN, STAY
  • RESIDENT YES, Language Environment parms (RUN OPTION), and Compile/Link-edit
  • Decide on the error handling and reporting to be used in their stored procedure
  • Develop and test a stored procedure that: Receives and returns parameters, Uses indicator variables to pass nullable parameters, and Uses PARAMETER STYLE SQL

Invoking Stored Procedures

  • Identify correct CALL syntax
  • Set up for Client CALL to stored procedure
  • Develop a Client Program that invokes a stored procedure

Returning Result Sets

  • Develop a stored procedure that returns result sets to its client application
  • Use sequential files as input for stored procedures result sets
  • Return a cursor that joins non-DB2 resources with DB2 tables

Receiving Result Sets

  • Describe the two alternatives for receiving result sets
  • Identify the preparation steps that must be taken in a client application to receive: A fixed number of result sets and An unknown number of result sets
  • Prepare and test a client program that receives a fixed number of result sets

IBM Data Studio

  • Use IBM Data Studio to create, edit, and deploy SQL external and native stored procedures
  • Use IBM Data Studio to run a stored procedure
  • Use IBM Data Studio to display schema objects
  • Use DB2 command line processor to invoke DB2 for z/OS stored procedures

SQL Stored Procedures

  • Create an external SQL procedure
  • Create a native SQL procedure
  • Avoid ambiguity with procedure names

Debugging Native SQL Stored Procedures using the Unified Debugger

  • Alter or create a Native SQL Stored Procedure to: Allow Debug Mode, Disallow Debug Mode, and Disable Debug Mode
  • Use the Unified Debugger to: View Source, Set Breakpoints, and View and change variable values

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.