DB2 Frequently Asked Questions and Answers:Part 1

11/16/2009 No Comment

Mainframe DB2 Frequently Asked Interview Questions and Answers.

What is JOIN and what are the different types of JOIN.
Answer :
The ability to join rows and combine data from two or more tables is one of the most powerful features of relational system. There are three type of joins:1. Equi-join; 2.Non-equijoin; 3.self-join

Which is the most widely used batch performance monitor for DB2?
Answer :

Can I alter a table (like adding a column) when other user is selecting some columns or updating some columns from the same table?
Answer : Yes. It is possible until the updating or selection is committed, DB2 table will not be restructured. New column definition will be there but it will not be included until all the tasks on the table are committed.

How many subqueries can you combine together ?
Answer :
Total 16 queries and subqueries are 15

What are the different methods of accessing DB2 from TSO? How is the connection established between TSO & DB2? Answer :
There are three ways in establishing TSO/DB2 connection 1. SPUFI 2. QMF 3. CATALOG VISIBILITY; A thread between TSO & DB2 is established while attempting to make connection between TSO & DB2.

How many buffer bools are available in DB2?
Answer :
Ten 32K size bufferpools and fifty 4K size buffer pools (BP0 to BP49). Default buffer pools are BP0, BP1, BP2 & BP32

What is B37 abend during SPUFI?
Answer :
The B37 ABEND in the SPUFI is because of space requirements. The query has resulted in so many rows that the SPUFI.OUT file is not large enough to handle it; One possible solution is to increase the space allocation of SPUFI.OUT file.

What is the command used by TSO users to invoke DB2?
Answer :

What is the error code -803 ?
: Unique Index violation

How do you filter out the rows retrieved from a DB2 table?
Answer :
One way is to use the SQL WHERE clause.

What is a collection?
Answer :
A collection is something that every programmer should assign/specify for every package. This is about 1-18 characters long.

What is Skeleton Cursor Table (SKCT)?
Answer :
The Executable form of a Plan. This is stored in SYSIBM.SCT02 table.

What is the equivalent Cobol Data type for Decimal (x,y) in DB2? What does the current SQLID register contain?
Answer :
PIC S9(X-Y)V9(Y) COMP-3; The current SQLID contains the current authorization ID.

Can we declare DB2 HOST variable in COBOL COPY book?
Answer :
NO. If we declare DB2 host variable in COBOL COPY book, at the time of Pre-compilation we get the host variable not defined, because pre-compiler will not expand COBOL COPY book. So we declare it either in DCLGEN with EXEC SQL INCLUDE DCLGEN NAME END-EXEC or we directly hardcode it in the working storage section.

What should be specified along with a cursor in order to continue updating process after COMMIT?
Answer :
With Hold option.

What is the name of the default DB2 catalog database?
Answer :

When can you be sure that a query will return only one row?
Answer :
When you use the primary key and only the primary key in the WHERE clause.

What is the difference between JOIN and UNION?
Answer :
JOIN is used to retrieve data from different tables using a single SQL statement. UNION is used to combine the results of two or more SQL queries.

What is a correlated subquerry?
Answer :
In a subquerry, if the outer query refers back to the outcome of inner query it is called correlated subquerry. That's why the outer query is evaluated first unlike an ordinary subquerry

What are the functions of Bind?
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 optimizer as a subcomponent. Its function is to determine the optimum access strategy.
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