I saw the movie Inception over the weekend and after reading What a Programmer Sees When He Watches Inception, in which Marke Hallowell compared the dreams-within-dreams concept from the movie to a recursive function, I think I too see parallels between the movie and computer programming. I think a parallel exists between dreams-within-dreams and programs-within-programs.

As you’re watching the movie, especially in the later sequences, you are watching it on four and even five levels. The events that occur on each level have consequences for those levels below it. When the dreaming is over, the lower levels get to return the favor, having consequences on the levels above them by how they changed the dreamers themselves. To follow the movie you need to have an understanding of what is going on at each level.

Similarly in metaprogramming, you need an awareness of what level you are operating on at any given time as well as some knowledge of when and how to mix in elements from other levels. As an example, let’s take one of the simplest possible programs and see what happens when we add more levels through metaprogramming. The example is in SQL PL:

BEGIN CALL DBMS_OUTPUT.PUT_LINE('Hello world!'); END

The output of the program is as you would expect:

Hello world!

Rewriting it into a metaprogram, we can add a second level:

BEGIN
  EXECUTE IMMEDIATE 'BEGIN CALL DBMS_OUTPUT.PUT_LINE(''Hello world!''); END';
END

Notice that we needed to turn the single-quotes that surround the message into double single-quotes once we embed our original program into a string. Now let’s have the outer world of the meta-program affect the inner world of the program by having it provide part of the message:

