Grab Deal : Flat 20% off on live classes + 2 free self-paced courses! - SCHEDULE CALL
Interview- Those dreadful minutes of interaction that can make or break your career. Many times, it is seen that the candidates are really good and talented, but they experience interview anxiety. You can control this anxiety by preparing well for your interview. To prepare well for your interview, you need to take a good glance at what kind of questions are asked for the job role that you are applying for.
Today in this blog, we have collated a list of the most frequently asked questions for interviews that require a sound knowledge of DB2 related concepts and principles. The blog covers db2 mainframe interview questions and answers for experienced as well as entry-level candidates or freshers you can say. These DB2 mainframe interview questions will let you know about Subselect, Inner and Outer join, GroupBy, OrderBy, etc. DB2 questions have been written to get you acquainted with SQL databases.
DB2 is a popular Database Management System used for the MVS Operating System where the DB2 is a subsystem of the MVS Operating System.
DB2 is an enormous relational database, the board framework with numerous segments in it. The four noteworthy segments of DB2 are:
The information changes can be caused perpetual by utilizing To submit. It likewise allows information to be gotten to by different applications that can reference the submitted information.
It is an information structure that must be incorporated into any host-language program utilizing SQL. It is utilized to pass criticism about the SQL activities to the program. Fields are return codes, blunder messages, taking care of codes, and warnings.
SQL Communication Area is a structure of factors, which are refreshed after each execution of SQL explanations. For an application that contains executable SQL proclamations, only one SQLCA is required. FORTRAN needs more than one SQLCA to be given. For Java, SQLCA isn't pertinent.
SQLCODE, SQLERRM, SQLERRD
The procedure that assembles get to ways to the DB2 table is known as the bind. The quandary utilizes Database Request Modules from the DB2 pre-arrange venture as information and produces an application plan. It checks for client confirmation and approves SQL explanations in the DBRM(s). DB2 bind procedure does the accompanying-
More than one DB2 application procedure can get similar information in the meantime, known as simultaneousness. Be that as it may, issues can happen; for example, lost updates access to unrepeatable reads and uncertain information.
Information chief can be considered as a part that is fit for dealing with the databases that are physically present and is equipped for conjuring different segments related to the framework for performing functionalities like logging, securing, and performing other I/O operations.
DBRM stands for Database Request Module and is a component inside DB2, which is created by the precompiler 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.
Data page can be considered as a unit that is fit for recovering information from the database. The database from which the information can be recovered is as 4 kilobytes or 32 kilobytes. The structure wherein information is recovered relies upon how the table is characterized inside the database. The information page likewise contains data in regards to the index or client that are a piece of the database.
RCT is extended as a Resource–Control Table and is characterized in the DB2/CICS district. This is the part that includes highlights that are assembled through macros of DSNCRCT. RCT matches with the exchange ID that of CICS, with the approval ID that of DB2. This ought to likewise be coordinated with plan ID.
A CURSOR is a programming device that helps the SELECT statement find out a set of rows but displays them one at a time. This is because the host language can deal with only one row at a time.
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.
In the simple OPEN CURSOR statement, the CURSOR is placed on the first row of a table. On the other hand, when the ORDER BY clause is added, rows would be fetched, sorted, and would be available for the FETCH statement.
Yes! The user can have more than one cursor open for any program.
The VARCHAR column REMARKS would be defined as follows:
The physical storage length of DATE is 4 bytes, TIME is 3 bytes, and TIMESTAMP is 10 bytes.
DCLGEN refers to Declaration Generator that is used to create the host language copy books for the table definitions. It also creates the DECLARE table.
DCLGEN has the EXEC SQL DECLARE TABLE statement that gives the layout of a table/view in terms of DB2 data types. It can also be defined as the host language copy book giving the host variable definitions for column names.
Major fields in SQLCA are SQLCODE, SQLERRM, and SQLERRD.
EXPLAIN is used for displaying the access path as determined by the optimizer for SQL statements. It can also be used in the case of SPUFI for single SQL statements or in the BIND step for Embedded SQL.
Users can use SPUFI or QMF statements to perform EXPLAIN for Dynamic SQL statements. They can also include the EXPLAIN command in the Embedded Dynamic SQL statements.
Two isolation levels are possible: One is Cursor Stability and the other is Repeatable Read denoted as CS and RR, respectively.
CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of a transaction.
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 a single table space for escalations to take place.
The tablespace that you are utilizing is distributed distinctly to STOGROUP, at that point you can enter the direction ALTER STOGROUP for including just as erasing volume. REORG TABLESPACE and RECOVER TABLESPACE are articulations that are useful in making new STOGROUP that can point towards the new volume. Adjust tablespace and REORG, and RECOVER are proclamations utilized for modifying and recuperating the tablespace dispensed in the memory.
The following are the points of interest connected to a PACKAGE.
The ideal method for putting a CURSOR to use in a COBOL program is to utilize DECLARE CURSOR, which can be utilized either in system division activity or in working stockpiling. This is being done fundamentally to feature the SELECT articulation. Once DECLARE CURSOR is utilized, this is trailed by OPEN, FETCH lastly CLOSE.
It is cursor stability that "tells" DB2 that database esteems pursued by this application are secured just while they are being utilized. (Changed qualities are secured until this application comes to the submit point). When a program moves to start with one line then onto the next, different projects may peruse or the main column.
It isn't required to bring DCLGEN into utilization. The essential utilization of DCLGEN comes during the pre-arrangement stage, where it helps in identifying the incorrectly spelled section names. Since DCLGEN is only an apparatus, it will just create variable definitions for the host and decrease the odds of mistakes.
Regardless of whether DB2 is down sooner or later of time, and still, at the end of the day, it won't affect the pre-gathering procedure of DB2-COBOL program. The purpose behind the equivalent is that the pre-compiler never alludes to the inventory tables of DB2.
DBRM indicates DataBase Request Module. It has the SQL explanations that are removed from the host language program gotten by the pre-compiler. PLAN is the consequence of the BIND procedure and has executable code for SQL statements in DBRM.
The buffer manager is the DB2 segment in charge of physically moving information between an outer medium and (virtual) stockpiling (plays out the genuine I/O activities). It limits the measure of physical I/O really performed with complex buffering techniques (i.e., read-ahead buffering and look-aside buffering).
Locks assume a crucial job in keeping up the simultaneousness in the DB2 condition. The following are the dispute circumstances brought about by Locks, which can corrupt the DB2 execution.
A NULL worth takes up one byte of capacity and shows that worth is absent instead of space or zero worth. It's what might be compared to TBD on an authoritative outline and regularly effectively depicts a business circumstance. Sadly, it requires additional coding for an application program to deal with this circumstance.
The table and its view are re-built from scratch, but the programs that are accessing the view do not have to be altered in case the view and attributes that have been accessed remaining the same.
Never does DB2 allow such a thing. Such dispensation could yield more duplicate values after violating entity integrity. The Primary keys in DB2 must, in all cases, be updated one at a time only.
In the db2 precompilation process where Db2 precompiler scans the program & copies all the SQL statements & hosts variable data into database request module (DBRM). The precompiler also returns the modified source code so that SQL statements aren’t causing any errors when the program is compiled.
The full form of SQLCA is the SQL communication area or Structured Query Language Communication Area. It is a structure of variables that is updated after every execution of an SQL statement.
Only one SQLCA is needed for an application that contains executable SQL statements. However, SQLCA is not applicable to Java applications.
Yes, FORTRAN applications require more than one SQLCA.
The maximum length of SQLCA is 136.
To be short and crisp, the answer is yes, we can alter the table at the same time when some other transaction is taking place but this does not exactly happen as it sounds to be.
The DB2 table will not change its structure in any form till a transaction or multiple transactions are taking place. The table-altering commands will be stored by the database engine and will not be executed till all the transactions are complete. After one user has modified the values in the table, then the column will be added.
Can you tell me any issues that may be here?
Yes, there might be an issue. For instance, if there is such a case where we have set that the attendance of any student cannot be null and at the same time we have some students in the table whose attendance we don’t know, the AVG function will calculate these students as well. Hence, we will get an answer but, it might not be correct.
Learn SQL Server from top instructor. Enroll in SQL Server Training now!
A certain point in a program when DB2 acquires or releases the locks against the tables and/or tablespaces. These include the intent locks as well.
In a COBOL-DB2 program, all the statements are coded in Area B.
Learn More On: How To Use DB2/Other Data Storage As Data Source In SSRS
There can be 2 possibilities:
RUN STATS is a DB2 utility used to collect statistics about the data value in TABLES. It is used by the optimizer to decide the access path. RUN STATS also collects statistics that are used for space management. These statistics are stored in the DB2 catalog tables.
Usually, the RUN STATS is run after the following:
The SELECT statement is not preferred in embedded SQL programs for 3 reasons:
DSNDB07 is a database where the DB2 performs its sorting operations. It includes the sort work-area of DB2 and external storage.
We can achieve the record level locking in DB2 versions where the record level locking is not allowed by having the length of the record greater than that of a page itself.
These are some of the most frequently asked questions in a DB2 interview. The DB2 interview questions and answers will let you understand the executable codes or SQL Server queries to let you know how confident you are for the interview. The DB2 interview questions are framed by JanBask experts who give training to IT industrialists. You really need to pay good attention to the answers to these questions. Do read the list thoroughly before your interview. Good Luck!
Through Janbask’s SQL Server Training, you’ll learn the skills to excel in real-wold data and storage scenarios.
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
MS SQL Server
Receive Latest Materials and Offers on SQL Server Course