Contents


An Introduction to DB2 UDB Scripting on Windows

Comments

© 2003 International Business Machines Corporation. All rights reserved.

Important: Read the disclaimer before reading this article.

In addition to providing support for the Structure Query Language (SQL), IBM® DB2® Universal DatabaseTM provides a rich and extensive set of administrative application programming interfaces (APIs). These APIs provide a programming interface into the administrative aspects of DB2 UDB as all of the day-to-day tasks required to properly maintain a DB2 UDB system rely on the DB2 administrative APIs.

The DB2 Control Center, for example, is a Java-based graphical user interface (GUI) tool which calls the DB2 administrative APIs to perform DB2 system, instance, and database administration tasks. DB2 UDB also provides command line tools such as the DB2 CLP to perform similar tasks, which also makes calls to the DB2 administrative APIs.

In addition to the DB2 UDB command line tools, DB2 Universal Database also provides a rich set of system utilities called DB2 system commands. These commands are typically used to perform tasks that have not yet been integrated into the GUI tools. The DB2 UDB system commands are typically invoked from the operating system's shell such as the Windows#xae shell (Wshell), also known as the command prompt.

You can combine the use of SQL, DB2 commands, DB2 UDB system commands, and operating system commands in almost any scripting language from simple Windows shell scripts to more complex scripts written in JScript, VBScript, Object REXX or PerlScript to automate or schedule simple repetitive tasks to more involved complex tasks, to just about every DB2 administrative task imaginable.

This article does not cover the DB2 UDB SQL Procedural Language (SQL/PL). If you are looking for information on SQL/PL, check out the DB2 Developer Domain articles Advanced SQL Procedural Scripting in DB2 v7.2 and Yet Another Article on Advanced Scripting by Paul Yip, as well as his book DB2 SQL Procedural Language for Linux, UNIX and Windows available from IBM Press.

In general, scripting refers to a language that is interpreted. Scripts are created in a text file and are not compiled into assembler or byte code before execution. Windows provides several environments that can be used to host scripting languages. Of these the most commonly ones used to perform system administration tasks are the Windows shell (Wshell) and Windows Scripting Host (WSH).

This article describes the different scripting interfaces available with DB2 UDB on the Windows platforms beginning with an overview of the DB2 administrative APIs, the DB2 command line tools, and the DB2 system commands. I'll show you the best techniques to script DB2 UDB with the Windows shell and provide some working examples you can download from this Website.

DB2 administrative APIs

The DB2 administrative APIs have always been included, documented, and supported as part of DB2 UDB for Linux, UNIX®, and Windows. As DB2 UDB continues to provide additional functionality, such as Split Mirror Images, the API set continues to expand to support these features. These APIs are documented in detail in the DB2 Administrative API Reference. Sample source code for programming with these APIs is also provided with the developer editions of DB2 UDB.

The DB2 administrative APIs are officially supported for the following programming languages:

  • C/C++
  • COBOL
  • Fortran
  • REXX

At first glance this might seem a rather short list, given the proliferation of programming languages such as Java, Perl and Visual Basic to name a few. However, most programming languages can call APIs written in other languages, with the C API being the most commonly supported. In addition to having direct access to the DB2 administrative APIs from C, COBOL, FORTRAN, and REXX, these APIs can be accessed indirectly through other methods, such as the Java Native Interface (JNI). In fact, almost all of the DB2 administration tools that ship with DB2 UDB today are written in Java, and Java is not on the short list of programming languages officially supported by the DB2 administrative APIs. The DB2 Control Center accesses these APIs using a Java Native Interface (JNI) developed by IBM.

For those programming and scripting languages, such as VBScript, Jscript, and Perl, that do not have direct access to the DB2 administrative APIs there is yet another alternative. The DB2 CLP, which is a command line interface, provides indirect access to almost all of the DB2 administrative API functionality via DB2 Commands. The DB2 Commands are documented in detail in the DB2 UDB Command Reference.

DB2 UDB command line tools

The DB2 UDB command line tools are components of DB2 UDB that provide support for processing DB2 commands as well as SQL statements. On Windows there are three command line tools available:

You can find these by clicking on Start -> Programs -> IBM DB2 -> Command Line Tools.

DB2 Command Center

The first command line tool is the DB2 Command Center (Figure 1), which provides a GUI for processing commands and SQL statements. You can work in either interactive mode or script (batch) mode or both. Start the DB2 Command Center by clicking on Start -> Programs -> IBM DB2 -> Command Line Tools -> Command Center.