BEGIN
  DECLARE MSG VARCHAR(128) DEFAULT 'world!';
  EXECUTE IMMEDIATE 'BEGIN CALL DBMS_OUTPUT.PUT_LINE(''Hello ' || MSG || '''); END';
END

What if we add a third level, or dream-within-a-dream? I have used such techniques to implement a generalized version of the ANY_OF function I described in my post on default parameters, so such meta-metaprogramming techniques can have their use. Let’s see what it does to our Hello World program:

BEGIN
  EXECUTE IMMEDIATE 'BEGIN DECLARE MSG VARCHAR(128) DEFAULT ''world!''; EXECUTE IMMEDIATE ''BEGIN CALL DBMS_OUTPUT.PUT_LINE(''''Hello '' || MSG || ''''''); END''; END';
END

The conversion was actually pretty straightforward. Just as we double every single-quote when going from program to metaprogram, we double every single-quote again to go from metaprogram to meta-metaprogram, so one single-quote in the original program becomes four single-quotes in the meta-metaprogram. Let’s make the above program a little more interesting by injecting something from the outer world down deep into the innermost world:

BEGIN
  DECLARE OUTERMSG VARCHAR(128) DEFAULT 'world!';
  EXECUTE IMMEDIATE 'BEGIN DECLARE MSG VARCHAR(128) DEFAULT '''
    || OUTERMSG || 
    '''; EXECUTE IMMEDIATE ''BEGIN CALL DBMS_OUTPUT.PUT_LINE(''''Hello '' || MSG || ''''''); END''; END';
END

The next step would be to see how something in the deepest inner-world can make it back up into the outermost world, but that’s enough for today. In my next post, I will return to discussing db2top with a new video.

In the past three posts, we have written metaprograms to create functions for comparing objects of built-in types, user-defined distinct types, ROW types, and ordinary ARRAY types. In today’s post, we will write a single metaprogram that can create comparison functions for all four.

The only obstacle to writing a generic metaprogram for built-in, UDDT, ROW, and ordinary ARRAY types is that we do not know how to determine which of the four it is from the name that is passed to our metaprogram. It turns out that there is a column named METATYPE in the SYSCAT.DATATYPES catalog view that we can use. Let’s look at a short example:

CREATE TYPE SIMPLETYPE AS INTEGER WITH COMPARISONS@
CREATE OR REPLACE TYPE ROWTYPE AS ROW (ONE INTEGER, TWO INTEGER)@
CREATE OR REPLACE TYPE ARRAYTYPE AS INTEGER ARRAY[]@

SELECT VARCHAR(TYPENAME, 10) TYPENAME, METATYPE 
FROM SYSCAT.DATATYPES 
WHERE TYPENAME IN ('SIMPLETYPE', 'ROWTYPE', 'ARRAYTYPE', 'INTEGER')@

This produces a result like the following:

TYPENAME   METATYPE
---------- --------
ROWTYPE    F       
ARRAYTYPE  A       
SIMPLETYPE T       
INTEGER    S

  4 record(s) selected.

We can use this to write a generic function that inspects this SYSCAT.DATATYPES view before deciding which kind of comparison function to create (implementations have been elided for readability, but are simply the code from the previous three articles in this series):

CREATE OR REPLACE PROCEDURE CREATE_EQ_FUNCTION(SCHEMA_NAME VARCHAR(128),
                                                           MODULE_NAME VARCHAR(128),
                                                           TYPE_NAME VARCHAR(128))
BEGIN
  DECLARE META_TYPE CHAR(1);
  ...
  SELECT METATYPE INTO META_TYPE
  FROM SYSCAT.DATATYPES
  WHERE TYPESCHEMA = SCHEMA_NAME
    AND (TYPEMODULENAME IS NULL AND MODULE_NAME IS NULL OR TYPEMODULENAME = MODULE_NAME)
    AND TYPENAME = TYPE_NAME;
  IF (META_TYPE = 'F') THEN
    -- Create ROW type EQ function
  ELSEIF (META_TYPE = 'T' OR META_TYPE = 'S') THEN
    -- Create UDDT/built-in type EQ function
  ELSEIF (META_TYPE = 'A') THEN
    -- Create ARRAY type EQ function
  ELSE
    SIGNAL SQLSTATE VALUE '75000' SET MESSAGE_TEXT = 'Unrecognized metatype. Cannot create comparison function.';
  END IF;
END@

Now, we have a generic metaprogram that can create EQ functions for all four metatypes. The following example demonstrates its use on the built-in, row, and array metatypes:

SET SERVEROUTPUT ON@
CREATE OR REPLACE TYPE TESTSCH.ROWTYPE AS ROW (ONE INTEGER, TWO INTEGER)@
CREATE OR REPLACE TYPE TESTSCH.ARRAYTYPE AS INTEGER ARRAY[]@
CALL CREATE_EQ_FUNCTION('SYSIBM', NULL, 'INTEGER')@
CALL CREATE_EQ_FUNCTION('TESTSCH', NULL, 'ROWTYPE')@
CALL CREATE_EQ_FUNCTION('TESTSCH', NULL, 'ARRAYTYPE')@
BEGIN
  DECLARE SIMPLE1 INTEGER;
  DECLARE SIMPLE2 INTEGER;
  DECLARE ROW1 TESTSCH.ROWTYPE;
  DECLARE ROW2 TESTSCH.ROWTYPE;
  DECLARE ARRAY1 TESTSCH.ARRAYTYPE;
  DECLARE ARRAY2 TESTSCH.ARRAYTYPE;
  SET SIMPLE1 = 1;
  SET SIMPLE2 = 1;
  SET ROW1.ONE = 1;
  SET ROW1.TWO = 1;
  SET ROW2.ONE = 1;
  SET ROW2.TWO = 1;
  SET ARRAY1[1] = 1;
  SET ARRAY2[1] = 1;
  CALL DBMS_OUTPUT.PUT_LINE('EQ(SIMPLE1, SIMPLE2): ' || EQ(SIMPLE1, SIMPLE2));
  CALL DBMS_OUTPUT.PUT_LINE('EQ(ROW1, ROW2): ' || EQ(ROW1, ROW2));
  CALL DBMS_OUTPUT.PUT_LINE('EQ(ARRAY1, ARRAY2): ' || EQ(ARRAY1, ARRAY2));
  SET SIMPLE2 = 2;
  SET ROW2.TWO = 2;
  SET ARRAY2[1] = 2;
  CALL DBMS_OUTPUT.PUT_LINE('EQ(SIMPLE1, SIMPLE2): ' || EQ(SIMPLE1, SIMPLE2));
  CALL DBMS_OUTPUT.PUT_LINE('EQ(ROW1, ROW2): ' || EQ(ROW1, ROW2));
  CALL DBMS_OUTPUT.PUT_LINE('EQ(ARRAY1, ARRAY2): ' || EQ(ARRAY1, ARRAY2));
END@

This should result in output like the following:

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.


  Return Status = 0

CREATE OR REPLACE FUNCTION EQ(LEFT  SYSIBM.INTEGER, RIGHT SYSIBM.INTEGER)
SPECIFIC EQ_SIMPLE CONTAINS SQL RETURNS INTEGER 
BEGIN
   DECLARE RESULT INTEGER;
   IF (LEFT = RIGHT) THEN
     SET RESULT = 1;
   ELSE
     SET RESULT = 0;
   END IF;
   RETURN RESULT;
END


  Return Status = 0

CREATE OR REPLACE FUNCTION EQ(LEFT TESTSCH.ROWTYPE, RIGHT TESTSCH.ROWTYPE)
SPECIFIC EQ_ROW READS SQL DATA RETURNS INTEGER
BEGIN
  DECLARE RESULT INTEGER;
  IF (LEFT.ONE = RIGHT.ONE AND LEFT.TWO = RIGHT.TWO) THEN
    SET RESULT = 1;
  ELSE
    SET RESULT = 0;
  END IF;
  RETURN RESULT;
END


  Return Status = 0

CREATE OR REPLACE FUNCTION EQ(LEFT  TESTSCH.ARRAYTYPE, RIGHT TESTSCH.ARRAYTYPE)
RETURNS INTEGER SPECIFIC EQ_ARRAY READS SQL DATA
BEGIN
  DECLARE ID INTEGER DEFAULT 1;
  DECLARE RESULT INTEGER;
  IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN
    SET RESULT = 1;
    WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO
       SET RESULT = EQ(LEFT[ID], RIGHT[ID]);
       SET ID = ID + 1;
    END WHILE;
  ELSE
    SET RESULT = 0;
  END IF;
  RETURN RESULT;
END 

DB20000I  The SQL command completed successfully.

EQ(SIMPLE1, SIMPLE2): 1
EQ(ROW1, ROW2): 1
EQ(ARRAY1, ARRAY2): 1
EQ(SIMPLE1, SIMPLE2): 0
EQ(ROW1, ROW2): 0
EQ(ARRAY1, ARRAY2): 0

That ends this series on metaprogramming for now. For the next two weeks, I am on vacation. Hopefully, I will have interesting new content to share when I get back.

In the first post in this series, I introduced the concept of metaprogramming in SQL using dynamic SQL and applied the technique to write a procedure that could generate comparison functions that compare two objects of any basic type. In the second post, I expanded the procedure to produce comparison functions that compare two objects of any ROW type. In today’s post, I will introduce the ARRAY type from DB2 9.7 and show how we can go about comparing two arrays, including arrays of ROW type objects.

The ARRAY Type

As described in the DB2 Information Center, the array type in DB2 9.7 is a user-defined data type consisting of an ordered set of elements of a single data type. There are various restrictions on what that single data type can be. Built-in types and ROW types are acceptable, but user-defined distinct types are not. There are two kinds of array types: ordinary arrays and associative arrays. Ordinary arrays have an upper bound on the number of elements and are indexed by ordinal position. Associative arrays have no such upper bound and they let you choose how they are indexed by providing a VARCHAR or INTEGER as the index.

Comparing Arrays

The ‘=’ operator cannot be used to compare two objects of ARRAY type. However, as we did for objects of ROW type in the last post, we will create a metaprogram that produces EQ functions for any ordinary ARRAY type we define. First, let’s define what it means for an ordinary array A to be considered equal to an ordinary array B:

1. The length of array A must be equal to the length of array B.
2. For each element in array A, the corresponding element by ordinal position in array B must be equal to that of array A.

Once again, let’s start with a program that compares two ordinary arrays of a specific type and we will later transform it into a metaprogram that can write comparison functions for any pair of ordinary arrays. First we need to compare the lengths (cardinalities) of the arrays:

CREATE TYPE SIMPLEARRAY AS INTEGER ARRAY[100]@

CREATE OR REPLACE FUNCTION EQ(LEFT SIMPLEARRAY, RIGHT SIMPLEARRAY)
RETURNS INTEGER
READS SQL DATA
BEGIN
  DECLARE ID INTEGER DEFAULT 1;
  DECLARE RESULT INTEGER;
  IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN
    SET RESULT = 1;

Then, if the cardinalities match, we iterate over the elements of the arrays, comparing each one:

    WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO
      IF (LEFT[ID] = RIGHT[ID]) THEN
        SET RESULT = 1;
      ELSE
        SET RESULT = 0;
      END IF;
      SET ID = ID + 1;
    END WHILE;
  ELSE
    SET RESULT = 0;
  END IF;
  RETURN RESULT;
END@

Before we transform this into a metaprogram, we will do our comparison using EQ instead of the ‘=’ operator. This way, it will work not just for elements that are of built-in type such as INTEGER elements but for ROW elements as well:

CREATE OR REPLACE FUNCTION EQ(LEFT SIMPLEARRAY, RIGHT SIMPLEARRAY)
RETURNS INTEGER
READS SQL DATA
BEGIN
  DECLARE ID INTEGER DEFAULT 1;
  DECLARE RESULT INTEGER;
  IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN
    SET RESULT = 1;
    WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO
      SET RESULT = EQ(LEFT[ID], RIGHT[ID]);
      SET ID = ID + 1;
    END WHILE;
  ELSE
    SET RESULT = 0;
  END IF;
  RETURN RESULT;
END@

Now, let’s make this a metaprogram. First, it will only work for SIMPLEARRAY objects:

CREATE OR REPLACE PROCEDURE CREATE_SIMPLEARRAY_EQ_FUNCTION()
MODIFIES SQL DATA
BEGIN
  DECLARE SQL VARCHAR(1024);
  SET SQL = 'CREATE OR REPLACE FUNCTION EQ(LEFT SIMPLEARRAY, RIGHT SIMPLEARRAY) ';
  SET SQL = SQL || 'RETURNS INTEGER ';
  SET SQL = SQL || 'READS SQL DATA ';
  SET SQL = SQL || 'BEGIN ';
  SET SQL = SQL || '  DECLARE ID INTEGER DEFAULT 1; ';
  SET SQL = SQL || '  DECLARE RESULT INTEGER; ';
  SET SQL = SQL || '  IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN ';
  SET SQL = SQL || '    SET RESULT = 1; ';
  SET SQL = SQL || '    WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO ';
  SET SQL = SQL || '      SET RESULT = EQ(LEFT[ID], RIGHT[ID]); ';
  SET SQL = SQL || '      SET ID = ID + 1; ';
  SET SQL = SQL || '    END WHILE; ';
  SET SQL = SQL || '  ELSE ';
  SET SQL = SQL || '    SET RESULT = 0; ';
  SET SQL = SQL || '  END IF; ';
  SET SQL = SQL || '  RETURN RESULT; ';
  SET SQL = SQL || 'END ';
END@

The final step is to generalize it to work for all ARRAY types:

CREATE OR REPLACE PROCEDURE CREATE_EQ_FUNCTION(DATATYPE VARCHAR(128))
MODIFIES SQL DATA
BEGIN
  DECLARE SQL VARCHAR(1024);
  SET SQL =        'CREATE OR REPLACE FUNCTION EQ(LEFT  ' || DATATYPE || ', ';
  SET SQL = SQL ||                               'RIGHT ' || DATATYPE || ') ';
  SET SQL = SQL || 'RETURNS INTEGER ';
  SET SQL = SQL || 'READS SQL DATA ';
  SET SQL = SQL || 'BEGIN ';
  SET SQL = SQL || '  DECLARE ID INTEGER DEFAULT 1; ';
  SET SQL = SQL || '  DECLARE RESULT INTEGER; ';
  SET SQL = SQL || '  IF (CARDINALITY(LEFT) = CARDINALITY(RIGHT)) THEN ';
  SET SQL = SQL || '    SET RESULT = 1; ';
  SET SQL = SQL || '    WHILE (ID <= CARDINALITY(LEFT) AND RESULT = 1) DO ';
  SET SQL = SQL || '      SET RESULT = EQ(LEFT[ID], RIGHT[ID]); ';
  SET SQL = SQL || '      SET ID = ID + 1; ';
  SET SQL = SQL || '    END WHILE; ';
  SET SQL = SQL || '  ELSE ';
  SET SQL = SQL || '    SET RESULT = 0; ';
  SET SQL = SQL || '  END IF; ';
  SET SQL = SQL || '  RETURN RESULT; ';
  SET SQL = SQL || 'END ';
END@

Now, we can write a program to use the CREATE_EQ_FUNCTION procedure to create a new EQ function for any ARRAY type and use it to compare two arrays:

CALL CREATE_EQ_FUNCTION('SIMPLEARRAY')@
BEGIN
  DECLARE ARRAY1 SIMPLEARRAY;
  DECLARE ARRAY2 SIMPLEARRAY;
  SET ARRAY1[1] = 1;
  SET ARRAY2[1] = 1;
  CALL DBMS_OUTPUT.PUT_LINE('Arrays are equal: ' || EQ(ARRAY1, ARRAY2));
  SET ARRAY2[1] = 2;
  CALL DBMS_OUTPUT.PUT_LINE('Arrays are equal: ' || EQ(ARRAY1, ARRAY2));
END@

The program above has a result like the following:

  Return Status = 0

DB20000I  The SQL command completed successfully.

Arrays are equal: 1
Arrays are equal: 0

Now we have written a CREATE_EQ_FUNCTION procedure for three distinct kinds of data types: simple types (built-in and user-defined distinct types), ROW types, and ARRAY types. In Metaprogramming in SQL (Part 4), we will look at how to use the METATYPE field of the SYSCAT.DATATYPES catalog view to write a single CREATE_EQ_FUNCTION that works on all three types we have encountered so far.

In the last post on this subject, we used dynamic SQL to generalize an integer comparison function into a stored procedure that builds comparison functions of any basic type. This served as a simple example, but did not really offer much benefit over using the ‘=’ operator. In today’s post, I will extend the same technique to make functions that compare ROW objects just as easily, even though the ‘=’ operator cannot be used on ROW objects.

First of all, what does it mean to say that two rows are identical? Let’s define row A to be equal to row B when:

1. The types of row A and row B are the same
2. Each field in row A has the same value as the same field in row B

The first requirement is satisfied by the strong typing provided by SQL PL. The second will require us to look inside each row for its individual fields to make a proper comparison. In an earlier post, I showed you an example of a comparison procedure for two objects of a ROW type called TEST_T. It looked like this:

CREATE OR REPLACE TYPE TEST_T AS ROW(ONE INTEGER)@
 
CREATE OR REPLACE PROCEDURE EQ(IN LEFT TEST_T, IN RIGHT TEST_T, OUT RESULT INTEGER)
BEGIN
  IF (LEFT.ONE = RIGHT.ONE) THEN
    SET RESULT = 1;
  ELSE
    SET RESULT = 0;
  END IF;
END@

With metaprogramming, we can generalize this into a procedure that creates row comparison functions for any ROW type. But first we need a way of discovering what fields make up a row. Fortunately, there is a catalog view called SYSCAT.ROWFIELDS that tells the name and type of every field in a row. Say we query this catalog view for the TEST_T type we created above:

SELECT VARCHAR(FIELDNAME,10) FIELDNAME,
       VARCHAR(FIELDTYPESCHEMA,10) FIELDTYPESCHEMA,
       VARCHAR(FIELDTYPEMODULENAME,10) FIELDTYPEMODULENAME,
       VARCHAR(FIELDTYPENAME,10) FIELDTYPENAME
FROM SYSCAT.ROWFIELDS
WHERE TYPESCHEMA = (VALUES CURRENT SCHEMA)
  AND TYPEMODULENAME IS NULL
  AND TYPENAME = 'TEST_T'

We get a result like this:

FIELDNAME  FIELDTYPESCHEMA FIELDTYPEMODULENAME FIELDTYPENAME
---------- --------------- ------------------- -------------
ONE        SYSIBM          -                   INTEGER      

  1 record(s) selected.

We’ll put some of this information (specifically, the fieldname) to use in our metaprogram, but let’s follow the process we followed last time and start by turning the above comparison procedure into a metaprogram with hardcoded inputs:

CREATE OR REPLACE PROCEDURE CREATE_TYPE_T_EQ_FUNCTION()
MODIFIES SQL DATA
BEGIN
  DECLARE SQL VARCHAR(1024);
  SET SQL =        'CREATE OR REPLACE FUNCTION EQ(LEFT TEST_T, RIGHT TEST_T) ';
  SET SQL = SQL || 'CONTAINS SQL ';
  SET SQL = SQL || 'RETURNS INTEGER ';
  SET SQL = SQL || 'BEGIN ';
  SET SQL = SQL || 'DECLARE RESULT INTEGER; ';
  SET SQL = SQL || 'IF (LEFT.ONE = RIGHT.ONE) THEN ';
  SET SQL = SQL || '  SET RESULT = 1; ';
  SET SQL = SQL || 'ELSE ';
  SET SQL = SQL || '  SET RESULT = 0; ';
  SET SQL = SQL || 'END IF; ';
  SET SQL = SQL || 'RETURN RESULT; ';
  SET SQL = SQL || 'END';
  EXECUTE IMMEDIATE SQL;
END@

Next, using the SYSCAT.ROWFIELDS catalog view, we generalize the above function to take the ROW type as an argument. Since the type name needs to be qualified by the schema and the module name, we have an extra challenge to deal with. The module name is NULL when the type is not defined inside a module and NULLs cannot be compared using the ‘=’ operator. One solution is to write two queries of the SYSCAT.ROWFIELDS view, one that assumes the module name is non-NULL and a second that assumes the module name is NULL. We choose which query to use when we check whether the module name argument is NULL.

CREATE OR REPLACE PROCEDURE CREATE_EQ_FUNCTION(TYPE_SCHEMA VARCHAR(128),
                                               TYPE_MODULE_NAME VARCHAR(128),
                                               TYPE_NAME VARCHAR(128))
MODIFIES SQL DATA
BEGIN
  DECLARE SQL VARCHAR(1024);
  DECLARE FULLYQUALIFIEDTYPE VARCHAR(386);
  DECLARE AT_END INT DEFAULT 0;
  DECLARE FIELD_NAME VARCHAR(128);
  DECLARE ROWFIELDSCURSOR CURSOR FOR SELECT FIELDNAME
                                            FROM SYSCAT.ROWFIELDS 
                                            WHERE TYPESCHEMA = TYPE_SCHEMA
                                              AND TYPEMODULENAME IS NULL
                                              AND TYPENAME = TYPE_NAME;
  DECLARE ROWFIELDSMODCURSOR CURSOR FOR SELECT FIELDNAME
                                            FROM SYSCAT.ROWFIELDS 
                                            WHERE TYPESCHEMA = TYPE_SCHEMA
                                              AND TYPEMODULENAME = TYPE_MODULE_NAME
                                              AND TYPENAME = TYPE_NAME;
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET AT_END = 1;

  -- Types need to be qualified by their schema, and if they are in a module, by the module too.
  IF (TYPE_MODULE_NAME IS NULL) THEN
    SET FULLYQUALIFIEDTYPE = TYPE_SCHEMA || '.' || TYPE_NAME;
  ELSE
    SET FULLYQUALIFIEDTYPE = TYPE_SCHEMA || '.' || TYPE_MODULE_NAME || '.' || TYPE_NAME || ' ';
  END IF;
  SET SQL =        'CREATE OR REPLACE FUNCTION EQ(LEFT ' || FULLYQUALIFIEDTYPE;
  SET SQL = SQL || ', RIGHT ' || FULLYQUALIFIEDTYPE || ') ';
  SET SQL = SQL || 'READS SQL DATA ';
  SET SQL = SQL || 'RETURNS INTEGER ';
  SET SQL = SQL || 'BEGIN ';
  SET SQL = SQL || 'DECLARE RESULT INTEGER; ';
  SET SQL = SQL || 'IF (';

  IF (TYPE_MODULE_NAME IS NULL) THEN
    -- Iterate over the row fields to build the Boolean expression
    OPEN ROWFIELDSCURSOR;
    FETCH ROWFIELDSCURSOR INTO FIELD_NAME;
    WHILE AT_END = 0 DO
      SET SQL = SQL || 'LEFT.' || FIELD_NAME || ' = ' || 'RIGHT.' || FIELD_NAME;
      FETCH ROWFIELDSCURSOR INTO FIELD_NAME;
      IF (AT_END = 0) THEN
        SET SQL = SQL || ' AND ';
      END IF;
    END WHILE;
    CLOSE ROWFIELDSCURSOR;
  ELSE
    -- Iterate over the row fields to build the Boolean expression
    OPEN ROWFIELDSMODCURSOR;
    FETCH ROWFIELDSMODCURSOR INTO FIELD_NAME;
    WHILE AT_END = 0 DO
      SET SQL = SQL || 'LEFT.' || FIELD_NAME || ' = ' || 'RIGHT.' || FIELD_NAME;
      FETCH ROWFIELDSMODCURSOR INTO FIELD_NAME;
      IF (AT_END = 0) THEN
        SET SQL = SQL || ' AND ';
      END IF;
    END WHILE;
    CLOSE ROWFIELDSMODCURSOR;
  END IF;

  SET SQL = SQL || ') THEN ';
  SET SQL = SQL || '    SET RESULT = 1; ';
  SET SQL = SQL || '  ELSE ';
  SET SQL = SQL || '  SET RESULT = 0; ';
  SET SQL = SQL || 'END IF; ';
  SET SQL = SQL || 'RETURN RESULT; ';
  SET SQL = SQL || 'END';
  CALL DBMS_OUTPUT.PUT_LINE(SQL);
  EXECUTE IMMEDIATE SQL;
END@

Notice that I put in a CALL DBMS_OUTPUT.PUT_LINE to print the SQL before the EXECUTE IMMEDIATE. This makes it easy to debug whether the metaprogram is writing the function properly by inspecting the code it produces. Once the above function is created, we can call it on our TEST_T type and use the resulting EQ function to compare two rows of type TEST_T, as in this example program:

SET SERVEROUTPUT ON@
CREATE OR REPLACE TYPE TEST_T AS ROW(ONE INTEGER)@

CREATE OR REPLACE MODULE TEST_MOD@
ALTER MODULE TEST_MOD PUBLISH
TYPE TEST_T AS ROW(TWO INTEGER)@

CALL CREATE_EQ_FUNCTION('KMCDONAL', NULL, 'TEST_T')@
CALL CREATE_EQ_FUNCTION('KMCDONAL', 'TEST_MOD', 'TEST_T')@

BEGIN
  DECLARE T1 TEST_T;
  DECLARE T2 TEST_T;
  DECLARE T3 TEST_MOD.TEST_T;
  DECLARE T4 TEST_MOD.TEST_T;

  SET T1.ONE = 1;
  SET T2.ONE = 1;
  CALL DBMS_OUTPUT.PUT_LINE('EQ(t1, t2) = ' || EQ(t1, t2));
  SET T2.ONE = 2;
  CALL DBMS_OUTPUT.PUT_LINE('EQ(t1, t2) = ' || EQ(t1, t2));

  SET T3.TWO = 1;
  SET T4.TWO = 1;
  CALL DBMS_OUTPUT.PUT_LINE('EQ(t3, t4) = ' || EQ(t3, t4));
  SET T4.TWO = 2;
  CALL DBMS_OUTPUT.PUT_LINE('EQ(t3, t4) = ' || EQ(t3, t4));

END@

The above test produces the following output:

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.


  Return Status = 0

CREATE OR REPLACE FUNCTION EQ(LEFT KMCDONAL.TEST_T, RIGHT KMCDONAL.TEST_T) CONTAINS SQL RETURNS INTEGER BEGIN DECLARE RESULT INTEGER; IF (LEFT.ONE = RIGHT.ONE) THEN     SET RESULT = 1;   ELSE   SET RESULT = 0; END IF; RETURN RESULT; END


  Return Status = 0

CREATE OR REPLACE FUNCTION EQ(LEFT KMCDONAL.TEST_MOD.TEST_T , RIGHT KMCDONAL.TEST_MOD.TEST_T ) CONTAINS SQL RETURNS INTEGER BEGIN DECLARE RESULT INTEGER; IF (LEFT.TWO = RIGHT.TWO) THEN     SET RESULT = 1;   ELSE   SET RESULT = 0; END IF; RETURN RESULT; END

DB20000I  The SQL command completed successfully.

EQ(t1, t2) = 1
EQ(t1, t2) = 0
EQ(t3, t4) = 1
EQ(t3, t4) = 0

In Metaprogramming in SQL (Part 3), we will use the above EQ function to build a more powerful EQ function that can compare objects of the ARRAY type, which is one of the new types in DB2 9.7.

Metaprogramming can feel like magic. You call a function that you neither wrote nor imported from any library and, magically, it comes back with a result. Even more magical is how metaprogramming lets you do otherwise impossible things with your programming language. In “The Art of Metaprogramming”, Jonathan Bartlett’s developerWorks series, he lists three examples that illustrate the benefits of metaprogramming:

• You can use metaprogramming to pre-generate tables of data for use at run-time.
• In applications with large amounts of boilerplate code and limited ability to abstract this code cleanly into functions, you can use metaprogramming to create a mini-language to write the boilerplate for you at run-time, simplifying your own code.
• You can use metaprogramming to transform a programming language that promotes verbosity into one that celebrates terseness. In addition to making up for inadequate language design, this can also ease maintenance.

Examples abound. A famous one is Ruby On Rails’ ActiveRecord class inspired by Martin Fowler’s ActiveRecord design pattern. At least in early versions of Rails, you could simply create an empty class like the following:

class Employee < ActiveRecord::Base
end

and, assuming you had a division column and mgrlastnm column in the employees table in your database, you could write code like Employee.find_by_division_and_mgrlastnm "Sales", "Simpson" and it would just work. The Rails metaprogramming logic would detect that such a function did not exist and would generate the missing function at run-time.

So how does all this relate to SQL? In this post and others, I will answer that question and attempt to illustrate the benefits of metaprogramming in SQL PL.

The Wikipedia article on metaprogramming defines it as "the writing of computer programs that write or manipulate other programs (or themselves) as their data, or that do part of the work at compile time that would otherwise be done at runtime." The article goes on to show an example of generative programming (the first half of metaprogramming) in bash script and references several programming languages with powerful metaprogramming facilities like Ruby, Python and C++.

Fortunately, you don't need a fancy dynamic language like Ruby or Python or a template metaprogramming language like C++ to do metaprogramming. You just need a language that can treat data as code. Thanks to dynamic SQL, most valid SQL statements in the form of data (i.e. as a string) can be interpreted as code at run-time.

For an example, we turn again to the writing of a generic comparison function. We will start with an EQ function that just compares integers and, step by step, we will convert it into a metaprogram that can create an EQ function for any simple type (I realize that the '=' operator does a fine job of this already and I will get to a more useful application in a later post when we look at ROW types). Step 1 is to write the end result we want, that is, the definition of an integer comparison function named EQ:

CREATE OR REPLACE FUNCTION EQ(LEFT INTEGER, RIGHT INTEGER)
CONTAINS SQL
RETURNS INTEGER
BEGIN
  DECLARE RESULT INTEGER;
  IF (LEFT = RIGHT) THEN
    SET RESULT = 1;
  ELSE
    SET RESULT = 0;
  END IF;
  RETURN RESULT;
END@

Step 2 is to turn the whole function into a string and execute that string as dynamic SQL. We'll put the logic that executes the string into its own function called CREATE_INTEGER_EQ_FUNCTION. We will use the EXECUTE IMMEDIATE statement to execute the dynamic SQL string:

CREATE OR REPLACE PROCEDURE CREATE_INTEGER_EQ_FUNCTION()
MODIFIES SQL DATA
BEGIN
  DECLARE SQL VARCHAR(1024);
  SET SQL =        'CREATE OR REPLACE FUNCTION EQ(LEFT INTEGER, RIGHT INTEGER) ';
  SET SQL = SQL || 'CONTAINS SQL ';
  SET SQL = SQL || 'RETURNS INTEGER ';
  SET SQL = SQL || 'BEGIN ';
  SET SQL = SQL || '  DECLARE RESULT INTEGER; ';
  SET SQL = SQL || '  IF (LEFT = RIGHT) THEN ';
  SET SQL = SQL || '    SET RESULT = 1; ';
  SET SQL = SQL || '  ELSE ';
  SET SQL = SQL || '  SET RESULT = 0; ';
  SET SQL = SQL || 'END IF; ';
  SET SQL = SQL || 'RETURN RESULT; ';
  SET SQL = SQL || 'END';
  EXECUTE IMMEDIATE SQL;
END@

Now, when we execute CREATE_INTEGER_EQ_FUNCTION(), it will create our EQ function:

call create_integer_eq_function
  Return Status = 0

values eq(1,2)
1          
-----------
          0
  1 record(s) selected.

Step 3 is to generalize. We will replace CREATE_INTEGER_EQ_FUNCTION() with CREATE_EQ_FUNCTION_FOR(datatype VARCHAR(128)) so now we can call it like this: call CREATE_EQ_FUNCTION_FOR('INTEGER').

CREATE OR REPLACE PROCEDURE CREATE_EQ_FUNCTION(DATATYPE VARCHAR(128))
MODIFIES SQL DATA
BEGIN
  DECLARE SQL VARCHAR(1024);
  SET SQL =        'CREATE OR REPLACE FUNCTION EQ(LEFT  ' || DATATYPE || ', ';
  SET SQL = SQL ||                               'RIGHT ' || DATATYPE || ') ';
  SET SQL = SQL || 'CONTAINS SQL ';
  SET SQL = SQL || 'RETURNS INTEGER ';
  SET SQL = SQL || 'BEGIN ';
  SET SQL = SQL || '  DECLARE RESULT INTEGER; ';
  SET SQL = SQL || '  IF (LEFT = RIGHT) THEN ';
  SET SQL = SQL || '    SET RESULT = 1; ';
  SET SQL = SQL || '  ELSE ';
  SET SQL = SQL || '  SET RESULT = 0; ';
  SET SQL = SQL || 'END IF; ';
  SET SQL = SQL || 'RETURN RESULT; ';
  SET SQL = SQL || 'END';
  EXECUTE IMMEDIATE SQL;
END@

Now, if we want to create an EQ function for CHAR(1) instead, we just pass a different argument.

call create_eq_function('char(1)') 

  Return Status = 0
values eq('a','a')
1          
-----------
          1
  1 record(s) selected.

values eq('a','b')
1          
-----------
          0
  1 record(s) selected.

This works well for simple types like integers, varchars, and user-defined distinct types with comparisons, but cannot deal with ROW types and ARRAY types, which cannot be compared with the '=' operator. In Metaprogramming in SQL (Part 2), Metaprogramming in SQL (Part 3), and Metaprogramming in SQL (Part 4), I'll show you the real power of metaprogramming when we tackle these more challenging data types.

With DB2 9.7′s introduction of modules and the ability to create an alias on a module, you now have the ability to program to a function or procedure’s interface rather than its implementation. Why would you want to do this? In today’s post, I will go over reasons to program to an interface and show you how to do it using modules and aliases.

Object-oriented programming languages realize their power when programmers program to an interface rather than an implementation, as it makes programs written in them more flexible. Such languages allow you to change or generalize your designs without significantly rewriting your code. However, the technique of programming to an interface is not limited to object-oriented languages. Anyone who has ever created a view on a table has some experience with the same concept applied to data.

Programming to an interface makes testing your code easier too, especially unit testing. In unit testing, you test components in isolation. If a component-under-test has explicit dependencies on other components, it can be difficult to test in isolation. If, however, these dependencies are on the interfaces of other components rather than on their implementations, you can replace the implementations with fakes or stubs to achieve isolation.

More Testable Designs

Say you have a program that reads data from a sensor and performs some calculation on it. You want to unit test that the calculation produces correct results for known values and boundary conditions. You also want this test to be repeatable so that you can rerun the test whenever you make changes to the calculation and know that you haven’t introduced a regression. The problem is that the sensor gets its data from an environment you can’t control. The system-under-test, that is, your calculation function, is coupled to sensor output that varies from one minute to the next, making it difficult or impossible to write an isolated unit test.

The solution is to program not to the sensor implementation but to its interface. Let’s say the software that handles reading the sensor takes the form of a user-defined function in SQL PL. Let’s pretend we have a sensor that gives us random data from the environment and let’s represent it with a function that returns a random number between 0 and 1000. Let’s say that the system-under-test is a function that reads the sensor six times and computes the average from those six readings.

SET SERVEROUTPUT ON@

CREATE OR REPLACE FUNCTION read_sensor() SPECIFIC read_sensor 
RETURNS INTEGER 
BEGIN
  DECLARE seed INTEGER;
  SET seed = (INTEGER(SECOND(CURRENT TIMESTAMP)) * 1000000) + MICROSECOND(CURRENT TIMESTAMP);
  RETURN 1000 * RAND(MOD(seed,2147483647));
END@

CREATE OR REPLACE FUNCTION avg_sensor_readings()
RETURNS DOUBLE
BEGIN
  DECLARE sum DOUBLE DEFAULT 0;
  DECLARE count INTEGER DEFAULT 0;
  WHILE count < 6 DO
    set sum = sum + read_sensor(); 
    set count = count + 1;
  END WHILE;
  RETURN sum / 6.0;
END@

CALL DBMS_OUTPUT.PUT_LINE('avg of 6 sensor readings = ' || DECIMAL(avg_sensor_readings(),9,2))@

If we run the above program we get something like the following:

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

  Return Status = 0

avg of 6 sensor readings = 523.00

If we run it again, we get a different average. This is unacceptable for testing our avg_sensor_readings function. We would like to replace the read_sensor function with a stub that consistently returns the numbers we need to test our averaging function. However, because we have coupled the avg_sensor_readings function to the sensor’s implementation, we cannot simply replace the read_sensor with a stub when running our tests and still be able to use the real read_sensor function when running in production.

How can we decouple these two components? If the read_sensor function were instead a table, we could create an alias on the table and modify the avg_sensor_readings function to query the alias instead of the table, but there is no such thing as an alias on a function. Fortunately, DB2 9.7 provides the solution with the introduction of modules, which can be given an alias. First, let’s move our read_sensor function into a module called sensors.

CREATE OR REPLACE MODULE sensors@

ALTER MODULE sensors PUBLISH
FUNCTION read_sensor() SPECIFIC read_sensor 
RETURNS INTEGER 
BEGIN
  DECLARE seed INTEGER;
  SET seed = (INTEGER(SECOND(CURRENT TIMESTAMP)) * 1000000) + MICROSECOND(CURRENT TIMESTAMP);
  RETURN 1000 * RAND(MOD(seed,2147483647));
END@

Now, we just need to create an alias for our sensors module and modify avg_sensor_readings to program to the interface (the alias):

CREATE OR REPLACE ALIAS sensors_interface FOR MODULE sensors@

CREATE OR REPLACE FUNCTION avg_sensor_readings()
RETURNS DOUBLE
BEGIN
  DECLARE sum DOUBLE DEFAULT 0;
  DECLARE count INTEGER DEFAULT 0;
  WHILE count < 6 DO
    set sum = sum + sensors_interface.read_sensor(); 
    set count = count + 1;
  END WHILE;
  RETURN sum / 6.0;
END@

Now, if we create a stub module, we can have our avg_sensor_readings function use the stub when it is being unit-tested and use the real function when in production. Here is the stub with the alias changed to point to the stub function instead of the original function:

CREATE OR REPLACE MODULE sensor_stubs@

ALTER MODULE sensor_stubs PUBLISH 
FUNCTION read_sensor() SPECIFIC read_sensor
RETURNS INTEGER 
RETURN 5@

CREATE OR REPLACE ALIAS sensors_interface FOR MODULE sensor_stubs@

CALL DBMS_OUTPUT.PUT_LINE('avg of 6 sensor readings (Expected: 5.00, Actual: ' || DECIMAL(avg_sensor_readings(),9,2) || ')')@

This has the following output:

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.


  Return Status = 0

avg of 6 sensor readings (Expected: 5.00, Actual: 5.00)

Now we always get the same average whenever we run the test. We can rely on this test to detect regressions in our avg_sensor_readings function. We can switch back and forth between the stub and real function without ever having to modify the avg_sensor_readings function.

IBM released DB2 for LUW 9.7.2 recently and, along with numerous quality improvements, there were a number of interesting new features. In today’s post, I will talk about the “default parameters in UDFs” feature and how that helped me shrink the amount of code I needed to write for one of my UDFs.

First, I want to explain why I was writing this particular UDF. I needed a function to take the comparison functions that I described previously in my post on DB2 Stored Procedures and Defaults and apply more than one of them at a time. I wanted to create the equivalent of a boolean OR operation using UDFs.

Say you have a function named EQ and it takes two integer arguments and compares the first argument to the second and returns a 1 if they match and a 0 if they don’t. Further, say you want to perform two such comparisons, that is, make two calls to EQ, and return a 1 if either comparison succeeded and return a 0 if both comparisons failed. You could call this function ANY_OF and use it like this:

ANY_OF(EQ(1, x), EQ(2, x))

I admit that this seems like a useless function when it is just as easy to write:

EQ(1, x) OR EQ(2, x)

or even:

x = 1 OR x = 2

I do, however, have a few reasons for turning boolean operators and comparison operators into UDFs. First of all, you can make these EQ functions take any data type as their arguments, including ROW types, and second, you can make it so that instead of the EQ functions comparing the first argument to the second, they take only the first argument and produce a special object that, at a later time, you can use to perform comparisons, allowing you to do something like this:

SET special_object = ANY_OF(EQ(1), EQ(2));
CALL PERFORM_COMPARISON(special_object, x);

As odd as the above code may seem, having the ability to declare arbitrary comparison operations and use them at a later time is useful in a project I am working on, mostly because it offers type safety advantages over manipulating strings directly to do the same thing. However, we’re going to keep things simple for now and, without loss of generality, have the EQ functions take two integer arguments and return a 1 or a 0.

I would like the ANY_OF function to take between 2 and 5 arguments, so that I can write:

ANY_OF(EQ(1, x), EQ(2, x), EQ(3, x), EQ(4, x), EQ(5, x))

just as easily as I can write:

ANY_OF(EQ(1,x), EQ(2,x))

The problem is that we now need to write four different versions of the ANY_OF function, each taking a different number of arguments:

CREATE OR REPLACE FUNCTION ANY_OF(ARG1 INTEGER,
                                  ARG2 INTEGER)
RETURNS INTEGER
BEGIN
  IF (ARG1 = 1 OR ARG2 = 1) THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END@
...
CREATE OR REPLACE FUNCTION ANY_OF(ARG1 INTEGER,
                                  ARG2 INTEGER,
                                  ARG3 INTEGER,
                                  ARG4 INTEGER,
                                  ARG5 INTEGER)
RETURNS INTEGER
BEGIN
  IF (ARG1 = 1 OR ARG2 = 1 OR ARG3 = 1 OR ARG4 = 1 OR ARG5 = 1) THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END@

With the introduction of default arguments in DB2 9.7.2, we need only write one version of ANY_OF:

CREATE OR REPLACE FUNCTION ANY_OF(ARG1 INTEGER, 
                                  ARG2 INTEGER,
                                  ARG3 INTEGER DEFAULT NULL,
                                  ARG4 INTEGER DEFAULT NULL,
                                  ARG5 INTEGER DEFAULT NULL)
RETURNS INTEGER
BEGIN
  IF (ARG1 = 1 OR ARG2 = 1 OR 
       (ARG3 IS NOT NULL AND ARG3 = 1) OR
       (ARG4 IS NOT NULL AND ARG4 = 1) OR
       (ARG5 IS NOT NULL AND ARG5 = 1)) THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END@

We specify that all arguments after the second argument default to NULL. If an argument is NULL, we will ignore that argument. This saved me a lot of code. Hopefully, UDF default arguments can save you some code as well.

Have you ever needed to write a pair of scripts: one to create some tables, data types, or user-defined functions and a second to uninstall the same? How do you test that the uninstall script did its job and didn’t leave any database objects behind? Today, using a tool that comes with DB2, I’ll show you how to ensure that your cleanup scripts do their jobs.

I test my cleanup scripts using a tool called db2look. It is a powerful tool for extracting statistics and DDL from a database and it has several command line options. In my case, since I know that my program installs new data types, tables, variables, functions, and procedures, I use db2look with the -e option, which generates all the DDL needed to duplicate the database.

Say you have two scripts: install.sql and uninstall.sql. You want to verify that any database objects created by install.sql get cleaned up when you run uninstall.sql. You can verify this using db2look as follows (assuming you have the authority to create a fresh database on your development system):

1. Create a new database to provide a baseline: create database test
2. Run db2look on your new database and store the result: db2look -d test -e -o db2look.baseline
3. Run your install script: db2 -tf install.sql
4. Run you uninstall script: db2 -tf uninstall.sql
5. Rerun db2look and store the result: db2look -d test -e -o db2look.postuninstall
6. Run your favorite diff tool on the two db2look result files you generated: diff db2look.postuninstall db2look.baseline

If your uninstall script did its job perfectly, the only difference you should see is a one-line mismatch in which a timestamp of when db2look was run is printed. Otherwise, the diff output tells you exactly what types, procedures, or tables your uninstall script failed to clean up. This kind of test can easily be made a part of any automated testing process you have for testing your scripts. In fact, if you do this as part of your automated testing and create your baseline before your tests are run, it would also detect if any of your automated tests failed to clean up any database objects they created as well.

If the timestamp line makes automated comparison difficult, you can always filter it out beforehand from each file using something like sed:

sed 's/-- Timestamp.*//' db2look.baseline > db2look.baseline.notimestamp
sed 's/-- Timestamp.*//' db2look.postuninstall > db2look.postuninstall.notimestamp
diff db2look.postuninstall.notimestamp db2look.baseline.notimestamp

If you remember the previous post, I showed how moving some logic from an anonymous block into a scalar function caused the program to start reporting an SQL0577N, saying that the underlying procedure was trying to modify SQL data even though it was doing no such thing. The clue to the cause of the problem is in the title and the first paragraph of the previous post: defaults.

The default behavior for a stored procedure when you don’t specify whether it MODIFIES SQL DATA or READS SQL DATA is to act as if you had specified MODIFIES SQL DATA. The weird part is the confusing message: SQL0577N User defined routine “KMCDONAL.EQ” (specific name “SQL100516224914100″) attempted to modify data but was not defined as MODIFIES SQL DATA, especially since the problem is not that it modifies without saying “MODIFIES”, but that its default says “MODIFIES” and a procedure declared as such cannot be used in the context in which I’m attempting to use it (i.e. inside a scalar function).

The solution is to explicitly state CONTAINS SQL as I do on line 5 below (specifying READS SQL DATA will also work, but I don’t read SQL in the EQ procedure either, so I may as well use CONTAINS SQL):

SET SERVEROUTPUT ON@
CREATE OR REPLACE TYPE TEST_T AS ROW(ONE INTEGER)@

CREATE OR REPLACE PROCEDURE EQ(IN LEFT TEST_T, IN RIGHT TEST_T, OUT RESULT INTEGER)
CONTAINS SQL
BEGIN
  IF (LEFT.ONE = RIGHT.ONE) THEN
    SET RESULT = 1;
  ELSE
    SET RESULT = 0;
  END IF;
END@

CREATE OR REPLACE FUNCTION EQ_FN(LEFT TEST_T, RIGHT TEST_T)
RETURNS INTEGER
BEGIN
  DECLARE RESULT INTEGER;
  PREPARE S1 FROM 'CALL EQ(?, ?, ?)';
  EXECUTE S1 INTO RESULT USING LEFT, RIGHT; 
  RETURN RESULT;
END@

BEGIN
  DECLARE T1 TEST_T;
  DECLARE T2 TEST_T;
  DECLARE RESULT INTEGER;
  SET T1.ONE = 1;
  SET T2.ONE = 1;
  SET RESULT = EQ_FN(T1, T2);
  CALL DBMS_OUTPUT.PUT_LINE('Result = ' || RESULT);
END@

This succeeds, resulting in the following output:

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

Result = 1

Have you ever spent a long time trying to figure out what was causing DB2 to give you a bizarre error message only to find that the root cause was a default you didn’t know was a default? I got just such a message the other day when writing an SQL stored procedure. In this post, I will show you the confusing message I encountered and give you the opportunity to see if you can figure out the solution.

I was writing a procedure to do a simple comparison between two variables of ROW type, since there are no built-in comparison functions for variables of ROW type in DB2 9.7. It looked something like the following:

SET SERVEROUTPUT ON@
CREATE OR REPLACE TYPE TEST_T AS ROW(ONE INTEGER)@

CREATE OR REPLACE PROCEDURE EQ(IN LEFT TEST_T, IN RIGHT TEST_T, OUT RESULT INTEGER)
BEGIN
  IF (LEFT.ONE = RIGHT.ONE) THEN
    SET RESULT = 1;
  ELSE
    SET RESULT = 0;
  END IF;
END@

BEGIN
  DECLARE T1 TEST_T;
  DECLARE T2 TEST_T;
  DECLARE RESULT INTEGER;
  SET T1.ONE = 1;
  SET T2.ONE = 1;
  PREPARE S1 FROM 'CALL EQ(?, ?, ?)';
  EXECUTE S1 INTO RESULT USING T1, T2;
  CALL DBMS_OUTPUT.PUT_LINE('Result = ' || RESULT);
END@

The above code worked just fine. As expected, the result looked like this:

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

Result = 1

But then I moved the prepare and execute code in the anonymous block above into a scalar function named EQ_FN:

CREATE OR REPLACE FUNCTION EQ_FN(LEFT TEST_T, RIGHT TEST_T)
RETURNS INTEGER
BEGIN
  DECLARE RESULT INTEGER;
  PREPARE S1 FROM 'CALL EQ(?, ?, ?)';
  EXECUTE S1 INTO RESULT USING LEFT, RIGHT; 
  RETURN RESULT;
END@

BEGIN
  DECLARE T1 TEST_T;
  DECLARE T2 TEST_T;
  DECLARE RESULT INTEGER;
  SET T1.ONE = 1;
  SET T2.ONE = 1;
  SET RESULT = EQ_FN(T1, T2);
  CALL DBMS_OUTPUT.PUT_LINE('Result = ' || RESULT);
END@

This did not work. The error message was:

DB20000I  The SET SERVEROUTPUT command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB20000I  The SQL command completed successfully.

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0577N  User defined routine "KMCDONAL.EQ" (specific name 
"SQL100516224914100") attempted to modify data but was not defined as MODIFIES 
SQL DATA.

My EQ procedure does not actually modify SQL data, so why does it say it attempted to? Do you know what I did wrong? I’ll give the answer in DB2 Stored Procedures and Defaults (Conclusion). Coverage of db2top continues next week.