C2090-612 - DB2 10 DBA for z/OS
Go back to IBM
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
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?
What is one of the first key steps in preparing to use the BACKUP SYSTEM utility?
Define one or more storage groups to a COPYPOOL.
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.
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?
ALTER TABLE TB1 ADD COLUMN COLC DATE;
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.
What trace class will show deadlock information?
Statistic class 3.
Altering the DSSIZE is allowed for what type of table space?
Universal 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?
Function A.B is stopped and queues all access until we start the function or run out of installation timeout.
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 trace gives information about frequent log space shortages?
Statistics trace class(3)
If security administration is separated from system and database administration, via zparm, which level of authority is required to define roles or trusted contexts?
Given the following CREATE TABLE statement: CREATE TABLE TB1 (C1 CHAR(4), C2 XML, C3 CLOB(1K)) ; How many VSAM clusters will DB2 create?
If a single row of the PLAN_TABLE has a 'Y' value in more than one of the sort composite columns, what is indicated?
One sort in the plan step will accomplish two tasks.
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?
What is a potential consideration when using triggers?
They are not invoked by the LOAD REPLACE utility.
If SEGSIZE 64 and MAXPARTITIONS 10 are specified in a CREATE TABLESPACE statement, what type of table space is created?
Partition-by-growth table space.
Which stored procedures need to be set up properly in order to register an XML schema?
XSR_REGISTER and XSR_COMPLETE
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.
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 statement is true about table check constraints?
A row meets the requirement of the constraint if the condition evaluates to true or unknown.
Which statement is NOT true?
REORG with SHRLEVEL CHANGE would automatically detect which threads need to be canceled before switch phase.
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?
Provide an effective commit / restart process.
How do you enable parallelism in a dynamic SQL statement?
Use SET CURRENT DEGREE = ANY
When is a merge scan join a well performing access path?
When the number of qualifying rows of the inner and outer table are both large.
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.
Unnecessary REORGs of indexes may be avoided by which of the following?
Use the SYSIBM.SYSINDEXSPACESTATS table.
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.
Which of the following types of SQL statements are NOT eligible to be stored in the dynamic SQL statement cache?
Which attribute(s) determine the absolute maximum number of partitions for a partition-by-growth table space?
MAXPARTITIONS, DSSIZE, page size
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 monitoring space growth over time, which statistics should NOT be considered?
SYSIBM.SYSTABLESPACEDSSIZE, AVGROWLENSYSIBM.SYSINDEXESPIECESIZE, AVGKEYLEN
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
For which table type could you specify ALTER TABLE tb1 DATA CAPTURE CHANGES?
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?
START TRACE(PERFM) CLASS(3) PLAN(NEWBILLPL)
You have been asked to find packages that use isolation level UR in dynamic SQL. Which trace will be utilized?
Performance trace class(3)
To monitor the storage used above and below the 2GB bar, what must occur?
Start a statistics class 1 trace.
What kind of information is stored in SYSIBM.SYSCOPY?
General information needed for recovery.
Objects DB1.TS1 .... DB1.TS10 are in copy pending status. Which of the following actions removes the copy pending state AND creates an entry in SYSIBM.SYSCOPY?
Run the COPY TABLESPACE utility with option FULL YES for each of the 10 table spaces.
Which DB2 online utility job output provides you with information about the BACKUP SYSTEM history?
REPORT with RECOVERY option
In order to communicate a DISPLAY DATABASE (DB1) SPACENAM (TS1) command execution from one DB2 member to another, which coupling facility structure is used?
A table is created using the ORGANIZED BY HASH UNIQUE syntax, what action should occur?
Specify the column names to be hashed.
What must be done in order to audit read access to a table?
AUDIT ALL must be set on the table and an audit trace must be started.
Which of the following tasks cannot be done via a single ALTER TABLE statement?
Change a table check constraint on the table.
Which DSNZPARM parameter controls the number of locks that may be acquired on a table space?
Why does the following DDL command fail? CREATE INDEX X1 ON T1 (COL1, COL2, COL3, BUSINESS_TIME WITHOUT_OVERLAPS);
The index must be defined as UNIQUE.
When DB2 detects at runtime requiring a large amount of data to be read, which prefetch method is used more often?
Which authority level allows a user to run utilities that change the data in an application database?
The UNLOAD utility can be used to do which of the following functions?
Unload compressed data.
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?
Increase VPSIZE to 180,000 pages.