The DB2 Server for VSE & VM product provides application programmers with statements for retrieving and manipulating data; the coding task consists of embedding these statements into the host language code. This chapter shows how to code statements that will retrieve and manipulate data for one or more rows of data in DB2 Server for VSE & VM tables. (It does not go into the details of the different host languages. For exact rules of placement, continuation, and delimiting SQL statements, see the host language appendixes.)
One of the most common tasks of an SQL application programmer is to retrieve data. This is done using the select-statement, which is a form of query that searches for rows of tables in the database that meet specified search conditions. If such rows exist, the data is retrieved and put into specified variables in the host program, where it can be used for whatever it was designed to do.
After you have written a select-statement, you code the SQL statements that define how information will be passed to your application.
You can think of the result of a select-statement as being a table having rows and columns, much like a table in the database. If only one row is returned, you can deliver the results directly into host variables specified by the SELECT INTO statement. For example, the following statement will deliver the salary of the employee with the last name of 'HAAS' into the host variable EMPSAL:
SELECT SALARY INTO :EMPSAL FROM EMPLOYEE WHERE LASTNAME='HAAS'
If more than one row is returned, you must use a cursor to fetch them one at a time. A cursor is a named control structure used by an application program to point to a specific row within an ordered set of rows.
Writing select-statements, defining cursors, and using the SELECT INTO statement are discussed in the next few sections. For a detailed definition of queries, refer to the DB2 Server for VSE & VM SQL Reference manual.
This section discusses the three forms of a query: the subselect, the fullselect, and the select-statement.
Figure 9 shows the most basic form, the subselect query.
Figure 9. Format of the Subselect
>>-| select-clause |--| from-clause |--+------------------+--+---------------------+--+-------------------+-><
'-| where-clause |-' '-| group-by-clause |-' '-| having-clause |-'
|
The subselect query retrieves the columns specified in the SELECT clause from the tables specified in the FROM clause, applies whatever restrictions the optional clauses; (WHERE, GROUP BY, and HAVING) might put on the scope of the rows selected; and presents the results in a result table, which will be called R. The rows of R are unordered. Only the SELECT clause and the FROM clause are mandatory.
An example of a subselect query is:
SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE WORKDEPT = 'E11'
Figure 10 shows the fullselect query.
Figure 10. Format of the Fullselect
.-| union |-----------.
V |
>>-----+-subselect----+--+-------------------------------------><
'-(fullselect)-'
union
|---+-UNION-----+-----------------------------------------------|
'-UNION ALL-'
|
The fullselect query is a merge of two result tables (R1 and R2) from two subselects into one final result table (R). The merging is done by the UNION operator. The rows of R are unordered. (For a description of the UNION operation, see Combining Queries into a Single Query: UNION.)
An example of a fullselect is:
SELECT EMPNO, WORKDEPT, 'EDUCATION' FROM EMPLOYEE WHERE EDLEVEL > 16 UNION ALL SELECT RESPEMP, DEPTNO, 'STAFFING' FROM PROJECT WHERE PRSTAFF > 5
By using the literal 'EDUCATION' in the first subselect and 'STAFFING' in the second, you will be able to tell from R which row was included as a result of which criterion (or query).
Figure 11 shows the select-statement.
Figure 11. Format of the Select-statement
>>-fullselect----+----------------------+----------------------><
+-| order-by-clause |--+
+-| update-clause |----+
'-+-----------------+--'
'-| with-clause |-'
|
The select-statement can optionally put the rows of R from the fullselect in order by the values of the columns identified in the ORDER BY clause. Alternatively, the select-statement can allow the rows of R to be subsequently updated in the application program, under the restriction that this only be done to those columns listed in the update-clause (FOR UPDATE OF). (This explanation excludes consideration of the preprocessor NOFOR support, which is discussed in the next chapter.) Also, the with-clause may be used to select which isolation level that is to be used by the query. This overrides any other isolation level specification.
An example of a select-statement is:
SELECT EMPNO, FIRSTNME, LASTNAME, HIREDATE FROM EMPLOYEE ORDER BY HIREDATE, LASTNAME
| Note: | In this example, the UNION operator and some of the optional clauses in the fullselect are not used. |
The distinction among these three forms of query is often quite subtle and academic. It can be useful, however, when other SQL statements specify the form of query that is allowed as part of the statement. For example, CREATE VIEW and INSERT are two statements that use the subselect. This tells you that you cannot incorporate UNION or ORDER BY in the query component of those statements.
Figure 12. Format of the SELECT clause
.-ALL------.
>>-SELECT----+----------+---+- * --------------------------+---><
'-DISTINCT-' | .-,-----------------------. |
| V | |
'----+-expression---------+--+-'
+-table_name.*-------+
+-view_name.*--------+
'-correlation_name.*-'
|
This clause is the first part of a subselect query. It consists of the keyword SELECT followed by a select-list,
which usually consists of one or more expressions. (Expressions are discussed later in this chapter.)
The following are examples of select-lists that can occur in queries to the sample tables:
SELECT EMPNO, FIRSTNME, LASTNAME SELECT EMPNO, BONUS + COMM SELECT SALARY * 1.10 SELECT 250 SELECT HIREDATE + 1 YEAR
If you specify DISTINCT immediately after SELECT, the system eliminates duplicates from the query-result. (You can use DISTINCT only once in any query.) For example, the following SELECT clause returns the set of different departments:
Similarly, the following SELECT clause returns the set of different departments and jobs:
ALL indicates that duplicates are not to be eliminated. This is the default.
SQL provides a special shorthand notation for selecting all the columns of a table:
SELECT *
For example, the following statement returns the entire row from the DEPARTMENT table for manager number 000010:
SELECT * INTO :DEPART, :NAME, :MGR, :EMPDEPT FROM DEPARTMENT WHERE MGRNO = '000010'
As a good programming practice, however, you should explicitly specify every column you want to be returned by your query. This will avoid programming errors when, for example, a new column is added to a table but your program is using SELECT * and making no provision to store the extra column value.
If you specify a constant as a select-list expression, that constant occurs in every row returned by the query. For example, the following figure shows a query that returns a constant:
An alphabetic constant, such as 'NAME IS', is always enclosed within single quotation marks (') when used in an SQL statement. A numeric constant should not be enclosed this way.
Figure 13. Format of the FROM Clause
.-,-----------------------------------------.
V |
>>-FROM--------+-table_name-+---+------------------+---+-------><
'-view_name--' '-correlation_name-'
|
This clause specifies the name of the table from which you want to retrieve data.
If you are authorized, you can access a table that is owned by someone else, by adding the name of the owner before the table_name with a period. For example, to specify the table EMPLOYEE owned by user SMITH:
FROM SMITH.EMPLOYEE
Because any number of users can define a table with the same name, you should always use fully qualified table names. This avoids confusion if you are writing a program that someone else will preprocess.
As Figure 13 indicates, multiple table names are possible, and some or all of these names can have corresponding correlation names. These aspects of the FROM clause are discussed later in this chapter.
Figure 14. Format of the WHERE Clause
>>-WHERE--search_condition------------------------------------->< |
This clause specifies your search conditions. If you do not include it, all the rows of the table will be used to calculate the expressions in the select-list. Here are some examples of WHERE clauses:
WHERE SALARY > 30000 WHERE EMPNO = :X WHERE SALARY < :R1 AND EDLEVEL = :Y
Search conditions are discussed in Constructing Search Conditions.
Figure 15. Format of the GROUP BY Clause
.-,--------------.
V |
>>-GROUP BY-------column_name---+------------------------------><
|
This clause lets you group rows with matching values in one or more columns. Here is an example of the use of the GROUP BY clause:
SELECT WORKDEPT, SUM(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT
For more information, see Grouping the Rows of a Table.
Figure 16. Format of the HAVING Clause
>>-HAVING--search_condition------------------------------------>< |
This clause specifies the conditions that must be satisfied by the group. Here is an example:
SELECT WORKDEPT, SUM(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT HAVING WORKDEPT <> 'A00'
For more information, see Grouping the Rows of a Table.
Figure 17. Format of the ORDER BY Clause
.-,-----------------------------.
V .-ASC--. |
>>-ORDER BY--------+-column_name-+---+------+--+---------------><
'-integer-----' '-DESC-'
|
This clause delivers the rows of the result table in the order specified. You can indicate order by specifying a list of column names or integers that refer to select-list items. For example, ORDER BY 3,5 denotes ordering primarily by the third item and secondarily by the fifth item in the select-list. By using integers in the ORDER BY clause, you can order the query result by a selected expression that is not a simple column name.
The following query returns results ordered by the expression SALARY + COMM:
SELECT EMPNO, SALARY+COMM FROM EMPLOYEE WHERE WORKDEPT='D11' ORDER BY 2
You cannot specify ordering by a column that is not in the select-list. For example, the following statement would fail because FIRSTNME is not in the select-list:
The optional word ASC indicates ascending order, and is the default. DESC indicates descending order. ORDER BY 2,5 DESC indicates ascending order on item 2 and descending order on item 5. Character data is ordered alphabetically, numeric data algebraically, and datetime data chronologically. Null values are sorted first in descending order, and last in ascending order. If you do not specify an ORDER BY clause, rows will be delivered in an order determined by the system.
By default, string data is sorted based on the System/390(R) collating sequence. However, the collating sequence required for certain alphabets is different from the default System/390 collating sequence. Users expect that sorted data will match the order that is culturally correct for them, and that searches on data will return the result that is correct for the sorting sequence of their language. They are at ease with only one sort order, the one used in their dictionaries, telephone directories, book indexes, and so on.
A way to accommodate special sorting requirements is to use Field Procedures. Field Procedures can be used to encode data being inserted into a column. The encoding effectively alters the collating sequence for the data in the column, enabling the special sorting requirements to be met by the System/390 collating sequence. For more information, see Using Field Procedures.
Trailing blanks in variable string (VARCHAR and VARGRAPHIC) columns do not affect the relative order of rows delivered by the ORDER BY clause. Because the system does not use the trailing blanks when it compares VARCHAR or VARGRAPHIC rows, two columns that differ only by their number of trailing blanks may not maintain their relative positions.
Figure 18. Format of the UPDATE clause
.-,--------------.
V |
>>-FOR UPDATE OF-------column_name---+-------------------------><
|
This clause is optional for static SQL if NOFOR support is specified at preprocessor time.
The update-clause (FOR UPDATE OF) tells the system that you might want to update some columns of the result table. To update with a cursor, use the WHERE CURRENT OF clause in an UPDATE statement. (See Manipulating the Cursor.) You can update only those columns that you list in the update-clause. A column can be in the update-clause without being in the select-list; therefore, you can update columns that are not explicitly retrieved by the cursor. The update-clause is not required for deletion of the current row of a cursor. Deletion with a cursor is done using the WHERE CURRENT OF clause in a DELETE statement. For an explanation of the DELETE statement, see the DB2 Server for VSE & VM SQL Reference manual.
| Note: | If you do not want to be bound by the above restriction on which columns can be updated, you simply invoke NOFOR support at preprocessor time and omit the update-clause. In this situation, the preprocessor will assist you by issuing warning or error messages if your program tries to update columns that are not in the current database. If the conditions identified by the warning messages are not corrected, unexpected error messages can subsequently occur at program run time. |
The WITH clause specifies the isolation level for the query, which overrides any other isolation level specification. For example, a statement specifying WITH UR in a package prepped with ISOL(CS) will use an isolation level of uncommitted read.
For more information on isolation levels, see Selecting the Isolation Level to Lock Data (DB2 Server for VM) or Selecting the Isolation Level to Lock Data (DB2 Server for VSE).
Figure 19. Format of the WITH clause
>>-WITH----+-RR-+----------------------------------------------><
+-CS-+
'-UR-'
|
The previous section showed how to use a select-statement to create an SQL query. You can now use that query to retrieve values into an application program from multiple rows in a table.
To do so, you must first declare an SQL cursor, which is a control structure that points to a row in a table.
The rows returned by the query are called the result table of the cursor.
A cursor can be in an open or a closed state.
In the open state, it maintains a position in its result table on a certain row (called the current row). If you delete the current row, the cursor will be positioned between the two rows that surrounded the deleted rows. If you request the next row and receive a message that there are no more rows (SQLCODE 100 and SQLSTATE '02000'), the cursor will be positioned after the last row. Before you OPEN the cursor, it is said to be positioned before the first row.
Figure 20. Format of the DECLARE CURSOR statement
>>-DECLARE--cursor_name--CURSOR FOR----+-| select-statement |-+-><
+-| insert-statement |-+
'-statement_name-------'
|
Use the DECLARE CURSOR statement to define a cursor. This statement associates a cursor_name with a specified select-statement, insert-statement, or statement-name. For example:
DECLARE C1 CURSOR FOR SELECT LASTNAME, FIRSTNME
FROM EMPLOYEE WHERE SALARY>:AMT
DECLARE C2 CURSOR FOR INSERT INTO ACTIVITY
(ACTNO, ACTKWD, ACTDESC)
VALUES (:ACT, :KEYWORD, :DESC)
| Note: | Statement-name is only used with dynamic SQL. For an explanation of its use, see Retrieving the Query Result. |
The select-statement or insert-statement is a part of the DECLARE CURSOR statement, so you must not place EXEC SQL in front of SELECT or INSERT (however, do place it in front of the DECLARE).
Your program may contain many DECLARE CURSOR statements that define different cursors and associate them with different queries. During the processing of a program, several cursors may be in the open state at one time. It is possible to define more than one cursor that operates on the same data within the same logical unit of work. It is also possible to open a cursor and then operate on the same data with a non-cursor operation such as a Searched DELETE. However, mixing these operations should be avoided, because the result of one operation can adversely affect another. For example, do not update a row using a Positioned UPDATE and subsequently delete it with another cursor operation or with a Searched DELETE.
The DECLARE CURSOR statement that defines a cursor must occur earlier in the program than any statement operating on that cursor. It does not result in any processing when the program is executed (that is, it does not automatically open the cursor).
The scope of a cursor-definition is an entire program. Therefore, cursor names must be unique within a program. You cannot have two DECLARE CURSOR statements in the same program that use the same cursor-name, even if they are in different blocks or procedures.
For additional detail on the DECLARE CURSOR statement, see the DB2 Server for VSE & VM SQL Reference manual.
After you define a cursor, you can manipulate it using the
SQL statements shown in Figure 21. (See the DB2 Server for VSE
& VM SQL Reference manual for a complete description of these statements.)
Figure 21. SQL Statements for Manipulating Cursors
|
Statements for Manipulating Query and Insert Cursors |
Statements for Manipulating Query Cursors |
Statements for Manipulating Insert Cursors |
|---|---|---|
| OPEN | FETCH | PUT |
| CLOSE | Positioned DELETE |
|
|
| Positioned UPDATE |
|
Partial Format:
>>-OPEN--cursor_name------------------------------------------->< |
If you are opening a query-cursor (a cursor defined in terms of a select-statement), this statement examines the input host variables (if any) used in the definition of the cursor, determines the result table for the cursor, and leaves it in the open state. When the system executes an OPEN statement for a query-cursor, it positions the cursor before the first row of the result table. After the query-cursor is opened, the system does not reexamine its input variables until you close and reopen the cursor. No rows in the result table are fetched to the host program until a FETCH statement is executed. Always open the cursor before issuing the first FETCH or PUT statement.
If you are opening an insert-cursor and your program is blocking, this statement prepares the system to block the rows that are to be inserted. With an insert-cursor, you can change the values of the input host variables between inserts; you do not have to close and reopen the cursor.
Partial Format:
.-,-------------------------.
V |
>>-FETCH--cursor_name--INTO----------host_variable_list----+---><
|
This statement can be executed only when the indicated cursor is in the open state. The position of the cursor is advanced to the next row of the result table, and the selected columns of this row are delivered into the output host variables referenced in the host_variable_list.
The following is an example of the FETCH statement:
A cursor can move forward only when it is in its result table; the system cannot return to rows that have already been fetched (other than closing the cursor and reopening it).
If the result table of the cursor is empty, or if all its rows have already been fetched, the system returns the not found return code (SQLCODE=100 and SQLSTATE='02000') and the cursor is positioned after the last row of the result table. To perform further operations with the cursor, you must close and reopen it.
It is possible for two or more rows in the result table to have exactly the same values. (For example, many rows of the EMPLOYEE table may have the same WORKDEPT, and you might define a cursor that selects only WORKDEPT from the table.) These duplicate values are not eliminated from the result table unless you specify DISTINCT in the SELECT clause of the DECLARE CURSOR statement.
You can use indicator variables in the INTO clause. (For a detailed discussion of indicator variables, see "Using Indicator Variables".) Each main variable in the INTO clause may, at your option, have an associated indicator variable. If a null value is returned, and you haven't provided an indicator variable, a negative SQLCODE is returned to your program and execution of the statement is halted.
Partial Format:
>>-PUT--cursor_name-------------------------------------------->< |
This statement can be executed only when the indicated cursor is in the open state. The PUT statement inserts one row of data as defined by a cursor. The contents of input host variables referenced in the host_variable_list (defined in the VALUES clause of the DECLARE CURSOR statement for insert) are delivered to the database.
For instance, the following statements insert a new row of data into the EMPLOYEE table:
DECLARE CC CURSOR FOR INSERT INTO EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, EDLEVEL) VALUES (:EMP, :FIRST, :MID, :LAST, :ED) OPEN CC PUT CC CLOSE CC
The values represented by the host variables :EMP, :FIRST, :MID, :LAST, and :ED are placed into the corresponding columns of the new row. The other columns are assigned the null value.
After the PUT statement is executed, you can assign different values to the input host variables to add another row. Alternatively, you can place constants in the VALUES clause of the DECLARE CURSOR statement instead of host variables. This causes identical values to be inserted into the related columns for each PUT.
The PUT statement is used mostly for inserting multiple rows of data into a table in groups or blocks (although, it also works with non-blocked inserts). Blocked inserts are specified with the BLOCK preprocessor parameter. If blocking is in effect, rows are not inserted until the block is full, or until a CLOSE statement is issued. For information on preprocessing your program with the BLOCK option specified, see Preprocessing the Program (DB2 Server for VM) or Preprocessing the Program (DB2 Server for VSE). For information on using the BLOCK option in DRDA protocol for DB2 Server for VM see Using the Blocking Option to Process Rows in Groups.
Partial Format:
>>-DELETE FROM--table_name--WHERE CURRENT OF--cursor_name------>< |
This statement can be executed only when the indicated cursor is in the open state and positioned on a row of the result table. It deletes that particular row from the table. The cursor itself remains where it was; it is considered to be in the between position and, cannot be used for further deletions or updates until it is repositioned by a FETCH statement.
From the example under the FETCH statement, you could delete a row from the EMPLOYEE table after doing a FETCH, by issuing:
DELETE FROM EMPLOYEE WHERE CURRENT OF QUERY1
Partial Format:
>>-UPDATE--table_name--set_clause--WHERE CURRENT OF--cursor_name--> >-------------------------------------------------------------->< |
This statement is similar to the DELETE statement, except that it updates the row of the table on which the cursor is positioned rather than deleting it, leaving the position of the cursor unchanged. When using this statement, you must specify the update-clause in the select-statement.
The following example updates the SALARY column of each fetched row of the EMPLOYEE table:
DECLARE QUERY2 CURSOR FOR SELECT LASTNAME, FIRSTNAME, MIDINITT FROM EMPLOYEE WHERE WORKDEPT = 'D21' FOR UPDATE OF SALARY OPEN QUERY2 FETCH QUERY2 INTO :LAST, :FIRST, :MID UPDATE EMPLOYEE SET SALARY = SALARY + :DELTA WHERE CURRENT OF QUERY2 CLOSE QUERY2
Format:
>>-CLOSE--cursor_variable-------------------------------------->< |
The indicated cursor leaves the open state, and its result table becomes undefined. No FETCH or PUT statement can be executed on the cursor, and no DELETE or UPDATE statement can refer to its current position until the cursor is reopened by an OPEN statement. The CLOSE statement permits the resources associated with maintaining an open cursor to be released. It should be placed in your program so that it is executed as soon as the program is finished using a cursor.
If your program is blocking, you can close an insert-cursor with an incomplete block to insert the remaining rows.
Always close a cursor before committing changes. If changes are committed before an insert cursor (that is being blocked) is closed, an error occurs.
Figure 22, which shows a fragment of pseudocode, illustrates the use of a query cursor C1. It finds the employees of all the rows of the EMPLOYEE table whose department number matches host variable DEPT. The FETCH statements retrieve the selected columns successively into host variables EMP, FNAME, and LNAME. After the results are retrieved, they are displayed on the console.
 |
Recall that SQLCODE is set to +100 (SQLSTATE '02000') when there are no rows remaining to be fetched.
The SELECT INTO statement finds the only row of the table specified in the FROM clause that satisfies the given search condition. From this row, the system selects the columns that you supplied in the select-list. The results are inserted in the host variables that you specified in the INTO clause. The data type and length attributes of the host variables must be compatible with the data type and length attributes of the expressions in the select-list. If specified, the WITH clause specifies the isolation level to be used on the query and overrides any other isolation level specification.
Figure 23. Format of the SELECT INTO statement
.-,-------------------------.
V |
>>-| select-clause |--INTO----------host_variable_list----+----->
>-----| from-clause |--+------------------+--+-----------------+-><
|
For example, the following statement selects the employee number, last name, and yearly salary from the EMPLOYEE table where the employee number is '000130'. It places the result in the host variables EMP, NAME, and PAY:
SELECT EMPNO, LASTNAME, SALARY INTO :EMP, :NAME, :PAY FROM EMPLOYEE WHERE EMPNO = '000130'
If the number of expressions in the select-list is greater than the number of output host variables in the INTO clause, a warning flag (called SQLWARN3) in the SQLCA is set to W. Also, if more than one row satisfies the search condition in a SELECT INTO statement, an error condition occurs, and the values of the host variables are unpredictable.