Functions To Consider

There are some differences between db2's functions and procedures. I have experiences some limitations especially on the debugging side.

Below is a link with examples:
http://www.channeldb2.com/group/db29fundamentalscertificationworkshop/forum/topics/functions-stored-procedures

Because of limited output of the functions in db2 most of the time the output of functions is limited debugging often occurs with elimination of table insert/updates.

1. Debugging Using Insert/Update

Example:
BEGIN NOT ATOMIC
    FOR REC AS C1 CURSOR WITH HOLD FOR
        SELECT 'TEST' col1,
                       ROW_NUMBER() OVER() as ROWNUM
        FROM TABLE
    DO
        CALL FUNCTION1;--
        CALL FUNCTION2;--
    END FOR;--
END;

Some good practices:
    Add a row counter for interval commits.
    Add indentation for readability.
 

Below is the merge for that can be added in the function to update the current row it is busy with...

MERGE INTO TABLE USING
    (VALUES ('DEBUG','COUNT',ROWNUM)) AS LV (NAME,REF,VALUE)
ON L.NAME = LV.NAME AND L.REF = LV.REF
WHEN MATCHED THEN
    UPDATE SET L.VALUE = LV.VALUE
WHEN NOT MATCHED THEN
    INSERT (NAME,REF,VALUE)
    VALUES ('DEBUG','COUNT',0);--

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_merge.htm




Got a very slow running cursor that inserted/updated very slow. For reference the cursor would have run 5 days if it ran non stop, with the loads it took about 3 hours.


DECLARE CURSOR1 CURSOR FOR
    SELECT  ... ;

LOAD FROM CURSOR1 OF CURSOR      
    INSERT INTO TABLE (col1,col2) NONRECOVERABLE;
COMMIT;

Other Options:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0008305.html

How to get the function/procedure text:

select text from syscat.ROUTINES where

routinename='MY_STORED_PROCEDURE'

A function to enable/disable Foreign Keys.

CREATE OR REPLACE PROCEDURE dbatb.alter_fkey

( IN schema_name VARCHAR(30) , IN ACTION CHAR(1) )

LANGUAGE SQL

SPECIFIC alterfkey

BEGIN

DECLARE ALTER_ACTION VARCHAR(15);--

DECLARE STATEMENT VARCHAR(1000);--

IF ACTION NOT IN ('E','e','D','d') THEN

SIGNAL SQLSTATE '80000'

SET MESSAGE_TEXT='Invalid FKEY Action. Valid options are:(E)nable (D)isable';--

END IF;--

SET ACTION = UPPER(ACTION);--

SET ALTER_ACTION = CASE WHEN ACTION = 'E' THEN 'ENFORCED' WHEN ACTION = 'D' THEN 'NOT ENFORCED' END ;-- 

FOR QRY AS c1 CURSOR WITH HOLD FOR SELECT TABSCHEMA, TABNAME, CONSTNAME FROM SYSCAT.REFERENCES WHERE TABSCHEMA = UPPER(schema_name)

DO 

                SET statement = 'ALTER TABLE ' || TABSCHEMA||'.'||TABNAME || ' ALTER FOREIGN KEY  ' || CONSTNAME || ' ' || ALTER_ACTION || ' ';--

                EXECUTE IMMEDIATE statement;--

END FOR;--

END;