Benefits & Early Experiences Testing Native SQL Stored Procedures

1. Philip Czachorowski Fidelity Investments philip.czachorowski@fmr.com New England DB2 Users Group Meeting Benefits & Early Experiences Testing Native SQL Stored Procedures 11/19/09

2. Benefits & Early Experiences Testing Native SQL SP • Stored Procedure Benefits • DB2 Supported SP Languages • Native SQL SP Overview • Performance Considerations • Native SQL SP Benefits and Appropriate Usage Guidelines • Implementation Considerations • Conclusions

3. Stored Procedure Benefits Without Stored Procedures With Stored Procedures Stored procedures reduces database interactions and network latency.

4. Stored Procedure Benefits • Performance • Allows the grouping of SQL statements • Reduced database interactions/network traffic • Encapsulation of data rules • Define data rules in DBMS instead of programs • Move closer to data • Modular Code/Reusability • Implement data access once as common procedures • Security • Grant access rights to the SP, not the referenced tables • Limits users to only executing functions implemented in the SP

5. DB2 Supported Stored Procedure Languages *This does not imply that SQL PL has all the features of a high level language **Executes in DDF address space If executed via distributed access.

6. Native SQL SP – SQL PL • Based on ISO/ANSI SQL Standards • SQL – Part 4: Persistent Stored Modules (PSM) • First published in 1996 as an extension to SQL92 • Standard includes: • External Routines (high level languages) • SQL Routines (SQL extensions) – main focus • IBM’s Implementation of SQL Routines • Language implementation - SQL PL • External SQL Procedures – DB2 for z/OS Version 5 • Native SQL Stored Procedures – DB2 9 for z/OS • Other vendors (proprietary languages - pre-standard) • Oracle: PL/SQL - 1992 • Sybase Microsoft SQL Server: T-SQL -1995

7. Native SQL SP – SQL PL • Header • SP name • In/Out Parameters • Options • Body • Assignment • SQL-procedure-statement • SQL-control-statement • SQL-statement • Compound statement • SQL-variable-declaration • DECLARE-CURSOR-statement • SQL-condition-declaration • handler-declaration • return-codes-declaration

8. Native SQL SP Header Selected Create Stored Procedure Options

9. Native SQL SP Header – Part 2 Selected Create Stored Procedure Options

10. Native SQL SP Header – Part 3 Selected Create Stored Procedure Options

11. Native SQL SP Body

12. outer: BEGIN DECLARE SALARYDECIMAL(9,2); inner1: BEGIN DECLARE SALARY DECIMAL(9,2); SET SALARY = (SELECT SUM(SALARY)FROM DSN8910.EMP); END inner1; inner2: BEGIN DECLARE SALARY DECIMAL(9,2); SET inner2.SALARY = (SELECT SUM(SALARY)FROM DSN8910.EMP); SELECT SUM(SALARY)INTO outer.SALARY FROM DSN8910.EMP; if1: IF SALARY > 9000 then inner3: BEGIN DECLARE SALARY DECIMAL(9,2); SET SALARY = inner2.SALARY; END inner3; END IF; END inner2; END outer Compound Statements - Nesting

13. emp2: BEGIN DECLARE FETCH_END CHAR(1); DECLARE SALARY DECIMAL (9,2); DECLARE R_SALARY DECIMAL (9,2); DECLARE R_BONUS DECIMAL (9,2); DECLARE emp_cursor2 CURSOR FOR SELECT SALARY, BONUS FROM DSN8910.EMP; DECLARE CONTINUE HANDLER FOR NOT FOUND SET FETCH_END = 'Y'; OPEN emp_cursor2; FETCH emp_cursor2 INTO R_SALARY, R_COMM; WHILE FETCH_END <> 'Y' DO FETCH emp_cursor2 INTO R_SALARY, R_BONUS; SET emp2.SALARY = emp2.SALARY + R_SALARY + R_BONUS; END WHILE; CLOSE emp_cursor2; END emp2; While Loop with Cursor Processing

