ALLBASE/SQL Reference Manual (36216-90216)

170 Chapter5
Concurrency Control through Locks and Isolation Levels
Understanding ALLBASE/SQL Data Access
promotes quick access to the same pages of data by different transactions, because a page
may not have to be read in from disk if it is already in the buffer.
When you issue a query, you request a specific set of rows and columns from different
tables in a database. The content of this set of rows and columns is the query result. For
every query, ALLBASE/SQL maintains a cursor, which is a pointer to a row in the query
result. A query result may be much larger than the size of available memory, so result rows
are read into your application's tuple buffer in blocks of up to 12K at a time. As your
application advances through a query result, the cursor position advances. When the
application has read the last row in the tuple buffer, a new set of rows is read in until the
end of the query result is reached.
NOTE In procedures or embedded SQL applications, you can explicitly declare and
open a cursor for each query result. In ISQL, you do not explicitly open
cursors; ALLBASE/SQL maintains the pointer position for you.
For unsorted queries, the tuple buffer is filled with rows of data taken from pages found in
the data buffer. Of course, the tuples in the query result are a subset of the content of each
data page. In other words, the data buffer contains everything on each data page, but the
tuple buffer contains only the columns and rows you have requested. As the cursor moves
through the tuple buffer containing the query result, additional rows must be fetched from
the data buffer. When data has been fetched from all qualifying pages in the data buffer,
additional data pages must be read into the data buffer from disk, and then additional
qualifying rows and columns must be read into the tuple buffer. In the case of sorting, the
sort output is stored in a temporary table in the SYSTEM DBEFileSet before being read
into the data buffer.