Introduction to DB2 UDB script compilation on Windows (1)

In addition to the support for the Structured Query Language Structure Query Language, SQL), IBM®DB2®Universal Database™It also provides a set of rich and extensive APIs for managing application programming interfaces ). Because all the daily tasks required to correctly maintain the DB2 UDB system depend on the DB2 Management APIs, these Apis provide a programming interface for the DB2 UDB management.

For example, DB2 Control Center (Control Center) is a Java-based graphical user interface GUI that calls DB2 Management APIs to execute DB2 system, instance, and database management tasks. DB2 UDB also provides command line tools such as DB2 CLP to execute similar tasks. These tools also call the DB2 management API.

In addition to the DB2 UDB command line tool, DB2 Universal Database also provides a rich set of system utilities called DB2 system commands. These commands are usually used to execute tasks that are not integrated into GUI tools. Usually from the operating system shell such as Windows shell-Wshell), also known as a command prompt, call DB2 UDB system commands.

You can use almost all scripting languages from simple Windows shell scripts to complex scripts written with JScript, VBScript, Object REXX, or PerlScript) combine SQL, DB2, DB2 UDB, and operating system commands to automate or schedule simple and repetitive tasks, to form more complex tasks or any DB2 management tasks that you can think.

Generally,ScriptScripting refers to a language to be interpreted. The script is created in a text file and then compiled into assembler or byte code. Windows provides several environments that can be used to execute scripting languages. The two most common environments for executing system management tasks are Windows shellWshell and Windows Scripting HostWSH ).

This article describes several script compiling interfaces that can be used for DB2 UDB on Windows. First, we will give an overview of DB2 Management APIs, DB2 command line tools, and DB2 system commands. I will introduce you to the best technology for preparing DB2 UDB scripts using Windows shell, and provide several useful examples that can be downloaded from this website.

DB2 management API
DB2 UDB for Linux and UNIX®As part of Windows, DB2 Management APIs are always included in DB2 UDB and are documented and supported. As DB2 UDB continues to provide other functions, such as splitting the image Split Mirror Images), the API set continues to expand to support these features. The sample source code programmed using these Apis is also provided together with the developer version of DB2 UDB.

DB2 management API officially supports the following programming languages:

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

As programming languages such as Java, Perl, and Visual Basic are listing a few), this list may seem quite short at first glance. However, most programming languages can call APIs written in other languages. C APIs are most widely supported. In addition to direct access to DB2 Management APIs through C, COBOL, FORTRAN, and REXX, you can also indirectly access these Apis through other methods, such as Java Native Interface and JNI ). In fact, almost all the DB2 management tools provided with DB2 UDB are written in Java, but Java is not on the brief list of programming languages officially supported by the DB2 management API. The DB2 control center uses the Java Local interface JNI developed by IBM to access these Apis.

There is another method for programming languages and scripting languages such as VBScript, Jscript, and Perl that cannot directly access the DB2 management API. As the Command line interface, DB2 CLP can use DB2 Command to provide indirect access to almost all DB2 management API functions.

DB2 UDB command line tool
DB2 UDB command line tools are components of DB2 UDB. These tools support the processing of DB2 commands and SQL statements. You can use three command line tools on Windows:

• DB2 Command Center)
• DB2 command line processor CLP)
• DB2 command window

You can clickStart-> Programs-> IBM DB2-> Command Line ToolsFind these 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 interactive mode or script batch mode, or combine the two methods. ClickStart-> Programs-> IBM DB2-> Command Line Tools-> Command CenterStart the DB2 command center.

Figure 1. DB2 Command Center

Figure 1 is a screenshot of the DB2 command center. In this example, we have imported the sample DB2 backup script shown in Listing 1. You canScriptMenu selectionImportTo import the script.

DB2 command line processor CLP)
Generally, the DB2 command line processor CLP can be used as a DB2 prompt for calling DB2 commands.DB2 =>, Just like a Windows Command PromptC:\It is used to call the same operating system commands. You can also use any DB2 command line tool to call DB2 system commands for discussion later) and operating system commands, as long as you add an exclamation point before the command!You can.

Figure 2 shows the DB2 CLP and some basic information about how to obtain DB2 Command help. ClickStart-> Programs-> IBM DB2-> Command Line Tools-> Command Line ProcessorTo start the DB2 CLP.

Figure 2. DB2 command line Processor

For more information about the new features of DB2 CLP in DB2 UDB V8.1, please read the DB2 developer field article by Paul C. Zikopoulos to customize the DB2 Universal Database Command Line processor.

DB2 command window
You can regard the DB2 command window as a DB2 shelldb2cmd.exe), Which extends the Windows shellcmd.exe) To provide support for DB2 commands and SQL statements. The commands entered in the DB2 command window must be in uppercase, lowercase, or uppercase/lowercase format.DB2. Operating system commands suchdir) Is passed directly to Windows shellcmd.exe). No exclamation point is required.!).

The main difference between the DB2 CLP and DB2 command windows is that the CLP provides a DB2 command prompt.DB2 =>The DB2 command window provides a Windows Command Prompt where both DB2 commands and operating system commands can be entered. In Windows, you can use the DB2 command window to execute the DB2 UDB script most effectively because it supports the call of SQL statements, DB2 commands, DB2 system commands, and operating system commands.

