DB2

DB2 Interview Questions - part V

1. How would you find out total number of rows in a table?

Use Select Count(*)

2.How do you eliminate duplicate values in SELECT?

Use SELECT DISTINCT

DB2 Interview Question part IV

1)My SQL statement: SELECT AVG(SALARY) FROM EMP yields inaccurate results. What could be the problem?

Ans: Probably SALARY is not declared to have NULLs and the employees for whom the salary is not known are also counted.

2)How do you retrieve the first 5 characters of FIRSTNAME column of EMP table?

Ans: SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP;

3)How do you concatenate the FIRSTNAME and LASTNAME from EMP table to give a complete name?

Ans: SELECT FIRSTNAME || ‘ ‘ || LASTNAME FROM EMP;

4)What is the use of VALUE function?

Ans: 1. Avoid NEGATIVE SQLCODEs by handling nulls and zeroes in computations

2. Substitute a numeric value for any nulls used in computation

5)What is UNION,UNION ALL?

Ans: UNION => eliminates duplicates
UNION ALL => retains duplicates
Both these are used to combine the results of different SELECT statements.

6)Suppose I have five SQL SELECT statements connected by UNION/UNION ALL, how many times should I specify UNION to eliminate the duplicate rows?

Ans: Once.

7)What is the restriction on using UNION in embedded SQL?

Ans: It has to be in a CURSOR.

8)In the WHERE clause what is the difference BETWEEN and IN?

Ans: BETWEEN => supplies a range of values
IN => supplies a list of values.

9)Is BETWEEN inclusive of the range values specified?

Ans: Yes.

10)How is 'LIKE' used in WHERE clause? What are the wildcard characters?

Ans: LIKE is used for partial string matches.
‘%’ => for a string of any character;
‘_’ => for a single character

11)When do you use a LIKE statement?

Ans: To do partial search e.g. to search employee by name, you need not specify the complete name; using LIKE, you can search for partial string matches.

12)What is the meaning of underscore (‘_’) in the LIKE statement?

Ans: Match for any single character.

13)What do you accomplish by GROUP BY ... HAVING clause?

Ans: GROUP BY partitions the selected rows on the distinct values of the column on which you group by.
HAVING selects GROUPs which match the criteria specified

14)Consider the employee table with column PROJECT nullable. How can you get a list of employees who are not assigned to any project?

Ans: SELECT EMPNO
FROM EMP
WHERE PROJECT IS NULL;

15)What is the result of this query if no rows are selected?

Ans: SELECT SUM(SALARY)
FROM EMP
WHERE QUAL=‘MSC’;
NULL

DB2 Table spaces

The different types of Table spaces.

1. Simple Table Space
2. Segmented Table Space
3. Partitioned Table Space (some partitioned table spaces can also be EA-enabled table spaces)
4. LOB Table Space

How will compress data in a table space or partition?

To compress data in a table space or partition, specify COMPRESS YES on CREATE TABLESPACE or ALTER TABLESPACE, then run LOAD or REORG. When you compress data, bit strings that occur frequently are replaced by shorter strings. Information about the mapping of bit strings to their replacements is stored in a compression dictionary. Computer processing is required to compress data before it is stored and to decompress the data when it is retrieved from storage.

Can you compress data in LOB space?

No.

What is referential integrity?

Referential integrity refers to the consistency that must be maintained between primary and foreign keys.

It is the state in which all values of all foreign keys at a given DB2 are valid. Rules of referential integrity:

Every foreign key value must have a matching primary key value.

An insert into a primary key table can not violate referential integrity and no checking is required.

A deletion of foreign key value can not violate referential integrity and no checking is required.

Changes in primary key values are allowed only for those values that don't have a matching foreign key.

Insert and update rules: The insertion of any given foreign key value (non-null value) or an update to that value is allowed only if the matching value exists in the primary key.

Delete rules for primary key value:

If RESTRICT is specified on foreign key definition, deletion of primary key value is not allowed.

If CASCADE is specified, both primary key and the foreign key values will be deleted.

IF SET NULL is specified, the foreign key value will be set to null, provided nulls are allowed in the foreign key column.

When can an insert of a new primary key value threaten referential integrity?

