C2090-544 - DB2 9.7 Advanced DBA for LUW Exam

Go back to IBM

Example Questions

If your DB2 server is not responding (hung), and you cannot run DB2 commands from the Command Line Processor, which utility can be used to list the applications currently connected to the database MYDB? Which statement is true about clustering indexes in a partitioned database? A table named TAB_A was created with the COMPRESS YES option specified and populated with 100,000 rows. If a DBA wants to create an index on table TAB_A, which statement is true? A primary and standby database are enabled for HADR and are in peer state. The HADR_SYNCMODE configuration parameter is set to SYNC. An application connects to the primary database, issues a COMMIT and receives a successful return code in response. How were the log buffers for this unit of work processed? If table space TBSP1 is using automatic storage, and the existing storage paths are full, which command would increase the available storage? In order for HADR to start, which statement is true when setting the HADR_TIMEOUT database configuration value? At which two levels can events be audited? (Choose two.) The RECOVER command is used in an attempt to fix a damaged table space. What will the system do during the RECOVER process? The DBA needs to create a table with key columns YEARMONTH and YEAR. This table needs to be partitioned by column YEARMONTH with three months per data partition. Additionally, data needs to be organized by column YEAR, so that all rows within any three month date range are clustered together based on 12 months of data. Which CREATE TABLE statement will accomplish this objective? Which statement is correct about statistical views? An employee is not able to connect to the PRODDB database using the correct user ID and password. The TCP/IP protocol is running normally; other remote clients are also having problems connecting to this database. Which two could be the cause of the problem? (Choose two.) A Windows user with a local database is not able to connect to a zOS database. The System Database Directory and the Node Directory have the correct entries. Other users can connect to the database from their workstations without problems. What should be done resolve the problem? Which action(s) will cause a compression dictionary to be removed from a table? Which factor should influence your selection of distribution key(s)? Table TAB1 was created using the following statement: CREATE TABLE tab1 (c1 INT, c2 INT, c3 INT, c4 INT, c5 INT); If column C1 is unique and queries typically access columns C1, C2 and C3 together, which statement(s) will create index(es) that will provide optimal query performance? In which two environments would intra-partition parallelism be used? (Choose two.) Which series of items are required to establish a TCP/IP connection to a DB2 for z/OS database from a DB2 for Linux, UNIX, or Windows server? Given an HADR pair configured with HADR_PEER_WINDOW set to 300 seconds. If the standby server looses communication with the primary at 11:02:03 am, when can the following command be run so that the standby server will successfully be promoted to be a primary server? TAKEOVER HADR ON DB mydb BY FORCE PEER WINDOW ONLY Your UNIX system administrator has indicated that there appears to be a lot of I/O wait time associated with your database, which is stored in a 7+p RAID array. An investigation reveals the following information: DB2_PARALLEL_IO=* Tablespace with ID 7 has a single container and is dedicated to the largest table. It has a page size of 16K, an extent size of 32, and a prefetch size of 64. Which setting would optimize I/O for the largest table? If instance DB2INST1 is created and its authentication type is set to KRB_SERVER_ENCRYPT, which statement is true? Which command would be used to obtain the name of the first active log file for a database with log retain enabled? Which statement about compression and temporary tables is NOT correct? Given a newly created database and sufficient memory. If the following statements are successful: CREATE BUFFERPOOL bp1 SIZE 40MB; CREATE BUFFERPOOL bp2 PAGESIZE 16K SIZE 40000; How much memory will be allocated for buffer pools BP1 and BP2? While attempting to establish connectivity between a Windows client and a UNIX server, the following commands were issued from the Windows client: CATALOG TCPIP NODE node001 REMOTE hostnm01 SERVER 50000; CATALOG DATABASE samp001 AS samp001 AT NODE node001; TERMINATE; When trying to connect to the SAMP001 database the following error was received: SQL30061N The database alias or database name name was not found at the remote node. The database name is not an existing database at the remote database node. The statement cannot be processed. What should be done resolve the problem? What is a method for enabling non-buffered I/O at the table space level in a DB2 database? Which command will back up a database named SAMPLE to a TSM server using two concurrent TSM client sessions? A journaling application inserts data with continually increasing sequential keys while a clean up process randomly deletes erroneous rows. What can be done to maximize insert performance? What are two authorities and/or privileges required for replaying SQL audit logs? (Choose two.) Which database configuration parameters can be used to limit the amount of memory allocated for the LOAD utility? How can you change an existing non-partitioned index for a partitioned table to a partitioned index? A database called PRODDB has a weekly full backup taken on Saturday, non-cumulative (delta) backups taken daily, and a midweek cumulative backup that is taken on Wednesday. A database crash occurs on a Saturday prior to the full backup being taken. What is the fastest way to restore the database? Table TAB1 was created using the following statement: CREATE TABLE tab1 (c1 INT, c2 INT, c3 INT, c4 INT, c5 INT); If column C1 is unique and queries typically access columns C1, C2, C3 and C4 together, which statement(s) will create index(es) that will provide optimal query performance? Which database manager configuration parameter is used to set an instance-wide soft limit on the total amount of memory that can be consumed by private sorts, within that instance, at any given time? Which conditions must be met to implement HADR? Which command will prevent prefetching during crash recovery? Which option of the ALTER TABLE statement tells the DB2 optimizer to use an index scan on a table that might vary in size at run time from being empty to containing a large number of rows? When an agent that is connected to the database finishes its work of successfully processing a query, what happens to the agent? How can a DBA validate that an existing backup was compressed? What steps, in the correct order, are performed by the SQL and XQuery compilers to generate executable code for non-federated queries? What will the db2audit facility do when a DB2 database instance is stopped? What would indicate intra-partition parallelism in an Optimizer Plan? What are the steps required to remove July 2005 data from a partitioned table and replace with it with July 2010 data? Given INTRA_PARALLEL is OFF, average row length is 900 bytes, and temporary table cardinality is 1000, which setting would prevent a SORTHEAP overflow when the temporary table is sorted? A database named DB1 has a database partition group named MULTIPG that has been defined across partitions 3, 4 and 5. The DBA issues the following series of commands/SQL statements: CONNECT TO db1; SET CURRENT DBPARTITIONNUM 4; ALTER DATABASE PARTITION GROUP multigp ADD DBPARTITIONNUM (6); Which partition will DB2 use as a model for table spaces on partition 6? What is the minimum authority required to execute the LOAD command? A DBA would like to examine repartitioning options for a partitioned database named PRODDB. Which tool can be used to provide recommendations on re-partitioning? A database named MYDB was created by executing the following command: CREATE DATABASE mydb AUTOMATIC STORAGE NO Later, a decision is made to modify this database so that both it and its table spaces use automatic storage. What steps are required to make this modification? Which tool or command will display only the automatic storage paths in an existing backup image? Which type of table queue is used to pass data between database partitions when rows are being fetched? Which of the following techniques will ensure that you CANNOT encounter two active primary servers in an HADR pair?