A 1. DB2 Enterprise Server Edition (ESE) is running on Linux and needs to validate the userids and passwords on the z/OS server for the DB2 clients connecting to DB2 for z/OS. Which of the following authentication levels satisfies this while providing authentication for other DB2 clients at the DB2 ESE server?
A. DCS
B. DRDA
C. HOST
D. CLIENT
E. SERVER
C 2. To permit all users from any DB2 UDB client to connect to the database, which of the following is required in the database's instance?
A. Set trust_clntauth = Client, trust_allclnts=No
B. Set trust_clntauth = Server, trust_allclnts=No
C. Set trust_clntauth = Client, trust_allclnts=Yes
D. Set trust_clntauth = Server, trust_allclnts=Yes}
C 3. A DB2 UDB server has 64-bit UNIX installed. Which of the following is required to update a 32-bit instance to a 64-bit instance after installing the 64-bit DB2 UDB code?
A. Issue the db2icrt command
B. Issue the db2start command
C. Issue the db2iupdt command
D. Issue the db2iconv command
D 3. In which of the following locations must userids and passwords be defined if using authentication SERVER?
A. The node directory on the server
B. The system catalog on the server
C. The PASSWORD table in the database
D. The operating system on the server
D 4. Given an application with the embedded static SQL statement:
INSERT INTO admin.payroll (employee, salary) VALUES ("Becky Smith",80000)
Which of the following privileges must a user hold to run the application?
A. ALTER on the table
B. INSERT on the table
C. DBADM on the database
D. EXECUTE on the package
D 5.AUTHENTICATION=SERVER_ENCRYPT allows DB2 to encrypt which of the following?
A. data
B. userid
C. password
D. userid and password
C 6. Which of the following allows the user "manager" (who is a regular user) to control access to schema "city"?
A. CREATE SCHEMA city RESTRICT manager
B. CREATE SCHEMA city GRANT TO manager
C. CREATE SCHEMA city AUTHORIZATION manager
D. CREATE SCHEMA city, when logged on as user "manager"}
E 7. Which of the following is required to use the IMPORT utility to import data into a table?
A. SYSCTRL authority
B. LOAD authority on the table
C. ALTER privilege on the table
D. IMPORT authority on the table
E. INSERT privilege on the table
D 8. Which of the following authorities can be used to roll forward through database logs, but NOT restore a backup image into a new database?
A. DBADM
B. SYSADM
C. SYSCTRL
D. SYSMAINT
DE 9. Which two of the following identify which users have SYSCTRL authority?
A. The node directory
B. The system catalog
C. The database configuration
D. The operating system security
E. The database manager configuration
C 10. Given the following statement:
"DROP TABLE payroll.employee"
returns the following message:
SQL0551N "USER1" does not have the privilege to perform operation
"DROP" on object "PAYROLL.EMPLOYEE." SQLSTATE=42501
Which of the following will correct the situation?
A. GRANT DROP AUTHORITY TO user1
B. GRANT DROPIN ON SCHEMA user1 TO user1
C. GRANT DROPIN ON SCHEMA payroll TO user1
D. GRANT DROPIN ON SCHEMA employee TO user1
B 11.Given an application with the embedded static SQL statement:
INSERT INTO admin.payroll (employee, salary) VALUES ("Joe Smith",30000)
Which of the following table privileges must be held on admin.payroll to successfully bind the application?
A. ALTER
B. INSERT
C. UPDATE
D. BINDADD
E. EXECUTE
A 12. An administrator issues the following statement:
GRANT ALTER ON TABLE address TO smith
After the statement is completed, which of the following actions can the user SMITH perform?
A. Add constraints to the table
B. Issue ALTER TRIGGER on the table
C. Drop columns from the ADDRESS table
D. Decrease the size of columns in the table
A 13. Which of the following can be done by a user who is granted the CONTROL privilege on an INDEX?
A. Drop the index
B. Alter the index
C. Add columns to the index
D. Create an index extension on the index
C 14. Which of the following actions will occur when issuing the command FORCE APPLICATION ALL?
A. No new database connections are allowed
B. Uncommitted units of work are committed
C. Uncommitted units of work are rolled back
D. Disconnect warning messages are sent to connected users
CE 14. Which two of the following must be done in order to allow DB2 databases on a server to be detected by DB2 clients?
A. Run the Configuration Assistant at the database server
B. Set AUTHENTICATION=CLIENT in the database manager configuration
C. Ensure the database configuration parameter DISCOVER_DB is set to ENABLE
D. Ensure the database manager configuration parameter DISCOVER is set to ENABLE
E. Ensure the database manager configuration parameter DISCOVER_INST is set to ENABLE
D 15. Which of the following must be set to restrict clients from being able to discover any DB2 instances on a server?
A. DISCOVER_DB parameter to DISABLE
B. DISCOVER_INST parameter to DISABLE on a DB2 instance
C. DB2 Administration Server configuration parameter SEARCH to DISABLE
D. DB2 Administration Server configuration parameter DISCOVER to DISABLE
B 16. There is an instance on a server that needs to be discovered. The two databases in the instance are PAYROLL and CERTIFY. The PAYROLL database should not be seen. Which of the following will meet this requirement?
A. Set the DAS configuration parameter DISCOVER to DISABLE.
Set the DISCOVER_DB parameter in the CERTIFY database configuration file to ENABLE.
B. Set the DAS configuration parameter DISCOVER to SEARCH.
Set the DISCOVER_DB parameter in the PAYROLL database configuration file to DISABLE.
C. Set the DAS configuration parameter DISCOVER to SEARCH.
Set the DISCOVER_INST parameter in the PAYROLL database configuration file to DISABLE.
D. Set the DAS configuration parameter DISCOVER to DISABLE.
Set the DISCOVER_INST parameter in the CERTIFY database configuration file to ENABLE.}
A 17. The status of jobs scheduled by the Task Center can be monitored using which of the following?
A. Journal
B. Health Center
C. Snapshot Monitor
D. Information Catalog Center}
A 18. {When scheduled using the Task Center, all of the following actions can be specified to take place upon completion or failure of the task, EXCEPT:
A. Create a new task.
B. Send an email or page.
C. Cancel other scheduled tasks.
D. Re-run the task that just completed.
B 19. What value should the NOTIFYLEVEL configuration parameter have in order to capture all notification messages?
A.5 B.4 C. 3 D. -1
B 20. Given the following notification message:
*
2002-02-05-03.14.38.559911 Instance:db2inst1 Node:000
PID:89198(db2agent (DB2PROD ))
Appid:*LOCAL.db2inst1.020205091435
buffer pool services sqlbStartPoolsErrorHandling Probe:39
ADM6080E The tablespace "ID" (ID "3"), was put OFFLINE and in
ROLLFORWARD_PENDING. Tablespace state is 0x"00004080". 1
*
Which database does this message apply to?
A. ID B. DB2PROD C. db2inst1 D. db2agent
C 21. Which of the following can have their default location changed when issuing the CREATE DATABASE command?
A. Log files B. User tables C. Table spaces D. Stored procedures
A 22. Given the following command:
CREATE DATABASE payroll ALIAS paynew ON path/drive
USING CODESET codeset
TERRITORY territory
COLLATE USING IDENTITY
How is character data compared within the database?
A. Byte for byte
B. Based on the codeset
C. Based on the codepage
D. Based on the territory
D 23. After a successful table space restore command, which of the following states will the table space be in?
A. Restore pending
B. Recovery pending
C. Restore in progress
D. Roll forward pending
B 24. Which of the following statements is required to register a federated database source?
A. CREATE VIEW
B. CREATE WRAPPER
C. CREATE TRANSFORM
D. CREATE TYPE MAPPING
A 25. Given the following statements:
CREATE TABLE T1 (COL1 INT NOT NULL PRIMARY KEY, COL2 CHAR, COL3 CLOB (40K), COL4 VARCHAR(10));
CREATE UNIQUE INDEX IND_1 ON T1 (COL1, COL2) INCLUDE (COL3) ALLOW REVERSE SCANS;
The CREATE UNIQUE INDEX statement will fail because:
A. LOB columns cannot be used in an index.
B. A unique index cannot include an existing primary key.
C. Reverse scans are not supported on multi-column indexes.
D. INCLUDE columns are not supported in indexes that support REVERSE SCANS.
C 26. Given the following DDL statements:
CREATE TABLE PERSON OF PERSON_T
(REF IS OID USER GENERATED)
CREATE TABLE EMP OF EMP_T UNDER PERSON
INHERIT SELECT PRIVILEGES
CREATE TABLE STUDENT OF STUDENT_T UNDER PERSON
INHERIT SELECT PRIVILEGES
CREATE TABLE STAFF (COL1 INT, COL2 INT)
and
INSERT one row into PERSON
INSERT one row into EMP
INSERT one row into STUDENT
INSERT two rows into STAFF
How many rows will be returned after issuing the following SQL statement?
SELECT * FROM PERSON?
A. 1 B. 2 C. 3 D. 5 E. 10
B 27. Given the following DDL statements:
CREATE TABLE person OF person_t
(REF IS OID USER GENERATED)
CREATE TABLE emp OF emp_t UNDER person
INHERIT SELECT PRIVILEGES
CREATE TABLE student OF student_t UNDER person
INHERIT SELECT PRIVILEGES
Which of the following will drop all tables associated with the hierarchy?
A. Dropping the PERSON table
B. Dropping the table hierarchy called PERSON
C. Deleting all rows from the person hierarchy
D. Dropping all columns from the subtables STUDENT and EMP
CE 28. Which two of the following objects can be changed with an ALTER statement?
A. Index
B. Trigger
C. Procedure
D. Constraint
E. Table space
B 30. Which of the following table space characteristics for a System Managed (SMS) table space can be modified with the ALTER TABLESPACE statement?
A. Extent size
B. Prefetch size
C. Size of an existing container
D. Location of an existing container
C 31. Which of the following is an advantage of an SMS table space?
A. The table space can use raw devices.
B. A table can be split across multiple table spaces.
C. Space for the objects in the table space is not allocated until required.
D. The size of the containers in the table space can be changed using the ALTER TABLESPACE statement.}
B 32. In a single partition database, which of the following commands or statements allows containers to be added to an SMS table space?
A. BACKUP DATABASE
B. RESTORE DATABASE
C. ALTER TABLESPACE
D. ADD TABLESPACE CONTAINER
A 33. Which of the following kinds of table spaces allows LOBs to use the filesystem cache?
A. An SMS table space
B. A user temporary table space
C. A system temporary table space
D. A DMS table space created with raw devices
A 34. Which of the following commands will add the same amount of additional space for each container in a DMS table space named TSP1?
A. ALTER TABLESPACE tsp1 EXTEND ...
B. ALTER TABLESPACE tsp1 ENLARGE ...
C. ALTER TABLESPACE tsp1 ADD NEW STRIPE SET ...
D. ALTER TABLESPACE tsp1 BEGIN NEW STRIPE SET ...
B 35. Given the following statement:
CREATE TABLESPACE dms1 MANAGED BY DATABASE USING
(FILE 'dms01' 1024K)
How many pages will be created for the table space?
A. 128 B. 256 C. 512 D. 1024
AC 36. Which two of the following ALTER TABLESPACE options can be used to reduce the amount of space associated with a DMS table space?
A. RESIZE
B. EXTEND
C. REDUCE
D. ADD TO STRIPE SET 1
E. BEGIN NEW STRIPE SET
B 37. A DBA has created three related backup tasks in the Task Center. Which of the following tools can be used to edit the tasks?
A. Journal
B. Command Center
C. Control Center
D. Development Center
A 38. For tasks created and successfully executed in the Task Center, which of the following is the default output location?
A. Journal
B. System console
C. DB2 Command Window
D. User specified file
D 39. Given the following table definitions:
CANDIDATE
---------
CandidateName CHAR(20) NOT NULL
CandidateID INTEGER NOT NULL
Address CHAR(100) NOT NULL
CandidatePhoto BLOB(1M)
TEST_TAKEN
----------
TestName CHAR(50) NOT NULL
TestNumber INTEGER NOT NULL
TestScore INTEGER NOT NULL
CandidateID INTEGER NOT NULL
And the following information:
• Candidate IDs are unique in the CANDIDATE table
• Requirement to select all candidate IDs who have taken test number 701
Assuming no other indexes, which of the following indexes should be created to support the requirement?
A. Primary key on candidate (CandidateID) allowing reverse scans
B. Unique index on candidate (CandidateID) disallowing reverse scans
C. Clustered index on test_taken (CandidateID) allowing reverse scans
D. Index on test_taken (CandidateID, TestNumber) allowing reverse scans
B 40. A table is experiencing frequent inserts. Which of the following options can defer how frequently DB2 must allocate additional index pages?
A. CLUSTER
B. PCTFREE
C. MINPCTUSED
D. SPECIFICATION ONLY
C 41. What is the purpose for creating a primary key on a table?
A. To ensure duplicate key values are entered
B. To support creation of a table check constraint
C. To support referential integrity between tables
D. To provide free space on each index page for new data
C 42. Given the table definitions:
CREATE TABLE employees
(employee_num INT NOT NULL PRIMARY KEY,
employee_name CHAR(20));
CREATE TABLE pc
(serial_num INT NOT NULL PRIMARY KEY,
model_num INT,
owner_id INT,
FOREIGN KEY (owner_id)
REFERENCES employees ON UPDATE RESTRICT);
and the statement:
UPDATE pc SET owner_id = 1000 WHERE serial_num = 7654
Which of the following conditions must be met before the update will be successful?
A. Owner_id 1000 must exist in the pc table.
B. Owner_id 1000 must not exist in the pc table.
C. Employee_num 1000 must exist in the employees table.
D. Employee_num 1000 must not exist in the employees table.
B 43. Given statements issued in the following sequence:
CREATE TABLE tab1 (col1 INT, col2 INT);
CREATE VIEW v1 AS SELECT * FROM tab1;
ALTER TABLE tab1 ADD col3 INT;
and contents of tab1:
COL1 COL2 COL3
------ ------ ------
1 2 3
2 3 4
What is the result of issuing the following statement?
SELECT * FROM V1
A. SQL0204N "V1" is an undefined name.
B. COL1 COL2
----- -----
1 2
2 3
C. COL1 COL2 COL3
------ ------ ------
1 2 3
2 3 4
D. SQL0575N View or materialized query table "V1" cannot be used because it has been marked inoperative.
A 44. Which of the following DB2 objects allows multiple users to access data in a table with each user only being able to access certain portions of the data?
A. Views
B. Summary Tables
C. Dimension Tables
D. Table Constraints
BC 45. Given the following DDL statements:
CREATE VIEW v1 AS SELECT col1 FROM tab1 WHERE col1>;10
CREATE VIEW v2 AS SELECT col1 FROM v1 WITH CHECK OPTION
CREATE VIEW v3 AS SELECT col1 FROM v2 WHERE col1<100
Which two of the following statements will fail?
A. INSERT INTO v1 VALUES (5)
B. INSERT INTO v2 VALUES (5)
C. INSERT INTO v3 VALUES (5)
D. INSERT INTO v3 VALUES (100)
E. INSERT INTO v3 VALUES (200)
C 46. Given the following table:
TAB1
NAMES SALARIES
----------------- -------------------
Joe Smith 32,456
Mary James 56,010
Pete Jones 155,900
David Parson 21,500
and the following statements:
1) REVOKE SELECT ON TABLE tab1 FROM PUBLIC
2) REVOKE SELECT ON TABLE tab1 salaries FROM PUBLIC
3) GRANT SELECT ON tab1v TO PUBLIC
4) CREATE VIEW tab1v AS SELECT names FROM tab1
5) CREATE VIEW tab1v AS SELECT salaries FROM tab1
Which of the statements and in what order must they be called to prevent normal users from seeing SALARIES, but allow them to see NAMES?
A. 1
B. 2
C. 1,4,3
D. 1,5,3
C 26.Which of the following database configuration parameter settings can improve the buffer pool hit ratio?
A. The index sort flag = YES (INDEXSORT)
B. The catalog cache size >; 0 (CATALOGCACHE_SZ)
C. The number of I/O servers >; 0 (NUM_IOSERVERS)
D. The percentage of lock list per application>; 0 (MAXLOCKS)
A 27. Given an application that contains embedded static and dynamic SQL.
Which of the following will capture explain information for all statements in the application that can be examined with both Visual Explain and db2exfmt?
A. The EXPLSNAP ALL and EXPLAIN ALL bind options
B. The EXPLSNAP YES and EXPLAIN YES bind options
C. The SET CURRENT EXPLAIN SNAPSHOT YES and SET CURRENT EXPLAIN MODE YES statements
D. The SET CURRENT EXPLAIN SNAPSHOT ALL and SET CURRENT EXPLAIN MODE ALL statements}
C 28. Which of the following commands can be issued in a Command Line Processor (CLP) session to capture explain information for subsequent SQL statements that can be formatted using db2exfmt?
A. SET CURRENT SNAPSHOT YES
B. SET CURRENT QUERY EXPLAIN
C. SET CURRENT EXPLAIN MODE EXPLAIN
D. SET CURRENT EXPLAIN SNAPSHOT EXPLAIN
A 29. Given the following export statement:
EXPORT TO SAMPLE.DEL OF DEL SELECT * FROM SAMPLE
Which of the following column delimiters will be used in the output file after issuing this statement?
A. , (comma)
B. (blank)
C. ; (semicolon)
D. " (double quote)
B 30. Which of the following is a feature of the IMPORT utility?
A. Views can be created
B. Datalink columns can be imported
C. System catalog tables can be targets
D. REFRESH IMMEDIATE summary tables can be targets
D 31. Which of the following commands loads data into a table and updates the catalog statistics for that table?
A. LOAD FROM data.ixf OF IXF INSERT INTO dept1.assets STATISTICS YES
B. LOAD FROM data.ixf OF IXF CREATE INTO dept1.assets STATISTICS YES
C. LOAD FROM data.ixf OF IXF UPDATE INTO dept1.assets STATISTICS YES
D. LOAD FROM data.ixf OF IXF REPLACE INTO dept1.assets STATISTICS YES
BE 37. Which two of the following are possible with LOAD?
A. Triggers are fired
B. Statistics are gathered
C. The target can be a nickname
D. Loaded data is captured for replication
E. Loaded data is copied for roll forward recovery
DE 32. Which two of the following DB2 commands update information used by the optimizer when choosing an access path?
A. REORG
B. IMPORT
C. REBIND
D. RUNSTATS
E. CREATE INDEX
C 33. Given the following data from SYSCAT.TABLES:
TABNAME CARD NPAGES FPAGES OVERFLOW
T1 1242 84 85 0
ORG 8000 157 157 2
SALES 844 19 19 45
DEPARTMENT 767 23 23 5
Which of the following tables will benefit most from being reorganized?
A. T1
B. ORG
C. SALES
D. DEPARTMENT
AC 34. When looking at SYSCAT.TABLES, which two of the following columns can indicate it is appropriate to run RUNSTATS?
A. CARD
B. CLUSTERED
C. STATS_TIME
D. CHECKCOUNT
E. CREATE_TIME
C 35. The FLUSH PACKAGE CACHE statement should be run after issuing which of the following commands?
A. REORG
B. DB2RBIND
C. RUNSTATS
D. CREATE TABLE UPDATE DATABASE CFG USING ...
A 36. Which of the following is supported by DB2MOVE?
A. Exporting and importing LOBs
B. Moving table space definitions
C. Moving logs to a stand-by database
D. Exporting and importing trigger definitions
A 37.Which of the following can the DB2 Design Advisor recommend?
A. Indexes for provided workload
B. Buffer pool assignments for table spaces
C. Optimal values for database memory configuration
D. Referential integrity constraints for selected tables
B 38. Which of the following must occur to restore a database which has table space containers on invalid drives/devices?
A. Use roll forward recovery on the database
B. Use the REDIRECT option to restore the database
C. Use the RESTART DATABASE command on the database
D. Use the recovery history file to restore the database
BD 39. A backup image of the MYDB database exists in the <db2backup>; directory. The following restore command was run:
RESTORE DATABASE mydb FROM <db2backup>; INTO sample NEWLOGPATH <newlogpath>; REPLACE EXISTING
Which two of the following reflect the state of the SAMPLE database after the restore?
A. The database manager configuration file is over-written.
B. The system catalog tables for the database are replaced.
C. The LOGPATH database configuration parameter is retained.
D. The database configuration file for SAMPLE is over-written.
E. The authentication type for the database is reset to the default.
C 40. If a user accidentally drops a table and commits work, which of the following recovers the data from the dropped table?
A. Once committed, the data from the dropped table cannot be recovered
B. Obtain the transaction ID for the DROP TABLE statement and roll back the transaction
C. If DROPPED TABLE RECOVERY is enabled for the table space, restore table space, and roll forward
D. If DROPPED TABLE RECOVERY is not enabled for the table space, enable DROPPED TABLE RECOVERY, and restore table space and roll forward
A 41.To minimize database restart time due to rebuilding indexes, which of the following is the correct setting of the INDEXREC configuration parameter?
A. ACCESS
B. RESTART
C. REBUILD
D. DEFERRED
A 42. Given the following information:
Database manager configuration parameter INDEXREC = RESTART
Database configuration parameter INDEXREC = SYSTEM
When will invalid indexes be rebuilt?
A. When the database restarts
B. When the last connection terminates
C. When the invalid index is first accessed
D. When the REORG INDEXES command is issued}
B 43.Which of the following is required to support infinite active log space?
A. USEREXIT = ON, LOGSECOND = 0
B. USEREXIT = ON, LOGSECOND = -1
C. LOGRETAIN = RECOVERY, LOGSECOND = 0
D. LOGRETAIN = RECOVERY, LOGSECOND = -1
C 44. Given the following DB2DIAG.LOG entry:
"Crash Recovery is needed."
Which of the following could be the cause for this entry?
A. A load failed
B. A restore failed
C. A hardware problem
D. A rollforward is required
BC 45. Given the following information:
LIST HISTORY BACKUP ALL FOR <database-alias>;
SQL2160W A damaged recovery history file has been replaced. Processing continued.
Which two of the following can be used to determine previous recovery activity on the database?
A. Re-issue the LIST HISTORY BACKUP command
B. Restore the database from previous backup
C. Restore recovery history file from previous backup
D. Restore just the catalog table space from a previous backup
E. Copy the DB2RHIST.ASC file from another database on the same instance
A 46. Which combination of database configuration parameters limits the database to version recovery only?
A. LOGRETAIN=NO USEREXIT=NO
B. LOGRETAIN=NO USEREXIT=YES
C. LOGRETAIN=RECOVERY USEREXIT=NO
D. LOGRETAIN=RECOVERY USEREXIT=YES
AE 47.Which two of the following are only possible using a redirected restore?
A. Redefining table space containers
B. Replacing a damaged recovery history file
C. Recovering table spaces in roll-forward pending
D. Specifying an alternate log path for roll forward
E. Restoring the database onto a different server with the same operating system}
A 48. Which commands are required to restore a Version 7 backup image of the database SAMPLE to a Version 8 instance?
A. RESTORE DATABASE sample FROM <backupdir>; INTO sample;
B. RESTORE DATABASE sample FROM <backupdir>; INTO sample;
DB2IMIGR;
C. RESTORE DATABASE sample FROM <backupdir>; INTO sample;
MIGRATE DATABASE sample;
D. RESTORE DATABASE sample FROM <backupdir>; INTO sample;
DB2IMIGR;
MIGRATE DATABASE sample;
D 49. An online database backup was started for database MYDB at 1 AM and completed at 3 AM. Which of the following commands will restore the database from this backup image and allow connections to the database?
A. RESTORE DB mydb FROM <backupdir>;;
B. RESTORE DB mydb FROM <backupdir>; WITHOUT ROLLING FORWARD;
C. RESTORE DB mydb FROM <backupdir>;; ROLLFORWARD DATABASE mydb TO 1AM AND STOP;
D. RESTORE DB mydb FROM <backupdir>;; ROLLFORWARD DATABASE mydb TO END OF LOGS AND STOP;}
BD 50. Given the following information:
• A full database backup of DB1 was taken at 1:00PM.
• A table space TS1 was backed up at 1:15PM.
• A table in TS1 was dropped at 2:00PM.
Which two of the following commands are valid after restoring table space TS1 from the full database backup taken at 1:00PM?
A. ROLLFORWARD DATABASE db1 TO 1:15PM
B. ROLLFORWARD DATABASE db1 TO 2:30PM
C. ROLLFORWARD TABLESPACE ts1 TO 2:30PM
D. ROLLFORWARD DATABASE db1 TO END OF LOGS
E. ROLLFORWARD TABLESPACE ts1 TO END OF LOGS
A. DCS
B. DRDA
C. HOST
D. CLIENT
E. SERVER
C 2. To permit all users from any DB2 UDB client to connect to the database, which of the following is required in the database's instance?
A. Set trust_clntauth = Client, trust_allclnts=No
B. Set trust_clntauth = Server, trust_allclnts=No
C. Set trust_clntauth = Client, trust_allclnts=Yes
D. Set trust_clntauth = Server, trust_allclnts=Yes}
C 3. A DB2 UDB server has 64-bit UNIX installed. Which of the following is required to update a 32-bit instance to a 64-bit instance after installing the 64-bit DB2 UDB code?
A. Issue the db2icrt command
B. Issue the db2start command
C. Issue the db2iupdt command
D. Issue the db2iconv command
D 3. In which of the following locations must userids and passwords be defined if using authentication SERVER?
A. The node directory on the server
B. The system catalog on the server
C. The PASSWORD table in the database
D. The operating system on the server
D 4. Given an application with the embedded static SQL statement:
INSERT INTO admin.payroll (employee, salary) VALUES ("Becky Smith",80000)
Which of the following privileges must a user hold to run the application?
A. ALTER on the table
B. INSERT on the table
C. DBADM on the database
D. EXECUTE on the package
D 5.AUTHENTICATION=SERVER_ENCRYPT allows DB2 to encrypt which of the following?
A. data
B. userid
C. password
D. userid and password
C 6. Which of the following allows the user "manager" (who is a regular user) to control access to schema "city"?
A. CREATE SCHEMA city RESTRICT manager
B. CREATE SCHEMA city GRANT TO manager
C. CREATE SCHEMA city AUTHORIZATION manager
D. CREATE SCHEMA city, when logged on as user "manager"}
E 7. Which of the following is required to use the IMPORT utility to import data into a table?
A. SYSCTRL authority
B. LOAD authority on the table
C. ALTER privilege on the table
D. IMPORT authority on the table
E. INSERT privilege on the table
D 8. Which of the following authorities can be used to roll forward through database logs, but NOT restore a backup image into a new database?
A. DBADM
B. SYSADM
C. SYSCTRL
D. SYSMAINT
DE 9. Which two of the following identify which users have SYSCTRL authority?
A. The node directory
B. The system catalog
C. The database configuration
D. The operating system security
E. The database manager configuration
C 10. Given the following statement:
"DROP TABLE payroll.employee"
returns the following message:
SQL0551N "USER1" does not have the privilege to perform operation
"DROP" on object "PAYROLL.EMPLOYEE." SQLSTATE=42501
Which of the following will correct the situation?
A. GRANT DROP AUTHORITY TO user1
B. GRANT DROPIN ON SCHEMA user1 TO user1
C. GRANT DROPIN ON SCHEMA payroll TO user1
D. GRANT DROPIN ON SCHEMA employee TO user1
B 11.Given an application with the embedded static SQL statement:
INSERT INTO admin.payroll (employee, salary) VALUES ("Joe Smith",30000)
Which of the following table privileges must be held on admin.payroll to successfully bind the application?
A. ALTER
B. INSERT
C. UPDATE
D. BINDADD
E. EXECUTE
A 12. An administrator issues the following statement:
GRANT ALTER ON TABLE address TO smith
After the statement is completed, which of the following actions can the user SMITH perform?
A. Add constraints to the table
B. Issue ALTER TRIGGER on the table
C. Drop columns from the ADDRESS table
D. Decrease the size of columns in the table
A 13. Which of the following can be done by a user who is granted the CONTROL privilege on an INDEX?
A. Drop the index
B. Alter the index
C. Add columns to the index
D. Create an index extension on the index
C 14. Which of the following actions will occur when issuing the command FORCE APPLICATION ALL?
A. No new database connections are allowed
B. Uncommitted units of work are committed
C. Uncommitted units of work are rolled back
D. Disconnect warning messages are sent to connected users
CE 14. Which two of the following must be done in order to allow DB2 databases on a server to be detected by DB2 clients?
A. Run the Configuration Assistant at the database server
B. Set AUTHENTICATION=CLIENT in the database manager configuration
C. Ensure the database configuration parameter DISCOVER_DB is set to ENABLE
D. Ensure the database manager configuration parameter DISCOVER is set to ENABLE
E. Ensure the database manager configuration parameter DISCOVER_INST is set to ENABLE
D 15. Which of the following must be set to restrict clients from being able to discover any DB2 instances on a server?
A. DISCOVER_DB parameter to DISABLE
B. DISCOVER_INST parameter to DISABLE on a DB2 instance
C. DB2 Administration Server configuration parameter SEARCH to DISABLE
D. DB2 Administration Server configuration parameter DISCOVER to DISABLE
B 16. There is an instance on a server that needs to be discovered. The two databases in the instance are PAYROLL and CERTIFY. The PAYROLL database should not be seen. Which of the following will meet this requirement?
A. Set the DAS configuration parameter DISCOVER to DISABLE.
Set the DISCOVER_DB parameter in the CERTIFY database configuration file to ENABLE.
B. Set the DAS configuration parameter DISCOVER to SEARCH.
Set the DISCOVER_DB parameter in the PAYROLL database configuration file to DISABLE.
C. Set the DAS configuration parameter DISCOVER to SEARCH.
Set the DISCOVER_INST parameter in the PAYROLL database configuration file to DISABLE.
D. Set the DAS configuration parameter DISCOVER to DISABLE.
Set the DISCOVER_INST parameter in the CERTIFY database configuration file to ENABLE.}
A 17. The status of jobs scheduled by the Task Center can be monitored using which of the following?
A. Journal
B. Health Center
C. Snapshot Monitor
D. Information Catalog Center}
A 18. {When scheduled using the Task Center, all of the following actions can be specified to take place upon completion or failure of the task, EXCEPT:
A. Create a new task.
B. Send an email or page.
C. Cancel other scheduled tasks.
D. Re-run the task that just completed.
B 19. What value should the NOTIFYLEVEL configuration parameter have in order to capture all notification messages?
A.5 B.4 C. 3 D. -1
B 20. Given the following notification message:
*
2002-02-05-03.14.38.559911 Instance:db2inst1 Node:000
PID:89198(db2agent (DB2PROD ))
Appid:*LOCAL.db2inst1.020205091435
buffer pool services sqlbStartPoolsErrorHandling Probe:39
ADM6080E The tablespace "ID" (ID "3"), was put OFFLINE and in
ROLLFORWARD_PENDING. Tablespace state is 0x"00004080". 1
*
Which database does this message apply to?
A. ID B. DB2PROD C. db2inst1 D. db2agent
C 21. Which of the following can have their default location changed when issuing the CREATE DATABASE command?
A. Log files B. User tables C. Table spaces D. Stored procedures
A 22. Given the following command:
CREATE DATABASE payroll ALIAS paynew ON path/drive
USING CODESET codeset
TERRITORY territory
COLLATE USING IDENTITY
How is character data compared within the database?
A. Byte for byte
B. Based on the codeset
C. Based on the codepage
D. Based on the territory
D 23. After a successful table space restore command, which of the following states will the table space be in?
A. Restore pending
B. Recovery pending
C. Restore in progress
D. Roll forward pending
B 24. Which of the following statements is required to register a federated database source?
A. CREATE VIEW
B. CREATE WRAPPER
C. CREATE TRANSFORM
D. CREATE TYPE MAPPING
A 25. Given the following statements:
CREATE TABLE T1 (COL1 INT NOT NULL PRIMARY KEY, COL2 CHAR, COL3 CLOB (40K), COL4 VARCHAR(10));
CREATE UNIQUE INDEX IND_1 ON T1 (COL1, COL2) INCLUDE (COL3) ALLOW REVERSE SCANS;
The CREATE UNIQUE INDEX statement will fail because:
A. LOB columns cannot be used in an index.
B. A unique index cannot include an existing primary key.
C. Reverse scans are not supported on multi-column indexes.
D. INCLUDE columns are not supported in indexes that support REVERSE SCANS.
C 26. Given the following DDL statements:
CREATE TABLE PERSON OF PERSON_T
(REF IS OID USER GENERATED)
CREATE TABLE EMP OF EMP_T UNDER PERSON
INHERIT SELECT PRIVILEGES
CREATE TABLE STUDENT OF STUDENT_T UNDER PERSON
INHERIT SELECT PRIVILEGES
CREATE TABLE STAFF (COL1 INT, COL2 INT)
and
INSERT one row into PERSON
INSERT one row into EMP
INSERT one row into STUDENT
INSERT two rows into STAFF
How many rows will be returned after issuing the following SQL statement?
SELECT * FROM PERSON?
A. 1 B. 2 C. 3 D. 5 E. 10
B 27. Given the following DDL statements:
CREATE TABLE person OF person_t
(REF IS OID USER GENERATED)
CREATE TABLE emp OF emp_t UNDER person
INHERIT SELECT PRIVILEGES
CREATE TABLE student OF student_t UNDER person
INHERIT SELECT PRIVILEGES
Which of the following will drop all tables associated with the hierarchy?
A. Dropping the PERSON table
B. Dropping the table hierarchy called PERSON
C. Deleting all rows from the person hierarchy
D. Dropping all columns from the subtables STUDENT and EMP
CE 28. Which two of the following objects can be changed with an ALTER statement?
A. Index
B. Trigger
C. Procedure
D. Constraint
E. Table space
B 30. Which of the following table space characteristics for a System Managed (SMS) table space can be modified with the ALTER TABLESPACE statement?
A. Extent size
B. Prefetch size
C. Size of an existing container
D. Location of an existing container
C 31. Which of the following is an advantage of an SMS table space?
A. The table space can use raw devices.
B. A table can be split across multiple table spaces.
C. Space for the objects in the table space is not allocated until required.
D. The size of the containers in the table space can be changed using the ALTER TABLESPACE statement.}
B 32. In a single partition database, which of the following commands or statements allows containers to be added to an SMS table space?
A. BACKUP DATABASE
B. RESTORE DATABASE
C. ALTER TABLESPACE
D. ADD TABLESPACE CONTAINER
A 33. Which of the following kinds of table spaces allows LOBs to use the filesystem cache?
A. An SMS table space
B. A user temporary table space
C. A system temporary table space
D. A DMS table space created with raw devices
A 34. Which of the following commands will add the same amount of additional space for each container in a DMS table space named TSP1?
A. ALTER TABLESPACE tsp1 EXTEND ...
B. ALTER TABLESPACE tsp1 ENLARGE ...
C. ALTER TABLESPACE tsp1 ADD NEW STRIPE SET ...
D. ALTER TABLESPACE tsp1 BEGIN NEW STRIPE SET ...
B 35. Given the following statement:
CREATE TABLESPACE dms1 MANAGED BY DATABASE USING
(FILE 'dms01' 1024K)
How many pages will be created for the table space?
A. 128 B. 256 C. 512 D. 1024
AC 36. Which two of the following ALTER TABLESPACE options can be used to reduce the amount of space associated with a DMS table space?
A. RESIZE
B. EXTEND
C. REDUCE
D. ADD TO STRIPE SET 1
E. BEGIN NEW STRIPE SET
B 37. A DBA has created three related backup tasks in the Task Center. Which of the following tools can be used to edit the tasks?
A. Journal
B. Command Center
C. Control Center
D. Development Center
A 38. For tasks created and successfully executed in the Task Center, which of the following is the default output location?
A. Journal
B. System console
C. DB2 Command Window
D. User specified file
D 39. Given the following table definitions:
CANDIDATE
---------
CandidateName CHAR(20) NOT NULL
CandidateID INTEGER NOT NULL
Address CHAR(100) NOT NULL
CandidatePhoto BLOB(1M)
TEST_TAKEN
----------
TestName CHAR(50) NOT NULL
TestNumber INTEGER NOT NULL
TestScore INTEGER NOT NULL
CandidateID INTEGER NOT NULL
And the following information:
• Candidate IDs are unique in the CANDIDATE table
• Requirement to select all candidate IDs who have taken test number 701
Assuming no other indexes, which of the following indexes should be created to support the requirement?
A. Primary key on candidate (CandidateID) allowing reverse scans
B. Unique index on candidate (CandidateID) disallowing reverse scans
C. Clustered index on test_taken (CandidateID) allowing reverse scans
D. Index on test_taken (CandidateID, TestNumber) allowing reverse scans
B 40. A table is experiencing frequent inserts. Which of the following options can defer how frequently DB2 must allocate additional index pages?
A. CLUSTER
B. PCTFREE
C. MINPCTUSED
D. SPECIFICATION ONLY
C 41. What is the purpose for creating a primary key on a table?
A. To ensure duplicate key values are entered
B. To support creation of a table check constraint
C. To support referential integrity between tables
D. To provide free space on each index page for new data
C 42. Given the table definitions:
CREATE TABLE employees
(employee_num INT NOT NULL PRIMARY KEY,
employee_name CHAR(20));
CREATE TABLE pc
(serial_num INT NOT NULL PRIMARY KEY,
model_num INT,
owner_id INT,
FOREIGN KEY (owner_id)
REFERENCES employees ON UPDATE RESTRICT);
and the statement:
UPDATE pc SET owner_id = 1000 WHERE serial_num = 7654
Which of the following conditions must be met before the update will be successful?
A. Owner_id 1000 must exist in the pc table.
B. Owner_id 1000 must not exist in the pc table.
C. Employee_num 1000 must exist in the employees table.
D. Employee_num 1000 must not exist in the employees table.
B 43. Given statements issued in the following sequence:
CREATE TABLE tab1 (col1 INT, col2 INT);
CREATE VIEW v1 AS SELECT * FROM tab1;
ALTER TABLE tab1 ADD col3 INT;
and contents of tab1:
COL1 COL2 COL3
------ ------ ------
1 2 3
2 3 4
What is the result of issuing the following statement?
SELECT * FROM V1
A. SQL0204N "V1" is an undefined name.
B. COL1 COL2
----- -----
1 2
2 3
C. COL1 COL2 COL3
------ ------ ------
1 2 3
2 3 4
D. SQL0575N View or materialized query table "V1" cannot be used because it has been marked inoperative.
A 44. Which of the following DB2 objects allows multiple users to access data in a table with each user only being able to access certain portions of the data?
A. Views
B. Summary Tables
C. Dimension Tables
D. Table Constraints
BC 45. Given the following DDL statements:
CREATE VIEW v1 AS SELECT col1 FROM tab1 WHERE col1>;10
CREATE VIEW v2 AS SELECT col1 FROM v1 WITH CHECK OPTION
CREATE VIEW v3 AS SELECT col1 FROM v2 WHERE col1<100
Which two of the following statements will fail?
A. INSERT INTO v1 VALUES (5)
B. INSERT INTO v2 VALUES (5)
C. INSERT INTO v3 VALUES (5)
D. INSERT INTO v3 VALUES (100)
E. INSERT INTO v3 VALUES (200)
C 46. Given the following table:
TAB1
NAMES SALARIES
----------------- -------------------
Joe Smith 32,456
Mary James 56,010
Pete Jones 155,900
David Parson 21,500
and the following statements:
1) REVOKE SELECT ON TABLE tab1 FROM PUBLIC
2) REVOKE SELECT ON TABLE tab1 salaries FROM PUBLIC
3) GRANT SELECT ON tab1v TO PUBLIC
4) CREATE VIEW tab1v AS SELECT names FROM tab1
5) CREATE VIEW tab1v AS SELECT salaries FROM tab1
Which of the statements and in what order must they be called to prevent normal users from seeing SALARIES, but allow them to see NAMES?
A. 1
B. 2
C. 1,4,3
D. 1,5,3
C 26.Which of the following database configuration parameter settings can improve the buffer pool hit ratio?
A. The index sort flag = YES (INDEXSORT)
B. The catalog cache size >; 0 (CATALOGCACHE_SZ)
C. The number of I/O servers >; 0 (NUM_IOSERVERS)
D. The percentage of lock list per application>; 0 (MAXLOCKS)
A 27. Given an application that contains embedded static and dynamic SQL.
Which of the following will capture explain information for all statements in the application that can be examined with both Visual Explain and db2exfmt?
A. The EXPLSNAP ALL and EXPLAIN ALL bind options
B. The EXPLSNAP YES and EXPLAIN YES bind options
C. The SET CURRENT EXPLAIN SNAPSHOT YES and SET CURRENT EXPLAIN MODE YES statements
D. The SET CURRENT EXPLAIN SNAPSHOT ALL and SET CURRENT EXPLAIN MODE ALL statements}
C 28. Which of the following commands can be issued in a Command Line Processor (CLP) session to capture explain information for subsequent SQL statements that can be formatted using db2exfmt?
A. SET CURRENT SNAPSHOT YES
B. SET CURRENT QUERY EXPLAIN
C. SET CURRENT EXPLAIN MODE EXPLAIN
D. SET CURRENT EXPLAIN SNAPSHOT EXPLAIN
A 29. Given the following export statement:
EXPORT TO SAMPLE.DEL OF DEL SELECT * FROM SAMPLE
Which of the following column delimiters will be used in the output file after issuing this statement?
A. , (comma)
B. (blank)
C. ; (semicolon)
D. " (double quote)
B 30. Which of the following is a feature of the IMPORT utility?
A. Views can be created
B. Datalink columns can be imported
C. System catalog tables can be targets
D. REFRESH IMMEDIATE summary tables can be targets
D 31. Which of the following commands loads data into a table and updates the catalog statistics for that table?
A. LOAD FROM data.ixf OF IXF INSERT INTO dept1.assets STATISTICS YES
B. LOAD FROM data.ixf OF IXF CREATE INTO dept1.assets STATISTICS YES
C. LOAD FROM data.ixf OF IXF UPDATE INTO dept1.assets STATISTICS YES
D. LOAD FROM data.ixf OF IXF REPLACE INTO dept1.assets STATISTICS YES
BE 37. Which two of the following are possible with LOAD?
A. Triggers are fired
B. Statistics are gathered
C. The target can be a nickname
D. Loaded data is captured for replication
E. Loaded data is copied for roll forward recovery
DE 32. Which two of the following DB2 commands update information used by the optimizer when choosing an access path?
A. REORG
B. IMPORT
C. REBIND
D. RUNSTATS
E. CREATE INDEX
C 33. Given the following data from SYSCAT.TABLES:
TABNAME CARD NPAGES FPAGES OVERFLOW
T1 1242 84 85 0
ORG 8000 157 157 2
SALES 844 19 19 45
DEPARTMENT 767 23 23 5
Which of the following tables will benefit most from being reorganized?
A. T1
B. ORG
C. SALES
D. DEPARTMENT
AC 34. When looking at SYSCAT.TABLES, which two of the following columns can indicate it is appropriate to run RUNSTATS?
A. CARD
B. CLUSTERED
C. STATS_TIME
D. CHECKCOUNT
E. CREATE_TIME
C 35. The FLUSH PACKAGE CACHE statement should be run after issuing which of the following commands?
A. REORG
B. DB2RBIND
C. RUNSTATS
D. CREATE TABLE UPDATE DATABASE CFG USING ...
A 36. Which of the following is supported by DB2MOVE?
A. Exporting and importing LOBs
B. Moving table space definitions
C. Moving logs to a stand-by database
D. Exporting and importing trigger definitions
A 37.Which of the following can the DB2 Design Advisor recommend?
A. Indexes for provided workload
B. Buffer pool assignments for table spaces
C. Optimal values for database memory configuration
D. Referential integrity constraints for selected tables
B 38. Which of the following must occur to restore a database which has table space containers on invalid drives/devices?
A. Use roll forward recovery on the database
B. Use the REDIRECT option to restore the database
C. Use the RESTART DATABASE command on the database
D. Use the recovery history file to restore the database
BD 39. A backup image of the MYDB database exists in the <db2backup>; directory. The following restore command was run:
RESTORE DATABASE mydb FROM <db2backup>; INTO sample NEWLOGPATH <newlogpath>; REPLACE EXISTING
Which two of the following reflect the state of the SAMPLE database after the restore?
A. The database manager configuration file is over-written.
B. The system catalog tables for the database are replaced.
C. The LOGPATH database configuration parameter is retained.
D. The database configuration file for SAMPLE is over-written.
E. The authentication type for the database is reset to the default.
C 40. If a user accidentally drops a table and commits work, which of the following recovers the data from the dropped table?
A. Once committed, the data from the dropped table cannot be recovered
B. Obtain the transaction ID for the DROP TABLE statement and roll back the transaction
C. If DROPPED TABLE RECOVERY is enabled for the table space, restore table space, and roll forward
D. If DROPPED TABLE RECOVERY is not enabled for the table space, enable DROPPED TABLE RECOVERY, and restore table space and roll forward
A 41.To minimize database restart time due to rebuilding indexes, which of the following is the correct setting of the INDEXREC configuration parameter?
A. ACCESS
B. RESTART
C. REBUILD
D. DEFERRED
A 42. Given the following information:
Database manager configuration parameter INDEXREC = RESTART
Database configuration parameter INDEXREC = SYSTEM
When will invalid indexes be rebuilt?
A. When the database restarts
B. When the last connection terminates
C. When the invalid index is first accessed
D. When the REORG INDEXES command is issued}
B 43.Which of the following is required to support infinite active log space?
A. USEREXIT = ON, LOGSECOND = 0
B. USEREXIT = ON, LOGSECOND = -1
C. LOGRETAIN = RECOVERY, LOGSECOND = 0
D. LOGRETAIN = RECOVERY, LOGSECOND = -1
C 44. Given the following DB2DIAG.LOG entry:
"Crash Recovery is needed."
Which of the following could be the cause for this entry?
A. A load failed
B. A restore failed
C. A hardware problem
D. A rollforward is required
BC 45. Given the following information:
LIST HISTORY BACKUP ALL FOR <database-alias>;
SQL2160W A damaged recovery history file has been replaced. Processing continued.
Which two of the following can be used to determine previous recovery activity on the database?
A. Re-issue the LIST HISTORY BACKUP command
B. Restore the database from previous backup
C. Restore recovery history file from previous backup
D. Restore just the catalog table space from a previous backup
E. Copy the DB2RHIST.ASC file from another database on the same instance
A 46. Which combination of database configuration parameters limits the database to version recovery only?
A. LOGRETAIN=NO USEREXIT=NO
B. LOGRETAIN=NO USEREXIT=YES
C. LOGRETAIN=RECOVERY USEREXIT=NO
D. LOGRETAIN=RECOVERY USEREXIT=YES
AE 47.Which two of the following are only possible using a redirected restore?
A. Redefining table space containers
B. Replacing a damaged recovery history file
C. Recovering table spaces in roll-forward pending
D. Specifying an alternate log path for roll forward
E. Restoring the database onto a different server with the same operating system}
A 48. Which commands are required to restore a Version 7 backup image of the database SAMPLE to a Version 8 instance?
A. RESTORE DATABASE sample FROM <backupdir>; INTO sample;
B. RESTORE DATABASE sample FROM <backupdir>; INTO sample;
DB2IMIGR;
C. RESTORE DATABASE sample FROM <backupdir>; INTO sample;
MIGRATE DATABASE sample;
D. RESTORE DATABASE sample FROM <backupdir>; INTO sample;
DB2IMIGR;
MIGRATE DATABASE sample;
D 49. An online database backup was started for database MYDB at 1 AM and completed at 3 AM. Which of the following commands will restore the database from this backup image and allow connections to the database?
A. RESTORE DB mydb FROM <backupdir>;;
B. RESTORE DB mydb FROM <backupdir>; WITHOUT ROLLING FORWARD;
C. RESTORE DB mydb FROM <backupdir>;; ROLLFORWARD DATABASE mydb TO 1AM AND STOP;
D. RESTORE DB mydb FROM <backupdir>;; ROLLFORWARD DATABASE mydb TO END OF LOGS AND STOP;}
BD 50. Given the following information:
• A full database backup of DB1 was taken at 1:00PM.
• A table space TS1 was backed up at 1:15PM.
• A table in TS1 was dropped at 2:00PM.
Which two of the following commands are valid after restoring table space TS1 from the full database backup taken at 1:00PM?
A. ROLLFORWARD DATABASE db1 TO 1:15PM
B. ROLLFORWARD DATABASE db1 TO 2:30PM
C. ROLLFORWARD TABLESPACE ts1 TO 2:30PM
D. ROLLFORWARD DATABASE db1 TO END OF LOGS
E. ROLLFORWARD TABLESPACE ts1 TO END OF LOGS
浙公网安备 33010602011771号