DB2 Interview Questions Asked in Top MNCs

11/21/2009 No Comment

IBM DB2 Interview Questions with Answers. 

These questions can be expected in a Mainframe DB2 Interview.

What do you mean by QUIESCE?
Answer : 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

What do you mean by a Cluster Index ?
Answer : Causes the data rows to be stored in the order specified in the index A mandatory index defined on a partitioned table space

How many clustering indexes can be defined for a table?

Answer : Only One

Differentiate Primary Key & Unique index ? 

Answer : Primary : 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 uniue values There can be one or more unique indexes on a table

What does SQLCODE -922 mean?

Answer : Authorization failure

What does SQL-811 mean?

Answer : SELECT statement has resulted in retrieval of more than one row

What does the SQLCODE -818 pertain to?

Answer : This is generated when the consistency tokens in the DBRM and the load module are different

Is View Update possible ? 

Answer : Not all of them Some views are updatable eg 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

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

Answer : NO

What are the four environments which can access DB2 ?
Answer : TSO, CICS, IMS and BATCH

Explain what is an inner JOIN and outer JOIN ?

Answer : Inner Join: combine information from two or more tables by comparing all values that meet the search criteria in hte designated column or columns of one table with all the class 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.

Explain what is FREEPAGE and PCTFREE in TABLESPACE creation in DB2?

Answer : PCTFREE: percentage of each page to be left free FREEPAGE: Number of pages to be loaded with data between each free page

Explain what are simple, segmented and partitioned table spaces in DB2?

Answer : Simple Table space Can contain one or more tables Rows from multiple tables can be interleaved on a page under the DBAs control and maintenance Segmented Table space Can contain one or more tables Table space 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 Table space Can contain one table Tables pace is divided into parts and each part is put in a separate VSAM Data Set

What do you mean by filter factor?
Answer : One divided by the number of distinct values of a column

What do you mean by index Cardinality?

Answer : The number of distinct values a column or columns contain

What do you mean by a SYNONYM?
Answer : 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

Differentiate between SYNONYM and ALIAS?

Answer : SYNONYM: is dropped when the table or table space is dropped Synonym is available only to the creator ALIAS: is retained even if table or table space 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 do you mean by NOT NULL WITH DEFAULT? When will you use it?
Answer : This column cannot have nulls and while insertion, if no value is supplied then it wil 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 NOT NULL?When will you use it?
Answer : The column cannot have nulls Use it for key fields

Under what situations you would prefer to use VARCHAR?
Answer : When a column which contains long text, eg remarks, notes, may have in most cases less than 50% of the maximum length

Explain hat are the main disadvantages of using VARCHAR?

Answer : 
1. Can lead to high space utilization if most of the values are close to MAXIMUM. 
2. Positioning of VARCHAR column has to be done carefully as it has performance implications. 
3. Relocation of rows to different pages can lead to more I/Os on retrieval.

How do I create a table MANAGER ( EMP#, MANAGER) where MANAGER is a foreign key which references to EMP# in the same table?
Answer : 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. BIND or run time?

Answer : At run TIME

What do you mean by auditing?
Answer : Recording SQL statements that access a table Specified at table creation time or through alter

What do you mean by a DB2 bind?Answer : A DB2 bind is a process that builds an access path to DB2 tables

What do you mean by a DB2 access path?
Answer : An access path is the method used to access data specified in DB2 SQL Statements.

What do you mean by a DB2 plan?

Answer : An application plan or package is generated by the bind to define an access path.

Explain what is normalization and what are the five normal forms?

Answer : Normalization is a design procedure for representing data in tabular format The five normal forms are progressive rules to represent the data with minimal redundancy.
Related Posts

No comments :


Aired | The content is copyrighted and may not be reproduced on other websites. | Copyright © 2009-2016 | All Rights Reserved 2016

Contact Us | About Us | Privacy Policy and Disclaimer