DB 2 Stored Procedures The Good the Bad

DB 2 Stored Procedures – The Good the Bad and the Ugly Lauri Pietarinen, Relational Consulting Session Code: F 17 19. 11. 2015 9. 15 Platform: LUW, z/OS

What you will learn • Understand the power of stored procedures from a application design viewpoint • Understand the current restrictions and sore points of SQL/PL • Understand new possibilities available in the older and newest versions • Understand new possibilities to use SQL/PL as a scripting language in LUW 2

The Ugly JAVA-program App-server DB-server SQL SQL Result SQL SQL Result SQL SQL Result Result SQL Result SQL Result 3

The Good SQL DB 2 -server STORED PROCEDURE JAVA-program App-server RS 1 RS 2 RS 3 Results returned in result sets! • • • Performance! Less NW traffic! Security! Testability! Static DB 2! Explain Plan! Predictability! Easy to monitor! z. IIP! Easy language! (SQL proc) 4

The Bad? • Application logic spread over several languages and platforms • Interfaces need to be defined • Need separate coders for Java and SQL/PL(? ) • Recommendation: Teach Java-coders SQL/PL and SQL, same people should code both ends so as to minimize communication problems. 5

The Ugly • Sybase Transact-SQL: 1986 • Oracle PL/SQL: 1990 • Informix SPL: 199 x • DB 2 SQL/PL External: 200 x! • DB 2 SQL/PL NATIVE: 201 x!! 6

The Good CREATE PROCEDURE ADD_PERSON ( IN P_ID INT, IN P_NAME VARCHAR(20), OUT P_MESSAGE VARCHAR(100) ) BEGIN CASE WHEN P_NAME = ’’ THEN SET P_MESSAGE = ’MUST GIVE NAME’; RETURN; WHEN P_ID <= 0 THEN SET P_MESSAGE = ’ID MUST BE POSITIVE’; RETURN; ELSE SET P_MESSAGE = ’OK’; END CASE; INSERT INTO PERSON ( ID, NAME ) VALUES( P_ID, P_NAME ); END 7

The (Fairly) Good z/OS • • • z/OS stuff IMS/TM calls Small differences… Validate Run Trailing feature-wise in general Lot’s in common, but… LUW • • • Modules Anonymous blocks Types etc… 8

The Ugly CREATE PROCEDURE STUPIDPROC( ) BEGIN P 1: GOTO P 2; P 2: GOTO P 1; END -- Edsger W. Dijkstra: 1968: Go To Statement Considered harmful -- Did they not get the memo? -- Even Java does not have GOTO • -- Gordon Bell: The cheapest, fastest, and most reliable components [and language constructs] are those that aren’t there. • 9

The Ugly CREATE PROCEDURE STUPIDOSAMPLE 2 ( ) P 1: BEGIN DECLARE K INTETER; P 2: BEGIN DECLARE K INTEGER; P 3: BEGIN DECLARE K INTEGER; SET K = 7 /* refers SET P 1. K = 5; END; to K in P 3 */ SET P 2. K = 666; END; SET P 3. K = 123; /* wrong context!! */ END; 10

The Good CREATE FUNCTION COLLIST ( PIN_SCHEMA CHAR(8), PIN_TABLE CHAR(18) ) RETURNS VARCHAR(1000) BEGIN DECLARE V_LIST_OF_COLS VARCHAR(1000); SET V_LIST_OF_COLS = ''; Even in z/OS! FOR F_COLS AS SELECT NAME FROM SYSIBM. SYSCOLUMNS WHERE TBCREATOR = PIN_SCHEMA AND TBNAME = PIN_TABLE ORDER BY COLNO DO SET V_LIST_OF_COLS = V_LIST_OF_COLS || F_COLS. NAME || ' '; END FOR; RETURN V_LIST_OF_COLS; END@ SELECT NAME, COLLIST(CREATOR, NAME) AS LIST_OF_COLS FROM SYSIBM. SYSTABLES WHERE CREATOR = ‘LAURI' AND NAME IN ('EMP', 'DEPT')@ --------+---------+--NAME LIST_OF_COLS --------+---------+--DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION … EMPNO FIRSTNME MIDINIT LASTNAME WORKDEPT … 11

