DB2 Address Space and Database Services

CS185c

Chris Pollett

Feb. 16, 2010

(These notes are based on slides which are copyright IBM)

Outline

Last day, we began to look at the components of the DBM1 address space in more detail.
We discussed at what semantic levels the RDS, DM, and BM work.
Today, we are going to look at each of these units in more detail, in particular, also considering how SQL statements get processed.

The RDS does SQL processing.
How SQL is executed depends on whether the command is a DDL, DCL, or DML command.
The first two kinds of commands are interpreted; the latter kind is compiled.
The reason for DML to go through a "compile" process, which is called "bind" in DB2 lingo, is to find an optimized way for DB2 to access the data.
DB2 deviates slightly from the traditional compile process where the outcome is an executable object code. In DB2, the result of the bind process is a section. This section is saved in a package within the DB2 directory.

The executives component is the router for all SQL requests during bind and execution time.
It is also the housekeeper of DB2: It has general services for storage acquisition, tracking, and freeing.
The executives also process the transaction operations such as COMMIT and ROLLBACK.
Finally, invocation of triggers is handled by the Executives as well.

The parser is used to analyze syntax.
In particular, it handles lexical analysis of the input SQL into a sequence of tokens.
For instance, given the character S,E,L,E,C,T the lexer subunit might return a single terminal token SELECT
It also handles the grammatical construction of a parse tree based on this sequence of tokens.
For example, seeing the sequence of tokens <COLUMN_SEQ > COMMA <COLUMN_NAME >, might add a new node in the parse tree of type <COLUMN_SEQ > and with three children <COLUMN_SEQ >, COMMA, <COLUMN_NAME > . Here tokens in <... > are nonterminals and might be parents of other tokens.
During parsing, if any syntax errors are detected, processing stops and an SQL error code is returned to the application.

Which of the following statements is true?

There is a one-to-one correspondence between the partitions of a partitioned table with an XML column and the XML table spaces associated to it.
The RDS is responsible to invoke the IRLM to do locking.
The Buffer Manager uses a deferred write strategy to minimize I/Os.

The RDS interpreter processes SQL definitional/control type of statements such as CREATE, ALTER, and DROP.
DDL statements do not go through bind time.
A DDL statement also does not go through the Structure Generation component which creates the runtime structures which we will discuss in a moment.
When a DDL statement is processed by the Interpreter component, the object is created/altered/dropped.
The DB2 catalog and directory are also updated to reflect the changes.

The authorization control function is responsible for the checking if a user can use certain data objects.
When a new data object is created, the authorization ID of the owner is recorded in the authorization tables of the catalog. The owner has full access rights to the data object created and can GRANT any or all access rights over the data object to other authorization IDs. The owner can also REVOKE any access right originally granted.
The processing of SQL control type statements (GRANT and REVOKE) is similar to the processing of definitional statements.
At run time, the CALL placed in the application program by the pre-compiler causes control to be passed to the authorization function through the executives.
The authorization control function performs validity checks on the parse tree and makes the appropriate modifications to the authorization tables of the catalog to reflect the occurrence of the GRANT or REVOKE.

After the optimizer is done with its processing, the structure generator traverses the procedural parse tree and generates an access module that is composed of a series of run-time structures.
Each run-time structure contains a function code, which identifies a specific operation, and the necessary operands to perform the function.
Examples of function code are cursor-select, nested-loop-join, sort, bind in, bind out, etc..
The structure generator is invoked both for static bind and dynamic prepare statements by the Executives.
For a static SQL statement, the run-time structures is saved as a section into a package. The content of the package is saved into the DB2 directory.

The Runtime Executor is what actually executes an SQL statement.
It processes the runtime structures in the specified order.
If packages are involved it checks if they are in the EDM pool and loads them if necessary
As it processes a section, it invokes the DM's services to get records.
Finally, it returns the results of all of its operations to the calling application.

An SQL statement is received by Executives
The Parser transforms the SQL into a parse tree
The Optimizer these does these things: Authorization, Symbol checking, Semantic checking, Access path selection
Using the results of the Optimizer, the Structure Generator generates runtime structure and returns it to Executives

The DDL statement goes through Parser for syntax checking.
Parse tree is then passed to the Interpreter to check existence and authorization.
The Interpreter invokes DM/BM to update the Catalog and Directory to reflect the newly created object.

The RDS works at the set level. When the RDS goes one level down to the Data Manager (DM), the interface is at the record level.
The Data Manager component provides physical record management and associated services to access data stored in DB2.
The Runtime Executor invokes DM to get the data services based on the runtime structure. The data service can be either fetch, update, insert, delete, etc.
The DM may further invoke other components such as Buffer Manager (BM) if the needed data is not in the buffer pool.
For a fetch request, DM evaluates the sargable (search arguments such as id='5') predicates. If the record satisfies these predicates, the result is returned back to runtime. Otherwise, another record fetch is attempted by the DM.

We'll look at each of these in turn...next day