四川大学 IBM 技术中心 DB2 认证考试复习题集(701) Pre-Assessment/Sample Test for Test /Exam 701-DB2 UDB V8.1for Linux, UNIX and Windows Database Administration Duration 90 minutes Total questions 70 Questions required for a pass 43(61%) 第一套 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 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 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 4.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 5.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 第 1 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 6.AUTHENTICATION=SERVER_ENCRYPT allows DB2 to encrypt which of the following? A.data B.userid C.password D.userid and password 7.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"} 8.Which of the following is required to use the IMPORT utility to import data into a table? A. SYSCTRL authority B. OAD authority on the table C.TER privilege on the table D. MPORT authority on the table E.INSERT privilege on the table 9.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 10.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 11. 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? 第 2 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 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 12.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 13.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 14.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 15. 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 第 3 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 16.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 17.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 18.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.} 19.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} 20.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: 第 4 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) A. Create a new task. B. Send an email or page C. Cancel other scheduled tasks. D. Re-run the task that just completed. 21.What value should the NOTIFYLEVEL configuration parameter have in order to capture all notification messages? A. 5 B. 4 C. 3 D. -1 22.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 23.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 24.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 25.After a successful table space restore command, which of the following states will the table space be in? 第 5 页 四川大学 IBM 技术中心 A.Restore pending DB2 认证考试复习题集(701) B.Recovery pending C.Restore in progress D.Roll forward pending 26.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 27. 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. 28. 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 29.Given the following DDL statements: CREATE TABLE person OF person_t (REF IS OID USER GENERATED) 第 6 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 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 30.Which two of the following objects can be changed with an ALTER statement? A.Index B.Trigger C.Procedure D.Constraint E.Table space 31.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 32.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.} 33.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 34.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 第 7 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 35.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 ... 36.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 37.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 38.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 39.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 第 8 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 40.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 TestNumber CHAR(50) NOT NULL 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 41. 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 42.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 第 9 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 43.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. 44.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 第 10 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) D.SQL0575N View or materialized query table "V1" cannot be used because it has been marked inoperative. 45.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 46.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) 47.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 第 11 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 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 48.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) 49.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} 50.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 51.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) 第 12 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) C.; (semicolon) D." (double quote) 52.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 53.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 54.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 55.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 56.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 第 13 页 四川大学 IBM 技术中心 DEPARTMENT 767 DB2 认证考试复习题集(701) 23 23 5 Which of the following tables will benefit most from being reorganized? A. T1 B. ORG C. SALES D. DEPARTMENT 57.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 58.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 ... 59.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 60.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 61.Which of the following must occur to restore a database, which has table space containers on invalid drives/devices? 第 14 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 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 62.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. 63.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 64.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 64. Given the following information: Database manager configuration parameter INDEXREC = RESTART Database configuration parameter INDEXREC = SYSTEM 第 15 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 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} 65.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 66.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 67.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 68.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 第 16 页 四川大学 IBM 技术中心 D. DB2 认证考试复习题集(701) LOGRETAIN=RECOVERY USEREXIT=YES 69.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 70.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; 71.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; 72.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 第 17 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 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 73.Which of the following tools can be used to catalog a database? A.Journal B.Task Center C.License Center D.Configuration Assistant 74.The table STOCK has the following column definitions: type status quantity price CHAR(1) CHAR(1) INTEGER DEC (7,2) items are indicated to be out of stock by setting STATUS to NULL and QUANTITY and PRICE to zero. Which of the following statements updates the STOCK table to indicate that all the items except for those with TYPE of "S" are temporarily out of stock? A.UPDATE stock SET status='NULL', quantity=0, price=0 WHERE type <> 'S' B.UPDATE stock SET (status, quantity, price) = (NULL, 0, 0) WHERE type <> 'S' C.UPDATE stock SET (status, quantity, price) = ('NULL', 0, 0) WHERE type <>'S' D.UPDATE stock SET status = NULL, SET quantity=0, SET price = 0 WHERE type <>'S' 第 18 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 第二套 1.Which two of the following can collect statistics about tables or indexes? A.REORG B.ANALYZE C.RUNSTATS D.ALTER TABLE E.CREATE INDEX 2.Which of the following DB2 utilities can insert data into a DRDA host database using DB2 Connect? A.LOAD B.EXPORT C.IMPORT D.UPLOAD 3.When creating an index, which of the following allows DB2 to attempt to maintain a physical ordering of the table data? A.Use the PCTFREE parameter B.Designate as clustering index C.Specify that it is the PRIMARY KEY D.Use the ALLOW REVERSE SCANS parameter 4.Which of the following is required to schedule jobs in the Task Center? A.DBADM authority B.SYSADM authority C.SCHEDULE authority D.Any userid and password 5.To see the DDL needed to create objects in an existing database you can do which two of the following? A.Use the db2look utility. B.Run SELECT * FROM SYSCAT.TABLES. C.Click on Show DDL in Visual Explain D.Click on Generate DDL in the Control Center. E.Click on the Catalogs tab in the Control Center. 6.In trying to backup database SAMPLE, the following message is received: SQL1035N The database is currently in use. SQLSTATE=57019 Which of the following should be done to attempt to get the backup to complete successfully? A.Stop the instance B.Reset the connection C.Quiesce the database D.Reissue command with the OFFLINE option 第 19 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 7.The following create index command is issued: CREATE UNIQUE INDEX ix1 ON table1 (org ASC, date1 DESC) INCLUDE (title) Which of the following is TRUE? A.Index IX1 will support forward index scans B.Index IX1 will support reverse index scans C.Columns ORG, DATE1 and TITLE enforce uniqueness D.Column TITLE can be searched on using an index scan 8.A user with SYSADM authority issues the statement: GRANT USE OF TABLESPACE customers TO PUBLIC After the statement is completed, which of the following actions can all users perform? A. Drop the table space CUSTOMERS. B.Create tables in table space CUSTOMERS. C.Drop any table in table space CUSTOMERS. D.Load rows into any table in the table space CUSTOMERS. 9.Which of the following is NOT granted to the group PUBLIC when a database is created? A.CONNECT B.IMPLICIT_SCHEMA C.CREATE_NOT_FENCED_ROUTINE D.SELECT on the system catalog tables and views 10.Given database configuration parameter: AUTORESTART=OFF Transaction ABC was updating data at the time that the server crashed. What will happen to the changes made by transaction ABC that had not committed at the time the server crashed? A.At first connection attempt to the database, uncommitted changes made by transaction ABC will be committed. B.At first connection attempt to the database, uncommitted changes made by transaction ABC will be rolled back. C.When the administrator issues a RESTART DB command, uncommitted changes made by transaction ABC will be committed. 第 20 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) D . When the administrator issues a RESTART DB command, uncommitted changes made by transaction ABC will be rolled back. 11.When creating a database, how many table spaces are created? A.0 B.1 C.2 D.3 E.4 12.Given the following scenario: * A database is configured with circular logging * The database has active connections * A table space within the database is OFFLINE because some of its containers are temporarily inaccessible After the containers become accessible, which of the following can bring the table space ONLINE? A.Using the ACTIVATE DATABASE command B.Using the ALTER TABLESPACE statement C.The next new connection to the database D.Changing from circular logging to log retain 13.For messages written using the db2AdminMsgWrite API, the component listed in the notification log will be which of the following? A.User Function B.Application ID C.User Application D.Admin Message API 14.A DB2 table has just been reorganized. To realize potential improvements in query performance, which of the following should be the next step? A.Collect statistics for the table B.Update the database configuration C.Rebind any packages that access the table D.Drop and recreate each index on the table 第 21 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 15.Which two of the following steps are required on the DB2 server to allow remote client access using TCP/IP? A.Catalog the client workstation B.Create a DB2 instance for TCP/IP C.Set the DB2COMM registry variable D.Update the database manager configuration E.Update the database configuration for each database 16.How can the amount of data returned when listing the tables in the Control Center be limited? A.Use the filter feature of the Details pane. B.Use the filter feature of the Tables folder. C.Create your tables in different table spaces. D.Use the Customize Columns feature of the Details pane 17.How can the amount of data returned when listing the tables in the Control Center be limited? A.Use the filter feature of the Details pane. B.Use the filter feature of the Tables folder. C.Create your tables in different table spaces. D.Use the Customize Columns feature of the Details pane. 18.Given the dynamic SQL statement: SELECT * FROM t1 WHERE customer = ? Which two of the following tools can be used to examine the explain results for this statement? A.dynexpln B.db2exfmt C.db2expln D.Design Advisor E.Visual Explain 19.A non-administrative user has successfully connected to the database. At what level are the privileges defined that control the access this user has to the data? A.The DB2 instance level B.The database object level C.The operating system level D.The database directory level 第 22 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 20.When using the EXPORT utility to extract data from a table, which of the following file formats saves the table attributes as well as any existing indexes in the output file? A.ASC B.DEL C.IXF D.WSF 21.Which of the following can add data to a table with a primary key defined and have all rows which violate the primary key constraint stored in an exception table? A.LOAD B.INSERT C.UPDATE D.IMPORT 22.Given a table space creation statement that does not indicate the size of its containers, which of the following table space types was created by DB2? A.DMS B.LOB C.SMS D.LARGE E.INDEX 23.Which of the following can occur when a view is created on a DB2 table? A.Memory usage is reduced B.Data access time is increased C.Data access control is increased D.Query compilation time is reduced 24.Given the following table definition: CREATE TABLE class_list ( studentId INTEGER, grade INTEGER CONSTRAINT grade# CHECK (grade <= 100) NOT ENFORCED ) and the following contents of the CLASS_LIST table: 第 23 页 四川大学 IBM 技术中心 STUDENTID __________ DB2 认证考试复习题集(701) GRADE __________ 98 100 123 123 101 98 How many records will be retrieved by the following statement? SELECT * FROM class_list WHERE grade = 123 A.0 B.1 C.2 D.3 25.Given the following output from workstation WORK1: List node directory: Node name = REMOTEDB Directory entry type = LOCAL Protocol = TCPIP Hostname= host1.ibm.com List database directory: Database alias = RSAMP Database name Node name = SAMPLE = REMOTEDB Directory entry type = Remote Which of the following will backup the database SAMPLE from workstation WORK1? A.BACKUP DATABASE remote REMOTE remotedb B.ATTACH TO rsamp; BACKUP DATABASE sample C.CONNECT TO sample; BACKUP DATABASE sample D.ATTACH TO remotedb; BACKUP DATABASE sample 26.Given the following command: BACKUP DATABASE sample TO e: WITH 5 BUFFERS Which of the following is the size of the buffer used during the backup? A.54K pages B.1024 4K pages C.The value of DB2_BACKUPSZ registry variable D.The value of BUFFPAGES Database configuration parameter 第 24 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 27.Given the following information: Database server timezone is Atlantic Time Client timezone is Central European Time The following command is issued from the client: ROLLFORWARD DB MYDB TO 2003-10-03-15.13.01.000000 USING LOCAL TIME Which time is used on the rollforward command? A.Atlantic Time B.Central European Time C.UTC time on the client D.UTC time on the server 28.Given a database named MYDB that contains table space MYTS. An offline database backup of database MYDB was started at 1am and completed at 3am. Which of the following commands will restore from the offline backup and allow users to successfully connect to the database MYDB after the restore completes? A.RESTORE DB mydb FROM /backups B.RESTORE DB mydb ONLINE FROM /backups C.RESTORE DB mydb FROM /backups AND STOP D.RESTORE DB mydb TABLESPACE (MYTS) FROM /backups WITHOUT ROLLING FORWARD 29.Which of the following will capture explain snapshots for an embedded static SQL application? A.The EXPLAIN YES bind option B.The EXPLSNAP YES bind option C.The SET CURRENT EXPLAIN SNAPSHOT YES command D.The SET CURRENT EXPLAIN SNAPSHOT EXPLAIN command 30.Use the exhibit button to display the exhibit for this question. The exhibit shows documentation from the System Monitor Guide and Reference. An application is experiencing poor performance that has been surmised to be due to contention on a critical table. Which of the following will help to determine if contention is the issue? A.Turn LOCK monitor switch on and get snapshot for locks. B.Turn LOCK monitor switch on and get snapshot for applications. C.Turn APPLICATION monitor switch on and get snapshot for locks. D.Turn APPLICATION monitor switch on and get snapshot for applications. 第 25 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 31.Which of the following authority levels can force users off the system? A.DBADM B.DBCTRL C.DBMAINT D.SYSCTRL E.SYSMAINT 32.Which of the following commands is required to update an instance on UNIX or Linux to use a newly installed fixpak? A.db2iupdt B.db2icrt C.db2imigr D.db2iconv 33.Which of the following may result in a table queue in an access plan? A.Hash join B.Sort operation C.Nested loop join D.Intra-partition parallelism 34.Health Monitor alerts will be sent to which two of the following locations? A.Journal B.Health Center C.Visual Explain D.Command Center E.Recovery History File 35.Which of the following statements is TRUE when defining an index with ALLOW REVERSE SCANS? A.Two indexes are created B.Index can have INCLUDE columns C.Index must be created in ascending order D.Optimizer must create a temporary table for the reverse scan 36.Given the following command: 第 26 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) CREATE DATABASE inventory CATALOG TABLESPACE MANAGED BY SYSTEM USING ('path1','path2') EXTENTSIZE 16 PREFETCHSIZE 32 USER TABLESPACE MANAGED BY SYSTEM USING ('patha') TEMPORARY TABLESPACE MANAGED BY DATABASE USING ( FILE 'filea' 1000) WITH "EXTENTSIZE 8" How many bufferpools are in the inventory database? A.0 B.1 C.2 D.3 E.4 37.Given the following statements: CREATE REGULAR TABLESPACE new_tbsa PAGESIZE 32K MANAGED BY DATABASE USING (FILE 'filea' 1000, FILE 'fileb' 1000) CREATE LARGE TABLESPACE new_tbsb MANAGED BY DATABASE USING (FILE 'filec' 1000, FILE 'filed' 1000) CREATE TABLE tab1 (col1 CLOB (30K), col2 CHAR (30)) IN new_tbsa LONG IN new_tbsb Records are inserted into TAB1. In which of the following locations is the data placed? A.OL1 and COL2 data will go into NEW_TBSA B.COL1 and COL2 data will go into NEW_TBSB C.COL1 data will go into NEW_TBSA and COL2 data will go into NEW_TBSB D.COL1 data will go into NEW_TBSB and COL2 data will go into NEW_TBSA 38.Given that there are two containers in table space TS1 and the following statement: ALTER TABLESPACE ts1 REDUCE (ALL 100 M) Which of the following describes the result of the statement? A.he total size of table space TS1 will be reduced to 100 MB. B.ach container in table space TS1 will be reduced in size to 100 MB. C.ach container in table space TS1 will be reduced in size by 100 MB. 第 27 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) D.ach container in table space TS1 that has no data will be reduced in size to 100 MB. E.ach container in table space TS1 that has no data will be reduced in size by 100 MB. 39.A DBA has created three related backup tasks in the Task Center. After all three of these tasks are completed, a fourth task must be run. Which of the following describes how the DBA can automate the running of the fourth task based on the results of the first three tasks? A.ndicate on the schedule for the fourth task that it is to begin when the first three tasks are completed. B.et a notification on the first three tasks that sends a note to the DBA indicating that the fourth task can be initiated. C.reate a grouping task for the three backup tasks. Create a task action for the fourth task to be run on the completion of the group. D.hen creating the fourth task, indicate on the Success Code Set field that the first three tasks must run successfully before running this one. 40.hich two of the following Task Types can be created in the DB2 Task Center? A.S command scripts B.B2 command scripts C.C application programs D.Control Center extensions E.System initialization scripts 41.Which of the following actions ensures that a table is created with two columns as the parent key for a foreign key relationship? A.reate the table and then create a unique index on the two columns B.reate the table and indicate the two columns are the partitioning key C.reate the table and then alter the table to define a check constraint on the two columns D.reate the table and then alter the table to define a unique constraint on the two columns 42.iven the table definition: CREATE TABLE class_list (student_ID grade INTEGER, INTEGER CONSTRAINT chk CHECK (grade <= 100) ) and the statement: INSERT INTO class_list VALUES (98, 100), (123,123), (101, 98) 第 28 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) How many records will be retrieved by the following statement? SELECT * FROM class_list A.0 B.1 C.2 D.3 43.Given the following table definition: CREATE TABLE class_list (studentId INTEGER, grade INTEGER CONSTRAINT chk CHECK (grade <= 100) NOT ENFORCED ) and the statement: INSERT INTO class_list VALUES (98, 100), (123,123), (101, 98) How many records will be retrieved by the following statement? SELECT * FROM class_list A.0 B.1 C.2 D.3 44.Which of the following System Catalog Table columns can be examined to see if a foreign key has been validated after a load? A.SYSCAT.TABLES.CONST_VALID B.SYSCAT.TABLES.FOREIGN_KEY C.SYSCAT.TABLES.KEYS_CHECKED D.SYSCAT.TABLES.CONST_CHECKED 45.Given the following information derived from GET DBM CFG SHOW DETAIL: Description: Parameter: Size of instance shared memory (4KB) INSTANCE_MEMORY Current Value: AUTOMATIC(3010) Delayed Value: 33110 Current Value: AUTOMATIC(3010) indicates which two of the following? 第 29 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) A.The Design Advisor supplied the value of 3010 4KB pages to be used. B.DB2 calculated the amount of instance memory needed for the current configuration. C.On the next db2start, DB2 will reserve a minimum of 3010 4KB pages for the instance's shared memory. D.On the next database activation, DB2 will reserve a minimum of 3010 4KB pages for the instance's shared memory. E.The minimum amount of instance shared memory required for the instance configuration at db2start was 3010 4KB pages. 46.Given the following results from GET DBM CFG SHOW DETAIL: Description: Parameter: Size of instance shared memory (4KB) INSTANCE_MEMORY Current Value: AUTOMATIC(3010) Delayed Value: 33110 What does "Delayed Value: 33110" indicate? A.On next db2start, the instance shared memory will be a minimum of 3010 4KB pages. B.On next db2start, the instance shared memory will be a minimum of 33110 4KB pages. C.On next db2start, DB2 will automatically calculate the required instance shared memory. D.The instance shared memory will be increased to 33110 4KB pages as soon as the operating system can make the memory available. 47.Given the following scenario using the Command Line Processor (CLP): The size of buffer pool BP1 is currently 25000 pages user1 issues: CONNECT TO db1 user2 issues: CONNECT TO db1 user1 issues: ALTER BUFFERPOOL bp1 SIZE 10000 user1 issues: CONNECT RESET user1 issues: CONNECT TO db1 When does the new size of the BP1 buffer pool become effective? A.When database DB1 is backed up B.When the database is re-activated C.When USER1 re-connected to the database D.When USER1 issued the ALTER BUFFERPOOL statement 48.Which of the following commands will cause explain information to be captured for SQL statements issued from the Command Line Processor (CLP) in the same session? 第 30 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) A.db2 -explain <sql statement> B.SET CURRENT EXPLAIN CAPTURE EXPLAIN C.REBIND CLP PACKAGE WITH EXPLSNAP YES D.SET CURRENT EXPLAIN SNAPSHOT EXPLAIN 49.Which of the following will capture explain snapshot information for applications that include embedded static and dynamic SQL? A.The EXPLSNAP YES bind option B.The EXPLSNAP ALL bind option C.The SET CURRENT EXPLAIN SNAPSHOT YES command D.The SET CURRENT EXPLAIN SNAPSHOT ALL command 50.Given the following scenario: User1 issues the following commands: --------------------------------------------UPDATE DBM CFG USING DFT_MON_BUFFERPOOL ON UPDATE MONITOR SWITCHES USING LOCK OFF Then User2 issues the following commands: --------------------------------------UPDATE DBM CFG USING DFT_MON_SORT ON UPDATE MONITOR SWITCHES USING LOCK ON If the DB2 instance is stopped and restarted after these commands are issued and User2 reattaches, which of the following database monitor switches are active for User2? A.SORT, LOCK B.BUFFERPOOL, SORT C.BUFFERPOOL, LOCK D.BUFFERPOOL, LOCK, SORT 51.Which two of the following are valid database snapshot monitor switches? A.UOW B.DEADLOCK C.STATEMENT D.TABLESPACE E.CONNECTION 第 31 页 四川大学 IBM 技术中心 DB2 认证考试复习题集(701) 52.Which of the following would provide the ability to query transaction activity using SQL statements? A.GET SNAPSHOT FOR TRANSACTIONS ON db-name WRITE TO FILE B.GET SNAPSHOT FOR TRANSACTIONS ON db-name WRITE TO TABLE C.CREATE EVENT MONITOR ev1 FOR TRANSACTIONS WRITE TO TABLE D.CREATE EVENT MONITOR ev1 FOR TRANSACTIONS WRITE TO FILE '/db2event' 53.Which two of the following will retrieve the dynamic statements currently contained in the statement cache? A.SET CURRENT EXPLAIN MODE EXPLAIN B.UPDATE DBM CFG USING DFT_MON_STMT ON C.GET SNAPSHOT FOR DYNAMIC SQL ON db-name D.CREATE EVENT MONITOR ev2 FOR STATEMENTS WRITE TO TABLE E.SELECT * FROM TABLE(SNAPSHOT_DYN_SQL('db-name',-1)) AS tab 54.Instances that are down are reported in the Health Center as which of the following? A.Alarm B.Normal C.Warning D.Attention 55.Given the following statement: IMPORT FROM datafile.ixf OF IXF REPLACE_CREATE INTO table1 Which of the following describes how table and index definitions are affected if data is imported into an existing table? A.Index definitions are changed B.Table definitions are changed C.Table and index definitions are created D.Table and index definitions are not changed 56.In order to resume a failed load operation without reloading the entire input file, which of the following options should be specified during the initial LOAD? A.ROWCOUNT B.SAVECOUNT C.CHECKPOINT D.WARNINGCOUNT 第 32 页
© Copyright 2024