The Good W LY ON CREATE TABLE MYNUMBERS ( NUM INTEGER )@ LU SET SERVEROUTPUT ON@ /* Anonymous block to add numbers 1. . 100 to the table */ BEGIN DECLARE V_I INTEGER DEFAULT 0; WHILE V_I <= 100 DO SET V_I = V_I + 1; CALL DBMS_OUTPUT. PUT_LINE(‘ADDING ’ || V_I); INSERT INTO MYNUMBERS ( NUM ) VALUES ( V_I ); END WHILE; END@ SELECT COUNT(*) FROM MYNUMBERS@ 12

Testing performance of prepared SQL that have parameter markers • Example to be added here… 13

W LU SET SERVEROUTPUT ON@ DECLARE V_CPUTIME 1 BIGINT; V_CPUTIME 2 BIGINT; V_CPUTIMEDELTA BIGINT; V_I INTEGER; LY ON BEGIN SET V_CPUTIME 1 = DBMS_UTILITY. GET_CPU_TIME(); SET V_I = 0; L_LOOP: LOOP IF V_I > 10000000 THEN LEAVE L_LOOP; END IF; SET V_I = V_I + 1; END LOOP; SET V_CPUTIME 2 = DBMS_UTILITY. GET_CPU_TIME(); SET V_CPUTIMEDELTA = V_CPUTIME 2 - V_CPUTIME 1; CALL DBMS_OUTPUT. PUT_LINE( ‘CPU DELTA = ‘ || V_CPUTIMEDELTA ); END@ ANSWER 140 1/100 SEC 14

SET SERVEROUTPUT ON@ W LU The Good ON LY BEGIN DECLARE v_filehandle UTL_FILE_TYPE; DECLARE is. Open BOOLEAN; DECLARE v_dir. Alias VARCHAR(50) DEFAULT 'mydiralias'; DECLARE v_filename VARCHAR(20) DEFAULT 'my_IDUG_file. txt'; CALL UTL_DIR. CREATE_OR_REPLACE_DIRECTORY(v_dir. Alias, '/temp'); SET v_filehandle = UTL_FILE. FOPEN(v_dir. Alias, v_filename, 'w'); SET is. Open = UTL_FILE. IS_OPEN( v_filehandle ); IF is. Open != TRUE THEN RETURN -1; END IF; CALL DBMS_OUTPUT. PUT_LINE('Opened file: ' || v_filename); CALL UTL_FILE. PUT_LINE(v_filehandle, 'Hello IDUG EMEA participants!!'); CALL UTL_FILE. FCLOSE(v_filehandle); END 15

The Bad CREATE TABLE DEMO ( C 1 CHAR(5) ) ; - - - CREATE PROCEDURE PROCA ( IN PIN_TEXT VARCHAR(100) ) BEGIN CALL PROCB( P_TEXT ); END# -----------------CREATE PROCEDURE PROCB ( IN PIN_TEXT VARCHAR(100) ) BEGIN CALL PROCC( P_TEXT ); END# -----------------CREATE PROCEDURE PROCC ( IN PIN_TEXT VARCHAR(100) ) BEGIN INSERT INTO DEMO VALUES(P_TEXT); END# - Where did it fail? ------------------CALL PROCA('123456') THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER *N IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE. SQLCODE=-302, SQLSTATE=22001, DRIVER=4. 17. 30 16

