DB2 10.1 Advanced DBA for Linux UNIX and Windows

QUESTION NO: 1

Which constraints are used to tell the DB2 Optimizer to consider business rules that are not enforced by the database manager?

A. Check constraints

B. Default constraints

C. Informational constraints

D. Referential integrity constraints

Answer: C

Explanation:

QUESTION NO: 2

Given the following database design:

A Explain plan shows that a broadcast join is being used by a query that joins the DATE_DIM dimension table and the SALES fact table. Which action should be performed to improve query performance?

A. Create a replicated summary table for the SALES table using the table space TS_REPL.

B. Create a replicated summary table for the DATE_DIM table using the table space TS_REPL.

C. Create an index on both the DATE_DIM and the SALES table for the column that is used in the collocated join.

D. Create a replicated summary table for the SALES table using the table space TS_DIM and create an index on the DATE_DIM table.

Answer: B

Explanation:

QUESTION NO: 3

Which procedure is used to Explain an SQL statement using information obtained from the package cache or the package cache event monitor?

A. EXPLAIN_FROM_DATA

B. EXPLAIN_FROM_ACTIVITY

C. EXPLAIN_FROM_SECTION

D. EXPLAIN_FROM_CATALOG

Answer: C

Explanation:

QUESTION NO: 4

Which statement about the initiation of parallel I/O is correct?

A. For list prefetching, each list of data pages is divided into smaller lists according to extent boundaries and assigned to different I/O servers.

B. For read-ahead prefetching, each list of data pages is divided into smaller lists according to extent boundaries and assigned to different I/O servers.

C. For sequential prefetching, parallel I/O is initiated when the prefetch size is a multiple of the extent size for a table space. Each prefetch request is divided into smaller requests and assigned to different I/O servers.

D. For sequential prefetching, parallel I/O is initiated when the extent size is a multiple of the prefetch size for a table space. Each prefetch request is divided into smaller requests and assigned to different I/O servers.

Answer: C

Explanation:

QUESTION NO: 5

The following message is frequently seen in the DB2 Diagnostics Log file (db2diag.log): MESSAGE : ADM5502W The escalation of "28581" locks on table "TEST.TABLE1" to lock intent "X" was successful. Which two database configuration parameter settings should be checked? (Choose two.)

A. LOCKLIST

B. MAXAPPLS

C. MAXLOCKS

D. MAXAGENTS

E. LOCKTIMEOUT

Answer: A,C

Explanation:

QUESTION NO: 6

Consider the following statement:

Which indexes should be created for optimal query performance?

A. CREATE INDEX emp_idx ON employee (workdept);CREATE INDEX dept_idx ON department (deptno);

B. CREATE INDEX emp_idx ON employee (workdept) CLUSTER;CREATE INDEX dept_idx ON department (deptname, deptno);

C. CREATE INDEX emp_idx ON employee (workdept, lastname, job);CREATE INDEX dept_idx ON department (deptname, deptno);

D. CREATE INDEX emp_idx ON employee (lastname, job, workdept) CLUSTER;CREATE INDEX dept_idx ON department (deptname);

Answer: C

Explanation:

QUESTION NO: 7

What will occur when a database administrator adds a new automatic storage path to an existing storage group?

A. New table spaces will start consuming storage on the new path.

B. Existing table spaces will not consume storage on the new path.

C. The storage group will be placed in "Rebalance Pending" mode.

D. DB2 will verify that space is available on the storage path specified.

Answer: B

Explanation:

QUESTION NO: 8

Which monitoring tool can be used to obtain stripe set information for database managed storage table spaces?

A. The MON_GET_CONTAINER table function.

B. The SNAP_GET_TBSP_PART table function.

C. The SNAPTBSP_RANGE administrative view.

D. The TS_GET_CONTAINER administrative view.

Answer: A

Explanation:

QUESTION NO: 9

Which statement about value compression is correct?

A. To use value compression, you must have a license for the DB2 Storage Optimization Feature.

B. Value compression uses a page-level dictionary-based compression algorithm to compress data based on repetition of values found in a single column.

C. Value compression involves removing duplicate entries for a value, only storing one copy, and keeping track of the location of any references to the stored copy.

D. When value compression is used, the byte count of compressed NULLs, zeros, and blanks might be larger than that of the uncompressed version of the same values.

Answer: C

