Appendix L. Sample Questions

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:

  1. Highest set of privileges available in DB2 ESE

  2. Lowest set of privileges available in DB2 ESE

  3. Set of privileges at the DBADM level

  4. 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:

  1. User appusr1 to grant DBADM privilege to user appusr2

  2. User appusr1 to grant SYSADM privilege to user appusr2

  3. System Administrator (root) to grant SYSADM and DBADM to user appusr2

  4. System Administrator (root) to add user appusr2 to the group usr1grp

  5. 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:

  1. CLIENT

  2. SERVER

  3. CLIENT and SERVER

  4. 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:

  1. Belong to the same Windows workgroup

  2. Belong to the same Windows domain

  3. Belong to the different Windows workgroup

  4. 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:

  1. CLIENT

  2. SERVER

  3. CLIENT_ENCRYPT

  4. 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:

  1. Revoke the SELECT privilege from PUBLIC.

  2. Grant the SELECT privilege as required to specific users.

  3. Revoke the SELECT privilege from PUBLIC; grant the SELECT privilege as required to specific users.

  4. 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?

  1. If DB2LDAPCACHE=NO and DIR_CACHE=NO, then always read the information from LDAP.

  2. If DB2LDAPCACHE=NO and DIR_CACHE=YES, then always read the information from LDAP once and insert it into the DB2 cache.

  3. If DB2LDAPCACHE=YES and DIR_CACHE=NO, then always read the information from LDAP.

  4. If DB2LDAPCACHE=YES and DIR_CACHE=YES, then always read the information from LDAP once and insert it into the DB2 cache.

  5. A and B

  6. 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:

  1. db2set DB2_ENABLE_LDAP=YES

  2. db2set DB2LDAP_CLIENT_PROVIDER=IBM

  3. db2 update dbm cfg DB2_ENABLE_LDAP=YES

  4. db2 update dbm cfg DB2LDAP_CLIENT_PROVIDER=IBM

Q09

To enable LDAP explicitly, execute the following command:

  1. db2set DB2_LDAP=YES

  2. db2set DB2_ENABLE_LDAP=YES

  3. db2 update dbm cfg using DB2_LDAP YES

  4. 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?

  1. db2set DB2LDAPCACHE=NO

  2. db2set DB2LDAPCACHE=YES

  3. db2set DB2LDAPREFRESH=NO

  4. db2set DB2LDAPREFRESH=YES

Q11

Before accessing information in the LDAP directory, an application or user is authenticated by:

  1. The DB2 server

  2. The LDAP server

  3. The distinguished name

  4. 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?

  1. 4

  2. 8

  3. 16

  4. 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. 1

  2. 2

  3. 4

  4. 8

Q14

Which of the following statements is true about Federated Database support in DB2?

  1. Any number of DB2 instances can be configured to function as federated servers.

  2. Federated Server uses DRDA communication protocols to communicate with DB2 family instances.

  3. Federated servers use only a single protocol to communicate with non-DB2 family instances.

  4. A and B

  5. A and C

Q15

What is the processing of a query fragment at a data source instead of at the federated server known as?

  1. LOCAL

  2. REMOTE

  3. PASSTHRU

  4. 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?

  1. DB2 chooses the plan it believes will process the query with the most resource cost.

  2. DB2 chooses the plan it believes will process the query with the least resource cost.

  3. DB2 queries the federated server statistics and submits the query fragment to the data source.

  4. 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');
  1. The optimizer models the CPU at the federated server as two times faster than the CPU at the MYSERV server.

  2. The optimizer models the CPU at the federated server as two times slower than the CPU at the MYSERV server.

  3. The optimizer models the CPU at the MYSERV server as five times faster than the CPU at the federated server.

  4. 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');
  1. The optimizer models the CPU at the federated server as two times faster than the CPU at the MYSERV server.

  2. The optimizer models the CPU at the federated server as two times slower than the CPU at the MYSERV server.

  3. The optimizer models the CPU at the MYSERV server as five times faster than the CPU at the federated server.

  4. 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');
  1. The I/O devices at the federated server process data are two times faster than the I/O devices at the MYSERV server.

  2. The I/O devices at the federated server process data are two times slower than the I/O at the MYSERV server.

  3. The I/O devices at the MYSERV server process data are 20 times faster than the I/O at the federated server.

  4. The I/O devices at the MYSERV server process data are 20 times slower than the I/O at the federated server.

  5. None of the above

