Database Management Systems

The sys.dm_exec_query_stats Dynamic Management View returns performance statistics for cached query plans. It includes the plan_generation_num column, which increments each time a query is recompiled, allowing you to identify top recompiled queries. Other views like sys.dm_exec_requests monitor active sessions.

In IBM CICS, the Program Control Table (PCT) contains definitions of all transaction identifiers and maps them to their respective initial application programs. The PPT lists programs and their locations, the TST defines temporary storage queues, and the FCT defines files and their access characteristics.

The Resource Control Table (RCT) in CICS contains DB2 interface information including transaction-to-plan bindings and security parameters. RCT defines which CICS transactions can access which DB2 plans and resources. TST manages temporary storage queues, PPT contains program and mapset definitions, and FCT controls file access for VSAM files.

Hash partitioning is a standard table partitioning method where data is distributed across partitions using a hash function on partition key values. Key-based is not a standard partitioning method name. Freelist relates to space management within segments, not partitioning. Index is a separate access structure, not a partitioning method. Common partitioning methods include range, list, hash, and composite.

DB_CREATE_FILE_DEST is the primary parameter used to enable Oracle Managed Files (OMF) by specifying the default directory for datafiles. Other parameters like USER_DUMP_DEST and LOG_ARCHIVE_DEST serve administrative purposes and do not initialize OMF for database files.

In database mirroring, the mirror database remains in a restoring/standby state and cannot be directly read or written to by users, representing idle capacity. Identical hardware is not strictly mandatory, automatic failover is supported, and a witness server is optional.

The Database Maintenance Plan Wizard is specifically designed to help DBAs create comprehensive maintenance plans including backup strategies. Database Engine Tuning Advisor is for performance tuning, SQL Server Configuration Manager is for server configuration, and the design surface requires more advanced knowledge.

The Read Uncommitted isolation level allows dirty reads because it doesn't lock shared data and can read uncommitted modifications from other transactions. Since the requirement is to avoid dirty reads while accepting reduced accuracy, Read Uncommitted is the isolation level to avoid.

Navigational database languages require programmers to write code that manually navigates data relationships, which demands precise knowledge of physical tables and index paths. In contrast, declarative languages like SQL let users specify what data they want without knowing the underlying structure.

The sys.dm_exec_query_stats DMV returns performance statistics for cached query plans, including a 'compilation_count' column that tracks how many times each query has been compiled. This allows identifying queries with the highest recompilation counts. The other DMVs focus on current requests, active sessions, and optimizer information respectively.

Data warehouses are designed around major subject areas (like customers, sales, products) rather than business applications. They contain historical data in a consistent format and are read-optimized for analysis. They are not updated by end users, use consistent naming conventions, and contain historical data rather than just current data.

Operational systems support day-to-day business operations in real time using current data. They capture and process transactions as they occur. Decision support systems (analytical systems) use historical data for analysis. The key distinction is operational = real-time current data, analytical = historical data for decision making.

Sync points (syncpoints) in CICS transactions occur at programmer-defined locations using commands like SYNCPOINT. They allow explicit control over transaction boundaries rather than being fixed at the beginning or end. They don't automatically occur after each READ FOR UPDATE.

Snapshot replication is ideal for this scenario because it distributes a complete copy of the publication at scheduled intervals (daily 5 a.m.). The publication is not large, and while there are many changes during the day, snapshot replication completely refreshes subscriber data each time. Transactional and merge replication are more complex and unnecessary for this daily refresh requirement.