DB2 Interview Questions for Freshers Part – 3

Top Most DB2 Interview Questions Part – 3

1.What is ACQUIRE/RELEASE in BIND?

Determine the point at which DB2 acquires or releases locks against table and tablespaces,including intent locks.

2.What else is there in the PLAN apart from the access path?

PLAN has the executable code for the SQL statements in the host program.

3.What happens to the PLAN if index used by it is dropped?

Plan is marked as invalid. The next time the plan is accessed,it is rebound.

4.What are PACKAGES?

They contain executable code for SQL statements for one DBRM.

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

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

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

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

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

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

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

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

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

14.What is index cardinality?

The number of distinct values a column or columns contain.

15.How do you retrieve the data from a nullable column?

Use null indicators. Syntax … INTO :HOSTVAR:NULLIND

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

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

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

19.When will you chose to run RUNSTATS?

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

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

21.What is IMAGECOPY?

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

22.When do you use the IMAGECOPY?

To take routine backup of tables After a LOAD with LOG NO After REORG with LOG NO

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

24.WWhat 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.