F 15 Fundamentals of DB 2 SQL Procedures

F 15 Fundamentals of DB 2 SQL Procedures Philip K. Gunning, DGI Anaheim, CA Sept 9 - 13, 2002

Outline • • Background Installation and Preparation Setup Development Center (SPB, et al) Building SQL Procedures Case Study Performance Considerations Summary IBM Data Management Technical Conference

Background IBM Data Management Technical Conference

Background • First appeared in DB 2 UDB V 7. 1 – DB 2 for OS/390 V 6. 1 • Other Database Vendors have had their own proprietary stored procedure languages for some time • Search on YAHOO! found 78, 000 hits on “Oracle Stored Procedures” • Search on YAHOO! Found 31, 600 hits on “Sybase Stored Procedures” IBM Data Management Technical Conference

Background • Search on YAHOO! Found 25, 200 hits on “DB 2 Stored Procedures” • Search on YAHOO! Found 44, 500 hits on “Transact SQL” • Search on YAHOO! Found 11 hits on “PL/SQL” IBM Data Management Technical Conference

Background • Many applications have been written in their entirety in a stored procedure language – Flexibility – Enable Rapid Application Development – Minor learning curve since similar to SQL • Portability Across Platforms – Cross Platform Development and Debugging IBM Data Management Technical Conference

Installation and Preparation IBM Data Management Technical Conference

Installation and Preparation • UNIX – Application Development Client – AIX V 4. 3. 3 ML 9 or 5. 1 ML 1 – IBM C for AIX Version 5. 0 – IBM Visual Age C++ Version 5. 0 • Windows XP, . NET, 2000, NT, ME, 98 IBM Data Management Technical Conference

Installation and Preparation • Change DBM CFG Parameter KEEPFENCED (KEEPDARI) to NO while developing and testing routines – If not, changes will not be reloaded as process that runs the routine will remain active • db 2 update dbm cfg using KEEPFENCED no IBM Data Management Technical Conference

Installation and Preparation • UNIX – Set two registry values – DB 2_SQLROUTINE_COMPILER_PATH – DB 2_SQLROUTINE_COMPILE_COMMAND – PATH and LIBPATH environment variables set in db 2 profile and call to it is placed in. profile • Can either use the db 2 set cmd or use the SQL Stored Procedure Builder Options dialog form the Development Center to set the values of these DB 2 registry variables IBM Data Management Technical Conference

Installation and Preparation • Windows – Application Development Client – SQL Procedures supported on NT, 2000, XP and. NET Server – DB 2_SQLROUTINE_COMPILER_PATH – DB 2_SQLROUTINE_COMPILE_COMMAND • Need not be set if environment variables for compiler set as SYSTEM variables IBM Data Management Technical Conference

Installation and Preparation • MVS Conformance Flagger – Used to Flag SQL for procedures developed on workstation which are being migrated to DB 2 for OS/390 or Z/OS – ISO/ANSI SQL 92 Entry Level Standard • http: //www 7 b. boulder. ibm. com/dmdd/librar y/techarticle/0204 milligan. ht ml IBM Data Management Technical Conference

Installation and Preparation • Management Tools FMID required for DB 2 for Z/OS or OS/390 – REXX Support for DSNTPSMP SP Creation – C compiler – Workload Manager and appropriate WLM application environments – DB 2 V 6. 1 and above • SQL Reference for Cross Platform Development IBM Data Management Technical Conference

Installation and Preparation • Recoverable Resource Manager Services attachment facility (RRSAF) required on Z/OS IBM Data Management Technical Conference

SQL • Procedure created via CREATE PROCEDURE COMMAND • CREATE PROCEDURE update_salary ( IN employee_number CHAR(6), IN rating INT ) IBM Data Management Technical Conference

Attendee Notes P Create procedure may fail in the precompile or compile stage. DB 2 will create a log file that contains error messages. This log will be placed in the /sqlproc/db_name/schema_name/tmp or sqlprocdb_nameschema_nametmp directory on Windows. IBM Data Management Technical Conference

SQL • SQL-Procedure Body keyword of CREATE PROCEDURE statement specifies the SQL statement that is the body of the SQL Procedure • Multiple SQL Procedure statements can be specified within a compound statement IBM Data Management Technical Conference

SQL CREATE PROCEDURE bump_salary (IN deptnumber SMALLINT) LANGUAGE SQL BEGIN DECLARE SQLSTATE CHAR(5); DECLARE v_salary DOUBLE; DECLARE v_id SMALLINT; DECLARE v_years SMALLINT; DECLARE at_end INT DEFAULT 0; DECLARE not_found CONDITION FOR SQLSTATE '02000'; DECLARE C 1 CURSOR FOR SELECT id, CAST(salary AS DOUBLE), years FROM staff WHERE dept = deptnumber; DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; Copyright IBM Corp. Sample Procedure IBM Data Management Technical Conference

OPEN C 1; FETCH C 1 INTO v_id, v_salary, v_years; WHILE at_end = 0 DO CASE WHEN (v_salary < 2000 * v_years) THEN UPDATE staff SET salary = 2150 * v_years WHERE id = v_id; WHEN (v_salary < 5000 * v_years) THEN CASE WHEN (v_salary < 3000 * v_years) THEN UPDATE staff SET salary = 3000 * v_years WHERE id = v_id; ELSE UPDATE staff SET salary = v_salary * 1. 10 WHERE id = v_id; END CASE; ELSE UPDATE staff SET job = 'PREZ' WHERE id = v_id; END CASE; FETCH C 1 INTO v_id, v_salary, v_years; END WHILE; CLOSE C 1; END @ Copyright IBM Corp. Sample Procedure SQL IBM Data Management Technical Conference