Figure 1. DB2 Command Center
DB2 Command Center
DB2 Command Center

Figure 1 is a screen capture of the DB2 Command Center. In this example we have imported the sample DB2 backup script shown in Listing 1. You can import scripts by selecting Import from the Script menu.

DB2 command line processor (CLP)

In general terms the DB2 Command Line Processor (CLP) can be thought of as a DB2 prompt DB2 => for invoking DB2 commands, much like the Windows command prompt C:\ is used for invoking operating system commands. DB2 system commands, discussed later, and operating system commands can also be invoked from any of the DB2 Command Line Tools as long as they are preceded with a bang !.

Figure 2 shows the DB2 CLP along with some basic information on how to get help for the DB2 commands. Start the DB2 CLP by clicking on Start -> Programs -> IBM DB2 -> Command Line Tools -> Command Line Processor.

Figure 2. DB2 command line processor
command line processor
command line processor

To find out more about what's new with the DB2 CLP in DB2 UDB v8.1, read the DB2 Developer Domain article Customizing the DB2 Universal Database Command Line Processor by Paul C. Zikopoulos.

DB2 command window

You can think of the DB2 command window as a DB2 shell (db2cmd.exe) that extends the Windows shell (cmd.exe) to provide support for DB2 commands and SQL statements. Commands entered in the DB2 command window must be preceded by DB2 in upper, lower, or mixed case. Operating system commands, such as dir, are simply processed to the Windows shell (cmd.exe) and do not require a bang (!).

The major difference between the DB2 CLP and the DB2 command window is that the CLP provides a DB2 Command prompt DB2 =>, whereas the DB2 command window provides a Windows command prompt where you can enter both DB2 commands and operating system commands. It is the DB2 command window that can be used most effectively to perform DB2 UDB scripting on Windows because it readily supports invoking SQL statements, DB2 commands, DB2 system commands, and operating system commands.

Hint: If you attempt to run a DB2 command directly from a Windows shell, you will receive the following error message:

DB21061E Command line environment not initialized.

This indicates that the shell cannot process your DB2 command. You simply need to start a DB2 command window by entering db2cmd.exe to initialize the command environment.

There are several options that you can set to change the default behavior of the DB2 CLP. You can get a list of these options by entering the list command options command, as shown in Figure 3. The most common options used to invoke DB2 scripts from the DB2 command windows will be demonstrated in examples that follow.

Figure 3. DB2 command window and options
DB2 command window and options
DB2 command window and options

For more information on using the DB2 CLP to script SQL and DDL statements, read the DB2 Developer Domain article DB2's Command Line Processor and Scripting by Blair Adamache. The DB2 command options are also documented in detail in the DB2 UDB Command Reference.

A DB2 script is simply a text file with one or more DB2 commands, although it can contain SQL statements, DB2 system commands, and operating system commands. Although it is not required, it is generally a good idea to develop a standard naming convention for your DB2 scripts by using an appropriate file extension. Some of the most common file extensions are shown in Table 1.

Table 1. Common file extensions for scripts
.db2DB2 script containing DB2 commands
.ddlDB2 script containing data definition language (DDL) statements
.sqlDB2 script containing SQL statements

For illustration purposes, Listing 1 contains a very simple DB2 script to back up the sample database using several DB2 commands. The script can be executed from any of the DB2 command line tools. All of the scripts used in this article are also available for download. These scripts use the default DB2 instance (DB2) and the sample database (SAMPLE). Some of these scripts will require that you install the Windows Resource Kit.

Listing 1. Sample DB2 script to back up sample database (db2backup.db2)

-- --
This is a sample DB2 Backup Script
--
ATTACH TO DB2;
BACKUP DATABASE SAMPLE;
DETACH;
TERMINATE;

You can execute the above DB2 script from a DB2 command window using the following syntax:

db2 -tvf db2backup.db2 -l db2backup.log -r db2backup.rpt

In the above example the DB2 script file (db2backup.db2) is executed using the following DB2 command options:

-t
indicates that each command is terminated, by default with a semicolon (;)
-v
indicates that each command should be echoed to standard out
-f
indicates that commands are read from an input file
-l
indicates that commands are logged to an output file
-r
indicates that results are saved to a report file

