© 2002 International Business Machines Corporation. All rights reserved.

Important: Read the disclaimer before reading this article.

Introduction

This article is a sequel to an earlier article, Advanced SQL Procedural Scripting in DB2® v7.2. This article provides you with new insights, tips, and tricks. Because this article was written in reaction to feedback from those who read the first article and as the result of additional questions, it is less structured. In fact, there is no structure - oh well. From the heading of each section, however, you should be able to quickly find what you need.

All examples are written for UNIX®. With minor changes (provided), they will work for Windows® as well.

Back to top

What's new in DB2® V8.1 for SQL procedural language (SQL PL) scripting

The two main enhancements in DB2 Universal Database^TM Version 8 are:

• Support for partitioned databases
• Support for FETCH FIRST n ROWS

Support for partitioned databases

If you've read my other articles on SQL PL for triggers and scripting, you may have noticed the note at the top:

Examples were developed and tested for DB2 Personal Edition, Workgroup Edition and Enterprise Edition. If you have problems with the examples, please contact the author.

That was the stealth way of saying SQL PL for dynamic compound statements in Enterprise-Extended Edition (also known as EEE) was not supported. As of DB2 Version 8, this restriction has been lifted. On a side note, Enterprise Edition and Enterprise-Extended Edition have been merged to become Enterprise Server Edition (ESE) in Version 8.

Support for FETCH FIRST n ROWS

The support for FETCH FIRST n ROWS lets you restrict result sets for variable assignments when more than one row can result. Listing 1 shows this in action:

Listing 1. Example of using FETCH FIRST n ROWS with a sub select

 
BEGIN ATOMIC 
    DECLARE v_name VARCHAR(30); 
    SET v_name = (SELECT firstnme || ' ' || lastname 
                     FROM employee 
                     FETCH FIRST 1 ROWS ONLY); 
SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT=v_name; 
END@ 

Without using the clause FETCH FIRST 1 ROWS ONLY, if the SELECT returns more than one row, an error will result because we can only assign a single value to the variable v_name. With some head scratching, you might have figured out that the workaround in DB2 Version 7.2 was to use a labeled FOR loop with a LEAVE statement to simulate a cursor that fetched only the first row.

The FETCH FIRST 1 ROWS ONLY clause guarantees that only the first row will be returned, and therefore the SET statement will never fail (although null may be returned if the SELECT returned no rows). Just to show that it works, the above example uses SIGNAL SQLSTATE with a custom message. The message is the name of the first employee in the employee table.

 
$ db2 -td@ -f fig1.db2 
 
DB21034E  The command was processed as an SQL statement because it was not a valid 
  Command Line Processor command.  During SQL processing it returned: 
SQL0438N  Application raised error with diagnostic text: "CHRISTINE HAAS". 
SQLSTATE=80000 

Back to top

Checking the success of SQL statement execution

Imagine a scenario where you have a set of application install scripts. You have one script that inserts base data into a database, for example. Another script, part of a second stage of installation, absolutely must not execute if any operation in the first script did not complete successfully. One way to handle this situation is to have the first script log the success or failure of important SQL operations so that the second script can determine if it should execute or not.

Here's a neat little trick that was recently brought my attention. To set up this example, create two tables as follows:

 
db2 "create table SQL_LOG (description VARCHAR(20), status SMALLINT)" 
db2 "create table T1 (c1 INT NOT NULL UNIQUE)" 

The SQL_LOG table is used to track the success or failure of important SQL executions. Table T1 is used to facilitate the example. The important feature of table T1 is that it has a unique constraint defined on column C1.

Now, consider the statements in Listing 2.

Listing 2. Shell scripting and DB2 return codes

 
db2 "insert into T1 values (1)" 
db2 "insert into SQL_LOG values ('insert first value', $?)" 
db2 "insert into T1 values (1)" 
db2 "insert into SQL_LOG values ('insert second value', $?)" 

Windows: Replace $? with %errorlevel%

This script is fairly straightforward. The value 1 (one) is then inserted into T1 twice. After each insert into T1, we also perform an INSERT into the SQL_LOG table with a description of the operation and the value of $?. The second insert on T1 will fail because of the unique constraint.

