db2 udb v8.1 for z/os database administration

1.Running accounting classes 1 and 2 for performance analysis is recommended for which of the following reasons?
A.DB2 time can be separated from application time.
B.A detailed view of the access path is reported.
C.Detail is reported on the I/O wait time.
D.Detail is reported down to the package and DBRM level.
Correct:A
2.The following query can be used to check the catalog for consistency: SELECT DBNAME, NAME FROM SYSIBM.SYSTABLESPACE TS WHERE NOT EXISTS (SELECT * FROM SYSIBM.SYSDATABASE DB WHERE DB. NAME = TS.DBNAME); Which specific condition is this query testing?
A.It validates all of the columns in SYSTABLESPACE.
B.It finds all of the table spaces in SYSDATABASE that do not have any rows in SYSTABLESPACE.
C.It finds all of the databases in SYSDATABASE that do not have corresponding rows in SYSTABLESPACE.
D.It finds all of the databases in SYSTABLESPACE that do not have corresponding rows in SYSDATABASE.
Correct:D
3.Periodic incremental image copies are taken on a regular basis. Which of the following would best help reduce the recovery time?
A.Purge old log records
B.Purge old incremental copies
C.Take more frequent incremental image copies
D.Merge the incremental copies with the last full copy
Correct:D
4.A newly created partitioned table space has been created with the DEFINE NO attribute. Which of the following is the best way to ensure partition parallelism when initially loading the table?
A.No special considerations needed. Partition parallelism will always be enforced.
B.For a partitioned table space with DEFINE NO, the initial load cannot use partition parallelism.
C.Run a load job without specifying the PART n clause. DB2 will automatically use partition parallelism when possible.
D.Run a load job with an empty data file for the first partition. Then run the load jobs for each partition in parallel.
Correct:D
5.To minimize storage usage in a stored procedure address space, which of the following parameters could be altered?
A.APPLENV
B.CTHREAD
C.NUMTCB
D.STORTIME
Correct:C
6.A significant increase in getpages reported in performance trace output for a SELECT statement is most likely caused by
A.a change in access path
B.a decrease in bufferpool sizes
C.a run-time reduction in parallel degree
D.normal insert activity on the objects involved
Correct:A
7.The following command is issued: -DIS DB(DBTST1A) SPACE(*) RESTRICT LIMIT(*) The result of the command shows that TS1 has the status RW,CHKP,COPY. Which of the following utilities should the DBA execute first to ensure appropriate data integrity and successful execution in removing the restrictions in a production environment?
A.CHECK DATA
B.COPY TABLESPACE
C.REPAIR NOCOPYPEND
D.REPAIR NOCHECKPEND
Correct:A
8.A developer with authorization-id DEV1 needs to define a trigger on a table named ACCOUNT in order to support logic to determine the state of the data before it is inserted into the table. Which statement grants a privilege that is necessary to perform the action?
A.GRANT TRIGGER ON TABLE ACCOUNT TO DEV1
B.GRANT CREATE TRIGGER ON TABLE ACCOUNT TO DEV1
C.GRANT BEFORE TRIGGER ON TABLE ACCOUNT TO DEV1
D.GRANT SELECT TRIGGER ON TABLE ACCOUNT TO DEV1
Correct:B
9.A table encounters heavy insert processing every night. Although the number of inserts remains unchanged, the time that the insert processing takes increases each night. What should the DBA examine?
A.DB2 log parameters
B.Free space allocation
C.Buffer pool thresholds
D.Primary space allocation
Correct:B
10.In the PLAN_TABLE for an SQL statement, the ACCESSTYPE is "N" and the METHOD="2". Given these PLAN_TABLE values, which type of access path is DB2 using to retrieve the data?
A.A table space scan with a sort
B.A non-matching index scan with a sort
C.A two-column match in a nested loop join
D.IN-list index access with merge scan join
Correct:D
11.A DBA has detected that incorrect updates to the PAYROLL table in the PERSONNEL database were made recently. In order to determine what application is making these updates, the DBA starts an AUDIT trace. Which of the following is necessary to identify all authorization-ids that update this table in the future?
A.The AUDIT TRACE is sufficient
B.ALTER TABLE PAYROLL AUDIT CHANGES
C.ALTER TABLE PAYROLL AUDIT UPDATES
D.ALTER DATABASE PERSONNEL AUDIT ALL
Correct:B
12.To recover a DB2 subsystem at a remote site, tape backups of all user data are required at the recovery site. The DBA currently has local site image copy on disk. Which of the following should the DBA do next?
A.Invoke the COPY utility to create a tape remote site backup from the existing disk image copy.
B.Run IEBGENER to copy the existing disk image copy to tape and manually register this copy in SYSCOPY.
C.Run the COPYTOCOPY utility to create a tape image copy for the remote site from the original disk local image copy.
D.Run the DSN1COPY utility to create a tape image copy for the remote site from the original disk local backup and manually register the copy in SYSCOPY.
Correct:C
13.Resource class DSNR is used to protect access to DB2 from different environments. Which of the following profile names in the DSNR resource class could be used to allow access to DB2 subsystem DSN1 from IMS?
A.DSN1.DLI
B.DSN1.IMS
C.DSN1.MASS
D.DSN1.SASS
Correct:C
14.What feature is NOT an advantage of utilizing WLM-established address spaces for stored procedures?
A.Stored procedures can run as a SUB program.
B.Incoming request for stored procedures are handled in a first-in, first-out order.
C.Stored procedures inherit the z/OS dispatching priority of the DB2 thread that issues the CALL statement.
D.Stored procedures can access protected resources with the authority of the creator of the stored procedure.
Correct:B
15.A single index is defined with two columns: COL1 and COL2. Which of the following WHERE clauses is eligible to utilize this index for matching multi-index access?
A.WHERE COL1 = 1 OR COL2 = 'AB'
B.WHERE COL1 IN (1, 2, 3, 4, 5)
C.WHERE COL1 = 1 OR (COL1 <> 2 AND COL2 = 'AB')
D.WHERE COL1 = 1 OR (COL1 = 2 AND COL2 <> 'AB')
Correct:D
16.A DISPLAY of a table space shows that there are pages in the LPL. Which of the following utilities CANNOT be run on this object?
A.REORG
B.REPORT
C.REBUILD INDEX
D.REPAIR with the SET statement
Correct:A
17.A nightly batch job experiences frequent deadlocks with other DB2 jobs. Which of the following should the DBA try to minimize the deadlocks?
A.Rebind the batch job with repeatable read
B.Increase the log frequency in the batch job
C.Increase the DB2 system checkpoint frequency
D.Decrease the time between commits in the batch job
Correct:D
18.When DB2 is started at a remote site for disaster recovery purposes, the following DSNZPARM value should be in effect: sitetype=recoverysite Which of the following best describes the meaning of this parameter?
A.The site is named as an alternative for recovery purposes.
B.Image copies are made for local recovery but can be used at the recovery site.
C.Image copies are made for recovery site recovery but can be used at the local site.
D.Image copies are made for remote site recovery but have to be manually registered in syscopy using DSNJU003 to be accepted.
Correct:A
19.The following statement is executed: ALTER TABLESPACE DB2CERT.CERTTS LOCKSIZE ANY FREEPAGE 10 Which of the following is the result of the statement?
A.DB2 chooses the lock size and there is one free page after each 10 pages.
B.DB2 chooses the lock size and there is 10 percent free space in each page.
C.DB2 chooses the LOCKMAX value and there is 10 percent free space in each page.
D.DB2 chooses the maximum number of locks and there is one free page after each 10 pages.
Correct:A
20.The DBA has noticed an increase in FARINDREF and NEARINDREF values from running RUNSTATS. Which of the following is the most likely cause?
A.Updates to variable-length columns resulting in an increase in the table row length.
B.Updates to variable-length columns in the row length of a padded index.
C.Lack of available freespace in the index causing page splits on inserts.
D.Lack of available freespace causing inserts to be out of clustering sequence.
Correct:A
21.Which of the following will prevent the RECOVER utility from performing fallback processing?
A.The NOFALLBACK RECOVER option is specified.
B.A QUIESCE was done before the COPY was created.
C.A REORG LOG NO or a LOAD LOG NO was done before the COPY was created.
D.A MODIFY STATISTICS DATE(*) is run before the RECOVER utility is executed.
Correct:C
22.One of the bootstrap data sets (BSDSs) has been damaged, which causes DB2 to operate in single BSDS mode. If DB2 is stopped, it will not restart until dual BSDS mode is re-established. What are some of the steps involved in restoring the dual BSDS mode?
A.* Remove the damaged BSDS * Run DSNJU003 to correct the missing BSDS information using records from the archive log * Run DSNJU003 to list the contents
B.* Rename the damaged BSDS * Define a new BSDS * Use REPRO to copy the BSDS from the non-damaged BSDS to a newly defined BSDS * Run DSNJU004 to list the contents
C.* Rename the damaged BSDS * Define a new BSDS * Use REPRO to copy the BSDS from the active log to a newly defined BSDS * Run DSNJU003 to delete the old archive log records
D.* Delete the damaged BSDS * Define a new BSDS * Restore the image copy of the BSDS from the log to a newly defined BSDS * Run DSNJU004 to delete the old archive log records
Correct:B
23.What type of index is defined by the following syntax? CREATE UNIQUE INDEX DB2USER1.TESTCN2X ON DB2USER1.TEST_CENTER (TEST_CENTER_ID ASC) USING STOGROUP CERTSG PIECESIZE 512K
A.A sparse index
B.An index that is not SMS managed
C.A nonpartitioned secondary index (NPSI)
D.A data-partitioned secondary index (DPSI)
Correct:C
24.A DBA is implementing a new group of applications into production and wants to estimate the impact on the size of the EDM pool. Which of the following catalog tables can the DBA query to obtain this estimate?
A.SYSDBRM
B.SYSPACKAGE
C.SYSPACKSTMT
D.SYSROUTINES
Correct:B
25.Which TWO of the following phrases could be used in creating a table partition that ends before 12/01/2003 (mm/dd/yyyy)?
A.NUMPARTS 12
B.VALUES("11/30/2003")
C.VALUES("12/01/2003")
D.ENDING AT("11/30/2003")
E.END PARTITION("12/01/2003")
Correct:B D
26.A DBA just added a table check constraint to a table. Which of the following utilities needs to be executed for the table to be usable?
A.CHECK DATA
B.CHECK INDEX
C.CHECK CONSTRAINT
D.INTEGRITY CHECK
Correct:A
27.USER01's primary authorization-id is connected to a RACF group (or other external security system group). To give USER01 access to a newly created table, the SELECT privilege should be granted to
A.the table owner
B.RACF (or external security system)
C.the package that accesses the table
D.a secondary authorization-id of USER01
Correct:D
28.Which of the following keywords of the DISPLAY THREAD command limits the display of information to only distributed threads?
A.SITE
B.TYPE
C.DETAIL
D.LOCATION
E.CONNECTION
Correct:D
29.A large number of queries will be based on a common set of aggregates on a few dimension columns. Due to the large amount of data to be processed, the DBA wants to avoid recomputation and reuse the aggregated query results. Which of the following would best fit the DBA's requirement?
A.Union in view
B.Star schema fact table
C.Materialized query table
D.Declared global temporary table
Correct:C
30.The DBA is designing a two-column index to support a query that has two equal predicates for table T2, one local and one join predicate. Which of the following is true if the goal is to obtain a two-column index match on T2 as the inner (new) table of a nested loop join?
A.The index can have either column as the leading index column.
B.The index must have the join predicate as the leading index column.
C.The index must have the local predicate as the leading index column.
D.The index can never match on both columns given the table join sequence.
Correct:A