It is always a good idea to log all DB2 commands to a log file (db2backup.log using the -l option as well as saving the output of the commands to a report file (db2backup.rpt) using the -r option. The difference between these options is:

  • The -l option logs the start and completion of each command along with a date and time.
  • The -r option saves the output of each command to a file.

Using both of these options lets you use the report file to view a summary of the script's execution and the log file to view the details of each command.

The DB2 command window itself can also be invoked from a Windows shell by simply typing db2cmd from a Windows shell (command prompt). It has a number of useful switches that can be passed to it when it is invoked. Table 2 describes these options:

Table 2. Options for the DB2 command window
-cExecute the DB2 command window and terminate.
-wWait until the DB2 command window terminates.
-iInherit the environment from the invoking shell.
-tInherit the title from the invoking shell.

You can execute the script in Listing 1 from any Windows shell by first invoking the DB2 command window as in the following example:

db2cmd -c -w -i db2 -tvf db2backup.db2 -l db2backup.log -r db2backup.rpt

The DB2 command window provides return codes for each command that is executed. The return codes are listed in Table 3.

Table 3. DB2 CLP return codes
0DB2 command or SQL statement executed successfully
1SELECT or FETCH statement returned no rows
2DB2 command or SQL statement warning
4DB2 command or SQL statement error
8Command line processor system error

Note: If you are executing statements in interactive mode, the DB2 CLP does not provide a return code for each command.

DB2 system commands

DB2 system commands are a set of command line utilities you can use to perform tasks that for one reason or another have not yet been integrated into the DB2 Control Center or other GUI tools. DB2 system commands are typically invoked from the operating system shell such as the Windows command prompt, but can be invoked from the DB2 Command Center, DB2 Task Center, DB2 CLP, DB2 command window, and of course, your favorite scripting language.

There are close to a hundred DB2 system commands available. See the DB2 Command Reference for a complete list of these commands, along with detailed documentation. It is always a good idea to read through the DB2 UDB Fix Pack release notes for new DB2 system commands because they are frequently addedor enhanced via this method.

Table 4 is a very short list of some of the more popular DB2 system commands that can be very useful when scripting. At first glance some of these might seem unlikely candidates for scripting so a description is provided to give you some idea on how you might use them in a script.

Table 4. Examples of DB2 system commands
db2auditDB2 provides an audit facility to assist in the detection of unknown or unanticipated access to data. This utility can be used to automate security auditing as described in Scripting scenarios.
db2batchReads SQL statements from either a flat file or standard input, dynamically prepares and describes the statements, and returns an answer set. This DB2 command can be used within a script designed for benchmarking different instance and database configuration parameters. You will want to restart the instance and capture system information such as DB2 registry, instance, and database configurations before invoking this utility.
db2exfmtFormats the contents of the explain tables. This DB2 command can be used within a script designed for SQL tuning because you can use it to automate the explaining, extraction, and formatting of SQL statement. Scripting this DB2 command allows a large number of SQL statements to be explained, extracted, and formatted for review at a later time.
db2chkbkThis utility can be used to test the integrity of a backup image and to determine whether or not the image can be restored. This utility can be very useful at the end of a database backup script to generate a backup completion report.
db2flsnReturns the name of the database transaction log file that contains the log record identified by a specified log sequence number (LSN). This utility can be very useful as part of a database transaction log pruning script when log retain is enabled for recovery and you are running data replication and need to determine where to start pruning transaction logs.

Some of the DB2 system commands can be scripted to simply automate the functionality they already provide. The DB2 Audit Facility (db2audit.exe) is a good example of a utility that can be automated by scripting. Other DB2 system commands provide little value on their own, but can be very useful when combined with other DB2 commands or operating system commands. I'll show you some examples in the Scripting scenarios section of this article.

Windows shell scripts

The Windows shell scripting environment has come a long way from the simple batch files of early Windows operating systems. Windows shell scripts are ideal for scheduling and automating tasks that require the use of either Window shell commands or other programs that have a command line interface.

The Windows shell is an attractive scripting environment for automating DB2 UDB administration tasks for several reasons. First, the Windows shell is integrated into the Windows operating system making it available on all versions of Windows on both clients and servers. Second, Windows shell scripting is relatively simple, making it easy to implement. This allows you to focus on getting the task at hand accomplished. Finally, there are a large number of books, manuals, and reference material available for Windows shell scripting.

The Windows shell scripting environment provides:

  • Conditional command execution
  • Standard input, output, and error
  • Command line arguments
  • Environment and system variables
  • Local and global variable names
  • String and numeric variables
  • Arithmetic operators (+, -, *, /, and %)
  • Strings, substrings, and substitution
  • Conditional statements (if, if else, if not, if defined)
  • Iterative processing (range, elements, files, directories)
  • Subroutine chaining and nesting (GOTO and CALL)
  • Drive and folder localization (pushd and popd)

In addition to these features, Windows shell scripts can be further enhanced by using system utilities that are available with the Windows Resource Kit. Some of these utilities are listed in Table 5.

Table 5. Windows Resource Kit Utilities
choicePrompts the user to make a choice in a batch program by displaying a prompt and pausing for the user to choose from among a set of keys. You can use this command only in batch programs.
timethisTimes how long it takes the system to run a given command.
logtimeLogs the start or finish of command-line programs from a batch file. This is useful for timing and tracking batch jobs such as mail-address imports.
UptimeAnalyzes a single server by processing the event log to determine reliability, availability, and current uptime. The target system can either be the local system or a remote system.
logeventThis tool enables you to make entries to the Event Log on either a local or remote computer from the command prompt or a batch file.
netsvcYou can use this tool to remotely start, stop, pause, continue, and query the status of services from the command line.

A Windows shell script in the simplest form is a text file that contains one or more commands. The default file extension for Windows shell scripts is .bat and .cmd. Listing 2 is an example of a simple Windows shell script.

Listing 2. Windows shell script "Hello World" (hworld.bat)

@echo off
rem
rem This is a sample Windows Shell script.
rem echo Hello World!

There are two basic methods for invoking DB2 commands from a Windows shell script:

  • Calling DB2 command window from a Wshell script
  • Running a Wshell script from a DB2 command window

Calling a DB2 command window from a Wshell script

Listing 3 is a sample Windows shell script to back up the sample database using the DB2 CLP. This file does not need to execute inside a DB2 command window because it invokes one and then passes it the DB2 backup database command. The value for the environment variable DB2INSTANCE sets the default instance to "DB2", it can also be substituted with the DB2 ATTACH command. I'll show you this later.

Hint: Because the Windows shell does not support named command line parameters, which can be passed to a script in any order, it is a common practice to explicitly define these values as global or local variables within the script.

Listing 3. DB2 backup script invoked from a Windows shell (db2backup2.bat)

@echo off
rem
rem This is a Windows Shell Script that invokes a DB2 Command Window that
rem performs a database backup by calling the DB2 backup database command.
rem
set DB2INSTANCE=DB2
set DB2DATABASE=SAMPLE
title Starting database backup of %DB2DATABASE% on %date% at %time%...
DB2CMD.EXE -c -w -i DB2 BACKUP DATABASE %DB2DATABASE%
if not %errorlevel% == 0 (
echo Database backup of %DB2DATABASE% failed,RC=%errorlevel% )
else (
echo Database backup of %DB2DATABASE% completed on %date% at %time%.
)

The advantage of executing a DB2 command window from within the shell script is that you do not have to ensure that the shell script is executed from within a DB2 command window. The disadvantage of this method is that you can only run a single DB2 command for each invocation of the DB2 command window. You can get around this limitation by placing several DB2 commands in a separate DB2 script and execute the DB2 script using the -tf switch as shown in Listing 4.

Listing 4 is also a Windows shell script to back up the sample database using the DB2 CLP. This script does not need to execute inside a DB2 command window because it invokes a DB2 command window and then passes it the DB2 script (db2backup.db2) that contains multiple DB2 commands, including the backup database command (shown in Listing 5).

Listing 4. DB2 backup script invoked from a Windows shell (db2backup3.bat)

@echo off rem rem This is a Windows Shell Script
that invokes a DB2 Command Window that rem performs a
database backup by calling the DB2 backup database
command rem located in a db2 script file
(db2backup.db2).
rem
set DB2INSTANCE=DB2
set DB2DATABASE=SAMPLE

title Starting database backup of %DB2DATABASE% on
%date% at %time%... DB2CMD.EXE -c -w -i DB2 -tf
db2backup.db2 -l db2backup.log -r db2backup.rpt
if no %errorlevel% == 0 (
echo
Database backup of %DB2DATABASE% failed, RC=%errorlevel% )
else (
echo
Database backup of %DB2DATABASE% completed on %date% at %time%.
)

Listing 5 shows the DB2 script that is executed by the Windows shell script in Listing 4.

Listing 5. DB2 backup script (db2backup.db2)

-- --
This is a sample DB2 Backup Script
--
ATTACH TO DB2;
BACKUP DATABASE SAMPLE;
DETACH;
TERMINATE;

Running a Wshell script from a DB2 command window

The advantage of executing a Windows shell script from within a DB2 command window is that you can invoke several DB2 commands within the shell script and do not have to place them in a separate script. This gives you better execution control over each individual DB2 command. The disadvantage of this method is that the execution of the shell script must be performed within a DB2 command window. You can get around this limitation by executing the DB2 command window from any scheduler, including the DB2 Task Center, and passing it the Windows shell script as follows:

db2cmd.exe -c -w -i db2backup4.bat

In this example, the scheduler will receive the final return code when the DB2 command window exits.

Listing 6 is a sample Windows shell script to back up the sample database using the DB2 backup database command. The script must be invoked from within a DB2 command window.

Listing 6. DB2 backup script invoked from within DB2 Command Window (db2backup4.bat)
@echo off:
:---------------------------------------------------------------------------::
:: This is a Windows Shell Script that must be run
inside a DB2 Command:: Window (db2cmd).
::---------------------------------------------------------------------------::
set SCRIPT_NAME=%~n0 set SCRIPT_VER=1.0 set
DB2INSTANCE=DB2 set DB2DATABASE=SAMPLE

title %SCRIPT_NAME% v%SCRIPT_VER%

echo Starting database backup of %DB2DATABASE% on
%date% at %time%... DB2 BACKUP DATABASE SAMPLE if not
%errorlevel% == 0 ( 
echo Database backup of
%DB2DATABASE% failed, RC=%errorlevel% 
) 
else 
( 
echo
Database backup of %DB2DATABASE% completed on %date% at
%time%. 
)

If you have installed the Windows Resource Kit, you can change the script as shown in Listing 7. This version of the script uses the logevent utility to write information and error messages to the Windows Event Log instead of standard out.

Listing 7. Sample Script from DB2 Command Window (db2backup5.bat)
@echo off
::---------------------------------------------------------------------------::
:: This is a Windows Shell Script that must be run
inside a DB2 Command :: Window (db2cmd). It uses the
logevent utility from the Windows Resource :: Kit to
log information and error messages.
::---------------------------------------------------------------------------::
set SCRIPT_NAME=%~n0 set SCRIPT_VER=1.0 set
DB2INSTANCE=DB2 set DB2DATABASE=SAMPLE

title %SCRIPT_NAME% v%SCRIPT_VER%
::---------------------------------------------------------------------------::
:: Backup Database (Requires Windows Resource Kit)
::---------------------------------------------------------------------------::
echo Starting database backup of %DB2DATABASE% on
%date% at %time%... logevent -s I -r %script_name%
"Starting database backup of %DB2DATABASE% on %date% at
%time%... "DB2 BACKUP DATABASE %DB2DATABASE% if
%errorlevel% == 0 ( 
echo Database backup of
%DB2DATABASE% failed, RC=% errorlevel% logevent -s I -r
%script_name% "Database backup of %DB2DATABASE% failed,
RC=%errorlevel% " 
) 
else (
echo Database backup of
%DB2DATABASE% completed on %date% at %time%. logevent
-s E -r %script_name% "Database backup of %DB2DATABASE%
completed on %date% at %time%." 
)

Scheduling tasks

The DB2 Task Center provides a graphical user interface for scheduling jobs. This facility has been significantly enhanced in DB2 UDB v8.1. It provides a wizard for creating new tasks that can be DB2 scripts, operating system scripts, MVS shell scripts, and JCL scripts.

As with the DB2 Command Center scripts can be imported from a file. The DB2 Task Center allows you to schedule a task to run locally or remotely on another DB2 UDB system. You can define custom return codes for task completion as well as stop the task in the event of a failure. You can define a list of contacts to be notified upon the success or failure of a task. The DB2 Task Center also allows you to group tasks in categories as shown in Figure 4, which shows we have created a DB2 Maintenance category for the sample DB2 backup script (Listing 1), which we have imported.

Figure 4. DB2 Task Center
Task Center
Task Center

On Windows the DB2 Task Center can be started by any of the following means:

  • Start -> Program -> IBM DB2 -> General Administration Tools -> Task Center
  • Entering the command db2tc from a Windows shell
  • Directly from the DB2 Control Center

You can get more information about the DB2 Task Center from Administration Made Easier: Scheduling and Automation in DB2 Universal Database.

Scripting scenarios

Now that you've learned how DB2 UDB provides the interfaces for scripting, how to access these interfaces from the Windows shell and how you can schedule scripts with the DB2 Task Center, let's take a look at some scenarios where you might use a script to automate a task. These include tasks that are not currently automated, tasks that require the combination of DB2 commands, DB2 system commands, and operating system specific commands, and/or features only available through the DB2 system commands. Let's take a look at the following examples:

Archiving the diagnostic log

One of the easiest tasks that can be automated with a simple Windows Shell script is the periodic archiving of the DB2 diagnostic log. Every DB2 instance uses the DB2 diagnostic log file to write diagnostic information related to the instance and all databases within the instance. The amount of information written to the log is controlled by the instance configuration parameter DIAGLEVEL. Setting the DIAGLEVEL to the highest level of four (4) can provide an invaluable amount of information during problem determination. However, at this level the DB2 diagnostic log can quickly grow to over a gigabyte in size.

Currently DB2 does not provide any facility to automatically archive or prune the DB2 diagnostic log. One strategy for managing the DB2 diagnostic log file is to periodically archive the DB2 diagnostic log and keep these archives readily available for a number of days. After a given number of days you can simply delete the archive, because it will have already been backed up to tape during periodic system backups. This task can easily be accomplished using a Windows shell script as shown in Listing 8.

Listing 8. DB2 Archive Diagnostic Log script (db2arcdiag.bat)
@echo off
::---------------------------------------------------------------------------::
:: This is a Windows Shell Script to archive the db2
diagnostic log file. It :: assumes the db2diag.log file
is in the default diagnostic log directory :: and
renames it to db2diag.YYYYMMDD.log It also deletes all
but the last :: seven (7) of these diagnostic log
archives.
::---------------------------------------------------------------------------::
set script_name=%~n0 set script_log=%~n0.log set
script_ver=1.0 set DB2INSTANCE=DB2 set
DB2DATABASE=SAMPLE

title %script_name% v%script_ver% echo %script_name%
v%script_ver% started on %date% at %time%


::---------------------------------------------------------------------------::
:: Set Script Date Variable
::---------------------------------------------------------------------------::
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
set script_date=%%l%%j%%k 
)


