Get Cozy with DB 2 for zOS Stored
Get Cozy with DB 2 for z/OS Stored Procedures and UDFs Denis Tronin CA Technologies Session Code: F 11 November 16, Wednesday, 11: 00 - 12: 00| Platform: DB 2 for z/OS
Agenda • • • Stored Procedure and User Defined Function concepts Recent enhancements overview Stored procedure and UDF monitoring capabilities Administrative procedures Q&A 2
Stored Procedure & UDF Concepts EXEC SQL SELECT EXEC SQL CALL SELECT User EXEC SQL SELECT DB 2 User DB 2 SELECT 3
Stored Procedure and User Defined Function Concepts Benefits SP and UDF represent a piece of business logic written in SQL or other host language, stored at database server, and that can be invoked many times from various applications. This all: • Reduces network communications ü Multiple SQL statements executed for one connection call • Heighten performance, security, and compatibility ü Data are accessed by the processing programs directly at server ü Access is granted for a routine not to a specific table ü Separation of SQL and application code simplifies coding and conversions • Brings non-traditional workloads ü Access data outside of the database from sources as VSAM, Hadoop, etc. • Simplifies database maintenance ü Administrative procedures available for both DBAs and developers 4
Stored Procedure and User Defined Function Concepts Overview • Stored Procedure is a user-written program executed at the database server • Usually contains SQL statements • Defined to DB 2 with CREATE PROCEDURE statement • Invoked from application program with CALL statement • CALL MY_PROC('Input value', : Result. Set) • Takes and returns data via parameters: • Input parameters (IN and INOUT) • Output parameters (OUT and INOUT) • Output RESULT SET with an opened cursor 5
Stored Procedure and User Defined Function Concepts Overview • User Defined Function (UDF) is similar to stored procedure, i. e. it is a user-written program executed at the database server • However UDFs only partially cover stored procedure capabilities • Usually contains SQL statements • Defined to DB 2 with CREATE FUNCTION statement • Invoked from within other SQL statements (select/update/etc. ) • SELECT MY_FUNC('input value', 'value') FROM T 1; • Takes parameters as an input • In contrast with SP, returns a result • Either as a scalar value or a table 6
Stored Procedure and User Defined Function Concepts Execution 7
Stored Procedure and User Defined Function Concepts Stored Procedure Types • External • Written in a host language: assembler, C, C++, Cobol, Java, Rexx, PL/I • Source code is separate from procedure definition • Runs under WLM address space • External SQL • • Written in SQL Procedural Language Converted into C programs and compiled as external procedures Created by specifying FENCED or EXTERNAL on CREATE PROCEDURE Runs under WLM address space • Native SQL • Written in SQL Procedural Language • Created by NOT specifying FENCED or EXTERNAL on CREATE • Runs under DBM 1 address space 8
Stored Procedure and User Defined Function Concepts User-Defined Function Types • External UDF • • Written in a host language: assembler, C, C++, Cobol, Java, PL/I Source code is separate from function definition Runs under WLM address space Can return either a scalar value or a table result set • SQL Scalar • Written in SQL Procedural Language (no actual SQL allowed) • Runs under DBM 1 address space • SQL Table • A single RETURN subselect statement • Runs under DBM 1 address space • Sourced UDF • Invokes another existing function (built-in or UDF) for distinct types 9
Stored Procedure and User Defined Function Concepts 10
Stored Procedure and User Defined Function Concepts Sample • DDL for an external procedure in C to calculate a table size: • CREATE PROCEDURE TRODE 05. EXT_SP_C 1 (IN TBNAME VARCHAR(257), OUT RC INT, OUT TBSIZE INT) EXTERNAL NAME 'SP#C 1' LANGUAGE C READS SQL DATA WLM ENVIRONMENT D 11 AWLM 1 PROGRAM TYPE MAIN COLLID TRODE 05 SP • Procedure parameters • Input table name • Output return code • Output table size value IN TBNAME OUT RC OUT TBSIZE • CALL TRODE 05. EXT_SP_C 1('TRODE 05. T 1', RC, TSIZE) 11
Stored Procedure and User Defined Function Concepts Sample C code #pragma runopts(PLIST(OS)) #include <stdlib. h> EXEC SQL PREPARE s 1 FROM : sql; if (SQLCODE != 0) { *(int *) argv[2] = SQLCODE; return; } EXEC SQL INCLUDE SQLCA; main(int argc, char *argv[]) { EXEC SQL BEGIN DECLARE SECTION; struct { short int len; char data[1024]; } sql; long int TBSIZE; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE c 1 CURSOR FOR s 1; EXEC SQL OPEN c 1; if (SQLCODE != 0) { *(int *) argv[2] = SQLCODE; return; } EXEC SQL FETCH c 1 INTO : TBSIZE; if (SQLCODE != 0) { *(int *) argv[2] = SQLCODE; return; } *(int *) argv[3] = -1; strcpy(sql. data, "SELECT COUNT(*) FROM "); strcat(sql. data, argv[1]); sql. len = strlen(sql. data); *(int *) argv[2] = SQLCODE; *(int *) argv[3] = TBSIZE; EXEC SQL CLOSE c 1; } The code must be compiled and link-edit into a WLM load library (D 11 AWLM 1) The DBRM must be bound into a collection as specified in the DDL (TRODE 05 SP) 12
Recent Enhancements 13
Recent Enhancements Native SQL Procedure (DB 2 9) • Introduced in DB 2 9 NFM • Stored completely in DB 2 • No external load module • Runs in DBM 1 address space (WLM is only used for debugging) • Contains SQL PL statements • Written in SQL PL • BEGIN / END, DECLARE / SET, FOR / WHILE, IF / ELSE, RETURN… • Nested compound statements are allowed • Easy to create and maintain • Designed with application life cycle in mind: Create Debug Replace or Add a new version Deploy • z. IIP eligible when invoked remotely (via DRDA) 14
Recent Enhancements Native SQL Procedure (DB 2 9) Sample • SQL procedure sample • Same as previously discussed external procedure written in C language • CREATE PROCEDURE TRODE 05. SQL_SP_C 1 (IN TBNAME VARCHAR(257), OUT TBSIZE INT) LANGUAGE SQL READS SQL DATA BEGIN DECLARE STMT VARCHAR(1024); DECLARE C 1 CURSOR FOR S 1; SET STMT = 'SELECT COUNT(*) FROM '||TBNAME; PREPARE S 1 FROM STMT; OPEN C 1; FETCH C 1 INTO TBSIZE; CLOSE C 1; END 15
Recent Enhancements Native SQL Procedure Improvements (DB 2 10) • Distinct and XML data types are allowed on SQL procedure parameter or SQL variable CREATE TYPE CURRENCY AS DECIMAL(5, 2); CREATE PROCEDURE MYPROC 1(IN P 1 XML, OUT P 2 CURRENCY) • CREATE and ALTER statements are allowed in the code • REBIND PACKAGE with the new options: • PLANMGMT to retain/backup package data • SWITCH to restore/fallback package data • DECLARE cursor CURSOR WITH RETURN TO CLIENT • A result set can be returned from a SQL procedure at any nesting level directly to the client calling application • No materialization through global declared tables (DGTT) is required 16
Recent Enhancements Autonomous SQL Procedure (DB 2 11) • AUTONOMOUS option on CREATE PROCEDURE statement • SQL procedure is executed in a separate unit of work • Procedure can COMMIT and ROLLBACK not affecting the caller SQLs • Uncommitted caller’s data are not visible • Locks are not shared between caller and procedure • Changes are committed upon completion when SQLCODE >= 0 • Autonomous procedures might be good for auditing scenarios 1 T 1→ 0 T 2→ 1 0 17
Recent Enhancements SQL PL: GLOBAL VARIABLEs (DB 2 11) • GLOBAL VARIABLE: • Created as a separate database object • SYSIBM. SYSVARIABLES and SYSIBM. SYSVARIABLEAUTH • Access is controlled with GRANT/REVOKE • Created once and available for use in any SQL statement • Value is independent and unique to the session (DB 2 connection) • Very similar to special registers • Value is not affected by a COMMIT or ROLLBACK CREATE VARIABLE schema. name data-type DEFAULT default-value Easy and neat way of passing data between SQL calls and control execution application logic flow 18
Recent Enhancements SQL PL: GLOBAL VARIABLEs (DB 2 11) • Global variable value can be changed via: • SET • FETCH / SELECT INTO / VALUES INTO • when embedded into an application program • CALL • when the global variable is an OUT or INOUT procedure parameter • Sample to use a global variable in the SQL procedure: CREATE VARIABLE OBJT CHAR(10); CREATE VARIABLE OBJCNT INT; SET OBJT = 'DATABASE'; … and then in SQL procedure: IF OBJT='DATABASE' THEN SELECT COUNT(*) INTO OBJCNT FROM SYSIBM. SYSDATABASE; ELSE SELECT COUNT(*) INTO OBJCNT FROM SYSIBM. SYSTABLESPACE; END IF; 19
Recent Enhancements SQL PL: ARRAY data type (DB 2 11) • User data type for having multiple values in a single variable • Elements are based on one of the existing built-in data types • Defined with CREATE TYPE and dropped via DROP TYPE • Definitions are stored to SYSIBM. SYSDATATYPES • Supported only: • • As SQL PL variables In SQL scalar functions, as a parameter or RETURNS data-type In native SQL procedures, as parameter In CAST specification, as a target data type Arrays simplify writing complex SQL PL programs and allow passing multiple IN and OUT parameters to a SQL procedure 20
Recent Enhancements SQL PL: ARRAY data type (DB 2 11) • Arrays can be defined as ordinary or associative • Ordinary array • “Classic array” where elements are referenced by position number and number of elements is set during creation • Sample CREATE TYPE Ord. Arr. T AS INTEGER ARRAY[]; • Defines a new ordinary array type DECLARE my. Ord. Arr. Var Ord. Arr. T; • Declares a variable of this new array type SET my. Ord. Arr. Var[10] = 5; • • • Populates the variable as a array of 10 elements The first element with index 0 is set to 5 The rest elements with index 1 to 9 are set to NULL 21
Recent Enhancements SQL PL: ARRAY data type (DB 2 11) • Associative array • “Dictionary array” with no upper limit for the number of elements • Index values are unique and do not have to be contiguous • The only supported index types are INTERGER and VARCHAR • Sample CREATE TYPE Assoc. Arr. T AS INTEGER ARRAY[VARCHAR(1)]; • Defines a new associative array type with elements of type INTEGER and indexes of type VARCHAR[1] DECLARE my. Assoc. Arr. Var Assoc. Arr. T; • Declares a variable of this new array type SET my. Assoc. Arr. Var['A'] = 5; SET my. Assoc. Arr. Var['B'] = 10; • Array contains 2 elements, an element with index 'A' is set to 5, 'B' set to 10 22
Recent Enhancements SQL PL: ARRAY data type (DB 2 11) • ARRAY_EXISTS(array, index) predicate if element exists • ARRAY_FIRST(array) / ARRAY_LAST(array) element • ARRAY_NEXT(array, index) /ARRAY_PRIOR(array, index) element • ARRAY_DELETE(array [, index_start [, index_end]]) → reduced array • ARRAY_TRIM(array, number) / TRIM_ARRAY → truncated array • CARDINALITY(array) → array size • MAX_CARDINALITY(array) → array maximum size • ARRAY_AGG(column-expression, [ORDER BY]) populated variable • FROM UNNEST(array) [WITH ORDINALITY] AS T(column 1 [, column 2]) array as table 23
Recent Enhancements SQL UDF (DB 2 10) • Non-inline SQL Scalar UDF • Expanded pre-V 10 SQL functions with SQL PL capabilities • Similar to SQL procedures • DB 2 creates an associated package • REBIND PACKAGE for access path changes • Returns a single value • SQL Table UDF • Table functions in pre-V 10 were only supported as external functions CREATE FUNCTION my. SQLfunc(TP CHAR) RETURNS TABLE(owner VARCHAR(128), name VARCHAR(128)) RETURN SELECT OWNER, NAME FROM SYSIBM. SYSTABLES WHERE TYPE=TP AND CREATOR LIKE 'T%'; SELECT * FROM TABLE(my. SQLfunc('T')); 24
Recent Enhancements GENERIC TABLE External UDF (DB 2 11) • Generic Table UDF returns a table of variable shape • Also known as polymorphic table function • Normally, the output table shape is defined in DDL • However, hard-coded shape is very limiting when output result set should vary greatly based on input criteria • For example, when getting output from Hadoop/Spark • Output result table is defined in the SQL statement that invokes the generic table UDF SELECT * FROM TABLE(UDFGEN()) AS T(COL 1 INT) vs. SELECT * FROM TABLE(UDFGEN()) AS T(COL 1 INT, COL 2 INT) 25
Recent Enhancements GENERIC TABLE External UDF – Sample • Let’s write a sample UDFGEN function that returns a table having variable number of columns • Number of table rows is passed as parameter to the function • Values in each column/row is the same – ascending number sequence • Invocation a) SELECT * FROM TABLE ( UDFGEN(3) ) AS T(C 1 INT) b) SELECT * FROM TABLE ( UDFGEN(2) ) AS T(C 1 INT, C 2 INT, C 3 INT) • Output a) C 1 -0 1 2 b) C 1 -0 1 C 2 -0 1 C 3 -0 1 26
Recent Enhancements GENERIC TABLE External UDF – DDL Sample CREATE FUNCTION TRODE 05. UDFGEN(SIZE INT) RETURNS GENERIC TABLE EXTERNAL NAME UDFGEN Ø LANGUAGE C is the only supported language for GENERIC PARAMETER STYLE SQL UDFs DISALLOW PARALLEL Ø SCRATCHPAD provides an area for the function to save LANGUAGE C information from one invocation NO FINAL CALL to the next Ø FINAL CALL defines whether a SCRATCHPAD 256 first call and final call are made to the function in addition to OPEN, NO EXTERNAL ACTION FETCH, or CLOSE calls NO COLLID WLM ENVIRONMENT D 11 AWLM 1 PROGRAM TYPE SUB 27
Recent Enhancements GENERIC TABLE External UDF – Code sample #pragma linkage(UDFGEN, fetchable) #include <stdlib. h> #include <dsnudf. h> /*db 2. SDSNC. H*/ typedef struct { long int count; } Scratch. Pad; void UDFGEN( int *size, short *size_ind, SQLUDF_OTDESC *table_descriptor, char *sqlstate, char *fn. Name, char *specific. Name, char *msgtext, SQLUDF_SCRATCHPAD *spadptr, long *call. Type ) Table descriptor is a major element here as it describes and points to each output table column { Scratch. Pad* spad = (Scratch. Pad*) spadptr->data; strcpy( sqlstate, "00000" ); *msgtext = '