SQL Programming Nugget: User-Defined Functions and Data Types

Earlier this week, I posted an entry to this blog that described a "lesson learned" experience in SQL programming (having to do with the use of a WHILE loop in a SQL stored procedure to process rows in a result set generated and returned by a nested stored procedure). Today, I'll relate another such experience to you. This time, it has to do with user-defined functions (UDFs) and associated data types. As with my previous "SQL programming nugget" post (see the link above), this one describes a situation that I encountered in a DB2 for Linux/UNIX/Windows (LUW) environment, but the same would have occurred in a DB2 for z/OS system.

OK, here's the deal: I was working on the development of a UDF that would take as an input argument a fixed-length character string of 3 bytes. Accordingly, the first part of the CREATE FUNCTION statement looked like this:

CREATE FUNCTION MYFUNC(ARG CHAR(3))

I filled in the rest (RETURNS information, variable declarations, function logic, etc.), submitted the statement, and got a message indicating successful creation of the UDF. Then, to test the UDF, I invoked it via the Command Editor, passing a 3-byte character string constant as the input argument, as follows:

SELECT MYFUNC('A01') ...
FROM ...

Execution of this SELECT statement resulted in an error message indicating that MYFUNC could not be found (SQLSTATE 42884). Huh? Not found? What do you mean, "not found?" I just created it! I checked the SYSCAT.ROUTINES catalog view, and there was my UDF. If I could find it, why couldn't DB2?

After I'd racked my brain for a while, it finally occurred to me that I might have introduced a data type mismatch when I passed to my UDF a character string constant. See, to be found by DB2 when invoked, a UDF has to be named properly in the SELECT statement (I did that), and it has to be passed the right number of input arguments (I did that), and the data type of the input argument(s) has to match, or be promotable to, the data type of the corresponding function parameter(s) (oops). I was thinking "matched data type" when I passed the character string constant 'A01', because that's a 3-byte character string and my UDF was defined as taking a CHAR(3) value as input. What I failed to consider is this: a character string constant is treated by DB2 as a varying-length value (i.e., a VARCHAR data type). That did not match the CHAR data type for the input parameter specified in the definition of my UDF. Furthermore, VARCHAR is not promotable to CHAR (to see what data types are promotable to others, refer to "Promotion of Data Types" in the DB2 for LUW SQL Reference, Volume 1, or the DB2 for z/OS SQL Reference).

To verify my understanding of what had gone wrong, I tried invoking my UDF in this manner:

SELECT MYFUNC(CHAR('A01')) ...
FROM ...

Sure enough, this worked, because the CHAR function returns a fixed-length representation of a character string. I also successfully invoked my UDF by passing to it a value from a table column that was created with a CHAR(3) data type specification (in other words, supposing that COL1 of table ABC was defined as CHAR(3), this invocation of my UDF worked: SELECT MYFUNC(COL1) FROM ABC). Finally, I created a UDF that was exactly like MYFUNC, except that the input parameter was defined as VARCHAR(3) instead of CHAR(3). When this modified UDF was invoked with a 3-character string constant passed as the input argument, it was found by DB2 and it worked just fine.

