Relational Database DB2 and Implementation Issues

Click here for audio-text lecture and feed it to the speech agent

RELATIONAL DATABASES


  • A relational database consists of a collection of tables.
  • All data values are atomic. No repeating groups are allowed.
  • A relational database is a pointerless database, User does not see or is made aware of pointers.
  • DB2 supports SQL (structured query language) which is both an interactive query language and a database programming language

    USER'S VIEW OF THE DB2 SYSTEM


  • DB2 supports concurrent users
  • Ensures consistent updates by locking mechanisms
  • Base table is the "real" table
  • A view is a "virtual" table
  • DB2 supports indexes (B-trees)
  • SQL Data definition functions:
    CREATE TABLE
    CREATE VIEW
    CREATE INDEX

    DROP TABLE
    DROP VIEW
    DROP INDEX

  • SQL Data manipulation functions:
    SELECT
    UPDATE
    DELETE
    INSERT


    VIEWS AND VIEWS


  • An SQL "view" is a table that does not exist, but is derived from one or more underlying base tables.
  • A user, in a given application, may be manipulating several base tables and SEVERAL views.
  • An "external view" of the database, on the other hand, is the totality of data seen by the user.
  • Therefore, SQL views are not the same as database external views.

    MAJOR SYSTEM COMPONENTS


    1. System services component
    2. Locking services component
    3. Database services component
  • Precompiler
  • Bind
  • Runtime Supervisor
  • Stored Data Manager
  • Buffer Manager


    PRECOMPILER



  • The DB2 Precompiler is a preprocessor to analyze a source program
  • SQL statements embedded in the source program such as:

    EXEC SQL SELECT CITY
    INTO :XCITY
    FROM S
    WHERE S# = 'S4';

    are replaced by CALLS to the Runtime Supervisor.
  • The SQL statements are stored in Database Request Module (DBRM), which is a stylized international representation of the original SQL statements.

    BIND


  • BIND is the optimizing database compiler.
  • BIND compiles SQL statements (stored in DBRMs) into "application plan" to be stored in System Catalog.
  • BIND attempts to optimize the access strategy.

    EXEC SQL SELECT CITY
    INTO :XCITY
    FROM S
    WHERE S# = 'S4';


    Access strategy 1: sequential scan
    Access strategy 2: use index on S#
  • The optimizer considers:
    Which tables are referenced
    How big those tables are
    What indexes exist
    How data are physically stored on disk
    The structure of WHERE conditions

    RUNTIME SUPERVISOR


  • Application program module invokes Runtime Supervisor (calls).
  • Control goes to Runtime Supervisor.
  • Supervisor retrieves application plan from Catalog, loads it into main memory and transfers control to it.

    DATA MANAGER


  • Application plan invokes the Data Manager.
  • Data Manager, like the file manager, performs operations on stored data, and passes data to application.

    BUFFER MANAGER


  • Data Manager invokes the Buffer Manager.
  • Buffer Manager, like the disk manager, performs the physical transferring of data between disk and storage.


    COMPILED VS. INTERPRETED


  • DB2 is a compiling system.
  • More efficient than interpretive system.
  • Dynamic database changes can be handled by the DB2 system automatically by recompiling the Application Plan.
    Example: If an index is dropped, the Runtime Supervisor examines the Catalog, and marks application plans dependent on this index as "invalid". The Bind (database compiler) will attempt to recompile the DBRM of this Application Plan to choose a different access strategy.
  • Important point: the source program is NOT recompiled, only the DBRM (SQL program) is recompiled to produce new Application Plan.