EMT Practice Test

1. Question Content...

---

Question1: Compression is desired for index IX1. The current definition of IX1 is as follows: CREATE INDEX IX1 ON TAB1 (COL1 ASC) USING STOGROUP STG1 BUFFERPOOL BP1 ; Which of the following activities must be performed first in order to compress the existing index IX1?

Question2: The following SQL statement is issued: SELECT col1 FROM tb1 ORDER BY col1 ASC The following results are received: 1111 AAAA Which statement is true?

Question3: You have been asked to find packages that use isolation level UR in dynamic SQL. Which trace will be utilized?

Question4: A LOB column with BLOB(40) INLINE LENGTH 40 has been defined. Which statement is correct?

Question5: Which clone table statement is correct?

Question6: When a table space defined as both partitioned-by-growth (PBG) and MEMBER CLUSTER YES is altered to MEMBER CLUSTER NO by the ALTER TABLESPACE command. Which statement is correct?

Question7: Which DB2 trace has potentially the highest level of overhead, and should typically be run in specific situations and only for short durations?

Question8: What must be done in order to audit read access to a table?

Question9: What is the maximum levels of backup that DB2 plan stability can support?

Question10: How do you enable parallelism in a dynamic SQL statement?

Question11: To monitor the storage used above and below the 2GB bar, what must occur?

Question12: Which stored procedures need to be set up properly in order to register an XML schema?

Question13: When defining a foreign key on a table, which statement is true?

Question14: Which privilege allows the execution of the EXPLAIN STMTCACHE ALL SQL statement?

Question15: When planning for backup/recovery (including disaster recovery), which statement is correct?

Question16: When is changing an index from non-clustering to clustering NOT allowed?

Question17: Which of the following techniques CANNOT be used to implement DB2 based row level authorization in a read-only environment for a specific table?

Question18: The available window to reorganize table spaces is being reduced. What data can provide information to allow for a priority list of candidate table spaces for reorganization based on recent activity with no recent RUNSTATS execution?

Question19: DB2 10 is in enabling-new-function mode* after migrating from Version 8, which statement is correct?

Question20: Which ALTER TABLE statement will generate a new table space version?

Question21: Which statement is true about table check constraints?

Question22: What START TRACE command provides detailed lock suspend and lock contention trace information?

Question23: In which situation does adding a referential constraint place a table space containing data into check- pending status?

Question24: 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?

Question25: Altering the DSSIZE is allowed for what type of table space?

Question26: Which of the following types of table spaces allow multiple tables?

Question27: Which attribute(s) determine the absolute maximum number of partitions for a partition-by-growth table space?

Question28: 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?

Question29: Which of the following types of SQL statements are NOT eligible to be stored in the dynamic SQL statement cache?

Question30: Which option allows the UNLOAD utility to unload LOBs into output files together with non-LOB data?

Question31: What is a consideration when specifying DATA CAPTURE CHANGES?

Question32: Unnecessary REORGs of indexes may be avoided by which of the following?

Question33: For monitoring space growth over time, which statistics should NOT be considered?

Question34: 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?

Question35: Which statement applies to the command STOP DB(MYDB*) SP(*) AT(COMMIT)?

Question36: 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?

Question37: In the CFRM policy, what is the purpose of the PREFLIST?

Question38: You have to design a table and application layout, in which automatic number generation is used. The application also wants to assign database generated numbers in an SQL UPDATE statement. Which statement is true?

Question39: What is one of the first key steps in preparing to use the BACKUP SYSTEM utility?

Question40: For which table type could you specify ALTER TABLE tb1 DATA CAPTURE CHANGES?

Question41: Which DSNZPARM parameter controls the number of locks that may be acquired on a table space?

Question42: In using plan stability, what storage space requirement should be of carefully monitored?

Question43: What would be a reason for altering the clustering index of a table?

Question44: The EXPLAIN STMTCACHE ALL statement provides information about SQL tuning. Which information is part of the DSN_STATEMENT_CACHE_TABLE?

Question45: What is the one significant difference between sequence objects and identity columns?

Question46: What is a potential consideration when using triggers?

Question47: 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?

Question48: Given the following CREATE TABLE statement: CREATE TABLE TB1 (C1 CHAR(4), C2 XML, C3 CLOB (1K)) ; How many VSAM clusters will DB2 create?

Question49: The threads initiated by the distributed transactions are no longer active in DB2, however this situation has caused -904 resource unavailable condition for the other executing threads in DB2. Which command could assist in identifying the remote locations from where such threads were triggered?

Question50: Which two of the following DB2 performance features will ignore clustering in favor of faster insert performance? (Choose two.)

Question51: Given that the following table (TB1) has been created with this statement: CREATE TABLE TB1 (COLA SMALLINT NOT NULL WITH DEFAULT ,COLB CHAR(5) NOT NULL WITH DEFAULT) IN DB1.TS1; Which of the following statements, which alter a table (TB1), will NOT invalidate all the plans, packages and dynamic cache statements associated with the table TB1?

Question52: 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?

Question53: Poorly tuned memory can affect application performance, such as having sequential prefetch turned off.
Which statistic should you monitor from the statistics report to be sure this is not occurring?

Question54: For monitoring space growth over time, which statistics should NOT be considered?

Question55: In order to retain an access path across a rebind which of the following commands could be used?

Question56: Which DB2 performance feature is designed specifically to reduce the number of indexes that need to be created?

Question57: DB2 does not take system checkpoints in which circumstance?

Question58: The UNLOAD utility can be used to do which of the following functions?

Question59: The access path chosen for a query shows a table space scan, when it appears that there is an index available that would provide a more efficient access path. What situation could have caused a table space scan to be chosen instead of the index?

Question60: 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?

Question61: When converting a multiple table segmented table space to partition-by-growth table spaces, which statement is correct?

Question62: What DB2 command when issued displays what migration mode the catalog is in?

Question63: A table is created using the ORGANIZED BY HASH UNIQUE syntax, what action should occur?

Question64: Which trace gives information about frequent log space shortages?

Question65: Which of the following tasks cannot be done via a single ALTER TABLE statement?

Question66: If SEGSIZE 64 and MAXPARTITIONS 10 are specified in a CREATE TABLESPACE statement, what type of table space is created?

Question67: On what type of index would an ALTER INDEX ADD COLUMN be allowed?

Question68: What two parameters allow DB2 to keep a copy of a dynamically prepared statement in the cache?

Question69: For which table type could you specify ALTER TABLE tb1 DATA CAPTURE CHANGES?

Question70: 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?

Question71: 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?

Question72: A single table should allow for up to 100 Terabytes of application data. How do you design your table space?

Question73: When DB2 detects at runtime requiring a large amount of data to be read, which prefetch method is used more often?

Question74: Which connection type can exploit trusted connections?

Question75: 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?

Question76: 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);

Question77: Using the -SET LOG command, what logging characteristics CANNOT be modified?

Question78: 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?

Question79: If security administration is separated from system and database administration, via zparm, which level of authority is required to define roles or trusted contexts?

Question80: In order to communicate a DISPLAY DATABASE (DB1) SPACENAM (TS1) command execution from one DB2 member to another, which coupling facility structure is used?

Question81: Which DB2 online utility job output provides you with information about the BACKUP SYSTEM history?

Question82: Which DSNZPARM will define the number of RID Blocks in the RID Pool?

Question83: You have to design a numeric column, which is also the primary key. The column should cover large (i.e.,
123456789012345678) numbers. Which column data definition covers the requirements?

Question84: 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.)

Question85: When planning for disaster recovery, which two items are NOT required to send to the remote site?
(Choose two.)

Question86: What IBM provided stored procedure will access DB2 real time statistics tables?

Question87: 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?

Question88: In using plan stability, what storage space requirement should be of carefully monitored?

Question89: Your table space DB1.TS1 is currently index-partitioned. Which steps should be performed at a minimum to make it a partitioned-by-range table space?

Question90: What is the appropriate action to maintain data consistency if your page set is in REFP restrictive state?

Question91: 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?

Question92: Which DB2 trace is utilized to capture overall subsystem virtual memory consumption?

Question93: What WLM action will establish performance objectives for DDF threads and their related address space?

Question94: The -DIS UTIL(REORGON) command was entered for a REORG utility with SHRLEVEL CHANGE with utilid REORGON. Which statement is NOT true?