Q20

Select the correct answer based on following statement:

alter server MYSERV options (ADD COMM_RATE '2');
  1. The network communications data rate to the federated server is at 2,000,000 bytes per second.

  2. The network communications data rate to the data source MYSERV is at 2,000,000 bytes per second.

  3. The network communications data rate at the data source MYSERV is two times faster than the network communications data rate at the federated server.

  4. 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?

  1. 100

  2. 50

  3. 31

  4. 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?

  1. refresh DEFERRED MAINTAINED BY USER

  2. refresh IMMEDIATE MAINTAINED BY USER

  3. refresh DEFERRED MAINTAINED BY SYSTEM

  4. refresh IMMEDIATE MAINTAINED BY SYSTEM

Q23

Which of the following sets of configuration parameters allow the database to use infinite active log space?

  1. Set LOGBUFSZ=8, LOGPRIMARY=10, and LOGSECOND=4

  2. Set LOGBUFSZ=8, LOGPRIMARY=10, and LOGSECOND=?1

  3. Set LOGBUFSZ=8, LOGPRIMARY=?1, and LOGSECOND=?1

  4. 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?

  1. db2set DB2_NEW_LOGPATH2=YES

  2. Update db cfg for SAMPLE using NEWLOGPATH2 /dblogm/SAMPLE

  3. db2set DB2_MIRROR_LOGPATH=YES

  4. 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?

  1. Defines a temporary table for the current session

  2. Does not appear in the system catalog

  3. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.

  4. Changes to the table are not logged.

  5. 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:

  1. Increase space for the active log directory.

  2. Reduce the LOGPRIMARY, LOGSECOND, and LOGFILSIZ.

  3. Set BLK_LOG_DSK_FUL to YES.

  4. A and B

  5. A and C

  6. B and C

Q27

Which of the following commands will collect a complete set of log files up to a known point in time?

  1. db2 connect to SAMPLE; db2 archive log for database SAMPLE

  2. db2 terminate; db2 archive log for database SAMPLE

  3. db2 connect to SAMPLE; db2 truncate log for database SAMPLE

  4. 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?

  1. View the db2diag.log and verify USEREXIT database configuration parameter is set to ON.

  2. View the ARCHIVE.LOG and USEREXIT.ERR.

  3. Increase space for the active log directory.

  4. View the notification log.

Q29

Which of the following statements best describe incremental delta backup image?

  1. A copy of all database data that has changed since the most recent, successful, full backup operation

  2. A copy of all database data that has changed since the last successful backup (full, incremental, or incremental delta) operation

  3. A copy of all database data that has changed since the last successful incremental backup

  4. A copy of all database data that has changed since the last successful incremental delta backup

  5. None of the above

Q30

Which of the following options is required to enable online and incremental backups?

  1. LOGRETAIN=YES, USEREXIT=YES

  2. TRACKMOD=YES, LOGRETAIN=YES

  3. USEREXIT=YES, TRACKMOD=NO

  4. 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'
  1. Step 3, 4, 5, and 6

  2. Step 3, 4, 6, and 5

  3. Step 1, 2, 5, and 6

  4. 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
  1. db2 terminate

  2. db2 force application all

  3. db2_all '<<+0< db2 restore database SAMPLE from /dbbackup/SAMPLE'

  4. db2_all '<<+0< db2 restore database SAMPLE from /dbbackup/SAMPLE taken at 20020829013314'

  5. db2_all '<<-0< db2 restore database SAMPLE from /dbbackup/SAMPLE'

  6. db2_all '<<+1< db2 restore database SAMPLE from /dbbackup/SAMPLE taken at 20020829013401'

  7. db2_all '<<+2< db2 restore database SAMPLE from /dbbackup/SAMPLE taken at 20020829013012'

  8. db2_all '<<+3< db2 restore database SAMPLE from /dbbackup/SAMPLE taken at 20020829013028'

    1. Step 1, 2, 3, and 5

    2. Step 1, 2, 4, 6, 7, and 8

    3. Step 1, 2, 3, 6, 7, and 8

    4. 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?

  1. db2 rollforward database SAMPLE to end of logs on tablespace(ts1)

  2. db2 rollforward database SAMPLE to end of logs on partitionnum(2)

  3. db2 rollforward database SAMPLE to end of logs on dbpartitionnum(2) tablespace(TS1)

  4. db2 rollforward tablespace for database SAMPLE to end of logs on dbpartitionnum(2)

