Question 1

Name and explain the type of locks?

Accepted Answer

There are three types of lock, namely: Shared – This type of lock permits two or more programs to read from the locked space but does not allow them to change it Update – This lock is more lenient It permits the program to read and change the locked space Exclusive – This lock restricts all users from accessing the locked space

Question 2

What is NOTCAT2?

Accepted Answer

It is an MVS message that indicates the presence of a duplicate catalogue in the database This problem can be fixed by removing one of the entries and then adding a new one

Question 3

How do you display last 5 records in DB2?

Accepted Answer

SELECT * FROM table_name ORDER BY column_name desc Fetch first 5 rows only;

Question 4

Difference between Embeded SQL and Dynamic SQL?

Accepted Answer

Static (embedded) SQL: 1In static SQL how database will be accessed is predetermined in the embedded SQL statement 2It is more swift and efficient 3SQL statements are compiled at compile time 4Parsing, validation, optimization, and generation of application plan are done at compile time 5It is generally used for situations where data is distributed uniformly 6EXECUTE IMMEDIATE, EXECUTE and PREPARE statements are not used 7 It is less flexible Dynamic (interactive) SQL: 1In dynamic SQL, how database will be accessed is determined at run time 2It is less swift and efficient 3SQL statements are compiled at run time 4Parsing, validation, optimization, and generation of application plan are done at run time 5It is generally used for situations where data is distributed non-uniformly 6EXECUTE IMMEDIATE, EXECUTE and PREPARE statements are used 7It is more flexible

Question 5

What is DBRM? What it contains? When it will be created?

Accepted Answer

- DBRM stands for Database Request Module - The output of pre-compile process is represented as DBRM - The SQL statements are extracted from the host language by the pre-compiler

Question 6

How does DB2 use multiple table indexes?

Accepted Answer

DB2 use the multiple indexes to satisfy multiple predicates in a SELECT statement that are joined by an AND or OR

Question 7

What is SPUFI?

Accepted Answer

SPUFI stands for SQL processing using file input It is the DB2 interactive menu-driven tool used by developers to create database objects

Question 8

What is Mainframe Testing?

Accepted Answer

Mainframe Testing is similar to client-server applications testing, but you have to know how to operate basic TSO and ISPF commands and menus, view mainframe files, look at and use SDSF or other output tool, log on CICS and transactions, use FTP or another transfer protocol, submit the batch job - it's for QA testing of mainframe applications

Question 9

What is SQLCA?

Accepted Answer

- SQLCA stands for SQL Communication Area - SQLCA is a structure of variables which are updated after every SQL statement’s execution - Exactly only one SQLCA need to be provided to an application that contains executable SQL statements - SQLCA is not applicable to JAVA application - More than one SQLCA need to be provided for FORTRAN application

Question 10

What is COPY PENDING status?

Accepted Answer

- A status occurs when image copy on a table needs to be taken - The table is available only for queries and can not be updated - COPY PENDING status can be removed by taking away the image copy or by using REPAIR utility

Question 11

What is the difference between Cursor Stability and Repeatable Read isolation levels?

Accepted Answer

CURSOR STABILITY: - CS is a row level locking - Acquires an exclusive lock on the row that is to be updated - When the control is moved to the next updatable row, the lock is released REPEATABLE READ: - RR is a page level locking - Acquires an exclusive lock on the entire page which is the source of row availability - When the control is moved to the next updatable page the lock is released

Question 12

What does it mean if the null indicator has -1, 0, -2?

Accepted Answer

-1 indicates the field is null 0 indicates the field is not null -2 indicates the field is truncated

Question 13

How can record locking be achieved in those DB2 versions which do not support it?

Accepted Answer

It can be done by setting the record length’s size to more than half of the page’s size

Question 14

What is the need to code COMMITS in batch programs?

Accepted Answer

COMMIT statements are used to release locks which are required for that unit of work, and then permit a new unit of work In other words, if COMMITS are not coded in the program and the program has been sent for execution, then while processing, in place of just going back to a few inserts since the nearest commit, the program has to go back to the inserts which were made during the entire run of the program This extra process takes around twice or thrice the time taken normally

Question 15

Why is free space left in KSDS Dataset?

Accepted Answer

While allocating KSDS Datasets, free space is declared at regular intervals at the time of initial loading This is done because this free space is utilized for keeping the data arranged in sequence physically, even though inserted randomly

Question 16

What is the difference between primary key & unique index ?

Accepted Answer

Primary key : a relational database constraint Primary key consists of one or more columns that uniquely identify a row in the table For a normalized relation, there is one designated primary key Unique index: a physical object that stores only unique values There can be one or more unique indexes on a table

Question 17

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

Accepted Answer

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

Question 18

What is the self-referencing constraint?

Accepted Answer

A31 The self-referencing constraint limits in a single table the changes to a primary key that the related foreign key defines The foreign key in a self referencing table must specify the DELETE CASCADE rule

Question 19

File contains 5 records say D,E,F,C,A-I want to copy D and A records only by using file-aid?

Accepted Answer

Go to file-aid copy optionEnter the from dataset to be copied and to dataset to which you want to copygo to options Enter 0 at Initial records to skip fieldThen enter 3 at the no of records to skip field and 1 to no of records to selectpress enter and PF3

Question 20

My SQL statement SELECT AVG(SALARY) FROM EMP yields inaccurate results. Why?

Accepted Answer

Because SALARY is not declared to have NULLs and the employees for whom the salary is not known are also counted

Technology Questions