Question95: A unique index (IX1) currently exists on COL1, COL2 and COL3 of the table. There is a request to be able to have an index on these three columns and COL4 to achieve the optimal access path. The addition of COL4 will not support the uniqueness requirement of the index. In order to add this column to the index while still preserving the unique constraint of the first three columns, which option should be taken?

Question96: DB2 workfiles are critical to performance of applications. Which of the following will NOT assist with achieving best performance for workfile usage?

Question97: The application will be handling a large volume of input data that will need to be added to or update 4 different tables. On average, there will be 50 million input records daily. To ensure that the application process to SQL INSERT and SQL UPDATE(or SQL MERGE) delivers a high level of availability to the tables involved in this application process, what is a key component of the application coding?

Question98: How can you find out which external and native stored procedures are active in the system?

Question99: Why does the following DDL command fail? CREATE INDEX X1 ON T1 (COL1, COL2, COL3, BUSINESS_TIME WITHOUT_OVERLAPS);

Question100: In determining whether a REORG on an index space would improve performance, which two of the following columns would NOT be needed? (Choose two.)

Question101: Given the following view definition: CREATE VIEW EMPD AS (SELECT D.DEPTNAME, E.LASTNAME FROM DEPT D INNER JOIN EMP E ON D.DEPTNO = E.WORKDEPT); Can an UPDATE statement be used to update the view EMPD on joined tables DEPT and EMP?

Question102: You take image copies of your table spaces every night. Since you cannot afford any data outage, you use the SHRLEVEL CHANGE option on your COPY utility control statement. Assume that write operations currently run on your table space while the image copies are being performed. Which of the following statements is true?

Question103: What must be in place for the support of index compression?

Question104: In a data sharing environment, how is the LRSN value created?

Question105: When is a merge scan join a well performing access path?

Question106: Which statement about encoding schemes is correct?

Question107: An EBCDIC table t1 and a UNICODE table t2 are joined by t1.col1=t2.col2. Which statement is true?

Question108: You want to ALTER a table space specifying MAXPARTITIONS 256. For which table space type does the ALTER fail?

Question109: Which of the following statements is NOT correct?

Question110: What is the result of the command -RECOVER POSTPONED CANCEL if the postponed-abort thread that you cancel was in the middle of a series of updates when DB2 abended?

Question111: Which of the following does NOT make a view a read-only view?

Question112: The billing application has a DB2 referential integrity set of three tables. TABA, TABB and TABC . Daily, Full Image copies are done for the three table spaces related to the three tables. There is a requirement to recover the three table spaces to noon time, which is about 8 hours after the full image copies were run.
Which one of the following statements is true about this recovery situation?

Question113: When determining what page size will be best for index compression, which utility can be used to provide information on space saved and potential buffer space wasted for each of the different page sizes?

Question114: What trace class will show deadlock information?

Question115: What kind of information is stored in SYSIBM.SYSCOPY?

Question116: What should be implemented in order to audit all the activity of the install SYSADM?

Question117: A table space is in AREO*. What does this status mean?

Question118: 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);

Question119: If a ROWID column is defined on a table as GENERATED BY DEFAULT, what must also be done?

Question120: When specifying "A" for the acceptance option in the column SECURITY_IN in the SYSIBM.LUNAMES catalog table, which statement is correct?

Question121: Which of the following utilities is NOT relevant for health checking the DB2 V10 catalog or directory in new function mode?

Question122: Which authority level allows a user to run utilities that change the data in an application database?

Question123: When is a merge scan join a well performing access path?

Question124: If a single row of the PLAN_TABLE has a 'Y' value in more than one of the sort composite columns, what is indicated?

Question125: The I/O patterns for objects in database NEWBILL need to be reviewed in detail. All of the NEWBILL work runs under a single plan, NEWBILLPL. What START TRACE command should be used to gather the I/O information needed for the objects in database NEWBILL?

Question126: During a recovery of a table space in a data sharing environment, what value is used to coordinate the log records across the DB2 members?

Question127: The performance of BP5 is being evaluated. The Page residency time is being used to see if VPSIZE should be changed. The statistics that are available are VPSIZE of 90,000 and VPSEQT of 15 %. There are 4458 synch I/O per second and 1696 async I/O per second. 6154 total pages read. The system page residency time is 14.6 seconds. Based on a system page residency time of 14.6 seconds, what should be done to increase page residency time?