Prompt: If you try to run the DB2 command directly from Windows shell, you will get the following error message:

DB21061E Command line environment not initialized.

This indicates that this shell cannot process DB2 commands. You only need to enterdb2cmd.exeTo start the DB2 command window and initialize the command environment.

You can set several options to change the default behavior of DB2 CLP. You can enterlist command optionsCommand to obtain the list of these options, as shown in 3. The following example shows the most common options for calling DB2 scripts in the DB2 command window.

Figure 3. DB2 command window and options

For more information about how to use DB2 CLP to compile SQL and DDL statement scripts, read the DB2 developer site article by Blair Adamache, DB2 command line processor and script compilation. DB2 Command options are also documented in the DB2 UDB Command Reference.

Although a DB2 script can contain SQL statements, DB2 system commands, and operating system commands, it is only a text file with one or more DB2 commands. Although not required, it is usually a good method to form a standard DB2 script naming convention by using an appropriate file extension. Table 1 shows the most common file extensions.

Table 1. Common script file extensions

File Extension	Description
. Db2	DB2 script containing DB2 commands
. Ddl	Contains the data definition language DDL) Statement of the DB2 script
. SQL	DB2 scripts containing SQL statements

For illustration, listing 1 contains a very simple DB2 script that uses several DB2 commands to back up the sample database. You can run this script from any DB2 command line tool. All scripts used in this article can be downloaded. These scripts use the default DB2 instance DB2) and SAMPLE database SAMPLE ). Some scripts require you to install the Windows Resource Kit ).

Listing 1. backing up the sample database DB2 script db2backup. db2)

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

You can use the following syntax to execute the preceding DB2 script from the DB2 command window:

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

In the preceding example, the following DB2 command option is used to execute the DB2 script file.db2backup.db2):

-T indicates the use of semicolons by default;) terminate each command
-V indicates that each command should be displayed to the standard output.
-F indicates the command to read from the input file
-L indicates to record the command to the output file
-R indicates to save the result to the report file.

Use-lOption to record all DB2 commands to the log filedb2backup.log) And use-rOption to save the command output to the report filedb2backup.rpt), Which is always a good method. The difference between the two options is:

• -lOption to record the start and end of each command and the date and time
• -rOption to save the output of each command to a file.

These two options allow you to view the script execution summary in the report file, and view the detailed information of each command in the log file.

You can also call the DB2 command window from the Windows shell command prompt.db2cmdYou can. It has some useful switches that can be passed to it when it is called. Table 2 describes these options:

Table 2. Options in the DB2 command window

Switch	Description
-C	Run the DB2 command window and then terminate.
-W	Wait until the DB2 command window ends.
-I	Inherit from the called shell environment.
-T	Inherit the title from the shell that is called.

By calling the DB2 command window, you can execute the script in Listing 1 from any Windows shell, as shown in the following example:

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

The DB2 command window provides the return code for each command executed. Table 3 lists the return codes.

Table 3. DB2 CLP return code

Code	Description
0	The DB2 command or SQL statement is successfully executed.
1	The SELECT or FETCH statement does not return any rows.
2	DB2 command or SQL statement warning
4	DB2 command or SQL statement Error
8	Command Line processor system error

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

DB2 system commands
DB2 system commands are a set of command line utilities that you can use to execute tasks that have not been integrated into the DB2 control center or other GUI tools for various reasons. Usually from the operating system shell such as Windows Command Prompt) Call DB2 system commands, but also from the DB2 command center, DB2 task center, DB2 CLP, DB2 command window, of course, it can also be called from your usual scripting language.

Hundreds of DB2 system commands are available. SeeDB2 Command ReferenceTo obtain a complete list of these commands and detailed documentation. It is always a good way to read the DB2 UDB revision package release notes to find new DB2 system commands, because it is often done by adding new commands or enhancing existing commands through the revision package.

Table 4 is a very short list that lists some commonly used DB2 system commands that are useful in script preparation. At first glance, some of these commands may not seem like script-based commands, so they provide a description of the commands so that you can understand how to use them in the script.

Table 4. Examples of DB2 system commands

Command	Description
Db2audit	DB2 provides an audit tool to help detect unknown or unexpected access to data. This utility can be used to automate security auditing, as described in the script preparation solution.
Db2batch	Read the SQL statement from a flat file or standard input, dynamically prepare and describe the statement, and then return the result set. This DB2 command can be used in scripts designed to benchmark different instance and database configuration parameters. Before calling the utility, restart the instance and capture system information, such as the DB2 registry, instance, and database configuration.
Db2exfmt	Format the table content. This DB2 command can be used in the script designed for SQL optimization, because it can be used to automate the interpretation, extraction, and formatting of SQL statements. Using this DB2 command for scripting allows you to interpret, extract, and format a large number of SQL statements for future review.
Db2chkbk	The utility can be used to test the integrity of the backup image and determine whether the image can be restored. This utility is useful when a backup completion report is generated at the end of the database backup script.
Db2flsn	Returns the name of the Database Transaction log file, which contains the log records identified by the specified log Sn LSN. When you enable Log Retention for recovery and you are running data replication and need to determine where to start trimming transaction logs, this application is useful as part of the database transaction log trimming script.