DB2 Questions   «Prev  Next»

DB2 Package and Plan Questions

  1. What is the difference between a package and a plan?
    How does one bind 2 versions of a CICS transaction with the same module name in two different CICS regions that share the same DB2 subsystem?

    Answer: Package and plan are usually used synonomously, as in this site. Within the context of IBM's DB2 database, what is the difference between a package and a plan?
    In the context of IBM's DB2 database, a package and a plan are key components used in the execution of SQL statements within application programs. While they are related, they serve distinct purposes and have different scopes. Here's a breakdown of the differences:
    1. Definition
      • Package: A package is a database object that contains the compiled, executable form of SQL statements from a specific application program. It is created during the bind process and is stored in the DB2 catalog. Each package is associated with a single source program.
      • Plan: A plan is a higher-level construct that is used by the DB2 runtime environment to execute an application program. It specifies which packages or DBRMs (Database Request Modules) can be executed and provides the runtime control information.
    2. Purpose
      • Package:
        • Optimizes and stores SQL statements for execution.
        • Supports modularity by allowing different pieces of a program to have their own packages.
        • Enables flexibility by allowing multiple packages to be updated or modified without affecting others.
      • Plan:
        • Determines which packages can be executed together during runtime.
        • Provides the entry point for executing programs in DB2.
        • Includes runtime information like buffer pool usage, access paths, and privileges.
    3. Relationship
      • A plan can include one or more packages, but a package does not directly include a plan.
      • The plan acts as a container for the packages, providing a way to group and execute related packages.
    4. Binding Process
      • Package:
        • Created by binding a single DBRM (Database Request Module), which contains SQL statements extracted during program precompilation.
        • You can rebind a package without affecting other packages or the overall plan.
      • Plan:
        • Created by binding a list of DBRMs or packages.
        • A plan must reference at least one package or DBRM.
        • Plans are less flexible than packages since modifying a plan often requires rebinding all referenced packages.
    5. Flexibility and Modularity
      • Package:
        • Introduced to improve modularity and reduce the dependency on plans.
        • Supports versioning, allowing multiple versions of a package to coexist, which is useful for application maintenance.
      • Plan:
        • Represents a broader execution context.
        • Changes to a plan typically require rebinding all associated packages, making it less modular.
    6. Execution Scope
      • Package:
        • Specific to a particular program module or SQL statement set.
        • Reusable across multiple plans.
      • Plan:
        • Tied to an application as a whole or to a set of packages used by that application.
    7. Use Case Example
      • Suppose an application consists of multiple modules, each containing SQL statements.
      • Packages are created for each module, allowing them to be individually maintained and optimized.
      • A plan is created to include all the necessary packages for the application, enabling their execution under a unified runtime control.

    Summary Table
    Aspect Package Plan
    Definition Compiled SQL for a single program module Execution container for one or more packages
    Purpose Optimize and store SQL statements Define runtime context and permissions
    Scope Module-specific Application-wide
    Creation Bind a DBRM Bind DBRMs or packages
    Flexibility More modular and maintainable Less flexible, impacts all packages
    Execution Specific to SQL modules Groups and executes packages

    This distinction makes packages ideal for modular development and maintenance, while plans serve as an overarching structure for executing an application in DB2.
    Both contain optimized code for SQL statements - a package for a single program, module or subroutine contained in the datebase request module (DBRM) library. A plan may contain multiple packages and pointers to packages. The one CICS module would then exist in a package that could be referenced in two different plans.

  2. What is an asychronous write?

    Answer: It is a write to disk that may occur before or long after a commit.
    The write is controlled by the buffer manager.
  3. Question: What is a lock within the context of the IBM DB2 Database?

    Answer:
    In the context of the IBM DB2 Database, a lock is a mechanism used to control access to database resources, such as tables, rows, or data pages, to ensure data consistency and integrity during concurrent transactions. Locks are an integral part of DB2's concurrency control strategy, enabling multiple users and applications to access and manipulate data simultaneously without causing conflicts or compromising data reliability.
    Key Aspects of Locks in DB2:
    1. Purpose of Locks
      • Prevent data anomalies, such as lost updates, dirty reads, and non-repeatable reads.
      • Ensure transaction isolation, which is a key principle of the ACID (Atomicity, Consistency, Isolation, Durability) properties of a database.
      • Maintain data consistency across concurrent transactions.
    2. Types of Locks

      DB2 supports various lock types based on the scope and level of access:

      • Shared Lock (S): Allows multiple transactions to read a resource but prevents any transaction from modifying it.
      • Exclusive Lock (X): Grants one transaction exclusive access to modify a resource, blocking others from reading or writing to it.
      • Intent Locks: Used at higher levels in the database hierarchy (e.g., table level) to indicate the intention to acquire specific locks at lower levels (e.g., row level).
        • Examples: Intent Share (IS), Intent Exclusive (IX), and Shared Intent Exclusive (SIX).
      • Update Lock (U): A temporary lock used to avoid deadlocks during update operations, initially allowing read access but convertible to an exclusive lock if a modification is performed.
    3. Granularity of Locks

      DB2 can lock resources at different granularities:

      • Row-level locking: Locks a single row in a table, providing high concurrency but with potential overhead.
      • Page-level locking: Locks a single page of data (a physical unit of storage).
      • Table-level locking: Locks the entire table, which reduces overhead but restricts concurrency.
    4. Lock Modes

      DB2 allows various lock modes depending on the operation:

      • Cursor Stability (CS): Locks the current row being read, and the lock is released as the cursor moves to the next row.
      • Read Stability (RS): Locks all rows that meet the query's criteria to ensure they remain unchanged during the transaction.
      • Repeatable Read (RR): Locks all rows that are read to prevent other transactions from modifying them.
      • Uncommitted Read (UR): No locks are acquired, allowing reading uncommitted data (dirty reads).
    5. Deadlocks and Timeouts
      • Deadlock: Occurs when two or more transactions hold locks on resources that each other needs, resulting in a cycle of dependency.
      • Timeout: Happens when a transaction waits too long for a resource lock held by another transaction.
    6. Lock Escalation

      DB2 uses lock escalation to reduce the overhead of managing a large number of fine-grained locks by converting them into a higher-level lock, such as a table-level lock.

    7. Lock Management

      DB2's lock manager coordinates the acquisition, release, and enforcement of locks to ensure optimal performance and maintain database integrity.


    Example: If Transaction A is updating a specific row in a table, DB2 places an exclusive lock (X) on that row. Other transactions attempting to access the same row must wait until Transaction A commits or rolls back, ensuring no conflicting updates occur. By using locks effectively, DB2 balances concurrency with data consistency and performance in a multi-user environment.
  4. What is meant by isolation level?

    Answer: This is a key concept for any relational database.  Isolation level is the manner in which locks are applied and released during a transaction.   For DB@ a repeatable read holds all locks untile the transaction completes or a syncpoint is issued.  For transactions using 'cursor stability' the page lock releases are issued as the cursor 'moves', i.e. as the transaction releases addressability to the records.
  5. What are leaf pages?

    Answer: They are the opposite of root pages.
    Leaf pages are the lowest level index pages - the pages that contain index entries and information to the corresponding table rows.
  6. What is a precompiler?

    Answer: It is a DB2 facility for static SQL statements - it replaces these statements with calls to the DB2 language interface module.
  7. What is a root page?

    Answer: The opposite of a leaf page; it is the highest level index page.
    An index can contain only the one root page; all other index pages are associated to the root.
  8. What is a thread?

    Answer: A thread is the connection between DB2 and some other subsystem, such as CICS or IMS/DC.

SEMrush Software