1. Overview of SQL PL Language Elements & Modules

2. Unit objectives After completing this unit, you should be able to: • Discuss DB2 data types • Select the proper data types • Work with user-defined data types • Work with Row data type • Work with Associative arrays • Implement data type anchoring • Create Module • Replace Module • Alter Module • Drop Module

3. DB2 Built-in Data Types

4. User-Defined Distinct Types UDTs –User-Defined Data Types: • Defined on existing data types • Generates a function to cast between the distinct type and its source type • Generates a function to cast between the source type and its distinct type • Used to enforce business rulesCREATE

5. ROW data type

6. ARRAY data type • Conventional array CREATE TYPE arrType AS INTEGER ARRAY[1000]; • Associative array CREATE TYPE arrType2 AS INTEGER ARRAY[VARCHAR(100)]; CREATE TYPE arrType3 AS myRowType ARRAY[VARCHAR(100)];

7. Associative arrays: Example (1 of 2)

8. Associative arrays: Example (2 of 2)

9. Data Type Anchoring • Keep procedural variables in sync with table columns • Scalar anchoring DECLAREempSalaryANCHORemployee.salary; • Row anchoring • DECLARE emp ANCHOR ROWemployee; BEGIN DECLARE emp ANCHOR ROW employee; SETemp.empno= ‘000100’; SETemp.lastname= ‘McClung’; SETemp.firstname= ‘Naomi’; END

10. Modules: Overview • Module = bundle of several related objects: • SPs, UDFs, global variables and cursors, types, conditions • Similar to a class in OO languages (but single instance)• • Four main benefits: • Code organization/structure • Scoping • CALL mySchema.myModule.myProc() • Information hiding • Each object can be “public” or “private” • Global privilege control • Instead of granting/revoking on each SP, UDF or variable

11. Modules: Module specification • Module that exportsa type, a Stored Procedure, and a User-Defined Function • CREATE OR REPLACE MODULE myMod; • ALTER MODULE myMod PUBLISH • TYPE myRowTypAS ANCHOR ROW myTab; • ALTER MODULE myMod PUBLISH • FUNCTION myFunc(val1 ANCHOR myTab.col1) • RETURNS myRowTyp; • ALTER MODULE myMod PUBLISH • PROCEDURE myProc(OUTparm1 ANCHOR myTab.col2);

12. ENDModules: Module implementation

13. Modules: Other statements • DROP MODULE myMod; • Drops entire module • ALTER MODULE myMod DROP BODY; • Drop “implementation”, keeps “specification” • ALTER MODULE myMod DROP PROCEDURE myProc; • Drops module object • GRANT EXECUTE ON MODULE myMod TO joe; • Grants user joeexecute privilege on all routines and access to all variables and types in myModModules

14. Unit summary Having completed this unit, you should be able to: • Discuss DB2 data types • Select the proper data types • Work with user-defined data types • Work with Row data type • Work with Associative arrays • Implement data type anchoring • Create Module • Replace Module • Alter Module • Drop Module