So, when you are creating a UDF, make sure that the data types of any input arguments will match - or can be promoted to - the data type as specified in the corresponding input parameter in the CREATE FUNCTION statement. Important exception to this rule: if you are using DB2 9.7 for LUW (the current release, which became generally available just last month), be aware of an enhancement with respect to function resolution (i.e., the process of finding the right function when a UDF is invoked). In a DB2 9.7 environment, if the regular function resolution process (the one I've described) fails to find an "exact match" for an invoked UDF, and if there is a function with the right name and the right number of parameters but with an inexact data type match in terms of input arguments and corresponding input parameters (as specified in the CREATE FUNCTION statement), the arguments will be converted to the data types of the parameters using the rules that apply to assignment of data values to columns. From a value-assignment perspective, VARCHAR is compatible with CHAR (refer to "Assignments and Comparisons" in the DB2 for LUW SQL Reference, Volume 1, or "Assignment and Comparison" in the DB2 for z/OS SQL Reference). That being the case, the "not found" situation that I encountered in a DB2 9.1 for LUW system when I passed a VARCHAR argument to a UDF defined with a CHAR input parameter (and the same thing would have happened in a DB2 9.5 system or a DB2 9 for z/OS environment) would NOT have occurred in a DB2 9.7 system. DB2 9.7 would have found and utilized my UDF.

Isn't that nice? One less thing to worry about. Keep those DB2 enhancements coming, IBM.

A SQL Programming Nugget: SQLSTATE and WHILE loops

Last week I taught an introduction to DB2 application development course at a large financial institution. That gave me the opportunity to share with the students some of my own experiences in the area of SQL programming. I'll share one of these "school of hard knocks" experiences with you via this blog entry, and I'll describe another in a subsequent post within the next few days. The situations I'll cover in these two entries (this one and the one to follow) occurred in a DB2 for Linux/UNIX/Windows (LUW) environment, but I believe that the lessons learned apply equally to DB2 for z/OS systems.

On, now, to SQL programming experience number one.

I was working on the development of a SQL stored procedure that would process a query result set generated by another stored procedure (a SQL stored procedure, by the way, is a stored procedure written using SQL procedure language, aka SQL PL - I've blogged a number of times on this topic, including an overview treatment posted last year). In other words, I was writing stored procedure A, which was to call stored procedure B. Stored procedure B, defined with DYNAMIC RESULT SETS 1 (indicating generation of a result set that would be consumed by a calling program), declared a cursor WITH RETURN TO CALLER (also necessary to make the query result set available to the calling program), opened that cursor, and returned control to stored procedure A. In stored procedure A I declared a result set locator variable, used an ASSOCIATE RESULT SET LOCATOR statement to get the location in memory of the result set generated by stored procedure B and assign it to the aforementioned result set locator variable, and allocated a cursor to enable the fetching of rows from the result set. So far, so good.

I decided to use a WHILE loop in stored procedure A to retrieve and act on each row in the result set generated by stored procedure B. Wanting to exit upon reaching the end of the result set (i.e., after FETCHing the last row), I started the loop with WHILE SQLSTATE = '00000' DO (having declared SQLSTATE - a return code value set by DB2 following execution of an SQL statement - in my SQL procedure and initialized it to '00000'), thinking that SQLSTATE would go to '02000' upon execution of the first FETCH statement following retrieval of the last row in the result set. Indeed, SQLSTATE is set to '02000' in that situation, but I messed up. How? By not making FETCH the last statement in the WHILE LOOP. See, I followed the FETCH in my loop with a SET statement that concatenated the value of a variable with a character string. That statement was always successful (indicated by SQLSTATE = '00000'), so, SQLSTATE-wise, here's what happened when the WHILE LOOP was executed following the retrieval of the last row in the result set:

FETCH cursor-name INTO variable1, variable2, ...; --SQLSTATE goes to '02000'
SET variable_xyz = variable_xyz CONCAT variable1; --SQLSTATE goes back to '00000'
END WHILE;

Thus, SQLSTATE was always '00000' when the end of my WHILE loop was reached, and the loop-evaluation condition of SQLSTATE = '00000' (the condition for initial entry into the loop and for loop reiteration) was always evaluated as "true". The result was an endless loop. Fortunately, I ran this SQL procedure on an instance of DB2 on my laptop, so I was the only person impacted. I got rid of the looping procedure, corrected the logic error, and went on from there, having learned a good lesson.

So, if you write a SQL stored procedure that processes a result set generated by another stored procedure, here are a couple of things to consider:

• A WHILE loop is fine for FETCHing through that result set, and you can even make SQLSTATE = '00000' the loop-evaluation condition - just MAKE SURE that FETCH is the last statement in the WHILE LOOP (before END WHILE, that is), if your intention in coding WHILE SQLSTATE = '00000' DO was to have DB2 exit the loop upon execution of the first FETCH following retrieval of the last row in the result set.
• Alternatively, consider indirectly using the SQLSTATE value to establish an exit-the-loop condition. This could be done by coding WHILE exit_ind = 0 DO (with exit_ind being a variable declared in your SQL procedure and initially set to 0), and then following the FETCH in the loop with IF SQLSTATE = '02000' THEN SET exit_ind = 1 (or IF SQLSTATE <> '00000' if you want to be more generic).

If, in your SQL stored procedure, you will be processing a result set generated by a SELECT statement in your procedure (as opposed to a result set generated by a cursor declared and opened in a stored procedure called by your stored procedure), consider using the FOR statement instead of a WHILE loop. A nice thing about FOR is that you will automatically exit the FOR loop after you've retrieved all the rows in the result set. Note that you could use FOR to process a result set generated by a stored procedure called by your stored procedure (let's call the lower-level program stored procedure B), if stored procedure B inserts the result set rows into a temporary table that is subsequently referenced in the SELECT statement in the FOR loop coded in your stored procedure.

I hope that your organization is using SQL stored procedures to access DB2 data - I'm a big fan of this approach. Check back in a few days for another SQL programming nugget - this one involving user-defined functions.

Migrating DB2 9 for z/OS Native SQL Procedures

Last week, a friend who works for a big DB2-using company in the American Midwest sent me a question concerning the migration of native SQL procedures from DB2 subsystem A to subsystem B (e.g., from a development to a test subsystem, or from test to production). This person's organization had been using DB2 for z/OS stored procedures for some time, and they had an automated process - utilizing IBM's Software Configuration and Library Manager product (SCLM) - that took care of moving a mainframe application program and associated items (load module, source code, control cards, etc.) from one DB2 environment to another. The company is in the process of going to DB2 for z/OS Version 9 from Version 8, and my friend and his colleagues are very much interested in taking advantage of the native SQL procedure functionality that's available in DB2 9 New Function Mode (I've done a good bit of blogging about DB2 9 native SQL procedures, starting with an overview-type entry posted last fall). There is plenty to get excited about here, including improved CPU efficiency versus external SQL procedures and excellent utilization of cost-effective zIIP engines on System z servers (when invoked through remote CALLs coming through the DB2 Distributed Data Facility), but people focused on the infrastructure of mainframe application systems may well wonder (as did my friend): given that a DB2 9 native SQL procedure is not dependent on external-to-DB2 items such as load and source modules (the executable is a DB2 package, and the source CREATE PROCEDURE statement is stored in the SYSROUTINES table in the DB2 catalog), how does one manage the migration of these stored procedures from (for example) test to production?

