DB2 scripting is useful for automating tasks, testing, and quickly prototyping simple scenerios. This article describes how to make scripts that contain SQL procedural language elements, how to spool the output of scripts to a log file, and how to deal with parameters.

Share:

Paul Yip (ypaul@ca.ibm.com), Database Consultant, IBM Toronto Labs

Paul Yip is a Database Consultant for IBM Toronto Labs and works primarily with IBM Business Partners to migrate applications from other database products to DB2. He enjoys a good round of golf that doesn't cost more than a good dinner, and fine dining that doesn't cost more than a good round of golf. He can be reached at ypaul@ca.ibm.com.



01 March 2002

Introduction

Examples were developed and tested for DB2® for Linux, UNIX, and Windows. If you have problems with the examples, please contact the author.

This article uses examples to teach you how to write advanced SQL scripts in DB2® Universal Database™ which can be used for, but are not limited to, running simple batch processes, developing test scenarios and automating the testing of application components. This article assumes that you are familiar with the basics of DB2 SQL. For a complete reference of DB2 SQL PL, including its use in functions, triggers, and stored procedures, we recommend the book, DB2 SQL PL: Essential Guide for DB2 UDB on Linux, UNIX, and Windows, i5/OS, and z/OS (ISBN: 0131477005) available from most online book sellers.

If you have read my previous DB2 Developer Domain article, "Using SQL Procedural Language for Triggers in DB2 UDB," then you are familiar with some of the syntax that you will see here.

Prerequisites: For these examples to work, you will need DB2 v7.1 with FixPak 3, DB2 v7.2, or higher.


Example 1: Hello world

Following the common tradition of learning new computer-related things, we'll begin with a simple script to create a table called HELLO and insert the words "hello world" into it.

1. Using a text editor, type the following lines and save the file as helloworld.db2.

!echo Beginning Script1@
!echo creating table....@
				
CREATE TABLE HELLO (mycol VARCHAR(20))@
				
!echo inserting values....@
				
INSERT INTO HELLO VALUES ('hello world')@
				
!echo displaying result@
				
SELECT * FROM HELLO@
				
!echo cleaning up@
				
DROP TABLE HELLO@
				
!echo done.@

2. Connect to a database, using a command like the following:

db2 connect to <dbname> user <userid> using <password>

3. When you are connected, enter the following command:

db2 -td@ -f helloworld.db2

This command executes like a standard DB2 Command Line Processor (CLP) command with some special flags:

  • The -t flag by itself specifies that the script uses the standard semicolon (;) as the end of a command.
  • When used in combination with the -d flag and @, however, the CLP interprets the @ as the end of a statement. A later example will stress the importance of this. Also, note that there must be no space between -d and @, because that would indicate that you want a space as the termination character.
  • Finally, the -f flag and file name parameter specifies the file name that is used as input to the CLP.

4. After you enter the command in step 3, the screen output of the script will look like this:

Beginning Script1
				
creating table....
				
DB20000I  The SQL command completed successfully.
				
inserting values....
				
DB20000I  The SQL command completed successfully.
				
displaying result
				
				
MYCOL
--------------------
hello world
				
  1 record(s) selected.
				
				
cleaning up...
				
DB20000I  The SQL command completed successfully.
				
Done.

From the above example, you can see that basic scripting is fairly easy in DB2. The example highlights the following points:

  • This type of scripting is different from OS shell scripts that prefix every DB2 statement with ‘db2'. For examples of this, see Passing parameters from OS shell scripts.
  • OS commands can be executed from these DB2 scripts by prefixing the OS command with an exclamation mark (!). The DB2 CLP also supports the echo statement, but I used "!echo" to illustrate use of OS commands.
  • The command termination character (@) is required at the end for all commands inside the script, including OS commands.

Tip: You might have noticed that the executed DB2 commands were not displayed to the screen, only the success or failure of the command. If you want to see the command shown on the screen then use the -v flag. For example:

db2 -v -td@ -f helloworld.db2

Example 2: A more complex script with logged output

Now let's try a significantly more complex script. Let's say, for example, that you want to use scripting to do something unattended overnight, but you don't plan on staying at the office to ensure that it executes successfully. One thing you could do is write the output of the script to a log file and view the results in the morning.

In order to illustrate some advanced logic that is supported by DB2, let's make the example more complicated by adding the following caveat: The script must only execute on Mondays and Fridays. To enforce this rule, we'll incorporate constraints into the script.

