In many cases, you can use certain strategies to improve
the performance of SQL statements that run on distributed systems.
Procedure
To improve SQL statements that
access distributed applications, use the following approaches:
- Commit frequently to avoid holding resources at the server.
- Avoid using several SQL statements when one well-tuned
SQL statement can retrieve the results that you want. Alternatively,
put your SQL statements in a stored procedure, issue your SQL statements
at the server through the stored procedure, and return the result.
Using a stored procedure creates only one send and receive operation
(for the CALL statement) instead of a potential send and receive operation
for each SQL statement.
Depending on how many SQL statements are
in your application, using stored procedures can significantly decrease
your elapsed time and might decrease your processor costs.
- Use the RELEASE statement and the bind option. The
RELEASE statement minimizes the network traffic that is needed to
release a remote connection at commit time. For example, if the application
has connections to several different servers, specify the RELEASE
statement when the application has completed processing for each server.
The RELEASE statement does not close cursors, release any resources,
or prevent further use of the connection until the COMMIT is issued.
It just makes the processing at COMMIT time more efficient.
The
bind option DISCONNECT(EXPLICIT) destroys all remote connections for
which RELEASE was specified.
- Consider using the COMMIT ON RETURN YES clause of the CREATE
PROCEDURE statement to indicate that DB2® should
issue an implicit COMMIT on behalf of the stored procedure upon return
from the CALL statement. Using the clause can reduce the length of
time locks are held and can reduce network traffic. With COMMIT ON
RETURN YES, any updates made by the client before calling the stored
procedure are committed with the stored procedure changes.
- Set the value of the CURRENT RULES special register to DB2. When requesting
LOB data, set the CURRENT RULES special register to DB2 instead
of to STD before performing a CONNECT. A value of DB2, which is the default, can offer performance
advantages. When a DB2 for z/OS® server
receives an OPEN request for a cursor, the server uses the value in
the CURRENT RULES special register to determine whether the application
intends to switch between LOB values and LOB locator values when fetching
different rows in the cursor. If you specify a value of DB2 for CURRENT RULES, the application indicates
that the first FETCH request specifies the format for each LOB column
in the answer set and that the format does not change in a subsequent
FETCH request. However, if you set the value of CURRENT RULES to STD,
the application intends to fetch a LOB column into either a LOB locator
host variable or a LOB host variable.
Although a value of 'STD for
CURRENT RULES gives you more programming flexibility when you retrieve
LOB data, you can get better performance if you use a value of DB2. With
the STD option, the server does not block the cursor. With the DB2 option,
it might block the cursor where it is possible to do so.
