DB2 Interview Questions and Answers:Part 3

11/15/2009 No Comment

Collection of DB2 Interview Questions and Answers.

All visitors are invited to submit their comments on DB2 Interview Questions.

Name the different data types in DB2?
Answer : Smallint, Integer, Decimal, Float, Character, Varcharacter, Graphic,Vargraphic, Date, Time, Timestamp

What do you mean by a view ? What are the advantages and restrictions of using a view ?

Answer : A view is a virtual table derived from one or more base tables. It prevents unauthorized users from having access to sensitive data. Cannot insert, delete or update a view, which is based on more than one base table.

What do you mean by referential Integrity?

Answer : Referential integrity is a condition wherein all references from one table to another are valid. It is the enforcement of all referential constraints(a rule that the value of a foreign key must appear as the value of a primary key of some other specific table.)

What do you mean by a Subquery ?
Answer : A subquery is a query that is written as part of another query's WHERE clause.

What do you mean by a corrleated subquery ?

Answer : A correlated subquery is one that has a correlation name as a table or view designator in the FROM clause of the outer query and the same correlation name as qualifier of a search condition in the WHERE clause of the subquery.

How does the processing of correlated subquery differ from a noncorrelated ?

Answer : The subquery in a correlated subquery is reevaluated for every row of the table or view named on the outer query, while the subquery Of a noncorrelated subquery is evaluated only once.

What do you mean by DB2 bind ?

Answer : A bind is a process that builds 'access paths' to DB2 tables. A bind uses the Database Request module(s) from the DB2 precompile step as input and produces an application plan. It also checks the user's authority and validates the SQL Statements in the DBRM.

What do you mean by a plan?

Answer :  A plan in db2 produced during the bind process. One or more database request modules with a plan name.

What do you mean by a synonym? How is it used?

Answer : A synonym is used to reference a table or view by another name. The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated.The synonym is linked to the AUTHID that created it.

What is an alias and how does it differ from a synonym?

Answer : An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view. The alias is not dropped when the table is dropped.

Describe in brief the primary key and the foreign key concept?

Answer : The primary key is a column or a set of columns that provide a unique identifier to each row of a table. The foreign key is a column or a set of columns that refers to the primary key of another table.

Explain in brief what is DDL and DML?

Answer : black"> - DDL - Data Definition Language (CREATE, ALTER, TRUNCATE)
DML - Data Manipulation Language (SELECT, INSERT, DELETE & UPDATE)

What do you mean by DCLGEN?

Answer : DCLGEN stands for declarations generator; it is a facility to generate DB2 sql data structures in COBOL or PL/I programs.

What is the importance of the CURSOR WITH HOLD clause in a cursor declaration?

Answer : The clause avoids closing the cursor and repositioning it to the last row processed when the cursor is reopened.

Explain what are the three lock types?

Answer : The three types are shared, update and exclusive. Shared locks allow two or more programs to read simultaneously but not change the locked space.An exclusive lock bars all other users from accessing the space.An update lock is less restrictive; it allows other transactions to read or acquire shared locks on the space.

What do you mean by deadlock ?

Answer : Deadlock occurs when transactions executing at the same time lock each other out of data that they need to complete their logical units of work.

Explain what is the difference between group by and order by ?

Answer : Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.

What do you mean by a clustered index ?

Answer : For a clustered index DB2 maintains rows in the same sequence as the columns in the index for as long as there is free space. DB2 can then process that table in that order efficiently.

Explain how can you add columns to an existing table ?

Answer : ALTER table command can be used to add columns to an existing table.
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