IBM Software Group
DB2 for z/OS Stored Procedure Performance – Language / API Comparison GopalNumber Krishnan, Todd Session TDZ – 2296A
Munk
DB2 for z/OS Performance IBM Silicon Valley Lab, San Jose, CA December 2008
© IBM Corporation
IBM Software Group
Stored Procedure Overview Stored Procedure Performance Checklist Customer reasons for the study Languages/API’s to be discussed Workload & Environment description Pros and cons of each language/API Performance and Cost comparison
© IBM Corporation
IBM Software Group
It is a user-written program that can be called by an application with an SQL CALL statement. It is a compiled program that is stored at a DB2 server, and can execute SQL statements. Stored procedures can be called: – locally (on the same system where the application runs) and – remotely (from a different system). • Reducing the traffic of information across the communication network • Splitting the application logic and encouraging an even distribution of the computational workload • Providing an easy way to call a remote program © IBM Corporation
IBM Software Group
Processing without & with Stored Procedures
The same SQL previously executed by the client has been stored on the server and is called by the client whenever necessary. The invocation is treated as a regular external call: •The application waits for te stored procedure to terminate • Parameters can be passed back and forth
© IBM Corporation
IBM Software Group
Modularity in application development Data will be processed always in a consistent way according to the rules defined in the stored procedure Reduced network traffic for distributed applications – Typical application requires two trips across the network for each SQL statement – Grouping SQL statements into a stored procedure results in two trips across the network for each group of statement, resulting in better performance for applications Improved application security – Sensitive business logic runs on the DB2 server – End users do not need table privilege © IBM Corporation
IBM Software Group
Access to features that exist only on the server: – Stored procedures can have access to commands that run only on the server. – They might have the advantages of increased memory and disk space on server machines. – They can access any additional software installed on the server.
Enforcement of business rules: – You can use stored procedures to define business rules that are common to several applications. – This is another way to define business rules, in addition to using constraints and triggers. © IBM Corporation
IBM Software Group
Application integration solutions: – You can use stored procedures to easily access non-DB2 resources. – With the use of WebSphere® MQ, you can coordinate accesses to multiple data and platforms.
Cost of ownership reduction – DRDA® activity is a candidate for zIIP redirect. – Stored procedures written in Java can take advantage of zAAP engines – Native SQL procedures have richer SQL functions and remote native SQL procedures, running as enclaves in DBM1 address space, are candidate for zIIP redirect with DB2 9 for z/OS.
© IBM Corporation
IBM Software Group
What is common to all Stored Procedures called via DRDA ? Portion of distributed workload that comes in via DRDA is eligible for zIIP redirect. This includes: – Call statement processing – Result set processing – Commit processing
For WLM managed stored procedures: – SQL processing runs under a TCB hence not eligible for zIIP redirect.
For Native SQL procedures: – Execution is under an enclave SRB, thus zIIP eligible © IBM Corporation
IBM Software Group
Stored Procedure Performance Checklist Consider overhead of each invocation –
Recommend more SQL Calls per Stored Procedure
Avoid metadata SPs Use the SP authorization cache (zparm CACHERAC) Don’t println() / DISPLAY in production Use PROGRAM TYPE SUB Separate WLM Enviornments for different LE runtime options Use STAY RESIDENT YES Use SECURITY DB2 No more than 512 SPs in one WLMENV Java Considerations: Don’t use JSPDEBUG in production Make sure the JVM is not destroyed between invocations Use a non-resettable JVM © IBM Corporation
IBM Software Group
External high level language procedures – COBOL, PL/I, C, C++, Assembler, REXX, and Java
External SQL language procedures Native SQL language stored procedures – Introduced by DB2 9 for z/OS
© IBM Corporation
IBM Software Group
Some customer reasons for looking at alternative languages To leverage potential cost savings of zIIP and zAAP assist processors. Productivity and manageability improvements available with new enterprise level tooling. Redesigning some applications and want to investigate viability of moving to a different language during redesign. Specific language programming skills dwindling and hard to acquire/replace. All of the above. © IBM Corporation
IBM Software Group
What Languages/API’s will be discussed ? COBOL C/C++ JDBC SQLJ External SQL Native SQL
© IBM Corporation
IBM Software Group
Performance Evaluation Environment z/OS 1.9 DB2 9 for z/OS IBM Driver for SQLJ and JDBC Type 4 clients 3 CP’s, 2 zIIPs, 2 zAAPs 8 GB’s of memory COBOL – Enterprise COBOL for z/OS compiler V4R1 External SQL/C/C++ – z/OS XL C/C++ compiler JDBC/SQLJ – JDK 1.4.2, non-resetable JVM, NUMTCB=20 © IBM Corporation
IBM Software Group
The IBM Relational Warehouse workload (IRWW) IRWW is an OLTP workload consisting of 7 transactions in support of a product warehouse, customer order, and order delivery system. Each transaction consists of roughly 25 DML on average with 7 of those being Insert/Update/Delete. All Stored Procedures are written using static SQL with the exception of JDBC.
© IBM Corporation
IBM Software Group
IRWW workload used for Comparison 7 OLTP transactions DML distribution for the workload : SQL DML AVERAGE -------- -------SELECT 3.65 INSERT 2.80 UPDATE 3.63 MERGE 0.00 DELETE 0.20 DESCRIBE DESC.TBL PREPARE OPEN FETCH CLOSE DML-ALL
0.00 0.00 0.00 4.41 8.50 2.53 25.72 © IBM Corporation
IBM Software Group
© IBM Corporation
IBM Software Group
COBOL Pros – Excellent throughput performance. – Robust development language. – Low billable cost.
Cons – Runs as WLM managed. – Not eligible for zAAP redirect. – Future availability of COBOL development resources. © IBM Corporation
IBM Software Group
C/C++ Pros – Best performance. – Robust development language. – Low billable cost • Lower than COBOL
– Future availability of C/C++ development resources – Portable
Cons – Runs as WLM managed – Not eligible for zAAP redirect. © IBM Corporation
IBM Software Group
SQLJ Pros –
zAAP specialty engine eligible.
–
Robust development language.
–
Future availability of Java development resources.
–
Portable.
–
Wide variety of development tools.
–
Easy to code.
–
Benefits of Static SQL Cons
–
Runs as WLM managed.
–
Higher billable cost. •
Above COBOL © IBM Corporation
IBM Software Group
JDBC Pros – zAAP specialty engine eligible. – Robust development language. – Future availability of Java development resources. – Portable. – Wide variety of development tools. – Easy to code • More complex than SQLJ
Cons – Runs as WLM managed. – Highest billable cost. • Above External SQL.
– Dynamic SQL. © IBM Corporation
IBM Software Group
External SQL Pros – Portable. – Easy to code. – Can be migrated to native SQL in DB2 9 for z/OS
Cons – Runs as WLM managed. – Not a robust development language. – Not eligible for zAAP redirect. – Higher billable cost. • Above SQLJ © IBM Corporation
IBM Software Group
Native SQL Pros – Higher zIIP redirect. – Portable. – Easy to code. – Excellent performance. – Lowest billable cost. – Enhanced SQL support : • FOR Loops, nested compound statements • More data types (BIGINT,BINARY,VARBINARY,DECFLOAT) • Versioning support.
Cons – Not a robust development language. – Requires DB2 9 for z/OS – Not eligible for zAAP redirect. © IBM Corporation
IBM Software Group
What is the difference between External and Native SQL procedures? External SQL – Parsed and translated into C. – Runs as a C stored procedure. – WLM managed.
Native SQL – Broken down into runtime structures like any other SQL statement. – Does not run in a WLM environment. – Runs under DBM1 enclave SRB, thus zIIP eligible. © IBM Corporation
IBM Software Group
When is it good to use SQL language? When is it bad? Good when … – SQL intensive – Contains minimal application logic – Lower billable cost and productivity are the most important priorities.
Bad when … – Contains significant amount of apllication logic – Executes math, string manipulation functions – Many IF/WHILE/CASE/REPEAT statements © IBM Corporation
IBM Software Group
Language Pros/Cons Comparison Summary
No zAAP
Supported in DB2 Version
No
Specialty Engine
Low
Runs in the DB Engine
High
Billable Cost
Yes
Throughput Performanc e
Robust Language
COBOL
V8,V9
Some zIIP C/C++
SQLJ
JDBC
External SQL
Native SQL
Yes
Yes
Yes
No
No
Above COBOL
Below COBOL
No
Below native SQL
Above COBOL
No
Below External SQL
Above External SQL
No
Below SQLJ
Above SQLJ
No
Below COBOL
Lowest
Yes
No zAAP
V8,V9
Some zIIP
zAAP
V8,V9
Some zIIP
zAAP
V8,V9
Some zIIP No zAAP
V8,V9
Some zIIP
Significant
V9
zIIP © IBM Corporation
IBM Software Group
© IBM Corporation
IBM Software Group
Language / API CPU Cost comparison for IRWW Workload Language/API
Base CPU/Tran Cost
Billable CPU/Tran Cost after zIIP and/or zAAP redirect
COBOL Stored Proc
1X (BASE)
0.88x (Some zIIP)
C Stored Proc
0.95x
0.83x (Some zIIP)
SQLJ Stored Proc
1.7x
1.15x (zAAP+ some zIIP)
JDBC Stored Proc
2.95x
1.76x (zAAP+ some zIIP)
External SQL Stored Proc
1.62x
1.49x (Some zIIP)
Native SQL Stored Proc
1.14x
0.65x
(Significant zIIP)
© IBM Corporation
IBM Software Group
© IBM Corporation
IBM Software Group
Choose the language based on your priorities and needs… Priorities – Throughput Performance – Billable CPU Cost – Productivity – Future availability of development resources
© IBM Corporation
IBM Software Group
Suggested languages based on top two priorities Native SQL,C, COBOL Cost / Productivity
Performance / Cost
C, SQLJ
Native SQL, SQLJ
Dev. Resources / Performance
Productivity / Dev. Resources
SQLJ, JDBC, C
© IBM Corporation
IBM Software Group
Stored Procedure
Tool Links:
Rational Developer for System Z – http://www-01.ibm.com/software/awdtools/rdz/about/?S_CMP=wspace
IBM Data Studio V1.2 – http://www-01.ibm.com/software/data/studio/
© IBM Corporation
IBM Software Group
Disclaimer © Copyright IBM Corporation [current year]. All rights reserved. U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. THE INFORMATION CONTAINED IN THIS PRESENTATION IS PROVIDED FOR INFORMATIONAL PURPOSES ONLY. WHILE EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND ACCURACY OF THE INFORMATION CONTAINED IN THIS PRESENTATION, IT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED. IN ADDITION, THIS INFORMATION IS BASED ON IBM’S CURRENT PRODUCT PLANS AND STRATEGY, WHICH ARE SUBJECT TO CHANGE BY IBM WITHOUT NOTICE. IBM SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES ARISING OUT OF THE USE OF, OR OTHERWISE RELATED TO, THIS PRESENTATION OR ANY OTHER DOCUMENTATION. NOTHING CONTAINED IN THIS PRESENTATION IS INTENDED TO, NOR SHALL HAVE THE EFFECT OF, CREATING ANY WARRANTIES OR REPRESENTATIONS FROM IBM (OR ITS SUPPLIERS OR LICENSORS), OR ALTERING THE TERMS AND CONDITIONS OF ANY AGREEMENT OR LICENSE GOVERNING THE USE OF IBM PRODUCTS AND/OR SOFTWARE.
IBM, the IBM logo, ibm.com, and DB2 are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both. If these and other IBM trademarked terms are marked on their first occurrence in this information with a trademark symbol (® or ™), these symbols indicate U.S. registered or common law trademarks owned by IBM at the time this information was published. Such trademarks may also be registered or common law trademarks in other countries. A current list of IBM trademarks is available on the Web at “Copyright and trademark information” at www.ibm.com/legal/copytrade.shtml
© IBM Corporation
IBM Software Group
© IBM Corporation
