DB2 Interview Questions

DB2 is one of the most widely used relational databases worldwide. Both freshers and experienced candidates across various roles need a solid understanding of this relational database. MindMajix's experts have compiled a list of the top DB2 interview questions in this blog to help you answer accurately and confidently.

These questions cover a wide array of topics on the subject, from the basic to the advanced. Once you've finished reading them, you'll have a firm understanding of DB2 and will easily face your interview.

• Basic
• Intermediate
• Advanced
• Cobol DB2
• IBM DB2 
• FAQs
• Tips

Top 10 Frequently Asked DB2 Interview Questions

1. What is DB2?
2. What is an instance in DB2?
3. What is a clustering index?
4. Difference between a package and a collection?
5. What is DB2 Optimizer?
6. Explain DBRM.
7. How to create the Cobol DB2 program?
8. What is IBM Db2 used for?
9. What is schema in Db2?
10. Does Db2 use SQL?

Basic DB2 Interview Questions

1. What is DB2?

Database2, or DB2, is a family of database management systems that include operational databases, data lakes, data warehouses, and data servers. It stores, analyses, and retrieves the organization's data efficiently. Both structured and unstructured data can be handled easily using DB2 environments.

If you want to enrich your career and become a professional in SQL Server, then enroll in "SQL Server Online Training" - This course will help you to achieve excellence in this domain.

2. What data types does DB2 support?

DB2 supports various data types, such as:

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

It's essential to know the different types of data available since they impact the usage in completing business tasks.

3. How many components are there in DB2?

DB2 has four major components.

1. System services - It supports system operations.
2. Locking services - It manages concurrent data access.
3. Database services - It handles SQL statements execution.
4. Distributed data facility - It offers distributed database support.

4. What is an instance in DB2?

Ans: A logical environment for DB2 management is called an instance. You may manage databases with the instance. Depending on the specifications, you can set up several instances on a single physical system. The instance directory contains the following:

• System Database Directory
• Dump files
• Database Manager Configuration file
• Node Directory
• Node Configuration File 
• Debugging files

5. What is Bufferpool in DB2?

A bufferpool is a portion of the main memory space allocated by the database manager. Bufferpools are used to cache tables and index data from the disc. A bufferpool is specific to each database. When a new database is built, a default bufferpool is also created.

6. Describe DB2 tablespaces.

A table space, a storage structure, contains tables, indexes, huge objects, and lengthy data. It can be utilized to group the data in a database into logical storage units that match the data's location on a system. These tablespaces are maintained in database partition groups.

Benefits of tablespaces in the database

• Tablespaces simplify backup and restore procedures. You can back up or convert each database object in a tablespace with just one command.
• Depending on the requirements, the database manager creates and expands containers.
• A single bufferpool can control multiple tablespaces. To speed up operations like sorts and joins, temporary tablespaces might be given access to their bufferpool.

7. How many types of tablespaces are there in Db2? 

• Catalog tablespace. 
• User tablespace. 
• Temporary tablespace.

8. What are the four environments which can access DB2?

CICS, IMS, BATCH, and TSO are the four environments that can access DB2.

9. How is using a PACKAGE beneficial?

Using a package is beneficial in many ways. It avoids the high bind cost and the necessity to bind several database request module (DBRM) members into a plan. In addition, it reduces the fallback complexities when errors occur due to modifications.

10. What is the use of SQLCA?

An SQLCA is a group of variables updated at the end of every SQL statement execution. One SQLCA is sufficient for an application that has SQL statements that can be executed. Multiple SQLCAs must be provided for FORTRAN. SQLCA does not apply to Java.

The maximum length of the SQLCA is 136. Some main fields that form SQLCA are SQLCODE, SQLERRM, and SQLERRD.

11. Describe the DB2 isolation level.

When any application processes data access, the isolation level determines the range to which data is locked or isolated from other concurrent processes.

Following are the four primary isolation levels:

• Repeatable read (RR) - Locks the table and ensures no data change in the entire transaction duration.
• Read stability (RS) - Support the result set stability and 
• Cursor stability (CS) - Unlocks each row that the cursor references.
• Uncommitted read (UR) - Provides access to read-only tables and read-only queries.

12. What is UNION in Db2?

In DB2, UNION is a method to merge select statements to fasten the delivery of query results. 

13. What is a check constraint?

Check constraint provides the correct data for queries of the database. Also, it helps in determining the values you can insert or update to the table.

14. What are the types of page locks?

The lock function helps build integrity in the database developers manage. The types of page locks include exclusive, update, and share.

15. Name the four buffer pools in Db2.

The four buffer pools in Db2 are BP0, BP1, BP2, and BP32.

Intermediate DB2 Interview Questions

16. What is the function of the LIKE statement in Db2?

In DB2, the LIKE statement determines the Boolean value when the original expression mentioned contains a characteristic or a specific part of the string.

17. What's the physical storage length of the data types DATE, TIME, and TIMESTAMP?

The physical storage length for 

• TIME is 3 bytes
• TIMESTAMP is 10 bytes
• DATE is 4 bytes

18. What is ALTER?

ALTER can be used to change Db2 object definitions. It allows you to alter, add, or remove columns from a table.

19. What is a clustering index?

A clustering index helps determine how rows are physically ordered (clustered) in a table space. It overall improves the performance and benefits those involving many records.

20. What is the buffer manager?

The role of the buffer manager is to physically transfer data between storage (input/output operations) and the external medium (virtual). It reduces the amount of I/O performed using buffer techniques.

21. Explain the release and acquire parameters in binding.

You can rely on the acquire and release parameters when the locks need to be taken and released.

The bind step's ACQUIRE(ALLOCATE) and RELEASE(DEALLOCATE) options define that locks will be taken on the tables when the thread starts and released when it finishes (used in the program).

22. What is the difference between package and collection?

Package: A package contains control structures for SQL statements execution in DB2., They are stored in the database system catalog tables.

Collection: A collection is a group of bound packages. Its main objective is to simplify the package sets.

23. Can we open multiple cursors for the same program? 

Cursors can be of two types: explicit and implicit, and Db2 supports both. Multiple cursors may be open at once for developers.

24. Explain storage groups.

 A storage group, a defined group of storage pathways, can be used to save data. The designated storage groups represent the various storage classes available to your database system. Table spaces can be allocated to the storage group that best fits the data. Only automatic storage tablespaces employ storage groups.

25. What is schema?

A schema is an organized collection of named objects in a database.

You cannot create several database objects with the same name in a database. The schema provides a collaborative setting to aid with this. Multiple database entries with the same name but different schema groups might exist in a database, along with many different schemas.

A schema can have several elements, such as tables, functions, indices, tablespaces, procedures, triggers, etc.

26. What are DB2 constraints?

A set of rules is defined to enforce database integrity, called constraints. The columns' values are allowed or prohibited by the constraints.

The constraint types are

• NOT NULL - It prohibits null values from one or more columns within the table.
• Unique - Sets columns values uniquely.
• Primary key - Declares relationships between multiple tables.
• Foreign Key - Allows the required relationship between the tables.
• Check - Adds conditional restrictions for a specific column in a table.
• Informational - The SQL compiler uses it to improve data access.

27. What are the triggers?

A trigger is a collection of operations carried out in response to an INSERT, UPDATE, or DELETE operation on a database table. In the database, triggers are automatically stored. Multiple programs can access and share them. They handle data governance.

The benefit of employing triggers is that any change to the application may be made at the trigger rather than modifying every program that uses the trigger. Triggers support quicker application development and are simple to manage.

Types of triggers.

• BEFORE triggers - They execute before any SQL operation.
• AFTER triggers - They execute after any SQL operation.

28. What is the use of sequence in DB2?

Ans: A sequence is a software function that creates integer numbers within a specific range in either ascending or descending order to create the primary key and coordinate other table keys. You use a sequence to access integer numbers like employee or transaction identifiers. SMALLINT, BIGINT, INTEGER, and DECIMAL are among the data types that a sequence can support.

Types of Sequences

• NEXTVAL: Returns an incremented value for a sequence number.
• PREVIOUS VALUE: Returns a recently generated value.

29. What are DB2 roles?

A role is a database object that combines multiple privileges and grants them to users, groups, PUBLIC, or other roles using a GRANT statement. 

30. What is meant by Lock Escalation?

The "lock escalation" technique helps to extend page lock sizes to tables or table space lock sizes when a transaction acquires more locks than those specified in NUMLKTS. Locks must be taken on objects in a single table space for escalations.

Advanced DB2 Interview Questions

31. What is DB2 Optimizer?

The DB2 optimizer may select the optimum query access plan if you choose the best optimization class for your workload and have reliable catalog statistics.

The benefits of DB2 Optimizer:

• It processes the SQL statements.
• It helps in selecting the access path.

32. What is the difference between CHAR and VARCHAR?

As the name implies, the primary difference between the two is that VARCHAR has a variable length, whereas CHAR has a fixed size. It means that whereas VARCHAR modifies its length following the length of the text to help save memory, CHAR always has a fixed length to hold the content. The maximum size for the CHAR data type is 254 bytes, whereas the maximum value for VARCHAR is 4046 bytes.

33. Write a query to find the number of rows in a DB2 table.

SELECT COUNT * FROM tablename

34. What is the difference between UNION and UNION ALL in DB2?

The union command combines two or more SELECT statements and can be used on one or more tables. The essential difference between UNION and UNION ALL is that when applied to tables, UNION eliminates duplicate rows, whereas UNION ALL retains them in place.

35. What is the difference between DELETE and DROP?

The DELETE * FROM table-name query removes every record from the table, but the database still contains an empty table with no rows or columns. However, the DROP TABLE table-name query removes (deletes) the table from the database by removing all the rows and columns and the table altogether. 

36. What is concurrency in Db2?

Concurrency is the capacity of many application processes to access the same data effectively concurrently. We need to control the concurrency to prevent missing updates, repetitive reads, access to uncommitted data, and other potentially undesired impacts.

37. What is a DCLGEN in DB2?

 DCLGEN generates a table or view declaration as a component of a partitioned data set that you can include in the program. When you use DCLGEN to create a table declaration, Db2 pulls the relevant information from the Db2 catalog.

38. How to use RUNSTATS? 

 A DB2 utility called RUNSTATS analyzes a table space or indexes to learn more about how efficiently the space is being used. The DB2 system tables store the collected data, and the SQL optimizer uses it to select the most advantageous access paths throughout the binding process.

Usually, the RUN STATS executed after the following:

• After a mass update.
• After REORG the table.
• After a load.
• After any significant deletions, insertions, etc.

39. Explain DBRM.

The DB2 component, DBRM, or Database Request Module, is created by DB2's pre-compiler. This module consists of SQL source statements taken directly from the application software. The binding process benefits from the inputs that DBRMs form.

40. Define the Data page.

A unit that can retrieve data from the database is a data page. The database from which we recover the data is in the form of 4 or 32 kilobytes. The way we define the table within the database determines how the data is retrieved. Additionally, the data page includes details about the user or catalog in the database.

41. Explain RCT.

RCT or Resource Control Table is defined in the DB2/CICS region. It consists of information acquired using DSN CRCT macros. RCT corresponds to the DB2 authorization and CICS transaction ID, respectively. 

42. How can a DASD volume allocated for tablespace be transferred to another?

You can use the command ALTER STOGROUP to add and remove volumes from the tablespace you are currently using if it is only allocated to STOGROUP. Statements used to change and recover the tablespace created in memory include ALTER tablespace, REORG, and RECOVER. A new STOGROUP that can point to the new volume can be created using the statements REORG TABLESPACE and RECOVER TABLESPACE.

43. What do you know about cursor stability?

The "cursor stability" parameter informs DB2 that the database values that are read by the application are protected while the data is being used.

44. Comment whether DCLGEN is necessary. If that were the case, what purpose would it serve?

It is not mandatory to use DCLGEN. As a tool, DCLGEN will only produce variable definitions for the host, which lowers the possibility of errors. The main application of DCLGEN is in the pre-compilation stage, which aids in identifying misspelled column names.

45. How would the VARCHAR column REMARKS be defined?

The VARCHAR column REMARKS would be defined as follows:

• 10 REMARKS
• 49 REMARKS-LEN PIC S9(4) USAGE COMP
• 49 REMARKS-TEXT PIC X(1920)

46. What is the result of the OPEN CURSOR statement?

The simple OPEN CURSOR statement places the CURSOR on the top row of a table. In contrast, rows are fetched, sorted, and made available for the FETCH process when the ORDER BY clause is present.

47. What information is associated with the SYSIBM.SYSLINKS table?

This table details the relationships between the tables made possible by referential constraints.

48. Let's say the table has an Alias called AL1. Which query helps to drop the AL1 alias?