1. With a text editor, type the following code and save the file as complex.db2:

!echo beginning complex.db2@
!echo creating table....@
CREATE TABLE HELLO (mycol VARCHAR(20))@
				
begin atomic
if (DAYOFWEEK (Current Timestamp)=2) then
    insert into HELLO values ('Hello Monday');
elseif (DAYOFWEEK (Current Timestamp)=6) then
    insert into HELLO values ('Hello Friday');
else
    SIGNAL SQLSTATE '80000'
        SET MESSAGE_TEXT='Script is for MON & FRI only!';
end if;
end@
				
!echo Retrieving from HELLO table..@
SELECT * FROM HELLO@
				
!echo Cleaning up...@
DROP TABLE HELLO@
				
!echo This message will get written to screen, but not to the log file@
VALUES ' This message will get written to the screen AND log file'@

Hint: DAYOFWEEK() is a function that, given a timestamp parameter, returns a value between 1 (Sunday) and 7 (Saturday) representing the day of the week.

Before running the above script, let's highlight some of the DB2 features that are illustrated here:

  • You can use DB2 built-in functions such as DAYOFWEEK(). You can incorporate any DB2- provided function or any of your own user-defined functions.
  • You can use DB2 special registers such as CURRENT TIMESTAMP, CURRENT SCHEMA, CURRENT DATE, CURRENT TIME.
  • You can use SIGNAL to throw a user-defined SQL error, which is returned to the console or application that launched the script.
  • You can use the DB2 VALUES statement in your scripts. However, if VALUES is used within an atomic compound statement, any result from the function will not be displayed.
  • You can use a mixture of compound and regular SQL statements in a single script.
  • You can use advanced procedural logic in compound SQL statements and commands can span multiple lines.

3. To run this script, enter the following commandfrom the command line. As before, you'll first need to have a connection to a database.

db2 -td@ -f complex.db2 -z output.log

The addition of the -z flag in the command and the filename parameter, output.log, causes DB2 to write all DB2 generated output to the specified file. With this flag, you can let the script run and return to your workstation later to analyze the results of the script.

Table 1 shows a side-by-side comparison of screen output and log file output when the script is run on a Friday:

Table 1
Screen OutputLog File Output
beginning complex.db2

DB20000I The SQL command
completed successfully.

Retrieving from HELLO
table..


MYCOL
--------------------
Hello Friday
1 record(s) selected.

Cleaning up...

DB20000I The SQL command
completed successfully.

This will get written to
screen, but not to log file


1
-------------------
----------------
This will get written to the
screen AND log file

1 record(s) selected.
DB20000I The SQL command
completed successfully.

MYCOL
--------------------
Hello Friday

1 record(s) selected.

DB20000I The SQL command
completed successfully.

1
--------------------
---------------
This will get written to the
screen AND log file

1 record(s) selected.

In the output above, the script executes successfully but we notice that the screen output differs from the output to the log file (output.log). The differences are highlighted in bold and are the OS "echo" commands prefixed by an exclamation (!) mark. Because those commands are executed at the OS level, not by DB2, their output does not get recorded by the -z flag.

Notice that the last comment, issued by the following DB2 VALUES statement:

This will get written to the screen AND log file

is written to both the screen and the log file because the command is executed by DB2 rather than by the OS.

Below is the corresponding screen and log file results when the script is run on a Wednesday:

Screen OutputLog File Output
beginning complex.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:
"Script is for MON & FRI
only!" SQLSTATE=80000


Retrieving from HELLO
table..

MYCOL
-------------------

0 record(s) selected.


Cleaning up...

SQL0100W No row was found
for FETCH, UPDATE or DELETE;
or the result of a query is
an empty table. SQLSTATE=02000


This will get written to
screen, but not to log file

1
--------------------
---------------
This will get written to the
screen AND log file

1 record(s) selected.
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:
"Script is for MON & FRI
only!" SQLSTATE=80000

MYCOL
--------------------
0 record(s) selected.

SQL0100W No row was found
for FETCH, UPDATE or DELETE;
or the result of a query
is an empty table. SQLSTATE=02000


1
-----------------
------------------
This will get written to the
screen AND log file

1 record(s) selected.

This time, bold highlighting indicates differences in output between successful and unsuccessful executions of the script. See how the script fails, as expected, and shows an "Application Raised Error SQL0438N" with our custom error message "Script is for MON & FRI only!". Because of the error, no rows were inserted into the HELLO table, and the subsequent DELETE statement found nothing to delete, resulting in an SQL warning, which is harmless for our requirements.