::---------------------------------------------------------------------------::
:: Set DB2 Path Variable
::---------------------------------------------------------------------------::
if not defined db2path ( 
for /F %%i in ('db2set db2path') do (set db2path=%%i)
echo %script_name% v%script_ver% set db2path as %db2path% )
				

set db2diag_path=%db2path%\%db2instance% 
echo
%script_name% v%script_ver% set db2diag_path to
%db2diag_path%

set db2diag_file=%db2diag_path%\db2diag.log 
echo
%script_name% v%script_ver% set db2diag_file to
%db2diag_file%


::---------------------------------------------------------------------------::
:: Archive DB2 Diagnostic Log File
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% archiving
%db2diag_file% to db2diag.%script_date%.log ren
%db2diag_file% db2diag.%script_date%.log 2>
%script_log% if not %errorlevel% == 0 ( echo ERROR:
Unable to archive %db2diag_file% to
db2diag.%script_date%.log, RC=%errorlevel% type
%script_log% )


::---------------------------------------------------------------------------::
:: Delete DB2 Diagnostic Log Archives 7 days or older
::---------------------------------------------------------------------------::
set db2diag_arch=%db2diag_path%\db2diag.*.log for /f
"skip=7" %%i in ('dir %db2diag_arch% /b /o:-n') do (
echo %script_name% v%script_ver% purging %%i del
%db2diag_path%\%%i 2> %script_log% if not
%errorlevel% == 0 ( echo ERROR: Unable to delete
archive %%i, RC=%errorlevel% type %script_log% ) )

