Latest Mainframe Interview Questions

1. What is DRDA ?

DRDA stands for Distributed Relational Database Architecture. DRDA is a connection protocol for relational database processing which IBM and vendor databases use. It consists of rules for communication between a remote relational DBMS and the application.

2. Explain the use of the WHERE?

The “WHERE” clause isolates an element or row whenever it is used with a relational statement.

3. How can a LIKE table be created?

It can be made by the use of the LIKE parameter in the statement CREATE. These tables are generally required for testing the production environment.

4. What is the need of running runstats in the test environment?

It is for clearing the default values from the catalog columns. Because if the columns have default values; then the result might not be an expected one.

5. What is the need of a secondary index in IMS?

It is a substitute path into any IMS database. It can be used as a file to acquire required data.

Mainframe
Mainframe

6. Explain foreign keys?

Foreign keys are properties of a particular table having matching entries to a primary key in some another table. This results in the formation of a relation between the two tables.

7. Write about the self-referencing constraint?

It limits the changes that can be made to the primary key from a foreign key. For this purpose, the foreign key has to define a DELETE CASCADE rule.

8. What does a deadlock mean in DB2?

When two independent processes contend for the same resource or the resources reserved by one another, it is called a deadlock. -911 and -913 are the SQLcode for a deadlock.

9. What is the function of DBCTL?

It is used to access IMS files for CICS Transactions and it is an address space. PSBs, DBDs, IMS files and ACBs are fixed into DBCTL for file access. Now if an IMS call is generated by a CICS program the processing gets transferred to DBCTL to be carried, and the result is sent as reply.

10. How are TYPE 1 and TYPE 2 indexes different?

TYPE 2 index is available for DB2V4. In this type, the data pages get locked, but the index pages are not locked. Therefore, TYPE 2 index are faster.

11. What are the different types of Table spaces?

There are three table spaces, namely:

Simple
Segmented
Partitioned

12. Write the difference between HIDAM and HDAM databases?

HIDAM has a separate index file which is based on the root segment. This file has information regarding the pointer that marks the position of the data. On the other hand, HDAM file does not have separate index file, but the key field of every record is passed through a randomizer which places the record to a particular position in the database.

13. Explain referential integrity?

It is the rule that states that consistency needs to be conserved between the foreign and primary keys. This means that each foreign key entry needs to have a fitting primary key entry.

14. How are composite and multiple indexes different?

Multiple index are two indexes, one each for a column of the same table. Composite index is only one index, which consists of combined values of 2 columns of a table.

15. What is the advantage in De-normalizing tables in DB2?

It lowers the requirement for executing intensive relational joins. It also cuts the number of required foreign keys.

16. Explain Database Descriptor in brief?

DBD, short for Database Descriptor, restricts access to the database when objects get altered, created or dropped.

17. State the maximum value up to which volumes can be inserted to an STOGROUP?

Volumes can be inserted up to 133. Practically though, only 3-4 volumes, when added to an STOGROUP, becomes hard to manage and monitor.

18. Explain delete-connected tables?

Tables, which are related through a foreign key, are known as delete-connected tables. This is because whenever any entry in the primary key is deleted, it affects the values in the foreign key table too.

19. Explain lock contention?

The DBD only allows entry to one object at a particular time. Lock contention is said to happen when more than one object seeks permission for execution simultaneously.

20. What is SPUFI?

SPUFI is the abbreviated form of SQL Processing Using File Input. It is a menu-driven tool which the developers use to make objects in the database.

21. What is an alias?

It is a substitute to a synonym. Aliases are developed for distributed environments so as to avoid the use of location qualifier of a view or table. It is not dropped when the table gets deleted.

22. What is the place for VSAM KSDS?

It is the BSDS.

23. Name and explain the type of locks?

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.

24. Explain isolation level?

Isolation level is the degree to which the activation group in focus is isolated from the activation groups executing at the same time.

25. What is NOTCAT2?

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.

26. What will happen if both STEPLIB and JOBLIB are specified?

JOBLIB is a Data Definition statement. It determines the location of the program, which called the EXEC statement. JOBLIB applies to all steps present in that whole job. It is not possible to use it for catalogued procedures. STEPLIB is very similar JOBLIB.

