DB2 Administration MCQ Questions Answers

1. Which can a DBA do to determine what an image copies, active logs and archive logs will  be required for the recovery of a table space?

2. Which table space characteristics for a system Managed (SMS) table space can be modified with the ALTER TABLESPACE Statements?

3. what is the root page in DB2?

4. What is a deadlock?

5. Which COPY utility statements will backup table space DBTAB1.TS1 preparing 2 copies for local use and 2 copies for disater recovery use?

6. Which index type will cause the rows of the table to be arranged on the disk according to the ordering of their index keys?

7. Which function does QUIESCE perform?

8. What are segmented table spaces?

9. Can you use LOCK TABLE on a view?

10. How do you perform the EXPLAIN of a dynamic SQL statement?

11. Which cannot be done with the ALTER TABLE statement?

12. Where is the output of EXPLAIN stored?

13. A DBA issues the command:-DIS DB(DSNDB06) SPACE(*) LOCKS. In the LOCKINFO field the following data is reported: H-IS,S,C. When Will the lock first be released by DB2?

14. What does sqlcode -922 denote?

15. What does the term IMAGECOPY mean?

16. Which is true when joining a table with DB2 Relational Connect function?

17. How many clustering indexes can be defined for a table?

18. If a user accidentally drops a table and commits work, which of the following recovers the dropped table?

19. When is the access path determined for dynamic SQL?

20. Which is not true for a segmented table space?

21. What is a DB2 bind?

22. How does DB2 determine which lock-size to use?

23. What is a DB2 access path?

24. What is dynamic SQL?

25. Can you update a view which is based on more than one table?

26. What happens during a DB2 Checkpoint?

27. The various locking levels available are: 

28. Which statement will gather only the index statistics for the table john.table1?

29. What do you need to do before you perform EXPLAIN?

30. The various locks available are _____________.  

31. When is the authorization check on DB2 objects done - at BIND time or run time?

32. Which ALTER TABLESPACE statements causes DB2 to record data page changes in the space map pages of a segmented table space DSN8D71A.DSN8S71E?

33. What is RUNSTATS?

34. What are partitioned table spaces?

35. Which command will give the minimum point in time recovery for table spaces of a recoverable database?

36. Which DB2 catalog table can be used to determine if a user has authorization to bind a new plan?

37. What is a clustering index?

38. Index cardinality represents:

39. Which is required to import data into a table?

40. What does the term EXPLAIN denote?

41. Is the parameter, “database_memory” configured automatically?

42. Which allows index data to be stored on separate devices from table data?

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

44. What technique can be used to reduce the number of calculations in a query?

45. What does it mean if the the null indicator = 0

46. Which of the following DSNZPARMs is necessary to influence access path selection for certain queries?

47. Given the following statement: CREATE TABLESPACE smt1 MANAGED BY DATABASE USING (FILE 'smt01' 1024K) How many pages will be created for the table space?

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

49. What does it mean if the the null indicator = -1

50. To set up a client that can access DB2 UDB through DB2 Connect Enterprise Edition, which of the following is the minimum software client that must be installed?

51. Which of the following RACF profiles would be used to allow IMS to access DB2 DSN1?

52. What parameter and option must be set before using a DB2 DBMS?

53. Which explicit system privileges allows the user to create new plans and packages without being able to also execute them?

54. An audit trace shows that TSO user TSOID1 with DBADM authority, is continually attempting to update a table in DB2 subsystem DSN1 that is not supposed to be updated. Which of the following will prevent access to the DB2 subsystem?

55. If you have a poorly clustered index with random synchronous I/Ss, what clause would you use to prevent an impending delay?

56. Given that there are two containers in table space Smart1 and the following statement: ALTER TABLESPACE Smart1 REDUCE (ALL 100 M) Which of the following will happen?

57. Given the following table definition: CREATE TABLE smarterer_list (empid INTEGER, points INTEGER CONSTRAINT chk CHECK (points <= 100) NOT ENFORCED ) and the statement: INSERT INTO smarterer_list VALUES (98, 100), (123,123), (101, 98) How many records will be retrieved by the following statement? SELECT * FROM smarterer_list

58. What table space characteristic can affect how your compiler operates?

59. Which of the following is not a statistic collected during RUNSTATS

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

61. Which can be done by a user who is granted the CONTROL privilege on an INDEX?

62. What is a technique to access non-contiguous data pages more efficiently?

63. What is intra-partition parallelism?

64. Which table space allows LOBs to use the filesystem cache?

65. Which is correct about the EXPLAIN statement

66. A DBA needs to use the DSN command processor to delete DB2 packages that are no longer needed. Which of the following choices is correct for the DBA to use?

67. What type of parameter is “dbheap”?

68. What is one way that a query might be rewritten?

69. To prepare an embedded SQL program for use with a host-language compiler, which of the following database components is required?

70. Which tool can be used to edit related backup tasks created in the Task Center

71. Which action will occur when issuing the command FORCE APPLICATION ALL?

72. Given the following code: EXEC SQL EXECUTE IMMEDIATE: sqlstmt Which of the following values must sqlstmt contain so that all rows are deleted from the STAFF table?

73. If your columns are indexed, what clause would you run for the RUNSTATS command to collect statistics?

74. What two types of configuration files does DB2 use?

75. What should be considered when specifying an optimization level?

76. What condition would lead to distribution statistics not being collected?

77. Where would the optimizer gather information to estimate the amount of prefetching for a tablespace?

78. At which times is the access control authorization routine (DSNX@XAC) invoked?

79. If an object is created statically by a role within a trusted context and the ROLE AS OBJECT OWNER clause is specified, who becomes the object owner when executing the package?

80. Which can be changed with an ALTER statement

81. Parameter to configure the default database path?

82. What does it mean if the the null indicator = -2

83. Important step in developing a performance-improvement process?

84. Which two of the following identify which users have SYSCTRL authority?

85. Scenario in which you would be using a single buffer pool?

86. What calculation can you use to estimate your overhead cost?

87. What are EDUs responsible for?

88. Under what context is pushdown analysis utilized?

89. After entering a query, what is the first step the SQL compiler takes?

90. Which must be set to restrict clients from being able to discover any DB2 instances on a server?

91. In a UNIX-based environment, where would the database manager configuration file be found?

92. Why would you need to execute RUNSTATS regularly when using the SQL compiler?

93. I have 5 SQL Select statements connected by a Union/Union All. How many times should I have to specificy union to eliminate duplicate rows?

94. What is the benefit of prefetching data?

95. What is a DB2 IMAGECOPY

96. Which DB2 object allows multiple users to access data in a table with each user only being able to access certain portions of the data?

97. When working with concurrency controls, what happens when the number of locks held on rows and tables is equal to the percentage of the locklist specified by maxlocks?

98. Why is the deadlock detector an important part of DB2 architecture?

99. Given the code: EXEC SQL DECLARE cursor1 CURSOR FOR SELECT name, age, b_date FROM person; EXEC SQL OPEN cursor1; Under which of the following situations will the above cursor be implicitly closed?

100. Why is a primary key needed on a table?