Q34

Which of the following statements is true about db2inidb utility?

  1. The snapshot option clones the primary database to offload work from the source database, such as running reports, analysis, or populate target system.

  2. 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.

  3. The mirror option uses the mirrored system as a backup image to restore over the source system.

  4. 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.

  5. All of the above

  6. None of the above

Q35

Given the following sequences:

  1. The full offline database backup image is copied from the primary server to the standby server.

  2. The database on the standby server restores from the recent full offline database backup image received from the primary server.

  3. The archived log files are copied from the primary server to the standby server.

  4. The database on the standby server rolls forward to the end of logs and stops.

  5. The clients reconnect to the standby database and resume operations.

Which of the following availability methods is being used in this scenario?

  1. Split mirror

  2. Split mirror and suspended I/O

  3. Split, standby, and mirror

  4. 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?

  1. You can suspend a subset of one or more partitions to create split mirror for performing offline backups.

  2. If the catalog partition is included in the subset, it must be the first partition to be suspended.

  3. If the catalog partition is included in the subset, it must be the last partition to be suspended.

  4. A and B

  5. 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?

  1. The contents of the db2nodes.cfg file:

    0 server1 0
    1 server2 0
    
  2. The contents of the db2nodes.cfg file:

    0 server1 0
    1 server2 1
    
  3. The contents of the db2nodes.cfg file:

    0 server2 0
    1 server2 1
    
  4. 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?

  1. The contents of the db2nodes.cfg file:

    0 server1 0
    1 server1 1
    2 server1 0
    3 server1 1
    
  2. The contents of the db2nodes.cfg file:

    0 server1 0
    1 server1 1
    2 server2 2
    3 server2 3
    
  3. The contents of the db2nodes.cfg file:

    0 server1 0
    1 server1 1
    2 server1 2
    3 server1 3
    
  4. 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?

  1. ALLOW NO ACCESS

  2. ALLOW READ ACCESS

  3. ALLOW INCLUSIVE ACCESS

  4. ALLOW SHARE ACCESS

Q40

Which of the following LOAD options will prevent existing data in the table to be selected?

  1. ALLOW NO ACCESS

  2. ALLOW READ ACCESS

  3. ALLOW INCLUSIVE ACCESS

  4. 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 graphics/ccc.gif DATABASE MANAGER processing. 11-20-2002 15:14:05 2 0 SQL6048N A communication error occurred during START or STOP graphics/ccc.gif DATABASE MANAGER processing. 11-20-2002 15:14:05 3 0 SQL6048N A communication error occurred during START or STOP graphics/ccc.gif DATABASE MANAGER processing. 11-20-2002 15:14:05 4 0 SQL6048N A communication error occurred during START or STOP graphics/ccc.gif DATABASE MANAGER processing.

To resolve this communication error, you must:

  1. Set DB2 registry DB2COMM=tcpip.

  2. Create $HOME/.rhosts.

  3. Change permission to $HOME/.rhosts.

  4. 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?

  1. The hostname at line "2" is not valid.

    0 server1 1
    1 server2 1
    2 server2 0
    3 server3 0
    
  2. The hostname/port at line "2" is not unique.

    0 server1 0
    1 server1 1
    2 server2 0
    3 server3 0
    
  3. A syntax error exists at line "2".

    0 server1 1
    1 server1 X
    2 server2 0
    3 server3 0
    
  4. 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]
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 graphics/ccc.gif "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. 1

  2. 2

  3. 3

  4. 4

