I have the following questions for which I am seeking answers from you guys.
1.1 What BIND Package/Plan option ensures that Plan_Table rows are populated.
1.2 What sort order returns rows for all explainable statements in a plan in their logical order.
1.3 What does an INDEXONLY access path indicate for an UPDATE or DELETE?
1.4 & 1.5 answered.
1.6 Identify multiple index access M,MX,MU or MI
1.7 How would you determine that the SQL is a correlated sub-query or non-correlated by looking at plan_table?
1.8 How can you identify a limited partition scan from PLAN_TABLE?
1.9 Explain differences between sequential prefetch and sequential detection.
1.10 SORTN_JOIN = 'Y' for a HYBRID join: Does it indicate that the new table is sorted?
1.11 What clauses usually result in METHOD 3 sorts?
1.12 What is the most important a factor influencing DB2 to make use an index to avoid sort?
1.13 How do you determine that a subquery is transformed into a join? 1.14 How do you determine when a column function is evaluated? When performing a sort to satisfy a GROUP BY clause, when data is being read from table or index or after data retrieval and after sorts?
1.15 SELECT COUNT(*). Can it use an index or does it have to scan all data page rows?
1.16 How can you disable a sequential prefetch for a query.
1.17 What are index screening predicates? ( that are specified on index key columns but are not part of matching columns.
1.18 Matchcols=3 and ACCESSTYPE=N. What does it indicate?
1.19 Under what conditions can an UPDATE statement use an available index?
1.20 When values of join column passed to the inner table are in sequence and the index on join column of inner table is clustered what type of join is the most efficient?
1.21 What feature can be used to influence a block fetch in a distributed application?
1.22 A batch program runs for 10 minutes to update 0.5 million rows commiting 600 times. The program is changed to issue 60000 commits. No other changes. Would the program run longer? Explain your answer.
1.23 Comment ON DELETE RESTRICT on FOREIGN KEY clause of CREATE TABLE STATEMENT.
1.24 How can you prevent DB2 from building a compression dictionary when a LOAD or REORG runs against a compressed tablespace?