book

SQL Procedures, Triggers, and Functions on IBM DB2 for i

Name: SQL Procedures, Triggers, and Functions on IBM DB2 for i
ISBN: 9780738441641

by Jim Bainbridge, Hernando Bedoya, Rob Bestgen, Mike Cain, Dan Cruikshank, Jim Denton, Doug Mack, Tom Mckinley, Simona Pacchiarini

April 2016

Intermediate to advanced

338 pages

9h 48m

English

IBM Redbooks

Read now

Unlock full access

Front cover
Notices
Trademarks
IBM Redbooks promotions
Preface
AuthorsNow you can become a published author, too!Comments welcomeStay connected to IBM Redbooks
Chapter 1. Introduction to data-centric programming
1.1 Data-centric programming1.2 Database engineering
Chapter 2. Introduction to SQL Persistent Stored Module
2.1 Introduction2.2 System requirements and planning2.3 Structure of an SQL PSM program2.4 SQL control statements2.4.1 Assignment statement2.4.2 Conditional control2.4.3 Iterative control2.4.4 Calling procedures2.4.5 Compound SQL statement2.5 Dynamic SQL in PSM2.5.1 DECLARE CURSOR, PREPARE, and OPEN2.5.2 PREPARE then EXECUTE2.5.3 EXECUTE IMMEDIATE statement2.6 Error handling2.6.1 The basic database error indicators2.6.2 Conditions and handlers2.6.3 GET DIAGNOSTICS2.6.4 SIGNAL and RESIGNAL2.6.5 RETURN statement2.6.6 Direct SQLSTATE and SQLCODE usage2.6.7 Error handling in nested compound statements2.7 Transaction management in procedures2.7.1 Transaction management example
Chapter 3. SQL fundamentals
3.1 SQL concepts3.1.1 Schemas and libraries3.1.2 Unqualified object names3.1.3 SQL PATH3.1.4 Global variables3.2 Common information for SQL routines and triggers3.2.1 Routine and trigger creation process3.2.2 IBM i names for generated SQL objects3.2.3 CREATE OR REPLACE3.2.4 Shared attributes3.3 DB2 sample database3.4 Transactions3.4.1 Transaction terminology3.4.2 Transaction management3.4.3 Transaction management in compound statements3.5 DB2 for i catalog views
Chapter 4. Procedures
4.1 Introduction to procedures4.2 Structure of a procedure4.3 Creating a procedure4.3.1 CREATE PROCEDURE syntax4.4 System catalog tables for procedures4.4.1 SYSROUTINES catalog4.4.2 SYSPARMS catalog4.5 Procedure signature and procedure overloading4.6 Calling a procedure4.6.1 CALL statement syntax4.7 Producing and consuming result sets4.7.1 Creating result sets in an SQL procedure4.7.2 Retrieving result sets in the caller4.8 Handling errors4.8.1 Basic database error indicators4.8.2 Handling errors within procedures4.8.3 Tailoring error messages4.9 Summary
Chapter 5. Triggers
5.1 Trigger concepts5.2 Trigger types5.2.1 SQL triggers5.2.2 External triggers5.3 Introduction to triggers5.4 Defining triggers5.5 Trigger examples5.5.1 Simple trigger examples5.5.2 Use of correlation names for column values5.5.3 Multiple event triggers5.5.4 Changing row values in a BEFORE trigger5.5.5 Calling a procedure from a trigger5.5.6 Using transition tables5.5.7 Signaling an error5.5.8 Self-referencing triggers5.5.9 DB2ROW versus DB2SQL triggers5.5.10 INSTEAD OF triggers5.6 Additional trigger considerations5.6.1 Trigger limits5.6.2 Qualifying references5.6.3 Trigger program attributes5.6.4 Adding columns to tables5.6.5 Dropping or revoking privileges on tables5.6.6 Renaming or moving a table5.6.7 Transaction isolation5.6.8 Datetime considerations5.6.9 Triggers and traditional record-level access5.6.10 Multiple triggers on the same table5.7 Trigger-related catalogs
Chapter 6. Functions
6.1 Introduction6.2 Nature of user-defined functions6.2.1 User-defined scalar functions6.2.2 User-defined table functions6.3 Types of user-defined functions6.3.1 Sourced UDFs6.3.2 SQL UDFs6.4 Structure of an SQL UDF6.5 CREATE FUNCTION syntax for SQL scalar and table functions6.5.1 Modifying or dropping a UDF6.6 Resolving a UDF6.6.1 UDF overloading and function signature6.6.2 Parameter matching and promotion6.6.3 Function path and the function selection algorithm6.7 System catalog tables and views6.7.1 SYSFUNCS catalog6.7.2 SYSPARMS catalog6.8 UDF examples6.8.1 Simple scalar UDF6.8.2 More complex SQL statement UDF6.9 UDF inlining6.9.1 Examples of INLINE and NON INLINE UDFs6.10 UDTF examples6.10.1 Single SQL statement UDTF6.10.2 More complex SQL statement UDTFs6.10.3 External action UDTF6.10.4 UDTF for ranking6.11 Pipelined table functions6.11.1 PIPE syntax6.11.2 Pipelined function examples6.12 Coding considerations: UDF preferred practices6.13 SQL control statements6.14 Handling errors in SQL UDFs

