40 NOT Answered Yet Test Questions:
1. Which DB2 catalog table can be used to determine if a user has authorization to bind a new plan?
Answers:
• SYSDBAUTH
• SYSTABAUTH
• SYSUSERAUTH
• SYSPLANAUTH
2. What do you need to do before you perform EXPLAIN?
Answers:
• Create PLAN_TABLE under the AUTHID
• Create EXPLAIN under the AUTHID
• Create SPUFI under the AUTHID
• Create QMF under the AUTHID
3. Which of the following commands will give the minimum point in time recovery for table spaces of a recoverable database?
Answers:
• Get dbm cfg
• List minimum point in time
• List tablespace show detail
• Get db cfg for <database-alias>
4. 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?
Answers:
• COMMIT
• Deallocation
• When the table space is stopped
• When DB2 moves to the next page in the table
5. What is a clustering index?
Answers:
• An index that stores only unique values
• A mandatory index defined on a partitioned table space
• An index Which is on more than one key
• None of the above
6. Which of the following COPY utility statements will backup table space DBTAB1.TS1 preparing 2 copies for local use and 2 copies for disater recovery use?
Answers:
• Copy Tablespace DBTAB1.TS1 LOCAL(2) OFFSITE(2)...
• Copy Tablespace DBTAB1.TS1 COPYDDN(DD1,DD2,DD3,DD4)...
• Copy Tablespace DBTAB1.TS1 LOCAL(DD1,DD2) OFFSITE(DD3,DD4)...
• Copy Tablespace DBTAB1.TS1 COPYDDN(DD1,DD2)RECOVERYDDN(DD3,DD4)...
7. Which of the following cannot be done with the ALTER TABLE statement?
Answers:
• Change the cache value for an identity column
• Turn on auditing for the table
• Add a primary key to the table
• Add a column to the end of a table row
8. What happens during a DB2 Checkpoint?
Answers:
• Resource managers are invoked to write their status to the log
• Data Manager writes DBE table log records
• Both a and b
• None of above
9. what is the root page in DB2?
Answers:
• It is a simple DB2 index
• It is a B-tree
• It is a B-tree's top page
• It is a clustered index
10. The various locking levels available are:
Answers:
• User, Schema
• Page, Table, Tablespace
• Database, Segment
• Segment, Extent
11. Which of the following statements will gather only the index statistics for the table john.table1?
Answers:
• Runstats on table john.table1 indexes all
• Runstats on table john.table1 for indexes all
• Runstats on table john.table1 on indexes all
• Runstats on table john.table1 and indexes all
12. When is the access path determined for dynamic SQL?
Answers:
• At run time
• At compile time
• At bind time
• All of the above
13. What is a deadlock?
Answers:
• A deadlock occurs when two or more programs are read simultaneously
• When transaction fails it is called a deadlock
• A deadlock occurs when transactions are executed at the same time
• None of the above
14. What is a DB2 bind?
Answers:
• A process that builds an access path to DB2 tables
• A process that accesses index
• A process that defines table structure
• None of the above
15. Index cardinality represents:
Answers:
• The number of distinct values a column Contains
• The number of duplicate values a column Contains
• The Index which is on more than one key
• None of above
16. What are segmented table spaces?
Answers:
• Table spaces divided into segments of 1 to 10 pages
• Table spaces divided into segments of 4 to 64 pages
• Table spaces containing only one table
• None of the above
17. How many clustering indexes can be defined for a table?
Answers:
• Two
• four
• One
• Six
18. Which of the following ALTER TABLESPACE statements causes DB2 to record data page changes in the space map pages of a segmented table space DSN8D71A.DSN8S71E?
Answers:
• ALTER TABLESPAcE dsn8d71a.dsn8s7ie log yes;
• ALTER TABLESPAcE dsn8d71a.dsn8s7ie close yes;
• ALTER TABLESPAcE dsn8d71a.dsn8s7ie Trackmod yes;
• ALTER TABLESPAcE dsn8d71a.dsn8s7ie changemod yes;
19. How does DB2 determine which lock-size to use?
Answers:
• It is based on the lock-size given while creating the tablespace
• The programmer may direct the DB2 on which lock-size to use
• It is not possible to dertermine the lock-size
• None of above
20. The various locks available are _____________.
Answers:
• Share
• Exclusive
• Update
• All of the above
21. What is a DB2 access path?
Answers:
• It is a method used to access data specified in DB2
• It is an application
• It is a package
• It is a method user to show the execution plan
22. If a user accidentally drops a table and commits work, which of the following recovers the dropped table?
Answers:
• Once commited the dropped table cannot be recovered
• If ALTER TABLESPACE..DROPPED TABLE RECOVERY on enabled,restore tablespace,role forward
• If ALTER TABLESPACE..DROPPED TABLE RECOVERY on not enabled, first alter tablespace and then restore database and rollforward
• If ALTER TABLESPACE..DROPPED TABLE RECOVERY on not enabled, first alter tablespace and then restore tablespace and rollforward
23. What does the term IMAGECOPY mean?
Answers:
• It is a full backup of a DB2 database
• It is a full backup of a DB2 redolog
• It is a full backup of a DB2 table
• All of the above
24. Where is the output of EXPLAIN stored?
Answers:
• In SYSIBM schema
• In userid.PLAN_TABLE
• IN SPUFI
• None of above
25. Which function does QUIESCE perform?
Answers:
• It flushes all DB2 buffers on to the disk
• It gives a correct snapshot of the database
• It is a LOAD utility
• None of the above
26. Can you update a view which is based on more than one table?
Answers:
• Yes
• No
27. Which of the following table space characteristics for a system Managed (SMS) table space can be modified with the ALTER TABLESPACE Statements?
Answers:
• Extent Size
• Prefetch Size
• Number of containers
• Size of an existing container
28. What is dynamic SQL?
Answers:
• An SQL statement written in procedure
• An SQL statement written in cursor
• An SQL statement created at program execution time
• None of the above
29. What does sqlcode -922 denote?
Answers:
• Database failure
• Network failure
• Authorization failure
• Instance failure
30. What does the term EXPLAIN denote?
Answers:
• A precompiler
• A utility to run a DB2 batch program
• It displays the access path as determined by the optimizer
• A BIND step for embedded SQL
31. Can you use LOCK TABLE on a view?
Answers:
• Yes
• No
32. What are partitioned table spaces?
Answers:
• Table spaces divided into segments
• Table spaces divided into parts
• Table spaces containing one or more tables
• All of the above
33. Which of the following 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?
Answers:
• Read the SYSIBM.SYSCOPY table
• Run the Report Recovery utility
• Run the COPY TABLESPACE utility
• Run the MERGECOPY TABLESPACE utility
34. Which of the following is not true for a segmented table space?
Answers:
• It is more efficient for table spaces greater than 4 GB
• It makes free pages available immediately after a commit
• It generally skips empty pages when a table scan is being processed
• It has more efficient space map processing than a simple tablespace
35. How do you perform the EXPLAIN of a dynamic SQL statement?
Answers:
• EXPLAIN of a dynamic SQL cannot be done
• By using SPUFI or QMF to EXPLAIN the dynamic SQL statement
• By including EXPLAIN command in the embedded dynamic SQL statements
• None of above
36. Which of the following is true when joining a table with DB2 Relational Connect function?
Answers:
• DB2's cost based optomizer is turned off for Federated database requests
• The DB2 optimizer is not aware that all tables are not in the same DB2 instance
• The DB2 optimizer is aware that some tables are on remote machines, but will not perform any optimization on SQL sent to the remote database
• The DB2 optimizer is aware that some tables are on remote machines, and where appropriate will rewrite SQL before sending it to the remote machine
37. Which of the following is required to import data into a table?
Answers:
• SYSCTRL
• LOAD authority on the table
• ALTER privilege on the table
• IMPORT authority on the table
• INSERT privilege on the table
38. What is RUNSTATS?
Answers:
• It is a package
• It is a utility used to collect statistics
• It is a Collection
• It is a DB2 Procedure
39. When is the authorization check on DB2 objects done - at BIND time or run time?
Answers:
• At bind time
• It is never done
• At run time
• At compile time
40. Which of the following index type will cause the rows of the table to be arranged on the disk according to the ordering of their index keys?
Answers:
• Unique Index
• Clustered Index
• Non-unique Index
• Index with include column
No comments:
Post a Comment