SQL Database Flashcards

Which of the following settings must be specified if Secure Sockets Layer is necessary to encrypt data in transit?

Answer: Set the DB2COMM registry variable to DB2COMM=SSL, TCPIP

To enable Secure Sockets Layer (SSL) encryption for data in transit in DB2, the DB2COMM registry variable must be configured to include SSL. By setting `DB2COMM=SSL, TCPIP`, DB2 is instructed to listen for both SSL-encrypted and unencrypted TCP/IP connections, allowing for secure communication. This ensures that data exchanged between clients and the DB2 server is encrypted.

Which of the following, exclusively for the instance db2inst1, will set the DB2 FMP COMM HEAPSZ registry variable for Linux and Unix systems to 1 GB (or 256000 pages)?

Answer: As the db2 instance owner (db2inst1) execute the command:db2set i DB2_FMP_COMM_HEAPSZ=256000

The `db2set` command is used to manage DB2 registry variables. The `-i` option specifies that the setting applies to the current instance, and `DB2_FMP_COMM_HEAPSZ=256000` sets the FMP communication heap size to 256000 pages (which is 1GB if a page is 4KB). This command must be executed by the instance owner to apply the change specifically to that instance.

An XML storage object dictionary already exists, and the table MYTABLE has an XML column. What happens if compression is enabled and REORG TABLE mytable KEEPDICTIONARY is then executed?

Answer: Existing data will remain uncompressed because the RESETDICTIONARY option of the REORG command was not used.

When an XML storage object dictionary already exists and `REORG TABLE mytable KEEPDICTIONARY` is executed, the existing dictionary is preserved. This means that only new or updated data will be compressed using the existing dictionary, while previously uncompressed data remains in its original state. To compress existing data, the `RESETDICTIONARY` option would be required to rebuild the dictionary and recompress all data.

To obtain the current encryption settings for a database protected by native encryption, which of the following activities is required?

Answer: Check ENCROPTS database configuration parameter for encryption options

For a database protected by native encryption in DB2, the `ENCROPTS` database configuration parameter stores the encryption options. Checking this parameter allows administrators to determine the current encryption settings, such as the encryption algorithm and key management details, applied to the database. This is the primary method for verifying native encryption settings.

What does the built-in global variable TRUSTED_CONTEXT in DB2 have as its schema name?

Answer: SYSIBM

In DB2, `SYSIBM` is the schema name for many built-in global variables, functions, and system catalog views. The `TRUSTED_CONTEXT` global variable, which provides information about the trusted context under which a session is running, resides within this system schema. This schema is reserved for system objects and ensures consistency across DB2 installations.

What is the Data Server Manager (DSM) statement that is TRUE?

Answer: DSM can be used to efficiently monitor, analyze, identify, and solve a performance issue

IBM Data Server Manager (DSM) is a comprehensive web-based tool designed for monitoring, administration, and performance management of DB2 databases. Its primary capabilities include real-time monitoring, historical analysis, and expert advice to help identify and resolve performance bottlenecks efficiently. This makes it an invaluable tool for maintaining optimal database performance.

Which of the following assertions regarding auditing procedures is TRUE?

Answer: If a user is switched within a trusted connection, the applicable audit policies are re- evaluated for the new user without regard to audit policies for the original user.

When a user is switched within an existing trusted connection in DB2, the database system re-evaluates the audit policies based on the *new* user's identity and associated roles. This ensures that the correct auditing rules are applied for the actions performed by the switched user, maintaining security and compliance. This dynamic re-evaluation is crucial for accurate auditing in multi-user trusted contexts.

What choices are acceptable for an online reorganization operation?

Answer: FULL and RECLAIM EXTENTS

For online reorganization operations in DB2, `FULL` specifies that the entire table data is reorganized, which can improve performance and reduce fragmentation. `RECLAIM EXTENTS` is an option that allows for the reclamation of empty extents within the table space, making that space available for reuse and optimizing storage. Both options contribute to efficient and optimized table maintenance.

Which command in a DB2 pureScale system enables explicit hierarchical locking (EHL)?

Answer: UPDATE DB CFG USING CF_LOCK_SZ AUTOMATIC

In a DB2 pureScale system, explicit hierarchical locking (EHL) is enabled by configuring the `CF_LOCK_SZ` database configuration parameter. Setting `CF_LOCK_SZ` to `AUTOMATIC` allows the Cluster Caching Facility (CF) to dynamically manage the size of its lock list, which is crucial for EHL to function effectively and optimize locking behavior across the cluster. This command directly controls the CF's lock management.

Which of the following commands will display a list of every disk configuration for a DB2 pureScale cluster's DATAFS file system?

Answer: db2cluster –cfs –listconfig –filesystem datafs

The `db2cluster` command is used to manage the DB2 pureScale cluster file system (CFS). The `-cfs` option specifies CFS operations, and `-listconfig` is used to display the configuration details, including disk configurations, for a specified file system like `datafs`. This command provides essential information for monitoring and troubleshooting the cluster file system.

Which security system is in charge of making sure users are who they say they are?

Answer: Authentication

Authentication is the security process of verifying the identity of a user, system, or application. It ensures that individuals or entities attempting to access a system are indeed who they claim to be, typically through credentials like usernames and passwords or digital certificates. This is the foundational step in any secure access control system.

What one of the following is true?

Answer: REORGCHK uses only current table and index statistics.

The `REORGCHK` utility in DB2 analyzes the current state of tables and indexes to determine if a reorganization is recommended. It relies on the most recent table and index statistics to assess fragmentation and other factors, providing an accurate evaluation of whether a `REORG` operation would be beneficial. It does not perform the reorganization itself, but rather advises on its necessity.

With trusted context rights obtained through a role, which operation can be completed?

Answer: DML operations

Trusted contexts in DB2 allow for enhanced security and privilege management. When rights are obtained through a role within a trusted context, these rights typically grant permissions for Data Manipulation Language (DML) operations like `SELECT`, `INSERT`, `UPDATE`, and `DELETE` on specific objects. These are common operations for application users, while administrative tasks usually require higher privileges.

What assertion regarding compression for BLU MPP tables is TRUE?

Answer: Compression must be explicitly enabled for BLU MPP tables

For BLU Acceleration tables in a Massively Parallel Processing (MPP) environment, compression is a key feature for performance and storage efficiency. However, it is not automatically enabled; administrators must explicitly enable compression for BLU MPP tables to leverage its benefits. This allows for granular control over storage optimization.

A DBA has noticed that queries run against the SALES table perform poorly. The SALES table has extremely high levels of data fragmentation, according to DB2 tools. For the SALES table's defined indexes, similar outcomes were found. Which of the above commands should the DBA use to enhance the speed of queries executed against the SALES table?

Answer: REORG TABLE sales AND INDEXES ALL;RUNSTATS ON TABLE sales AND INDEXES ALL;

The REORG command is used to reorganize the data and indexes of a table to reduce fragmentation, improve data organization, and optimize performance. By using the REORG TABLE command with the AND INDEXES ALL option, the DBA can reorganize both the SALES table and all the associated indexes. This helps in improving query performance by optimizing the data layout and index structures.

What sources of data does the DB2 Problem Determination tool (db2pd) use?

Answer: Monitoring table functions

Monitoring Table Functions are special functions provided by IBM Db2 that allow users to retrieve real-time performance and health metrics from the database. These functions provide valuable insights into various aspects of the database's behavior and activity, helping database administrators and developers diagnose and troubleshoot issues.