Chapter 7. Development and deployment
7.1 Tools for developing SQL routines and triggers7.1.1 System i Navigator7.1.2 IBM Data Studio7.1.3 IBM i Access Client Solutions7.1.4 Comparison7.1.5 DB2 Express-C7.2 Debug SQL routines and triggers7.2.1 Debug SQL routines and triggers by using IBM Data Studio7.2.2 Debug by using IBM Run SQL Scripts7.3 Reverse engineering of SQL routines and triggers7.3.1 System i Navigator7.3.2 GENERATE_SQL7.4 Ownership and authorities of SQL routines and triggers7.4.1 Ownership7.4.2 Authorities7.5 Deployment of SQL routines and triggers7.5.1 Deploying procedures and user-defined functions7.5.2 Deploying triggers
Chapter 8. Creating flexible and reusable procedures
8.1 Introduction to reusable SQL procedures8.2 A modular approach to SQL procedure development8.2.1 Global variables as default parameters8.2.2 Simplified SQL descriptor usage8.2.3 Result set consumption8.2.4 Extended indicators and more8.3 Summary
Chapter 9. IBM i and IBM DB2 for i services
9.1 Health Center procedures9.2 Utility procedures9.2.1 QSYS2.EXTRACT_STATEMENTS9.3 Plan cache procedures9.3.1 QSYS2.DUMP_PLAN_CACHE_topN9.4 DB Application Services9.5 Performance Services9.6 PTF Services9.6.1 GROUP_PTF_CURRENCY9.7 Security Services9.7.1 QSYS2.SQL_CHECK_AUTHORITY()9.8 Message Handling Services9.8.1 QSYS2.JOBLOG_INFO9.9 Librarian Services9.9.1 QSYS2.OBJECT_STATISTICS()9.10 Work Management Services9.10.1 QSYS2.ACTIVE_JOB_INFO()9.10.2 QSYS2.OBJECT_LOCK_INFO9.10.3 QSYS2.SYSTEM_STATUS_INFO9.11 Java Services9.11.1 QSYS2.SET_JVM()9.12 IBM i Application Services9.12.1 QSYS2.QCMDEXC()
Appendix A. Allocating, describing, and manipulating descriptors
Introduction to SQL descriptorsAllocating SQL descriptorsDEALLOCATE SQL DESCRIPTORDescribing SQL descriptorsManipulating data to and from descriptors
Appendix B. Additional material
Locating the web materialUsing the web material
Related publications
IBM RedbooksOnline resourcesHelp from IBM
Back cover

Overview

Structured Query Language (SQL) procedures, triggers, and functions, which are also known as user-defined functions (UDFs), are the key database features for developing robust and distributed applications. IBM® DB2® for i supported these features for many years, and they are enhanced in IBM i versions 6.1, 7.1, and 7.2. DB2 for i refers to the IBM DB2 family member and relational database management system that is integrated within the IBM Power operating system that is known as IBM i.

This IBM Redbooks® publication includes several of the announced features for SQL procedures, triggers, and functions in IBM i versions 6.1, 7.1, and 7.2. This book includes suggestions, guidelines, and practical examples to develop DB2 for i SQL procedures, triggers, and functions effectively.

This book covers the following topics:

Introduction to the SQL/Persistent Stored Modules (PSM) language, which is used in SQL procedures, triggers, and functions

SQL procedures

SQL triggers

SQL functions

This book is for IBM i database engineers and data-centric developers who strive to provide flexible, extensible, agile, and scalable database solutions that meet business requirements in a timely manner.

Before you read this book, you need to know about relational database technology and the application development environment on the IBM Power Systems™ with the IBM i operating system.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

External Procedures, Triggers, and User-Defined Functions on IBM DB2 for i

Publisher Resources

ISBN: 9780738441641Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills