Non-interactive Mode Using a File as Input

The CLP can use a file containing one or more CLP commands or SQL statements and process them one after the other. This is ideal to develop DB2 database scripts. For example, Figure 4.7 shows the contents of the file myInput.txt, which we will use as input to the CLP.

To execute this DB2 script file, the -f command option (for file) followed by the filename is required to indicate to the CLP that this file contains the input. (CLP command options are described in detail in the next section.) If the input file contains a statement terminator character, the -t command option (for terminator) is required to indicate a terminator character is present.

By default, the statement terminator is a semicolon (;). If you want to use a different terminator, the -dcharacter option (for delimiter) indicates which delimiter character is being used as the terminator. Use the -v option (for verbose) to echo the command you are executing. Figure 4.8 provides an example of invoking the CLP using these command options.

N O T E The input file must be a text file. Be aware that invisible characters may cause the DB2 CLP to fail processing the file. If using the Notepad application on Windows, for example, saving the text file with Unicode encoding rather than ANSI encoding will cause this error:

DB21007E End of file reached while reading the command.

Figure 4.7 Input file to be used by the CLP

If you prefix each of the CLP commands with db2 (the CLP executable) in a file and remove the terminator characters, you are effectively converting this file into an operating system script rather than a DB2 script. Depending on the operating system, you may have to make additional modifications. For example, on Windows, you need to use rem for comments. You may also need to change the filename so that the .bat extension is used. Figure 4.9 shows this for the file myOS_Input.bat.

On Linux and UNIX platforms, use the pound sign (#) for comments. You may also need to change the permissions of the file so that it is executable. Typically you can use this command to change the file permissions:

chmod +x myOS_Input.txt

Figure 4.10 shows the same script for a Linux or UNIX platform.

N O T E DB2 scripts do not accept parameters, but operating sys-tem scripts do. In other words, if you need to invoke your scripts with parameters, you need to use operating system scripts.

Figure 4.8 Invoking the CLP in non-interactive mode using a file as input

Figure 4.9 Invoking DB2 CLP commands and SQL statements in a Windows script file

4.2.1.2 CLP Command Options

The CLP is just another program designed to interact with DB2. Like many other programs, the CLP has been designed to accept several parameter options. The CLP command list command options displays the available CLP command option parameters (see Figure 4.11).

Figure 4.10 Invoking DB2 CLP commands and SQL statements in a Linux/UNIX script file

Figure 4.11 CLP command options

To turn on an option, use a dash (-) in the command line. To turn off an option, use a plus symbol (+). Some options are on (or off) by default. For example, to enable autocommit, invoke the CLP as follows:

db2 -c insert into employee (firstnme) values (‘Raul’)

After you execute this command, a COMMIT statement is automatically issued because autocommit is enabled. (As you can see in Figure 4.11, the Auto-Commit option was already on by default, so including -c in the above example is not necessary.)

To disable autocommit, invoke the CLP as follows:

db2 +c insert into employee (firstnme) values (‘Raul’)

Note that specifying a command option in the db2 command applies only to that session of the CLP. Issuing the db2 command without an option will use the default command option values, or the ones contained in the DB2OPTIONS registry variable, which we discuss later in this section.

You can also change a command option when working with the CLP in interactive mode using the following command:

update command options using option value option value...

Figure 4.12 shows an example where the v option (verbose) is used. This option causes the command or statement to be repeated or echoed when executed as discussed earlier. In Figure 4.12, note that the SELECT * FROM department statement is echoed.

If you would like the changes to your CLP options effective across all your CLP sessions, you can set the DB2OPTIONS registry variable with the desired options. In the command:

db2set db2options="-v -z myfile.log"

Figure 4.12 The CLP in interactive mode

the DB2OPTIONS registry variable is set so that any command executed will be echoed (-v option), and the output will be spooled in the file myfile.log (-z myfile.log option).

The changes take effect immediately for the current session and for any other new CLP sessions that you start.

To reset the values to the default, issue this command:

db2set db2options=

DB2 registry variables are explained in detail in Chapter 5, Understanding the DB2 Environ-ment, DB2 Instances, and Databases.

4.2.1.3 Obtaining Help Information from the CLP

One of the most useful CLP commands is the help command represented by a question mark (?). This command provides help on SQL error codes (SQLCODE), DB2 messages, and CLP command syntax. For example:

db2 ? SQL0104N db2 ? DB21004E

db2 ? list applications

In addition, using the help command by itself displays the entire list of CLP commands, as shown in Figure 4.13.

Figure 4.14 shows other examples of the help (?) command.

4.2.1.4 Line Continuation

There are two ways to use line continuation from the CLP: with the backslash character and with the delimiter terminator character.