Examining the compound SQL

Look again at the code in Step 1. The BEGIN ATOMIC and END@ signify a DB2 compound atomic SQL statement. By using compound SQL statements, you can put several SQL statements into one statement and have DB2 treat the entire script body as a single, all-or-nothing statement (like a transaction).

As illustrated by the example above, you can mix compound atomic and regular SQL statements in a single script to achieve the desired end result. Using compound atomic SQL statements also lets you use SQL procedural language elements.

Non-atomic compound SQL statements are not supported for scripting.

Termination characters

Notice how the @ symbol is not used as the termination character for statements that are between BEGIN ATOMIC and END@. Within a compound statement body, you must use the semicolon to signify the end of any SQL statement. It is for this reason that we had to use the -td@ flag, to enable the command parser to differentiate between the end of statements within the compound SQL statement and the other statements in the script.

SELECT statements inside compound SQL

If you enclose SELECT statements inside compound SQL statements, the results of the SELECT statement are not be displayed to the screen. This is because the SELECT statement "never happens" as far as the shell is concerned (DB2 only returns the success or failure of the compound atomic statement as a whole). For example, compare the results of the following:

Script CodeResult
SELECT 'hello'
FROM
sysibm.sysdummy1@
DB20000I The SQL command
completed successfully.

1
-------
hello

1 record(s) selected.
BEGIN ATOMIC
SELECT 'hello'
FROM
sysibm.sysdummy1;
END@
DB20000I The SQL command
completed successfully.

Hint: To suppress the column header of the result of the SELECT statement, use a -x flag when you issue the db2 command. Suppressing headers is useful if the output of your script is to be used as input to another process.

Throwing SQL exceptions

The SIGNAL SQLSTATE...SET MESSAGE_TEXT statement throws a user-defined SQL exception. In the above case, SQLSTATE 80000 is thrown with the error text "Script is for MON & FRI only!". If there were other SQL statements that modified data within the same BEGIN ATOMIC section, they would be rolled back by this error. Statements that follow the compound statement would continue to execute.

The limit on the length of the error message for SIGNAL statements is 70 characters. If you specify a message that is greater than this limit, it will be truncated without warning.


Example 3: Parameterizing your scripts

To make scripts more flexible, you might want to create scripts that can take parameters from the command line at execution time. Unfortunately, in the type of scripting that we've done so far parameters cannot be passed from the OS shell to the script. However, you can work around this limitation as follows:

  1. Temporarily creating a SQL user-defined function (UDF) or SQL stored procedure that takes parameters.
  2. Calling the function or procedure within the script.
  3. Dropping the UDF or stored procedure at the end of the script.

Choosing whether to use a UDF or stored procedure

There are two primary reasons to choose a stored procedure instead of a UDF:

  • If you are working with complex queries and large data sets, use SQL stored procedures for optimal performance.
  • If your code modifies data (INSERT, UPDATE, or DELETE), you must use an SQL stored procedure because SQL UDFs do not currently support data modifications.

Passing parameters from OS shell scripts

For OS shell scripts that prefix database commands with "db2", you can pass parameters in the following manner (for UNIX®):

db2 "select *
from employee where empno='$1'"
db2 "select * from
employee where
empno='$2'"

The above script selects from the EMPLOYEE table where the employee number is equal to the first parameter passed to the script from the OS, and then again with the second parameter passed. (On Windows® platforms, use %1 and %2 instead of $1 and $2, respectively.)

Shell scripts that use commands as above, however, do not easily support SQL procedure elements like IF/THEN/ELSE, local variables, FOR LOOPS, etc. within the script. This is mostly due to the fact that formatting of the code is more restrictive, which makes this method suitable for simple scripting only.

Example using a UDF

1. To prepare this example we need to create a table and insert some values into it. Connect to a database and execute the following SQL statements:

CREATE TABLE tab1 (id INT NOT NULL PRIMARY 
KEY, text VARCHAR(10))
INSERT INTO tab1 VALUES (1, 'one')
INSERT INTO tab1 VALUES (2, 'two')

2. Now, type the following script into a text file and save it as funcparam.ddl:

CREATE FUNCTION getText(key INT)
LANGUAGE SQL
RETURNS VARCHAR(20)
BEGIN ATOMIC
     RETURN SELECT text FROM tab1 
