PL/SQL and DB2 Database Concepts

Compilers generate low-level instructions (machine code or intermediate code) that are executed by the CPU or a runtime environment like JVM. DDL (Data Definition Language) interpreters are database tools that process schema definitions like CREATE TABLE commands - they don't execute compiled code.

Referential integrity ensures relationships between tables remain consistent. Primary keys, foreign keys, and cascading actions (like ON DELETE CASCADE) are core mechanisms for this. Unique constraints ensure data uniqueness but do not inherently manage relationships between different tables.

DB2 performs authorization checks at runtime when the actual SQL statement is executed. BIND time is when the application plan is created and optimized, but authorization is verified later when the plan runs. 'Execution time' and 'End time' are not the correct DB2 terminology.

In database terminology, a synonym is an alternate name for a database object like a table. It can function as a view-like alias (option A) or simply as an alternative reference name (option B). It is not a separate table, primary or secondary.

Index cardinality refers to the number of distinct values in the indexed column(s). A single-column index's cardinality is the count of distinct values in that column. For multi-column (composite) indexes, it's the count of distinct value combinations. Options A and B both correctly capture this definition.

Filter factor (or selectivity) is a measure used by query optimizers to estimate the fraction of rows that satisfy a predicate. For an equality predicate on a column with a uniform distribution, it is calculated as 1 divided by the number of distinct values (NDV).

Referential integrity is a database concept that ensures relationships between tables remain consistent. It is maintained by defining a Foreign Key in a child (reference) table that must match a Primary Key in the parent (base) table, ensuring no orphaned records exist.

In DB2, PCTFREE defines the percentage of space on each page to be left empty during initial load or reorganization for future updates. FREEPAGE specifies how often a completely empty page should be left in the tablespace (e.g., every 'n' pages) to accommodate growth.

DB2 for z/OS is designed to be accessed from multiple attachment facilities. The four primary environments are TSO (Time Sharing Option), CICS (Customer Information Control System), IMS (Information Management System), and Batch (standard JCL jobs). VSAM is a file system, not an access environment.

RUNSTATS (or RUN STATISTICS) should be executed after any operation that significantly changes data distribution. This includes bulk loads, mass updates, large delete operations, and major batch inserts. Updated statistics help the query optimizer choose efficient execution plans.

In PL/SQL, every block (anonymous blocks, procedures, functions, and packages) can have its own EXCEPTION section to handle errors that occur within that specific block. This allows for modular error handling where different blocks can manage exceptions differently based on their context.

PL/SQL exception messages contain three key components: the exception type (such as NO_DATA_FOUND or VALUE_ERROR), an error code (numeric identifier like ORA-01403), and a descriptive message explaining the error. A FINALLY block is a separate construct used in some languages for cleanup code but is not part of the exception message itself in PL/SQL.

PL/SQL exceptions are categorized into three types: Named System Exceptions (pre-defined by Oracle), Unnamed System Exceptions (handled via Pragma EXCEPTION_INIT), and User-defined Exceptions (declared by the developer for specific business logic). 'Implicit' and 'Explicit' refer to how they are raised, not their type.

Named system exceptions in PL/SQL (like NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX) are predefined exceptions that don't require explicit declaration, are raised automatically when specific Oracle errors occur, and are caught by referencing their standard names in exception handlers. These are built into the PL/SQL language for common error conditions.

Pragma EXCEPTION_INIT is used to associate a user-defined exception name with a specific internal Oracle error number (ORA-nnnnn). This allows the programmer to write an explicit 'WHEN' handler for an error that doesn't have a pre-defined name in PL/SQL.

In PL/SQL, user-defined exceptions must be explicitly raised using the RAISE statement when the error condition occurs. Unlike predefined exceptions that are raised automatically by Oracle, your custom exceptions need manual triggering with RAISE exception_name;. This allows controlled error handling for application-specific conditions.

Run-time errors can originate from multiple sources including hardware failures (disk crashes, memory errors), design faults (logic errors, incorrect requirements interpretation), and coding mistakes (syntax errors, boundary violations). The most comprehensive answer recognizes that all these categories contribute to run-time error conditions.

Internal exceptions (like NO_DATA_FOUND or ZERO_DIVIDE) are raised implicitly by the Oracle runtime engine whenever a program violates a database rule. User-defined exceptions, conversely, are typically raised explicitly by the programmer using the RAISE statement.