Appendix L. Sample Questions

This appendix contains sample certification exam questions that help verify your understanding of the topics discussed in this book.

Q01	SYSADM privileges are: Highest set of privileges available in DB2 ESE Lowest set of privileges available in DB2 ESE Set of privileges at the DBADM level None of the above
Q02	Given the following users: User appusr1 belongs to group usr1grp and user appusr2 belongs to group db2grp. User appusr1 is the owner of the instance db2inst1 and has updated the database manger configuration set the SYSADM_GROUP to usr1grp. If user appusr2 wants to drop db2inst1 instance and create a new instance db2inst2, user appusr2 needs: User appusr1 to grant DBADM privilege to user appusr2 User appusr1 to grant SYSADM privilege to user appusr2 System Administrator (root) to grant SYSADM and DBADM to user appusr2 System Administrator (root) to add user appusr2 to the group usr1grp System Administrator (root) to add user appusr1 to the group db2grp
Q03	If you set TRUST_ALLCLNTS=YES and TRUST_CLNTAUTH=SERVER, then authentication for Trusted non-DRDA Client Authentication with password is at: CLIENT SERVER CLIENT and SERVER None of the above
Q04	Kerberos authentication is supported only for clients and servers running Windows 2000, Windows XP, and Windows 2003 operating systems. In addition, both the client and server machines must: Belong to the same Windows workgroup Belong to the same Windows domain Belong to the different Windows workgroup Belong to the different Windows domain
Q05	With an authentication type of KRB_SERVER_ENCRYPT at the server, if the clients do not support the Kerberos security system, then the effective system authentication type is equivalent to: CLIENT SERVER CLIENT_ENCRYPT SERVER_ENCRYPT
Q06	During database creation, SELECT privilege on the system catalog views is granted to the PUBLIC group. To prevent non-privileged viewers from viewing the catalog, you must: Revoke the SELECT privilege from PUBLIC. Grant the SELECT privilege as required to specific users. Revoke the SELECT privilege from PUBLIC; grant the SELECT privilege as required to specific users. Revoke the CONTROL privilege from PUBLIC.
Q07	A caching mechanism exists so that the client searches the LDAP directory only once in its local directory catalogs. Once the information is retrieved, it is cached on the local machine. Subsequent access to the same information is based on the value of the DIR_CACHE Database Manager Configuration parameter and the DB2LDAPCACHE registry variable. Which one of the following statements is correct? If DB2LDAPCACHE=NO and DIR_CACHE=NO, then always read the information from LDAP. If DB2LDAPCACHE=NO and DIR_CACHE=YES, then always read the information from LDAP once and insert it into the DB2 cache. If DB2LDAPCACHE=YES and DIR_CACHE=NO, then always read the information from LDAP. If DB2LDAPCACHE=YES and DIR_CACHE=YES, then always read the information from LDAP once and insert it into the DB2 cache. A and B C and D
Q08	When running on Windows operating systems, DB2 supports using either IBM LDAP client or the Microsoft LDAP client to access the IBM SecureWay Directory Server. If you want to select the IBM LDAP client explicitly, you must use: db2set DB2_ENABLE_LDAP=YES db2set DB2LDAP_CLIENT_PROVIDER=IBM db2 update dbm cfg DB2_ENABLE_LDAP=YES db2 update dbm cfg DB2LDAP_CLIENT_PROVIDER=IBM
Q09	To enable LDAP explicitly, execute the following command: db2set DB2_LDAP=YES db2set DB2_ENABLE_LDAP=YES db2 update dbm cfg using DB2_LDAP YES db2 update dbm cfg using DB2_ENABLE_LDAP YES
Q10	To avoid LDAP information being cached in the database, node, and DCS directories, which one of the following is correct? db2set DB2LDAPCACHE=NO db2set DB2LDAPCACHE=YES db2set DB2LDAPREFRESH=NO db2set DB2LDAPREFRESH=YES
Q11	Before accessing information in the LDAP directory, an application or user is authenticated by: The DB2 server The LDAP server The distinguished name The operating system
Q12	In a multi-partition database, with four database partitions created for each server, there are four identical DB2 servers with a cluster. How many entries will be required in the /etc/services file? 4 8 16 32
Q13	In a partitioned database environment, if there are four DB2 servers and two database partitions for each DB2 server, then how many db2nodes.cfg files will be required? 1 2 4 8
Q14	Which of the following statements is true about Federated Database support in DB2? Any number of DB2 instances can be configured to function as federated servers. Federated Server uses DRDA communication protocols to communicate with DB2 family instances. Federated servers use only a single protocol to communicate with non-DB2 family instances. A and B A and C
Q15	What is the processing of a query fragment at a data source instead of at the federated server known as? LOCAL REMOTE PASSTHRU PUSHDOWN
Q16	In a federated system, the query optimizer generates local and remote access plans for processing a query fragment, based on resource cost. Which of the following statements is true? DB2 chooses the plan it believes will process the query with the most resource cost. DB2 chooses the plan it believes will process the query with the least resource cost. DB2 queries the federated server statistics and submits the query fragment to the data source. DB2 queries the data source statistics and submits the query fragment to the federated server.
Q17	Select the correct answer based on following statement: alter server MYSERV options (ADD CPU_RATIO '0.2'); The optimizer models the CPU at the federated server as two times faster than the CPU at the MYSERV server. The optimizer models the CPU at the federated server as two times slower than the CPU at the MYSERV server. The optimizer models the CPU at the MYSERV server as five times faster than the CPU at the federated server. The optimizer models the CPU at the MYSERV server as five times slower than the CPU at the federated server.
Q18	Select the correct answer based on following statement: alter server MYSERV options (SET CPU_RATIO '2.0'); The optimizer models the CPU at the federated server as two times faster than the CPU at the MYSERV server. The optimizer models the CPU at the federated server as two times slower than the CPU at the MYSERV server. The optimizer models the CPU at the MYSERV server as five times faster than the CPU at the federated server. The optimizer models the CPU at the MYSERV server as five times slower than the CPU at the federated server.
Q19	Select the correct answer based on following statement: alter server MYSERV options (SET IO_RATIO '2.0'); The I/O devices at the federated server process data are two times faster than the I/O devices at the MYSERV server. The I/O devices at the federated server process data are two times slower than the I/O at the MYSERV server. The I/O devices at the MYSERV server process data are 20 times faster than the I/O at the federated server. The I/O devices at the MYSERV server process data are 20 times slower than the I/O at the federated server. None of the above
Q20	Select the correct answer based on following statement: alter server MYSERV options (ADD COMM_RATE '2'); The network communications data rate to the federated server is at 2,000,000 bytes per second. The network communications data rate to the data source MYSERV is at 2,000,000 bytes per second. The network communications data rate at the data source MYSERV is two times faster than the network communications data rate at the federated server. The network communications data rate at the federated server is two times faster than the network communications data rate at the data source MYSERV.
Q21	How many blocks will be required to receive a result set from the data source MYSERV of 10,000 records with a row length of 100 if the RQRIOBLK is set to 65,535? 100 50 31 16
Q22	Given the following statement: create table mqt_account as (select a.c1, a.c2, b.c1 from taba a, tabb b where a.c1=b.c2) data initially DEFERRED refresh <REFRESH OPTION>; Which of the refresh options below should be used to allow the user to perform INSERT, UPDATE, and DELETE operations against the materialized query table? refresh DEFERRED MAINTAINED BY USER refresh IMMEDIATE MAINTAINED BY USER refresh DEFERRED MAINTAINED BY SYSTEM refresh IMMEDIATE MAINTAINED BY SYSTEM
Q23	Which of the following sets of configuration parameters allow the database to use infinite active log space? Set LOGBUFSZ=8, LOGPRIMARY=10, and LOGSECOND=4 Set LOGBUFSZ=8, LOGPRIMARY=10, and LOGSECOND= “1 Set LOGBUFSZ=8, LOGPRIMARY= “1, and LOGSECOND= “1 Set LOGBUFSZ=8, LOGPRIMARY= “1, and LOGSECOND=4
Q24	At the database level, mirroring log files helps protect the database SAMPLE from accidental deletion of an active log. In order to achieve this, which of the following is required? db2set DB2_NEW_LOGPATH2=YES Update db cfg for SAMPLE using NEWLOGPATH2 /dblogm/SAMPLE db2set DB2_MIRROR_LOGPATH=YES Update db cfg for SAMPLE using MIRRORLOGPATH /dblogm/SAMPLE
Q25	Which of the following statements is incorrect when referring to the Declared Global Temporary tables? Defines a temporary table for the current session Does not appear in the system catalog When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped. Changes to the table are not logged. All of the above
Q26	A transaction that receives a log disk full error (SQL0968C) will fail and be rolled back. In addition, DB2 will stop processing. To prevent disk full errors from causing DB2 to stop processing when it cannot create a new log file in the active log path , you should: Increase space for the active log directory. Reduce the LOGPRIMARY, LOGSECOND, and LOGFILSIZ. Set BLK_LOG_DSK_FUL to YES. A and B A and C B and C
Q27	Which of the following commands will collect a complete set of log files up to a known point in time? db2 connect to SAMPLE; db2 archive log for database SAMPLE db2 terminate; db2 archive log for database SAMPLE db2 connect to SAMPLE; db2 truncate log for database SAMPLE db2 terminate; db2 truncate log for database SAMPLE
Q28	For some reason, the user exit did not work properly for the SAMPLE database, and the DIAGLEVEL is set 2. You need to determine the cause of the problem. Which of the information (files) will you need to analyze to help you to identify the problem and determine the solution to resolve it? View the db2diag.log and verify USEREXIT database configuration parameter is set to ON. View the ARCHIVE.LOG and USEREXIT.ERR. Increase space for the active log directory. View the notification log.
Q29	Which of the following statements best describe incremental delta backup image? A copy of all database data that has changed since the most recent, successful, full backup operation A copy of all database data that has changed since the last successful backup (full, incremental, or incremental delta) operation A copy of all database data that has changed since the last successful incremental backup A copy of all database data that has changed since the last successful incremental delta backup None of the above
Q30	Which of the following options is required to enable online and incremental backups ? LOGRETAIN=YES, USEREXIT=YES TRACKMOD=YES, LOGRETAIN=YES USEREXIT=YES, TRACKMOD=NO TRACKMOD=NO, LOGRETAIN=YES
Q31	The database SAMPLE is defined on all four partitions numbered 0 through 3. Database partition 0 is the catalog partition. What is the correct way to perform an offline backup of this database? 1. db2 terminate 2. db2 force application all 3. db2stop 4. db2start 5. db2_all '<<+0< db2 backup database SAMPLE to /dbbackup/SAMPLE' 6. db2_all '<<0< db2 backup database SAMPLE to /dbbackup/SAMPLE' Step 3, 4, 5, and 6 Step 3, 4, 6, and 5 Step 1, 2, 5, and 6 Step 1, 2, 6, and 5
Q32	The database SAMPLE is defined on all four partitions numbered 0 through 3. Database partition 0 is the catalog partition. What is the correct way to restore this database from the recent full offline backup images below? SAMPLE.0.v8inst.NODE0000.CATN0000.20020829013314.001 SAMPLE.0.v8inst.NODE0001.CATN0000.20020829013401.001 SAMPLE.0.v8inst.NODE0002.CATN0000.20020829013012.001 SAMPLE.0.v8inst.NODE0003.CATN0000.20020829013028.001 db2 terminate db2 force application all db2_all '<<+0< db2 restore database SAMPLE from /dbbackup/SAMPLE' db2_all '<<+0< db2 restore database SAMPLE from /dbbackup/SAMPLE taken at 20020829013314' db2_all '<<-0< db2 restore database SAMPLE from /dbbackup/SAMPLE' db2_all '<<+1< db2 restore database SAMPLE from /dbbackup/SAMPLE taken at 20020829013401' db2_all '<<+2< db2 restore database SAMPLE from /dbbackup/SAMPLE taken at 20020829013012' db2_all '<<+3< db2 restore database SAMPLE from /dbbackup/SAMPLE taken at 20020829013028' Step 1, 2, 3, and 5 Step 1, 2, 4, 6, 7, and 8 Step 1, 2, 3, 6, 7, and 8 Step 1, 2, 4, and 5
Q33	Which of the following statements will roll forward a table space backup to the end of logs on database partition number 2? db2 rollforward database SAMPLE to end of logs on tablespace(ts1) db2 rollforward database SAMPLE to end of logs on partitionnum(2) db2 rollforward database SAMPLE to end of logs on dbpartitionnum(2) tablespace(TS1) db2 rollforward tablespace for database SAMPLE to end of logs on dbpartitionnum(2)
Q34	Which of the following statements is true about db2inidb utility? The snapshot option clones the primary database to offload work from the source database, such as running reports , analysis, or populate target system. The standby option continues rolling the log files forward; even new log files that are created by the source database are constantly fetched from the source system. The mirror option uses the mirrored system as a backup image to restore over the source system. The relocate option allows the split mirror to be relocated in terms of the database name, database directory path, container path, log path, and the instance name associated with the database. All of the above None of the above
Q35	Given the following sequences: The full offline database backup image is copied from the primary server to the standby server. The database on the standby server restores from the recent full offline database backup image received from the primary server. The archived log files are copied from the primary server to the standby server. The database on the standby server rolls forward to the end of logs and stops. The clients reconnect to the standby database and resume operations. Which of the following availability methods is being used in this scenario? Split mirror Split mirror and suspended I/O Split, standby, and mirror Log shipping
Q36	In a partitioned database environment, you do not have to suspend I/O writes on all partitions simultaneously . Which of the following statements is true? You can suspend a subset of one or more partitions to create split mirror for performing offline backups. If the catalog partition is included in the subset, it must be the first partition to be suspended. If the catalog partition is included in the subset, it must be the last partition to be suspended. A and B A and C
Q37	In this example, database partition number 0 is running on server1 configured for automatic failover. After the server1 failed over to the server2, database partition number 0 and database partition number 1 are both running on server2. Which of the following db2nodes.cfg files would exist on server2? The contents of the db2nodes.cfg file: 0 server1 0 1 server2 0 The contents of the db2nodes.cfg file: 0 server1 0 1 server2 1 The contents of the db2nodes.cfg file: 0 server2 0 1 server2 1 The contents of the db2nodes.cfg file: 0 server1 0 1 server1 0
Q38	There are two logical database partitions (0 and 1) defined on server1 and two logical database partitions (2 and 3) defined on server2, and the servers are configured for automatic failover. After the failover, database partition 2 and 3 also are running on server1. Which of the following db2nodes.cfg files would exist on server2? The contents of the db2nodes.cfg file: 0 server1 0 1 server1 1 2 server1 0 3 server1 1 The contents of the db2nodes.cfg file: 0 server1 0 1 server1 1 2 server2 2 3 server2 3 The contents of the db2nodes.cfg file: 0 server1 0 1 server1 1 2 server1 2 3 server1 3 The contents of the db2nodes.cfg file: 0 server1 0 1 server1 1 2 server2 0 3 server2 1
Q39	When loading data into a table, which statement is true for the target table in share lock mode? Which of the following LOAD options will allow existing data in the table to be selected? ALLOW NO ACCESS ALLOW READ ACCESS ALLOW INCLUSIVE ACCESS ALLOW SHARE ACCESS
Q40	Which of the following LOAD options will prevent existing data in the table to be selected? ALLOW NO ACCESS ALLOW READ ACCESS ALLOW INCLUSIVE ACCESS ALLOW SHARE ACCESS
Q41	In a partition databases environment, when you issue the db2start command, you will get the following output: [View full width] [View full width] 11-20-2002 15:14:05 1 0 SQL6048N A communication error occurred during START or STOP DATABASE MANAGER processing. 11-20-2002 15:14:05 2 0 SQL6048N A communication error occurred during START or STOP DATABASE MANAGER processing. 11-20-2002 15:14:05 3 0 SQL6048N A communication error occurred during START or STOP DATABASE MANAGER processing. 11-20-2002 15:14:05 4 0 SQL6048N A communication error occurred during START or STOP DATABASE MANAGER processing. To resolve this communication error, you must: Set DB2 registry DB2COMM=tcpip. Create $HOME/.rhosts. Change permission to $HOME/.rhosts. Update dbm cfg using SVCENAME db2cdb2inst1.
Q42	In a partition database environment, when you issue the db2start command, you will get the following output: SQL6031N Error in the db2nodes.cfg file at the line number "2". Reason code "9". The content of the db2nodes.cfg is: 0 server1 1 1 server1 1 2 server2 0 3 server3 0 Based on the db2nodes.cfg file above, which of the following db2nodes.cfg files will fix this problem? The hostname at line "2" is not valid. 0 server1 1 1 server2 1 2 server2 0 3 server3 0 The hostname/port at line "2" is not unique. 0 server1 0 1 server1 1 2 server2 0 3 server3 0 A syntax error exists at line "2". 0 server1 1 1 server1 X 2 server2 0 3 server3 0 The dbpartitionnum value at line "2" is not valid. 0 server1 1 4 server1 1 2 server2 0 3 server3 0
Q43	If you issue a db2start restart command, you will get the following error: [View full width] [View full width] db2start dbpartitionnum 3 restart hostname server3 port 3 netname netserv3 11-20-2002 15:14:05 3 0 SQL6031N Error in the db2nodes.cfg file at line "5". Reason code "12". The content of the /etc/services file is: DB2_db2inst1 50501/tcp DB2_db2inst1_END 50502/tcp The content of the db2nodes.cfg file is: 0 server1 0 netserv1 1 server2 0 netserv2 2 server3 0 netserv3 Based on the db2nodes.cfg and /etc/services files above, how many entries will be required in the /etc/services file? 1 2 3 4
Q44	How many buffer pools are created when a database is created? 1 2 3 4
Q45	How many table spaces are created when a database is created? 1 2 3 4
Q46	How many database partition groups are created when a database is created? 1 2 3 4
Q47	For a database with the following table spaces defined: Tablespace1 with a page size of 4 KB Tablespace2 with a page size of 4 KB Tablespace3 with a page size of 4 KB What is the minimum number of buffer pools required? 1 2 3 4
Q48	For a database with the following table spaces defined: Tablespace1 with a page size of 4 KB Tablespace2 with a page size of 8 KB Tablespace3 with a page size of 4 KB What is the minimum number of buffer pools required? 1 2 3 4
Q49	To create a user table space with a page size of 32 KB, which of the following must already exist? A buffer pool with a page size of 32 KB A system temporary table space with a page size of 32 KB A user temporary table space with a page size of 32 KB
Q50	In which of the following table spaces can the table below be created? Create table T1(c1 int,c2 char (200), c2 varchar(10000)) Tablespace1 with a page size of 4 KB Tablespace2 with a page size of 8 KB Tablespace3 with a page size of 16 KB Tablespace4 with a page size of 32 KB
Q51	What is the minimum page size for table space TS1, given the following statement? Create table T1(c1 int,c2 char (200), c2 varchar(3800)) in TS1 long in TSLONG 4 KB 8 KB 16 KB 32 KB
Q52	What is the minimum size of a DMS table space, given an extent size of 64 4-KB pages? 1 MB or 4 extents 1.5 MB or 6 extents 2 MB or 8 extents 3 MB or 12 extents
Q53	If a table space contains a single table with a row size of 700 bytes, including overhead, what is the minimum page size for the table space in order for the table to contain 100 million rows? 4 KB 8 KB 16 KB 32 KB
Q54	If the following table is created in a DMS table space: Create table table1 (c1 int not null primary key, c2 char(30), c3 char(10), c4 int) How many table space extents will be used for the table? 1 2 3 4
Q55	Which tool can be used to enable an SMS table space to allocate space an extent at a time, rather than a page at a time? db2alloc db2empfa db2extal db2mulpg
Q56	For a database created using a single-byte code page containing the following tables: TableA ------------------- C1A Char(250) C2A Char(250) C3A Char(250) C4A Int C5A VarChar(2000) TableB --------------------- C1B VarChar(2000) C2B Int C3B Int C4B Int Given the following SQL statement: Select A.C5A, A.C2A, B.C1B, B.C2B, B.C3B, B.C4B from TableA A, TableB B where A.C4A = B.C4B and B.C4B > 100 What is the minimum page size for the table space where the temporary table will be created? 4 KB 8 KB 16 KB 32 KB
Q57	What type of table space can contain both INDEXes and LOBs for a table? SMALL MEDIUM LARGE EXTRA LARGE
Q58	What option will insert the database partition number to each container name in a create table space statement? #N $N %N @N
Q59	Which of the following is not a valid option for the ALTER TABLESPACE command? resize drop remove extend
Q60	What is the page size for the catalog table space? 4 KB 8 KB 16 KB 32 KB
Q61	Table space TS1 was created using the following SQL statement: CREATE TABLESPACE TS1 MANAGED BY DATABASE USING (FILE 'CONT1' 2000, FILE 'CONT2' 2000) Which of the following SQL statements cannot be used to change the size of all of the containers in TS1 to 5,000 pages each? ALTER TABLESPACE TS1 RESIZE (ALL CONTAINERS 5000) ALTER TABLESPACE TS1 EXTEND (ALL 3000) ALTER TABLESPACE TS1 RESIZE (FILE 'CONT1' 5000, FILE 'CONT2' 5000) ALTER TABLESPACE TS1 INCREASE (ALL CONTAINERS 3000)
Q62	To set aside an area of the buffer pool for block-based prefetching , which of the following options must be set? NUMBLOCKPAGES BLOCKAREA RESERVEDAREA PAGESIZE
Q63	Which privilege level is the minimum necessary to alter a buffer pool? SYSCTRL DBADM SYSMAINT DBCONTROL
Q64	If insufficient memory is available to create a new buffer pool, which of the following will occur? An error is returned, and the buffer pool is not created. A warning is returned, and the buffer pool is created as large as the available memory. A warning is returned, and the buffer pool will not be created until the database is reactivated. The database is automatically reactivated.
Q65	Given a database in a 32-bit instance of DB2 with an existing 250,000 4-KB pages buffer pool, to create a new buffer pool automatically with a size of 50,000 4-KB pages, to which of the following values must the DATABASE_MEMORY configuration parameter be set? Automatic 50,000 200,000 300,000
Q66	Given the following user-defined table spaces and associated page sizes: Table Space Name Page Size ---------------- --------- Userspace1 16K Tempspace1 16K Tempspace2 8K tbspc1 8K tbspc2 16K What is the minimum number of buffer pools that must exist in this database? 1 2 3 4
Q67	Given the following statements: CREATE BUFFERPOOL bp1 SIZE 100000; ALTER TABLESPACE tbspc1 BUFFERPOOL bp1; Which of the following statements will fail? Alter bufferpool bp1 size 50000 Alter bufferpool bp1 size -1 Alter bufferpool bp1 size 150000 Drop bufferpool bp1
Q68	Given the following statements: CREATE BUFFERPOOL bp1 SIZE 100000; ALTER TABLESPACE tbspc1 BUFFERPOOL bp1; CREATE BUFFERPOOL bp2 SIZE 200000; DROP BUFFERPOOL bp1; When will the memory for buffer pool BP1 be freed back to the operating system? Immediately When another buffer pool is created When the database is reactivated When the table space tbspc1 is dropped
Q69	Given the following instance and database configuration information: INTRA_PARALLEL = YES MAX_QUERYDEGREE = 4 DFT_DEGREE = 8 How many total agent/subagent processes will be used to process the select * from table1 statement? 4 5 8 9
Q70	Given the following statement: CREATE TABLE T1 (c1 int, c2 int, c3 char(10), c4 char(10)) organize by ((c1,c3), c2) How many indexes will be automatically created by DB2? 1 2 3 4
Q71	For the table: create table t1(c1 varchar(30), c2 int, c3 int) organize by (c1, c2) and the following file loaded into the table: aaaaaaa,11,9 bbb,22,10 cccccc,33,11 bbb,22,7 dddd,44,5 bbb,44,7 dddd,44,23 dddd,22,5 With an empty table, how many extents will the above data cause to be used in the table? 1 5 6 7 8
Q72	Which two of the following generated columns are monotonic? int (current date / 10) int (current date / 100) int (current date / 1000) int (current date / 10000) int (current date / 100000)
Q73	When tuning a database for an OLTP workload, which of the following memory areas will provide the biggest impact for performance? Sorting Buffer pools Inter-partition communications Lock list
Q74	Given the following output from the SYSIBM.INDEXES view: TBNAME INDNAME NLEAF NLEVELS ------ ------- ----- ------- MYTAB X1 -1 -1 MYTAB X2 -1 -1 What action should be taken? Drop and recreate index X1. Perform REORG on table MYTAB. Perform RUNSTATS on table MYTAB. Drop and recreate index X2.
Q75	Assuming a LOCKLIST of 20,000 pages with each user wanting to insert 30,000 rows into a table ( assuming also that all inserted rows will be unique), how many concurrent users effectively perform the work? 70 37 15 5
Q76	Given 50 concurrent batch jobs inserting 30,000 rows, which of the following is the minimum size for the LOCKLIST that would allow the jobs to complete successfully? 20,000 30,000 40,000 50,000
Q77	To allow the optimizer to choose efficient access plans that will perform well, it is important to ensure that columns that are frequently accessed with SQL statements containing joins, group by's, or order by's be properly: Dropped Indexed Clustered Reorganized
Q78	Given the following table: create tab1 (c1 int, c2 int, c3 int, c4 int, c5 int); If column c1 is unique and the queries typically access columns c1 and c2 together, which of the following indexes will improve the query performance? create unique index xtab1 on tab1 (c1, c2) create unique index xtab1 on tab1 (c1) include (c2) create unique index xtab1 on tab1 (c1); create index xtab2 on tab1 (c2) create unique index xtab1 on tab1 (c2, c1) create unique index xtab1 on tab1 (c2) include (c1)
Q79	A database was created under DB2 UDB Version 7.x and migrated to Version 8.x. What is the type of all indexes created for this database? UNIQUE Type I Type II NON UNIQUE
Q80	For a database that was created on a previous version of DB2 and migrated to Version 8, all existing indexes will be Type I indexes; however, they can be converted to Type II indexes when: A table is clustered. A table is rebound with new package lists. A table is dropped and recreated. A table is reorganized.
Q81	Which of the following methods cannot be used to update index and/or table statistics in the system catalog tables? Using UPDATE statements that operate against a set of predefined catalog views Using the LOAD utility Using the REORG utility Using the REORGCHK command Using the RUNSTATS utility
Q82	Which of the following SQL statements can be used to determine whether RUNSTATS has been executed for a given table? select tabname, nleaf, nlevels, stats_time from syscat .tables select tabname, card, stats_time from syscat.indexes select tabname, card, stats_time from syscat.tables select tabname, nleaf, nlevels, stats_time from syscat.indexes
Q83	Given the following element from a database snapshot: Rejected Block Remote Cursor requests = 2283 and the following configuration parameter settings: RQRIOBLK = 32767 QUERY_HEAP_SZ = 1000 ASLHEAPSZ = 15 DOS_RQRIOBLK = 4096 Which of the following actions would improve query performance? Increase RQRIOBLK. Increase QUERY_HEAP_SZ. Increase ASLHEAPSZ. Increase DOS_RQRIOBLK.
Q84	Which of the following configuration parameters can be used to limit the maximum number of memory pages that can be used for shared sorts within a database? SORTHEAP SHEAPTHRES SHEAPTHRES_SHR SHEAPTHRES_PRIVATE
Q85	Which of the following configuration parameter settings will allow DB2 to use shared sorts? SHEAPTHRES=ON SHEAPTHRES_SHR=ON INTRA_PARALLEL=ON FEDERATED=ON
Q86	Given the following piece of snapshot output: Post threshold sorts = 32 Piped sorts rejected = 25 and the following configuration parameter settings: DFT_MON_SORT = ON SHEAPTHRES = 10000 SHEAPTHRES_SHR = 5000 SORTHEAP = 256 INDEXSORT = YES Which actions will improve query performance? Set SHEAPTHRES=5000 and DFT_MON_SORT=OFF. Set SHEAPTHRES=5000 and INDEXSORT=NO. Set SHEAPTHRES=20000, SHEAPTHRES_SHR=20000 and SORTHEAP=512. Set SHEAPTHRES=5000, SHEAPTHRES_SHR=5000 and SORTHEAP=128.
Q87	On an SMP server with 32 CPUs running four separate DB2 instances: db2inst1, db2inst2, db2inst2, and db2inst4, which of the following will divide the available CPUs evenly between all instances? For each instance run update dbm cfg using max_querydegree 4 immediate. For each instance run update dbm cfg using max_querydegree 8 immediate. For each instance run update dbm cfg using max_querydegree 16 immediate. For each instance run update dbm cfg using max_querydegree 32 immediate.
Q88	Which of the following configuration parameter settings will activate the connection concentrator? MAX_CONNECTIONS= MAX_COORDAGENTS MAX_CONNECTIONS> MAX_COORDAGENTS MAX_CONNECTIONS< MAX_COORDAGENTS