echo %script_name% v%script_ver% completed on %date% at
%time%

The above Windows shell script assumes the db2diag.log file is located in the default DB2 DIAGPATH and archives (renames) it to db2diag.YYYYMMDD.log based on the date the script is executed. It then deletes any db2diag.YYYYMMDD.log files that are seven days or older.

Collecting DB2 system information

Another task that can be quickly scripted using the Windows shell and DB2 commands is the collection of relevant system, instance, and database information. We can collect a snapshot of system resources, DB2 registry variables, instance and database configuration parameters, as well as database resource utilization. Having such a script always comes in handy before you start making changes to your database system.

Listing 9. DB2 Get Configuration script (db2getconf.bat)
@echo off
::---------------------------------------------------------------------------::
:: This is a Windows Shell Script to collect DB2 system
information. The :: information collected is stored in
a file name with the following format: ::
<instance>.<database>.YYYYMMDD.HHMMSS.rpt
::---------------------------------------------------------------------------::
set script_name=%~n0 set script_log=%~n0.log set
script_ver=1.0 set DB2INSTANCE=DB2 set
DB2DATABASE=SAMPLE

title %script_name% v%script_ver% echo %script_name%
v%script_ver% started on %date% at %time%


::---------------------------------------------------------------------------::
:: Set Script Date Variable
::---------------------------------------------------------------------------::
for /F "tokens=1-4 delims=/ " %%i in ('echo %date%') do
( set script_date=%%l%%j%%k )


