db2 luw: Running SQL Script from Command Prompt

Question) Sometimes developers provide an SQL file to DBA to promote to Production. How to run such SQL file from Unix/Linux environment?

Solution:

You can use “db2” command with below options: (db2 list command options)

-t    Set statement termination character (default is semicolon)

-s    Stop execution on command error

-v    Echo current command

-f    Read from input file

Scenario:1   When we receive an .sql file without any line termination (i.e., New Line as line termination)

[db2inst1@rhel1 tmp]$ cat a1.sql
connect to testdb3
select * from t1

$ db2 -vsf ./a1.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Scenario:2  When we receive an .sql file without semicolon (;)

$ cat a2.sql
connect to testdb3;
select * from t1;
[db2inst1@rhel1 tmp]$ db2 -tvsf ./a2.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Note: By default -t take semicolon as line terminator

Scenario:3  When we receive an .sql file without different terminator(@)

$ cat a3.sql
connect to testdb3@
select * from t1@
$ db2 -td@ -vsf ./a3.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Note: To define termination characters 1 or 2 characters in length, use -td followed by the chosen character or characters. For example, -td%% sets %% as the statement termination characters.

Scenario:4  Define more than 1 termination character

$ cat a4.sql
connect to testdb3@$
select * from t1@$
[db2inst1@rhel1 tmp]$ db2 -td@$ -vsf ./a4.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: