Outline

• Static SQL Statements
• Dynamic SQL Statements

Introduction

• On Thursday of last week, we were talking about DB2 extensions to SQL.
• We began by talking about transactions, and what makes a good transaction (ACID properties).
• We then talked about Stored Procedures, distinct types, user-defined functions, and triggers.
• Today, we are going to look at the complete process of executing static SQL statements, then dynamic SQL.
• As we go we will contrast these two approaches.

Sample Tables

• Our transactions are going to query the following tables:

A Sample SQL Statement

• Our transactions are going to make use of the following example SQL statement:

  SELECT empName, deptName, salary + bonus
  FROM employee E, department D
  WHERE E.deptId = D.deptId 
  AND salary+bonus > :hv_sum  
  AND deptName like 'DB2%'
  

• Notice we are allowed to use expressions in the SELECT clause.
• :hv_sum is a slot for a variable from the host language (say C) to go.
• The local predicate deptName like 'DB2%' limits the qualified rows from the department table. Since the department table has an index on the deptName column, this predicate is indexable if DB2 chooses to use the index_deptN index to access the department table.
• The WHERE class "salary+bonus" limits the qualified rows from the employee table. This predicate involves the calculation of two columns. Data Manager cannot do arithmetic. Therefore this predicate will be a stage 2 predicate evaluated by Runtime.

Embedding the Query Statically in C

EXEC SQL DECLARE CS1 CURSOR FOR
Select	empName, deptName, salary+bonus 
From	employee E, department D 
Where E.deptId=D.deptId 
and	salary + bonus > :hv_sum
and	deptName like 'DB2%'; 

EXEC SQL OPEN CS1;
//loop the next line
EXEC SQL FETCH CS1 INTO :hv1, :hv2, :hv3; 

EXEC SQL CLOSE CS1;

• Since the sample Select statement may return a set of rows, we need to code the embedded SQL in the form of a cursor in order to retrieve one row at a time from the result table.
• "Open cursor" will position cursor at the beginning of the result table.
• "Fetch" will retrieve one row and position the cursor at the next position in the result table. Fetch is looped until end of the result table.
• "Close cursor" at the end will release the DB2 resources.

Static SQL Application Pre-compile Process

• The embedded static application needs to go through the pre-compile process. This process does not require DB2 to be up and running.
• In general, a SQL statement is replaced by the pre-compiler with a call to DSNHLI passing an an argument a RDI (Relational Data Indicator).
• The RDI includes program name, consistency token, section number, call type, and addresses of input and output host variables.
• A statement like "declare cursor" is not an executable statement, so it will only be used by the pre-compiler and doesn't get converted to a function call. It will be commented out in the resulting source.
• "open", "fetch" and "close" calls though are replaced with DSNHLI calls by the pre-compiler.
• These four statements are assigned with the same section number by the pre-compiler in the bind file. After the bind, they are all mapped to the same SLTE (Section Lookup Table) entry in the package.
• The modified source should go through compile and link-edit process to create an executable load module. This process is not shown here.

Quiz

Which of the following statements is true?

1. In a subselect, the inner select is not allowed to refer to field names used in the outer select.
2. SQL - Native stored procedures need a C compiler in order to be compiled.
3. In a DB2 select statement the following two lines are equivalent:

   FROM CHECKOUTS C, MEMBERS M
   

   FROM CHECKOUTS C INNER JOIN MEMBERS M
   

Static SQL Bind Process Flow

• The SQL statements in the bind file need to be bound to DB2 before the application can execute them.
• BIND is a DB2 command to bind the bind file (or DBRM) into a package.
• The BIND command can be processed via the TSO Attach facility to enter into DB2. TSOAF will first invoke RACF to check whether the binder has the privilege to perform this bind. After this verification, the control is passed to the Executive sub-component, which is the router of the RDS component.
• We have briefly described what happens next before: The Parser is invoked first to parse the SQL syntax into a parse tree. The Optimizer will find the optimized access path and modify the parse tree to reflect the sequence of operations. Then the Structure Generator will walk the parse tree to generate the runtime structures. Finally the Executives invokes Data Manager/Buffer Manager to store the package/section into the DB2 Directory.

What the Parser does

• The parser actually has two components a lexical analyzer and a parser.
• The Lexical analyzer analyzes the input SQL statement against a predefined keywords table and a separator table. The outcome is a series of tokens. Run against our example SQL statement, "SELECT", "COMMA", and "FROM" are keyword tokens that might be found, The "C1", "C2" are "variable" tokens that could be found, etc. "variable" tokens are sometimes referred as "Identifiers".
• The series of tokens generated by the lexical analyzer may not represent the syntax of a valid SQL statement. The DB2 Parser uses a set of production rules (describing the grammar of the language) to check for syntax as well as to transform the valid token series into a parse tree. If an invalid token is detected, the DB2 parser returns with an SQL error.
• Production rules are usual described in Backus Naur form (BNF). Rules might look something like:

  select-list := select_item | select_list COMMA select_item
  

A Parse Tree is Constructed

• Here is what a parse tree for our SQL statement might look like:

Semantic Checking

• So far we have a parse tree...
• Next DB2's Query Optimizer component will perform semantic verification.
• A syntax checking only checks whether the syntax of an SQL statement follows the rules defined for that type of SQL.
• What semantic checking does is further verify that the "meaning" of the statement makes sense. For example, the "employee" in the FROM list should in fact be a table defined in the system; the "empName" in the SELECT list should in fact be a column name either from the employee or the department table, etc.
• The Optimizer uses the meta data from the DB2 Catalog to perform the semantic checking.
• After the semantic checking, the schema name is prefixed to the table names and column names as shown below in the slide. In this example, we assume the name of the schema is SCHEMA1:

  Select SCHEMA1.employee.empName, SCHEMA1.department.deptName,
  SCHEMA1. employee.salary+SCHEMA1. employee.bonus From	SCHEMA1.employee E, SCHEMA1.department D Where SCHEMA1.employee.deptId=SCHEMA1.employee.deptId
  and SCHEMA1.employee.salary +SCHEMA1.employee.bonus > :hv_sum
  and SCHEMA1.department.deptName like 'DB2%'
  

• Authorization checking is also performed to make sure the binder has the privileges for the "SELECT" operation of the employee tables and the department table.

Query Optimization

• Another job of the Optimizer is to enumerate all possible ways of accessing the data and find the cheapest plan for the execution of the query.
• Enumeration can be time consuming too. Optimizer sets a budget for the bind process. When the budget is reached, a compromise is made -- the best plan so far is selected.
• An access plan describes the access method of each table, the joining sequence and joining method of tables in a query.
• In this example, the original user query shows that employee table is the leading table of the join and the department table is the inner table of the join.
• But after analyzing the table cardinality, local predicate, and index of each table, the Optimizer may decide that it is cheaper to access the department table first, followed by nested loop join to the employee table, etc
• After the optimization process, the parse tree is transformed from non-procedural into procedural. That is, the parse tree now shows the execution sequence of DB2 operations.

Package/Section Generation

• A bind file may contain several SQL statements.
• Each SQL statement is assigned a section number. At the end of the day, we will have a package with each of the sections associated to these section numbers.
• As a Cursor is designed for applications to manipulate a result set of a query, Cursor-related statements are tied together as a section by the name of the cursor.
• In our running SQL example, we have four statements (declare cursor, open, fetch, and close) which are all mapped to the same section number by the pre-compiler.
• Only the "declare cursor" statement is prepared in this case. The rest of the same-section statement are executable statements only.
• After the Optimization, a "code generation" (Structure generation) component navigates the procedural tree and generates the optimized path execution form for this section.
• A section contains many runtime structures, each one describing its operation, the operand, and the next structure to operate on. Some structures may also contain the link to its open chain, or post-processing chain.
• In our running SQL example, the first structure is the "table scan" of the department table. This structure has a do-at-open structure, which binds in the host variables. The second structure is the Nest-loop-join (NLJ) of the employee table, which is accessed by the index of Index_deptID. The NLJ structure has a post processing structure, which evaluates the residual predicate of "salary + bonus > :hv_sum".
• The content of the section structures is relocated. In the relocation process, all address pointers are change to offsets.
• If there is more than one section in the bind file, the entire preparation process is repeated for each section.
• Finally the package is saved into the DB2 directory

Static SQL Execution -- Application Call

• Notice the section numbers are in the order the pre-compiler read the input file.
• The execution sequence of sections does not necessary follow the order of the section numbers!

Static SQL Execution -- The Loading of Plans and Packages

• When the application invokes its first SQL statement, "open cursor" in this sample example, the input host variable (hv_sum) value is moved to its place holder in the call DSNHLI's argument - this code logic is injected by the pre-compile process and the control flows to DSNHLI entry point.
• Depending on which DB2 attachment facility this application is linked with, the control will flow into the associated attachment facility.
• The attachment facility will first verify using the DB2 Catalog that the user has execution privileges on the package. If so, the attachment facility will create a thread to keep track of the SQL operations for this application instance. Then the control will pass to the Executives sub-component of RDS.
• DB2 caches frequently used plans, packages, and sections in its EDM pools to minimize the disk access to the DB2 Directory. Executives checks whether the requesting plan/packages/sections are in the cache. If not, a copy is retrieved from the DB2 Directory into the cache and a thread private copy of the section is made for the SQL operation of this application instance.

Open Cursor

• Let's now look at what happens during an open cursor...
• In our example, the first runtime structure has a do-at-open chain, which performs a "binding-in" of the input host variables. This causes the value of "hv_sum" to be passed in by application as a call argument to DSNHLI in the "OPEN" statement.
• If a sort is involved to support the return order, the rows of the result table are derived during the execution of the OPEN statement and a temporary copy of a result table is saved as a work file. In this case, the OPEN can take a long time to process. Otherwise, the result table rows are derived during the execution of each FETCH statement.
• If there are special registers, such as CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP, involved in the prepared section, the special registers values are determined and fixed for the query at the OPEN time even though the derivation of the result table is performed at the later FETCH time.
• In our example, at OPEN call, the only thing accomplished by DB2 for this statement is the bind in of input host variables.
• Since the OPEN is the first executable SQL statement in this sample application, the OPEN call also triggers the DB2 attach facility to connect to DB2, authenticate the user, create a thread and then branch into RDS to check user's execute privilege for the package, and then load in the package/section into EDM pool, if it's not already there.