Explanation:

QUESTION NO: 10

Which statement about compression dictionaries is FALSE?

A. Page-level dictionaries are stored in hidden rows within the table.

B. Table-level dictionaries are stored in hidden rows within the table.

C. Page-level dictionaries are automatically created or recreated by the database manager as pages are filled.

D. Table-level dictionaries are automatically created or recreated as by the database manager as pages are filled.

Answer: D

Explanation:

QUESTION NO: 11

What advantage do block-based buffer pools offer compared to regular buffer pools?

A. They improve the performance of sequential prefetching by reading contiguous pages from disk and writing them into contiguous pages in a buffer pool.

B. They improve the performance of random I/O by reading non-contiguous pages from disk and writing them into near-contiguous free space in a buffer pool.

C. They improve the performance of vectored reads by retrieving non-contiguous pages from disk, combining them into extent-sized blocks, and writing the blocks into contiguous pages in a buffer pool.

D. They improve the performance of scattered writes by retrieving non-contiguous pages from a buffer pool, combining them into extent-sized blocks, and writing the blocks into contiguous pages on disk.

Answer: A

Explanation:

QUESTION NO: 12

The following query is executed frequently against the SALES table: SELECT sales_person, region FROM sales WHERE region = 'Quebec' Which statement will create an index for optimal query performance?

A. CREATE INDEX sales_idx ON sales (region)

B. CREATE INDEX sales_idx ON sales (sales_person)

C. CREATE INDEX sales_idx ON sales (region, sales_person)

D. CREATE INDEX sales_idx ON sales (region) INCLUDE (sales_person)

Answer: C

Explanation:

QUESTION NO: 13

Which monitoring tool can be used to find out how many data pages have been read from the table space containers for regular, large, and temporary table spaces?

A. The MON_DB_SUMMARY administrative view.

B. The MON_BP_UTILIZATION administrative view.

C. The MON_GET_ACTIVITY_DETAILS table function.

D. The MON_GET_EXTENT_MOVEMENT_STATUS table function.

Answer: B

Explanation:

QUESTION NO: 14

What is the FAILARCHPATH database configuration parameter used for?

A. To specify a path that DB2 will use to look for archive log files that are needed for a rollforward recovery operation.

B. To specify a path to which DB2 will attempt to store a copy of archive log files to protect against accidental deletion.

C. To specify a path to which DB2 will attempt to store a copy of both active and archive log files to protect against disk failure and power outages.

D. To specify a path to which DB2 will attempt to store archive log files if they cannot be written to the primary or secondary archive destination because of a media problem.

Answer: D

Explanation:

QUESTION NO: 15

An auxiliary HADR standby database must use which HADR sync mode?

A. SYNC

B. ASYNC

C. NEARSYNC

D. SUPERASYNC

Answer: D

Explanation:

QUESTION NO: 16

What is the recommended method for removing old backup images and log files for a DB2 10.1 database?

A. Manually delete old backups and logs.

B. Utilize the PRUNE LOGFILE PRIOR TO command to remove old backups and logs.

C. Utilize the PRUNE HISTORY AND DELETE command to remove old backups and logs.

D. Set the DB2PRUNE_ACTION_LOG configuration parameter to a desired date before generating a new backup.

Answer: C

Explanation:

QUESTION NO: 17

Which diagnostic tool can be used to collect metrics on individual SQL statement executions?

A. db2dart

B. A snapshot monitor

C. A statement event monitor

D. The MON_GET_UNIT_OF_WORK_DETAILS table function

Answer: C

Explanation:

QUESTION NO: 18

Which statement about DB2 Merge Backup is FALSE?

A. It can be used to merge offline backups to build full offline table space backups.

B. It can be used to merge SNAPSHOT and TSM tape backup images to create a full database backup image.

C. It can be used merge multiple offline database backups to build a cumulative single offline database backup.

D. It can be used to merge backups on a machine that is different from the machine where the backup has been created in stand-alone mode.

Answer: B

Explanation:

QUESTION NO: 19

What two types of information can be obtained with the MON_GET_TABLESPACE monitor table function? (Choose two.)

A. Table space available space

B. Table space read and write activity

C. Table space container assignments

D. Table level activity within a table space

E. Table space backup and recovery information

Answer: A,B

Explanation:

QUESTION NO: 20

