DB2 Interview Questions - DB2 interview questions and answers for freshers and experienced. DB2 technical job interview questions of various companies and by job positions.
What is DB2?
DB2 is a Database Management System for the MVS Operating System where, DB2 is a subsystem of MVS Operating System.

What is the purpose of using COMMIT?
The data changes can be made permanent by using COMMIT. It also permits data to be accessed by other applications who can reference the committed data.

What is an access path?
The path that is used to get to data specified in SQL statements.

What are the uses of DB2 Optimizer?
  • It processes SQL statements.
  • It helps to select the access path
What is a DB2 bind?
Bind is a process that builds “access paths” to DB2 tables. A bind uses the Database Request Modules(s) (DBRM(s)) from the DB2 pre-compile step as input and produces an application plan. It also checks the user’s authority and validates the SQL statements in the DBRM(s).

What is a DB2 catalog?
The DB2 catalog is a set of tables that contain information about all of the DB2 objects(tables, views, plans etc.).
What information is used as input to the bind process?
The database request module produced during the pre-compile. The SYSIBM.SYSSTMT table of the DB2 catalog.

What is SQLCA?
SQL Communication Area is a structure of variables, which are updated after every execution of SQL statements. For an application that contains executable SQL statements, only one SQLCA is needed. FORTRAN need more than one SQLCA to be provided. For Java, SQLCA is not applicable.

What are some of the main fields in SQLCA?
Major Fields in SQLCA is SQL CODE, SQLERRM, and SQLERRD.

What is SQLCA’s maximum length?
136 is the maximum length of the SQLCA.

What is CHECK constraint?
It is specified as a condition or criteria to ensure data integrity. A value to be inserted or updated to a table is tested by CHECK constraint. The CHECK constraints are created during the creation of the table.

What is buffer pool?
Buffer pool is a reserved main storage, which is to satisfy the buffering requirements for one or more table spaces or indexes. It can be made up of either 4K or 32K pages.

What is the function of Data Manager?
The physical database is managed by the DB2 component called Data manager. It invokes other system components to perform logging, locking etc.

What is the function of buffer manager?
The buffer manager is the DB2 component responsible for physically transferring data between an external medium and (virtual) storage (performs the actual I/O operations). It minimizes the amount of physical I/O actually performed with sophisticated buffering techniques (i.e., read-ahead buffering and look-aside buffering).

What is a storage group (STOGROUP)?
STOGROUP is a named collection of DASD volumes, which is to be used by index spaces and table spaces of the database.

What is Declaration Generator (DCLGEN)?
Declaration Generator is a facility that is used to form SQL statements that describes a table or view. At pre-compile time, the table or view descriptions are then used to check the validity of SQL statements.

What are the contents of a DCLGEN?
EXEC SQL DECLARE TABLE statement which gives the layout of the table/view in terms of DB2 data types.
A host language copy book that gives the host variable definitions for the column names.

What are the buffer pools in DB2?
There are four buffer pools in DB2 and they are:
  • BP0
  • BP1
  • BP2
  • BP32
What is Clustering index?
Clustering index is a type of index, which locates the table rows and determines how to group the rows together in the tablespace.

What is concurrency?
More than one DB2 application process can access the same data at the same time, is known as concurrency. However, problems can happen such as, lost updates access to unrepeatable reads and uncommitted data.

What is DBRM?
Database Request Module and is a component inside DB2, which is created by the pre-compiler of DB2. This is a module that consists of SQL source statements that get extracted out of the application program. DBRMs form inputs that are helpful in the binding process.

What is Data page?
Data page can be considered as a unit that is capable of retrieving data from the database. The database from which the data can be retrieved is in the form of 4 kilobytes or 32 kilobytes. The form in which data is retrieved depends on the way the table is defined inside the database. Data page also contains information regarding the catalog or user that are part of the database.

What are data types?
They are attributes of columns, literals, and host variables. The data types are SMALLINT, INTEGER, FLOAT, DECIMAL, CHAR, VARCHAR, DATE and TIME.

What is RCT?
Resource Control Table and is defined in the DB2/CICS region. This is the component that comprises of features that are gathered through macros of DSNCRCT. RCT matches with the transaction ID that of CICS, with the authorization ID that of DB2. This should also be matched with plan ID.

What is the information associated with SYSIBM.SYSLINKS table?
This is the table that contains information on the links that exists between the tables created through referential constraints.

What is buffer manager and its functionalities?
Buffer manager can be considered as the component inside DB2 that helps in transferring data between virtual as well as external medium. The buffer manager reduces the quantity of physical input as well as output operations that are actually performed by making use of buffering techniques that are highly sophisticated.

What is cursor stability?
Cursor stability is the property that tells the DB2 that the values of database that are read by making use of this application gets protected while the data is used.

What are PACKAGES?
PACKAGES are units, which consist of executable codes that are meant for SQL statements for one respective DBRM.

What are the advantages of using PACKAGES?
PACKAGES help avoiding the binding of large numbers of DBRM members in one plan. It also dispenses with the cost of large BIND and avoids the entire transactions making them unavailable during BIND and automatic REBIND of the plan. Another advantage is that it minimizes the fall-back complexities when changes result in an error.
What is DCLGEN?
DCLGEN basically refers to DeCLarations GENerator whose primary purpose is to generate copy books of the host language for the tables. It is also used to create the DECLARE table.

What are the contents that are a part of DCLGEN?
The contents of DCLGEN are EXEC SQL DECLARE TABLE statement that gives the layout of the table/view in terms of the DB2 data types. It can also be defined as the host language copy book giving host variable definitions for the column names.

What is EXPLAIN?
EXPLAIN is basically used to show the path of access by the optimizer basically for an SQL statement. Furthermore, EXPLAIN can also be brought to use in SPUFI or even in BIND step.

Where the output of is EXPLAIN stored?

There is a certain point in a program at which DB2 acquires or perhaps releases the locks against tables as well as table-spaces. These include intent locks.

What is meant by aggregate function?
‘Aggregate’ functions are built in mathematical tools that are used in DB2 SELECT clause.

What is UNION and UNION ALL?
UNION is the function that eliminates duplicates in the table and UNION ALL retains the duplicates. Both of these are used to combine the results generated by multiple SELECT statements.

When the LIKE statement is used?
LIKE statement is used to conduct partial searches like the search of employees by name. It is not necessary to indicate the complete name, the partial string matches would do.

What is a CURSOR and what is its use?
CURSOR is programming device that helps the SELECT to find out set of rows but displays them one at a time. Since the host language can deal with only one row at a time.

What is the difference between the CS and RR isolation levels?
CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of the transaction.
Request to Download PDF


Vestibulum bibendum felis sit amet dolor auctor molestie. In dignissim eget nibh id dapibus. Fusce et suscipit orci. Aliquam sit amet urna lorem. Duis eu imperdiet nunc, non imperdiet libero.

Post A Comment: