A2090-612 - Assessment: DB2 10 DBA for z/OS Assessment
Go back to IBM
What START TRACE command provides detailed lock suspend and lock contention trace information?
START TRACE(PERFM) CLASS(30) IFCID(44,45)
What is a potential consideration when using triggers?
They are not invoked by the LOAD REPLACE utility.
When modeling data to provide for a physical design, which of the following defines the data types for the columns in the DB2 physical table?
Which stored procedures need to be set up properly in order to register an XML schema?
XSR_REGISTER and XSR_COMPLETE
Given the following view definition: CREATE VIEW EMPD AS (SELECT D.DEPTNAME,
No, only a clone of the view EMPD can be updated via an UPDATE statement.
In order to properly monitor locks on a continual basis, which action should be performed but will avoid using excessive CPU resources to gather the information?
Always have statistics classes 1,,3, and 4 and accounting classes 1 and 3 running.
In a normalized design, in third normal form, the entity defined in the logical design is often transformed in the physical design to which of the following?
What is the purpose of the following query? SELECT DBNAME, NAME FROM SYSIBM.SYSTABLESPACE TS WHERE NOT EXISTS (SELECT TP.DBNAME, TP.TSNAME FROM SYSIBM.SYSTABLEPART TP WHERE TP.DBNAME = TS.DBNAME AND TP.TSNAME = TS.NAME);
It identifies which table spaces have missing related rows in SYSIBM.SYSTABLEPART.
Workload Manager (WLM) manages how many concurrent stored procedures can run in an address space and the number of concurrent stored procedures in an address space cannot exceed the value of the NUMTCB parameter. Which statement about the value of NUMTCB is correct?
NUMTCB parameter must be set to 1 for REXX stored procedures.
When creating a stored procedure that will access non DB2 resources using the authorization ID of the user invoking the stored procedure, what value must the SECURITY clause specify?
When is changing an index from non-clustering to clustering NOT allowed?
If the new clustering index is for a table that uses hash organization.
Unnecessary REORGs of indexes may be avoided by which of the following?
Use the SYSIBM.SYSINDEXSPACESTATS table.
Which two parameters would reduce the impact of availability from a REORG with SHRLEVEL CHANGE for a partitioned table space with 16 parts? (Choose two.)
DRAIN_WAIT & RETRY
An object (table space, index space or a physical partition of a table space or index space) is in AREST or RESTP status. If backout activity is not already underway, what should occur?
Issue a RECOVER POSTPONED command.
Your current backup strategy is to run COPY SHRLEVEL CHANGE for all your table spaces each night. You create FULL image copies once a week and INCREMENTAL copies during the rest of the days. After migrating to DB2 10, you plan to create the FULL image copies using FLASHCOPY YES and COPYDDN, that is you create a flash copy image copy (FCIC) and additionally a sequential copy. What happens to the incremental copies that you continue to create on all other days?
The first image copy utility execution after the FULL FCIC and sequential copy created from the FCIC will end up as FULL copy although youspecify the FULL NO statement on the COPY utility control statement.
Altering the DSSIZE is allowed for what type of table space?
Universal table space.
Which DB2 online utility job output provides you with information about the BACKUP SYSTEM history?
REPORT with RECOVERY option
For monitoring space growth over time, which statistics should NOT be considered?
SYSIBM.SYSTABLESPACEDSSIZE, AVGROWLENSYSIBM.SYSINDEXESPIECESIZE, AVGKEYLEN
Using the -SET LOG command, what logging characteristics CANNOT be modified?
Altering DB2 from dual active logging mode to single active logging mode.
Which clone table statement is correct?
The clone and base tables share a table space.
The frequency of data being changed for several tables in the new Billing application is very unpredictable. A different approach to manage Runstats for these objects is desired. To provide additional information on update patterns for the new Billing Application, the DBA can use what information to better understand the unpredictable workload patterns on the tablespaces?
Which connection type can exploit trusted connections?
Which statement applies to the command STOP DB(MYDB*) SP(*) AT(COMMIT)?
Applies to both RELEASE(COMMIT) and RELEASE(DEALLOCATE) at the next COMMIT point.
For which table type could you specify ALTER TABLE tb1 DATA CAPTURE CHANGES?
What should be implemented in order to audit all the activity of the install SYSADM?
A DB2 audit policy should be established, identifying the SYSADM ID to be audited.
If security administration is separated from system and database administration, via zparm, which level of authority is required to define roles or trusted contexts?
The -DIS UTIL(REORGON) command was entered for a REORG utility with SHRLEVEL CHANGE with utilid REORGON. Which statement is NOT true?
It shows how many keys are rejected when issued during the SORTBLD phase.
What trace class will show deadlock information?
Statistic class 3.
Which privilege allows the execution of the EXPLAIN STMTCACHE ALL SQL statement?
Which of the following tasks cannot be done via a single ALTER TABLE statement?
Change a table check constraint on the table.
The EXPLAIN STMTCACHE ALL statement provides information about SQL tuning. Which information is part of the DSN_STATEMENT_CACHE_TABLE?
Number of times an SQL statement is executed.
The new billing application is seeing Class 2 Elapsed Time being made up of mainly Class 3 Synchronous database I/O time. All of the DB2 objects for the new billing application are in BP5. What command can be used to research the details behind the database I/O time associated with the new billing application?
DISPLAY BUFFERPOOL(BP5) LSTATS(ACTIVE) DETAIL
DB2 does not take system checkpoints in which circumstance?
During an application rollback.
Given the following statements have been run successfully and the buffer pools BP1 and BP2 both have the same page size: CREATE TABLESPACE TS1 IN DB1 DEFINE YES BUFFERPOOL BP1 MAXPARTITIONS 15 MEMBER CLUSTER ; CREATE TABLE TB1 (COL1 INT NOT NULL WITH DEFAULT) IN DB1.TS1 ; COMMIT ; ALTER TABLESPACE TS1 BUFFERPOOL BP2 MAXPARTITIONS 20 ; What is the next logical step in the sequence to CREATE an index IX1 on COL1 on TB1?
CREATE INDEX IX1 ON TB1(COL1);
Which of the following statements is NOT correct?
Partition parallelism in UNLOAD is activated with STACK(YES) and UNIT(TAPE).
In using plan stability, what storage space requirement should be of carefully monitored?
What is a consideration when specifying DATA CAPTURE CHANGES?
You cannot turn on DATA CAPTURE CHANGES if the table space is in advisory REORG- pending.
The following DDL statements are executed to create the following objects: CREATE TABLESPACE TS1 BUFFERPOOL BP0 IN DB1; CREATE TABLE USER1.TB1 ( COL1 INTEGER, COL2 VARCHAR(10) ) IN DB1.TS1; CREATE INDEX USER1.IX1 ON USER1.TB1 ( COL2 ) BUFFERPOOL BP0 COPY YES; Next, the following ALTER statements are done (in order): 1) ALTER TABLESPACE DB1.TS1 BUFFERPOOL BP8K0 MAXPARTITIONS 20; 2) ALTER TABLESPACE DB1.TS1 SEGSIZE 64; 3) ALTER INDEX USER1.IX1 BUFFERPOOL BP16K0; Which of the following will materialize all of the changes with the least amount of work?
REORG TABLESPACE DB1.TS1 SHRLEVEL REFERENCE
What would be a reason for altering the clustering index of a table?
To choose a clustering index to favor batch sequential processing.
A table space is in AREO*. What does this status mean?
The table space should be reorganized for optimal performance.
What WLM action will establish performance objectives for DDF threads and their related address space?
Create a WLM service definition that assigns service classes to the DDF threads.
In order to communicate a DISPLAY DATABASE (DB1) SPACENAM (TS1) command execution from one DB2 member to another, which coupling facility structure is used?
Which DB2 trace has potentially the highest level of overhead, and should typically be run in specific situations and only for short durations?
The DB2 performance trace.
To monitor the storage used above and below the 2GB bar, what must occur?
Start a statistics class 1 trace.
What is the one significant difference between sequence objects and identity columns?
Identity columns can be defined on tables and sequence objects cannot.
You want to ALTER a table space specifying MAXPARTITIONS 256. For which table space type does the ALTER fail?
Classic partitioned table space with one table.
Which attribute(s) determine the absolute maximum number of partitions for a partition-by-growth table space?
MAXPARTITIONS, DSSIZE, page size
When these two commands are issued in sequence, one after the other: STOP FUNCTION SPECIFIC(A.B) ACTION(REJECT) STOP FUNCTION SPECIFIC(A.B) ACTION(QUEUE) What will occur?
Function A.B is stopped and queues all access until we start the function or run out of installation timeout.
What is the DSNZPARM that determines the maximum amount of temporary storage in the work file data base for a single user at any given time?
Which DB2 performance feature is designed specifically to reduce the number of indexes that need to be created?