t WHERE t.id=key;
END@

3. Create a second file called funcparam.cmd, which will be a shell script that takes parameters with the following contents (use the appropriate version for your platform):

UNIXWindows
db2 connect to <dbname>
db2 -td@ -f funcparam.ddl
db2 values getText($1)
db2 drop function getText (INT)
db2 connect to <dbname>
db2 -td@ -f funcparam.ddl
db2 values getText(%1)
db2 drop function getText(INT)

4. Execute the script as follows:

chmod +x funcparam.cmd  (required only for UNIX to make the file executable)
funcparam.cmd 1

In the above example, the value 1 is passed as the only parameter to the funcparameter.cmd shell script. The shell script then creates the function and calls the function with the provided parameter. Then, it cleans up after itself by dropping the function before completing. Note that in the UNIX script, a connect statement is required because shell scripts in UNIX are forked off into their own process. The connect statement is not required on Windows.

Here is the output of the above example:

db2 -td@ -f funcparam.ddl
				
DB20000I  The SQL command completed 
successfully.
				
db2 values getText(1)
				
1
--------------------
one
				
1 record(s) selected.
				
db2 drop function getText(INT)
				
DB20000I  The SQL command completed 
successfully.

Example using an SQL stored procedure

Here is the equivalent version of the above script using SQL stored procedures. (You need to have a supported C compiler on your machine. See the DB2 Application Building Guide for more information.)

1. Type the following script into a text file and save the file as procparam.ddl:

CREATE PROCEDURE getText (IN key INT)
LANGUAGE SQL
RESULT SETS 1
BEGIN
      DECLARE C1 CURSOR WITH RETURN FOR
            SELECT text FROM tab1 t WHERE t.id=key;
				
      -- leave cursor open so that result set is returned.
      OPEN C1;
END@

2. Create a second file called procparam.cmd, which will be the shell script that takes parameters, with the following contents (use the appropriate version for your platform):

UNIXWindows
db2 connect to <dbname>
db2 -td@ -f procparam.ddl
db2 call getText($1)
db2 drop procedure getText (INT)
db2 connect to <dbname>
db2 -td@ -f procparam.ddl
db2 call getText(%1)
db2 drop procedure getText (INT)

Then, to execute the script, do:

chmod +x procparam.cm  (required for UNIX only)
procparam.cmd 1

Additional tips

This section includes some tips that I've found to be useful:

Timestamps and other special registers

Look at the following script. What would you expect to happen?

BEGIN ATOMIC
      Insert into HELLO values (char (current timestamp));
      Insert into HELLO values (char (current timestamp));
END

At first glance, you mightd expect two slightly different timestamp values to be inserted into the Hello table. However, if you execute it, you will find that the same timestamp value is inserted twice. This happens because ATOMIC compound SQL statements are really a set of SQL statements executed as a single SQL statement. Therefore, be careful how you use date and time registers.

This behavior applies to SQL UDFs as well when BEGIN ATOMIC is part of its definition. It does not apply to SQL stored procedures unless you have enclosed the SQL statements within the same BEGIN ATOMIC section.

If you need to generate unique timestamps, use the GENERATE_UNIQUE() function with the timestamp. For example:

BEGIN ATOMIC
      insert into HELLO
            values (char(timestamp(generate_unique()) + current timezone));
      insert into HELLO
            values (char(timestamp(generate_unique()) + current timezone));
END

Cursors in UDFs and scripts

Updatable cursors are currently not supported. If you need to make use of cursors in your scripts, you can use the FOR LOOP construct (which works like a read-only cursor), and use UPDATE statements inside the loop. For example:

BEGIN ATOMIC
      FOR cur1 AS SELECT c1, c2, c3 from mytable
      IF cur1.c1 = 1 THEN
            INSERT  INTO sometable1 values (cur1.c2, cur1.c3);
      ELSE
            UPDATE sometable1 SET somecol=cur1.c2;
      END IF;
END@

Summary

We have seen how to make use of DB2's enhanced scripting abilities. Scripting is useful for automating tasks, testing, and quickly prototyping simple scenerios. We've seen how to make scripts that contain SQL procedural language elements, how to spool the output of scripts to a log file, and how to deal with parameters in scripts by using UDFs and SQL stored procedures.

Acknowledgements: The author would like to thank Richard Swagerman and Rob Newman for their technical review of this material.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13144
ArticleTitle=Advanced SQL Scripting PL
publish-date=03012002