CALL PROCC( P_TEXT ); END# Y NL DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN DECLARE V_MESSAGE_TEXT VARCHAR(1000); DECLARE V_DB 2_LINE_NUMBER INTEGER; DECLARE V_SERVER_NAME VARCHAR(10); DECLARE V_SQLCODE INTEGER; DECLARE V_PROC VARCHAR(128); GET CURRENT DIAGNOSTICS CONDITION 1 V_MESSAGE_TEXT = MESSAGE_TEXT, V_DB 2_LINE_NUMBER = DB 2_LINE_NUMBER, V_SERVER_NAME = SERVER_NAME, V_SQLCODE = DB 2_RETURNED_SQLCODE; SET V_PROC = GETVARIABLE(’SYSIBM. PACKAGE_NAME’); IF V_SQLCODE = -438 THEN RESIGNAL; ELSE SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = V_SERVER_NAME !!': '!!V_PROC!! V_DB 2_LINE_NUMBER !!': '!! V_SQLCODE !!': '!! V_MESSAGE_TEXT; END IF; END; CREATE PROCEDURE PROCC ( IN P_TEXT VARCHAR(100) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN DECLARE V_MESSAGE_TEXT VARCHAR(1000); DECLARE V_DB 2_LINE_NUMBER INTEGER; DECLARE V_SERVER_NAME VARCHAR(10); DECLARE V_SQLCODE INTEGER; DECLARE V_PROC VARCHAR(128); GET CURRENT DIAGNOSTICS CONDITION 1 V_MESSAGE_TEXT = MESSAGE_TEXT, V_DB 2_LINE_NUMBER = DB 2_LINE_NUMBER, V_SERVER_NAME = SERVER_NAME, V_SQLCODE = DB 2_RETURNED_SQLCODE; SET V_PROC = GETVARIABLE(’SYSIBM. PACKAGE_NAME’); IF V_SQLCODE = -438 THEN RESIGNAL; ELSE SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = V_SERVER_NAME !!': '!!V_PROC!! V_DB 2_LINE_NUMBER !!': '!! V_SQLCODE !!': '!! V_MESSAGE_TEXT; END IF; END; INSERT INTO DEMO VALUES(P_TEXT); END# SO CREATE PROCEDURE PROCA ( IN P_TEXT VARCHAR(100) ) BEGIN O z/ THE UGLY! APPLICATION RAISED ERROR WITH DIAGNOSTIC TEXT: DB 2 T: PROCC: 24: -302: THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER *N IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE 17

Obtaining DL/I data via IMS-transactions Y NL SO O z/ CREATE PROCEDURE CALL_IMS_TRANSACTION ( ) -- read/update DL/I using IMS-transactions! BEGIN DECLARE IMS_LTERM CHAR(8); DECLARE IMS_MODNAME CHAR(8); DECLARE IMS_DATA_OUT VARCHAR(32000); DECLARE USER_OUT VARCHAR(1022); DECLARE ERROR_MESSAGE VARCHAR(120); DECLARE RC INT; DECLARE GLOBAL TEMPORARY TABLE SESSION. GTT_RESULT ( ROW VARCHAR(100) ); CALL SYSPROC. DSNAIMS('SENDRECV', 'N', 'IMSHEXCF', 'SCSIMS 9 H', '', IMS_MODNAME, IMS_LTERM, '', 'IVTNO DISPLAY LAST 1 ', IMS_DATA_OUT, 'DSNAPIPE', '', USER_OUT, ERROR_MESSAGE, RC); END# 18

The GOOD! (But hidden) LY ON Public W LU Module M_Manage_person Variable Gv_debug integers Procedure add_person Procedure drop_person Private Procedure Debug 19

CREATE TABLE PERSON ( PID INT NOT NULL PRIMARY KEY, PNAME CHAR(10) NOT NULL )@ CREATE MODULE M_PERSON_MANAGE@ ALTER MODULE M_PERSON_MANAGE PUBLISH VARIABLE GV_DEBUG INTEGER DEFAULT 0@ ALTER MODULE M_PERSON_MANAGE ADD PROCEDURE DEBUG ( PIN_MSG VARCHAR(100) ) BEGIN CALL DBMS_OUTPUT. PUT_LINE( PIN_MSG ); END@ ALTER MODULE M_PERSON_MANAGE PUBLISH PROCEDURE ADD_PERSON ( PIN_PID INT, PIN_PNAME CHAR(10) ) BEGIN IF GV_DEBUG = 1 THEN CALL DEBUG('ADDING '||PIN_PNAME ); END IF; INSERT INTO PERSON ( PID, PNAME ) VALUES ( PIN_PID, PIN_PNAME ); END@ 20

ALTER MODULE M_PERSON_MANAGE PUBLISH PROCEDURE DROP_PERSON ( PIN_PID INT ) BEGIN IF GV_DEBUG = 1 THEN CALL DEBUG('DROPPING '||PIN_PID ); END IF; DELETE PERSON WHERE PID = PIN_PID; END@ SET SERVEROUTPUT ON@ BEGIN SET M_PERSON_MANAGE. GV_DEBUG = 1; CALL M_PERSON_MANAGE. ADD_PERSON(1, ‘TOM'); CALL M_PERSON_MANAGE. DROP_PERSON( 1 ); END@ 21

Lauri Pietarinen Relational Consulting lauri. pietarinen@relational-consulting. com DB 2 Stored Procedures The Good the Bad and the Ugly Please fill out your session evaluation before leaving!