FAQs(196 to 210)

196) Are views updatable ?

A) Not all of them. Some views are updatable e.g. single table view with all the fields or mandatory fields. Examples of non-updatable views are views which are joins, views that contain aggregate functions (such as MIN), and views that have GROUP BY clause.

197) If I have a view which is a join of two or more tables, can this view be updatable? 

A) No.

198) What are the 4 environments which can access DB2 ?

A) TSO, CICS, IMS and BATCH

199) What is an inner join, and an outer join ?

A) Inner Join: combine information from two or more tables by comparing all values that meet the search criteria in the designated column or columns of one table with all the values in corresponding columns of the other table or tables. This kind of join which involve a match in both columns are called inner joins.

Outer join : Is one in which you want both matching and non matching rows to be returned. DB2 has no specific operator for outer joins, it can be simulated by combining a join and a correlated sub query with a UNION.

200) What is FREEPAGE and PCTFREE in TABLESPACE creation?

A) PCTFREE: percentage of each page to be left free

FREEPAGE: Number of pages to be loaded with data between each free page

201) What is RUNSTATS?

A) A DB2 utility used to collect statistics about the data values in tables which can be used by the optimizer to decide the access path. It also collects statistics used for space management. These statistics are stored in DB2 catalog tables.

202) When will you chose to run RUNSTATS?

A) After a load, or after mass updates, inserts, deletes, or after REORG.

203) Give some example of statistics collected during RUNSTATS?

A) Number of rows in the table, Percent of rows in clustering sequence, Number of distinct values of indexed column, Number of rows moved to a nearby/fairway page due to row length increase

204) What is REORG? When is it used?

A) REORG reorganizes data on physical storage to reclutser rows, positioning overflowed rows in their proper sequence, to reclaim space, to restore free space. It is used after heavy updates, inserts and delete activity and after segments of a segmented tablespace have become fragmented.

205) What is IMAGECOPY ? 

A) It is full backup of a DB2 table which can be used in recovery.

206) When do you use the IMAGECOPY? 

A) To take routine backup of tables, After a LOAD with LOG NO and After REORG with LOG NO

207) What is COPY PENDING status?

A) A state in which, an image copy on a table needs to be taken, In this status, the table is available only for queries. You cannot update this table. To remove the COPY PENDING status, you take an image copy or use REPAIR utility.

208) What is CHECK PENDING ?

A) When a table is LOADed with ENFORCE NO option, then the table is left in CHECK PENDING status. It means that the LOAD utility did not perform constraint checking.

209) What is QUIESCE?

A) A QUIESCE flushes all DB2 buffers on to the disk. This gives a correct snapshot of the database and should be used before and after any IMAGECOPY to maintain consistency.

210) What is a clustering index ? 

A) Causes the data rows to be stored in the order specified in the index. A mandatory index defined on a partitioned table space.