DB2 is a Database Management System that is pretty popular these days. Several corporate business companies are using this Database management system. Since it’s popular, the company needs more people to work for them and serve the needs of their customers appropriately.
So if you are planning to get a DB2 job then you are doing a smart thing as well. It’s a prestigious job and pays well too so more and more people are attracted to get the job. Although you might not be the only one to want to job and this is why you should be working hard to impress the interviewers and make them believe that you are good enough for the job.
One of the ways to impress the interviewer is to answer correctly to the questions you have been asked. You can only do so when you have enough knowledge and do practice for the interview questions before the actual interview. So all you need is a bunch of DB2 Interview Questions to practice so that you will have some good answers to the interviewer’s questions.
But the question is where would you find the most asked DB2 interview questions? Well, that’s right, right here on our article! Here we have several DB2 interview questions that you need to be aware of. So go ahead and take a look at the following DB2 interview questions and practice for the upcoming interview.
Db2 Interview Questions And Answers
1.How to find number of rows in DB2 tables?Ans-User has to use SELECT COUNT (*) on DB2 query.
2.How can duplicate values be eliminated from DB2 SELECT?Ans-To eliminate duplicate values in DB2 SELECT, the user has to use SELECT DISTINCT in DB2 query.
3.What is meant by aggregate function?Ans-‘Aggregate’ functions are built in mathematical tools that are used in DB2 SELECT clause.
4.Is it possible using MAX on a CHAR column?Ans-Yes! It is possible using the MAX on a CHAR column.
5.When the LIKE statement is used?Ans-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.
6.Why SELECT is not preferred in embedded SQL programs?Ans-SELECT is not preferred in embedded SQL programs for three reasons. First; if the table structure is changed by 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 is also eliminated.
7.What is the reason that MySQL statement “SELECT AVG (SALARY) FROM EMP” generates inaccurate output?Ans-The reason for the statement generating inaccurate outcome is because of the fact that SALARY has not been declared to have NULL and at the same time the employees whose salary is not known are also counted.
8.What is a CURSOR and what is its use?Ans-A 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.
9.How to retrieve rows from a DB2 table in embedded SQL?Ans-Rows form DB2 table embedded SQL can be retrieved using the single row SELECT statement. Another way is using the CURSOR. However, the first process is the preferred one.
10.What is the result of OPEN CURSOR statement?Ans-In simple OPEN CURSOR statement the CURSOR is placed on the first row of the table. On the other hand, if the ORDER BY clause is added, rows would be fetched, sorted and would be available for the FETCH statement.Now that you are aware of the benefits of Salesforce, for more detail check the SQL course.
DB2 Interview Questions
11.What is the physical storage length of the data types DATE, TIME, and TIMESTAMP?Ans-The physical storage length of DATE is 4 bytes, TIME is 3 bytes, and TIMESTAMP is 10 bytes.
12.What is meant by DCLGEN?Ans-DCLGEN refers to DeClarations GENerator that is used to create the host language copy books for the table definitions. It also creates the DECLARE table.
13.What are the contents of DCLGEN?Ans-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.
14.Can there be more than one cursor open for any program?Ans-Yes! The user can have more than one cursor open for any program.
15.How would the VARCHAR column REMARKS defined?Ans-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).
16.How can the FIRSTNAME and the LASTNAME from the EMP table concatenated to generate complete names?Ans-Statement to concatenate the first and last name to display the full name is FIRSTNAME II ** II LASTNAME FROM EMP.
17.How the value function is used?Ans-Two tasks are accomplished by the ‘Value’ function. The first one is avoiding –veSQLCODEs through the handling of NULLs as well as Zeroes in the computation. The second one is substituting numeric values for nulls used in computation.
18.What is UNION and UNION ALL?Ans-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.
19.Is there any restriction on its use in embedded SQL?Ans-While using UNION in embedded SQL it has to be in a CURSOR.
20.What are some of the main fields in SQLCA?Ans-Major Fields in SQLCA is SQL CODE, SQLERRM, and SQLERRD.
TCS DB2 Interview Questions
21.What is meant by EXPLAIN?Ans-Use of EXPLAIN is for displaying the access path as determined by the optimizer for SQL statement. It can also be used in case of SPUFI for single SQL statements or in the BIND step for embedded SQL.
22.How to do the EXPLAIN of any dynamic SQL statement?Ans-User can use SPUFI or QMF statements to EXPLAIN the dynamic SQL statement. They can also include EXPLAIN command in the embedded dynamic SQL statements.
23.What are the isolation levels possible?Ans-Two isolation levels are possible. One is CURSOR stability and the other one is Repeatable Read denoted as CS and RR respectively.
24.What is the difference between the CS and RR isolation levels?Ans-CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of the transaction.
25.What are the various types of locks?Ans-There are three different types of locks such as SHARE, EXCLUSIVE, and UPDATE.
26.What is ALTER?Ans-ALTER is the SQL command to change the definition of DB2 objects.
27.What is a DBRM, PLAN?Ans-DBRM denotes Data Base Request Module. It has the SQL statements that are extracted from the host language program obtained by the pre-compiler. PLAN is the result of the BIND process and has executable code for SQL statements in DBRM.
28.What are the advantages of using PACKAGES?Ans-When used, 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 fallback complexities when changes result in an error.
29.What is a collection?Ans-A collection is the user defined name that works as the anchor for packages but has no physical existence. The use is for grouping of packages.
30.What is dynamic SQL?Ans-Dynamic SQL is the SQL statement created at the time of execution of a program.
Db2 Interview Questions For Experienced
31.What is meant by ACQUIRE/RELEASE in BIND?Ans-ACQUIRE/RELEASE in BIND determines the point at which the DB2 either acquires or releases locks against the table and the table spaces. This includes the intent locks.
32.What is meant by PACKAGES?Ans-PACKAGES contain executable codes for SQL statements in respect of one DBRM.
33.What is meant by lock escalation?Ans-Lock escalation is the process of promoting PAGE lock sizes to table or table space lock size when the transaction has acquired more locks than the ones specified in NUMLKTS. Locks have to be taken on objects in single table space for escalations to take place.
34.What are meant by BETWEEN and IN and is BETWEEN inclusive of range values that are specified?Ans-BETWEEN would supply a range of values whereas IN would provide list of values. BETWEEN is always inclusive of the range values specified.
35.Define DB2.Ans-DB2 is a Database Management System for the MVS Operating System where, DB2 is a subsystem of MVS Operating System.
36.What are the uses of DB2 Optimizer?Ans-It processes SQL statements.It helps to select the access path
37.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.
38.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.
39.What is SQLCA’s maximum length?Ans-136 is the maximum length of the SQLCA.
40.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).
Best Db2 Interview Questions
41.List out the three types of page locks that can be held.Ans-ExclusiveUpdateShare.
42.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.
43.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.
44.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.
45.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.
46.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.
47.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.
48.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.
49.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.
50.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.
Db2 Job Interview Questions And Answers
51.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.
52.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.
53.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.
54.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.
55.Define predicate?Ans-Predicate is an element of a search condition. It expresses or implies a search condition.
56.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.
57.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
Conclusion:
So these are some of the best DB2 Interview questions that you can find. These questions have been picked by some experts and professionals who want to help the aspiring candidates. Try to answer these questions first on your own and then if you fail you can look up to their answers. You can also write the answers or record them to see how impressive you sound and know how to present an answer in the interview.That would be all that we have to offer you and we hope you found our article helpful. Go ahead and practice these questions and best of luck with your interview.
