Free IBM Certification SQL Database Question and Answers — Questions and Answers
Question 1: Which of the following settings must be specified if Secure Sockets Layer is necessary to encrypt data in transit?
- Set the AUTHENTICATION configuration parameter to SERVER_ENCRYPT
- Set the AUTHENTICATION configuration parameter to DATA_ENCRYPT
- Set the DB2COMM registry variable to DB2COMM=SSL, TCPIP (Correct answer)
- Set the DB2COMM registry variable to DB2COMM=SSL
Correct 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.
Question 2: 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)?
- As the db2 instance owner (db2inst1)execute the command:db2set i db2inst1 DB2_FMP_COMM_HEAPSZ=256000
- As the db2 instance owner (db2inst1) execute the command:db2set i DB2_FMP_COMM_HEAPSZ=256000 (Correct answer)
- Add to the following line to the userprofile (db2inst1): DB _FMP_COMM_HEAPSZ=256000
- As the db2 instance owner (db2inst1) execute the command:exportDB2_FMP_COMM_HEAPSZ=256000
Correct 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.
Question 3: 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?
- Existing data will remain uncompressed because the RESETDICTIONARY option of the REORG command was not used. (Correct answer)
- New and existing XML columns will be compressed but other columns remain unchanged.
- Only new or updated data for XML columns will be compressed.
- All data, both new and existing, will becompressed.
Correct 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.
Question 4: To obtain the current encryption settings for a database protected by native encryption, which of the following activities is required?
- Check ENCROPTS database configuration parameter for encryption options (Correct answer)
- Checkthe encrypted database parameter in database configuration
- Run the db2ckbkp command and verify the compression settings
- Select from the ADMIN_GET_ENCRYPTION_INFO table function
Correct 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.
Question 5: What does the built-in global variable TRUSTED_CONTEXT in DB2 have as its schema name?
- SYSIBMADM
- SYSCAT
- SYSFUN
- SYSIBM (Correct answer)
Correct 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.
Question 6: What is the Data Server Manager (DSM) statement that is TRUE?
- DSM can be used to efficiently monitor, analyze, identify, and solve a performance issue (Correct answer)
- DSM can be used for configuration management of DB2 Data Server drivers
- DSM cannot be used for database administration
- DSM can be used for reviewing audit trails
Correct 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.
Question 7: Which of the following assertions regarding auditing procedures 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. (Correct answer)
- The audit policy applied to a table is automatically applied to any view based on that table.
- Multiple audit policies can be associated with the same table at the same time.
- Audit policy changes are applied instantly and affect all inflight transactions.
Correct 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.
Question 8: What choices are acceptable for an online reorganization operation?
- CLEAN OVERFLOWS and RECLAIM EXTENTS
- FULL and CLEANUP OVERFLOWS
- CLASSIC and RECLAIM EXTENTS
- FULL and RECLAIM EXTENTS (Correct answer)
Correct 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.
Question 9: Which command in a DB2 pureScale system enables explicit hierarchical locking (EHL)?
- UPDATE DB CFG USING LOCKLIST AUTOMATIC MAXLOCKS AUTOMATIC
- UPDATE DB CFG USING LOCKLIST 4096 MAXLOCKS 90
- UPDATE DB CFG USING OPT_DIRECT_WRKLD YES
- UPDATE DB CFG USING CF_LOCK_SZ AUTOMATIC (Correct answer)
Correct 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.
Question 10: Which of the following commands will display a list of every disk configuration for a DB2 pureScale cluster's DATAFS file system?
- db2cluster –cfs –displayconfig –filesystem datafs
- db2cluster –cfs –listconfig –filesystem datafs (Correct answer)
- db2cluster –cfs –display –filesystem datafs
- db2cluster –cfs –list –filesystem datafs
Correct 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.
Question 11: Which security system is in charge of making sure users are who they say they are?
- Discretionary access control
- Mandatory access control
- Roles and privileges
- Authentication (Correct answer)
Correct 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.
Question 12: What one of the following is true?
- REORGCHK can be used to verify the level of data fragmentation and indicate whether reorganization might be required.
- REORGCHK can only be used for user tables, not for system tables.
- REORGCHK uses only current table and index statistics. (Correct answer)
- REORGCHK automatically runstables reorganization.
Correct 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.
Question 13: With trusted context rights obtained through a role, which operation can be completed?
- Security administration operations
- Server maintenance operations
- DDL operations
- DML operations (Correct answer)
Correct 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.
Question 14: What assertion regarding compression for BLU MPP tables is TRUE?
- Each table has a single compression dictionary that getsreplicated across all partitions
- Unique compression dictionaries are generated for each partition
- Compression requires decompression to evaluate partition joins
- Compression must be explicitly enabled for BLU MPP tables (Correct answer)
Correct 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.
Question 15: 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?
- RUNSTATS ON TABLE sales INPLACE AND INDEXES ALL;
- RUNSTATS ON TABLE sales AND INDEXES ALL;REORGCHK ON TABLE sales;
- REORG TABLE sales INPLACE;REORG INDEXES ALL FOR TABLE sales;RUNSTATS ON TABLE salesAND INDEXES ALL;
- REORG TABLE sales AND INDEXES ALL;RUNSTATS ON TABLE sales AND INDEXES ALL; (Correct answer)
Correct 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.
Question 16: What sources of data does the DB2 Problem Determination tool (db2pd) use?
- A set of pre-defined event monitors
- Monitoring table functions (Correct answer)
- the db2diag.log file
- DB2memory sets
Correct 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.
Free IBM Certification SQL Database Question and Answers
Which of the following settings must be specified if Secure Sockets Layer is necessary to encrypt data in transit?