14. Equivalent Code Using FOR emp1: BEGIN DECLARE SALARY1 DECIMAL(9,2); FOR empl_loop AS emp_cursor CURSOR FOR SELECT SALARY, BONUS FROM DSN8910.EMP E DO SET emp1.SALARY1 = emp1.SALARY1 + SALARY + BONUS; END FOR; END emp1;

15. Versioning • Versioning • Enables multiple versions to be maintained • A stored procedure must retain the same parameter signature • Maintaining versions • ALTER ADD VERSION • ALTER REPLACE VERSION • ALTER REGENERATE VERSION • ALTER ACTIVATE VERSION • ALTER DROP VERSION • Deployment • Bind deploy • Testing • SET CURRENT ROUTINE VERSION

16. Error Processing • Declare SQL conditions DECLARE overflow CONDITION FOR SQLSTATE ‘22003’; • Declare handlers • Specifies code that handles an exception or condition DECLARE EXIT HANDLER FOR overflow DECLARE CONTINUE HANDLER FOR overflow • Nesting and Scoping • Signal • Raise a condition • Specify a SQLSTATE (can be application defined value) • Resignal • Resignal the condition that activated the handler • Raise an alternate condition so that it can be processed at a higher level

17. Performance Considerations • Performance compared to a High Level Language SP depends on: • Number, type, and complexity of the control logic code • SQL statement call profile • Benefit of avoiding WLM address space • Virtual memory • SP packages will be larger • Impacts EDM Pool • zIIP engine exploitation • Native SQL SP called via DDF (processed un an enclave SRB) • IBM has a threshold for how much zIIP eligible CPU is allowed • Need to have available zIIP engines

18. External versus Native SQL SP Execution Exec SQL Call SP Call SP Control logic Exec SQL SELECT Control logic Exec SQL SELECT Control logic Exec SQL SELECT SELECT SELECT SELECT DBM1 Address Space WLM SP Address Space DBM1 Address Space Application TCB WLM TCB Exec SQL Call SP Control logic SQL SELECT Control logic SQL SELECT Control logic SQL SELECT DBM1 Address Space Simplified Diagram Application TCB

19. DB2 Performance Monitoring CLASS 1 CLASS 2 COBOL Stored Procedure Native SQL Stored Procedure

20. zIIP and zAAP Exploitation Call COBOL or C SP SRB TCB TCB Call COBOL or C SP TCB DRDA Call and Schedule Call and Schedule Call External SP SRB Call External SP TCB TCB TCB DRDA Call and Schedule Call and Schedule Call Java SP SRB Call Java SP TCB TCB TCB DRDA Call and Schedule Call and Schedule WLM SP Address Space Call Native SQL SP Under DDF SRB Under Appl TCB Call Native SQL SP TCB SRB Call Call DRDA DDF Address Space Application Address Space DBM1 Address Space General Processor zIIP Eligible DB2 Subsystem zAAP Eligible

21. Performance Characteristics • Net performance is a trade-off between the CPU savings by avoiding WLM overhead and the relative CPU time to process control statements • Net CPU cost will depend in part on the CPU time that can be off loaded to specialty engines

22. Native SQL SP Benefits

23. Appropriate Code Usage Guidelines Suggested Enhancements to Native SQL SP • Code structuring options, like grouping code • Collections, Arrays • Local functions • Advanced types • Compiler directive statements • Other ISO/ANSI SQL PSM features

24. Implementation Considerations • Test Native SP before implementing • Native SQL SP is new • Involve developers and support groups • Test performance • Establish appropriate usage guidelines • Native SQL SP • High Level Language SP • Establish development roles and responsibilities • SQL PL development skills • Application developers or DBAs • Establish coding standards • Handle as a programming language • Define naming and coding conventions • Standardize common functions like error processing

