benefits & early experiences testing native sql stored procedures philip czachorowski fidelity...
TRANSCRIPT
Benefits & Early Experiences Testing Native SQL Stored Procedures
Philip CzachorowskiFidelity Investments [email protected]
11/19/09
New England DB2 Users Group Meeting
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
DB2Web Service
Return
Connect
Commit
Connect
Select
Select
Insert
Update
Update
z/OSDistributed System
Process
Process
Process
Process
Process
Commit
DB2Web Service
Return
Connect
Commit
Connect
Select
Select
Insert
Update
Update
z/OSDistributed System
Process
Process
Process
Process
Process
Commit
DB2Web Service
Return
Connect
Process
Execute SP
Process
Commit
Connect
Select
Select
Insert
Update
Update
z/OSDistributed System
Commit
DB2Web Service
Return
Connect
Process
Execute SP
Process
Commit
Connect
Select
Select
Insert
Update
Update
z/OSDistributed System
Commit
Stored procedures reduces database interactions and network latency.
Without Stored Procedures
With Stored Procedures
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
Characteristic External High Level Lang. COBOL/C
External High Level Lang.
Java
External SQL Stored
Procedures
Native SQL Stored
Procedures
Implementation COBOL/C Program
Java
Program
Generates C Program
Native SQL
(SQL PL)
Computational Complete
Yes Yes Limited Yes*
Integration with SQL
No No Limited Yes
Execution Container
WLM SPAS WLM SPAS WLM SPAS Application TCB or DDF**
Exploit Specialty
Engine
No Yes
zAAP
No Yes if DDF**
zIIP
Reference External Files
Yes Yes No No
*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.
DB2 Supported Stored Procedure Languages
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
Category Syntax NativeSQL
ExternalSQL
ExternalLang
OtherSource
Definition CREATE PROCEDURE Yes Yes Yes
LANGUAGE SQL Yes
Behavior CALLED ON NULL INPUT Yes Yes Yes
DETERMINISTIC Yes Yes Yes
MODIFIES/READ/CONTAINS SQL Yes Yes Yes
FOR UPDATE CLAUSE REQUIRED Yes
Authorization PACKAGE OWNER Yes Bind
QUALIFER Yes Bind
SQL PATH Yes Bind
VALIDATE RUN/BIND Yes Bind
Selected Create Stored Procedure Options
9
Native SQL SP Header – Part 2
Category Syntax NativeSQL
ExternalSQL
ExternalLang
OtherSource
Debugging DISALLOW/ALLOW DEBUG MODE` Yes Yes
WLM ENVIRONMENT FOR DEBUG Yes
Failure ASUTIME NO LIMIT/n Yes Yes Yes
STOP AFTER FAILURES or CONTINUE Yes Yes Yes
Format DATE FORMAT Yes
DECIMAL Yes
INHERIT SPECIAL REGISTERS Yes Yes Yes
ROUNDING DEC Yes Bind
TIME FORMAT Yes
Selected Create Stored Procedure Options
10
Native SQL SP Header – Part 3
Category Syntax NativeSQL
ExternalSQL
ExternalLang
OtherSource
Optimization DEFER PREPARE/NODEFER Yes Bind
OPTHINT Yes Bind
REOPT NONE/AWAYS/ONCE Yes Bind
WITH KEEP DYNAMIC Yes Bind
WITHOUT IMMEDIATE WRITE/WITH Yes Bind
UOW/Locking COMMIT ON RETURN NO/YES Yes Yes Yes
ISOLATION LEVEL Yes Bind
RELEASE AT COMMIT/AT DEALLOCATE
Yes Bind
Versioning VERSION Yes Bind
Selected Create Stored Procedure Options
11
Native SQL SP Body
Type Statement External SQL Procedures
Native SQL
(SQL PL)
Structure Assignment Yes Yes
Compound statement Only 1 level Nested statements
Conditional IF Yes Yes
CASE Yes Yes
Looping LOOP Yes Yes
REPEAT Yes Yes
WHILE Yes Yes
FOR No Yes
Control CALL Yes Yes
GOTO Yes Yes
ITERATIVE Yes Yes
LEAVE Yes Yes
RETURN Yes Yes
12
Compound Statements - Nesting
outer: BEGIN DECLARE SALARY DECIMAL(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: BEGINDECLARE SALARY DECIMAL(9,2);SET SALARY = inner2.SALARY;
END inner3;END IF;
END inner2;END outer
13
While Loop with Cursor Processing
emp2: BEGINDECLARE 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, BONUSFROM DSN8910.EMP;
DECLARE CONTINUE HANDLER FOR NOT FOUNDSET 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;
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 conditionsDECLARE 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
WLM SP Address Space
DBM1 Address Space
Control logicExec SQL SELECTControl logicExec SQL SELECTControl logicExec SQL SELECT
Application TCB
DBM1 Address Space
Call SPExec SQL Call SP SELECT
SELECT
SELECT
WLM TCB
DBM1 Address Space
Control logicSQL SELECTControl logicSQL SELECTControl logicSQL SELECT
Application TCB
Exec SQL Call SP
Simplified Diagram
External versus Native SQL SP Execution
19
CP CPU TIME 0.067091 0.066733 AGENT 0.067091 0.066733 NONNESTED 0.002490 0.002212 STORED PRC 0.064601 0.064521 UDF 0.000000 0.000000 TRIGGER 0.000000 0.000000 PAR.TASKS 0.000000 0.000000 IIPCP CPU 0.001050 N/A CP CPU TIME 0.070599 0.070151 AGENT 0.070599 0.070151 NONNESTED 0.002460 0.002012 STORED PRC 0.068139 0.068139 UDF 0.000000 0.000000 TRIGGER 0.000000 0.000000 PAR.TASKS 0.000000 0.000000 IIPCP CPU 0.038873 N/A
COBOL Stored
Procedure
Native SQL Stored
Procedure
DB2 Performance MonitoringCLASS 1 CLASS 2
20
DB2 Subsystem
DDFAddressSpace
WLM SP Address Space
DBM1 Address Space
SRB
zIIP Eligible
TCB
Call External SP
SRB TCB
Call Native SQLSP
SRBUnder DDF
SRB
Call JavaSP
SRBDRDA
TCB
zAAP Eligible
General Processor
Call
Call COBOLor CSP Call
andSchedule
DRDA
DRDA
DRDA
TCB
TCB
Under ApplTCB
TCB
Call External SP
TCB
Call Native SQL SP
TCB
Call JavaSP
TCB
Call COBOLor C SP
TCB
Application Address
Space
Call
zIIP and zAAP Exploitation
Calland
Schedule
Calland
Schedule
Calland
Schedule
Calland
Schedule
Calland
Schedule
21
Performance Characteristics
Feature Advantages Favorable Workloads
SP executes internal to DB2 in DBM1
• Avoids overhead of scheduling SP in WLM
• Avoids API cost for individual EXEC SQL
• SP with many short executing SQL statements
• Reasonable complexity and number of control statements
zIIP Eligible • Executes on lower cost specialty engines
• SP executed via DDF will be zIPP eligible
Feature Disadvantage Unfavorable Workloads
Control code is executed as operators
• Higher CPU cost than a compiled language
• Large number of control statements, complex logic
• String manipulation
• 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
Benefits Attributes
Faster development, increased productivity
• Reduces development effort–Concise language, extended SQL support–All code maintained in DBMS
• Eliminates language impedance mismatch–Matched data types, integrated with SQL
• Simplified deployment–No Compiler, no Bind, no WLM
Improved performance and lower CPU cost
• Executes in DB2 engine (DBM1) –Avoids overhead of scheduling WLM task –Should perform better than External SQL SP
• zIIP eligible when executed distributed using DDF
Portable code • Implementation of the SQL PSM standard• Supported by other RDBMS products, like DB2 for
LUW
Native SQL SP Benefits
23
Native SQL SP High Level Language SP
• Reasonable number and complexity of control statements
• SP with many short executing SQL statements with some control statements
• Opportunity to exploit zIIP specialty engines
• Code that exceeds the capabilities of SQL PL
• Large programs in terms of lines of code or complex code
• String manipulation, functions, CPU intensive code
• References to OS files or other non-DB2 accessible resources
• Code structuring options, like grouping code• Collections, Arrays• Local functions• Advanced types • Compiler directive statements• Other ISO/ANSI SQL PSM features
Suggested Enhancements to Native SQL SP
Appropriate Code Usage Guidelines
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.