In our example we have the SQL statements in a set of files, where each statement is separated by a default delimiter, a semicolon. There is no difference in executing a set of DDL statements or a set of SQL statements. This means that the shell scripts are similar to the shell scripts in 5.2, “Deploying a database using scripts” on page 216, and the Java application can be reused.

To demonstrate populating data with SQL statements, we create one data insert file for each table in our sample database, that is DEPARTMENT, EMPLOYEE, and STAFF, and one file that contains all the statements. These files are named department.sql, employee.sql, staff.sq, and populate.sql.

Using a shell script

In Example 5-12we show how to populate the database by executing the three

files independently. All DB2 actions are logged in the file populatedb.log. This example is an extract from the sample populatedb.cmd.

Example 5-12 Populating ITSO sample database using a shell script

@REM Step 1

@REM -----------------------------------------------------------

db2 -o- -l populatedb.log -s CONNECT TO ITSODB user %1 using %2 if errorlevel == 4 goto error

@REM -----------------------------------------------------------

 

@REM Step 2

 

@REM -----------------------------------------------------------

 

db2 -o- -

l populatedb.log -s -c- -

tf department.sql

if errorlevel == 4 goto error

 

db2 -o- -

l populatedb.log -s -c- -

tf employee.sql

if errorlevel == 4 goto error

 

db2 -o- -

l populatedb.log -s -c- -

tf staff.sql

if errorlevel == 4 goto error

 

@REM -----------------------------------------------------------

 

@REM Step 3

 

@REM -----------------------------------------------------------

 

db2 -o- -

l populatedb.log commit

 

if errorlevel == 4 goto error

 

goto success

 

@REM -----------------------------------------------------------

 

@REM Step 4.a

 

@REM -----------------------------------------------------------

 

:error

 

 

db2 -o- -

l populatedb.log rollback

 

db2 -o- -

l populatedb.log connect reset

exit /b 4

 

 

Chapter 5. Deploying pre-configured databases

233

Page 247
Image 247
IBM DB2 manual Using a shell script, 233