executing multiple .sql files

teckguan · #1 (**permalink**) 12-26-07, 21:47

Hi everyone,

I am still new to both Shell script and db2. Currently I have a lot of .sql files to be executed. Therefore, I would like to ask how am I going to execute the files by just a few lines of command in Shell script? Thanks.

Regards,
Teck Guan.

Marcus_A · #2 (**permalink**) 12-26-07, 21:59

Create a shell script as follows (I will call it db2script.sh):

db2 -tvf file1.sql > file1.out
db2 -tvf file2.sql > file2.out
db2 -tvf file3.sql > file3.out
db2 -tvf file4.sql > file4.out
db2 -tvf file5.sql > file5.out

Make sure each of the above files has a "connect to db-name;" as the first statement. Each SQL statement will be terminated by a ";".

Then issue the following command to give execute authority on the script:
chmod 775 db2script.sh

The execute the script (this assumes you are on the correct path):
./db2script.sh

teckguan · #3 (**permalink**) 12-26-07, 22:24

Thanks. However, if I have 500 .sql files, I will have to type 500 lines. Therefore, I would like to ask is there any ways to execute the .sql files in easier way?

Regards,
Teck Guan.

stolze · #4 (**permalink**) 12-27-07, 02:35

You could use a simple loop in the shell script:

Code:

db2 "connect to <dbname>"
for f in *.sql; do
    db2 -f $f > $f.output
done

Note that Marcus used some additional options for the db2 CLP - namely, -t and -v. You may want to read up on those options (and others) and determine which ones you need.

Internet Services

Web Development

Digital Marketing

Consumer Tech