DB2 Stored Procedure Language

Are you new to DB2? Are you making a transition from either Oracle, Sybase or SQL Server? Are you trying to learn DB2 to make your resume look better? One of the immediate challenge for you to is to learn new stored procedure (SQL PL) language quickly. But if you have already used PL/SQL or T-SQL for your stored procedures, you are already half-way through to start programming in DB2 SQL PL language.

Learn from the experts who have coded in this language. In my opinion, this is the best way and this is how I learnt different programming languages in my career.

I will point you to an article written by Serge Rielau on how to backup and restore a schema in DB2. This article explains backup and restore of a schema in a way different than a traditional DBA will do using core DB2 tools. This is very useful when you want to accomplish backup / restore of the schema programmatically using SQL PL stored procedures. Please see the article for how to do the backup / restore of the schema using the code but I am going to use this code to explain few features of SQL PL.

First of all you will put together your scripts in a file and you have to define your SQL statement terminator.

One of the option is to specify it when you run the script using DB2 command by using a switch for a statement terminator. For example if you use @ as the statement terminator, your command line may look like:


$ db2 -td@ -f script.sql


The other option is to define the statement terminator in the script itself as shown below:


--#SET TERMINATOR @


You can use following command to run the script without specifying the statement terminator.


$ db2 -f script.sql


In Oracle, you need to create a USER to use a SCHEMA but this is not the case in DB2. DB2 supports true schema and it is just a container to hold objects together. If you use SET SCHEMA, you are specifying a qualifier for all the objects that you create after setting your SCHEMA. You need to have IMPLICITSCHEMA authority to be able to create schema on the fly.


SET SCHEMA = ADMIN
@


If you worked in Oracle, you might have created synonyms for your tables, procedures etc. DB2 allows you to create synonyms for tables but there is no need for creating the synonyms for procedures or functions as DB2 provides another way to find them by using SET PATH.


SET PATH = SYSTEM PATH, ADMIN
@


This sounds familiar to you but remember this statement create a table similar to the existing table but without data. Oracle will create the table and bring the data also. Just know the difference.


CREATE TABLE COLUMNS LIKE SYSSTAT.COLUMNS
@


Look at the same function below written in two different ways. This one uses just a single statement with RETURN.

 
CREATE FUNCTION LOC_MATCH(src VARCHAR(150), dst VARCHAR(50))
RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN CASE
          WHEN src IS NULL THEN NULL
          WHEN LOCATE(dst, src) > 0 THEN 2
          ELSE 1
       END
@

The function shown below uses a BEGIN ATOMIC for compound SQL statements. Sometime you can not remove compound statements but avoid them if you can. The same function as shown above will perform better than the one shown as below. The reason is – BEGIN ATOMIC is costlier than above example since a single RETURN is like evaluating an expression and that is easy for DB2.

 
CREATE FUNCTION LOC_MATCH(src VARCHAR(150), dst VARCHAR(50))
RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
    IF src IS NULL THEN
       RETURN NULL;
    ELSEIF LOCATE(dst, src) > 0 THEN
       RETURN 2;
    ELSE
       RETURN 1;
    END IF;
END
@

Look at the following two functions. Do you see a different DROP for the same overloaded function? Why – it is easier to drop an overloaded function or stored procedure by using its specific name rather than using a full function declaration to drop it.

 
DROP SPECIFIC FUNCTION DECODE3
@
CREATE FUNCTION DECODE(flag    VARCHAR(1),
                       yestext VARCHAR(30),
                       notext  VARCHAR(30))
RETURNS VARCHAR(30)
SPECIFIC DECODE3
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN CASE flag WHEN 'Y' THEN yestext
                 WHEN 'N' THEN notext
                 ELSE '' END
@  
DROP SPECIFIC FUNCTION DECODE4
@
CREATE FUNCTION DECODE(flag     VARCHAR(1),
                       yestext  VARCHAR(30),
                       notext   VARCHAR(30),
                       elsetext VARCHAR(30))
RETURNS VARCHAR(30)
SPECIFIC DECODE4
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN CASE flag WHEN 'Y' THEN yestext
                 WHEN 'N' THEN notext
                 ELSE elsetext END
@  

Look at these two overloaded functions. Do you see the difference? The author uses CASE WHEN approach to eliminate IF THEN ELSE construct to avoid BEGIN ATOMIC.

 
DROP SPECIFIC FUNCTION composetype6
@
CREATE FUNCTION composetype(typeschema  VARCHAR(128), 
                            typename    VARCHAR(128),
                            length      INTEGER,
                            scale       INTEGER,
                            codepage    INTEGER,
                            pschemaname VARCHAR(128))
RETURNS VARCHAR(350)
SPECIFIC composetype6
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN CASE WHEN typeschema NOT IN ('SYSIBM', pschemaname) 
            THEN '"' || typeschema || '".' ELSE '' END
    || CASE WHEN typeschema = 'SYSIBM' 
            THEN typename
            ELSE '"' || typename || '"' END
    || CASE WHEN typeschema = 'SYSIBM'
             AND typename IN ('CHARACTER', 'VARCHAR', 
                              'BLOB', 'CLOB', 'DECIMAL')
            THEN CASE WHEN length = -1
                      THEN '()'
                      ELSE CASE WHEN typename = 'DECIMAL'
                                THEN '(' || RTRIM(CHAR(length)) || ',' 
                                     || RTRIM(CHAR(scale)) || ')'
                                ELSE '(' || RTRIM(CHAR(length)) || ')'
                                END
                      END
            ELSE '' END
    || CASE WHEN typename IN ('CHARACTER', 'VARCHAR')
             AND codepage = 0
             THEN ' FOR BIT DATA'
             ELSE '' END
@

If you are accustomed to using DEFAULT value with function or stored procedure arguments in PL/SQL or T-SQL, you can use overload function as shown below to achieve the same thing.

 
DROP SPECIFIC FUNCTION composetype3
@
CREATE FUNCTION composetype(typeschema  VARCHAR(128), 
                            typename    VARCHAR(128),
                            pschemaname VARCHAR(128))
RETURNS VARCHAR(350)
SPECIFIC composetype3
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN composetype(typeschema, typename, -1, 0, -1, pschemaname)
@

Take a moment to look at this function below which returns 1 or 0 if a column exists in a table or not. This function uses BEGIN ATOMIC and I could not avoid it. But, there is something else that I want to show it to you. The arguments that I am using in this code are schemaName, tabName and colName and you can not use the same column name in the WHERE predicate to compare as that will always evaluate to true. Generally, the tendency is to change the name of the arguments so that they do not conflict with the actual column name. But, did you know that you can qualify the name of the argument with the function name as shown below?

CREATE FUNCTION IS_COL_EXISTS
(
schemaName VARCHAR(128),
tabName VARCHAR(128),
colName VARCHAR(128)
)
RETURNS INTEGER
SPECIFIC IS_COL_EXISTS
READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
   IF EXISTS (SELECT 1 FROM SYSCAT.COLUMNS
              WHERE TABSCHEMA = schemaName
              AND   TABNAME   = IS_COL_EXISTS.tabName
              AND   COLNAME   = IS_COL_EXISTS.colname) THEN
     RETURN 1;
   ELSE
     RETURN 0;
   END IF;             
END
@

Look at this procedure to drop all foreign key constraints. You might want to know as what is so special about this small piece of code. It tells how you should use the FOR … DO … END FOR construct if you want to process a cursor. If you have been a veteran in coding stored procedures, you might have started your coding practice by opening a cursor, fetching it and exiting the loop after checking if there are no more rows to process. This is made simple by using this construct. Even though there are other constructs in SQL PL to do the same thing but by far the most, this is much simpler and elegant.

CREATE PROCEDURE DROP_ALL_FOREIGN_KEYS
(
IN schemaName VARCHAR(128)
)
SPECIFIC DROP_ALL_FOREIGN_KEYS
MODIFIES SQL DATA NO EXTERNAL ACTION DETERMINISTIC
BEGIN
  -- Drop all foreign key constraints
  FOR theseFKeys
  AS
  SELECT 'ALTER TABLE '||TRIM(schemaName)||'.'||
          TRIM(tabName)||' DROP CONSTRAINT '||
          constname AS FKConst
          FROM SYSCAT.TABCONST
         WHERE tabschema = schemaName
           AND type = 'F' 
  DO
      EXECUTE IMMEDIATE theseFKeys.FKConst;
  END FOR;
END  
@

There are many things to learn in the following code snippet.

• Learn how you can call db2load system procedure to load the data in a table through a stored procedure. Isn’t that wonderful?
• See the usage of the dynamic SQL statement for ALTER TABLE statement. You can turn-off the logging temporarily by using ACTIVATE NOT LOGGED until first commit is used.
• See the usage of the SELECT tabname FROM (VALUES (‘COLDIST’), (‘COLUMNS’), (‘INDEXES’), (‘TABLES’)) AS T(tabname) statement. The FOR loop is executed to load the data in the 4 tables by using SELECT .. FROM (VALUES ..) statement.
• See the usage of the dynamic SQL statement by using the parameter markers.

  IF copymode = 0 THEN
    FOR thisStatistik AS cur CURSOR WITH HOLD FOR
      SELECT tabname FROM (VALUES 
       ('COLDIST'), ('COLUMNS'), ('INDEXES'), ('TABLES'))
       AS T(tabname)
    DO
      SET txt = 'ALTER TABLE "' || adminschema || '".'
               || tabname || ' ACTIVATE NOT LOGGED INITIALLY '
               || 'WITH EMPTY TABLE';
      EXECUTE IMMEDIATE txt;
      CALL db2load(1, '', 
             'LOAD FROM ' || loaddirectory || 'SYSSTAT_' || 
             || ' tabname || '.IXF'
             || ' OF IXF LOBS FROM ' || loaddirectory || ' MESSAGES '
             || messagedirectory || 'SYSSTAT_' || tabname || '.MSG' 
             || ' INSERT INTO ' || adminschema || '.' || tabname,
             load_sqlcode, load_sqlmessage,
             load_read, load_skipped, load_loaded, load_rejected,
             load_deleted, load_committed, load_part_read,
             load_part_rejected, load_part_partitioned, 
             load_mpp_summary);
      IF load_sqlcode <> 0 THEN
        SET load_sqlmessage = LTRIM(CHAR(load_sqlcode)) 
                            || '|SYSSTAT_' || tabname || '|' 
                            || COALESCE(load_sqlmessage, '');
        SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = load_sqlmessage;
      END IF;         
    END FOR;
  END IF;
  SET txt = 'UPDATE SYSSTAT.COLDIST SET (colvalue, valcount, distcount) '
         || '= (?, ?, ?) WHERE tabschema = ? '
         || 'AND tabname = ? AND colname = ?';
  PREPARE stmt FROM txt;