DROP ALIAS AL1 is the query to drop an alias.

Cobol DB2 Interview Questions

49. Mention how we can use CURSOR in a COBOL program.

The best way to use a CURSOR in a COBOL program is to use DECLARE CURSOR, which can be used in working storage or process division operation. After using DECLARE CURSOR, OPEN, FETCH, and CLOSE are used. The SELECT statement is highlighted by doing this.

50. Define the COBOL picture clause with a DB2 column defined as DECIMAL (11, 2).

PIC S9 (9) V99 COMP – 3

In the expression DECIMAL (11, 2), 2 is the precision, whereas 11 is the data type size.

51. If DB2 were to go down at any moment, how would that affect the pre-compilation of a DB2-COBOL program?

The way the DB2-COBOL software is precompiled will stay the same even if DB2 is unavailable at some point.

52. Mention the definition of COBOL in the VARCHAR field.

The REMARKS of the VARCHAR column are as follows: –

• 10 REMARKS
• 49 REMARKS – LEN PIC S9 (4) USAGE COMP.
• 49 REMARKS – TEXT PIC X (1920).

53. How to create the Cobol DB2 program?

Follow the below steps to create the Cobol DB2 program:

Step1: Create all these necessary tables

Step2: Create DCLGEN(optional)

Step3: Precompile

Step4: Compile and Link Edit

Step5: DB2 BIND

Step6: Execute/Run the Program

IBM DB2 Interview Questions

54. What is IBM Db2 used for?

IBM Db2 is a family of data management products. With the solutions' AI-powered features, you can modernize structured and unstructured data administration in on-premises and multi-cloud settings.

Related Article: IBM DB2 Interview Questions and Answers

55. Is IBM Db2 a SQL database?

Structured query language allows us to access the data in IBM DB2.

DB2 Interview Questions FAQs

1. Does Db2 use SQL?

The structured query language (SQL) is used to access the data in Db2 tables. For defining and modifying data in a relational database, SQL is the standard language.

2. What is DB2 in the mainframe?

Db2 is software that helps in relational database management. 

3. What is schema in Db2?

A schema is a group of named objects. A schema logically groups the objects in a database. A schema could include items like tables, indexes, table spaces of various types, functions, stored procedures, and triggers, to name a few. In a schema name, the qualifier comes first.

4. What is LDAP in Db2?

Lightweight Directory Access Protocol or LDAP is a Global Directory Service. This industry-standard protocol operates on a layer above the TCP/IP stack and is based on a client-server approach. The ability to connect to, access, change, and search the online directory is made possible through LDAP.

5. What is float in Db2?

The FLOAT function returns a floating-point representation of either a number or a string representation of a number.

6. What is a directory in Db2?

A directory consists of a set of Db2 tables.

7. How is data stored in Db2?

A Db2 database presents all of its data in tables, which are groupings of rows with the same columns.

Tips to prepare for the DB2 Interview

Research the company

Spend a couple of hours gathering as much information as possible on the company. Find current and previous employees by asking around in your network. It would be best if you also studied recent press releases and, yes, spent some time on Google. Candidates frequently only look at the information that a company promotes on its website and social media accounts and need to dig deeper into what others say. You'll gain a complete understanding of the organization by consulting various sources, and you'll be prepared to explain why you want to work there and what you can contribute.

Know everything about the open position

Before you can impress the interviewer that you'd be a great hire, you must know the prerequisites. Fortunately, most companies make it evident in the job posting what they are looking for in an applicant. Review the job description you received before submitting your resume once again. 

Test your knowledge

Test your knowledge before an interview by participating in practice tests and quizzes. Also, speak with seniors or subject-area experts. It will help you figure out your current preparation status. You can recognize areas for improvement.

Be thorough with the fundamentals. 

As you prepare for your interview, you must thoroughly understand the subject's concepts. Also, give real-time examples in your responses.

Be an early bird 

Lastly, whether your interview is online or in person, be earlier than your scheduled time.

Conclusion

While these questions and answers will prepare you for tackling interviews for any work involving DB2, the most important thing is how correctly and confidently you respond, as that is what counts most! You can gain in-depth subject knowledge and practical learning with the right learning partner. Enroll in MindMajix's SQL Server Training Course today and gain in-demand skills.

If you have attended DB2 interviews or have any questions you'd like answered, please share them in the comments area below. We'll respond to you at the earliest.