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

1. 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

2. Motivation Virtual tour Emphasis on value, not on syntax details Agenda

3. Evolution of SQL PL

4. 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?

5. Where does it hurt ? Audience Poll

6. 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

7. 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

8. Vnext: Full SQL PL in more places

9. -- 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

10. 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

11. 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

12. 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

13. 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

14. 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

15. 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

16. 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

17. 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

18. 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

19. 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

20. 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

21. 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

22. 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

23. 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

24. 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

25. 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

26. 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 @

27. 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

28. 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 %

29. 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

30. 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

31. 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

32. 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

33. 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

34. ? Q&A

35. Session: C06 SQL PL in DB2 9.7 all grown up Serge Rielau IBM Canada srielau@ca.ibm.com