The $? variable holds the return code of the previous SQL statement execution. If $? is 0 (zero), then the statement was successful. If $? is 1 (one), then the statement succeeded with warnings. Any other value means failure.

Here is the output when selecting all rows from the SQL_LOG table. The second entry reports a failure as expected:

 
SELECT * FROM SQL_LOG 
 
DESCRIPTION          STATUS 
-------------------- ------ 
insert first value        0 
insert second value       4 
 
  2 record(s) selected. 

Restrictions: This feature is only supported for shell scripting. It is not valid for DB2 scripts. In other words, $? and %errorlevel% only return a value if your script prefixes each statement with db2 as demonstrated by the example.

Back to top

Tricks for calling external programs

In the original Advanced SQL Procedural Scripting article, I showed you how to script with SQL PL using dynamic compound SQL statements. I also showed you how to call operating system commands by prefixing the command with an exclamation mark (!). If you played around with these two features, you might have observed that calling operating system commands was not supported from within compound SQL statements. In other words, you could do one or the other, but not both together.

For example, Listing 3 shows unsupported syntax:

Listing 3. (!) is not supported within dynamic compound statements

 
BEGIN ATOMIC 
  IF (CURRENT DATE < DATE('2002-12-25')) THEN 
    ! echo "SQL PL Book available soon!" >> messages.log 
  ELSE 
    ! echo "SQL PL Book now available!" >> messages.log 
  END IF; 
END@ 

Because dynamic compound statements are used to support SQL PL for scripting, triggers, and user-defined functions (UDFs), they all have the same restriction. So what can you do to work around this limitation?

Luckily, there is a little trick we can do which makes use of an UDF to perform actions not available in pure SQL. UDFs can be written in an external language (that is, something other than SQL) and registered with the database using the CREATE FUNCTION statement. In this example, the external language will be Java^TM.

The Java code can do (almost) anything a regular Java application can, including making operating system calls. After the external function has been registered, the function can be used wherever a regular database function can be used. Listing 4 shows the Java code that facilitates calling external programs:

Listing 4. External UDF code for os_cmdUDF.java

 
import java.io.*; 
 
public class os_cmdUDF { 
    public static int os_cmd(String cmd) { 
        Runtime rt = Runtime.getRuntime(); 
        Process p=null; 
        int success = 0; 
        try { 
            p = rt.exec(cmd); 
        } 
        catch (IOException e) { 
            success = -1; 
        } 
        return (success); 
    } 
} 
 

1. Copy the above code into a text editor and save the file as os_cmdUDF.java. Be careful how you name the file, because Java source files are case-sensitive and must match the class name. The code above is very simple - the class is called os_cmdUDF, and the method name is os_cmd(), which takes a single String parameter. Don't worry if you're not familiar with Java. The important thing to understand is that the parameter accepted by os_cmd() gets executed as an operating system call. The function returns 0 if the operating system call succeeds and -1 if it fails.

2. To build this external Java program as a DB2 function, we must compile the code, copy it to the sqllib/function directory, and issue the CREATE FUNCTION statement.
1. To compile this Java program execute:

   javac os_cmdUDF.java

   
   
2. The result will be a file called os_cmdUDF.class, which is the compiled form of the Java program. The .class file then needs to be copied to sqllib/function directory:

   cp os_cmdUDF.class sqllib/function/

   
   
3. The final step is to register the Java UDF using the CREATE FUNCTION statement. After you have connected to a database, issue the statement shown in Listing 5:
   Listing 5: Register the external UDF using CREATE PROCEDURE

   CREATE FUNCTION OS_CMD ( IN VARCHAR(1000)) RETURNS INTEGER EXTERNAL NAME 'os_cmdUDF!os_cmd' LANGUAGE JAVA PARAMETER STYLE JAVA NOT DETERMINISTIC NO SQL EXTERNAL ACTION

   
   
