Control-statements are compiler-directives. Their
impact is only at compile time. The resulting compiled queries are stored in
the Query Execution Plan (QEP). At runtime this static QEP is used to access
the database.
The scope of a control-statement extends can be ended
by an other control-statement. All SQL-statements in the scope of a
control-statement are influenced by this control-statement.
Control-statements that are of importance for a
certain have to be coded bfore that query.
General control-statements (e.g. control-statements
tha are used to influence table locks) are usually coded in the first part of a
program.
In some cases a control-statement is needed to
influence only one query. For instance to control the access-path of that query
or to influence the block-buffering. It is good practive to code such a
control-statement right in front of the query that has to be influenced and to
code another neutralizing control-statement right after that query in order not
to affect the following queries.
Example:
*----------------------------------------------------------------------
S080-COUNT-REK-MULTI-OVK SECTION.
*----------------------------------------------------------------------
EXEC SQL
CONTROL
QUERY INTERACTIVE ACCESS ON
END-EXEC
EXEC SQL
SELECT
COUNT (DISTINCT MULTI_OVK_NR)
INTO
:WS-COUNT-MULTI-OVK
FROM
=OVK-EXTRACT-VIN
WHERE VIN_PRODUKT_NR = :VIN-PRODUKT-NR OF WS-OVK-EXTRACT-VIN
AND REK_NR = :REK-NR OF WS-OVK-EXTRACT-VIN
AND INTERN_REK_VOLGNR =
:INTERN-REK-VOLGNR OF WS-OVK-EXTRACT-VIN
AND
MULTI_OVK_NR > 0
STABLE
ACCESS
END-EXEC
EXEC SQL
CONTROL
QUERY INTERACTIVE ACCESS OFF
END-EXEC
*----------------------------------------------------------------------
S090-COUNT-REK-BBO SECTION.
*----------------------------------------------------------------------
EXEC SQL
SELECT
COUNT(*)
INTO
:WS-COUNT-BBO
FROM =OVK-EXTRACT-VIN
WHERE VIN_PRODUKT_NR = :VIN-PRODUKT-NR OF WS-OVK-EXTRACT-VIN
AND REK_NR = :REK-NR OF WS-OVK-EXTRACT-VIN
AND INTERN_REK_VOLGNR =
:INTERN-REK-VOLGNR OF WS-OVK-EXTRACT-VIN
AND
MULTI_OVK_NR = 0
STABLE
ACCESS
END-EXEC
BEWARE
Never use CONTROL TABLE <tablename> without
any options. This results in returning all control options for that table to
the default values. Are you sure you want to do that?
Correlation
names
The CONTROL TABLE control-statement can be used with a
correlation name. The control-statement only affects queries where that
correlation name is used.
Example (OEV is the correlation name):
EXEC SQL
CONTROL
TABLE =OVK-EXTRACT-VIN AS
ACCESS PATH INDEX =OVK-EXTRACT-VIN-I2
END-EXEC.
EXEC SQL
DECLARE
A_CURSOR CURSOR FOR
SELECT …
FROM
=OVK-EXTRACT-VIN OEV
WHERE …
REPEATABLE ACCESS IN EXCLUSIVE MODE
FOR
UPDATE OF …
END-EXEC.
Extract from explain:
Operation 1.0 :
Scan
Table : \ZT1.$DATA.OK13WDB.OVKVIN
with correlation name OEV
Access
type : Record locks, repeatable
access
Lock
mode : Chosen
by the system
Column
processing : Requires retrieval of 8 out of 71 columns
Access
path 1 : Alternate
\ZT1.$DATA.OK13WDB.OVKVINI2, path forced
SBB for
reads : Virtual
Begin key
pred. : REK_NR = :REK-NR OF WS-OVK-EXTRACT-VIN
End key
pred. : REK_NR = :REK-NR OF WS-OVK-EXTRACT-VIN
No comments:
Post a Comment