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 terminate2. db2 force application all3. db2stop4. db2start5. 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?
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 01 server2 0
The contents of the db2nodes.cfg file:
0 server1 01 server2 1
The contents of the db2nodes.cfg file:
0 server2 01 server2 1
The contents of the db2nodes.cfg file:
0 server1 01 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 01 server1 12 server1 03 server1 1
The contents of the db2nodes.cfg file:
0 server1 01 server1 12 server2 23 server2 3
The contents of the db2nodes.cfg file:
0 server1 01 server1 12 server1 23 server1 3
The contents of the db2nodes.cfg file:
0 server1 01 server1 12 server2 03 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]
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 11 server1 12 server2 03 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 11 server2 12 server2 03 server3 0
The hostname/port at line "2" is not unique.
0 server1 01 server1 12 server2 03 server3 0
A syntax error exists at line "2".
0 server1 11 server1 X2 server2 03 server3 0
The dbpartitionnum value at line "2" is not valid.
0 server1 14 server1 12 server2 03 server3 0
Q43
If you issue a db2start restart command, you will get the following error:
[View full width]
db2start dbpartitionnum 3 restart hostname server3 port 3 netname netserv311-20-2002 15:14:05 3 0 SQL6031N Error in the db2nodes.cfg file at line "5". Reason code
"12".
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 IntC5A VarChar(2000)TableB---------------------C1B VarChar(2000)C2B IntC3B IntC4B 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 16KTempspace1 16KTempspace2 8Ktbspc1 8Ktbspc2 16K
What is the minimum number of buffer pools that must exist in this database?