Notes from DB2 9.5 SQL Procedure Developer Exam 735 Prep, Part 1: SQL Procedure Language

Notes from DB2 9.5 SQL Procedure Developer exam 735 prep, Part 1: SQL Procedure Language

and

Notes from DB2 9.5 SQL Procedure Developer exam 735 prep, Part 2: SQL Procedure Language (

Declare variable / Local variable declaration
Declare variable_name data_type Default null|constant
Do not name variable same as column name or parameter
DECLARE v_descrition VARCHAR(80);
DECLARE v1, v2 INT DEFAULT 0;
As of V9.5 array data types are supported.

1. First create data type in database
2. Declare it in the procedure
3. Arrays transient values that can not be stored in tables
4. Should be defined with schema and be unique on current server
5. LONG VARCHAR, LONG VARGRAPHIC, XML and user-defined types not supported
6. 1 based indexing
7. Specifying size of array is optional, but if used specifies maximum elements allowed in the array

Create Type array_name as data_type Array[. -> 2147483647]
DDL to define to database
CREATE TYPE numbers as INTEGER ARRAY[100];
CREATE TYPE names as VARCHAR(30) ARRAY[];
CREATE TYPE MYSCHEMA.totalcomp as DECIMAL(12,2) ARRAY[];
CREATE PROCEDURE PROC_VARRAY_test (out mynames names)
BEGIN
DECLARE v_pnumb numbers
SET v_pnumb = ARRAY[1,2,3,5,7,11];
SET mynames(1) =’MARINA’;
…
END
Variable assignment / There are several methods to assign a value(s) to a variable
1. Set variable_name = value|expression|null;
SET var1 = 10;
SET total = (select sum(c1) from T1);
SET v_numb(10) = 20
SET v_numb = ARRAY[1,2,3,4]; -- fill up array with values
2. Use VALUES INTO or SELECT (or FETCH) INTO
VALUES 2 INTO v1;
SELECT SUM(c1) INTO var1 FROM T1;
VALUES CURRENT DATE INTO cdate;
Cursors / Declare cursor_name Cursor for
Select …..
Without hold|with hold without return|with return
To caller|to client
DECLARE v_dept CHAR(3) DEAFULT ‘ ‘;
DECLARE myres_set CURSOR
FOR SELECT empno, lastname, job, salary, comm.
FROM employee
WHERE workdept = v_dept;
Note no ‘:’ before the use of v__dept (not like Cobol)
To return open cursor from procedure use ‘Dynamic Results Sets n’ in procedure definition
CREATE PROCEDURE emp_from_dept()
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE c_emp_dept CURSOR WITH RETURN
FOR SELECT empno, lastname, job, salary, comm.
FROM employee
WHERE workdept = ‘E21’;
OPEN c_emp_dept;
END P1
Conditional Statements / Two conditional statements supported IF and CASE
IF (condition) THEN
SET ….; -- note ‘;’ used at end of statement
ELSEIF (condition) THEN
SET … ; -- and here
ELSE
… ; -- and here
END IF;
CASE
WHEN (condition) THEN
SET … ; -- again ‘;’ used
WHEN (condition) THEN
SET … ;
ELSE
SET …;
END CASE;
Iteration / 4 types of iteration via LOOP, WHILE, REPEAT, FOR
1, A simple loop
L1: LOOP -- L1 is label
SQL statements;
LEAVE L1; -- leave L1 Loop
END LOOP L1;
2. WHILE - a check condition on entrance
WHILE condition
DO
SQL statements
END WHILE;
3. REPEAT – a check before exit (repeat UNTIL)
REPEAT
SQL statements;
UNTIL condition
END REPEAT;
4. FOR loop – an implicit loop over result set
FOR loop_name AS
SELECT … FROM -- open/fetch done implicitly
-- values from select accessible via
-- reference loop:name.column_name so no local
-- variable definition needed.
DO
SQL statements;
SET …; -- etc
END FOR;
Exception handling / User named conditions can be specified for later use in the compound statement it is declared in
DECLARE condition_name CONDITION FOR
SQLSTATE (VALUE)| string_constant
DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE ‘23503’;
DECLARE overflow CONDITION FOR SQLSTATE '22003';
Condition handlers are declared to define the logic to be taken by a procedure in the event of a SQLException or non SQLCODE=0 condition
DECLARE CONTINUE|EXIT|UNDO HANDLER FOR
Specific_condition|general_condition SQL_procedure_statement
WHERE specific_condition_value (SQLSTATE (VALUE) string_constant) |condition_name

1. An example where exception handling causes exit from procedure

CREATE PROCEDURE simple_error
(IN new_job CHAR(8), IN p_empno CHAR(6),
OUT p_state_out CHAR(5),OUT p_code_out INT)
SPECIFIC simple_error1
BEGIN
DECLARE SQLCODE INT DEFAULT 0; -- locally
-- defined
DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’; -- ditto
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT SQLSTATE, SQLCODE
INTO p_sqlstate_out, p_sqlcode_out
FROM SYSIBM.SYSDUMMY1;
-- if update fails handler above puts sqlstate and
-- sqlcode into output parms
UPDATE EMPLOYEE
SET job = new_job
WHERE empno = p_empno;
END

1. An example where processing continues with exception

CREATE PROCEDURE proc1 (IN num int, IN new_status varchar(10))
P1: BEGIN
DECLARE SQLCODE INTEGER default 0;
DECLARE SQLSTATE CHAR(5) default ‘ ‘;
DECLARE v_trunc INTEGER default 0;
DECLARE overflow CONDITION FOR SQLSTATE '22001';
-- declare it before it is needed
DECLARE CONTINUE HANDLER FOR overflow
BEGIN
INSERT INTO tab1 VALUES (num, substr
(new_sataus,1,5));
SET v_trunc = 2;
END; -- end of handler
INSERT INTO tab1 VALUES(num, new_status);
RETURN v_trunc;
END P1

1. Forcing an exception via SIGNAL

SIGNAL (SQLSTATE (value) sqlstate_string_constant|variable_name)| condition_name
SET MESSAGE_TEXT = some_message
CREATE PROCEDURE sign_test (IN num int, IN new_status varchar(10))
P1: BEGIN
DECLARE SQLCODE INTEGER default 0;
DECLARE SQLSTATE CHAR(5) default '';
IF length (new_status) > 5 THEN
SIGNAL SQLSTATE '72001' SET MESSAGE_TEXT = 'INPUT VALUE TOO LONG';
END IF;
INSERT INTO TAB1 VALUES (num, new_status);
END P1
Execution environment / SQL Procedure Language programs reside in the database and execute in the same address space as the DB2 database (eg. NOT FENCED)
Other stored procedure languages (Cobol, Java, etc) execute in separate address spaces (FENCED)
Procedure definition / CREATE PROCEDURE proc_name
IN, OUT, INOUT parameters
SPECIFIC specific_name
DYNAMIC RESULT SETS N
MODIFIES SQL DATA|CONTAINS SQL|READS SQL DATA
DETERMINISTIC|NOT DETERMISTICS
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
OLD SAVEPOINT LEVEL|NEW SAVEPOINT LEVEL
LANGUAGE SQL
EXTERNAL ACTION|NO EXTERNAL ACTION
PARAMETER CCSID ASCII|UNICODE
SQL procedure body - compound statement
Compound Statement /

1. General Syntax

Label BEGIN NOT ATOMIC|ATOMIC
Local-variable declaration
Cursor-declaration
Handler-declaration
Assignment, flow control, sql statements,…
END label
NOT ATOMIC – if unhandled error occurs no SQL statements are rolled back
ATOMIC – if unhandled error occurs then all SQL statements executed up to that point are rolled back.
ATOMIC can not be nested inside other ATOMIC compound statements

1. Compound statements can be nested or follow one another.
2. Variables are visible only within the compound statement where they are defined OR within a nested compound statement.

Calling a procedure / Call procedure_name(parm1 parm2 ) (or NULL for null variable)
Procedure calls can be nested (one procedure calls another)
•Variables and parameters are strongly typed (they must match)
•Local variables are matched to the stored procedure by their position
•All parameters must have a value
•Overloaded procedures are determined by the number of parameters
Passing back and retrieving return codes /

1. To return from a procedure

RETURN;

1. To pass back a return code

RETURN 2 (Must be integer)

1. To get the return code within the calling procedure

GET DIAGNOSTICS ret_code = DB2_RETURN_STATUS;
Reading cursor opened in nested procedure /

1. Declare a result set locator using the following syntax:

DECLARE rs_locator_var1 RESULT_SET_LOCATOR VARYING;

1. Associate this result set locator with the calling procedure:

ASSOCIATE RESULT SET LOCATOR( rs_locator_var1) WITH PROCEDURE proc_called;
3. Allocate the cursor that points to the result set from the calling procedure:
ALLOCATE cursor1 CURSOR FOR RESULT SET rs_locator_var1;

1. Use fetch as you normally would

FETCH cursor1 into var1, var2, …: -- var’s
-- previously declared.
For example:
CREATE PROCEDURE Use_nested_cursor (deptin int, OUT tot_dept_comm DEC(12,2))
BEGIN
DECLARE sqlcode int default 0;
DECLARE v_comm DECIMAL(12,2) DEFAULT 0.0;
DECLARE v_name, v_location varchar(20);
DECLARE v_job char(6);
DECLARE LOC1 RESULT_SET_LOCATOR VARYING;
SET tot_dept_comm = 0;
CALL result_from_cursor(deptin);
ASSOCIATE RESULT SET LOCATOR( LOC1) WITH PROCEDURE result_from_cursor;
ALLOCATE C1 CURSOR FOR RESULT SET LOC1;
FETCH FROM C1 INTO v_name,v_job,v_comm,v_location;
WHILE sqlcode = 0 DO
SET tot_dept_comm = tot_dept_comm + v_comm;
FETCH FROM C1 INTO v_name,v_job,v_comm,v_location;
END WHILE;
END
Global variables / Global variables are variables defined in a DB2 session but outside of a procedure. The variables can be accessed by any procedure during that session
CREATE VARIABLE global_var DATATYPE [DEFAULT value];
Assuming global_var defined as INTEGER
CREATE PROCEDURE …
BEGIN
SELECT COUNT(*) INTO global_var FROM …
END
CREATE PROCEDURE …
BEGIN
IF global_var > …
END
Creating a stored procedure /

1. Prequistes for creating a stored procedure

• Must have the privileges required to execute the CREATE PROCEDURE statement
• Must have the privileges to execute all SQL statements in the stored procedure
• All database objects (example, tables, views, functions, other procedures) that are referenced in this SQL procedure should exist in the database
• A successful database connection must be established from the CLP (this can be achieved by using the following db2 command : db2 connect to sample user userid using password )

1. Put procedure code in file (eg. Number_of_orders.db2 ) and use either a @ or ! as an alternate terminator

CREATE PROCEDURE NUMBER_OF_ORDERS ( in_status varchar(10), in_date DATE, out num_of_order int)
-- SQL Procedure
P1: BEGIN
declare v_number INTEGER DEFAULT 0;
SELECT count(poid)
INTO v_number
FROM PURCHASEORDER
where ucase(status) = ucase(in_status)
and orderdate < in_date;
SET num_of_order = v_number;
END P1 @

1. Go into CLP and execute the script to create the stored procedure.

db2 -td@ -vf myscript.db2
The general syntax of the command is:
db2 -td <terminating-character>
-vf <CLP-script-name>
-td reset terminating chars with …
-v verbose option, display each line
-f target of the command is a file

1. Test the procedure

db2 call number_of_orders('Shipped'
,current date, ?)
Authorizations / To create an SQL procedure the user needs

1. BINDADD on the database
2. IMPLICIT_SCHEMA if new schema used
3. Or CREATE_IN on the schema if exists

The user implicitly gets EXECUTE and GRANT EXECUTE authority
Callers of the sp need EXECUTE authority
SQL Access levels / NO SQL: no SQL statement can exist in the stored procedure
CONTAINS SQL: no SQL statement can modify or read data in the stored procedure
READS SQL: no SQL statement can modify data in the stored procedure
MODIFIES SQL: SQL statements can both modify or read data in the stored procedure (default)
A stored procedure at a lower level access (eg. NO SQL) can not call another SP at a higher level (eg. MODIFIES SQL)
Transferring SQL / To extract SQL procedure
GET ROUTINE INTO file_name FROM (SPECIFIC) PROCEDURE routine_name (HIDE BODY)
HIDE BODY will encrypt the procedure
To load the SQL procedure
PUT ROUTINE FROM file_name OWNER new_owner (USE REGISTERS)