Today’s video is a comparison between the output of the db2mtrk command and the db2top Memory screen. It covers current allocations, high watermarks, and maximum allocations for the instance, database, and application groups of memory pools as well as the repeat mode of db2mtrk.

Today’s video is a demonstration of buffer pool hit ratio in the Bufferpools screen of db2top. To produce a hit ratio of 100%, we query a single row repeatedly to perform logical reads with no corresponding physical reads. To reduce the hit ratio, we query more data than can fit in the buffer pool, resulting in physical reads.

Today’s video is a demonstration of the regular expressions (regex) feature of db2top. In the video, I apply regular expressions to the Dynamic SQL screen to filter what SQL statements are shown. I demonstrate setting and clearing a regex, simple regular expressions, and POSIX extended regular expression metacharacters:

Today’s video is a demonstration of the Locks screen of db2top. In the video, I create a lock chain and launch db2top to examine the Application Status, Object Name, Locked By and Lock Count columns for three applications involved in the chain. I then use the Lock Chain screen to visualize the chain:

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.

For the second video in this video series, we have a demonstration of the db2top Watch feature. It demonstrates capturing a single application’s SQL, capturing complete database structures to a set of files, and capturing all the dynamic SQL in the cache: