Database Management Systems

DDL stands for Data Definition Language, which includes SQL commands that define database structures like CREATE, ALTER, DROP for tables, indexes, views, and schemas. It deals with schema definition rather than data manipulation (DML) or data control (DCL). Option D uses Link incorrectly, and B uses Description instead of Definition.

SQL Server 2005 introduced CLR integration, allowing users to create assemblies using .NET code. This was a major new feature that enabled stored procedures, functions, triggers, and types to be written in C#, VB.NET, or other .NET languages. The CREATE ASSEMBLY statement is used for this purpose.

In relational database terminology, a horizontal row in a table is called a record (or sometimes tuple). Fields refer to the vertical columns that contain attributes. Cardinality refers to the number of rows in a table.

DML stands for Data Manipulation Language, the subset of SQL used for manipulating data. It includes commands like INSERT, UPDATE, DELETE, and SELECT for querying and modifying the data stored in database tables.

UR (Uncommitted Read) isolation level provides maximum data concurrency while preventing access to uncommitted data. This is achieved by allowing transactions to read data that other transactions have modified but not yet committed, while still protecting against reading uncommitted changes. RS (Read Stability), CS (Cursor Stability), and RR (Repeatable Read) are more restrictive levels that reduce concurrency to prevent various types of phenomena like non-repeatable reads and phantom reads.

The REORG utility is specifically designed to reorganize table data, reclaiming space from deleted rows and improving access patterns by physically reordering data on disk. db2look is for DDL extraction, db2move is for data movement/export/import, and runstats is for updating table statistics used by the query optimizer. REORG is the correct tool for the stated purpose of space reclamation through data reorganization.

Repeatable Read (RR) isolation level is designed to prevent phantom reads, which during an index scan requires acquiring a table-level lock to prevent other transactions from inserting or modifying rows that would affect the scan results. Read Stability (RS) and Cursor Stability (CS) use less restrictive locking at the row or page level. Uncommitted Read (UR) acquires minimal locks. The table-level lock in RR ensures that the set of rows matching a query remains consistent throughout the transaction.

The Client Configuration Assistant (CCA) is a DB2 tool used to catalog databases, which involves registering database connection information in the client's configuration. Journal, Alert Center, and License Center are not tools for cataloging databases - they serve different administrative or monitoring purposes. Cataloging is essential for clients to locate and connect to databases on the network.

SYSCTRL authority has the privilege to create databases in DB2. SYSMAINT can perform maintenance operations but cannot create databases. DBCTRL is not a standard DB2 authority level. While SYSADM (System Administrator) has all privileges including database creation, the question specifically asks for which authority CAN create a database, and SYSCTRL is the minimal authority with this privilege. The answer key is correct.

X locks (exclusive locks) provide the most restrictive locking mode in DB2. An X lock allows only the lock owner to access the table - all other applications are blocked from any access. The UR (Uncommitted Read) isolation level allows dirty reads, but even UR applications cannot read a table locked exclusively by another transaction. The question specifically identifies X lock as preventing concurrent access while making a narrow exception for UR-level readers.

DB2 AVI Extenders is the IBM product specifically designed for storing and managing image data within DB2 databases. It provides specialized data types and functions for image storage, retrieval, and manipulation. Net.Data is a web development environment, Net Search is not a DB2 storage product, and XML Extenders and Text Extenders handle their respective data types rather than image data.

Views are virtual tables that enhance data security and access control by limiting what data users can see or modify. By creating a view on a table, administrators can grant users access only to specific rows or columns, effectively increasing control over data access. Views do not reduce memory usage (they store no data), do not reduce compilation time (queries are still compiled), and do not increase data access time (access is similar to the base table).

When a PMD (Program Module Dump) is generated after a system restart, the system automatically writes a log entry to the Trace & Error log to document this event. This log serves as the primary diagnostic record for system anomalies and restarts. The entry is not written to the system log or availability log - only the Trace & Error log captures PMD-related information.

SQL Cache Invalidation is a feature that automatically removes cached data when the underlying database data changes. This solves the problem of stale cached data by providing real-time cache updates based on database triggers, eliminating the need for fixed time-based refresh intervals.