It turns out that new DB2 9 functionality, which gave rise to this question, also provides the solution in the form of two enhancements:

1. The new DEPLOY option of the BIND PACKAGE command. This new option lets you migrate a native SQL stored procedure (and a specific version of same, at that) from one DB2 for z/OS subsystem to another. DEPLOY essentially extends the functionality of a remote BIND command (that is, BIND to a remote subsystem), enabling you to add or replace a version of a native SQL procedure on the target remote subsystem from the current-location subsystem. DEPLOY does NOT change the logic portion of the native SQL procedure, which is stored in a special section of the package (the sections pertaining to SQL DML statements will of course be generated anew on the target remote system, so that you'll get appropriate access paths and such). Note that when you migrate a native SQL procedure using BIND PACKAGE with DEPLOY, you can change the qualifier used to resolve references to unqualified database objects named in SQL DML statements in the procedure.
2. New options for the ALTER PROCEDURE statement. You can issue ALTER PROCEDURE with the ACTIVATE VERSION option to make a particular version of a native SQL procedure the currently active version on a subsystem, so in the event that several versions of the native SQL procedure exist on the subsystem, the one identified via ALTER PROCEDURE ACTIVATE VERSION will be the one executed when a CALL to that procedure is executed (this default active version designation can be overridden at a thread level via the new CURRENT ROUTINE VERSION special register). ALTER PROCEDURE can also be used to drop a version of a native SQL procedure.

So, yes, when IBM's DB2 for z/OS development team was working on native SQL procedures for Version 9, they thought about mundane (though very important) matters such as inter-subsystem migration, as well as more-cool things like reduced execution instruction pathlength and nested compound SQL statements (the latter enabling, among other things, more sophisticated error-handling logic versus what you can achieve with an external SQL stored procedure). If you want to read more about native SQL procedure migration, check out the IBM "red book" titled "DB2 9 for z/OS Stored Procedures: Through the CALL and Beyond," the DB2 9 for z/OS Command Reference (for BIND PACKAGE with DEPLOY), and the DB2 9 for z/OS SQL Reference (for ALTER PROCEDURE with ACTIVATE VERSION).

Outer Join: Get the Predicates Right

A few days ago, I was working with a team of people from a large company, trying to improve the performance of some queries executing in a DB2 for z/OS-based data warehouse environment. One query in particular was running much longer than desired, and consuming a lot of CPU time, to boot. One of the team members noticed that the problem query, which involved several table-join operations, had a rather odd characteristic: no WHERE-clause predicates. All the predicates were in the ON clauses of the joins. In fact, there was even an inner join of table TAB_A (I won't use the real table names) with SYSIBM.SYSDUMMY1 (which of course contains nothing), with two ON predicates referencing columns in TAB_A, like this:

SELECT...
FROM TAB_A
INNER JOIN SYSIBM.SYSDUMMY1
ON TAB_A.COL1 = 12
AND TAB_A.COL2 = 'X'
...

One of the application developers (the queries we were analyzing are report-generating SELECT statements built and issued by application programs) removed this inner join to SYSDUMMY1 and changed the two ON-clause predicates to WHERE-clause predicates, and the query's elapsed and CPU times went way down.

We were left thinking that this Cartesian join (i.e., a join with no join columns specified) to SYSDUMMY1 might reflect someone's thinking that WHERE-clause predicates are not needed in table-join SELECT statements. In fact, the use of WHERE-clause predicates versus ON-clause predicates in table-join statements can have a very significant impact on query performance. We looked at another long-running query in the aforementioned data warehouse application, and this one also involved a join operation and also had no WHERE-clause predicates. Importantly, the join was a left outer join, and the ON clause included multiple predicates that referenced columns of the left-side table (the table from which we want rows for the result set, regardless of whether or not there are matching right-side table rows). A DBA took one of these left-side-table-referencing ON-clause predicates and made it a WHERE predicate, too. In other words, an ON-clause predicate like TAB_L.COL2 = 5 (with TAB_L being the left-side table in the left outer join operation) was added to the query in the form of a WHERE-clause predicate. The result? Response time for the query went from 10 minutes to less than 1 second.

Why did the query's performance improve so dramatically, when all the DBA did was make an ON-clause predicate a WHERE-clause predicate? Simple: for a left outer join, a WHERE-clause predicate that references a column of the left-side table will filter rows from that table. That same predicate, if coded in the ON-clause of the SELECT statement, will NOT filter left-side table rows. Instead, that predicate will just affect the matching of left-side table rows with right-side table rows. To illustrate this point, consider the predicate mentioned in the preceding paragraph:

TAB_L.COL2 = 5

Suppose this predicate is included in the query in the following way:

SELECT TAB_L.COL1, TAB_L.COL2, TAB_L.COL3, TAB_R.COL4
FROM TAB_L
LEFT OUTER JOIN TAB_R
ON
TAB_L.COL3 = TAB_R.COL3
AND TAB_L.COL2 = 5

With no WHERE-clause predicates in this query, all rows from TAB_L will qualify - none will be filtered out. What the TAB_L.COL2 = 5 predicate will do is affect row matching with TAB_R: if a row in TAB_R has a COL3 value that matches the value of COL3 in TAB_L, and if the value of COL2 in that row is 5, the TAB_R row (specifically, COL4 of that row, as specified in the query's SELECT-list) will be joined to the TAB_L row in the query result set; otherwise, DB2 will determine that the TAB_R row is not a match for any TAB_L rows (and the TAB_L rows without TAB_R matches will appear in the result set with the null value in the TAB_R.COL4 column).

Now, suppose that the same predicate is specified in a WHERE clause of the query, as follows:

SELECT TAB_L.COL1, TAB_L.COL2, TAB_L.COL3, TAB_R.COL4
FROM TAB_L
LEFT OUTER JOIN TAB_R
ON
TAB_L.COL3 = TAB_R.COL3
WHERE TAB_L.COL2 = 5

In this case, the predicate will be applied to TAB_L before the join operation, potentially filtering out a high percentage of TAB_L rows (as was the case for the query cited earlier that went from a 10-minute to a sub-second run time).

So, a person codes a predicate that references a column of the left-side table of a left outer join operation, and places that predicate in an ON clause of the query, versus a WHERE clause (and I'm not talking about a join predicate of the form TAB_L.COLn = TAB_R.COLn, which you expect to see in an ON clause). Is it possible that the query-writer actually wants the result described above, to wit: no filtering of left-side table rows, and a further condition as to what constitutes a right-side table match? Yes, that's possible, but there's a very good chance that this person mistakenly placed the predicate in an ON clause because he (or she) thought that this would have the same effect as coding the predicate in a WHERE clause. Mistakes of this type are fairly common because misunderstanding with respect to the effect of predicates in outer join queries is quite widespread. Patrick Bossman, a good friend who is a query optimization expert with IBM's DB2 for z/OS development organization, pointed out as much to me in a recent e-mail exchange. Patrick also sent me the links to two outstanding articles written by Terry Purcell, a leader on the IBM DB2 for z/OS optimizer team. These articles (actually, a part-one and part-two description of outer join predicates and their effects on query result sets) were written a few years ago, while Terry was with DB2 consultancy Yevich, Lawson, and Associates, but the content is still very much valid today (Patrick considers the articles to be "a must-read for folks writing outer joins"). Check 'em out.

Outer join is a powerful SQL capability that is widely used in DB2 environments. If you code outer join queries (or if you review such queries written by others), make sure that you use ON-clause and WHERE-clause predicates appropriately, so as to get the right result (job one) and the best performance (job two, right behind job one).