3. Now, you can make external operating system calls by passing the command as a parameter to the os_cmd() UDF.

   Listing 6 shows how we modify the DB2 script from above to use the new function:

   Listing 6: Example of using os_cmd()

   BEGIN ATOMIC DECLARE status INT DEFAULT 0; DECLARE script VARCHAR(20) DEFAULT '/home/db2inst1/sqllib/write_msg.sh '; IF (CURRENT DATE < DATE('2002-11-30')) THEN SET status = os_cmd(script || ' DB2 SQL PL Book Available Soon!'); ELSE SET status = os_cmd(script || ' DB2 SQL PL Book Now Available!'); END IF; IF (status <> 0) THEN SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT='OS command failed'; END IF; END@

   
   

The example attempts to execute a UNIX script called write_msg.sh, which appends the text passed as a parameter to a log file. Code differences from Listing 3 are highlighted in bold. The variable "script" was added to hold the script name with absolute path for convenience in case it changes.

Recommendation: To eliminate ambiguity, specify the absolute path of the called script.

The code in write_msg.sh is trivial, as shown in Listing 7.

Listing 7. Contents of write_msg.sh

 
# Simple message writer script 
echo $@  >> /home/db2inst1/application/messages.log 

UNIX: Ensure that the script write_msg.sh has its execution bit set and that the message.log file can be written to by the DB2 fenced user ID (i.e. db2fenc1).

Windows: Use write_msg.bat for the script name, replace $@ with %*, and use the proper Windows path syntax.)

To take full advantage of the UDF, additional code has been added to check the return code of the UDF. If the operating system command fails for whatever reason, SQLSTATE 80000 is returned.

By the way, if it was not obvious from the example, a new book on SQL PL called DB2 SQL Procedural Language for Linux, UNIX and Windows will be available soon from IBM Press/Prentice Hall (ISBN 0-13-100772-6). An excerpt from the book (draft) is available at http://www-106.ibm.com/developerworks/db2/library/techarticle/0209yip/splbook.html and can be pre-ordered using the ISBN number from any online book retailer.

With this new method of executing external operations at your disposal, imagine the possibilities:

• The UDF can be used wherever a scalar function is valid - as part of an SQL statement, other functions, triggers, and stored procedures.
• By calling operating system scripts, you can perform almost any OS operation.

If you do something really creative with this, I'd like to hear about it.

Back to top

Writing flexible table DDL scripts without the table space clause

On several occasions, I have been asked if there is a method to write parameterized scripts that create tables using a table space clause. The standard practice for creating tables in a particular table space, other than the default table space (USERSPACE1), is to supply the table space name as part of the CREATE TABLE command. For example, to create a table T1 in table space TS1, the following DDL statement would be used:

 
CREATE TABLE T1 (c1 INT) IN TS1; 

Consider the scenario where you have a set of DDL scripts that are used frequently, but the table space names change when executed in different environments (you may have many customers, each with their own table space naming policies), You'd like to be able to make your DDL scripts as flexible as possible, using something like:

 
CREATE TABLE T1 (c1 INT) IN $v_tablespace 

DB2 scripting does not currently support this, so here is a way to deal with this problem. The solution takes advantage of the way DB2 decides which table space to use when the IN clause is not specified with the CREATE TABLE statement.

How DB2 chooses a table space

DB2 will choose a table space for a table using the following rules:

1. If exists, choose a table space in partition group IBMDEFAULTGROUP over which the user has USE privilege with sufficient page size.
2. If exists, choose a table space over which the user has USE privilege with sufficient page size.
3. Issue an error (SQLSTATE 42727).

When more than one table space qualifies, preference is given according to who was granted the USE privilege:

1. The authorization ID
2. A group to which the authorization ID belongs
3. PUBLIC

If more than one table space still qualifies, the final choice is made by the database manager.

With these rules in mind, the solution relies on manipulating table space permissions. For this solution to work, the user ID used to create the tables cannot have any special privileges (such as DBADM) and cannot be the owner of any table spaces. This is generally not a problem, as I often see customers use a SYSADM user to create table spaces, and then an unprivileged user to create tables and other objects.

Using scripts to make it easier

Here is the breakdown of the steps for the solution:

1. Revoke USE privilege from all user table spaces, including USERSPACE1 from the user which will create the tables
2. Grant USE privilege to the table space where you want tables created
3. Connect to the database as the unprivileged user and create tables

Assume that we have the following requirements:

• Tables for application1 should be placed into table space APP1_TS.
• Tables for application2 should be placed into table space APP2_TS.
• Table space names should be fully parameterized for ease of deployment as table space names change.

