A2090-612 - Assessment: DB2 10 DBA for z/OS Assessment

Go back to IBM

Example Questions

What START TRACE command provides detailed lock suspend and lock contention trace information? What is a potential consideration when using triggers? 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? Given the following view definition: CREATE VIEW EMPD AS (SELECT D.DEPTNAME, 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? 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); 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? 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? Unnecessary REORGs of indexes may be avoided by which of the following? 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.) 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? 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? Altering the DSSIZE is allowed for what type of table space? Which DB2 online utility job output provides you with information about the BACKUP SYSTEM history? For monitoring space growth over time, which statistics should NOT be considered? Using the -SET LOG command, what logging characteristics CANNOT be modified? Which clone table statement is correct? 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)? 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? 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? What trace class will show deadlock information? 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? The EXPLAIN STMTCACHE ALL statement provides information about SQL tuning. Which information is part of the DSN_STATEMENT_CACHE_TABLE? 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? DB2 does not take system checkpoints in which circumstance? 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? Which of the following statements is NOT correct? In using plan stability, what storage space requirement should be of carefully monitored? What is a consideration when specifying DATA CAPTURE CHANGES? 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? What would be a reason for altering the clustering index of a table? A table space is in AREO*. What does this status mean? What WLM action will establish performance objectives for DDF threads and their related address space? 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? To monitor the storage used above and below the 2GB bar, what must occur? What is the one significant difference between sequence objects and identity columns? You want to ALTER a table space specifying MAXPARTITIONS 256. For which table space type does the ALTER fail? Which attribute(s) determine the absolute maximum number of partitions for a partition-by-growth table space? 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? 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?