Which Workload Manager (WLM) objects can the COLLECT ACTIVITY DATA clause be used with?

A. Data class, work class, and a limit

B. Data class, action class, and a threshold

C. Service subclass, action class, and a limit

D. Service subclass, work class, and a threshold

Answer: D

Explanation:

QUESTION NO: 21

Where is the HADR_TARGET_LIST configuration parameter set?

A. On the primary database only.

B. On all participating databases.

C. On the tertiary standby databases.

D. On the auxiliary standby databases only.

Answer: B

Explanation:

QUESTION NO: 22

What is the default user workload class that is created for each database table?

A. DEFAULT_USER

B. SYSUSERDEFAULTWLM

C. SYSDEFAULTUSERWORKLOAD

D. SYS_DEFAULT_USER_WORKLOAD

Answer: C

Explanation:

QUESTION NO: 23

Which statement about dropped table recovery is correct?

A. Multiple dropped tables can be recovered with a single table space-level restore operation.

B. By default, the DROPPED TABLE RECOVERY option is off when a new table space is created.

C. The dropped table recovery feature lets you recover only dropped table data with a database-level restore operation.

D. For a dropped table to be recoverable, the table space in which the table resides must have the DROPPED TABLE RECOVERY option turned on.

Answer: D

Explanation:

QUESTION NO: 24

In which situation would you NOT use a redirected restore?

A. To redefine the paths of a defined storage group.

B. To move table space containers onto different physical devices.

C. To restore a backup image from one operating system to another.

D. To restore a backup image to a target machine that is different from the source machine.

Answer: C

Explanation:

QUESTION NO: 25

A table space creation operation was unsuccessful because DB2 failed to allocate a container in a path. Which diagnostic tool can be used to help resolve the problem?

A. db2dart

B. db2diag

C. db2caem

D. db2fmcu

Answer: B

Explanation:

QUESTION NO: 26

What must you do before an online table space backup operation can be performed?

A. Create a full database backup image.

B. Create one or more tables in the table space.

C. Create an incremental backup image of the table space.

D. Set the DB2_ENABLE_ONLINEBKUP registry variable to ON.

Answer: A

Explanation:

QUESTION NO: 27

Which category must be enabled before db2audit will record the creation of new buffer pools?

A. OBJMAINT

B. SECMAINT

C. CHECKING

D. OBJCREATE

Answer: A

Explanation:

QUESTION NO: 28

What event category is used with the db2audit command to capture SQL data, such as host variable and parameter marker values, that are associated with an SQL statement?

A. AUDIT

B. VALIDATE

C. EXECUTE

D. CONTEXT

Answer: C

Explanation:

QUESTION NO: 29

A database administrator created all of the necessary federated objects for an SQL Server data source in a federated system. Which steps must be performed if the data types of several columns in the SQL Server data source are changed?

A. Drop the current data type mapping.Create the new data type mapping.

B. Drop the nickname to the table.Create the new data type mapping.Recreate the nickname to the table.

C. Drop the current data type mapping.Create the new data type mapping.Drop the nickname to the table.Recreate the nickname to the table.

D. Drop the current user mapping.Drop the current data type mapping.Create the new user mapping.Create the new data type mapping.Recreate the nickname to the table.

Answer: B

Explanation:

QUESTION NO: 30

To configure instance-level auditing with db2audit, which authority is required?

A. SYSADM

B. SYSCTRL

C. SECADM

D. SYSMAINT

Answer: A

Explanation:

QUESTION NO: 31

What are two supported methods for providing a low latency, high speed interconnect between DB2 members and the cluster caching facility (CF) in a DB2 pureScale environment? (Choose two.)

A. InfiniBand with RDMA (IB)

B. SCSI RDMA Protocol (SRP)

C. iSCSI Extensions for RDMA (iSER)

D. RDMA over Converged Ethernet (RoCE)

E. Internet Wide Area RDMA Protocol (iWARP)

Answer: A,D

Explanation:

QUESTION NO: 32

On which two operating systems can DB2 pureScale run? (Choose two.)

A. AIX

B. Linux

C. HP-UX

D. Solaris

E. Windows

Answer: A,B

Explanation:

QUESTION NO: 33

Which statement about data type mappings is true in a federated system?

A. The Oracle DOUBLE data type maps to the DB2 type FLOAT by default.

B. Data types can be mapped in both directions between a remote and local data type.