To fulfill the requirements, we need to mix operating system scripts and DB2 scripts because of the parameterization requirement. We will make use of an operating system script that acts as the master script (createtables.bat or createtables.sh) which calls all other scripts, app1_tables.ddl and app2_tables.ddl. The user user1 is the unprivileged user.

Here is the DDL script for application1 (app1_tables.ddl):

 
CONNECT TO sample USER user1@ 
CREATE TABLE tab1 (c1 INT)@ 

Here is the DDL script for application2 (app2_tables.ddl):

 
CONNECT TO sample USER user1@ 
CREATE TABLE tab2 (c1 INT)@ 

The following master script is called by a SYSADM user and takes two parameters: application1's table space name, and application2's table space name. The line numbers are shown to facilitate the discussion and are not part of the script.

Listing 8: Master script (createtab.sh)

 
1: db2 CONNECT TO sample 
2: db2 REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC 
3: db2 GRANT USE OF TABLESPACE $1 TO user1 
4: db2 -td@ -f app1_tables.ddl 
5: db2 CONNECT TO sample 
6: db2 REVOKE USE OF TABLESPACE $1 FROM user1 
7: db2 GRANT USE OF TABLESPACE $2 TO user1 
8: db2 -td@ -f app2_tables.ddl 
9: db2 CONNECT TO sample 
10: db2 REVOKE USE OF TABLESPACE $2 FROM user1 

Windows: Replace the $ character with %.

Let's walk through the master script.

• On Line 1, a connection is made to the database using the ID of the current (SYSADM) user.
• On line 2, we revoke USE privilege from USERSPACE1 from PUBLIC (which exists by default in every database). At this point, no table spaces are usable by the unprivileged user.
• On line 3, we grant USE of the table space identified by the second parameter. Given the rules DB2 uses to choose table spaces, all tables will be directed to the desired table space when the DDL script for application1 (app1_tables.ddl) is executed on line 4.
• Once the script completes, privileges are revoked from application1's table space on line 6.
• The same steps are repeated for application2 in lines 7 to 10.

To demonstrate this example, we'll create two table spaces called APP1_TS and APP2_TS using a SYSADM user:

 
CREATE TABLESPACE APP1_TS 
MANAGED BY SYSTEM USING ('app1_ts') 
 
CREATE TABLESPACE APP2_TS 
MANAGED BY SYSTEM USING ('app2_ts') 

Then, as SYSADM, issue:

 
createtab.sh app1_Ts app2_ts 

When the sub-scripts for application1 and application2 are called, you will be prompted for user1's password. When the script completes, you'll see that TAB1 was created in APP1_TS while TAB2 was created in APP2_TS.

 
select substr(t.tabname,1,10), substr(ts.tbspace,1,10) 
from syscat.tables t, syscat.tablespaces ts 
where t.tbspaceid=ts.tbspaceid and 
tabname like 'TAB%' and 
t.tabschema not like 'SYS%' 
 
 
1          2 
---------- ---------- 
TAB1       APP1_TS 
TAB2       APP2_TS 
 
  2 record(s) selected. 

After running this example, you might want to restore USE privilege on USERSPACE1 to PUBLIC:

 
db2 GRANT USE OF TABLESPACE USERSPACE1 TO PUBLIC 

Back to top

Summary

In summary, a new set of unrelated tips and tricks were presented in response to feedback from the original Advanced SQL Procedural Scripting article and new customer FAQs. New SQL PL features for dynamic compound SQL was highlighted followed by some useful techniques, which can be employed to enhance the functionality of your scripts. Included were tips on getting the return code of SQL statements, calling external programs using a UDF and how to create table space independent DDL scripts.

Acknowledgements

The author would like to thank Serge Rielau, Tyronne Mayadunne, and Ted Wasserman for their review and support.

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• What's new in DB2 V8.1 for SQL procedural language (SQL PL) scripting
• Checking the success of SQL statement execution
• Tricks for calling external programs
• Writing flexible table DDL scripts without the table space clause
• Summary
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

New Offerings

Speed innovation with an affordable, dynamic infrastructure for software delivery

Special offers