DB2 9 for z/OS: Converting from External to Native SQL Procedures
As some of you may know, I'm a big fan of the native SQL procedure functionality introduced with DB2 for z/OS Version 9 (I've written a number of blog entries on the subject, starting with one posted last year). Native SQL procedures offer a number of advantages versus external SQL procedures (formerly known simply as SQL procedures in pre-Version 9 DB2 environments), including (generally) better performance, zIIP engine eligibility when called from a remote client via DRDA, and simplified lifecycle processes (referring to development, deployment, and management). These advantages have plenty of folks looking to convert external SQL procedures to native SQL procedures, and that's fine, but some of these people are under the impression that the conversion process involves nothing more than dropping an external SQL procedure and re-issuing that routine's CREATE PROCEDURE statement, minus the EXTERNAL NAME and FENCED options (if either had been specified in creating the external SQL procedure). This may in fact do the trick for a very simple SQL procedure, but in many cases the external-to-native conversion will be a more involved process. In this post I'll provide some information as to why this is so, along with a link to a well-written "technote" on IBM's Web site that contains further details on the topic.
First, a little more on this drop-and-recreate-without-EXTERNAL-NAME-or-FENCED business. It is true that, in a DB2 9 New Function Mode system, a SQL procedure (i.e., a stored procedure for which the routine source is contained within the CREATE PROCEDURE statement) will be external if it is created with the EXTERNAL NAME and/or FENCED options specified, and native if created with neither EXTERNAL NAME nor FENCED specified; however, it is not necessarily the case that an external SQL procedure re-created sans EXTERNAL NAME and FENCED will behave as you want it to when executed as a native SQL procedure. Why is this so? Well, some of the reasons are kind of obvious when you think about it. Others are less so. On the obvious side, think about options that you'd specify for an external SQL procedure (which ends up becoming a C language program with embedded SQL) at precompile time (e.g., VERSION, DATE, DEC) and at bind time (e.g., QUALIFIER, CURRENTDATA, ISOLATION). For a native SQL procedure, there's nothing to precompile (as there is no associated external-to-DB2 program), and the package is generated as part of CREATE PROCEDURE execution (versus by way of a separate BIND PACKAGE step). That being the case, these options for a native SQL procedure are specified via CREATE PROCEDURE options (some of which have names that are slightly different from the corresponding precompile options, an example being the PACKAGE OWNER option of CREATE PROCEDURE, which corresponds to the OWNER option of the BIND PACKAGE command). Here's another reason to pay attention to these options of CREATE PROCEDURE when converting an external SQL procedure to a native SQL procedure: the default options for CURRENTDATA and ISOLATION changed to NO and CS, respectively, in the DB2 9 environment.
A less-obvious consideration when it comes to external-to-native conversion of SQL procedures has to do with condition handlers. These are statements in the SQL procedure that are executed in the event of an error or warning situation occurring. External SQL procedures do not allow for nested compound SQL statements (a compound SQL statement is a set of one or more statements, delimited by BEGIN and END, that is treated as a block of code); so, if you had within an external SQL procedure a compound SQL statement, and you wanted within that compound SQL statement a multi-statement condition handler, you couldn't do that by way of a nested compound statement. What people would often do instead in that case is code the condition handler in the form of an IF statement containing multiple SQL statements. In converting such an external SQL procedure to a native SQL procedure, the IF-coded condition handler should be changed to a nested compound SQL statement set off by BEGIN and END (in fact, it would be a good native SQL procedure coding practice to bracket even a single-statement condition handler with BEGIN and END). This change would be very much advised not just because nested compound statements are allowed in a native SQL procedure, but also because, in a native SQL procedure, an IF statement intended to ensure execution of multiple statements in an IF-based condition handler (e.g., IF 1=1 THEN...) would itself clear the diagnostics area, thereby preventing (most likely) the condition handler from functioning as desired (in an external SQL procedure, it so happens that a trivial IF statement such as IF 1=1 will not clear the diagnostics area).
Also in the not-so-obvious category of reasons to change the body of a SQL procedure when converting from external to internal: resolution of unqualified parameter, variable, and column names differs depending on whether a SQL procedure is external or native. Technically, there's nothing to prevent you from giving to a parameter or variable in a SQL procedure a name that's the same as one used for a column in a table that the SQL procedure references. If a statement in an external SQL procedure contains a name that could refer to a variable or a parameter or a column, DB2 will, in processing that statement, check to see if a variable of that name has been declared in the SQL procedure. If a matching variable name cannot be found, DB2 will check to see if the name is used for one of the procedure's parameters. If neither a matching variable nor a matching parameter name is found, DB2 will assume that the name refers to a column in a table referenced by the procedure. If the same statement is encountered in a native SQL procedure, DB2 will first check to see if the name is that of a column of a table referenced by the procedure. If a matching column name is not found, DB2 will then look for a matching variable name, and after that for a matching parameter name. If no match is found, DB2 will return an error if VALIDATE BIND was specified in the CREATE statement for the native SQL procedure (if VALIDATE RUN was specified, DB2 will assume that the name refers to a column, and will return an error if no such column is found at run time). Given this difference in parameter/variable/column name resolution, it would be a good idea to remove ambiguities with respect to these names in an external SQL procedure prior to converting the routine to a native SQL procedure. This could be done either through a naming convention that would distinguish variable and parameter names from column names (perhaps by prefixing variable and parameter names with v_ and p_, respectively) or by qualifying the names. Variable names are qualified by the label of the compound statement in which they are declared (so if you're going to go this route, put a label before the BEGIN and after the END that frame the compound statement), parameter names are qualified by the procedure name, and column names are qualified by the name of the associated table or view.
Then there's the matter of the package collection name that will be used when the SQL procedure is executed. For an external SQL procedure, this name can be specified via the COLLID option of the CREATE PROCEDURE statement. [If NO COLLID -- the default -- is specified, the name will be the same as the package collection of the calling program. If the calling program does not use a package, the SQL procedure's package will be resolved using the value of CURRENT PACKAGE PATH or CURRENT PACKAGESET, or the plan's PKLIST specification.] When a native SQL procedure is created, the name of the associated package's collection will be the same as the procedure's schema. In terms of external-to-native SQL procedure conversion, here's what that means:
Native SQL procedures are the way of the future, and I encourage you to develop a plan for converting external SQL procedures to native SQL procedures (if you have any of the former). It's very do-able, and for some external SQL procedures the conversion will in fact be very straightforward. For others, more conversion effort will be required, but the payoff should make that extra effort worthwhile.
First, a little more on this drop-and-recreate-without-EXTERNAL-NAME-or-FENCED business. It is true that, in a DB2 9 New Function Mode system, a SQL procedure (i.e., a stored procedure for which the routine source is contained within the CREATE PROCEDURE statement) will be external if it is created with the EXTERNAL NAME and/or FENCED options specified, and native if created with neither EXTERNAL NAME nor FENCED specified; however, it is not necessarily the case that an external SQL procedure re-created sans EXTERNAL NAME and FENCED will behave as you want it to when executed as a native SQL procedure. Why is this so? Well, some of the reasons are kind of obvious when you think about it. Others are less so. On the obvious side, think about options that you'd specify for an external SQL procedure (which ends up becoming a C language program with embedded SQL) at precompile time (e.g., VERSION, DATE, DEC) and at bind time (e.g., QUALIFIER, CURRENTDATA, ISOLATION). For a native SQL procedure, there's nothing to precompile (as there is no associated external-to-DB2 program), and the package is generated as part of CREATE PROCEDURE execution (versus by way of a separate BIND PACKAGE step). That being the case, these options for a native SQL procedure are specified via CREATE PROCEDURE options (some of which have names that are slightly different from the corresponding precompile options, an example being the PACKAGE OWNER option of CREATE PROCEDURE, which corresponds to the OWNER option of the BIND PACKAGE command). Here's another reason to pay attention to these options of CREATE PROCEDURE when converting an external SQL procedure to a native SQL procedure: the default options for CURRENTDATA and ISOLATION changed to NO and CS, respectively, in the DB2 9 environment.
A less-obvious consideration when it comes to external-to-native conversion of SQL procedures has to do with condition handlers. These are statements in the SQL procedure that are executed in the event of an error or warning situation occurring. External SQL procedures do not allow for nested compound SQL statements (a compound SQL statement is a set of one or more statements, delimited by BEGIN and END, that is treated as a block of code); so, if you had within an external SQL procedure a compound SQL statement, and you wanted within that compound SQL statement a multi-statement condition handler, you couldn't do that by way of a nested compound statement. What people would often do instead in that case is code the condition handler in the form of an IF statement containing multiple SQL statements. In converting such an external SQL procedure to a native SQL procedure, the IF-coded condition handler should be changed to a nested compound SQL statement set off by BEGIN and END (in fact, it would be a good native SQL procedure coding practice to bracket even a single-statement condition handler with BEGIN and END). This change would be very much advised not just because nested compound statements are allowed in a native SQL procedure, but also because, in a native SQL procedure, an IF statement intended to ensure execution of multiple statements in an IF-based condition handler (e.g., IF 1=1 THEN...) would itself clear the diagnostics area, thereby preventing (most likely) the condition handler from functioning as desired (in an external SQL procedure, it so happens that a trivial IF statement such as IF 1=1 will not clear the diagnostics area).
Also in the not-so-obvious category of reasons to change the body of a SQL procedure when converting from external to internal: resolution of unqualified parameter, variable, and column names differs depending on whether a SQL procedure is external or native. Technically, there's nothing to prevent you from giving to a parameter or variable in a SQL procedure a name that's the same as one used for a column in a table that the SQL procedure references. If a statement in an external SQL procedure contains a name that could refer to a variable or a parameter or a column, DB2 will, in processing that statement, check to see if a variable of that name has been declared in the SQL procedure. If a matching variable name cannot be found, DB2 will check to see if the name is used for one of the procedure's parameters. If neither a matching variable nor a matching parameter name is found, DB2 will assume that the name refers to a column in a table referenced by the procedure. If the same statement is encountered in a native SQL procedure, DB2 will first check to see if the name is that of a column of a table referenced by the procedure. If a matching column name is not found, DB2 will then look for a matching variable name, and after that for a matching parameter name. If no match is found, DB2 will return an error if VALIDATE BIND was specified in the CREATE statement for the native SQL procedure (if VALIDATE RUN was specified, DB2 will assume that the name refers to a column, and will return an error if no such column is found at run time). Given this difference in parameter/variable/column name resolution, it would be a good idea to remove ambiguities with respect to these names in an external SQL procedure prior to converting the routine to a native SQL procedure. This could be done either through a naming convention that would distinguish variable and parameter names from column names (perhaps by prefixing variable and parameter names with v_ and p_, respectively) or by qualifying the names. Variable names are qualified by the label of the compound statement in which they are declared (so if you're going to go this route, put a label before the BEGIN and after the END that frame the compound statement), parameter names are qualified by the procedure name, and column names are qualified by the name of the associated table or view.
Then there's the matter of the package collection name that will be used when the SQL procedure is executed. For an external SQL procedure, this name can be specified via the COLLID option of the CREATE PROCEDURE statement. [If NO COLLID -- the default -- is specified, the name will be the same as the package collection of the calling program. If the calling program does not use a package, the SQL procedure's package will be resolved using the value of CURRENT PACKAGE PATH or CURRENT PACKAGESET, or the plan's PKLIST specification.] When a native SQL procedure is created, the name of the associated package's collection will be the same as the procedure's schema. In terms of external-to-native SQL procedure conversion, here's what that means:
- If external SQL procedures were created with a COLLID value equal to the procedure's schema, it's smooth sailing ahead.
- If external procedures were create with a COLLID value other than the procedure's schema, a relatively minor adjustment is in order. This adjustment could take one of two forms: a) go with the one "root" package for the native SQL procedure in the collection with the name matching the routine's schema, and ensure that this collection will be searched when the procedure is called, or b) add a SET CURRENT PACKAGESET statement to the body of the SQL procedure, specifying the collection name used for COLLID in creating the external SQL procedure, and (via BIND PACKAGE COPY) place a copy of the "root" package of the native SQL procedure in that collection.
- If external SQL procedures were bound with NO COLLID, there could be a good bit of related work in converting those routines to native SQL procedures, especially if a number of "variants" of an external SQL procedure's "root" package were generated and placed in different collections. The external SQL procedure package variants will have to be identified, SET CURRENT PACKAGESET will be needed in the native SQL procedure to navigate to the desired collection at run time (perhaps using a value passed by the caller as a parameter), and variants of the native SQL procedure's "root" package (again, that being the one in the collection with the name matching the procedure's schema) will need to be copied into the collections in which the external SQL procedure package variants had been placed.
Native SQL procedures are the way of the future, and I encourage you to develop a plan for converting external SQL procedures to native SQL procedures (if you have any of the former). It's very do-able, and for some external SQL procedures the conversion will in fact be very straightforward. For others, more conversion effort will be required, but the payoff should make that extra effort worthwhile.
posted by Robert Catterall at
7:17 AM
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home