Database Management Systems

EXECUTE IMMEDIATE is used to prepare and execute a dynamic SQL statement in a single step. It's ideal for statements that won't be repeatedly executed. EXEC SQL is for static SQL embedded in host programs, while DECLARE CURSOR and OPEN are for cursor operations.

XQuery is the standard query language designed specifically for retrieving and manipulating data from XML documents. DB2 9 introduced native XML support with XQuery. LOB is a binary storage type, while images and videos are typically stored as BLOBs.

RUNSTATS is recommended after REORG because REORG changes the physical data distribution, making existing statistics obsolete. RUNSTATS collects fresh statistics about table data distribution, enabling the optimizer to choose efficient access paths for subsequent queries.

Views cannot be modified using ALTER. To change a view definition, you must DROP and recreate it. In contrast, ALTER TABLESPACE can change attributes like logging, ALTER SEQUENCE can restart or change increment, and ALTER INDEX can modify certain index properties.

True. The LOAD utility rebuilds indexes after loading data, especially when using REPLACE method which deletes existing data. For INSERT method or with certain options, indexes may be incrementally updated, but LOAD always ensures index consistency by rebuilding or updating them.

The DB2 Directory contains Database Descriptors (DBDs), which are internal control structures describing database physical structures. The DB2 Catalog stores metadata about database objects, the Optimizer uses statistics for access path selection, and the Data Manager manages actual data access.

The DB2/SQL EXPLAIN statement inserts detailed information about the access path of the query being explained into a table named PLAN_TABLE. DSN_STATEMNT_TABLE contains cost estimates, while SYSPLAN and SYSPACKAGE are catalog tables storing plan and package metadata rather than explain output.

In Oracle 9i database, the OSDBA operating system group is mapped to the SYSDBA database privilege, allowing users within this group to authenticate via the operating system. OSOPER maps to the SYSOPER privilege. Other options like 9iDBA or OSADM are not standard Oracle-predefined OS groups for database administration privileges.

The SYSOPER administrative privilege in Oracle databases permits basic operations like STARTUP, SHUTDOWN, and MOUNT/OPEN/CLOSE of a database. However, database creation requires the more powerful SYSDBA privilege.

Oracle's recommended approach requires backing up the fully functional database before beginning any tuning activities. This ensures you have a recovery point if tuning changes cause issues. Installing software, planning, and implementing design are prerequisites but must complete before the database is fully functional and ready for backup.

Evaluating the database server hardware is Oracle's first recommended task before any software installation or database design. Hardware assessment ensures the server meets requirements for memory, CPU, storage, and I/O capacity. Software installation (A), planning (D), and implementation (C) all happen after hardware evaluation.

The oraenv script provides a central mechanism for switching between different Oracle database instances by updating environment variables like ORACLE_SID, ORACLE_HOME, and PATH for all user accounts. Option C correctly describes this central update function. Options A and B mention scanning functions which don't apply. Option D describes database switching, which is the result, not the function.

DB_BLOCK_SIZE is a fundamental storage parameter that can only be set when a database is first created. It determines the size of data blocks and cannot be changed by simply modifying parameters or restarting - the entire database must be recreated with the new block size.

Database connections in Oracle environments typically use the format hostname:port:sid. For example: 'myserver:1521:ORCL' where 1521 is the default Oracle port and ORCL is the SID.

Database connections in Oracle environments typically use the format hostname:port:sid. For example: 'myserver:1521:ORCL' where 1521 is the default Oracle port and ORCL is the SID.