Fetch and Close

Now let's look at how FETCH and CLOSE get executed.

• If the result table derived at OPEN,
  • FETCH reads a row and returns it to the application
  • FETCH returns SQLSTATE='02000' on end-of-file
• If the result table was not derived at OPEN,
  • Each FETCH derives the next row of the result table and returns it to the application; the process is illustrated in the next slide.
  • When no more rows are to be produced for the result table, FETCH returns SQLSTATE='02000'
• The CLOSE cursor releases the DB2 resources tied to this cursor.

Deriving a Row of the Result Table

• To get a row using the above, the Runtime starts executing. It processes the first runtime structure, the SELECT RT for department table and determines we are doing this using a table scan (no index).
• The sargable predicate of "deptName like..." is represented inside this SELECT RT structure.
• The Runtime Executor then invokes the Data Manager to process a fetch row request.
• The Data Manager maps the first row into a page and requests the page to the Buffer Manager.
• If the page is not in the buffer pool, the Buffer Manager requests a synchronous I/O to bring in the page to the buffer pool, which is memory shared by all threads in the DB2 system.
• When the page is in the buffer pool, Buffer Manager returns the address of the page to Data Manager.
• DM evaluates the sargable predicate. If the row qualifies it goes onto the next step otherwise, it repeats the above until it gets a qualifying row for the department.
• Next the nested loop join on the employee table is done with this row. Runtime Executor will move the value of the D.deptId into the compared-to buffer of the indexable predicate of "E.deptId = D.deptId". The Runtime Executor then calls DM to retrieve a qualified row using the value of D.deptId to limit the search range by the Index Manager. There is no sargable predicate in this case.
• Once IM returns a RID in the search range, DM uses the RID to locate the table data and copies the columns data to the thread private buffers, which are allocated and chained off the NLJ RT.
• There is a post processing step for this NLJ RT. That is, the residual predicate of "salary + bonus > :hv_sum". Runtime Executor will calculates the value of salary + bonus and compared to the value of "hv_sum", which value was bound in at OPEN time.
• On the next FETCH, the Runtime Executor will continue the NLJ of the employee table with the same previous row of the department table.
• Joined result row is returned to application with each FETCH request. When the employee table is exhausted, the Runtime Executor will go back to process the SELECT RT for the department table. A next qualified row of the department table is found. And then the D.deptId value is used to process the NLJ RT. And so on, and so forth.

Dynamic SQL that uses JDBC

• As we mentioned in the "DB2 Interface" unit, a JDBC application can use the type 2 connectivity to enter a local DB2 via a RRS Attach Facility. It can also use the type 4 connectivity to communicate to a remote DB2 via a DDF server on that remote DB2.

Sample JDBC Application

con = DriverManager.getConnection (url);
PreparedStatement pstmt = con.prepareStatement(
"SELECT empName " +
"FROM employee E, department D " +
"WHERE E.deptId = D.deptId " +
"AND salary+bonus > ? " +
"AND deptName like 'DB2%' " );

pstmt.setFloat(1,75000);

rs = pstmt.executeQuery();

while(rs.next()) {
  empName = rs.getString(1);
  System.out.println("EmpName=" + empName) ;
}

rs.close();
pstmt.close();
con.close();

• Suppose a Type 2 driver is used.
• The JDBC Type 2 driver makes a connection to DB2 via RRS Attach Facility on behalf of the application. The user's authorizations for accessing DB2 are verified. A DB2 thread is created.
• JDBC driver issues the "prepare" statement on behalf of the application. The request will first enter RRSAF to verify the user's privilege to prepare the statement and then will flow into RDS for a dynamic prepare. The dynamic prepare is very similar to a static bind. The statement will be parsed by the Parser. The user's SELECT privileges against the tables are verified. The parse tree will be optimized to reflect the access methods and the join sequence by the Optimizer. The executable form of the query is saved in thread private storage.
• If the dynamic statement cache is active for the DB2 and the statement prepared is found in the cache, then the "prepare" process is skipped and a copy of its executable form is copied to thread private storage.
• Then the value of the input host variable ? (75000) is passed to the JDBC driver
• When the executeQuery is carried out, the JDBC driver will issue an "OPEN cursor" request passing the ? (75000) value. This request flows through RRSAF to DB2. Runtime Executor will bind in the value.
• JDBC driver will issue a "FETCH" request. This request flows through RRSAF to DB2. The rest of the flow is the same as the static "FETCH" process. When the end of the result table is reached, JDBC driver is returned with SQLSTATE of '02000'. JDBC driver returns null pointer to rs.next();
• Then the various closes are done.