DB2 Questions   «Prev  Next»

DB2 Cursor Stability Questions

  1. What is cursor stability?

    Answer: Here's a breakdown of cursor stability ("CS") in IBM's DB2 database management system:
    Purpose: Cursor Stability is an isolation level that governs how a cursor in your application interacts with data while a transaction is in progress. It aims to balance consistency and concurrency for read operations.
    How it works
    • Lock Scope: CS locks the "current row" being accessed by a cursor as it's being fetched. Once the cursor moves to the next row, the previous lock is released.
    • Protects Against: Cursor stability prevents the following scenarios that can lead to inconsistencies:
      • Dirty Reads: Prevents reading uncommitted changes made by other transactions while your transaction is still active.
      • Lost Updates: Prevents updates made by another transaction from overwriting your changes before you've committed.

    Trade-Offs:
    • Balance: Cursor Stability is the default isolation level in DB2 because it strikes a balance between strong read consistency and allowing concurrent updates.
    • Exceptions: Note that even with CS, other transactions might still change the underlying data after a row is read but before a planned update, affecting your update's outcome.

    Contrasting with Other Isolation Levels
    • Read Stability (RS): Offers more strict consistency but might reduce concurrency due to larger lock scope (more rows locked).
    • Uncommitted Read (UR): Allows dirty reads and various anomalies but provides the highest concurrency (least restrictive).

    Important Considerations
    • Scrollable Cursors: CS semantics can be more complex with scrollable cursors (where you can move forwards and backward within a result set).
    • Applications: Applications that require strong accuracy for read operations within a transaction often rely on the guarantees of CS.

    Cursor stability means that DB2 takes a lock on the page the cursor is accessing and releases the lock when the cursor moves to another page.
  2. What is the significance of the CURSOR WITH HOLD clause in a cursor declaration?

    Answer:
    The clause avoids closing the cursor and repositioning it to the last row processed when the cursor is reopened.
  3. What is the SQL Communications Area and what are some of its key fields?

    Answer:
    It is a data structure that must be included in any host-language program using SQL.
    It is used to pass feedback about the sql operations to the program. Fields are return codes, error messages, handling codes and warnings.
  4. What is the purpose of the WHENEVER statement?

    Answer:
    The WHENEVER statement is coded once in the host program to control program actions depending on the SQL-CODE returned by each sql statement within the program.
  5. What is the purpose of DCLGEN in IBM'S DB2 ?

    Answer:
    Here's the purpose of DCLGEN (Declarations Generator) in IBM's DB2:
    Core Function:
    • DCLGEN automates the generation of SQL table declarations (`DECLARE TABLE` statements) and the corresponding host variable structures for use in COBOL, PL/I, or C programs.
    • It eliminates the need for developers to manually write and maintain these declarations, saving time and reducing the risk of errors.

    How It Works:
    1. Source of Information: DCLGEN extracts metadata about existing tables or views from the DB2 catalog (system tables that store information about database objects).
    2. Output: Based on this metadata, it produces:
      • DECLARE TABLE Statement: A SQL statement that accurately defines the table's columns, data types, and constraints.
      • Host Variable Structures: Data structures in your chosen programming language that mirror the table's layout, allowing seamless data exchange between the program and the database.

    Key Benefits
    • Consistency: Ensures that your program's data structures always stay in sync with the database schema. Changes in the database will be automatically reflected when you regenerate the declarations.
    • Time-Saving: Eliminates tedious, repetitive, and error-prone manual coding of table and variable declarations.
    • Language Support: Supports COBOL, PL/I, and C, providing flexibility across different programming languages used to interact with DB2.

    Typical Usage Scenario
    1. Schema Changes: A database administrator modifies a table in DB2.
    2. Run DCLGEN: The developer runs DCLGEN to extract the updated table information.
    3. Recompile Application: DCLGEN's output is included in the program, and the application is recompiled to work with the new table structure.
    DCLGEN stands for declarations generator;  it is a facility to generate DB2 sql data structures in COBOL or PL/I programs.
  6. What is the FREE command?

    Answer:
    The FREE command can be used to delete plans and/or packages no longer needed.
  7. Describe the differences between the three ways DB2 can implement a join using a 1) merge join, 2)nested join or 3) hybrid join.
    Explain the differences.

    Answer:
    1. merge join: A merge join requires that the tables being joined be in a sequence; the rows are retrieved with a high cluster ratio index or are sorted by DB2.
    2. nested join: A nested join does not require a sequence and works best on joining a small number of rows. DB2 reads the outer table values and each time scans the inner table for matches.
    3. hybrid join: The hybrid join is a nested join that requires the outer table be in sequence.
  8. What is the difference between a subquery and join?

    Answer:
    Any subquery can be rewritten as a join, but not vice versa.  Joins are usually more efficient as join rows can be returned immediately, subquerys require a temporary work area for inner selects results while processing the outer select.
  9. What is the difference between IN subselects and EXISTS subselect?

    Answer:
    If there is an index on the attributes tested an IN is more efficient since DB2 uses the index for the IN.   (IN for index is the mnemonic).
  10. What is a Cartesian product?

    Answer:
    A Cartesian product results from a faulty query. It is a row in the results for every combination in the join tables.

