What is CHECK PENDING?

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.

What is COPY PENDING status?

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.

When do you use the IMAGECOPY?

To take routine backup of tables

After a LOAD with LOG NO

After REORG with LOG NO

What is IMAGECOPY?

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

What is REORG? When is it used?

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.

When will you chose to run RUNSTATS?

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

What is RUNSTATS?

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.

How do you insert a record with a nullable column?

To insert a NULL, move -1 to the null indicator

To insert a valid value, move 0 to the null indicator.

What does it mean if the null indicator has -1, 0, -2 ?

-1  : the field is null

  0 : the field is not null

-2  : the field value is truncated

How do you retrieve the data from a nullable column?

Use null indicators. Syntax ... INTO :HOSTVAR:NULLIND

What is index cardinality?

The number of distinct values a column or columns contain.

What is a synonym ?

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.

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

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.

What is the difference between SYNONYM and ALIAS?

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 info from programs. Alias is a global object & is available to all.

What are the disadvantages of using VARCHAR?

• 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.

When would you prefer to use VARCHAR?

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

How do I create a table MANAGER ( EMP#, MANAGER) where MANAGER is a foreign key which references to EMP# in the same table? Give the exact DDL.

First CREATE MANAGER table with EMP# as the primary key. Then ALTER it to define the foreign key. When is the authorization check on DB2 objects done - at BIND time or run time? At run time.

What are the 2 sqlcodes that are returned?

100 ( for successful completion of the query ), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).

What is a collection?

A user defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages. In SPUFI suppose you want to select max. of 1000 rows , but the select returns only 200 rows.

What are the advantages of using a PACKAGE?

·   Avoid having to bind a large number of DBRM members into a plan

·   Avoid cost of a large bind

·   Avoid the entire transaction being unavailable during bind and automatic rebind of a plan

·   Minimize fallback complexities if changes result in an error.