book

DB2® SQL PL: Essential Guide for DB2® UDB on Linux™, UNIX®, Windows®, i5/OS™, and z/OS®, Second Edition

by Zamil Janmohamed, Clara Liu, Drew Bradstock, Raul Chong, Michael Gao, Fraser McArthur, Paul Yip

November 2004

Intermediate to advanced

576 pages

11h 49m

English

IBM Press

Read now

Unlock full access

On Demand Computing BooksDB2 BooksMore Books from IBM Press
Installing DB2History of Stored ProceduresA Brief Introduction to Stored Procedures, Triggers, and FunctionsWhat’s New in the Second EditionDB2’s SQL Procedural Language (SQL PL)Other Stored Procedure LanguagesSQL PL Development Tools–DB2 Development CenterBook ConventionsSyntax DescriptionMeanings of StyleCodeBook StructureContacting the Authors
The CREATE PROCEDURE StatementProcedure NameParametersSpecific NameDYNAMIC RESULT SETSCONTAINS SQL, READS SQL DATA, MODIFIES SQL DATADETERMINISTIC or NOT DETERMINISTICCALLED ON NULL INPUTINHERIT SPECIAL REGISTERSOLD SAVEPOINT LEVEL, NEW SAVEPOINT LEVELLANGUAGE SQLEXTERNAL ACTION or NO EXTERNAL ACTIONPARAMETER CCSIDSQL Procedure BodyThe SQL Procedure Body StructureCommentsVariablesSetting VariablesDB2 Special RegistersBringing It All Together ExampleDB2 UDB for iSeries ConsiderationsFENCED and NOT FENCEDCOMMIT ON RETURNSET OPTION StatementDB2 UDB for zSeries ConsiderationsFENCEDNO DBINFONO COLLID or Collid collection-idWLM ENVIRONMENTASUTIMESTAY RESIDENTPROGRAM TYPESECURITYRUN OPTIONSCOMMIT ON RETURNSTOP AFTER SYSTEM DEFAULT FAILURES or CONTINUE AFTER FAILURESummary
DB2 Data TypesValid DB2 Built-In Data Types and Their Value RangesLarge ObjectsChoosing Proper Data TypesWorking with User-Defined Distinct TypesData ManipulationWorking with Dates and TimesWorking with StringsWorking with Generated ColumnsWorking with Identity Columns and Sequence ObjectsIdentity ColumnGenerate Value RetrievalChange of Identity Column CharacteristicsSequence ObjectChange of Sequence Object CharacteristicsPrivileges Required for Using Sequence ObjectsGenerated Value RetrievalPlatform Portability ConsiderationsSummary
Compound StatementsNOT ATOMIC Compound StatementATOMIC Compound StatementUsing LabelsConditional StatementsThe IF StatementThe CASE StatementLooping StatementsFOR LoopWHILE LoopREPEAT LoopLOOPTransfer of Control StatementsGOTOLEAVEITERATERETURNCOMMITROLLBACKSummary

