Using DB2 Handles (Magic xpa 4.x)

The DB2 Gateway is written in CLI, which requires the use of handles.

Handles are data objects that contain information about a single SQL statement. The handles are allocated at the beginning and released when the SQL statement is no longer used. Each Magic xpa cursor is a SQL statement that correlates to a DB2 handle. The statement handle is allocated the first time the cursor is opened; specifically the first time Magic xpa tries to fetch records from the table or file.

The statement handle is released only when the cursor is released or dropped. This is done because the cursor can be opened more than once. Link cursors are reopened for each record fetched, while main cursors are not reopened every time you go to the next page. Reallocating the statement each time will degrade performance.

As a result, for non-resident tasks, the handle is released when the task is closed, or for resident tasks, the handle is released when you exit Magic xpa runtime.

In DB2, the open handles limit is based on the isolation level: for each isolation level there is a limit of open handles. In Magic xpa, the isolation level can be set in the database level, and tables can be mapped to a different database in Magic xpa. This will result in different handles remaining open in different isolation levels, and the number of open handles remaining higher.