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:

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.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home