C2090-544 - DB2 9.7 Advanced DBA for LUW Exam
Go back to IBM
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?
The clustering index column(s) should be prefixed by the partitioning key column(s).
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?
The index will automatically be compressed since the table it is associated with is compressed; the ALTER INDEX command does not have to be executed.
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?
The log buffers were written to the DB2 log files on the primary server, sent to the standby, and written to log files on the standby server. The return code was then sent to the application.
If table space TBSP1 is using automatic storage, and the existing storage paths are full, which command would increase the available storage?
ALTER DATABASE ADD STORAGE ON '/db2/filesystem1'
In order for HADR to start, which statement is true when setting the HADR_TIMEOUT database configuration value?
The HADR_TIMEOUT value must be identical on both the primary and the standby server.
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?
Restore the database and attempt to make as much of the database available as possible.
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?
CREATE TABLE tab1(yearmonth INT,year CHAR(2))PARTITION BY RANGE (yearmonth) (STARTING 201001 ENDING 201012 EVERY 3)ORGANIZE BY DIMENSIONS (year))
Which statement is correct about statistical views?
A statistical view provides the optimizer with more accurate cardinality estimates.
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.)
The DB2COMM environment variable at the server is not set to TCPIP.
The server node and/or database are not cataloged correctly on the client.
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?
Catalog the zOS database in the Database Connection Services Directory.
Which action(s) will cause a compression dictionary to be removed from a table?
Set the table COMPRESS attribute to NO;run REORG against the table.
Which factor should influence your selection of distribution key(s)?
The distribution key(s) should include the most frequently joined column(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?
CREATE UNIQUE INDEX xtab1 ON tab1 (c1) INCLUDE (c2, c3);
In which two environments would intra-partition parallelism be used? (Choose two.)
Single database partition, multiple processor cores
Multiple database partitions, multiple processor cores
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?
hostname, port number/protocol, target database name, local database name, node name
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
Any time before 11:07:03 am
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?
Databases created in DB2INST1 will use SERVER_ENCRYPT authentication if KERBEROS authentication is not available.
Which command would be used to obtain the name of the first active log file for a database with log retain enabled?
GET DATABASE CONFIGURATION
Which statement about compression and temporary tables is NOT correct?
Compression is only applied to created temporary tables that were defined with the CREATE TEMPORARY TABLE ?COMPRESS YES statement.
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?
Change the service name (SVCENAME) in the remote servers database configuration.
What is a method for enabling non-buffered I/O at the table space level in a DB2 database?
Use the NO FILE SYSTEM CACHING clause with the CREATE TABLESPACE or ALTER TABLESPACE command.
Which command will back up a database named SAMPLE to a TSM server using two concurrent TSM client sessions?
BACKUP DATABASE sample USE TSM OPEN 2 SESSIONS WITH 4 BUFFERS
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?
Issue an ALTER TABLE APPEND ON statement.
What are two authorities and/or privileges required for replaying SQL audit logs? (Choose two.)
EXECUTE privilege on audit routines with DATA
Which database configuration parameters can be used to limit the amount of memory allocated for the LOAD utility?
UTIL_HEAP_SZ and DATABASE_MEMORY
How can you change an existing non-partitioned index for a partitioned table to a partitioned index?
Use the DROP INDEX and CREATE INDEX commands.
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?
RESTORE DB proddb INCREMENTAL AUTOMATIC TAKEN AT (Friday);
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?
CREATE UNIQUE INDEX xtab1 ON tab1 (c1) INCLUDE (c2, c3, c4);
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?
Operating systems on primary and standby servers must be the same; DB2 versions must be the same; and the databases must have the same bit-size (32 or 64 bit).
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?
It is placed in the agent pool if the connection is terminated.
How can a DBA validate that an existing backup was compressed?
Check the header information of the backup file using db2ckbkp.
What steps, in the correct order, are performed by the SQL and XQuery compilers to generate executable code for non-federated queries?
parse query; check semantics; rewrite query; build access plan
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?
1) Detach partition containing July 2005 data2) Load July 2010 data into a new table3) Attach the new table as a new partition in the existing partitioned table4) Check constraint integrity for the partitioned table
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?
SORTHEAP AUTOMATIC, SHEAPTHRES 0
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?
Use the ALTER DATABASE command to convert the database; use the ALTER TABLESPACE command to convert all existing DMS table spaces.
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?
asynchronous table queue
Which of the following techniques will ensure that you CANNOT encounter two active primary servers in an HADR pair?
Use a virtual IP Address in the CATALOG DATABASE command.