A2090-544 - Assessment: DB2 9.7 Advanced DBA for LUW - Assessment
Go back to IBM
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).
Setting the DB2_FMP_COMM_HEAPSZ=0 will result in which the following?
Non-fenced routines can be invoked, however automatic database maintenance will be disabled.
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
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.
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 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
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.
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.
At which two levels can events be audited? (Choose two.)
The DBA needs to create a table with key columns YEARDAY, YEAR, and DAY. This table needs to be partitioned by column YEARDAY with three months per data partition. Additionally, data needs to be organized 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(yearday INT,year CHAR(2),day INT))PARTITION BY RANGE (yearday) (STARTING 201001 ENDING 201012 EVERY 3)ORGANIZE BY DIMENSIONS (year))
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
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.
How can a DBA validate that an existing backup was compressed?
Check the header information of the backup file using db2ckbkp.
Which two operators would indicate inter-partition parallelism in an Optimizer Plan? (Choose two.)
Assuming table TAB_A resides in a DB2 9.7 database, what is one way to obtain an estimate of how much space could be saved if this table were compressed?
The ADMIN_GET_TAB_COMPRESS_INFO_V97 table function.
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?
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).
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
Which database configuration parameters can be used to limit the amount of memory allocated for the LOAD utility?
UTIL_HEAP_SZ and DATABASE_MEMORY
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.
What are two authorities and/or privileges required for replaying SQL audit logs? (Choose two.)
EXECUTE privilege on audit routines with DATA
What will the db2audit facility do when a DB2 database instance is stopped?
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.
What determines the degree of inter-partition parallelism used?
The number of database partitions and the definition of partition groups.
Which command CANNOT be used to create the compression dictionary for a table?
Given a database with Self-Tuning Memory Manager (STMM) enabled, what will DB2 do when a new buffer pool is created using the IMMEDIATE option?
Attempt to create the buffer pool and immediately allocate the memory requested from available database shared memory. If the memory available is not sufficient, then allocation of the buffer pool will be deferred until the database is stopped and restarted.
Which command will prevent prefetching during crash recovery?
What is the proper set of steps to execute a redirected restore and change the container paths for a database not using automatic storage?
Issue a RESTORE DATABASE command with the REDIRECT option;issue a SET TABLESPACE CONTAINERS command;issue a RESTORE DATABASE command with the CONTINUE option;
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.
A DBA has configured an Oracle data source in a federated database (that references a table named ORA1). How do you update the statistics for table ORA1 at the federated server?
Perform the equivalent of RUNSTATS on ORA1 in Oracle; then drop and re-create the ORA1 nickname.
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.
Which data organization schemes are supported?
PARTITION BY RANGE and ORGANIZE BY
Which action can be performed by the ALTER TABLESPACE statement?
Add a container to an SMS table space on a database partition that currently has no containers.
If you know that the table space containers for your database reside on RAID-5 3+p arrays, which registry variable should be assigned the value *:3 to help improve performance?
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.
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 command would be used to obtain the name of the first active log file for a database with log retain enabled?
GET DATABASE CONFIGURATION
A batch application updates a large number of rows within a single transaction. What is one way to help the application complete quickly?
Code the application to issue a LOCK TABLE statement.
A database named MYDB is created in instance DB2INST1, which has been configured to use KRB_SERVER_ENCRYPT authentication. If a client attempting to access database MYDB does not support Kerberos authentication, which authentication method will be used to establish a connection?
Which statement is correct about statistical views?
A statistical view provides the optimizer with more accurate cardinality estimates.
Which command CANNOT be used to explicitly build a compression dictionary for a table?
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
Given a SHEAPTHRES value of 2560, in which two cases will a SHEAPTHRES_SHR value of 1024 be meaningful? (Choose two.)
MAX_CONNECTIONS 2000, MAX_COORDAGENTS 100
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))
A DBA has established connectivity to a DB2 for z/OS database and is exploiting a System z sysplex for load balancing and fault-tolerance. Which series of statements and commands can be issued to ensure that agents remain allocated to help maintain the sysplex server list?
UPDATE DBM CFG USING NUM_POOLAGENTS 200;db2stop;db2start;
Which description is correct when discussing the DB2_MEM_TUNING_RANGE registry variable?
It is recommended to set this variable only when using STMM, when DATABASE_MEMORY is set to AUTOMATIC, and if insufficient system memory problems are occurring.
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.
If LOGSECOND=-1 then which of the following statements is true?
You still use LOGPRIMARY and LOGFILSIZ configuration parameters to specify how many log files the database manager should keep in the active log path.
What new security authorities have been introduced in DB2 V9.7?
WLMADM, SQLADM, EXPLAIN