managing and optimizing informix dynamic server databases

1. Where are table spaces (tblspaces) created?
A. in dbspaces
B. in pages
C. in extents
D. in chunks
Answer: A

2. What is database administration NOT concerned with?
A. indexing
B. creating tables
C. accessing data
D. application building
Answer: D

3. Disk space for a table is allocated in one or more units called what?
A. tblspaces
B. extents
C. blobs
D. chunks
Answer: B

4. What is a chunk when using raw devices?
A. a contigous unit of disk space.
B. a dynamic unit of disk space.
C. a location in shared memory
D. a data table.
Answer: A

5. Which statement correctly changes the next extent size?
A. ALTER TABLE customer MODIFY NEXT SIZE 300
B. ALTER TABLE customer MODIFY NEXT EXTENT SIZE 300
C. ALTER TABLE customer MODIFY EXTENT SIZE 300
D. ALTER TABLE customer NEXT SIZE 300
Answer: A

6. What is the system catalog?
A. A catalogoued set of index criteria.
B. A set of tables created by the administrator
C. A set of tables that manage the operating system
D. A set of tables that describe the structure of the dataabase.
Answer: A

7. With database logging, where are transaction records first placed?
A. in a temporary database table.
B. in the logical log buffer.
C. in the primary chunk
D. in the physical buffer.
Answer: B

8. Where is fragment information stored?
A. in th system catalog tables.
B. in the physical logs.
C. in the reserved pages.
D. in the first page of the database tblspace.
Answer: A

9. Which two types of fragmentation are allowed in a CREATE TABLE statement?
A. disk striping
B. dbspace
C. indexing
D. round robin
E. mirroring
F. tblspace
G. sorting
H. expression
Answer: DH

10. Why is a hash function advantageous in an expression-based distribution?
A. Because it creates an even distribution of data.
B. Because it creates an uneven distribution of data.
C. Because it does not do expression testing.
D. Because it places all data in one dbspace.
Answer: AB

11. Which two happen when the ALTER FRAGMENT statement is run?
A. Transaction logging, if present, is suspended.
B. The table is locked until the statement is completed.
C. The database is locked until the statement is completed.
D. For databases with logging, the statement executes as a single transaction.
Answer: BD

12. Which fragments an index by expression?
A. FRAGMENT BY EXPRESSION col_1
B. FRAGMENT BY EXPRESSION col_2 > = 10000 IN dbspace1
  AND col_2 < 10000 IN dbspace2
C. FRAGMENT BY EXPRESSION col_1 < 10000
  AND col_2 IN dbspace2
D. FRAGMENT BY EXPRESSION col < 20000 IN dbspace1, col >= 20000 IN dbspace2
Answer: D

13. What are four vaild logical and relational operators that can be used with expression-based distribution?
A. AND
B. IN
C. <
D. >=
E. !=
F. MATCHES
Answer: ABCD

14. In which two situations would you use a round robin fragmentation strategy?
A. When all fragments must be scanned.
B. To create uneven data distributions.
C. When queries perform sequential scans and you have little information about the data being stored.
D. When tables are accessed with a high degress of selectivity.
Answer: AC

15. What are three advantages of fragmentation?
A. higher availability.
B. reduced disk space requirements.
C. parallel scans are possible with PDQ.
D. finer granularity of archives and resources.
Answer: ACD

16. What does the ATTACH clause in the ALTER FRAGMENT statement do?
A. It adds extents to a table.
B. It attaches a raw data file to a table.
C. It combines two unfragmented tables.
D. It combines two fragmented tables.
Answer: C

17. Which statement fragments a table as round robin?
A. FRAGMENT AS ROUND ROBIN IN dbspace1, dbspace2.
B. FRAGMENT TABLE BY ROUND ROBIN IN dbspace1, dbspace2.
C. FRAGMENT BY ROUND ROBIN IN dbspace1, dbspace2.
D. FRAGMENT BY ROUND ROBIN
Answer: C

18. Given that the SQL statement SET LOCK MODE TO WAIT has not been executed, if a statement attempts to alter a row that another process has locked, what happens?
A. The lock is released.
B. An error is returned.
C. The row is immediately updated.
D. The row is updated after the lock is removed.
Answer: B

19. Which isolation level does a database without logging default to?
A. committed read
B. dirty read
C. mode ANSI
D. repeatable read
Answer: B

20. Which three can be locked?
A. a row
B. a page
C. a column
D. a key
E. shared memory
Answer: ABD

21. If there is a lock on an adjacent key, an insert will fail if the isolation level is set to what?
A. committed read
B. repeatable read
C. shared read
D. indexed read
Answer: B

22. Which two are types of database concurrency?
A. sort
B. update
C. read
D. write
Answer: BC

23. What are three kinds of Informix locks?
A. shared lock
B. update lock
C. insert lock
D. exclusive lock
E. repeatable lock
Answer: ABD

24. If only one user is updating a table and no other users are accessing the table, which locking level is the most efficient?
A. row level locking
B. page level locking
C. table level locking in share mode.
D. table level locking in exclusive mode.
Answer: B

25. If the table data is static, what is the most efficient isolation level that can be used?
A. dirty read
B. committed read
C. cursor stability
D. repeatable read
Answer: A

26. What does indexing increase the amount of?
A. disk space used
B. data compression
C. access time
D. shared memory used.
Answer: A

27. When should a column NOT be indexed?
A. When the column contains many duplicate values.
B. When the column is used as part of a join.
C. When the column is used frequently in filter expressions for a SELECT statement.
D. When the column is a foreign key.
Answer: A

28. If a table with the index i_idx becomes unclustered, which statement re-clusters it?
A. RECLUSTER INDEX i_idx
B. ALTER INDEX i_idx TO CLUSTER
C. RECLUSTER INDEX i_idx ON TABLE orders
D. ADD CLUSTER INDEX i_idx.
Answer: D

29. To index a highly duplicate column, which step is most effective?
A. Break the data into multiple tables.
B. Join the column to a column in another table.
C. Use a b+ tree index.
D. Use a composite index with a column which has few duplicates.
Answer: D

30. What does fill factor influence?
A. The percentage of each extent used during table loading.
B. The percentage of a chunk filled before an alarm is generated.
C. The percentage of index nodes used during initial index build.
D. The percentage of log space that a transaction can occupy before rollback occurs.
Answer: C