Using Cursors in SQL ProceduresPositioned DeletePositioned UpdateSelecting Data from UPDATE, INSERT, or DELETE StatementsCursor Behavior with COMMIT/ROLLBACKSave Points Within ProceduresUsing Cursors to Return Result SetsReturning Multiple Result SetsCursors and LockingLock ModesIsolation LevelsUncommitted Read (UR)No Commit (NC)—iSeries OnlyCursor Stability (CS)Read Stability (RS)Repeatable Read (RR)Controlling Locking in DB2 LUWNo Wait and Lock WaitControlling Locking in DB2 UDB for iSeriesControlling Locking in DB2 UDB for zSeriesEvaluate Uncommitted and Avoiding LocksLock Timeout and Deadlock DetectionDB2 UDB for iSeries ConsiderationsDECLARE CURSOR Statement in DB2 UDB for iSeriesSummary
Basic Error Checking: SQLCODE and SQLSTATECondition HandlersCustom Errors and Error MessagesUsing SIGNAL to Force the Invocation of a HandlerUsing RESIGNAL to Force the Invocation of a HandlerScope of HandlersRAISE_ERROR FunctionGET DIAGNOSTICSProcessing Results from Called ProceduresBringing It All TogetherDB2 UDB for iSeries ConsiderationsSIGNAL and RESIGNAL StatementsRAISE_ERROR FunctionGET DIAGNOSTICSDB2 UDB for zSeries ConsiderationsCondition HandlersGET DIAGNOSTICS StatementRETURN StatementSummary
PREPARE and EXECUTE: The Two Phases of Any SQL StatementDynamic SQL Versus Static SQLRestrictions and ConsiderationsUsing Dynamic SQL with EXECUTE IMMEDIATEEscaping Single Quotes (’)Reusing Dynamic SQL Statements with PREPARE and EXECUTEUsing Dynamic SQL in CursorsDynamic CALL StatementsAuthorization ConsiderationSummary
Basic Nested SQL ProceduresPassing Parameters Between Nested SQL ProceduresReturning Values from Nested SQL ProceduresReturning Result Sets from Nested SQL ProceduresReturning Result Sets to the ClientReturning Result Sets to the CallerReceiving Result Sets as a CallerReceiving Multiple Result Sets as a CallerReceiving Results from a Procedure in a TriggerLevels of NestingRecursionSecuritySummary
The CREATE FUNCTION StatementFunction Name and ParametersReturns Function OutputSpecific NameLanguage SQLDETERMINISTIC or NOT DETERMINISTICEXTERNAL ACTION or NO EXTERNAL ACTIONCONTAINS SQL, READS SQL DATA, MODIFIES SQL DATASTATIC DISPATCHCALLED ON NULL INPUTINHERIT SPECIAL REGISTERS (LUW and iSeries Only)PREDICATES (LUW only)SQL Function BodyUser-Defined Functions by ExamplesA Simple Scalar UDF ExampleA Complex Scalar UDF ExampleA Table UDF Example (LUW and iSeries)Invoking SQL Procedures in UDFs (LUW and iSeries Only)The CREATE TRIGGER StatementTrigger NameBEFORE, AFTER, or INSTEAD OFINSERT, DELETE, or UPDATEREFERENCING ClausesFOR EACH ROW or FOR EACH STATEMENTMODE DB2SQLTriggered ActionTriggers by ExamplesA BEFORE Trigger ExampleAn AFTER Trigger ExampleA Complex AFTER Trigger ExampleAn INSTEAD OF Trigger Example (for LUW Only)A Comparison of View Triggers and Table Triggers (LUW only)Invoking UDFs and SQL Procedures from TriggersConsiderations for Invoking SQL Procedures from UDFs and TriggersData Access RestrictionsTransaction Control in SQL ProceduresTable Read or Write Conflict in SQL Procedures on LUWDB2 for iSeries ConsiderationsDB2 for zSeries ConsiderationsThe CREATE FUNCTION StatementTrigger ConsiderationsInvoking UDFs and SQL Procedures from TriggersNesting SQL StatementsSummary
Leveraging Advanced SQLCombining SELECT with INSERT, UPDATE, and DELETENEW TABLE and OLD TABLEINCLUDEFINAL TABLEDeleting a Few Rows at a TimeMERGE StatementDeclared Global Temporary TablesIntroduction to Temporary TablesCreating the Environment for Temporary TablesDeclaring Global Temporary TablesUsing Temporary Tables in SQL ProceduresSharing Temporary Tables Between Multiple ProceduresConsiderationsCreated Global Temporary TablesWorking with Save PointsIntroduction to Application Save PointsUsing Save Points in SQL ProceduresSequence ObjectsSummary
Deploying on Linux, UNIX, and WindowsDeploying SQL Procedures Using DDLDeploying FunctionsDeploying Functions Using the Development CenterDeploying Functions Using ScriptsDeploying TriggersDeploying Using GET ROUTINE and PUT ROUTINEDeploying Using Older Releases of DB2Deploying from Pre-V8.2 to Post-V8.2Deploying from Pre-V8.2 to V8.2Deploying from V8.2 to Pre-V8.2Code Page ConsiderationsBackup and Recovery ConsiderationsDeployment Considerations for DB2 for zSeriesDeploying SQL ProceduresDeploying Functions and TriggersDeployment Considerations Based on Your C Compiler RequirementsCommon Deployment Considerations for LUW and zSeriesDeploying from One Database to AnotherDeploying in DB2 UDB for iSeriesUsing the Development CenterUsing iSeries Navigator and CL Commands in iSeriesSummary
Performance Considerations for LUWThe Configuration AdvisorMonitoring SQL PerformanceUsing Explain to Analyze Access PlansThe Design AdvisorLarge Object (LOB) ConsiderationsTemporary Tables ConsiderationsImproving PerformanceCreating IndexesOngoing Maintenance to Keep Performance at Its PeakDB2 for iSeries ConsiderationsMonitoring Memory UsagePerformance Monitors and CommandsQuery Optimizer and Access PlansVariable Length Columns and Large ObjectsDatabase StatisticsDB2 for zSeries ConsiderationsThe EDM Statement CacheThe Performance Expert ToolImproving Response Time and ThroughputBuffer Pool SizeRID Pool SizeRUNSTATSPCTFREE and FREEPAGEDistribute Your Datasets EfficientlyCreate Additional Work File Table SpacesSpace FormattingAvoid Excessive ExtentsMaximum Number of Open DatasetsResolving Excessive Wait Time or Timeouts for UDFs and Stored ProceduresTuning Your QueriesGiving Optimization Hints to DB2Limit Resources for a Stored ProcedureConsiderations for Stored Procedures in a Distributed EnvironmentSummary
Table and Index Best PracticesTable ConsiderationsTemporary Table ConsiderationsIndex ConsiderationsBest Practices for Easier Code MaintenanceCode ReadabilityCode SizeGrouping SQL Procedures by SchemaNaming ConventionsReturn Values Versus Out ParametersException HandlingCommit and RollbackBest Practices for PerformanceExceptionsRepetitive SQLTable TruncationReducing LockingOn LUWOn iSeriesOn zSeriesDynamic SQL for DDLUsing a Single Insert to Replace Multiple Insert StatementsValue AssignmentDeterministic Versus Not DeterministicCase StatementFunctionsWorking with Result SetsCursor ConsiderationsLimiting Result SetsOptimizing Result Set ReturnsMinimize Result Set ColumnsPrecompile Options on iSeriesSummary
Getting Started with DB2 UDB for LUWLaunching the DB2 Command WindowLaunching the DB2 Command Line ProcessorOverview of DB2 ArchitectureDB2 InstancesCreating DatabasesExecuting SQL in the DB2 Command WindowExecuting SQL in DB2 Interactive ModeConfiguring the DB2 EnvironmentDB2 Profile RegistriesUnderstanding Buffer PoolsWorking with Table SpacesWorking with TablesIntroducing the System Catalog TablesGetting Started with DB2 UDB for iSeriesLaunching the InterfaceOverview of the DB2 UDB for iSeries ArchitectureExecuting SQL in DB2 UDB for iSeriesWorking with TablesCatalogGetting Started with DB2 UDB for zSeriesOverview of the DB2 UDB for zSeries ArchitectureCatalogInteracting with DB2 for zSeriesUsing DB2IUsing DB2 LUW Client ToolsSummary
Dynamic Compound StatementStand-Alone CodeChoosing Between Dynamic Compound Statements and SQL ProceduresSummary
Configuring the Build EnvironmentBuilding SQL ProceduresCreating a DB2 Command-Line Processor ScriptBuilding SQL Procedures in DB2 UDB for iSeriesBuilding SQL Procedures in DB2 for zSeriesThe Build ProcessMethods to Build a DB2 for zSeries SQL ProcedureSummary
Installing the DB2 Development CenterDB2 for WindowsDB2 for Linux and UNIXUsing the Development Center for the First TimeDevelopment Center ViewsCustomizing the Development CenterRunning ProceduresStored Procedure Run SettingsDebugging Stored ProceduresDebugging Nested Stored ProceduresWorking with ProjectsImporting Stored ProceduresDB2 for iSeries ConsiderationsDB2 for zSeries ConsiderationsEnvironment SettingsDeveloping SQL Stored Procedures from the Development CenterThe Project and Server ViewsDebugging DB2 for zSeries SQL Stored ProceduresSummary
PrivilegesPrivileges Required by DevelopersPrivileges Required by LUW Stored Procedure DevelopersPrivileges Required by iSeries Stored Procedure DevelopersPrivileges Required by zSeries Stored Procedure DevelopersPrivileges Required by UsersPrivileges Required by LUW UsersPrivileges Required by iSeries UsersPrivileges Required by zSeries UsersUsing Stored Procedures in a Production EnvironmentDB2 for zSeries ConsiderationsDYNAMICRULES Bind/Rebind Option ValueThe Package’s Runtime EnvironmentThe DEFINE BehaviorThe INVOKE BehaviorCommon Attribute Values for the DEFINE and INVOKE BehaviorsSimplifying AuthorizationSummary
Creating the SAMPLE Database on LUWCreating the SAMPLE Database on iSeriesCreating the SAMPLE Database on zSeriesSAMPLE Database DDL: Script 1 sampleDDL_1.db2DDL for Additional Database Objects: Script #2 sampleDDL_2.db2
IBM RedbooksLUWiSerieszSeriesCross-PlatformCertificationCertification GuidesTutorialsEducationIBM Learning ServicesApplication ProgrammingDatabase AdministrationPerformance TuningFree Self-Study Computer-Based Training CoursesIBM Solution Partnership Centers (SPCs)TutorialsBooksAdditional WebsitesDB2 FixPaks and DownloadsEmail Services and PeriodicalsUser Groups and Conferences
Receiving Result Sets in JavaReceiving Result Sets in a Java ApplicationReceiving Result Sets in a Java Stored ProcedureReceiving Result Sets in a C or C++ ApplicationReceiving Multiple Result Sets in JavaReceiving Multiple Result Sets in a Java ApplicationReceiving Multiple Result Sets in a Java Stored ProcedureReceiving Multiple Result Sets in a C or C++ Application

Content preview from DB2® SQL PL: Essential Guide for DB2® UDB on Linux™, UNIX®, Windows®, i5/OS™, and z/OS®, Second Edition

Chapter 9. User-Defined Functions and Triggers

In this chapter, you will learn

What User-Defined Functions (UDFs) are
How UDFs can be used to simplify your application development efforts
What triggers are
How triggers can be used to transparently and consistently enforce application logic in the database

UDFs and triggers are database objects that help reduce the complexity of database application development.

On LUW, although the language syntax for SQL PL is consistent for all types of objects from an application development perspective, the language implementation to support SQL PL for stored procedures is different from that for UDFs and triggers. SQL PL support in UDFs and triggers is a subset of that in stored procedures. It is referred to as ...

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.

Start your free trial

IBM DB2 9.7 Advanced Application Developer Cookbook

Mohankumar Saraswatipura, Sanjay Kumar

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

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

IBM Db2 11.1 Certification Guide

Robert (Kent) Collins, Mohankumar Saraswatipura

DB2 SQL Tuning Tips for z/OS Developers

Tony Andrews

Publisher Resources

ISBN: 0131477005Purchase book