25+ DB2 Interview Questions and Answers [BEST & NEW]-2020
DB2 Interview Questions and Answers

25+ DB2 Interview Questions and Answers [BEST & NEW]

Last updated on 03rd Jul 2020, Blog, Interview Questions

About author

Wilson (Sr Technical Project Manager )

Highly Expertise in Respective Industry Domain with 10+ Years of Experience Also, He is a Technical Blog Writer for Past 4 Years to Renders A Kind Of Informative Knowledge for JOB Seeker

(5.0) | 17547 Ratings 1576

DB2 is basically one database designed by IBM. It is mainly managing relational database management system (RDBMS) which helps to store data in proper relational approach which help for analyzing and retrieving data very efficiently. DB2 admin expert’s people are really very much highly demanded in the current market scenario, the base salary for the same also very attractive.

Now, if you are looking for a job which is related to DB2 then you need to prepare for the 2020 DB2 Interview Questions. It is true that every interview is different as per the different job profiles. Here, we have prepared the important DB2 Interview Questions and Answers which will help you get success in your interview.

1) Define DB2?

Ans: 

DB2 is a Database Management System for the MVS Operating System where, DB2 is a subsystem of MVS Operating System.

2) What is the purpose of using COMMIT?

Ans: 

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

3) List out the Data types available?

Ans: 

The Data types available here are:

  • SMALLINT 
  • INTEGER 
  • FLOAT 
  • DECIMAL
  • CHAR 
  • VARCHAR 
  • DATE 
  • TIME

4) What are the uses of DB2 Optimizer?

Ans: 

  • It processes SQL statements.
  • It helps to select the access path

5) Define SQLCA.

Ans: 

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.

sql-ca

6) How to execute stored procedures?

Ans: 

The following is the process to execute the stored procedures:-

  • From the command line typedb2 connect to Sample;
  • db2 -td@ -vf createSQLproc.db2- Type the entire script. After completion of entering the script save the script- Execute the script by invoking the CALL command from
  • Command editor:CALL Sample;

7) What is SQLCA?

Ans: 

  • 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.

8) What is the max length of SQLCA?

Ans: 

The maximum length of SQLCA is 136.

9) What does SQLCABC has?

Ans: 

  • SQLCABC is one of the fields available in SQLCA.
  • It is an INTEGER type.
  • It contains the length of the SQLCA.

10) Is it possible to alter a table – for example adding a column, when another user is accessing or updating some columns?

Ans: 

  • It is possible to alter a table.
  • The DB2 tables will not re-structure until any transaction is committed.
  • A new column is defined and identified by the database.
  • The new column will be included only after the transactions of the table are committed.
  • All the altering table statements will be received from other users and are stored.
  • The commit status is updated by the database engine and then the new columns are added.

11)  Provide given some clear definition or explanation regarding picture clause in DB2. And in the case of null indicator variable how it can be utilized properly?

Ans: 

Picture clause is one the key feature which needs to be defined for identifying specific characteristics and as well as requirements of editing of a, particularly elementary items. This picture clause can be different for varieties activity in DB2 or mainframe DB2, we can able to use S9(4) COMP as picture clause for null indicator variable.

12)  Every RDBMS database has one common disadvantage of creating the deadlock. Now DB2 also followed the same RDBMS structure, so deadlocks also been generated in DB2. Please explain how or which specific components need to be check for identifying DB2 deadlocks?

Ans: 

DB2 deadlocks is one of the critical problems for any kind of DB2 user, currently, DB2 is running for two kinds of approaches one is for IBM mainframe where DB2 is the only option to use, but the facility is deadlocked condition or any other critical problem of RDBMS database normally not introducing for using the same in Mainframe object. But this kind of problematic scenario can easily be generated when DB2 has been using for any kind of web application or some else rather than Mainframe. Deadlocks conditions normally generated in case of some concurrency issues which are critical for web application users. Normally IRLM (Internal Resource Lock manager) which is known as locking service component of DB2 has been provided all the locking-related services who manages every concurrent issue in DB2 database.

13) What do the initials DDL and DML stand for and what is their meaning?

Ans: 

  • DDL is data definition language and DML is data manipulation language.
  • DDL statements are CREATE, ALTER, TRUNCATE.
  • DML statements are SELECT, INSERT, DELETE and UPDATE.

14)  What is a sub-select? Is it different from a nested select?

Ans: 

A sub-select is a select which works in conjunction with another select. A nested select is a kind of sub-select where the inner select passes to the where criteria for the outer select.

15)  What is the difference between group by and order by?

Ans: 

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

16)  Explain the EXPLAIN statement.

Ans: 

The explain statement provides information about the optimizer’s choice of access path of the SQL.

17)  What Is an Access Path?

Ans: 

The path that is used to get to data specified in SQL statements.

18)  What Is Db2 (ibm Database 2)?

Ans: 

DB2 is a subsystem of the MVS operating system. It is a Database Management System (DBMS) for that operating system.

19) What Does Dsndb07 Database Do?

Ans: 

DSNDB07 is where DB2 does its sorting. It includes DB2’s sort work area and external storage.

20) What Is An Alias?

Ans: 

It is an alternate name that can be used in SQL statements to refer to a table or view in the same or remote DB2 subsystem.

    Subscribe For Free Demo

    21) Explain What A Plan Is?

    Ans: 

    Plan is a DB2 object (produced during the bind process) that associates one or more database request modules with a plan name.

    22)  What Is A Db2 Bind?

    Ans: 

    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).

    23)  How Do You Select A Row Using Indexes In Db2?

    Ans: 

    Specify the indexed columns in the WHERE clause of db2 query.

    24) How Do You Find The Maximum Value In A Column In Db2?

    Ans: 

    Use SELECT MAX(…) .. in db2 query

    25) How Do You Retrieve The First 5 Characters Of Firstname Column Of Db2 Table Emp ?

    Ans: 

    SQL Query :

    • SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP;

    26) What Are Aggregate Functions?

    Ans: 

    Bulit-in mathematical functions for use in SELECT clause.

    27)  Can You Use Max On A Char Column?

    Ans: 

    YES.

    28) Define CHECK constraint.

    Ans: 

    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.

    29) What is SQLCA’s maximum length?

    Ans: 

    136 is the maximum length of the SQLCA.

    30) Discuss about DB2 bind?

    Ans: 

    The process that builds access paths to the DB2 table is known as bind. The bind uses Database Request Modules  from the DB2 pre-compile step as input and produces an application plan. It checks for user authentication and validates SQL statements in the DBRM(s).

    Course Curriculum

    Learn In-Demand Industry Experts Curated DB2 Certification Course

    Weekday / Weekend BatchesSee Batch Details

    31) List out the three types of page locks that can be held.

    Ans: 

    • Exclusive
    • Update
    • Share.

     32) Define buffer pool.

    Ans: 

    The 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.

    33) How and when does the Db2 enforces the unique key?

    Ans: 

    • DB2 utilizes unique index to avoid identical key values storage in database.
    • A unique index must be created whenever a table is to be created with primary key.
    • DB2 marks the table as unavailable until the needed indexes are created explicitly.
    • DB2 prevents the duplicate entities into the table that has unique key.
    • A unique key is declared by using the UNIQUE clause of CREATE TABLE command.
    • DB2 enforces the unique index when INSERT,UPDATE statements are applied to the table.
    • DB2 also enforces the unique index during the LOAD utility execution.

    34) What is CHECK constraint. Explain with example.

    Ans: 

    • A CHECK constraint is one of the constraints to ensure data integrity- CHECK constraint is specified as a condition / criteria
    • The value that is to be inserted or updated in a table, need to be tested by the CHECK constraint
    • If the value is meeting the criteria, then the data is inserted or updated in the designated column of a table
    • CHECK constraint is used at the time of creation of a table

    The following example illustrates the use of CHECK constraint:

    • CREATE TABLE PRODUCT (PROD_ID INTEGER NOT NULL,QUANTITY INTEGER CHECK (QUANTITY >= 1)

    35) What are the advantages of using a PACKAGE?

    Ans: 

    • Packages are the alternative to creating procedures and functions.
    • They are stand-alone schema objects.

    The advantages of packages are:

    • Modularity:All the procedures and functions belongs to a particular module of an application can be encapsulated in a package. 
    • Easy to design the application:The package body and package specification can be coded and compiled separately without its body. Stored procedures are compiled and executed by qualifying the package names. 
    • Hiding Information:The programming elements declared in the specification of the package is public. The package body elements are declared as private, i.e., they are invisible to the application. Hence they are secured. 
    • Added Functionality:Public variables and cursors are persisted for a session, which enables the sharing of data by all programs of the calling environment. 
    • Better Performance:Packaged sub programs are invoked for the first time by loading the entire package into the memory. Disk I/O operations are not needed for calling related sub programs in the package later. 
    • Overloading:Multiple programs can be placed in the same package with different number of parameters or types or parameters.

    36) What is DBRM?

    Ans: 

    • 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.

    37) What it contains?

    Ans: 

    • It contains executable host SQL statements.
    • During the pre-compilation process, the SQL code is embedded in the COBOL and will be extracted and moved into DBRM.

    38)  Every RDBMS database always followed some well define the structure for the executed SQL statement in their environment. In case of DB2 which component is responsible for executing the SQL statement? Explain with an example?

    Ans: 

    SQL statements can be varieties for every kind of databases. IBM Db2 has also followed some specific SQL structure define in their environment. Those SQL can be executed smoothly compare to other database and performance of the same little better than other always. Normally some of the define database service components are handling the same very smartly and execute it as faster as possible compared to other databases. IBM Db2 is mainly concentrating on executing SQL query very smartly without huge performance issue. DB2 has provided queue result of executing a query by this IBM DB2 database service components, and managing the huge buffering pool without loss of any critical transactional data store in the database.

    39) In DB2, is it possible to use one of the popular aggregator keywords like MAX if the column defines as CHAR? If yes, please explain the same how we can do it.

    Ans: 

    This is the basic DB2 Interview Questions asked in an interview. Yes, it is possible to use MAX easily in case of the column defined as CHAR. But it is required to ensure that a CHAR column should always contain some numeric value, in that case, MAX will always provide the correct result in case of DB2. If there have some non-numeric value then there have a lot of possibility of receiving some wrong kind of data. As an example suppose you are willing to get some MAX value of ‘www’, ‘099’, ‘99’ kind of data. Then it may give some kind of the wrong result as DB2 supported AS/400 which follows EBCDIC to store values. That’s why 099 will store as 99 in the database. So keep 99 always be a maximum value kind of approach.

    40)  What is referential integrity?

    Ans: 

    Referential integrity refers to the consistency that must be maintained between primary and foreign keys i.e. every foreign key value must have a corresponding primary key value.

    41)  What is a foreign key?

    Ans: 

    A foreign key is the key defined in one table to reference the primary key of a reference table. This foreign key must have the same structure as the reference table’s primary key.

    42) What is the database descriptor?

    Ans: 

    The database descriptor, DBD is the DB2 component that limits access to the database whenever objects are created, altered or dropped.

    43)  What is lock contention?

    Ans: 

    To maintain the integrity of DB2 objects the DBD permits access to only on object at a time. Lock contention happens if several objects are required by contending application processes simultaneously.

    44) What is the significance of DB2 free space and what parameters control it?

    Ans: 

    The two parameters used in the CREATE statement are the PCTFREE which specifies the percentage of free space for each page and FREEPAGE which indicates the number of pages to be loaded with data between each free page. Free space allows room for the insertion of new rows.

    45) What Information Is Used As Input To The Bind Process?

    Ans: 

    The database request module produced during the pre-compile. The SYSIBM.SYSSTMT table of the DB2 catalog.

    46) What Is Meant By The Attachment Facility?

    Ans: 

    The attachment facility is an interface between DB2 and TSO, IMS/VS, CICS, or batch address spaces. It allows application programs to access DB2.

    47)  What Is Meant By Auto Commit?

    Ans: 

    AUTO COMMIT is a SPUFI option that commits the effects of SQL statements automatically if they are successfully executed.

    48)  What Is A Base Table?

    Ans: 

    A base table is a real table – a table that physically exists in that there are physical stored records.

    49)  What Is The Function Of Buffer Manager?

    Ans: 

    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).

    50)  My Sql Statement Select Avg(salary) From Emp Yields Inaccurate Results. Why?

    Ans: 

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

    Course Curriculum

    Best In-Depth Practical Oriented DB2 Training By Expert Trainers

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    51)  What Is The Use Of Value Function?

    Ans: 

    • Avoid -ve SQLCODEs by handling nulls and zeroes in computations
    • Substitute a numeric value for any nulls used in computation

    52)  Explain The Function Of Data Manager ?

    Ans: 

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

    53)  What Is A Storage Group (stogroup)?

    Ans: 

    STOGROUP is a named collection of DASD volumes, which is to be used by index spaces and table spaces of the database.

    54)  Define Predicate?

    Ans: 

    Predicate is an element of a search condition. It expresses or implies a search condition.

    55) Explain the function of Data Manager.

    Ans: 

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

    56)  What Is Declaration Generator(dclgen)?

    Ans: 

    DCLGEN is a facility that is used to generate SQL statements that describe a table or view. These table or view descriptions are then used to check the validity of other SQL statements at precompile time. The table or view declares are used by the DB2I utility DCLGEN to build a host language structure, which is used by the DB2 precompiler to verify that correct column names and data types have been specified in the SQL statement.

    57)  What Is A Data Page?

    Ans: 

    A data page is a unit of retrievable data, either 4K or 32K (depending on how the table is defined), containing user or catalog information.

    58) Define Declaration Generator (DCLGEN).

    Ans: 

    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.

    59)  List out the buffer pools in DB2

    Ans: 

    There are four buffer pools in DB2 and they are:

    • BP0
    • BP1
    • BP2
    • BP32

    60) When it will be created?

    Ans: 

    The DBRM is created after pre-compilation of SQL statements.

    61) What are sqlcodes –803,-805, -811, -818,-904,-911,-913,-101, +100?

    Ans: 

    • 803: INSERT or UPDATE operations are performed in constrained UNIQUE INDEX columns with certain unique values. 
    • 805: An attempt to the application program that uses a DBRM or a package. The location-name, collection-id, dbrm-name, consistency-token are not found. 
    • 811: An embedded select statement’s result is a table with more than one row or the result of subquery’s predicate is exceeding one value. 
    • -818: Variation of consistency token in DBRM and the load modules are mismatching / different. 
    • -904: The resource is unavailable for the execution of SQL code. 
    • -911: The current transaction has been rolled out because of deadlock or time out. 
    • -913: Execution is unsuccessful due to deadlock or timeout.
    •  -101: Complicated or long SQL or long SQL query. Need to rephrase the query. 
    • +100: Row was not found for a fetch. Update/delete/query is resulting an empty table.

    62) What do you mean by NOT NULL WITH DEFAULT? When will you use it?

    Ans: 

    • NOT NULL WITH DEFAULT is a clause of CREATE INDEX.
    • It is ensured that the uniqueness of the key column, that is defined with index. But allows the NULL values in the column.
    • It indicates that the column could not contain a NULL, when a user does not enter any value. DB2 generates the default value.
    • It ensures that no null values are persisted.
    • NOT NULL WITH DEFAULT is used when there must be a value in the columns. For example every product should have product name, every employee should have employee name.

    63) Are view updateable?

    Ans: 

    • Certain views are updateable.
    • A single table view can be updatable.
    • Views with joins, aggregate functions, having GROUP BY clause are non-updateable views.

    64) What is COPY PENDING status?

    Ans: 

    • 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.

    65) One of the common approaches in the RDBMS database, suppose we are expecting the average salary for an entire organization from a specific table by using common aggregator AVG. Is there any possibility of given some wrong average value for any kind of common mistake? If yes explain about the mistake and how we can recover the same?

    Ans: 

    We are normally calculating an average value by using AVG aggregator for one of the columns which holding numeric values. Sometimes developer by mistake mentions that column as expected null, then the average value will always come as wrong as it will consider the null value as one of the values of salary. Also, they did some mistake of mentioning 0 in the salary field, rather than mentioning 0 deactivate or removing the member who doesn’t have any salary will give more appropriate result.

    66)  What is a NULL value? What are the pros and cons of using NULLS?

    Ans: 

    A NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value. It’s the DB2 equivalent of TBD on an organizational chart and often correctly portrays a business situation. Unfortunately, it requires extra coding for an application program to handle this situation. For DB2 queries use NULL indicator.

    • 0 : If column is not null that is it have some value in it.
    • -1: If column contains null
    • -2: If column contains null as a part of data conversion.

    67)  What is a synonym? How is it used?

    Ans: 

    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.

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

    Ans: 

    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.

    69)  If the base table underlying a view is restructured, e.g. attributes are added, does the application code accessing the view need to be redone?

    Ans: 

    No. The table and its view are created a new, but the programs accessing the view do not need to be changed if the view and attributes accessed remain the same.

    70) What is the self-referencing constraint?

    Ans: 

    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.

    EDB2 Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    71)  What Is A Buffer Pool?

    Ans: 

    A buffer pool is main storage that is reserved to satisfy the buffering requirements for one or more tablespaces or indexes, and is made up of either 4K or 32K pages.

    72)  On The Create Tablespace, What Does The Close Parameter Do?

    Ans: 

    CLOSE physically closes the tablespace when no one is working on the object. DB2 (release 2.3) will logically close tablespaces.

    73)  What Is A Clustering Index?

    Ans: 

    It is a type of index that

    • locates table rows and
    • determines how rows are grouped together in the tablespace.

    74)  What Will The Commit Accomplish?

    Ans: 

    COMMIT will allow data changes to be permanent. This then permits the data to be accessed by other units of work. When a COMMIT occurs, locks are freed so other applications can reference the just committed data.

    75)  Define Declaration Generator (dclgen) ?

    Ans: 

    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.

    76) Define Clustering Index ?

    Ans: 

    Clustering index is a type of index, which locates the table rows and determines how to group the rows together in the tablespace.

    77)  What Is Concurrency?

    Ans: 

    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.

    78)  Explain The Function Done By Data Manager?

    Ans: 

    Data manager can be considered as a component that is capable of managing the databases that are physically present and is capable of invoking other components associated with the system for performing functionalities like logging, locking and in performing other I/O operations.

    79) Define clustering index.

    Ans: 

    Clustering index is a type of index, which locates the table rows and determines how to group the rows together in the tablespace.

    80)  What Is A Database Request Module(dbrm)?

    Ans: 

    A DBRM is a DB2 component created by the DB2 pre-compiler containing the SQL source statements extracted from the application program. DBRMs are input to the bind process.

    81) What Is The Function Of The Data Manager?

    Ans: 

    The Data Manager is a DB2 component that manager the physical databases. It invokes other system components, as necessary, to perform detailed functions such as locking, logging, and physical I/O operations (such as search, retrieval, update, and index maintenance).

    82) Explain about DBRM.

    Ans: 

    DBRM stands for 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.

    83) Define Data page.

    Ans: 

    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.

    84)  Explain about RCT.

    Ans: 

    RCT is expanded as 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.

    85) How can tablespace be moved to another DASD volume that is allocated for that tablespace?

    Ans: 

    Tablespace that you are using is allocated only to STOGROUP, then you can enter the command ALTER STOGROUP for adding as well as deleting volume. REORG TABLESPACE and RECOVER TABLESPACE are statements that are helpful in creating new STOGROUP that can point towards the new volume. ALTER tablespace and REORG and RECOVER are statements used for altering and recovering the tablespace allocated in the memory.

    86)  What is the information associated with SYSIBM.SYSLINKS table?

    Ans: 

    This is the table that contains information on the links that exists between the tables created through referential constraints.

    87) Explain in detail about buffer manager and its functionalities?

    Ans: 

    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.

    88)  Explain about cursor stability?

    Ans: 

    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.

    89) What is REORG? When is it used?

    Ans: 

    • Data reorganization on physical storage is done by REORG.
    • It is used for reclaiming the space by restoring the free space.
    • Rows can be clustered using REORG.
    • The overblown rows can be positioned in their proper sequence.
    • REORG is advantageous to be used after heavy updates, inserts and delete operations.
    • Useful followed by segments of a segmented table spaces.

    90) How is a typical DB2 batch program executed?

    Ans: 

    DSN utility can be used to run a DB2 batch program from native TSO.- The following example illustrates the execution of a batch program in DB2:DSN SYSTEM (DSP3)RUN PROGRAM (EDD470BD) PLAN (EDD470BD) LIB (‘ED01T.OBJ.LOADLIB’)END- To run the DSN command in JCL, use IKJEFT01 utility program.

    91) Is DECLARE TABLE in DCLGEN necessary? Why it used?

    Ans: 

    • Declaration of table in DCLGEN is not necessary.
    • DECLARE TABLE in DCLGEN is necessary to validate the table-name, view-name, column-name … during pre-compilation by the pre-compiler.

    92)How do you leave the cursor open after issuing a COMMIT? (For DB2 2.3 or above only)

    Ans: 

    • Cursor can be left open after issuing a COMMIT.
    • By using WITH HOLD option in the DECLARE CURSOR statement.
    • The pseudo-conversational CICS programs will not be effected.

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

    Ans: 

    • 1 indicates the field is null.
    • 0 indicates the field is not null.
    • 2 indicates the field is truncated.

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

    Ans: 

    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.

    95) What is the difference between SPUFI and QMF?

    Ans: 

    SPUFI:

    • Several queries can be executed at once.
    • The result of the query will be stored in PS or in PDS member.
    • SQL Code is known after the executing the query.
    • SPUFI is a quick and dirty SQL execution engine. 

    QMF:

    • More than one query can not be executed.
    • The result of the query can not be persisted.
    • SQL Code of the query can not be known.
    • QMF is a query or reporting environment and supports formatting of reports.

    96) Suppose we are willing to shutdown DB2 database or startup one DB2 database. Then which component needs to be used for handling startup and shutdown?

    Ans: 

    DB2 start up and shut down can be handled by system define service components of DB2. There have two key system service components, global db2start and db2stop are mainly managing for starting and shut down of DB2 database.

    97) Suppose we are willing to apply some locks in the DB2 database, then which level of execution we can able to add those locks. Give some more details on the same?

    Ans: 

    DB2 database locking or any RDBMS database locking system are well defined. There have several phases they can apply locking:

    • Page: locking can be done for an entire page. In that case none of the table accessible at the lock period.
    • Table: locking the table. One that specific table will not be accessible by another request.
    • Table Space: tablespace can be the lock, in that case, all the table using that specific tablespace cannot accessible during the locking period by another request.

    98)  What Is Meant By Concurrency?

    Ans: 

    Concurrency is what allows more than one DB2 application process to access the same data at essentially the same time. Problems may occur, such as lost updates, access to uncommitted data, and un-repeatable reads.

    99) What is a composite index and how does it differ from a multiple index?

    Ans: 

    A multiple index is not one index but two indexes for two different columns of a table. A composite index is one index made up of combined values from two columns in a table. If two columns in a table will often be accessed together a composite index will be efficient.

    100) What is meant by index cardinality?

    Ans: 

    The number of distinct values for a column is called index cardinality. DB2’s RUNSTATS utility analyzes column value redundancy to determine whether to use a tablespace or index scan to search for data.

    101) Explain transactions, commits and rollbacks in DB2?

    Ans: 

    In DB2 a transaction typically requires a series of updates, insertions and deletions that represent a logical unit of work. A transaction puts an implicit lock on the DB2 data. Programmers can use the COMMIT WORK statement to terminate the transaction creating smaller units for recovery. If the transaction fails DB2 uses the log to roll back values to the start of the transaction or to the preceding commit point.

    102) What are the three lock types?

    Ans: 

    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.

    103) What is isolation level?

    Ans: 

    This is a key concept for any relational database. Isolation level is the manner in which locks are applied and released during a transaction. For DB2 a ‘repeatable read’ holds all locks until the transaction completes or a SYNCPOINT is issued. For transactions using ‘cursor stability’ the page lock releases are issued as the cursor ‘moves’, i.e. as the transaction releases addressability to the records.

    104) What is the difference between static and dynamic SQL?

    Ans: 

    Static SQL is hard-coded in a program when the programmer knows the statements to be executed. For dynamic SQL the program must dynamically allocate memory to receive the query results.

    105)  DB2 can implement a join in three ways using a merge join, a nested join or a hybrid join. Explain the differences?

    Ans: 

    A merge join requires that the tables being joined be in a sequence; the rows are retrieved with a high cluster ratio index or are sorted by DB2. A nested join does not require a sequence and works best on joining a small number of rows. DB2 reads the outer table values and each time scans the inner table for matches. The hybrid join is a nested join that requires the outer table be in sequence.

    106)  What is the difference between IN sub-select and EXISTS sub-select?

    Ans: 

    If there is an index on the attributes tested an IN is more efficient since DB2 uses the index for the IN. (IN for index is the mnemonic).

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free