Never. New primary key values are not a problem. However, the values of foreign key inserts must have corresponding primary key values in their related tables. And updates of primary key values may require changes in foreign key values to maintain referential integrity.

What is the self-referencing constraint?

A single table can include both a primary key and a related foreign key. The limitations to changes in the primary key that the foreign key defines are called self-referencing constraints. The foreign key in a self-referencing table must specify the DELETE CASCADE rule.

What happens if either the DELETE RESTRICT or DELETE SET NULL is specified on a self-referencing table during the execution of an ALTER TABLE statement?

DB2 issues error message defining an invalid constraint is attempted.

What is table check constraint?

A.Table check constraints designate the values that specific columns of a base table can contain, providing you a method of controlling the integrity of data entered into tables. You can create tables with table check constraints using the CREATE TABLE statement, or add the constraints with the ALTER TABLE statement. E.g.,
CREATE TABLE EMPSAL (ID INTEGER NOT NULL,
SALARY INTEGER CHECK (SALARY >= 15000).

What is Check integrity and check pending state?

If the check integrity is compromised, or cannot be guaranteed for a table, the table space or partition that contains the table is placed in a check pending state. Check integrity is the condition that exists when each row of a table conforms to the check constraints defined on that table.

What are delete-connected tables?

Tables related with a foreign key are called delete-connected because a deletion in the primary key table can affect or be affected by the contents of the foreign key table.

What is a cycle in DB2 mean?

A.A cycle is a situation in which tables are related to each other through both primary and foreign key relationships. I.e., A set of referential constraints in which each associated table is a descendent of itself.

DB2: Normalization and de-normalization

Normalization rules help to avoid redundancies and inconsistencies in the data. It protects against update and delete anomalies and provide smaller tables and fewer total bytes.

The rules of normalization are:

1st Normal Form:- No data item (repeating groups) should not be repeated within a given record.

2nd Normal Form: Each column that is not in the key provides a fact that depends on the entire key.

3rd Normal Form: Each non-key column provides a fact that it’s independent of other non-key columns and depends only on key columns. 4th Normal Form: No row contains two or more independent multi-valued facts about an entity. 5th Normal Form: Sub-relations that cannot be reconstructed.

De-normalization technique

The rules of normalization do not consider performance. De-normalization concentrates on performance, but not on redundancy. What you have to consider is the trade-off--whether duplication, in several tables, of often-requested columns is less expensive than the time it takes to perform joins. This duplication of columns in multiple tables is de-normalization, and increases redundancy.

Is there any advantage to de-normalizing DB2 tables?

Denormalizing DB2 tables reduces the need for processing intensive relational joins and reduces the number of foreign keys.

DB2: What is a distributed operation?

A DB2 application program can use SQL to access data at other database management systems (DBMSs) other than the DB2 at which the application's plan is bound. This DB2 is known as the local DB2. The local DB2 and the other DBMSs are called application servers. Any application server other than the local DB2 is considered a remote server, and access to its data is a distributed operation.

What is meant by local DB2?

The DB2 application server in which the application's plan is bound is known as local DB2.

How many connections are possible for an application program at a time?

At a time, only one connection is possible for an application program.

The local DB2 is connected to a remote a DBMS. The application program issues a SELECT statement. Then it tries to connect to another DBMS. What can you expect?

DB2 will not allow it. An application program can have only one connection at a time and cannot connect to new application server until it executes a commit or rollback operation.

What are the different types of methods by which a local DB2 connect to a remote DBMS?

DB2 provides two methods of accessing data at remote application server

1. DRDA: This protocol allows the application program to connect to DB2 as well as other types of DBMS.

2. DB2 private protocol access: It allows DB2 connect to another DB2 running on a different application server.

What does Remote unit of work mean?

A unit of work is a transaction made up of a related set of SQL statements. Under remote UOW, DB2 on one computer can send multiple, related set of SQL statements to another DBMS on a remote computer. The remote site performs the processing for the statements, but the sending site controls whether to commit or rollback the UOW.

What does an SQL connection mean?

An SQL connection is an association between an application process and a local or remote application server. SQL connections can be managed by the application or by using bind options at any time.

Syndicate content