General DB2 Administration MCQs

1. What is a federated database?

2. What object types is the lock mode S (Share) applicable to?

3. A DBA wishes to audit all access to the non-audited table OWNER.Smartemp. Assuming no audit traces are started, which of the following steps are needed to audit access to this table?

4. Which two of the following types of storage management methods are supported by DB2 OLAP Server?

5. When writing a query with a primary key, how would the DISTINCT clause be applied?

6. Why would you want to specify an optimization class when compiling an SQL query?

7. Which is required to use the IMPORT utility to import data into a table?

8. On the client side, what is linked with the DB2 Universal Database client library?

9. When would phantom read phenomenon occur?

10. Which of the following are fields from SQLCA

11. What is an example of an environmental consideration in DB2 administration?

12. If you wish to update your statistics after a change in your database, what utility can be executed?

13. What is the primary focus of performance tuning, and how should performance tuning should take place?

14. What is an example of an instance when the database manager would allocate memory?

15. REORG is a function that organizes data on physical storage to recluster rows, positioning overflowed rows to reclaim space and to free up space. When is it used?

16. When is it best to tune your system?

17. Parameter that would specify the maximum size of a memory segment?

18. Is the autorestart parameter configurable on-line?

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

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

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

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

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

24. What is a DB2 IMAGECOPY

25. What is the benefit of prefetching data?

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

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

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

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

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

31. Under what context is pushdown analysis utilized?

32. What are EDUs responsible for?

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

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

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

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

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

38. Parameter to configure the default database path?

39. Which can be changed with an ALTER statement

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

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

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

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

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

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

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

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

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

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

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

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

52. What type of parameter is “dbheap”?

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

54. Which is correct about the EXPLAIN statement

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

56. What is intra-partition parallelism?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

79. What does the term EXPLAIN denote?

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

81. Index cardinality represents:

82. What is a clustering index?

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

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

85. What are partitioned table spaces?

86. What is RUNSTATS?

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

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

89. The various locks available are _____________.  

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

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

92. The various locking levels available are: 

93. What happens during a DB2 Checkpoint?

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

95. What is dynamic SQL?

96. What is a DB2 access path?

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

98. What is a DB2 bind?

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

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

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

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

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

104. What does the term IMAGECOPY mean?

105. What does sqlcode -922 denote?

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

107. Where is the output of EXPLAIN stored?

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

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

110. Can you use LOCK TABLE on a view?

111. What are segmented table spaces?

112. Which function does QUIESCE perform?

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

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

115. What is a deadlock?

116. what is the root page in DB2?

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

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