Description of DB2 joined-table
A joined-table specifies an intermediate result table that is the result of either an inner, outer, cross, or exception join.
The table is derived by applying one of the join operators: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, LEFT EXCEPTION, RIGHT EXCEPTION, or CROSS to its operands.
Diagram for displaying INNER( LEFT, RIGHT, FULL LEFT) OUTER join
The above diagram is a syntax diagram for SQL JOIN operations in a relational database system. This diagram defines how tables in a database can be joined using different join types and conditions.
Analysis of the Diagram:**
  1. Basic Table Reference (`table-reference`):
    • The diagram starts with a `table-reference`, which refers to a table in the database system.
  2. JOIN Operations:
    • The primary structure shows different types of SQL joins that can be applied between tables.
    • The general syntax follows: 
              table-reference JOIN table-reference ON join-condition
    • The `ON` clause is used to define the `join-condition`, which specifies the relationship between the columns of the two tables.
  3. Types of Joins:
    • INNER JOIN:
      • Retrieves only the matching rows between the two tables.
    • OUTER JOIN (includes different types):
      • LEFT OUTER JOIN: Returns all rows from the left table and matching rows from the right table. If no match is found, NULLs are included.
      • RIGHT OUTER JOIN: Returns all rows from the right table and matching rows from the left table, with NULLs where there is no match.
      • FULL OUTER JOIN: Returns all rows from both tables, with NULLs for missing matches.
    • EXCEPTION JOIN (possibly a database-specific syntax):
      • This seems to be an implementation-specific join type that may be used for special filtering conditions.
    • CROSS JOIN:
      • Performs a Cartesian product, meaning each row from the first table is combined with every row from the second table.
  4. Using Clause:
    • Instead of the `ON` condition, joins can also use the `USING(column-name)` clause, which simplifies join conditions when the column names are the same in both tables.
How This Relates to Tables in a Database System**:
  • A database system consists of multiple tables that store structured data.
  • The join operations in the diagram show how these tables can be queried together to retrieve meaningful relationships.
  • The different join types allow users to select the specific way tables should be combined based on their relationship and the nature of the data stored.
Diagram for displaying INNER (LEFT, RIGHT, FULL LEFT) OUTER join

If a join operator is not specified, INNER is implicit. The order in which multiple joins are performed can affect the result. Joins can be nested within other joins. The order of processing for joins is generally from left to right, but based on the position of the required join-condition or USING clause.
Parentheses are recommended to make the order of nested joins more readable. For example: 
TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1
LEFT JOIN TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1
ON TB1.C1=TB3.C1

is the same as 
(TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1)
LEFT JOIN (TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1)
ON TB1.C1=TB3.C1

An inner join combines each row of the left table with every row of the right table keeping only the rows where the join-condition (or USING clause) is true. Thus, the result table may be missing rows from either or both of the joined tables. Outer joins include the rows produced by the inner join as well as the missing rows, depending on the type of outer join.
Exception joins include only the missing rows, depending on the type of exception join.
  1. A left outer join includes the rows from the left table that were missing from the inner join.
  2. A right outer join includes the rows from the right table that were missing from the inner join.
  3. A full outer join includes the rows from both tables that were missing from the inner join.
  4. A left exception join includes only the rows from the left table that were missing from the inner join.
  5. A right exception join includes only the rows from the right table that were missing from the inner join.
A joined table can be used in any context in which any form of the SELECT statement is used. A view or a cursor is read-only if its SELECT statement includes a joined table.

Example: Left outer join using Nested Loop
The following figure illustrates a nested loop for a left outer join. The outer join preserves the unmatched row in OUTERT with values A=10 and B=6. The same join method for an inner join differs only in discarding that row. Figure 1.6 illustrates a nested loop join.
Figure 1. Nested loop join for a left outer join
The SQL query visible in the image is as follows: 
SELECT A, B, X, Y
FROM (SELECT FROM OUTERT WHERE A = 10)
LEFT JOIN INNERT ON B = X;

Key Features in the Image:
  1. Tables:
    • OUTERT with columns A and B.
    • INNERT with columns X and Y.
  2. Join Type:
    • A Left Outer Join is performed using a nested loop join. The condition for the join is B = X.
  3. Intermediate and Final Results:
    • The subquery (SELECT FROM OUTERT WHERE A = 10) filters rows in OUTERT where column A equals 10.
    • The join results in a composite table with columns A, B, X, and Y. Matched and unmatched rows are included, with null values for Y in cases where no match exists.
  4. Resulting Composite Table:
    • The composite table contains the following rows: 
      A   B   X   Y
10  3   3   B
10  1   1   D
10  2   2   E
10  6   -   -
10  1   1   D
This image demonstrates how the left join works, combining data from two tables while ensuring that all rows from the left table (`OUTERT`) are included, even if there's no match in the right table (`INNERT`). Figure 1.6: Nested loop join for a left outer join

SEMrush Software