Q44

How many buffer pools are created when a database is created?

  1. 1

  2. 2

  3. 3

  4. 4

Q45

How many table spaces are created when a database is created?

  1. 1

  2. 2

  3. 3

  4. 4

Q46

How many database partition groups are created when a database is created?

  1. 1

  2. 2

  3. 3

  4. 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. 1

  2. 2

  3. 3

  4. 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. 1

  2. 2

  3. 3

  4. 4

Q49

To create a user table space with a page size of 32 KB, which of the following must already exist?

  1. A buffer pool with a page size of 32 KB

  2. A system temporary table space with a page size of 32 KB

  3. 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))
  1. Tablespace1 with a page size of 4 KB

  2. Tablespace2 with a page size of 8 KB

  3. Tablespace3 with a page size of 16 KB

  4. 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
  1. 4 KB

  2. 8 KB

  3. 16 KB

  4. 32 KB

Q52

What is the minimum size of a DMS table space, given an extent size of 64 4-KB pages?

  1. 1 MB or 4 extents

  2. 1.5 MB or 6 extents

  3. 2 MB or 8 extents

  4. 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?

  1. 4 KB

  2. 8 KB

  3. 16 KB

  4. 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. 1

  2. 2

  3. 3

  4. 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?

  1. db2alloc

  2. db2empfa

  3. db2extal

  4. 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?

  1. 4 KB

  2. 8 KB

  3. 16 KB

  4. 32 KB

Q57

What type of table space can contain both INDEXes and LOBs for a table?

  1. SMALL

  2. MEDIUM

  3. LARGE

  4. EXTRA LARGE

Q58

What option will insert the database partition number to each container name in a create table space statement?

  1. #N

  2. $N

  3. %N

  4. @N

Q59

Which of the following is not a valid option for the ALTER TABLESPACE command?

  1. resize

  2. drop

  3. remove

  4. extend

Q60

What is the page size for the catalog table space?

  1. 4 KB

  2. 8 KB

  3. 16 KB

  4. 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?

  1. ALTER TABLESPACE TS1 RESIZE (ALL CONTAINERS 5000)

  2. ALTER TABLESPACE TS1 EXTEND (ALL 3000)

  3. ALTER TABLESPACE TS1 RESIZE (FILE 'CONT1' 5000, FILE 'CONT2' 5000)

  4. 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?

  1. NUMBLOCKPAGES

  2. BLOCKAREA

  3. RESERVEDAREA

  4. PAGESIZE

Q63

Which privilege level is the minimum necessary to alter a buffer pool?

  1. SYSCTRL

  2. DBADM

  3. SYSMAINT

  4. DBCONTROL

Q64

If insufficient memory is available to create a new buffer pool, which of the following will occur?

  1. An error is returned, and the buffer pool is not created.

  2. A warning is returned, and the buffer pool is created as large as the available memory.

  3. A warning is returned, and the buffer pool will not be created until the database is reactivated.

  4. 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?

  1. Automatic

  2. 50,000

  3. 200,000

  4. 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. 1

  2. 2

  3. 3

  4. 4

Q67

Given the following statements:

CREATE BUFFERPOOL bp1 SIZE 100000;
ALTER TABLESPACE tbspc1 BUFFERPOOL bp1;

Which of the following statements will fail?

  1. Alter bufferpool bp1 size 50000

  2. Alter bufferpool bp1 size -1

  3. Alter bufferpool bp1 size 150000

  4. 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?

  1. Immediately

  2. When another buffer pool is created

  3. When the database is reactivated

  4. 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?

  1. 4

  2. 5

  3. 8

  4. 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. 1

  2. 2

  3. 3

  4. 4

Q71

For the table:

create table t1(c1 varch