IBM DB2 Interview Questions, Answers And Explanations.
What do you mean by leaf pages?Answer : They are the opposite of root pages Leaf pages are the lowest level index pages - the pages that contain index entries and information to the corresponding table rows.
What do you mean by a pre-compiler?
Answer : It is a DB2 facility for static SQL statements - it replaces these statements with calls to the DB2 language interface module.
Explain what is a root page?
Answer : The opposite of a leaf page; it is the highest level index page An index can contain only the one root page; all other index pages are associated to the root.
What do you mean by a thread?
Answer : A thread is the connection between DB2 and some other subsystem, such as CICS or IMS/DC.
What do you mean by a Bind ? What are the different bind parameters ?
Answer : Bind is a process where the DBRM’S are converted into a Plan which is an executable module containing the access path logic produced by the DB2 optimizer. It also does syntax checking of the SQL code in the DBRM’S and authorization checking.
PLAN NAME – Name of the plan
ACTION ON PLAN – ADD or REPLACE. – This parameter controls whether the existing plan will be replaced by the newly built plan (with this bind) or a new plan will be created.
RETAIN – To retain or not the old authorities. The retain specification makes DB2 decide whether the authorizations of the user who did the previous BIND of the plan will be retained for the new plan.
ISOLATION LEVEL – CS and RR – This parameter helps DB2 in deciding the duration of PAGE LOCKS required by the application program.
VALIDATION – RUN or BIND. To tell whether authorization will be checked at run or bind time. ACQUIRE – USE or ALLOCATE. – This parameter controls the table space to be maintained over the resource used by the program. It decides the duration for which these locks should be held.
RELEASE – COMMIT or DEALLOCATE- This parameter specifies to DB2 when the table space locks ACQUIRED earlier by the program should be RELESED EXPLAIN – YES or NO OWNER – AUTH ID – primary authorization. PREPARE – YES or NO.
What do you mean by Rebind and Bind (replace)?
Answer : Rebind has to be done if the plan o r package is invalid.
What do you mean by an index ? Explain its usage ?
Answer : An index is an ordered set of pointers to the data in the DB2 table, stored separately from the table. This is used to improve performance and ensure uniqueness (only for unique index) .
What do you mean by a clustering index ? Explain what is REORG. When will we do REORG ?Answer : Clustering index causes the data rows to be stored in the order specified in the index. A mandatory index defined on a partitioned table space. Only one clustering indexes can be defined for a table. REORG reorganizes data on physical storage to re-cluster rows, positioning overflowed rows in their proper sequence, to reclaim space, to restore free space. It is used after heavy updates, inserts and deletes activity and after segments of a segmented tables space have became fragmented.
What do you mean by a filter factor? For a table space scan , the FF Should be high or low? Filter Factor is one divided by the number of distinct values of a column?
Answer : Filter Factors eliminate non-qualifying rows from the estimate of the access cost for the various access methods. Usually, only a subset of rows will qualify to be returned for each particular predicate on the WHERE clause. The lower the filter factor for a given predicate, the more likely DB2 will choose that predicate's access path for the plan, assuming it participates in an index. Every predicate has a filter factor, and these values are used to calculate QCARD and DMCARD for the SQL statement; that is, the number of rows returned to the user (query cardinality) and the number of Stage 1 calls (Data Manager cardinality). The ultimate purpose of applying the filters is to estimate the number of rows Returned.
What do you mean by RUNSTATS? When will you do this? After RUNSTATS what will you do?
Answer : A DB2 utility used to collect statistics about the data values in tables which can be used by the optimizer to decide the access path. It also collects statistics used for space management. These statistics are stored in DB2 catalog tables. After a load, or after mass updates, inserts, deletes, or after REORG we will chose to run RUNSTATS. Some example of statistics collected during RUNSTARTS # of rows in the table Percent of rows in clustering sequence # of distinct values of indexed column # of rows moved to a nearby/faraway page due to row length increase.
Describe 'explain' command?
Answer : EXPLAIN is used to display the access path as determined by the optimizer for a SQL stmt . It can be used in SPUFI(for single SQL statement) or in BIND step(for embedded SQL).
What is an indicator variable in DB2?
Answer : An Indicator variable is a small integer used to indicate whether its associated host variable is null.
How can you declare an indicator variable?
Answer : An Indicator variable is preceded by a colon(:) and coded immediately after the host variable. The variable has to be declared in the working storage section. Example: :DEPT:DEPT-IND Where DEPT-IND is an Indicator variable.
What do you mean by a cursor?
Answer : Cursor is programming device that allows the SELECT to find a set of rows but return them one at a time. Cursor should be used because the host language can deal with only one row at a time. A cursor is a named control structure used to make multiple number of rows available to a program and then to process the selected table data one row at a time (similar to reading a sequential file)
Where do you declare the cursor in program?
Answer : Working storage.
What is 'with hold' option in cursor?
Answer : At the end of a Commit or Rollback the cursor will be closed and has to be reopened again. If we want the cursor to be opened even after the Commit or Rollback, ‘with hold’ option has to specified on the Declare cursor statement. Example: DECLARE DEPTCUR CURSOR WITH HOLD FOR SELECT EMPNO, ENAME, SALARY FROM EMPTABLE FOR UPDATE OF SALARY Cannot be used in programs that are not pseudo conversational.
Can we update/delete via a cursor? What are the restrictions on this?
Answer : Update / delete is possible via cursor. Only the current row is updated/deleted.
How can you find the no of rows of a column?
Answer : Use SELECT COUNT(*)
When, views are not up-datable ?
Answer : Non-up-datable views are views which are joins, views that contain aggregate functions (such as MIN), and views that have GROUP BY clause. Some views are up-datable ex: single table view with all the fields or mandatory fields.
What do you mean by a correlated sub query?
Answer : A sub-query in which the (nested) inner query refers back to the table in the outer query. Correlated sub-queries must be evaluated for each qualified row of the outer query that is referred to.
What do you mean by 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.
What are the techniques are used to retrieve data from more than one table in a single SQL statement?
Answer : Joins, unions and nested selects are used to retrieve data.
Related Posts
No comments :