DB2 Interview Questions And Answers prepared from Codingcompiler experts. These DB2 interview questions were asked in various interviews conducted by top multinational companies across the globe. We hope that these interview questions on DB2 Development will help you in cracking your next DB2 job interview. All the best and happy learning.
DB2 Interview Questions
DB2 Interview Questions And Answers
Frequently Asked DB2 Interview Questions and Answers
Top Best DB2 Interview Questions
DB2 Interview Questions
1. What is DB2 ( IBM Database 2 )?
2. How to find the number of rows in a DB2 table?
3. How can the duplicate values be eliminated from DB2 SELECT
4. What is Aggregate?
5. What is an access path?
6. What is an alias?
7. Is it possible using MAX on a CHAR column?
8. Explain what a plan?
9. What could be the reason that the MySQL statement ‘SELECT AVG (SALARY) FROM EMP’ generates an inaccurate output?
10. What are UNION and UNION ALL?
DB2 Interview Questions And Answers
1. What is DB2 ( IBM Database 2 )?
Answer: DB2 is subsystem of the MVS operating system. It is a Database Management System ( DBMS ) for that operating system.
2. How to find the number of rows in a DB2 table?
Answer: To find the number of rows in a DB2 table, the user has to use SELECT COUNT (*) on the DB2 query.
3. How can the duplicate values be eliminated from DB2 SELECT?
Answer: To eliminate the duplicate values from DB2 SELECT, the user has to use SELECT DISTINCT in the DB2 query.
4. What is Aggregate?
Answer: ‘Aggregate’ functions are built-in mathematical tools that are used in the DB2 SELECT clause.
5. What is an access path?
Answer: The path that is used to get to data specified in SQL statements.
6. What is an alias?
Answer: It is an alternate name that can be used in SQL training Hyderabad statements to refer to a table or view in the same or remote DB2 subsystem.
7. Is it possible using MAX on a CHAR column?
Answer: Yes! It is possible using MAX on a CHAR column.
8. Explain what a plan?
Answer: Plan is a DB2 object (produced during the bind process ) that associates one or more database request modules with a plan name.
9. What could be the reason that the MySQL statement ‘SELECT AVG (SALARY) FROM EMP’ generates an inaccurate output?
Answer:
The reason for the statement generating an inaccurate outcome is that SALARY has not been declared to have NULL values and, at the same time, the employees whose salary is not known are also counted.
10. What are UNION and UNION ALL?
Answer: Both UNION and UNION ALL are used to combine the results generated by multiple SELECT statements.
UNION is the function that eliminates duplicates in the table
UNION ALL retains the duplicates
DB2 Interview Questions And Answers
11. Is there any restriction on the use of UNION in Embedded SQL?
Answer:
While using UNION in Embedded SQL, it has to be in a CURSOR.
12. What is meant by the attachment facility?
Answer: The attachment facility is an interface between DB2 and TSO, IMS/VS, CICS, or batch address spaces. It allows application programs to access DB2.
13. What is meant by AUTO COMMIT
Answer: AUTO COMMIT is a SPUFI option that commits the effects of SQL statements automatically if they are successfully executed.
14. Which component checks DB2 deadlocks?
Answer: Locking services are provided by Locking services component which is known as Internal Resource Lock Manager (IRLM) and manages concurrency issues and dead locks.
15. Which component is responsible for execution of SQL statements?
Answer: Database services component is responsible for execution of SQL statements and it manages buffer pool also.
16. What is the physical storage length of DATE data type?
Answer: DATE data types take 4 bytes and default is ‘YYYY-MM-DD’
17. Cursors are used to handle single row selections at a time. They are data structure which hold all the results of a query. State whether true or false?
Answer: This statement is incorrect as cursors are used to handle multiple row selections at a time.
18. When a table is dropped all the alias get dropped automatically. State whether true or false?
Answer: This statement is incorrect. Synonyms get dropped automatically when we drop the table but alias of the table remains there. We need to manually drop the alias.
19. How you will count the number of rows from a table TAB?
Answer: SELECT COUNT(*) FROM TAB query is used to count the number of rows in a table.
20. When you will face SQLCODE -818?
Answer: SQLCODE -818 is a timestamp mismatch of load module and bind timestamp built from DBRM.
Frequently Asked DB2 Interview Questions and Answers
21. Why SELECT is not preferred in Embedded SQL programs?
Answer:
SELECT is not preferred in Embedded SQL programs for three reasons. First, if the table structure is changed by the addition or deletion of a field and the program is modified, then using SELECT might retrieve the columns that the user may not use. This would lead to Input-Output overhead and the chances of index-only scan are also eliminated.
22. When you will face SQLCODE -922?
Answer: If a user is not authorized to access DB2 objects, he will face SQLCODE -922.
23. How to retrieve rows from a DB2 table in Embedded SQL?
Answer: Rows from a DB2 table in Embedded SQL can be retrieved using the single-row SELECT statement. Another way is by using the CURSOR statement. However, the first process is the preferred one.
24. What will the COMMIT accomplish?
Answer: 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.
25. What is -206?
Answer: Column does not there in select
26. What is the -207?
Answer: Invalid column name
27. What is -209?
Answer: Ambiguous column name
28. What is the -209 error message?
Answer: Both sides of the comparison in a SELECT.
29. Is it possible to change a table – for example including a fragment when another customer is getting to or invigorating a couple of portions?
Answer:
It is possible to change the table.
- The DB2 tables won’t re-structure until any trade is submitted.
- Another section is portrayed and perceived by the database.
- The new section will be fused just after the trades of the table are submitted.
- All the changing table announcements will be gotten from various customers and are taken care of.
- The submit status is revived by the database engine and after that, the new fragments are incorporated.
30. How and when does the Db2 actualize the novel key?
Answer:
- DB2 utilizes a unique document to keep up vital good ways from unclear key characteristics set away in the database.
- A remarkable rundown must be made at whatever point a table is to be made with the fundamental key.
- DB2 marks the table as difficult to reach until the necessary records are made unequivocally.
- DB2 envisions duplicate substances into the table that has a unique key.
- A stand-out key is broadcasted by using the UNIQUE state of the CREATE TABLE request.
- DB2 maintains the striking rundown when INSERT, UPDATE verbalizations are applied to the table.
- DB2 in like manner approves the unique record during the LOAD utility execution.
Top Best DB2 Interview Questions
31. What is COPY PENDING status?
Answer:
- A status happens when an image copy on a table ought to be taken.
- The table is available only for inquiries and can not be revived.
- Copy PENDING status can be cleared by evacuating the image copy or by using REPAIR utility.
32. What Is The Function Of Buffer Manager?
Answer :
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).
33. What Is A Buffer Pool?
Answer :
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.
34. Explain in detail the pad boss and its functionalities?
Answer:
- Pad supervisor can be considered as the part inside DB2 that colleagues in moving data between virtual for the most part as the external medium.
- The help authority reduces the level of physical duty in like way as to yield rehearses that are performed by using buffering out and out pushed techniques.
35. What Will The Commit Accomplish?
Answer :
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.
36. Is cement speedier than IsNull?
Answer:
- Cement is inside recommended CASE explanation, ISNULL is an inside engine purpose of repression.
- The mix is an ANSI standard most remote point, ISNULL is T-SQL.
- Execution complexities can and do arise when the choice affects the execution plan at any rate the partition in quite far speed is minute.
37. What Is a Declaration Generator(dclgen)?
Answer :
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.
38. What Is Pagespace?
Answer :
Pagespace refers to either to an unpartitioned table, to an index space, or to a single partition of a partitioned table of index space.
39. What Is A Predicate?
Answer :
A predicate is an element of a search condition that expresses or implies a comparison operation.
40. What Is A Db2 Catalog?
Answer :
The DB2 catalog is a set of tables that contain information about all of the DB2 objects(tables, views, plans etc.).
41. What is the explanation behind the floor work?
Answer:
- In figuring and programming working, beyond what many would consider possible is the limit that takes as information a real number and gives as yield the best entire number not so much or identical to, represented.
- Also, beyond what many would consider possible maps to the least entire number more noticeable than or undefined from, showed up.
42. Once You Create A View, Where Would Information About The View Be Stored?
Answer :
When a view is created, system information about the view is stored in SYSIBM.SYSVIEWS.
OTHER INTERVIEW QUESTIONS
- SQL Interview Questions
- SQL Server Interview Questions For Experienced
- RPA Interview Questions And Answers
- Android Interview Questions
- Mulesoft Interview Questions
- JSON Interview Questions
- PeopleSoft HRMS Interview Questions
- PeopleSoft Functional Interview Questions
- PeopleTools Interview Questions
- Peoplesoft Technical Interview Questions
- 199 Peoplesoft Interview Questions
- 200 Blue Prism Interview Questions
- Visualforce Interview Questions
- Salesforce Interview Questions
- 300 SSIS Interview Questions
- PHP Interview Questions And Answers
- Alteryx Interview Questions
- AWS Cloud Support Interview Questions
- Google Kubernetes Engine Interview Questions
- AWS Devops Interview Questions
- Apigee Interview Questions
- Actimize Interview Questions
- Kibana Interview Questions
- Nagios Interview Questions
- Jenkins Interview Questions
- Chef Interview Questions
- Puppet Interview Questions
- DB2 Interview Questions
- AnthillPro Interview Questions
- Angular 2 Interview Questions
- Hibernate Interview Questions
- ASP.NET Interview Questions
- Kubernetes Interview Questions