FAQs(211 to 225)

211) How many clustering indexes can be defined for a table?

A) Only one.

212) What is the difference between primary key & unique index ?

A) Primary Key: a relational database constraint. Primary key consists of one or more columns that uniquely identify a row in the table. For a normalized relation, there is one designated primary key.

Unique index: a physical object that stores only unique values. There can be one or more unique indexes on a table.

213) What is sqlcode -922 ?

A) Authorization failure

214) What is sqlcode -811?

A) SELECT statement has resulted in retrieval of more than one row.

215) What does the sqlcode of -818 pertain to? 

A115) This is generated when the consistency tokens in the DBRM and the load module are different.

216) What are simple, segmented and partitioned table spaces ?

A) Simple Tablespace: Can contain one or more tables. Rows from multiple tables can be interleaved on a page under the DBA?s control and maintenance Segmented Tablespace: Can contain one or more tables. Tablespace is divided into segments of 4 to 64 pages in increments of 4 pages. Each segment is dedicated to single table. A table can occupy multiple segments Partitioned Tablespace: Can contain one table. Tablespace is divided into parts and each part is put in a separate VSAM dataset.

217) What is filter factor?

A) One divided by the number of distinct values of a column.

218) What is index cardinality? 

A) The number of distinct values a column or columns contain.

219) What is a synonym ?

A) Synonym is an alternate name for a table or view used mainly to hide the leading qualifier of a table or view.. A synonym is accessible only by the creator.

220) What is the difference between SYNONYM and ALIAS?

A) SYNONYM : is dropped when the table or tablespace is dropped. Synonym is available only to the creator.

ALIAS : is retained even if table or tablespace is dropped. ALIAS can be created even if the table does

not exist. It is used mainly in distributed environment to hide the location information from

programs. Alias is a global object & is available to all.

221) What do you mean by NOT NULL WITH DEFAULT? When will you use it?

A) This column cannot have nulls and while insertion, if no value is supplied then it will have zeroes, spaces or date/time depending on whether it is numeric, character or date/time.Use it when you do not want to have nulls but at the same time cannot give values all the time you insert this row.

222) What do you mean by NOT NULL? When will you use it?

A) The column cannot have nulls. Use it for key fields.

223) When would you prefer to use VARCHAR?

A) When a column which contains long text, e.g. remarks, notes, may have in most cases less than 50% of the maximum length.

224) What are the disadvantages of using VARCHAR?

A) Can lead to high space utilization if most of the values are close to maximum.

Positioning of VARCHAR column has to be done carefully as it has performance implications.

Relocation of rows to different pages can lead to more I/Os on retrieval.

225) How do I create a table MANAGER (EMP-NO, MANAGER) where

MANAGER is a foreign key which references to EMP-NO in the same table? Give the exact DDL.

A) First CREATE MANAGER table with EMP-NO as the primary key. Then ALTER it to define the foreign key.