GUI Tools

Modifying the statement termination character

Starting with DB2 Universal Database (UDB) Version 8.2 FixPak 1 (equivalent to Version 8.1 FixPak 8), you can modify the statement termination character within a script that is being run in the command line processor (CLP) or the Command Editor. This on the fly modification is similar to the method currently available in DB2 UDB for OS/390. The following example shows how the termination character can be changed after each statement:

   connect to gilroy user newton using password;
   select * from newton.department;
   --#SET TERMINATOR :
   select * from newton.employee:
   --#SET TERMINATOR @
   select * from newton.department@
   --#SET TERMINATOR ;
   select * from newton.department;
   --#SET TERMINATOR &
   terminate&

The ability to change the termination character is important when a script contains compound statements. In the following example, DB2 UDB assumes that the first ; that it encounters in the compound CREATE TRIGGER statement is the termination character for the whole CREATE TRIGGER statement. However, this is not the case. It is only meant to be the termination character for one of the statements inside the compound CREATE TRIGGER statement.

  
   CONNECT TO SAMPLE;
   DROP TRIGGER newton.NWTTRIGGER;
   CREATE TRIGGER newton.NWTTRIGGER AFTER DELETE 
   ON newton.NWTTABLE FOR EACH ROW MODE DB2SQL 
   BEGIN ATOMIC
      insert into newton.nwttable values(0,'0');
      insert into newton.nwttable values( -1, '-1');
   END;
   CONNECT RESET;
   TERMINATE;

The following example shows how the statement termination character can be modified within the script to achieve the desired results.

   CONNECT TO SAMPLE;
   DROP TRIGGER newton.NWTTRIGGER;
   --#SET TERMINATOR @
   CREATE TRIGGER newton.NWTTRIGGER AFTER DELETE 
   ON newton.NWTTABLE FOR EACH ROW MODE DB2SQL 
   BEGIN ATOMIC
      insert into newton.nwttable values(0,'0');
      insert into newton.nwttable values( -1, '-1');
   END@
   --#SET TERMINATOR ;
   CONNECT RESET;

If you do not need your scripts to run locally on DB2 for OS/390 or your DB2 UDB scripts do not connect to OS/390, then using --#SET TERMINATOR is not the recommended method for modifying statement termination characters. Instead you should use the existing -tdX or ;-- options.

The -tdX option allows you to specify the termination character when calling a script using a CLP command. The 'X' represents the character being used as the statement termination character. For example, in the command:

   db2 -tvf test.txt -td&

The & will be used as the statement termination character when running the script in the test.txt file. If that script contained the compound CREATE TRIGGER statement, it would be written as:

   CONNECT TO SAMPLE&
   DROP TRIGGER newton.NWTTRIGGER&
   CREATE TRIGGER newton.NWTTRIGGER AFTER DELETE 
   ON newton.NWTTABLE FOR EACH ROW MODE DB2SQL 
   BEGIN ATOMIC
      insert into newton.nwttable values(0,'0');
      insert into newton.nwttable values( -1, '-1');
   END&
   CONNECT RESET&
   TERMINATE&

The script containing the compound CREATE TRIGGER statement can also be rewritten using the ;-- option as follows:

   CONNECT TO SAMPLE;
   DROP TRIGGER newton.NWTTRIGGER;
   CREATE TRIGGER newton.NWTTRIGGER AFTER DELETE 
   ON newton.NWTTABLE FOR EACH ROW MODE DB2SQL 
   BEGIN ATOMIC
      insert into newton.nwttable values(0,'0');--
      insert into newton.nwttable values( -1, '-1');--
   END;
   CONNECT RESET;
   TERMINATE;

Database unavailable status in the database details pane of the Control Center

You can use the Control Center's details pane to view information about your databases. Selecting a database in the object tree or contents pane displays a summary of its state. In certain situations database information might be unavailable. Some reasons for this unavailability are described in the following table.

Default write to table output generation (Create Event Monitor)

A Generate button has been added to the Output options dialog, which is launched from the Create Event Monitor window. Clicking the Generate button generates the default write to table output option. This output is equivalent to the syntax generated by the db2evtbl command.

The generated option shows the user which tables and data elements will be included when the event monitor is created. Users can modify the command to suit their needs.

The generated syntax is based on the event monitor name and event types specified in the Create Event Monitor window. Specify the event monitor name and event types before generating the output option syntax.

If the event monitor name or event types change after the generation of the output option, a message displays to remind the user to regenerate the output option before creating the event monitor. If the output option is not regenerated, event tables will be generated based on the event monitor name that was previously specified.