DB2 Server for VSE & VM: Interactive SQL Guide and Reference

Using Placeholders in SQL Statements

You can form SQL statements that contain placeholders. They reserve areas in the statements, which are filled in when the statement is performed. One reason for doing this, for example, is to avoid typing an UPDATE statement for each table update. Suppose you want to update the EMPLOYEE table to reflect a $100 bonus increase for particular employees.

1. To prevent your changes from being automatically committed, type:

      set autocommit off
   

   This starts a logical unit of work. Now type:

      hold update employee -
      set bonus = &1 -
      where empno = '&2'
      
   

   The use of HOLD at the beginning of this example places the UPDATE statement in the command buffer without processing it.

   In the example, &1 and &2 are the placeholders. The number following the ampersand refers to the sequence in which the placeholders are replaced. The database manager performs the replacement as follows: the first item of information replaces &1, the second replaces &2, and so on.

2. Start the UPDATE statement and supply the replacement information:

      start (bonus+100.00 '000010')
   

   This command adds a $100 bonus to employee 000010, and produces a message indicating that one row (ROWCOUNT=1) was updated. The actual UPDATE statement processed is:

      update employee
      set bonus = bonus + 100.00
      where empno = '000010'
   

   The two items of information which replace the placeholders are called parameters.

3. Because START is an ISQL command, it does not replace the UPDATE statement in the command buffer. This lets you continue to use the UPDATE statement to update another row as follows:

      start (bonus+100.00 '000050')
   

4. This process can continue for as many updates as needed. Remember though, that the START command uses the statement currently contained in the command buffer. If you typed another SQL statement, it would replace the UPDATE statement in the buffer, and you would have to recall the UPDATE statement to continue. (Statement recalling is discussed in Chapter 6, Storing SQL Statements.)
5. Type the following command to return to normal command processing:

      set autocommit on
   

   Respond to the resulting message with the following to prevent the changes from being committed:

      rollback
   

The following are rules for the use of placeholders and parameters:

• A placeholder for a character data item must be enclosed in single quotation marks.
• A parameter for a character data item must also be enclosed in single quotation marks.
• When using parameters in a START command, enclose them in parentheses and separate each parameter with a blank.
• A parameter may be one word, several words, or an expression.
  • If one parameter (replacing just one placeholder) consists of a list of words separated by commas, such as a list of column names in a select_list, the list of words must use commas (and not blanks) as separators.
  • If a parameter contains a list of words separated by blanks, that list of words must be enclosed in single quotation marks to distinguish between the blanks within the one parameter and the blanks that separate parameters.
• In a stored SQL command, you can only use the ampersand (&) to create placeholders.

EXERCISE 2 (Answers are in Appendix A, Answers to the Exercises, page ***.) Perform the following: Enter and hold an SQL statement that retrieves the entire DEPARTMENT table. Change the command created in step 1 to select two columns, and use placeholders to define them. Start the command, replacing the two placeholders with values that retrieve the DEPTNO and DEPTNAME columns. Check the results, and then end the display. Change the current SQL statement to add a WHERE clause. Use a placeholder for the search condition. Start the command, replacing the placeholders with values that select the department name and the manager number for departments that report to E01.