How TEMPLATE and LISTDEF of DB2 utilities works

  A detailed explanation of TEMPLATE and LISTDEF can be found at http://www.alancsmith.co.uk/db2/listdef.html

How DB2 online reorg works

Click on link http://www.alancsmith.co.uk/db2/olro/onlinero.html

When DB2 creates result table of a query and how insert, update, delete affects the result table

IBM manual does not clearly explain when it exactly creates a result table. Below information is taken from DB2 10 manual.

DB2 can process a cursor in two different ways:

• It can create a temporary copy of the result table during the execution of the OPEN statement. You can specify INSENSITIVE SCROLL on the cursor to force the use of a temporary copy of the result table.
• It can derive the result table rows as they are needed during the execution of later FETCH statements.

If the result table is not read-only, DB2 uses the latter method. If the result table is read-only, either method could be used. The results produced by these two methods could differ in the following respects:

When a temporary copy of the result table is used: Insert operations that are executed while the cursor is open cannot affect the result table once all the rows have been materialized in the temporary copy of the result table. For a scrollable insensitive cursor, update and delete operations that are executed while the cursor is open cannot affect the result table. For a scrollable sensitive static cursor, update and delete operations can affect the result table if the rows are subsequently fetched with sensitive FETCH statements.

When a temporary copy of the result table is not used: Insert, update, and delete operations that are executed while the cursor is open can affect the result table. The effect of such operations is not always predictable.