::---------------------------------------------------------------------------::
:: Set Script Time Variable
::---------------------------------------------------------------------------::
for /F "tokens=1-4 delims=:. " %%i in ('echo %time%')
do ( set script_time=%%i%%j%%k )


::---------------------------------------------------------------------------::
:: Set Script Report File
::---------------------------------------------------------------------------::
set
script_rpt=%db2instance%.%db2database%.%script_date%.%script_time%.rpt


::---------------------------------------------------------------------------::
:: Collect DB2 Services Configuration (Requires Windows
Resource Kit)
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% collecting DB2
Services configuration... echo DB2 Services
Configuration > %script_rpt% srvinfo | find "DB2"
>> %script_rpt% 2> %script_log% if not
%errorlevel% == 0 ( echo ERROR: Unable to collect DB2
Services data, RC=%errorlevel% type %script_log% )


::---------------------------------------------------------------------------::
:: Collect DB2 Registry Configuration
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% collecting DB2
Registry configuration... echo DB2 Registry
Configuration >> %script_rpt% db2set -all
>> %script_rpt% 2> %script_log% if not
%errorlevel% == 0 ( echo ERROR: Unable to collect DB2
Registry data, RC=%errorlevel% type %script_log% )


::---------------------------------------------------------------------------::
:: Collect DB2 Database Administration Server
(DB2DAS00) Configuration
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% collecting DB2DAS00
instance configuration... db2 get admin cfg >>
%script_rpt% 2> %script_log% if not %errorlevel% ==
0 ( echo ERROR: Unable to collect DB2DAS00 instance
data, RC=%errorlevel% type %script_log% )


::---------------------------------------------------------------------------::
:: Collect DB2 Instance Configuration
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% collecting
%DB2INSTANCE% instance configuration... db2 get dbm cfg
>> %script_rpt% 2> %script_log% if not
%errorlevel% == 0 ( echo ERROR: Unable to collect
%DB2INSTANCE% instance data, RC=%errorlevel% type
%script_log% )


::---------------------------------------------------------------------------::
:: Collect DB2 Database Configuration
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% collecting
%DB2DATABASE% database configuration... db2 get db cfg
for sample >> %script_rpt% 2> %script_log% if
not %errorlevel% == 0 ( echo ERROR: Unable to collect
%DB2DATABASE% database data, RC=%errorlevel% type
%script_log% )

db2 connect to %DB2DATABASE% if not %errorlevel% == 0 (
echo ERROR: Unable to connect to %DB2DATABASE%
database, RC=%errorlevel% type %script_log% )

db2 "select BUFFERPOOLID, BPNAME, NPAGES from
SYSCAT.BUFFERPOOLS" >> %script_rpt% 2>
%script_log% if not %errorlevel% == 0 ( echo ERROR:
Unable to collect %DB2DATABASE% database bufferpool
data, RC=%errorlevel% type %script_log% )

db2 list tablespaces show detail >> %script_rpt%
2> %script_log% if not %errorlevel% == 0 ( echo
ERROR: Unable to collect %DB2DATABASE% database
tablespace data, RC=%errorlevel% type %script_log% )

db2 connect reset if not %errorlevel% == 0 ( echo
ERROR: Unable to reset connection from %DB2DATABASE%
database, RC=%errorlevel% type %script_log% )

echo %script_name% v%script_ver% completed on %date% at
%time%

You might even store this information in tables within your database so that this information is saved along with the database backup image. Maintaining a periodic history of this system information provides an invaluable asset that can be used for future capacity planning.

DB2 security auditing

Now let's take a look at a good example of a DB2 system command that can be very useful when automated with a scripting language. The DB2 Audit Facility is a utility that can be used to substantially enhance DB2 instance and database security auditing. It is completely managed and controlled by a DB2 system command called db2audit.exe. This command enables you to completely implement very strong DB2 UDB security auditing practices. You can configure, start, and stop security auditing as well as flush and extract audit data from the facility. This process would be a very time consuming day-to-day task, but it can easily be automated with a scripting language. For example, after you have configured and started the DB2 Audit Facility, you might want to create and schedule a script to periodically flush the audit log and extract the audit data into a report file.

Listing 10. DB2 Get Auditing Report script (db2getaudit.bat)
@echo off
::---------------------------------------------------------------------------::
:: This is a Windows Shell Script to collect DB2 Audit
Facility information. :: You must first start the DB2
Audit Facility (db2audit start) and optionally ::
configure scope and status (db2audit configure scope
all status both). It :: flushes and extracts the audit
data to the default DB2 Audit Facility :: directory
(\sqllib\<instance>\security\ and then and
renames the extracted :: files to
db2audit.YYYYDDMM.HHMMSS.rpt and prunes the DB2 Audit
Log.
::---------------------------------------------------------------------------::
set script_name=%~n0 set script_log=%~n0.log set
script_ver=1.0 set DB2INSTANCE=DB2 set
DB2DATABASE=SAMPLE

title %script_name% v%script_ver% echo %script_name%
v%script_ver% started on %date% at %time%


::---------------------------------------------------------------------------::
:: Set Script Date Variable
::---------------------------------------------------------------------------::
for /F "tokens=1-4 delims=/ " %%i in ('echo %date%') do
( set script_date=%%l%%j%%k set prune_date=%%l%%j%%k )


::---------------------------------------------------------------------------::
:: Set Script Time Variable
::---------------------------------------------------------------------------::
for /F "tokens=1-4 delims=:. " %%i in ('echo %time%')
do ( set script_time=%%i%%j%%k set prune_hour=%%i )


::---------------------------------------------------------------------------::
:: Set DB2 Path Variable, DB2 Audit Path, and DB2 Audit
Log
::---------------------------------------------------------------------------::
if not defined db2path ( for /F %%i in ('db2set
db2path') do (set db2path=%%i) echo %script_name%
v%script_ver% set db2path to %db2path% )

set db2audit_path=%db2path%\%db2instance%\SECURITY echo
%script_name% v%script_ver% set db2audit_path to
%db2audit_path%

set db2audit_file=%db2audit_path%\db2audit.log echo
%script_name% v%script_ver% set db2audit_log to
%db2audit_file%


::---------------------------------------------------------------------------::
:: Flush DB2 Audit Data to Log File
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% flushing data to
db2audit.log... db2audit.exe flush 2> %script_log%
if not %errorlevel% == 0 ( echo ERROR: Unable to flush
db2 audit data to log, RC=%errorlevel% type
%script_log% )


::---------------------------------------------------------------------------::
:: Extract DB2 Audit Data from Log File
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% extracting data from
db2audit.log... db2audit.exe extract 2> %script_log%
if not %errorlevel% == 0 ( echo ERROR: Unable to
extract db2 audit data from log, RC=%errorlevel% type
%script_log% )


::---------------------------------------------------------------------------::
:: Rename DB2 Audit Out File
(db2audit.YYYYMMDD.HHMMSS.rpt)
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% renaming db2audit.out
file... ren %db2audit_path%\*.out
*.%script_date%.%script_time%.rpt 2> %script_log% if
not %errorlevel% == 0 ( echo ERROR: Unable to rename
db2 audit out file, RC=%errorlevel% type %script_log% )


::---------------------------------------------------------------------------::
:: Prune DB2 Audit Log File
::---------------------------------------------------------------------------::
echo %script_name% v%script_ver% pruning db2audit.log
file... db2audit.exe prune date
%prune_date%%prune_hour% 2> %script_log% if not
%errorlevel% == 0 ( echo ERROR: Unable to prune db2
audit file, RC=%errorlevel% type %script_log% )

echo %script_name% v%script_ver% completed on %date% at
%time% </instance>

This data can then be imported or loaded into a security audit database that can automatically forward alert notifications via triggers and/or stored procedures to the appropriate administrators.

Conclusion

In this article we have seen how all the administrative tasks required to properly maintain a DB2 UDB system rely on the DB2 administrative APIs and how the DB2 Command Line Tools and DB2 system commands can be used to perform database administrative tasks from a command line interface. We demonstrated how you can combine DB2 commands, DB2 system commands, and operating system command in a Windows shell scripts to automate many of these tasks.

In the second article in this series we will explore yet another scripting environment available on the Windows platform, the Windows Scripting Host. How you can combine the use of DB2 commands and DB2 system commands to develop more complex scripts with native Windows scripting languages such as Jscript and VBScript. We will also explore the DB2 Windows Management Instrumentation (WMI) provider that is new with DB2 UDB v8.1.

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.


Downloadable resources


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13791
ArticleTitle=An Introduction to DB2 UDB Scripting on Windows
publish-date=05022003