A DBA is coordinating a DB2 disaster recovery and wants DB2 to
- Consider the table spaces unavailable until the disaster recovery is complete
- Do the necessary log processing
Which of the following should the DBA use to accomplish this?
Issue -START DB2 ACCESS(FORCE).
Issue -START DB2 ACCESS(MAINT).
Set DSNZPARM parameter DEFER ALL, and then issue -START DB2.
Set DSNZPARM parameter RESTART ALL, and then issue -START DB2.
The audit trace cannot capture information for which of the following actions?
Access to catalog tables
Explicit GRANTs and REVOKEs
Start of a utility job
A database administrator needs the ability to create an object in a buffer pool (BP1). Which GRANT statement would provide the necessary privilege to perform this action?
GRANT BUFFERPOOL1 USAGE TO authorization-id
GRANT EXECUTE ON BUFFERPOOL TO authorization-id
GRANT USE OF BUFFERPOOL BP1 TO authorization-id
GRANT CREATIN BUFFERPOOL BP1 TO authorization-id
Which of the following statements about the INCURSOR option of the LOAD utility is false?
The INCURSOR option can be specified with the SHRLEVEL CHANGE option.
The cursor must be defined before it is used by the LOAD utility.
The specified cursor can be used with the DB2 UDB cross-loader function.
Data cannot be loaded into the same table on which the cursor is defined.
To verify the integrity of DB2 directory and catalog table spaces and to scan for broken links, which of the following should be run?
If materialization has occurred for an SQL statement, which of the following will the PLAN_TABLE show?
The view or nested table expression name and TABLE_TYPE of 'W'
Only query blocks with the base table names and TABLE_TYPE of 'M'
The base table name and TABLE_TYPE of 'W'
An entry with TABLE_TYPE of 'M'
To allow the image copy of an index to be used by the RECOVER utility, which of the following procedures should not be performed?
Prepare and test incremental image copy jobs.
Prepare and test full image copy jobs.
Prepare and test recovery jobs.
Create or alter the index with the COPY YES option.
The statement CREATE LOB TABLESPACE ts1 creates a table space in which database?
The results of the command -DISPLAY UTILITY(*) will provide all the following information except
Type of utilities running
Status of the utility jobs
Estimated elapsed time of completion for the utility jobs
Estimate of how much processing has completed for the utility jobs
Compression dictionary memory is allocated within which of the following?
MSTR address space
DBM1 address space
DB2 automatically converts to table-controlled partitioning in all the following conditions except
Altering the table to add a new partition
Altering the table space to specify NUMPARTS option
Altering the table to rotate a partition from first to last
Dropping the table space index that is defined with PART VALUES
When the command DISPLAY DATABASE ADVISORY is used without the RESTRICT option, which of the following cannot be determined?
A table space of a base table is in CHECK-pending (CHKP) status.
A LOB table space is in auxiliary warning (AUXW) status.
An index space is in the REBUILD-pending (ARBDP) status.
An index space is in the informational COPY-pending (ICOPY) status.
Which of the following applies to an identity column but not to a sequence object?
Can be restarted
Can be a decimal or an integer
Is associated with a user table
Is used to generate sequence values
Which of the following is a main performance advantage of multirow INSERT of non-LOB data in a client/server environment?
There is only one network flow from the client to the server.
The data is inserted onto only one data page, so only one I/O needs to occur.
Only one log record is cut, because multiple data rows are inserted as a single block.
The data is inserted directly from the communication buffer to the data page in one move.
If the following SQL EXEC SQL UPDATE T1 SET C1 = C1 +1; is executed, which of the following GET DIAGNOSTICS statements could be used to determine how many rows were updated?
EXEC SQL GET DIAGNOSTICS :rcount = ROW_COUNT;
EXEC SQL GET DIAGNOSTICS :rcount = SQLERRD;
EXEC SQL GET DIAGNOSTICS :rcount = NUMBER;
EXEC SQL GET DIAGNOSTICS :rcount = DB2_ROW_COUNT;
Which of the following is true regarding the recovery of the DB2 catalog and directory?
All objects that are related to SYSUTILX can be recovered in parallel.
All catalog and directory objects can be recovered in parallel.
All catalog and directory objects can be recovered in a single job step.
The catalog and directory objects after SYSDBASE can be recovered in parallel.
A materialized query table (MQT) is defined with the MAINTAINED BY SYSTEM option. Which of the following can be used to populate the MQT?
When trying to determine whether a reorganization of an index is necessary, which of the following would not be of use to you?
SYSIBM.SYSINDEXPART column LEAFDIST
SYSIBM.SYSINDEXES column CLUSTERING
SYSIBM.SYSINDEXES column CLUSTERRATIOF
SYSIBM.SYSINDEXPART columns LEAFNEAR and LEAFFAR
Which of the following REORG parameters will greatly reduce the elapsed time of the utility phase that alternates application usage from the original data sets to the shadow data sets of a 12-partition table space?
Data in a DB2 table is stored in VSAM data sets. How can sensitive DB2 data in these VSAM data sets be protected from unauthorized user access outside of DB2?
Encode the data in the DB2 tables, using compression.
Encrypt the data by using DB2 edit procedures or field procedures.
Use RACF profiles and permits, or their equivalents, for the data sets.
Use DB2 GRANT and REVOKE statements to allow certain DB2 users access to the data.
The table columns CITY and STATE are used in a compound SQL WHERE clause. The optimizer might incorrectly estimate the number of qualifying rows in the table if
Correlation statistics are not collected
The columns are defined as variable character
These columns form a composite partitioning key
A single index, which contains both columns, is created
After altering a check constraint on a table, which of the following utilities should you run before the table can be used?
In the plan table, METHOD = 2 signifies
Which of the following is the most important factor to consider when choosing the clustering index?
Random data access
Sequential data access
How the data is partitioned
How many partitions are defined for the table space
Which of the following REORG utility options can be used to determine whether a table space needs to be reorganized?
The command -DIS DB(DBTST1A) SPACE(*) RESTRICT LIMIT(*) is issued; the output shows that index space IX1 has the following status: RW,RBDP. Based on the status information, which of the following is not true about the scope of unavailability for dynamic SQL?
Deletes are allowed for the table rows.
Queries are allowed for the table, but DB2 does not choose an index in RBDP for an access path.
Updates and inserts are allowed for table rows, even if their corresponding non-unique indexes are in RBDP status.
Updates and inserts are allowed for table rows, causing DB2 to insert keys into a unique index that is in RBDP status.
At a conceptual level, an "entity" on a logical model will often map to which DB2 object?
Which of the following does the PAGE_RANGE column of the PLAN_TABLE indicate?
Access that is limited to a subset of the table or index partitions
The start and stop range for a matching index scan
Index screening that has occurred for the query
The range of pages for each parallelism group
The PAYROLL table is defined with AUDIT ALL. A TSO user has user ID DBA007, with primary authorization ID JONES and secondary authorization ID PAYGROUP. This user updates a row in the PAYROLL TABLE after issuing a SET CURRENT SQLID = 'DBA007'. In this situation, what authorization ID is used in a DB2 audit trace record?
When you are defining a primary key and before the table is usable, which of the following must be created?
A unique index, using the primary-key columns
A duplicate index, using the primary-key columns
A clustering index, using the primary-key columns
A partitioning index, using the primary-key columns
Which of the following is not considered an essential disaster-recovery element?
DB2 load and exit libraries
Recent image copies of all database objects
REPORT RECOVERY utility output for all objects
Which of the following is true when you are preparing to do a system-level point-in-time recovery to an arbitrary point in time?
The SET LOG SUSPEND command is required prior to executing a BACKUP SYSTEM FULL.
To restore the DB2 system to an arbitrary point in time, a BACKUP SYSTEM FULL utility is required.
To restore the DB2 system to the point in time of a prior system-level backup, a cold start is required.
To restore the DB2 system to an arbitrary point in time, running a BACKUP SYSTEM DATA ONLY is sufficient.
Which of the following single-table predicates is considered stage 1?
COL NOT EQUAL value
COL NOT IN (correlated subquery)
COL NOT EQUAL (correlated subquery)
Which of the following ALTER statements could necessitate an application program modification?
ALTER TABLE PROD.EMPLOYEE PRIMARY KEY(LAST_NAME)
ALTER TABLE PROD.EMPLOYEE ALTER COLUMN LAST_NAME CHAR(45)
ALTER TABLE PROD.EMPLOYEE ADD PART 15 VALUES("2004-01-19")
ALTER TABLE PROD.EMPLOYEE ADD MATERIALIZED QUERY (fullselect)
What type of cursor can take advantage of block fetching in a distributed environment to reduce the number of messages sent across the network?
Cursors that update data
Cursors that delete data
Cursors that are defined FOR UPDATE OF
Cursors that do not update or delete data
A subsystem has been successfully converted to version 8. Which of the following would result if the -DISPLAY GROUP DETAIL command were run and it reported a mode of E?
The system is in enable new-function mode.
The catalog and directory are in Unicode and can accept long names.
The catalog and directory are in Unicode and can accept long names; new V8 functions are available.
The catalog and directory are in Unicode and can accept long names; data has been converted to Unicode.
Which two of the following are design considerations for the version 8 Data Partitioned Secondary Indexes (DPSI)?
The DPSI indexes are defined as nonunique.
The DPSI index requires its own storage group.
The DPSI index can be defined for a single partition.
The DPSI index helps eliminate BUILD2 phase utility processing.
The DPSI index cannot be used with a partitioning or type 2 index in an access path.
Which of the following statements is false when the command STOP DB2 MODE(QUIESCE) is entered?
There are no pending writes.
There are no outstanding units of recovery.
DB2 does not need to access the data sets on restart through the log.
DB2 does not allow currently executing programs to complete processing.
Which of the following keywords would be used to collect frequency distribution statistics on nonindexed columns?
KEYCARD and FREQVAL
COLGROUP and FREQVAL
Which of the following data types allows for direct row access (PRIMARY_ACCESSTYPE=D)?
The design requirement for the TRANS table is to divide the data into 13 partitions, each of which contains one month of data that is partitioned on the transaction date. The user normally accesses the transaction table through the customer account column. The design needs to support a critical requirement for availability on the trANS table and to allow the DBA to run a SHRLEVEL NONE REORG job at the partition level, with minimal disruption to data availability. To facilitate reorganization, which of the following would best satisfy the design requirements?
Create a partitioning index on the transaction date and a secondary index on the customer account.
Create a single partitioning, clustering index on the combined transaction date and customer account columns.
Create a table-controlled partitioned table that partitions on the transaction date. Also, create a partitioned clustering index on the customer account.
Create a table-controlled partitioned table that partitions on the transaction date. Also, create a partitioning index on the combined columns transaction date and customer account.
If a secondary authorization ID has been established for the DBA group DBAGRP, how can it be used to qualify unqualified objects in an SQL statement?
SET CURRENT PATH = 'DBAGRP'
SET CURRENT SQLID = 'DBAGRP'
SET CURRENT AUTHID = 'DBAGRP'
SET CURRENT USERID = 'DBAGRP'
A DB2 data sharing group consists of two members: DSN1 and DSN2 on z/OS logical partitions zOS1 and zOS2, respectively. zOS1 has an unplanned outage, causing member DSN1 to fail. Now applications running on DSN2 cannot access some of the DB2 data, owing to retained locks that are held by member DSN1. Which of the following will get these retained locks resolved as quickly as possible to allow access to all the application data?
Restart DB2 for member DSN1 on zOS1.
Restart DB2 for member DSN1 on zOS2, using the ACCESS MAINT mode.
Restart DB2 for member DSN1 on zOS2, using the restart-light mode.
Recycle DSN2 and perform a group restart on zOS2 for both members DSN1 and DSN2.
Which of the following statements about the RESTORE SYSTEM utility is true?
Terminate the RESTORE SYSTEM utility by using the TERM UTILITY command.
The RESTORE SYSTEM utility can be run from any member in a data sharing group.
The RESTORE SYSTEM utility restores the logs from the log copy pool and the data from the database copy pool.
If specified, a particular backup version can be used by the RESTORE SYSTEM utility.
Which of the following statistics trace classes provides information about deadlocks and timeouts?
Which of the following DB2 traces could be used by the DBA to assign DB2 costs to individual authorization IDs?
If overall system performance is slow, analysis should begin with which of the following?
Monitoring buffer pool hit ratios
Identifying poorly performing SQL statements
Locating problems at the z/OS level, using RMF
Collecting trace data, using DB2 accounting classes 1, 2, 3, 7, and 8
Which SQL clause provides the necessary privilege to create an object using the volumes VOL1 and VOL2 that are defined to storage group STOGRP1?
USE OF STOGROUP STOGRP1
USE OF VOLUMES (VOL1, VOL2)
CREATE IN STOGROUP STOGRP1
CREATE USING VOLUMES (VOL1, VOL2)
When a CREATE INDEX statement specifies the NOT PADDED clause, which of the following statements is false?
DB2 can use index-only access for the varying-length columns within the index key.
DB2 does not pad any varying-length columns in the index key to their maximum lengths.
Index-key comparisons are faster because DB2 compares pairs of corresponding varying-length columns as the entire key.
Storage requirements for the index key can be reduced because DB2 stores only a 2-byte-length field along with the data.
In a WHERE clause, why are Boolean term predicates often preferred over non-Boolean term predicates?
Non-Boolean term predicates are stage 2 predicates.
The optimizer does not use default filter factors for Boolean term predicates.
Non-Boolean term predicates can be coded only in a query containing inner joins.
Only Boolean term predicates can use matching index access on a single index.
Which of the following parameter settings would allow the buffer pool to contain 20 percent random pages?
VPSEQT = 80
VPSIZE = 20
VPRAND = 20
VPSTEAL = FIFO
Which of the following statements is true when adding a check constraint to a table that is not empty?
The constraint is enforced immediately when it is defined.
The constraint is added to the table description, but its enforcement is deferred until the next bind.
The constraint is enforced or deferred, depending on the contents of the CURRENT PATH special register.
The constraint is enforced or deferred, depending on the contents of the CURRENT RULES special register.
Which of the following will happen when a primary key is dropped by using the DROP PRIMARY KEY clause of the ALTER TABLE?
The dependent tables no longer have foreign keys.
The dependent tables are placed in a CHECK-pending restricted status.
The table is placed in a CHECK-pending restricted status.
The table's primary index is no longer a unique index.
Which of the following is true if the ALTER INDEX CLUSTER statement is used to change an index to be the clustering index for a table?
A table can have multiple clustering indexes.
New data inserted into the table is not placed in clustering order.
Preexisting data rows are immediately put into the new clustering order.
A table must be reorganized to put existing data rows into clustering order.
Which of the following is an invalid WLM command to perform an operation against the application environment?
A DBA wants to check the health of the DB2 catalog or directory. Which of the following utilities should the DBA not use?
Which of the following statements about declared temporary tables is false?
Can have an index
Qualified by SESSION
Materialized in DSNDB07
Not recorded in the catalog
Table space DB1.TS1 has pages in the LPL. Which of the following can be used to start the recovery of the object?
-STA FUNCTION SPECIFIC
-STA DB(DB1) SPACENAM(TS1) ACCESS(RW)
-STA DB(DB1) SPACENAM(TS1) ACCESS(FORCE)
Which of the following utilities can be used to help reduce DB2 I/O?
Which of the following most accurately describes the default buffer pool for a user-defined INDEX?
In referencing a 4,096-partition table space, when would a DBA create an NPSI instead of a DPSI?
If there is a lot of contention when running utilities
When the ability to rotate partitions is needed
When predicates do not exist to allow page range screening
When partitioned data is accessed randomly the majority of the time