DB2 Basics: Executing a Script of SQL

There are quite a few scenarios in which DBAs need to execute a script of SQL. Sometimes developers provide such a script to be executed. Sometimes we just have a large number of commands that need to be done as a whole.

File Naming

For Linux and UNIX systems the file name does not matter a bit. I like to name my SQL files with .sql at the end. I also find .ddl and .dml acceptable for data definition language and data manipulation language when a file is specific to only one of those. Windows is more problematic in my experience, as are MS applications like Outlook. You may need to stick to or a avoid certain file extensions for Windows.

A list of file extensions frequently used with DB2 databases:

Extension Purpose
.sql General SQL statements
.ddl Data Definition Language – such as CREATE TABLE, ALTER TABLE, and CREATE INDEX
.dml Data Manipulation language – such as INSERT, UPDATE, and DELETE
.del Delimited data, often CSV, but may use other delimiters
.csv Comma delimited data
.ixf DB2’s Integrated Exchange Format – includes both data and information on table structure
.asc Non-delimited ASCII data

Some of those extensions are not specifically related to executing a script of SQL, but it is good information to have.

SQL vs. Shell or Other Language Scripts

Scripts of SQL commands may show up as shell scripts, actually. If they are shell scripts, they will look something like this:

db2 "connect to sample"
db2 "create table...."
db2 "alter table ...."
db2 "create index ...."
db2 "insert into ...."

Notice the “db2” before each statement, and the quotes around the statements. The quotes around the statements may be optional in some scenarios. These files should end in .sh or .ksh to indicate they are shell scripts specifically. Shell scripts can be executed at the UNIX or Linux command line simply like this:

./filename.sh |tee ./filename.sh.out

Always send the output somewhere so you can review it later if needed.

A file of pure SQL should look more like:

connect to sample;
create table....;
alter table ....;
create index ....;
insert into ....;

Note the lack of the “db2” at the beginning of each line. Note also that each line terminates in a semicolon(;). The semicolon is the delimiter in this example. Since the semicolon is the default delimiter, you could execute the above file using:

db2 -tvmf filename.sql |tee filename.sql.out

Finally, you might have a file that uses an alternate delimiter. This is required when working with certain triggers and stored procedures. In the case of an alternate delimiter, the file might look like this:

connect to sample@
create table....@
alter table ....@
create index ....@
insert into ....@

That file would be executed using:

db2 -td@ -vmf filename.sql |tee filename.sql.out

Basic Command Line Options

There are several command line options, I used above. Here is the breakdown of these options I use most frequently:

  • t – terminated – the statements are terminated with a delimiter. The default delimiter is the semi-colon
  • d – delimiter – the default delimiter is being overriden, and db2 uses the character immediately following d as the delimiter.
  • v – verbose – the statement will be echoed in output prior to the result of the statement. This is extremely useful when reviewing output or troubleshooting failed statements
  • m – prints the number of lines affected by DML
  • f – file – indicates that db2 should execute statements from a file, with the filename specified one space after the f.

There are plenty of other interesting command line options available in the DB2 Knowledge Center: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0010410.html?cp=SSEPGG_10.5.0/3-5-2-0-2&lang=en

Things that may Happen Inside or Outside of a Script

There are things that may either be a part of the SQL script you’re executing, or done outside of the SQL script prior to executing the script.

For example you could have this SQL script:

create table....;
alter table ....;
create index ....;
insert into ....;

And first connect to a database or issue SET CURRENT SCHEMA or other commands, followed by executing the script:

$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/AIX64 10.5.3
 SQL authorization ID   = DB2INST1
 Local database alias   = WC42U1L1

$ db2 -tvmf filename.sql |tee filename.sql.out

The other choice is to incorporate the connect into the script like this:

connect to sample;
create table....;
alter table ....;
create index ....;
insert into ....;

And simply execute the full script:

$ db2 -tvmf filename.sql |tee filename.sql.out

The choice between the two depends on your personal preferences and environment. If you’ll be executing a script against multiple databases, would you prefer to have to edit the file to include the proper connection information for each one, or would you prefer to just connect first? Having the database name in the file can be useful for the output to verify later that you connected to the right database when executing the script.

You may also like...

7 Responses

  1. Chris Aldrich says:

    I have started to redirect standard error before piping to tee. This is especially important when debugging the script as sometimes small syntax errors get written to the screen through standard error but do not show up in the log piped out by tee. So I like to do

    ksh myDBscript.sh 2>&1 | tee myDBScript.log

    Just a little extra protection in case I or someone else messed up the script. Gives a change to find the problem.

    I also want to mention that if you issue multiple statements and/or connect to multiple databases to save the TERMINATE command for the very end of the script. I have had where a TERMINATE was in between commands in a script and it killed the session with DB2 (even though I was running as db2 -tvf) and the rest of the commands then errored (including trying to reconnect).

  2. Manohar says:

    Hi Chris , when you are trying to write an SQL script which has multiple db connections in between , we better go for CONNECT RESET than TERMINATE , as the TERMINATE command even terminate the back-end process. , You can refer this http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001973.html?cp=SSEPGG_9.7.0%2F3-6-2-4-122 .
    Give a TERMINATE at EOF to make sure you come out clean

  3. Raf Mathijs says:

    I usually check my logs by using grep:

    cat logfile | grep -i -e “SQL….N” -e “SQL….W”

  4. Peter says:

    Just my personal preference is to check for errors. I went with logfile parsing in the past but this is too cumbersome for simple check whether my script was successful or not. I am looking at the error level that gets returned by db2. Everything other than 0 might be suspicious. However in most cases 1 is ok and sometimes I am fine with 2 as well.

    http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0010411.html

    Another pattern that I started to like is to have shell and sql scripts in conjunction. The shell scripts make sure the db connection is set up and the sql script does the actual work. This way I can use parameters for connecting to different databases and the actual sql work still stays the same regardless of the DB name. Nothing beats the beauty of having only one call to cater the needs of dev and production environments alike.

    There is one more perk of always having a shell script as a driver. The last line should always be “banner success” (make sure that all error cases exit with an error code beforehand already).

  5. Srini says:

    Hi Ember,

    Need to be able to setup a db2 luw database on the fly with creating the database, creating as many table as one wants in it with indexes, primary keys creation etc on the fly as well as loading of generated data thru SQL with commit points within as it inserts records and thus be able to create however big a database size as one wants…Do you know of any such shell script or something that has all this capabilities or a set of SQL someplace that can do this?

    I know Vikram Khatri, one of amongst known names in DB2 World, used to have such scripts in his db2ude.com. which unfortunately got taken down….

    Incase you are aware of one such, please let know..Thanks..

    • Ember Crooks says:

      I don’t have or know of any such scripts. Obviously, db2move can be useful if trying to copy an existing database, when used with db2look. It’s not hard to write a script to cycle through a list of export files in a subdirectory and import them into tables of the same name using ksh or perl. But I don’t have any to share. I’d suggest asking on the forums and at conferences.

  1. September 4, 2014

    […] lot of work is done with DB2 using SQL scripts of commands. See my blog entry on DB2 Basics: Executing a Script of SQL if you’re not familiar with how to execute a script of […]

Leave a Reply

Your email address will not be published. Required fields are marked *