DB2 Frequently Asked Questions and Answers:Part 3

11/27/2009 No Comment

Mainframe DB2 Frequently Asked Interview Questions and Answers.

Describe EXPLAIN in DB2
Answer : EXPLAIN is used to detail the access paths chosen by DB2 optimizer for SQL statement. This command is used to monitor the performance of SQL statement used in a program.

What do you mean by a BIND in DB2?
Answer : BIND mainly performs two things syntax checking and authorization checking.It binds together all packages into an application plan hence the name BIND.Apart from this bind has optimiser as a subcomponent.Its function is to determine the optimum access strategy.

What is the maximum no of rows per page in DB2?
Answer : The maximum is 127

What is the only place of VSAM KSDS in DB2?

Answer : BSDS is a VSAM KSDS.

Does all users have the privilege to use the  SQL statement SELECT * (DML)?

Answer : No the user should be given the GRANTED privilege to use it.

Can you tell what is the size of a data page in DB2?

Answer : The size of the data page is 4K to 8K.

What is the best locksize that you use when you create a tablespace in DB2?

Answer : The answer is Locksize = ANY.Unless you are completely sure what is the purpose of tablespace ie.,Read-only or R/W.If you use lock size =any, Db2 would automatically determine what type of locks it should use.

Name the error code for Unique Index Violation in DB2?

Answer : The error code is -803.

If the table size less than 10 PAGES, can you define an Index ?

Answer : No you cannot define an index.

What is the Maximum Length of SQLCA and what is the content of SQLCABC in DB2?
Answer : The Max length is 136. and the SQLCABC has the Value of SQLCA.

Can you tell what is the maximum number of characters that a tablename can have? 

Answer :  18 characters.

What is the max number of volumes that can be added to a STOGROUP in DB2?
Answer : The answer is 133. Usually it will be difficult monitor more than 3 or 4 volumes to a Stogroup.

Explain -805 sql return code?

Answer : Program name not in plan. Bind the plan and include the DBRM for the program named as part of the plan.

Tell me the usage of DSNDB07 ?

Answer : This is the area where sorting takes place in DB2

When you are using cursor in the application programming, when does the SQL statement gets executed ?

Answer : SQL statement gets executed when we open cursor

What  is the use of CURRENTDATA in bind?

Answer : CURRENTDATA option ensures block fetch while selecting rows from a table. In DB2V4 the default has been changed to NO. Therefore it is necessary to change all the bind cards with CURRENTDATA(YES) which is default in DB2V3 & earlier to CURRENTDATA(NO).

Differentiate between TYPE 1 index & TYPE 2 index.

Answer : TYPE 1 & TYPE 2 are specified when an index is created on the table. TYPE 2 index is the option which comes with DB2V4. With TYPE 2 index data can be retreived faster as only the data pages are locked and not the index pages. As such, TYPE 2 index is recommended.

Explain what are the levels of isolation available with DB2V4.

Answer : CS RR UR( added new for DB2V4 which stands for un-commited read which allows to retrieve records from the space which has exclusive locks also but data integrity will be affected if this option is used ) The best available option for data integrity & data concurrency is CS.
Explain in brief how do you achieve record level locking in DB2 versions when record level locking is not allowed?
Answer : By having the length of the record greater than that of a page.

Can you tell how to define the data items to receive the fetch items for the SQL?

Answer : Using the DSECT, followed by lines of - 'dataitems DS datatype'.

Explain how will you delete duplicate records from a table in DB2?

Answer : Delete From Table1Where Id In (Select Id From Tabel1 As Temp Group By Id Having Count(*) >1)

Explian the difference between Where and Having Clause

Answer : The clause WHERE is for Rows and HAVING is for Groups.

How can you view the structure of DB2 table?

Answer : By using QMF.

Can you tell what is the max number of tables that can be stored on a Partitioned Table Space ?

Answer : Only one

List the different types of Table spaces in DB2

Answer : The different types of tablespaces in DB2 are cited below.
1. Simple Table Space
2. Segmented Table Space and
3. Partitioned Table Space.

CICS Interview QuestionsJCL Interview Questions
DB2 Interview QuestionsCOBOL Interview Questions
VSAM Interview QuestionsIDMS Interview Questions
IMS Interview QuestionsMVS Interview Questions
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