Attendee Notes P The procedure body consists of a procedure-name, that names the procedure being defined. In dynamic SQL, CURRENT SCHEMA is used as a qualifier for an unqualified name and the QUALIFIER bind option for static SQL. The unqualified name with the number of parameters is unique within its schema, but does not need to be unique across schemas. This is know as overloading in other languages. IN, OUT, or INOUT identifies the parameters of the procedure and specifies the mode, name and data type of each parameter. P Specific-name is used for commenting on or dropping the procedure. It can never be used to invoke the procedure. P Dynamic results is an upper bound for returned result sets. P Contains, Reads, and Modifies SQL DATA P LANGUAGE SQL FOR SQL PROCEDURES P SQL PROCEDURE BODY specifies the SQL statement that is the body of the SQL procedure. IBM Data Management Technical Conference

SQL • CALL statement invokes stored procedure – Has a procedure signature which is a combination of schema, procedure name and number of parameters • Returning Result Sets – Declare cursor on result set, open result set, and leave cursor open when exiting procedure – only unread rows are passed back – use nulls to improve performance IBM Data Management Technical Conference

SQL • If result sets are required then RETURN to CALLER or RETURN TO CLIENT must be specified – RETURN TO CALLER results are only visible to program at previous nesting level – RETURN TO CLIENT results are visible only if procedure was invoked from a set of nested procedures, function or method invalidates this – Only visible to client application that made the call IBM Data Management Technical Conference

SQL • See Appendix N, DB 2 UDB SQL Reference for complete list of SQL statements allowed in SQL routines • See DB 2 for Z/OS SQL Reference for that platform IBM Data Management Technical Conference

SQL • Condition Handlers – Control passed to condition handler if a condition is raised and you have declared a handler for the respective condition – If no handler, DB 2 passes control to next statement in procedure body • SQLCODE and SQLSTATE will contain corresponding values if they were declared IBM Data Management Technical Conference

Attendee Notes P If a statement raises an SQLWARNING or NOTFOUND condition, DB 2 passes control to the handler you declared. If a statement raises an SQLEXCEPTION condition control is passed to the handler for that specific condition. IBM Data Management Technical Conference

SQL • SIGNAL and RESIGNAL Statements – can be used to explicitly raise a specific SQLSTATE • CALL is now a compiled statement and can be run from a command line IBM Data Management Technical Conference

Development Tools IBM Data Management Technical Conference

Development Center IBM Data Management Technical Conference

Stored Procedure Builder IBM Data Management Technical Conference

SQL Procedure IBM Data Management Technical Conference

Deploy IBM Data Management Technical Conference

Case Study • Texas Department of Public Safety • Combined 8 legacy applications into 1 using all SQL Procedures • Started out on DB 2 UDB V 7. 1 – JAVA Stored Procedures – Got proficient with SPB and development on Windows • Migrated to DB 2 for OS/390 V 6. 1 • Switched to SQL Procedures IBM Data Management Technical Conference

Case Study • Developed concurrently on DB 2 for Windows whilst connectivity to OS/390 was being established – TCP/IP – DDF – CICS – Workload Manager (WLM) – WLM Stored Procedures IBM Data Management Technical Conference

Case Study • After DB 2 V 6. 1 and OS/390 configured developed over 300 SQL Procedures • Implemented Texas Crime Information Center application totally in SQL Procedures • Credited flexibility and ease of development in streamlining the typical development timeline IBM Data Management Technical Conference

Case Study • • Used DB 2 Management Clients package DB 2 Control Center DB 2 Stored Procedure Builder DB 2 Visual Explain IBM Data Management Technical Conference

Case Study • Still did good Logical and Physical Design • Extensive use of Identity Columns • Developed good indexes via DB 2 Explain and SQL Reviews • Attaining sub-second response • Continue to add new features/functionality using SQL Procedures IBM Data Management Technical Conference

Case Study • Lessons Learned – Want to be leading-edge but not necessarily bleeding-edge! – Lot’s of time spent getting TCP/IP, DB 2 for OS/390 V 6. 1, DSNTSMP stored procedure working along with WLM – Ran into 32 k limitation and had to use multiple stored procedures – Starting/Stopping/Purging WLM procedures took some time to develop IBM Data Management Technical Conference

References · SC 09 -4848 -00, What’s New, V 8. 1 · DB 2 UDB SQL Reference, Volume 1 and 2 · SC 09 -4825 -00, DB 2 UDB Application Building Guide: Building and Running Applications V 8. 1 · SC 09 -4827 -00, DB 2 Application Development Guide: Programming Server Applications IBM Data Management Technical Conference

References · DB 2 UDB Command Reference, V 8. 1 · http: //www. ibm. com/software/data/db 2/d ata/udb/ad · ftp: //ftp. software. ibm. com/ps/db 2/info · SC 26 -9944 -01 DB 2 for Z/OS SQL Reference · SC 26 -9933 -01 DB 2 for Z/OS Application Programming and SQL Guide IBM Data Management Technical Conference

References • http: //www. ibm. com/software/data/db 2/os 3 90/spb IBM Data Management Technical Conference

References IBM Data Management Technical Conference

F 15 Thanks! Fundamentals of DB 2 SQL Procedures pgunning@breakthroughdb 2. com Anaheim, CA Sept 9 - 13, 2002