181) What happens to the PLAN if index used by it is dropped?
A) Plan is marked as invalid. The next time the plan is accessed, it is rebound.
182) What are PACKAGES ?
A) They contain executable code for SQL statements for one DBRM.
183) What are the advantages of using a PACKAGE?
A) The advantages of using PACKAGE are:
1. Avoid having to bind a large number of DBRM members into a plan
2. Avoid cost of a large bind
3. Avoid the entire transaction being unavailable during bind and automatic rebind of a plan
4. Minimize fallback complexities if changes result in an error.
184) What is a collection?
A) A user defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages.
185) How do you insert a record with a nullable column?
A) To insert a NULL, move -1 to the null indicator, To insert a valid value, move 0 to the null indicator
186) In SPUFI suppose you want to select maximum of 1000 rows, but the select returns only 200 rows. What are the 2 SQLCODEs that are returned?
A) +100 (for successful completion of the query), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).
187) How would you print the output of an SQL statement from SPUFI?
A) Print the output dataset.
188) Lot of updates have been done on a table due to which indexes have
gone haywire. What do you do?
A) Looks like index page split has occurred. DO a REORG of the indexes.
189) What is dynamic SQL?
A) Dynamic SQL is a SQL statement created at program execution time.
190) When is the access path determined for dynamic SQL?
A) At run time, when the PREPARE statement is issued.
191) Suppose I have a program which uses a dynamic SQL and it has been performing well till now. Off late, I find that the performance has deteriorated. What happened?
A) There may be one of the following reasons:
Probably RUN STATS is not done and the program is using a wrong index due to incorrect stats.
Probably RUNSTATS is done and optimizer has chosen a wrong access path based on the latest statistics.
192) How does DB2 store NULL physically?
A) As an extra-byte prefix to the column value. Physically, the null prefix is Hex '00' if the value is present and Hex 'FF' if it is not.
193) How do you retrieve the data from a nullable column?
A) Use null indicators. Syntax ... INTO :HOSTVAR:NULLIND
194) What is the picture clause of the null indicator variable?
A) S9(4) COMP.
195) What does it mean if the null indicator has -1, 0, -2?
A) -1 : the field is null; 0 : the field is not null; -2 : the field value is truncated