Figure 9 is a sample Database Services Utility file that executes SQL statements. It illustrates some of the principles described so far.
| Note: | You must always end SQL statements with a semicolon. |
Figure 9. Database Services Utility Example File
*--------------------------------------------------------------------------------*
| 1 *---------------* col 72 80 |
| |--------------------- | INPUT RECORDS | --------------------------| | |
| | *---------------* | | |
| V V V |
| CONNECT MICHAEL IDENTIFIED BY MFB2901; MFB001 |
| SELECT * FROM PROJECT ORDER BY PROJNO; MFB002 |
| INSERT INTO PROJECT (PROJNO, PROJNAME, MFB003 |
| DEPTNO) VALUES MFB004 |
| ('AD3101','PERSONNEL SERVICES','D01'); MFB005 |
| INSERT INTO PROJECT (PROJNO, PROJNAME, MFB006 |
| DEPTNO) VALUES ('OP3000','USER SUPPORT', MFB007 |
| 'E01'); MFB008 |
| SELECT EMPNO,WORKDEPT,EDLEVEL MFB009 |
| FROM EMPLOYEE MFB010 |
| WHERE EDLEVEL > 12 MFB011 |
| ORDER BY WORKDEPT; MFB012 |
*--------------------------------------------------------------------------------*
The Database Services Utility supports the SQL CONNECT statement so that you can:
You can use the CONNECT statement to identify yourself as a particular DB2 Server for VSE & VM user for the current application server. To identify yourself to the database manager, enter the following:
CONNECT authorization-id IDENTIFIED BY password;
where authorization-id is either your SQL identifier (if you have one) or your user ID, and password is your database-access password.
|A VM application requestor cannot use the authorization-id |IDENTIFIED BY password clause on a non-DB2 Server for VM application server or |if you are using DRDA flow. If you use the clause while using DRDA |flow, an error occurs and the previous connection remains intact.
The ID specified in the last CONNECT statement processed by the database manager is the user ID on which the database manager bases its authorization checking for all subsequent Database Services Utility processing.
You can use the CONNECT statement to identify yourself as a user of another application server. To do so, use:
CONNECT authorization-id IDENTIFIED BY password TO server-name;
where authorization-id is your SQL identifier, password is your database-access password, and server-name is the name of the target application server.
Note that you must supply an SQL CONNECT statement in the DB2 Server for VSE input control card file before any other SQL or Database Services Utility command, unless you invoke the Database Services Utility from an application program that has already executed an SQL CONNECT. ( Using the Database Services Utility from Programming Languages describes how to invoke the Database Services Utility from an application program.) Refer to the DB2 Server for VSE & VM Database Administration manual for additional information on SQL CONNECT processing.
|DRDA flow does not support the authorization-id IDENTIFIED |BY password clause.
Suppose you have ACTIVITY tables in two databases, RDB1 and RDB2. To query both of them, type in your control file:
CONNECT TO RDB1; SELECT * FROM ACTIVITY; COMMIT; CONNECT authorization-id IDENTIFIED BY password TO RDB2; SELECT * FROM ACTIVITY;
You would replace authorization-id with your SQL identifier and replace password with your database-access password.
To use the SQL CONNECT statement to switch to another application server, type:
CONNECT TO server-name;
where server-name is the name of the application server to which you want to connect.
| DB2 Server for VSE |
|---|
|
If the CONNECT statement is issued without the identify clause (for example, CONNECT TO RDB1), and the previous LUW ends with a COMMIT or ROLLBACK statement, you are connected to the application server with the same user ID and password that was used in the previous LUW. |
| DB2 Server for VM |
|---|
|
If a CONNECT is not explicitly issued, or is issued without the identify clause (for example, CONNECT TO RDB1), the DB2 Server for VM application requester does an implicit connection when you execute your first SQL statement. The database manager uses the entry in the CMS communications directory file (COMDIR) to give you connect authorization to the application server. If the authorization ID is not resolved from the CMS COMDIR, the database manager uses the VM user ID. In some situations, the user ID received at the target application server is different from your VM user ID. For example, an entry in the CMS COMDIR might change the user ID, or the target system might change it. Refer to the DB2 Server for VSE & VM Database Administration for additional information on SQL CONNECT processing. |
When you do not specify options with the CONNECT statement, the system displays the current SQL user ID and application server name. This is a null CONNECT. To enter a null CONNECT, use:
CONNECT;
If a null CONNECT is issued before a server connection is established by a previous CONNECT statement, a blank user ID and a blank application server-name is returned. If a null CONNECT is issued before a valid user ID is established by a previous CONNECT statement, a blank user ID and the connected server-name is returned.
For further information on the CONNECT statement, refer to the DB2 Server for VSE & VM SQL Reference.
The Database Services Utility writes the results of an SQL SELECT statement (an SQL query) to the DB2 Server for VSE report (SYSLST) or the DB2 Server for VM message file (SYSPRINT).
The use of the SQL SELECT statement is often called a query because SELECT statements are the means of extracting information from a database.
The Database Services Utility automatically handles multiple-row query results; you do not have to declare a cursor. Figure 10 is an example of pseudocode showing how a query is coded in an application program to return many rows using a cursor. Figure 11 shows how the Database Services Utility handles the same multiple-row query.
Figure 10. Sample Multiple-Row Query in an Application Program
EXEC SQL DECLARE C1 CURSOR FOR
SELECT PROJNO,PROJNAME
FROM PROJECT WHERE DEPTNO = 'E21'
ORDER BY PROJNO
EXEC SQL OPEN C1
EXEC SQL FETCH C1 INTO :NUMBER, :NAME
DO WHILE (SQLCODE=0)
DISPLAY (NUMBER, NAME)
EXEC SQL FETCH C1 INTO :NUMBER, :NAME
END-DO
EXEC SQL CLOSE C1
|
SELECT PROJNO,PROJNAME
FROM PROJECT WHERE PROJNO = 'E21'
ORDER BY PROJNO;
^
|
*-------------------------------*
|Required delimiter for |
|the Database Services Utility |
*-------------------------------*
|
The Database Services Utility does not support INTO clauses. Figure 12 shows how an application program uses the INTO clause to return a single value. Figure 13 shows how to specify the same single value query in the Database Services Utility.
Figure 12. Sample Single Value Query in an Application Program
SELECT AVG(BONUS) INTO :EXTRA FROM EMPLOYEE WHERE JOB = 'MANAGER' |
SELECT AVG(BONUS) FROM EMPLOYEE WHERE JOB = 'MANAGER'; |
The Database Services Utility handles logical units of work in almost the same way as application programs. Most Database Services Utility commands or SQL statements implicitly begin a logical unit of work. The following commands and statements, however, do not:
Database Services Utility Commands SQL Statements
COMMENT CONNECT
SET AUTOCOMMIT COMMIT [RELEASE]
SET ERRORMODE ROLLBACK [RELEASE]
SET FORMAT
SET ISOLATION
SET LINECOUNT
SET LINEWIDTH
SET UPDATE STATISTICS
A logical unit of work continues until you issue an SQL COMMIT statement or a ROLLBACK statement. After the COMMIT or ROLLBACK is processed, another Database Services Utility command or SQL statement begins a new logical unit of work.
If you do not include a COMMIT statement or a ROLLBACK statement, the Database Services Utility treats all the control commands as a single logical unit of work. If all processing is error-free during this single logical unit of work, the changes are committed to the database; if errors occurred, no changes are committed to the database.
The Database Services Utility provides the command SET AUTOCOMMIT ON or OFF. When AUTOCOMMIT is on, the utility performs a COMMIT operation after the successful execution of each command that accesses the database. When AUTOCOMMIT is off (the default mode of processing) or is not specified in your (input) control file, logical units of work are processed as described previously in "COMMIT".
If you want the utility to commit logical units of work, include the following in your (input) control file:
SET AUTOCOMMIT ON;
SQL comments can be included within SQL statements used in the Database Services Utility and within Database Services Utility commands wherever a separator is valid, as long as the existing Database Services Utility syntax rules are followed. SQL comments are identified by two consecutive hyphens (--) on the same line. The hyphens must not be separated by a space. SQL comments must not be part of a literal, double-byte character set (DBCS) string or quoted identifier. Each SQL comment must be contained on a single line. In the Database Services Utility, an SQL statement must be terminated by a semicolon (;). If a semicolon appears in an SQL comment, however, it does not end the SQL statement. For example,
SELECT * FROM T1 --this does not end the SQL statement; SELECT * FROM T1; -- this ends the SQL statement
The following restrictions apply when using SQL comments within Database Services Utility commands:
| Note: | The ENDDATA subcommand of the DATALOAD command should not contain any other information. |