Mainframe DB2 Frequently Asked Questions and Answers:Part 2

9/03/2009 No Comment

DB2 Frequently Asked Questions and Answers in companies.

What is the maximum number of rows retrieved per page?
Answer : 127

What is the only place of VSAM KSDS in DB2?
Answer : BSDS is a VSAM KSDS.

Can all users have the privilege to use the SQL statement SELECT * (DML)?
Answer : No. The users should be granted privilege to use it.

What is the size of a data page?
Answer : 4K to 8K

What's the best locksize that you could use when you create a tablespace?

Answer : The answer is LOCKSIZE = ANY. Unless you are sure what's the Purpose of tablespace
ie.,Read-only or Read/Write. If you use LOCKSIZE = ANY, DB2 would automatically determine what type of locks it should use.

Can you define an Index if the table size is less than 10 PAGES?
Answer : No

What's the maximum Length of SQLCA and what's the content of SQLCABC?

Answer : The maximum length is 136 and the SQLCABC has the Value of SQLCA.

What's the maximum number of volumes that can be added to a STOGROUP?

Answer : The answer is 133. Usually it will be difficult to monitor more than 3 or 4 volumes to a Stogroup.

What's the maximum number of characters that a tablename can have?

Answer : The answer is 18 characters.

What is the meaning of -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.

When does the SQL statement gets executed when you use cursor in the application programming?
Answer : SQL statement gets executed when we open cursor

What does CURRENTDATA option in bind indicate

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

What is the difference 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 retrieved faster as only the data pages are locked and not the index pages. Hence TYPE 2 index is recommended.

What are the levels of isolation available with DB2V4?
Answer : CS RR UR (added new for DB2V4 which stands for uncommitted 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 and data concurrency is CS.

How do you achieve record locking in DB2 in the versions which do not support record level locking?
Answer : By having the record length more than half of the page size

In a DB2-CICS program which acts as co-ordinator and which is the participant?

Answer : DB2 acts as the participant and CICS acts as a co-ordinator

What does DML stand for and what are some examples of it?

Answer : Data Manipulation Language. Some examples are SELECT, INSERT, DELETE, REPLACE.

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

What is the difference between WHERE and HAVING Clause?

Answer : WHERE is for Rows and HAVING is for Groups

How to see the structure of DB2 table?
Answer : Using QMF.

What is the maximum number of tables that can be stored on a Partitioned Table Space?

Answer : ONE

Name the different types of Table spaces.

Answer : 1. Simple Table Space; 2. Segmented Table Space and 3. Partitioned Table Space

What is the maximum and minimum number of partitions allowed in a partitioned tablespace?
Answer : Minimum is 4 and Maximum is 64.

What is the maximum number of tables that can be joined?

Answer : Fifteen

What technique is used to retrieve data from more than one table in a single SQL statement?

Answer : The JOIN statement combines data from more that two tables

What is a foreign key?

Answer : It identifies a related row in another table and establishes a logical relationship between rows in two tables.

Explain the use of the WHERE clause.

Answer : It directs DB2 to extract data from rows where the value of the column is the same as the current value of the host variable.
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