Mainframe DB2 Frequently Asked Interview Questions and Answers.
Describe EXPLAIN in DB2Answer : 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 Questions | JCL Interview Questions |
DB2 Interview Questions | COBOL Interview Questions |
VSAM Interview Questions | IDMS Interview Questions |
IMS Interview Questions | MVS Interview Questions |
Related Posts
No comments :