25. Implementation Considerations • Select development tools and methodology • GUI based tools like Data Studio • Mainframe based development • Debugging capabilities, like using the Unified Debugger • Create source code maintenance procedures • Use Native SQL SP versioning • Integrate into external source code management system • Establish testing procedures and select tools • Define standard names for versioning • Testing methods and tools • Production migration process • Establish production procedures • Operational and system monitoring tools and procedures • Performance monitoring tools and procedures

26. Implementation Considerations - Coding • Document code • Include a standard prolog • Format stored procedures lines • Retain the multi-line format by including line formatting characters • Declare variable names unambiguously • Declare variable names where they will be used • Use unique names or qualify all references • Declare cursors with unique names • Declare within the compound statement where referenced • Avoid using GOTO • Consider breaking up complex procedures into multiple procedures • Nested stored procedures • Reference result sets via Locators (ASSOCIATE/ALLOCATE) • Temporary tables

27. Implementation Considerations – Errors • Trap all errors and capture appropriate diagnostic information • Consider callable routines • Declare the variables SQLCODE and SQLSTATE • Standardize application specific SQLSTATES • Code HANDLER statements to handle errors and warnings • Define separate HANDLER statements to handle specific SQL errors • Declare a generalized HANDLER FOR SQLEXCEPTION and SQLWARNING at the outermost compound statement • Be careful coding CONTINUE HANDLER declarations • Retrieve diagnostic information using GET DIAGNOSTICS • Extract the information appropriate for the error • Capture the DB2_LINE_NUMBER • Standardize how errors are returned to the caller • Use of the RETURN value • Establish a standard set of OUT parameters

28. Implementation Considerations - UOW • Two UOW models • Commit on return • Commit by caller • Commit on return • Advantage: Ensures that locks are immediately freed • Disadvantage: Is incompatible with nested SPs • Commit by caller • Advantage: Enables caller to coordinate UOW with multiple resources • Disadvantage: Distributed clients may hold locks for an excessive times. May require 2PC.

29. Conclusions • Native SQL SP has potential benefits • Faster development • Improved performance • Enables architecture vision • Native SQL SP is new and maturing • Missing some functions • Test to understand strengths and constraints • Determine appropriate uses and benefits • Create usage guidelines • Use the appropriate SP language to match the requirements: • Control logic profile/complexity • Performance requirements • Developer skills • Architecture vision

30. Conclusions • Treat SQL PL as a programming language • Establish coding standards and guidelines • Develop skills • Apply performance engineering practices • Establish test and production infrastructure • Development procedures and tools • Testing and debugging • Deployment standards • Source code management • Production procedures and tools • System and performance monitoring procedures

31. Bibliography • “DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond,” IBM Redbook, SG24-7604-00, March, 2008 • “DB2 9 for z/OS Technical Update,” IBM Redbook, SG24-7330-00, June, 2007 • “DB2 for z/OS Stored Procedure Performance – Language Comparison,” Todd Munk and Gopal Krishnan, IBM,IOD Conference, October, 2008 • “DB2 for z/OS Stored Procedures,” Manogari Simanjuntak, IBM,IOD Conference, October, 2008 • “Leveraging zIIP and zAAP Speciality Engines with DB2 for z/OS,” Gopal Krishnan, IBM,IOD Conference, October, 2008 • “Maximizing Offload to zIIP Processors with DB2 9 for z/OS Native SQL Stored Procedures,” Richard Corrihons, IBM, April 14, 2008 • “Understanding SQL’s Stored Procedures: A Complete Guide to SQL/PSM,” Jim Melton, Morgan Kaufmann Publishers,1998 • “Supporting Procedural Constructs in Existing SQL Compilers,” Gene Fu, Jyh-Herng Chow, Nelson Mattos, and Brian Tran, Proceedings of the 1996 Conference of the Centre for Advanced Studies on Collaborative Research.