Foreword |
|
xxi | |
|
|
1 | (10) |
|
|
2 | (1) |
|
History of Stored Procedures |
|
|
2 | (1) |
|
A Brief Introduction to Stored Procedures, Triggers, and Functions |
|
|
3 | (1) |
|
What's New in the Second Edition |
|
|
4 | (3) |
|
DB2's SQL Procedural Language (SQL PL) |
|
|
5 | (1) |
|
Other Stored Procedure Languages |
|
|
5 | (1) |
|
SQL PL Development Tools---DB2 Development Center |
|
|
6 | (1) |
|
|
7 | (4) |
|
|
7 | (2) |
|
|
9 | (1) |
|
|
9 | (1) |
|
|
9 | (2) |
|
Basic SQL Procedure Structure |
|
|
11 | (26) |
|
The Create Procedure Statement |
|
|
11 | (9) |
|
|
13 | (1) |
|
|
14 | (2) |
|
|
16 | (1) |
|
|
17 | (1) |
|
Contains SQL, Reads SQL Data, Modifies SQL Data |
|
|
18 | (1) |
|
Deterministic or Not Deterministic |
|
|
18 | (1) |
|
|
18 | (1) |
|
Inherit Special Registers |
|
|
18 | (1) |
|
Old Savepoint Level, New Savepoint Level |
|
|
18 | (1) |
|
|
19 | (1) |
|
External Action or No External Action |
|
|
19 | (1) |
|
|
19 | (1) |
|
|
20 | (1) |
|
The SQL Procedure Body Structure |
|
|
20 | (8) |
|
|
21 | (1) |
|
|
22 | (1) |
|
|
23 | (1) |
|
|
24 | (4) |
|
Bringing It All Together Example |
|
|
28 | (2) |
|
DB2 UDB for iSeries Considerations |
|
|
30 | (2) |
|
|
31 | (1) |
|
|
32 | (1) |
|
|
32 | (1) |
|
DB2 UDB for zSeries Considerations |
|
|
32 | (4) |
|
|
34 | (1) |
|
|
34 | (1) |
|
No Collid or Collid collection-id |
|
|
34 | (1) |
|
|
35 | (1) |
|
|
35 | (1) |
|
|
35 | (1) |
|
|
35 | (1) |
|
|
35 | (1) |
|
|
35 | (1) |
|
|
36 | (1) |
|
Stop After System Default Failures or Continue After Failure |
|
|
36 | (1) |
|
|
36 | (1) |
|
Overview of SQL PL Language Elements |
|
|
37 | (26) |
|
|
38 | (3) |
|
Valid DB2 Built-In Data Types and Their Value Ranges |
|
|
38 | (3) |
|
|
41 | (1) |
|
Choosing Proper Data Types |
|
|
42 | (1) |
|
Working with User-Defined Distinct Types |
|
|
43 | (2) |
|
|
45 | (2) |
|
Working with Dates and Times |
|
|
45 | (1) |
|
|
46 | (1) |
|
Working with Generated Columns |
|
|
47 | (2) |
|
Working with Identity Columns and Sequence Objects |
|
|
49 | (12) |
|
|
50 | (7) |
|
|
57 | (4) |
|
Platform Portability Considerations |
|
|
61 | (1) |
|
|
62 | (1) |
|
Using Flow of Control Statements |
|
|
63 | (30) |
|
|
64 | (6) |
|
Not Atomic Compound Statement |
|
|
66 | (1) |
|
Atomic Compound Statement |
|
|
67 | (1) |
|
|
68 | (2) |
|
|
70 | (6) |
|
|
70 | (3) |
|
|
73 | (3) |
|
|
76 | (7) |
|
|
77 | (2) |
|
|
79 | (2) |
|
|
81 | (1) |
|
|
82 | (1) |
|
Transfer of Control Statements |
|
|
83 | (9) |
|
|
83 | (2) |
|
|
85 | (2) |
|
|
87 | (2) |
|
|
89 | (1) |
|
|
90 | (1) |
|
|
91 | (1) |
|
|
92 | (1) |
|
Understanding and Using Cursors and Result Sets |
|
|
93 | (32) |
|
Using Cursors in SQL Procedures |
|
|
94 | (4) |
|
|
98 | (2) |
|
|
100 | (2) |
|
Selecting Data from Update, Insert, or Delete Statements |
|
|
102 | (4) |
|
Cursor Behavior with Commit/Rollback |
|
|
106 | (6) |
|
Save Points Within Procedures |
|
|
110 | (2) |
|
Using Cursors to Return Result Sets |
|
|
112 | (3) |
|
Returning Multiple Result Sets |
|
|
115 | (1) |
|
|
116 | (7) |
|
|
116 | (1) |
|
|
116 | (3) |
|
Controlling Locking in DB2 LUW |
|
|
119 | (2) |
|
Controlling Locking in DB2 UDB for iSeries |
|
|
121 | (1) |
|
Controlling Locking in DB2 UDB for zSeries |
|
|
122 | (1) |
|
DB2 UDB for iSeries Considerations |
|
|
123 | (1) |
|
Declare Cursor Statement in DB2 UDB for iSeries |
|
|
123 | (1) |
|
|
124 | (1) |
|
|
125 | (50) |
|
Basic Error Checking: SQLCODE and SQLSTATE |
|
|
125 | (2) |
|
|
127 | (7) |
|
Custom Errors and Error Messages |
|
|
134 | (15) |
|
Using Signal to Force the Invocation of a Handler |
|
|
134 | (4) |
|
Using Resignal to Force the Invocation of a Handler |
|
|
138 | (3) |
|
|
141 | (2) |
|
|
143 | (2) |
|
|
145 | (4) |
|
Processing Results from Called Procedures |
|
|
149 | (3) |
|
|
152 | (3) |
|
DB2 UDB for iSeries Considerations |
|
|
155 | (9) |
|
Signal and Resignal Statements |
|
|
155 | (2) |
|
|
157 | (1) |
|
|
158 | (6) |
|
DB2 UDB for zSeries Considerations |
|
|
164 | (9) |
|
|
164 | (1) |
|
Get Diagnostics Statement |
|
|
165 | (5) |
|
|
170 | (3) |
|
|
173 | (2) |
|
|
175 | (18) |
|
Prepare and Execute: The Two Phases of Any SQL Statement |
|
|
176 | (1) |
|
Dynamic SQL Versus Static SQL |
|
|
176 | (1) |
|
Restrictions and Considerations |
|
|
177 | (3) |
|
Using Dynamic SQL with Execute Immediate |
|
|
180 | (2) |
|
Escaping Single Quotes (') |
|
|
182 | (1) |
|
Reusing Dynamic SQL Statements with PREPARE and EXECUTE |
|
|
182 | (4) |
|
Using Dynamic SQL in Cursors |
|
|
186 | (3) |
|
|
189 | (3) |
|
Authorization Consideration |
|
|
192 | (1) |
|
|
192 | (1) |
|
|
193 | (22) |
|
Basic Nested SQL Procedures |
|
|
194 | (1) |
|
Passing Parameters Between Nested SQL Procedures |
|
|
195 | (1) |
|
Returning Values from Nested SQL Procedures |
|
|
195 | (2) |
|
Returning Result Sets from Nested SQL Procedures |
|
|
197 | (11) |
|
Returning Result Sets to the Client |
|
|
199 | (2) |
|
Returning Result Sets to the Caller |
|
|
201 | (2) |
|
Receiving Result Sets as a Caller |
|
|
203 | (3) |
|
Receiving Multiple Result Sets as a Caller |
|
|
206 | (2) |
|
Receiving Results from a Procedure in a Trigger |
|
|
208 | (3) |
|
|
211 | (2) |
|
|
211 | (2) |
|
|
213 | (1) |
|
|
213 | (2) |
|
User-Defined Functions and Triggers |
|
|
215 | (56) |
|
The Create Function Statement |
|
|
216 | (6) |
|
Function Name and Parameters |
|
|
217 | (2) |
|
|
219 | (1) |
|
|
219 | (1) |
|
|
219 | (1) |
|
Deterministic or Not Deterministic |
|
|
220 | (1) |
|
External Action or No External Action |
|
|
220 | (1) |
|
Contains SQL, Reads SQL Data, Modifies SQL Data |
|
|
220 | (1) |
|
|
221 | (1) |
|
|
221 | (1) |
|
Inherit Special Registers (LUW and iSeries Only) |
|
|
221 | (1) |
|
|
221 | (1) |
|
|
221 | (1) |
|
User-Defined Functions by Examples |
|
|
222 | (13) |
|
A Simple Scalar UDF Example |
|
|
222 | (1) |
|
A Complex Scalar UDF Example |
|
|
223 | (3) |
|
A Table UDF Example (LUW and iSeries) |
|
|
226 | (4) |
|
Invoking SQL Procedures in UDFs (LUW and iSeries Only) |
|
|
230 | (5) |
|
The Create Trigger Statement |
|
|
235 | (5) |
|
|
237 | (1) |
|
Before, After, or Instead Of |
|
|
237 | (1) |
|
Insert, Delete, or Update |
|
|
238 | (1) |
|
|
239 | (1) |
|
For Each Row or For Each Statement |
|
|
239 | (1) |
|
|
240 | (1) |
|
|
240 | (1) |
|
|
240 | (16) |
|
|
240 | (2) |
|
|
242 | (4) |
|
A Complex After Trigger Example |
|
|
246 | (1) |
|
An Instead Of Trigger Example (for LUW Only) |
|
|
247 | (3) |
|
A Comparison of View Triggers and Table Triggers (LUW only) |
|
|
250 | (2) |
|
Invoking UDFs and SQL Procedures from Triggers |
|
|
252 | (4) |
|
Considerations for Invoking SQL Procedures from UDFs and Triggers |
|
|
256 | (7) |
|
|
256 | (1) |
|
Transaction Control in SQL Procedures |
|
|
257 | (5) |
|
Table Read or Write Conflict in SQL Procedures on LUW |
|
|
262 | (1) |
|
DB2 for iSeries Considerations |
|
|
263 | (2) |
|
DB2 for zSeries Considerations |
|
|
265 | (3) |
|
The Create Function Statement |
|
|
265 | (1) |
|
|
266 | (2) |
|
Invoking UDFs and SQL Procedures from Triggers |
|
|
268 | (1) |
|
|
268 | (1) |
|
|
268 | (3) |
|
Leveraging DB2 Application Development Features |
|
|
271 | (28) |
|
|
272 | (9) |
|
Combining Select with Insert, Update, and Delete |
|
|
272 | (9) |
|
Declared Global Temporary Tables |
|
|
281 | (9) |
|
Introduction to Temporary Tables |
|
|
281 | (1) |
|
Creating the Environment for Temporary Tables |
|
|
281 | (1) |
|
Declaring Global Temporary Tables |
|
|
282 | (4) |
|
Using Temporary Tables in SQL Procedures |
|
|
286 | (1) |
|
Sharing Temporary Tables Between Multiple Procedures |
|
|
287 | (3) |
|
|
290 | (1) |
|
Created Global Temporary Tables |
|
|
290 | (1) |
|
|
290 | (5) |
|
Introduction to Application Save Points |
|
|
291 | (1) |
|
Using Save Points in SQL Procedures |
|
|
292 | (3) |
|
|
295 | (3) |
|
|
298 | (1) |
|
Deploying SQL Procedures, Functions, and Triggers |
|
|
299 | (26) |
|
Deploying on Linux, UNIX, and Windows |
|
|
299 | (15) |
|
Deploying SQL Procedures Using DDL |
|
|
299 | (6) |
|
|
305 | (3) |
|
|
308 | (1) |
|
Deploying Using Get Routine and Put Routine |
|
|
309 | (3) |
|
Deploying Using Older Releases of DB2 |
|
|
312 | (1) |
|
|
313 | (1) |
|
Backup and Recovery Considerations |
|
|
313 | (1) |
|
Deployment Considerations for DB2 for zSeries |
|
|
314 | (2) |
|
|
314 | (1) |
|
Deploying Functions and Triggers |
|
|
314 | (1) |
|
Deployment Considerations Based on Your C Compiler Requirements |
|
|
315 | (1) |
|
Common Deployment Considerations for LUW and zSeries |
|
|
316 | (3) |
|
Deploying from One Database to Another |
|
|
316 | (3) |
|
Deploying in DB2 UDB for iSeries |
|
|
319 | (5) |
|
Using the Development Center |
|
|
319 | (1) |
|
Using iSeries Navigator and CL Commands in iSeries |
|
|
319 | (5) |
|
|
324 | (1) |
|
|
325 | (30) |
|
Performance Considerations for LUW |
|
|
325 | (12) |
|
The Configuration Advisor |
|
|
325 | (3) |
|
Monitoring SQL Performance |
|
|
328 | (3) |
|
Using Explain to Analyze Access Plans |
|
|
331 | (2) |
|
|
333 | (1) |
|
Large Object (LOB) Considerations |
|
|
334 | (1) |
|
Temporary Tables Considerations |
|
|
334 | (2) |
|
Ongoing Maintenance to Keep Performance at Its Peak |
|
|
336 | (1) |
|
DB2 for iSeries Considerations |
|
|
337 | (11) |
|
|
338 | (1) |
|
Performance Monitors and Commands |
|
|
338 | (5) |
|
Query Optimizer and Access Plans |
|
|
343 | (3) |
|
Variable Length Columns and Large Objects |
|
|
346 | (1) |
|
|
347 | (1) |
|
DB2 for zSeries Considerations |
|
|
348 | (5) |
|
|
348 | (1) |
|
The Performance Expert Tool |
|
|
349 | (1) |
|
Improving Response Time and Throughput |
|
|
350 | (2) |
|
|
352 | (1) |
|
Limit Resources for a Stored Procedure |
|
|
352 | (1) |
|
Considerations for Stored Procedures in a Distributed Environment |
|
|
353 | (1) |
|
|
353 | (2) |
|
|
355 | (16) |
|
Table and Index Best Practices |
|
|
356 | (2) |
|
|
356 | (1) |
|
Temporary Table Considerations |
|
|
356 | (1) |
|
|
357 | (1) |
|
Best Practices for Easier Code Maintenance |
|
|
358 | (2) |
|
|
358 | (1) |
|
|
359 | (1) |
|
Grouping SQL Procedures by Schema |
|
|
359 | (1) |
|
|
359 | (1) |
|
Return Values Versus Out Parameters |
|
|
359 | (1) |
|
|
359 | (1) |
|
|
360 | (1) |
|
Best Practices for Performance |
|
|
360 | (6) |
|
|
360 | (1) |
|
|
360 | (1) |
|
|
360 | (1) |
|
|
361 | (1) |
|
|
362 | (2) |
|
Using a Single Insert to Replace Multiple Insert Statements |
|
|
364 | (1) |
|
|
364 | (1) |
|
Deterministic Versus Not Deterministic |
|
|
364 | (1) |
|
|
365 | (1) |
|
|
365 | (1) |
|
|
366 | (1) |
|
|
366 | (1) |
|
|
366 | (1) |
|
Optimizing Result Set Returns |
|
|
366 | (1) |
|
Minimize Result Set Columns |
|
|
367 | (1) |
|
Precompile Options on iSeries |
|
|
367 | (2) |
|
|
369 | (2) |
|
Appendix A Getting Started with DB2 |
|
|
371 | (40) |
|
Getting Started with DB2 UDB for LUW |
|
|
371 | (19) |
|
Launching the DB2 Command Window |
|
|
371 | (1) |
|
Launching the DB2 Command Line Processor |
|
|
372 | (2) |
|
Overview of DB2 Architecture |
|
|
374 | (1) |
|
|
375 | (1) |
|
|
375 | (1) |
|
Executing SQL in the DB2 Command Window |
|
|
376 | (2) |
|
Executing SQL in DB2 Interactive Mode |
|
|
378 | (1) |
|
Configuring the DB2 Environment |
|
|
379 | (4) |
|
Understanding Buffer Pools |
|
|
383 | (1) |
|
Working with Table Spaces |
|
|
384 | (4) |
|
|
388 | (2) |
|
Getting Started with DB2 UDB for iSeries |
|
|
390 | (11) |
|
|
391 | (1) |
|
Overview of the DB2 UDB for iSeries Architecture |
|
|
392 | (4) |
|
Executing SQL in DB2 UDB for iSeries |
|
|
396 | (2) |
|
|
398 | (1) |
|
|
399 | (2) |
|
Getting Started with DB2 UDB for zSeries |
|
|
401 | (8) |
|
Overview of the BD2 UDB for zSeries Architecture |
|
|
402 | (1) |
|
|
403 | (1) |
|
Interacting with DB2 for zSeries |
|
|
404 | (5) |
|
|
409 | (2) |
|
Appendix B Inline SQL PL for DB2 UDB for Linux, UNIX, and Windows |
|
|
411 | (8) |
|
Dynamic Compound Statement |
|
|
411 | (4) |
|
|
415 | (1) |
|
Choosing Between Dynamic Compound Statements and SQL Procedures |
|
|
416 | (1) |
|
|
417 | (2) |
|
Appendix C Building from the Command Line |
|
|
419 | (12) |
|
Configuring the Build Environment |
|
|
419 | (2) |
|
|
421 | (3) |
|
Creating a DB2 Command-Line Processor Script |
|
|
421 | (3) |
|
Building SQL Procedures in DB2 UDB for iSeries |
|
|
424 | (3) |
|
Building SQL Procedures in DB2 for zSeries |
|
|
427 | (2) |
|
|
427 | (1) |
|
Methods to Build a DB2 for zSeries SQL Procedure |
|
|
428 | (1) |
|
|
429 | (2) |
|
Appendix D Using the DB2 Development Center |
|
|
431 | (32) |
|
Installing the DB2 Development Center |
|
|
432 | (1) |
|
|
432 | (2) |
|
|
434 | (1) |
|
Using the Development Center for the First Time |
|
|
435 | (6) |
|
|
441 | (1) |
|
Customizing the Development Center |
|
|
441 | (6) |
|
|
447 | (1) |
|
Stored Procedure Run Settings |
|
|
448 | (1) |
|
Debugging Stored Procedures |
|
|
449 | (3) |
|
Debugging Nested Stored Procedures |
|
|
452 | (2) |
|
|
454 | (1) |
|
Importing Stored Procedures |
|
|
455 | (2) |
|
DB2 for iSeries Considerations |
|
|
457 | (1) |
|
DB2 for zSeries Considerations |
|
|
457 | (1) |
|
|
458 | (1) |
|
Developing SQL Stored Procedures from the Development Center |
|
|
459 | (1) |
|
The Project and Server Views |
|
|
459 | (2) |
|
Debugging DB2 for zSeries SQL Stored Procedures |
|
|
461 | (1) |
|
|
461 | (2) |
|
Appendix E Security Considerations in SQL Procedures |
|
|
463 | (10) |
|
|
463 | (4) |
|
Privileges Required by Developers |
|
|
464 | (2) |
|
Privileges Required by Users |
|
|
466 | (1) |
|
Using Stored Procedures in a Production Environment |
|
|
467 | (1) |
|
DB2 for zSeries Considerations |
|
|
468 | (1) |
|
Dynamicrules Bind/Rebind Option Value |
|
|
468 | (1) |
|
The Package's Runtime Environment |
|
|
468 | (1) |
|
|
469 | (1) |
|
|
469 | (1) |
|
Common Attribute Values for the Define, and Invoke Behaviors |
|
|
470 | (1) |
|
Simplifying Authorization |
|
|
470 | (1) |
|
|
470 | (3) |
|
|
473 | (14) |
|
Creating the Sample Database on LUW |
|
|
473 | (1) |
|
Creating the Sample Database on iSeries |
|
|
474 | (1) |
|
Creating the Sample Database on zSeries |
|
|
475 | (1) |
|
Sample Database DDL: Script 1 sampleDDL_1.db2 |
|
|
475 | (7) |
|
DDL for Additional Database Objects: Script #2 sampleDDL_2.db2 |
|
|
482 | (5) |
|
Appendix G Additional Resources |
|
|
487 | (8) |
|
|
487 | (1) |
|
|
487 | (1) |
|
|
488 | (1) |
|
|
488 | (1) |
|
|
488 | (1) |
|
|
488 | (1) |
|
|
489 | (1) |
|
|
489 | (1) |
|
|
489 | (3) |
|
|
489 | (2) |
|
IBM Solution Partnership Centers (SPCs) |
|
|
491 | (1) |
|
|
491 | (1) |
|
|
492 | (1) |
|
|
492 | (1) |
|
DB2 FixPaks and Downloads |
|
|
493 | (1) |
|
Email Services and Periodicals |
|
|
493 | (1) |
|
User Groups and Conferences |
|
|
494 | (1) |
|
Appendix H Sample Application Code |
|
|
495 | (26) |
|
Receiving Result Sets in Java |
|
|
495 | (9) |
|
Receiving Result Sets in a Java Application |
|
|
495 | (6) |
|
Receiving Result Sets in a Java Stored Procedure |
|
|
501 | (3) |
|
Receiving Result Sets in a C or C++ Application |
|
|
504 | (4) |
|
Receiving Multiple Result Sets in Java |
|
|
508 | (7) |
|
Receiving Multiple Result Sets in a Java Application |
|
|
508 | (3) |
|
Receiving Multiple Result Sets in a Java Stored Procedure |
|
|
511 | (4) |
|
Receiving Multiple Result Sets in a C or C++ Application |
|
|
515 | (6) |
Index |
|
521 | |