Database Management Systems

Under DB2's Cursor Stability isolation level, locks are held only on the row where the cursor is currently positioned. When the application deletes that row, the cursor no longer has a row to position on, so the lock is released. Moving the cursor to another row also releases the previous lock.

SYSMAINT is a system-level authority granted through the DB2 Database Manager configuration. It allows maintenance operations like backups and restores but does not include data access permissions like CONNECT or full administrative control.

Repeatable Read is the strictest isolation level that locks all rows scanned during query execution. This ensures that if the same query is run again, it returns the exact same result set without phantom reads.

Repeatable Read isolation level locks all rows that are scanned to build the result set, preventing phantom reads and ensuring that if the same query is executed again within the transaction, the same rows will be returned. Read Stability only locks individual rows as they are read, Cursor Stability locks only the current row position, and Uncommitted Read imposes minimal locking.

Declared temporary tables (DGTTs) are private to a session and are primarily used to store intermediate query results during complex processing, automatically disappearing when the session terminates.

Application Source Qualifier is specifically used with Salesforce.com sources in Informatica PowerCenter. For COBOL sources you use COBOL source definitions, for XML sources you use XML Source Qualifier, and for relational sources you use standard Source Qualifier.

The Application Source Qualifier transformation is specifically used with external application sources like Salesforce.com to define the data extraction logic. It is not used for standard relational sources, COBOL sources, or XML sources which have their own appropriate qualifiers. Options A, B, and D use different source qualifier types specific to those source systems.

Under Cursor Stability isolation level, a lock is held only while the cursor remains on the current row. When the application deletes that row, the lock must be released as the row no longer exists for the cursor to point to. Option A describes normal cursor movement, option C and D involve operations by other applications which would require different locking behaviors.

Under the Cursor Stability (CS) isolation level, DB2 acquires a lock on the row currently positioned by the cursor. When the application deletes the row, the lock is held until a commit or rollback occurs to ensure transaction integrity.

The SYSMAINT (System Maintenance) authority is a system-level authority configured within the DB2 Database Manager configuration file (dbm cfg). Privileges like CONNECT, CONTROL, or EXECUTE are database-level privileges granted via SQL.

Repeatable Read (RR) isolation level locks all rows that are scanned to build the result set, preventing other transactions from modifying those rows. Uncommitted Read (UR) doesn't lock rows, Cursor Stability (CS) locks only the current row, and Read Stability (RS) locks only rows that are actually returned, not all scanned rows.

Repeatable Read is the strictest standard isolation level that prevents phantom reads by locking all rows scanned during query execution. Unlike Read Stability (which locks only rows actually returned) or Cursor Stability (which locks only the current row), Repeatable Read locks the entire row set used to build the result. This ensures that if the same query is re-executed within the transaction, it returns identical results because no other transaction can modify, insert, or delete rows that would affect that result set.

Declared temporary tables are specifically designed to store intermediate or transient results during complex processing operations. Unlike regular tables, they exist only for the duration of a session or transaction and are automatically dropped afterward. They are not meant for backup (use backup utilities), load staging (use external staging tables), or sharing between applications (use regular tables or shared schemas). Their purpose is to hold partial results during multi-step data transformations, complex calculations, or query processing.

OLAP (Online Analytical Processing) databases used for data warehousing contain multiple table types. Staging tables hold raw data before processing, Dimension tables store descriptive attributes (like time, geography, product), and Fact tables store quantitative metrics and foreign keys to dimensions. All three are fundamental components of a dimensional data warehouse. The star or snowflake schema architecture requires both fact and dimension tables, and staging tables are essential for the ETL loading process.

A star schema is a dimensional database design where a central fact table (containing quantitative metrics and foreign keys) is directly connected to multiple dimension tables (containing descriptive attributes). The fact table is at the center 'surrounded by' dimension tables, creating a star-like pattern when visualized. Dimension tables are never at the center - they provide the context (who, what, where, when) for the metrics stored in the fact table. This design optimizes query performance for analytical queries.