DB2 UDB for iSeries Considerations

This section covers specific iSeries issues related to cursors.

DECLARE CURSOR Statement in DB2 UDB for iSeries

The DECLARE CURSOR statement in DB2 UDB for iSeries allows for additional options:

• Specifying cursor sensitivity

• Specifying cursor scrollability

• Explicit specification for not returning a cursor

Figure 5.32 shows the complete syntax diagram for the statement in iSeries.

Figure 5.32. The DECLARE CURSOR statement in DB2 UDB for iSeries.

                           .--ASENSITIVE----------------.    .--NO SCROLL--. >>-DECLARE--cursor-name---+----------------------------+----+-------------+---->                           +--INSENSITIVE---------------+    '--SCROLL-----'                           |             .--DYNAMIC--.  |                           '--SENSITIVE--+-----------+--'              .--WITHOUT HOLD--.    .-WITHOUT RETURN----------------. >--CURSOR----+----------------+----+-------------------------------+----------->              '--WITH HOLD-----'    |              .--TO CALLER--.  |                                    '-WITH RETURN--+-------------+--'                                                   '--TO CLIENT--' >----FOR--+-select-statement-+-----------------------------------------------><           '-statement-name---' 

The sensitivity specification indicates whether the cursor is sensitive to changes made to the data after the cursor is opened.

• ASENSITIVE implies that if a cursor is read-only it will be INSENSITIVE; otherwise it could be SENSITIVE or INSENSITIVE.

• INSENSITIVE implies that if changes are made to the data after the cursor is opened, they will not be visible in the cursor. The cursor is read-only in this case.

• SENSITIVE implies that if changes are made to the data (by this, or other applications) after the cursor is opened, they will be visible in the cursor.

The scrollable specification indicates whether the cursor is scrollable.

• SCROLL implies that the cursor is scrollable.

• NO SCROLL implies that the cursor is not scrollable.

The WITHOUT RETURN clause is also available to explicitly state that the cursor will not be returned to the CALLER and CLIENT. This is the default behavior on iSeries.