C. Cast functions can be pushed down to remote servers even if a counterpart function does not exist there.

D. For relational data sources, the degree to which you can override existing data type mappings or create mappings is limited.

Answer: B

Explanation:

QUESTION NO: 34

Which statement about IBM WebSphere Information Integrator Q Replication is correct?

A. It reads the DB2 recovery log and stores committed transactional data in staging tables.

B. It reads the DB2 recovery log and converts committed transactional data to SQL statements.

C. It provides the lowest latency of data in the target databases and lowest throughput of changed data to the target databases.

D. It provides the lowest latency of data in the target databases and highest throughput of changed data to the target databases.

Answer: D

Explanation:

QUESTION NO: 35

Which table configuration CANNOT utilize multi-temperature storage?

A. A range-partitioned table utilizing multiple table spaces that map to DMS containers.

B. Multiple tables containing the same data at different ages placed into the same table space.

C. Multiple tables containing the same data at different ages placed into separate table spaces.

D. A range-partitioned table utilizing multiple table spaces that map to individual storage groups.

Answer: B

Explanation:

QUESTION NO: 36

What is the preferred method for reclaiming unused storage in a DMS table space?

A. Perform an ALTER TABLESPACE...REDUCE operation.

B. Perform a REORG on each table within the table space, then perform an ALTER TABLESPACE...REDUCE operation.

C. Perform an ALTER TABLESPACE...LOWER HIGH WATER MARK operation, and then an ALTER TABLESPACE...REDUCE operation.

D. Perform a REORG on each table within the table space, then perform an ALTER TABLESPACE...LOWER HIGH WATER MARK operation.

Answer: C

Explanation:

QUESTION NO: 37

If the following statements are executed: CREATE STOGROUP sg_hot ON '/path1', '/path2', '/path3'; CREATE STOGROUP sg_med ON '/path4', '/path5', '/path6'; CREATE TABLESPACE tbsp1 USING STOGROUP sg_hot; What is the recommended way to move table space TBSP1 from storage group SG_HOT to storage group SG_MED?

A. Issue an ALTER TABLESPACE statement with the 'USING sg_med' option.

B. Issue an ALTER STOGROUP statement with the 'MOVE tbsp1 TO sg_med' option.

C. Perform a table space RESTORE operation with the 'USING STOGROUP sg_hot' option.

D. Use the ADMIN_MOVE_TABLESPACE procedure to move table space TBSP1 to SG_HOT.

Answer: A

Explanation:

QUESTION NO: 38

A DB2 9 database named MYDB that does not use automatic storage was migrated to DB2 10.1. When will a default storage group be defined for this database?

A. The first time the database is accessed after the migration.

B. The first time the CREATE STOGROUP statement is executed.

C. When the ALTER DATABASE mydb CONVERT statement is used to convert the database to an automatic storage database.

D. When the ALTER TABLESPACE statement is used to convert the table space that holds the system catalog for the database to automatic storage.

Answer: B

Explanation:

QUESTION NO: 39

The following statement was used to create a table space named USER_TMP in a non-partitioned database: CREATE USER TEMPORARY TABLESPACE user_tmp MANAGED BY SYSTEM USING (?mnt/fsystem4? ?mnt/fsystem5? Which operation can be performed on the USER_TMP table space with an ALTER TABLESPACE statement?

A. New storage containers can be added to it.

B. A different buffer pool can be associated with it.

C. A hard limit can be placed on the size to which its storage containers can grow.

D. Its high water mark can be lowered and its size can be reduced to the high water mark.

Answer: B

Explanation:

QUESTION NO: 40

A DBA wants to create a set of tables in a storage group named SG_1, but no table spaces have been assigned to this group. What kind of table spaces must the DBA assign to the storage group before the desired tables can be created?

A. Automatic storage (AS) table spaces

B. System managed space (SMS) table spaces

C. Database managed space (DMS) table spaces that use FILE containers

D. Database managed space (DMS) table spaces that use RAW containers

Answer: A

Explanation:

Site Search:

Close

Close
Download Free Demo of VCE
Exam Simulator

Experience Avanset VCE Exam Simulator for yourself.


Simply submit your e-mail address below to get started with our interactive software demo of your free trial.


Enter Your Email Address

Free Demo Limits: In the demo version you will be able to access only first 5 questions from exam.