slide1 n.
Download
Skip this Video
Download Presentation
Oct 6, 2009 • 1:00 p.m. – 2:00 p.m. Platform: DB2 for LUW

Loading in 2 Seconds...

play fullscreen
1 / 35

Oct 6, 2009 • 1:00 p.m. – 2:00 p.m. Platform: DB2 for LUW - PowerPoint PPT Presentation


  • 97 Views
  • Uploaded on

Session: C06 SQL PL in DB2 9.7 all grown up. Serge Rielau IBM Canada. Oct 6, 2009 • 1:00 p.m. – 2:00 p.m. Platform: DB2 for LUW. Motivation Virtual tour Emphasis on value, not on syntax details. Agenda. Evolution of SQL PL. Not just new syntax enhancements here and there Reducing TCO

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Oct 6, 2009 • 1:00 p.m. – 2:00 p.m. Platform: DB2 for LUW' - megan-downs


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

Session: C06 SQL PL in DB2 9.7 all grown up

Serge RielauIBM Canada

Oct 6, 2009 • 1:00 p.m. – 2:00 p.m.

Platform: DB2 for LUW

agenda
Motivation

Virtual tour

Emphasis on value, not on syntax details

Agenda
what s in it for me
Not just new syntax enhancements here and there

Reducing TCO

Where are the savings

Application development cost

Application maintenance cost

Enablement cost

What’s in it for me?
overview
Full SQL PL support across the board

UDFs, Triggers, Blocks

Data structures

Rows

Arrays of rows (local and global)

Associative arrays

Modules

Cursor data type

Data type anchoring

Global named exceptions

Built-in module library

CALL statement

CONSTANT

Overview
full sql pl across the board
Inlined vs. compiled SQL PL

Inlined: UDF and triggers inlined in SQL statement

Compiled: SP is compiled into a separate object

Inlined

Performane advantage (no context switch between SQL and SQL PL)

Several SQL PL language features not supported:

Cursor and result set handling

Error handling

Dynamic SQL

Compiled

Full SQL PL

Available so far in SPs only

Full SQL PL across the board
scenario for running example
-- Materials table

CREATE TABLE materials (id INTEGER, name VARCHAR(100), description VARCHAR(256))

