Building and debugging SQL/PL Stored Procedures

Klaas Brant, KBCE b.v. - Wednesday 15 May - Session 3
 

SQL/Persistent Stored Modules is a ISO standard which is implemented in Db2, MySQL/MariaDB and, with an addon, in Postgresql. IBM call the language SQL/PL because it is used in Db2 for more than just Stored Module (Stored Procs). In this seminar we focus on SQL/PL Procedures which exist on both Db2 for LUW and Db2 for z/OS. It is on bot platforms 100% indentical. What you code and test on DB2 LUW can be deployed on z/OS and the other ways around. So the seminar is for both Db2 LUW and Db2 for z/OS 

This session is a workshop! All attendees should bring their laptop so they can code and deploy some SQL/PL stored procedures on a DB2 in the cloud.

Topics:

  • About SQL/PL Stored procedures and Modules
    • What are they and when (not) to use them
    • What happens at create time...
    • The many options of CREATE DDL
    • Create - Replace - Alter - Drop options and implications
  • SQL/PL data types
    • DB2 and user data types
    • Row data type
    • Arrays
  • SQL/PL Flow of Control
    • Atomic vs Not Atomic
    • Conditional Stements: IF and CASE
    • Looping: FOR, WHILE, REPEAT and LOOP
    • Transfer Control: GOTO, LEAVE, ITERATE, RETURN, COMMIT / ROLLBACK
  • SQL/PL Cursors
    • Why you need cursors
    • Locking by cursors
    • perform positional delete / update
    • the effect of commit / rollback
    • Passing an open cursor to caller
  • SQL/PL Condition Handling
    • SQLCODE vs SQLSTATE
    • Condition handlers
      • What are they
      • How to code
      • Scope of the handler
    • Force Application exception
    • Raising Errors
  • Advanced topics
    • Nesting and Recursion
    • Debugging and profiling
    • Performance / Rebinding the package
    • Security
    • Hint & tips

Platform: LUW and z/OS

logo

logo

← Back to all sessions

About: Klaas Brant

photo

Klaas Brant is the founder of KBCE, a company that specializes in DB2. He specializes in administration, tuning and backup / recovery strategies. Klaas has been working with DB2 for more than 30 years on z/OS, and more than 20 years on DB2 LUW. He has published many DB2 articles and has been teaching DB2 since the 1980's. He is the organizer of the DB2 Symposiumsince the start in 1999. Klaas is also the chairman of the Dutch DB2 User Group (DDUG). Klaas is a member of IBM's DB2 Gold Consultants Team and an IBM Data Champion. He can be reached at: kbrant@kbce.com

logo