SQL stored procedure text

You can view a SQL stored procedure by doing the following select


db2 -x "select text from syscat.routines where routinename='TOTAL_RAISE'"

If you do not use the -x option and run the command as
db2 "select text from syscat.routines where routinename='TOTAL_RAISE'"

you would get some junk characters included ..Following is the example output without -x option

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------CREATE PROCEDURE total_raise ( IN p_min DEC(4,2) ,IN p_max DEC(4,2) ,OUT p_total DEC(9,2) )SPECIFIC total_raiseLANGUAGE SQLtr: BEGIN -- Declare variables DECLARE v_salary DEC(9,2); DECLARE v_bonus DEC(9,2); DECLARE v_comm DEC(9,2); DECLARE v_raise DEC(4,2); DECLARE v_job VARCHAR(15) DEFAULT 'PRES'; -- Declare returncode DECLARE SQLSTATE CHAR(5);
-- Procedure logic DECLARE c_emp CURSOR FOR SELECT salary, bonus, comm FROM employee WHERE job != v_job; -- (1)
OPEN c_emp; -- (2)
SET p_total = 0;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; -- (3)
WHILE ( SQLSTATE = '00000' ) DO SET v_raise = p_min;
IF ( v_bonus >= 600 ) THEN SET v_raise = v_raise + 0.04; END IF;
IF ( v_comm < 2000 ) THEN SET v_raise = v_raise + 0.03; ELSEIF ( v_comm < 3000 ) THEN SET v_raise = v_raise + 0.02; ELSE SET v_raise = v_raise + 0.01; END IF;
IF ( v_raise > p_max ) THEN SET v_raise = p_max; END IF;
SET p_total = p_total + v_salary * v_raise; FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; END WHILE;
CLOSE c_emp; -- (4)END tr

6 byte RID support in V9

6 byte RID in V9

V8 and prior versions of DB2 limited the size of a RID to be 4 bytes.3 bytes for page and 1 byte for slot number.This limitation placed constraints on the tablespace sizes:
Pagesize--------Tablespace size limitation
4k page 64GB
8k page 128GB
16k page 256GB
32k page 512GB

V9 has 6 byte RID which allows for bigger tablespaces.4 bytes for page and 2 bytes for slot number.
Pagesize--------Tablespace size limitation
4k page 2TB
8k page 4TB
16k page 8TB
32k page 16TB

ALTER TABLESPACE TABLESPACENAME CONVERT TO LARGE statement can be used to convert tablespaces after the migratrion.It should be noted that only DMS tablespaces can be large.So,SMS tablespaces can not be large tablespaces even in V9.