INSERT INTO materials VALUES (1, ‘2X4', ’12-feet long two by four’),

(3, ‘nail', ’2 inch drywall nail’), (7, ‘drywall', ’ 8x12-feet drywall sheet’) @

-- Suppliers table

CREATE TABLE suppliers (id INTEGER, name VARCHAR(100), email VARCHAR(100))

INSERT INTO suppliers VALUES (74, ‘Bob the builder', ’bob@bob.com’),

(33, ‘Steel&Lumber', ’sales@sandl.com’) @

-- Supplier/Material table

CREATE TABLE supProd (suppId INTEGER, prodId INTEGER, qty INTEGER)

INSERT INTO supProd VALUES (74, 3, 12000),

(74, 7, 850),

(33, 7, 0) @

Problem statement

“Define a trigger on materials table such that when a new material is inserted, the trigger will send email to each of the suppliers of that material to request a quote”

Scenario for running example
full sql pl example
CREATE TRIGGER sendQuoteReqs AFTER INSERT ON materials

REFERENCING new AS new FOR EACH ROW

BEGIN

DECLARE suppId INTEGER;

DECLARE suppName VARCHAR (128);

DECLARE suppEmail VARCHAR (128);

DECLARE cur1 CURSOR CONSTANT CURSOR FORSELECT id, name, emailFROM suppliers s, supProd spWHERE s.id = sp.suppIdAND new.id = sp.prodId;

DECLARECONTINUE HANDLER FOR email.EmalNotSent

INSERT INTO errorLog VALUES (‘Failed to send email to supplier ‘ || suppName);

OPEN cur1;

-- Send email to each supplier of this product

FETCH cur1 INTO suppId, suppName, suppEmail;

WHILE cur1 IS FOUND DO

CALL sendQuoteRequestEmail(suppId, suppName, suppEmail, new.id, new.name);

FETCH cur1 INTO suppId, suppName, suppEmail;

END WHILE;

END @

Full SQL PL: example
row data type
Ubiquitous “struct” or “record” type

Even more natural in database procedural language

What can be done with rows

Single variable to fetch and insert

Can pass a parameters and return from UDFs

Can define global and module variables

Can use them to build arrays of rows (lightweight tables + compact array syntax)

ROW data type
row data type1
In previous example, unit of info was supplier

But supplier info split into several variables

Must carry variables around on all operations on supplier

However, when dealing with materials, DB2 didn’t create a variable per column. It created a variable for the whole row

CREATE TRIGGER… REFERENCING new AS new …

Row types let users do the same

ROW data type
row data type example
CREATE TYPE supplierType AS ROW( suppId INTEGER,

suppName VARCHAR (100),

suppEmail VARCHAR (100)

) @

CREATE TRIGGER sendQuoteReqs AFTER INSERT ON materials

REFERENCING new AS new FOR EACH ROW

BEGIN

DECLARE supp supplierType;

DECLARE cur1 CURSOR CONSTANT CURSOR FORSELECT id, name, emailFROM suppliers s, supProd spWHERE s.id = sp.suppIdAND new.id = sp.prodId;

...

FETCH cur1 INTO supp;

CALL sendQuoteRequestEmail(supp, new.id, new.name);

END @

ROW data type: example
anchored row type
We didn’t have to specify a type for the trigger transition variable

All DB2 needs to know is what table the trigger is associated with

… AFTER INSERT ON materials …

Why not let the user do the same for row types ?

CREATE TYPE supplierType AS ROW( suppId INTEGER,

suppName VARCHAR (128),

suppEmail VARCHAR (128)

) @

CREATE TYPE supplierRow1 AS ANCHOR ROW suppliers @

Anchored row type
anchored row type example
But… why define a type at all?

CREATE TRIGGER sendQuoteReqs AFTER INSERT ON materials

REFERENCING new AS new FOR EACH ROW

BEGIN

DECLARE supp ANCHOR ROW suppliers;

END @

Anchored row type: example
anchored row type1
Available in:

Local variables

Global variables

Parameters and return types

Value

Less code to write

Code automatically in-sync with data

Can anchor on tables and or cursors

Anchored row type
anchored row type cursor anchor
New requirement: in addition to supplier data, must fetch quantity

CREATE TRIGGER sendQuoteReqs AFTER INSERT ON materials

REFERENCING new AS new FOR EACH ROW

BEGIN

DECLARE cur1 CURSOR CONSTANT CURSOR FORSELECT id, name, email, qtyFROM suppliers s, supProd spWHERE s.id = sp.suppIdAND new.id = sp.prodId;

DECLARE suppAndQty ANCHOR ROW cur1;

FETCH cur1 INTO suppAndQty;

END

Anchored row type: cursor anchor
scalar anchoring
Same concept, but at scalar level

Can anchor on a column or on another variable

CREATE TYPE supplierType2 AS ROW( suppId ANCHOR supplier.id,

suppName ANCHOR supplier.name,

suppEmail ANCHOR supplier.email

) @

CREATE VARIABLE id ANCHOR supplier.id @

CREATE FUNCTION getEmail(id ANCHOR supplier.id)

RETURN ANCHOR supplier.id … @

Scalar anchoring
associative arrays example
New requirement: keep track of how many quotes we’ve requested from each supplier

CREATE TYPE reqList AS INT ARRAY[INT] @

CREATE VARIABLE reqsPerSupp reqList @

. . .

CREATE TRIGGER ……-- Send email to each supplier of this product

FETCH cur1 INTO suppAndQty;

WHILE cur1 IS FOUND DO

CALL sendQuoteRequestEmail(suppAndQty, new.id, new.name);

SET reqsPerSupp[suppId] = CASE

WHEN reqsPerSupp[suppId] IS NULL THEN 1

ELSE reqsPerSupp[suppId] + 1

END CASE;

FETCH cur1 INTO suppAndQty;

END WHILE;

Associative Arrays: example
associative arrays
Conventional arrays are “dense”

CREATE TYPE arrTyp AS INT ARRAY[2000] @

DECLARE a arrTyp;

SET a[1500] = 100; /* a has now 1500 elements */

Associative arrays are “sparse”

CREATE TYPE arrTyp AS INT ARRAY[INT] @

DECLARE a arrTyp;

SET a[1500] = 100; /* a has now 1 element */

Associative Arrays
associative arrays1
Index can be any integer type or VARCHAR of any length

Element can be any scalar type or a row type

CREATE TYPE arrType AS suppTyp ARRAY[VARCHAR(100)]; …DECLARE var arrType;

SET var[‘Supplier One’] = supp; /* supp is a row */

Can use Scalar Anchoring on both element and indexCREATE TYPE suppArr AS

ANCHOR ROW suppliers ARRAY[ANCHOR suppliers.id]@

Can be used in variables, parameters, UDF return type and global/module variables

Associative Arrays
modules
Module = bundle of several related objects

SPs, UDFs, global variables and cursors,types, conditions

Similar to a class in OO languages (but single instance)

Bundle canned applications, libraries

Impose structure on medium-sized and large apps

Use module to “hide” implementation

Modules
modules example
CREATE OR REPLACE MODULE hideArray @

ALTER MODULE hideArray ADD TYPE reqList

AS INT ARRAY[ANCHOR TO suppliers.id] @

ALTER MODULE hideArray ADD VARIABLE reqsPerSupp reqList @

ALTER MODULE hideArray PUBLISH PROCEDURE

incSuppCount(IN suppId ANCHOR suppliers.id)

BEGIN

SET reqsPerSupp[suppId] = CASE

WHEN reqsPerSupp[suppId] IS NULL THEN 1

ELSE reqsPerSupp[suppId] + 1

END CASE;

END @

Modules: example
modules benefits
Code organization/structure

Related routines share variables, types and conditions

Scoping

Possibilities of name conflicts

CALL mySchema.myModule.myProc()

Several users can deploy same module under different schema, with no name clashes

Information hiding

Each object can be “public” or “private”

Global privilege control

Instead of granting/revoking on each SP, UDF or variable

Modules - benefits
modules module specification
CREATE OR REPLACE MODULE myMod @

ALTER MODULE myMod PUBLISH CONDITION badCode @

ALTER MODULE myMod PUBLISH

FUNCTION myFunc(val1 ANCHOR myTab.col1)

RETURNS myRowTyp @

ALTER MODULE myMod PUBLISH

PROCEDURE myProc(OUT parm1 ANCHOR myTab.col2) @

GRANT EXECUTE ON MODULE myMod TO joe @

Grants user joe execute privilege on all routines and access to all variables and types in myMod

Modules: module specification
modules module implementation
ALTER MODULE myMod DROP BODY @

ALTER MODULE myMod ADD

VARIABLE pkgVar ANCHOR myTab.col1 @

ALTER MODULE myMod ADD FUNCTION myFunc()

RETURNS INT

BEGIN

DECLARE var1 INT;

SELECT * INTO var1

FROM myTab

WHERE col1 < pkgVar;

IF (var1 > maxVal) THEN

SIGNAL badCode;

END IF;

RETURN var1;

END @

Modules: module implementation

ALTER MODULE myMod ADD PROCEDURE

myProc(OUT parm1 ANCHOR myTab.col2)

BEGIN

DECLARE var1 INT;

DECLARE EXIT HANDLER FOR badCode BEGIN END;

SET var1 = myFunc();

END @

cursor data type
Cursors are values

Pass cursors as parameters

Cursor global variable

Function that returns a cursor

Cursor OUT parameters can be returned to JDBC clients

Parameterized cursors

Cursor predicates

Can open cursor in a procedure and assign to global variable. Cursor becomes a global cursor. Closed when there are no variables referencing it.

CURSOR data type
cursor data type example
CREATE FUNCTION

getSupps(prodId IN ANCHOR materials.id)

RETURNS CURSOR

BEGIN

DECLARE cur CURSOR;

SET cur = CURSOR FOR SELECT id, name, emailFROM suppliers s, supProd spWHERE s.id = sp.suppIdAND sp.prodId = prodId;

OPEN cur;

RETURN cur;

END %

CURSOR data type: example

CREATE PROCEDURE sendEmails

(cur CURSOR)

BEGIN

DECLARE varCur myCurTyp;

DECLARE supp ANCHOR ROW suppliers;

-- Send email to each supplier of this product

FETCH cur INTO supp;

WHILE cur IS FOUND DO

CALL sendQuoteRequestEmail(supp);

FETCH cur INTO supp;

END WHILE;

END %

cursor data type example1
CREATE TRIGGER sendQuoteReqs AFTER INSERT ON materials

REFERENCING new AS new FOR EACH ROW

BEGIN

DECLARECONTINUE HANDLER FOR email.EmalNotSent

INSERT INTO errorLog VALUES (‘Failed to send email to supplier ‘ || suppName);

DECLARE cur CURSOR;

SET cur = getSupps(new.id);

CALL sendEmails(cur);

END @

CURSOR data type: example
anonymous blocks
Server-side SQL scripting

One-time maintenance operations on the db

Quick reports

Example: validate shipments and report errors

BEGIN

FOR s INSELECT * FROM localShipments DO

BEGIN

DECLARE CONTINUE HANDLER FOR shipments.invalidOrigin

CALL print('ERROR: Bad shipment. Date =' || s.date || ', Origin = ' || s.origin);

CALL validateShipment (‘|’ || s.origin || s.description);

END;

END FOR;

END

Cached on first execution

Can use parameter markers

Anonymous blocks
call enhancements
Default values for parametersCREATE PROCEDURE logError

(id INT, message VARCHAR(200) DEFAULT ‘No message’) …

The following statements are equivalentCALL logError(10)CALL logError(12, DEFAULT)

Named parameters

CALL logError(message => ‘limit exceeded’, id => 31)

CALL enhancements
constant
Constant global variables: application-wide constants

CREATE VARIABLE maxWithdrawal FLOATCONSTANT 500 @

Module version:ALTER MODULE atm PUBLISH VARIABLE maxWithdrawal FLOATCONSTANT 500 @

Constant local variables

DECLARE maxArrSize INTCONSTANT 256;

CONSTANT
conclusions
SQL PL evolving from simple procedural control language to full programming language

Reduced app dev, app maintenance and app enablement

Note: DB2 also provides full PL/SQL support

Conclusions
slide35

Session: C06

SQL PL in DB2 9.7 all grown up

Serge Rielau

IBM Canada

srielau@ca.ibm.com