It determines the dataset in which the program exists. It applies only to one step and not the entire job. STEPLIB can be used at any position in the job step and can also be used for catalogued procedures.

In a situation when both STEPLIB and JOBLIB are specified, JOBLIB will be ignored by the system.

27. State the difference between JES2 and JES3?

In JES3, the allocation of data sets for the steps takes place even before the job is scheduled for execution. While in JES2, this allocation takes place just before the execution of each step.

28. State all the File OPEN modes?

The four File Open methods are:

INPUT
OUTPUT
EXTEND
I-O

29. Is the order of the WHEN clause significant in an EVALUATE statement?

Yes, the order is significant as the evaluation takes place in the top to bottom manner and, therefore, the order has a large impact in the determination of results.

30. When are scope terminators compulsory?

They are compulsory for EVALUATE statements and in-line PERFORMS. Scope terminators are always declared explicitly to improve readability.

31. What is linkage section?

It is a part of a program that is being called and maps to data items in the program’s (calling) working storage. Linkage Sections is where shared items are declared and defined.

32. How is NEXT SENTENCE and CONTINUE different?

The NEXT SENTENCE hands the flow of control to the statement that follows the next period. On the other hand, CONTINUE hands the flow of control to the next statement after the scope terminator.

33. What is the need to code COMMITS in batch programs?

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.

34. How is data retrieved from multiple tables using a single SQL statement?

Data can be retrieved from multiple tables by using joins, nested selects and unions.

35. Write about a ‘view’ in brief?

It is a virtual table that is made using data from base tables and other views. This table is not stored separately.

36. What parameters are used to control the free space in DB2?

Free space permits the addition of new rows.

There are two parameters, which are used to control the free space in DB2, they are:

PCTFREE – It defines the free space percentage that will be present in each page.

FREEPAGE – It specifies the number of pages to be loaded before leaving a page empty.

37. What is the function of CURRENTDATA option in bind?

It allows block fetch when selecting rows in a table. It is set to NO by default from DB2V4. So it is important to change CURRENTDATA (YES), in the bind cards, to NO in DB2V3 & earlier, as YES is the default value for them.

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

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

39. State the maximum number of tables that can be joined?

The maximum numbers of tables that can be joined are fifteen.

40. Explain index cardinality?

Index cardinality is the number of discrete entries in a column. In DB2, a utility RUNSTATS examines the column value redundancy to find if an index scan or tablespace is required for searching of data.

41. Define clustered index?

A clustered index is the one in which columns are maintained in the same sequence as the rows until there is still free space in the memory. DB2 then processes the table in the given order expeditiously.

42. Name the lockable units in DB2?

DB2 implements locking on four types of units:

Pages
Indexes sub page
Tables
Table space

43. What is the function of the verb INITIALIZE?

This verb sets Alphanumeric, alphabetic and alphanumeric field items to ““(Space) and Numeric and Numeric edited field items to “0” (Zero). The INITIALIZE verb leaves FILLER items unchanged.

44. What is the importance of COMMON Attribute?

Its functions are utilized in nested COBOL programs. If COMMON attribute will not be specified, the nested programs will find it impossible to access the program. An example of COMMON program is Pgmname.

45. What will be the result if ON SIZE ERROR phrase is declared?

When the condition is true, the code written in the “ON SIZE ERROR” phrase is performed. During this process, the content of the destination field does not change.

If this phrase is not mentioned, then the statement is executed with truncation. MOVE statement does not support the ON SIZE ERROR phrase.

46. Can records be accessed randomly in ESDS?

Yes, records can be accessed randomly. It can be done by reference of Relative Byte Address (RBA). However, it is not possible to delete records.

47. Why is free space left in KSDS Dataset?

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.

48. Write in brief about clusters?

It is a combination consisting of the sequence set, index and data portions in the Data set. The cluster has the program access given to it by the Operating System, and this allows access to be granted to all parts of the Dataset at the same time.

49. Explain path?

It is a file that makes accessing a file by alternate index possible. The path defines a relation between the base cluster and the AIX.

50. What is the function of IEBGENER?

IEBGENER helps in copying one SAM File to some other Source. It is also used to perform simple data reformatting through the supply of control cards via SYSIN.