FREE IBM Certification SQL Database Question and Answers
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?
the effect of enabling compression and running the command REORG TABLE mytable KEEPDICTIONARY is that existing data will remain uncompressed because the RESETDICTIONARY option of the REORG command was not used. When you enable compression on a table with an XML column and execute the REORG command with KEEPDICTIONARY, the existing dictionary is retained, and it will not apply compression to the already existing XML data. The KEEPDICTIONARY option ensures that the existing dictionary and its mappings to the uncompressed XML data are maintained. As a result, only new or updated data for XML columns will be compressed based on the updated compression setting, while the existing data in the XML column will remain uncompressed because the dictionary was not reset.
What choices are acceptable for an online reorganization operation?
The options that are valid for an online reorganization operation in IBM Db2 are FULL and RECLAIM EXTENTS. FULL: This option performs a full reorganization of the table, which means that all the data and indexes are reorganized. It reclaims unused space, improves data and index organization, and can improve performance. RECLAIM EXTENTS: This option is used to reclaim unused extents in the table space. It releases unused space, making it available for reuse.
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)?
This command sets the DB2_FMP_COMM_HEAPSZ registry variable to 1 GB (or 256000 pages) only for the instance db2inst1. The "db2set" command is used to set or modify DB2 registry variables, and in this case, the variable being set is DB2_FMP_COMM_HEAPSZ. By executing this command as the db2inst1 user, you are configuring the specific instance to have the specified communication heap size, which will be effective only for that particular instance.
Which of the following settings must be specified if Secure Sockets Layer is necessary to encrypt data in transit?
To encrypt data in transit with Secure Sockets Layer (SSL) for IBM Db2, you need to set the DB2COMM registry variable to "DB2COMM=SSL, TCPIP." This configuration enables SSL communication over TCP/IP, ensuring that the data transmitted between the client and the Db2 server is encrypted and secured. Setting "DB2COMM=SSL, TCPIP" allows Db2 to use SSL to encrypt the communication channel, providing data confidentiality and integrity while it travels over the network. SSL encryption prevents unauthorized access or tampering of data during transmission, enhancing the security of sensitive information exchanged between clients and the Db2 server.
To obtain the current encryption settings for a database protected by native encryption, which of the following activities is required?
To acquire the current encryption settings for a database protected by native encryption in IBM Db2, you need to check the ENCROPTS database configuration parameter. The ENCROPTS parameter stores the encryption options for the database, and by examining its value, you can determine the current encryption settings and options used to protect the data in the database.
What is the Data Server Manager (DSM) statement that is TRUE?
The statement that is TRUE about Data Server Manager (DSM) is: DSM can be used to efficiently monitor, analyze, identify, and solve a performance issue. Data Server Manager is an IBM tool designed to provide comprehensive and advanced monitoring and management capabilities for IBM Db2 databases. It offers a user-friendly interface and a wide range of features to monitor the performance and health of Db2 databases efficiently.
Which command in a DB2 pureScale system enables explicit hierarchical locking (EHL)?
Setting the CF_LOCK_SZ (Cross Member Lock Size) configuration parameter to AUTOMATIC enables Explicit Hierarchical Locking in a DB2 pureScale environment. This parameter determines the size of the cross-member locks, and when set to AUTOMATIC, DB2 dynamically determines the appropriate size based on the workload and system characteristics.
What assertion regarding compression for BLU MPP tables is TRUE?
In IBM Db2 with BLU Acceleration, compression is not automatically enabled for BLU MPP tables. You need to explicitly enable compression for each BLU MPP table to take advantage of storage savings and performance improvements that compression offers.
Which of the following commands will display a list of every disk configuration for a DB2 pureScale cluster's DATAFS file system?
In this command: -cfs stands for Cluster File System, which is the file system used in a DB2 pureScale environment. -listconfig is the option used to list the disk configurations of the specified file system. -filesystem datafs is used to specify the name of the file system for which you want to list the disk configurations. In this case, it is "DATAFS." So, the correct command to use is db2cluster -cfs -listconfig -filesystem datafs. This will provide you with the disk configurations associated with the file system DATAFS in the DB2 pureScale cluster.
What one of the following is true?
REORGCHK is a command in IBM Db2 that analyzes the current statistics of tables and indexes to determine the level of data fragmentation. It does not consider historical statistics, only the most up-to-date statistics available in the database.
With trusted context rights obtained through a role, which operation can be completed?
With trusted context privileges acquired through a role in IBM Db2, DML (Data Manipulation Language) operations can be performed. Roles are used to group and manage sets of privileges, making it easier to grant or revoke permissions for a specific set of users or groups. Trusted contexts provide a mechanism for establishing a trusted identity, allowing a user or application to access certain resources with elevated privileges based on a defined set of rules.
What does the built-in global variable TRUSTED_CONTEXT in DB2 have as its schema name?
The correct schema name of the DB2 built-in global variable TRUSTED_CONTEXT is SYSIBM. In IBM Db2, the TRUSTED_CONTEXT global variable is used to access information about the trusted context associated with a user session. Trusted context provides a way to establish a trusted identity for a user or application, allowing them to access certain resources with elevated privileges.
What sources of data does the DB2 Problem Determination tool (db2pd) use?
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.
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?
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.
Which security system is in charge of making sure users are who they say they are?
Authentication is the process of confirming the identity of a user, system, or entity to ensure that the claimed identity is valid and authorized to access specific resources or perform certain actions. It is a crucial step in the security process to prevent unauthorized access and protect sensitive information.
Which of the following assertions regarding auditing procedures is TRUE?
The following statement about audit policies is TRUE: 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. In IBM Db2, trusted contexts are used to establish a trusted identity for a user or application, allowing them to access certain resources with elevated privileges. When a user switches within a trusted connection, the security context changes, and Db2 re-evaluates the applicable audit policies for the new user's identity.