DB2 Interview Questions and Answers

March 5th, 2019

In case you’re searching for DB2 Interview Questions and answers for Experienced or Freshers, you are at the correct place. GangBoard offers Advanced DB2 Interview Questions and answers that assist you in splitting your DB2 interview and procure dream vocation as a DB2 Developer.

Q1) Why mainframe is call has z os?

Answer: Due to zero downtime mainframe is call has z os

Q2) What is the -204 SQL error message?

Answer: Table not found

Q3) What is the -551 error message?

Answer: Lack of access issue.

Q4) What are the utility in db2?

Answer: Image copy, load, REROG, RUSTAT.

Q5) What is the map _table?

Answer: The map table is the user in the reorg to update the access point of the table.

Q6) What is the -805 issue?

Answer: DBRM missing

Q7) What is the 904 issue?

Answer: Resource unavailable

Q8) What Is the 910 issue?

Answer: Time out issue

Q9) What is the -905?

Answer: CPU asquint resource limits

Q10) What is the -911?

Answer: Deadlock issue

Q11) What is the -205?

Answer: Column name not found

Q12) What is the -924?

Answer: Db2 connection issue

Q13) What is 818?

Answer: Plan and program time stamp mismatch

Q14) What is -206?

Answer: Column does not there in select

Q15) What is the -207?

Answer: Invalid column name

Q16) What is -209?

Answer: Ambiguous column name

Q17) What is the -209 error message?

Answer: Both sides of the comparison in a SELECT.

Q18) What is the -224?

Answer: FETCH cannot make an INSENSITIVE cursor SENSITIVE.

Q19) What is the -229?

Answer: The statement was not found.

Q20) What error message -257?

Answer: Implicit conversion in datatype is not allowed.

Q21) What is this the error message for -305?

Answer: Null indicator needed.

Q22) What is SQL code -551?

Answer: Does not have the access for a specific id

Q23) What is SQL code -305?

Answer: Null value is not handled null indicator

Q24) What is SQL code -310?

Answer: Decimal value missed

Q25) What is -502?

Answer: Cursor not open

Q26) What is SQL code -503?

Answer: Cursor is already used

Q27) What is SQL code -532?

Answer: Foreign key violation

Q28) What is SQL code -803?

Answer: Duplicate recode found

Q29) What is SQL code -805?

Answer: Plan or package not found

Q30) What is SQL code -818?

Answer: Timestamp mismatch

Q31) What is SQL code -922?

Answer: Authorization failure

Q32) What TCL in db2?

Answer:

Transaction control language
Commit
Rollback

Q33) What is the access is a need for creating the table?

Answer: Admin access

Q34) What DCL?

Answer: Data control langue

Q35) What is SQL code -181?

Answer: Bad data format in data and time data type

Q36) What is the -199?

Answer: Illegal use of symbol in query

Q37) What is 206?

Answer: Specification of the column not found

Q38) What is the SQL code -029?

Answer: Into clause required

Q39) What is the SQL code -112?

Answer: An aggregate function is required

Q40) What is the SQL code -204?

Answer: Undefined name/table name is not found

Q41) What is the SQL code -251?

Answer: Token name is not valid

Q42) What is the JCL?

Answer: Job control language

Q43) What is Tso?

Answer: Time-sharing option

Q44) What is ispf?

Answer: Interactive system productivity facility

Q45) Why we use conditional steps in JCL?

Answer: Because if previous steps result with correct RC then only next step should execute so we have used conditional parameter in JCL

Q46) What are the main parameters of JCL?

Answer: Job, exec, dd

Q47) How much charter hold in job card?

Answer: 8bytes

Q48) What is the statement of job card?

Answer:
Job name
Job operation
Job parmeter

Q49) What is msg level?

Answer:
It is used for printing system information
Syntax
Msglevel=(m1,m2)?

Q50) What is msg class

Answer: Jcl message routed

Q51) What is means by REORG? When is it used REORG?

Answer:

Data REORG on physical amassing is done by REORG.
It is used for recouping the space by restoring the free space.
Sections can be gathered using REORG.
The overstated sections can be arranged in their fitting plan.
REORG is useful to be used in the wake of overpowering updates, inserts and delete exercises.
Significantly sought after by sections of parceled tablespaces.

Q52) What is a DBRM, PLAN?

Answer:

DBRM is characterized by the DataBase Request Module.
It holds the SQL proclamations that are separated from the host language program acquired by the pre-compiler.
PLAN is the aftereffect of the BIND procedure and has executable code for SQL explanations in DBRM.

Q53) What is implied by ACQUIRE/RELEASE in Scrape?

Answer:

Get/RELEASE stuck Imbroglio decides the time when the DB2 either gets or discharges locks against the table and the tablespaces.
This incorporates the purpose locks.

Q54) What are the benefits of utilizing PACKAGES?

Answer:

At the point when utilized, PACKAGES stays away from the authoritative of enormous quantities of DBRM individuals in a single arrangement.
It likewise gets rid of the expense of enormous BIND and stays away from the whole exchanges making them inaccessible during BIND and programmed REBIND of the arrangement.
Another bit of leeway is that it limits the fallback complexities when changes bring about a mistake.

Q55) What is accumulation?

Answer:

Accumulation is a client characterized name that functions as the grapple for bundles yet has no physical presence.
The utilization is for the gathering of bundles.

Q56) What is a gathering?

Answer:

A gathering is a client characterized name that fills in as the grapple for bundles yet has no physical presence.
The utilization is for the gathering of bundles.

Q57) What is suggested by DCLGEN?

Answer:

DCLGEN proposes DeClarations GENerator that is used to make the host language copy books for the table definitions.
It also makes the DECLARE table.

Q58) What is the substance of DCLGEN?

Answer:

The substance of DCLGEN is the EXEC SQL DECLARE TABLE explanation that gives the relationship of the table/see concerning the DB2 data types.
It can in like manner be depicted as the host language copy book giving host variable definitions for the part names.

Q59) What is a CURSOR and what is its use?

Answer:

A CURSOR is a programming device that tendencies the SELECT to find the methodology of fragments yet shows to them each thusly.
Since the host language can oversee only each line in this manner.

Q60) How to recoup portions from a DB2 table in embedded SQL?

Answer:

Pieces structure DB2 table embedded SQL can be recuperated using the single line SELECT explanation.
Another course is by using the CURSOR. In any case, the standard system is the favored one.

Q61) What is the consequence of the OPEN CURSOR verbalization?

Answer:

In essential OPEN CURSOR explanation, the CURSOR is put on the fundamental section of the table.
On the other hand, if the ORDER BY stipulation is joined, sections would be gotten, composed and would be open for the FETCH clarification.

Q62) Is DECLARE TABLE in the DCLGEN key? For what reason did it use?

Answer:

Declaration of the table in DCLGEN isn’t fundamental.
Report TABLE in DCLGEN is critical to support the table-name, see the name, section name … during pre-collection by the pre-compiler.

Q63) What is SQLCA?

Answer:

SQLCA speaks to SQL Communication Area.
SQLCA is a structure of components that are revived after each SQL clarification’s execution.
Unequivocally only one SQLCA ought to be given to an application that contains executable SQL clarifications.
SQLCA doesn’t have any critical bearing on the JAVA application.
More than one SQLCA ought to be suited FORTRAN application.

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

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

Q66) What is CHECK necessity? Explain with the model.

Answer:

A CHECK impediment is one of the objectives to ensure data reliability
CHECK restriction is demonstrated as a condition/criteria
The value that will be implanted or invigorated in a table, ought to be attempted by the CHECK restriction
If the value is meeting the criteria, by then the data is installed or revived in the doled out fragment of a table
CHECK constraint is used at the hour of the development of a table.

Q67) What is DBRM? What it contains? When it will be made?

Answer:

DBRM speaks to the Database Request Module.
The yield of the pre-total strategy is addressed as DBRM.
The SQL verbalizations are expelled from the host language by the pre-compiler.

Q68) What it contains?

Answer:

It contains executable host SQL declarations.
During the pre-gathering process, the SQL code is embedded in the COBOL and will be isolated and moved into DBRM.

Q69) I don’t get your importance by NOT NULL WITH DEFAULT? When will you use it?

Answer:

NOT NULL WITH DEFAULT is an announcement of CREATE INDEX.
It is ensured that the uniqueness of the key segment, that is described with the rundown. Regardless, licenses the NULL characteristics in the section.
It shows that the area couldn’t contain a NULL when a customer doesn’t enter any value. DB2 produces the default regard.
It ensures that no invalid characteristics are persisted.
NOT NULL WITH DEFAULT is used when there must be a motivation in the segments. For example, everything should have a thing name, every agent should have a specialist name.

Q70) Is see updateable?

Answer:

Certain points of view are updateable.
A single table view can be updatable.
Viewpoints with joins, all-out limits, having GROUP BY arrangement are non-updateable points of view.

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

Q72) Explain related sub-request.

Answer:

Related sub-request are those business wherein the nester request inside side prescribes back to the outside arrangements’ table.
For each line that is qualified, the appraisal of related sub-request is an all-out need.

Q73) Notice the structure for including relatively as setting a CURSOR to use in a COBOL program.

Answer:

The best system for setting a CURSOR to use in a COBOL program is to use DECLARE CURSOR, which can be used either in structure division movement or in working managing.
This is being finished all things considered to consolidate the SELECT verbalization.
Once DECLARE CURSOR is used, this is trailed by OPEN, FETCH in end CLOSE.

Q74) What is the information identified with SYSIBM?SYSLINKS table?

Answer: This is the table that contains information on the affiliations that exist between the tables made through referential basics.

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

Q76) What is the social affair in the db2 joined the server?

Answer:

A pack is a specific, bound DBRM that contains moved to get to approaches to manage DB2. Social affairs are the yield of the “Pickle Package” heading which takes DBRM as data.
There can be one DBRM per pack.
Right, when the social event is formed using the DBRM it might be urged to the “Issue Plan” requesting to get an application Plan.

Q77) Is cement speedier than IsNull?

Answer:

Cement is inside recommended a 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.

Q78) What is the control level in db2?

Answer:

Segment Level in DB2 One of the ways DB2 keeps up synchronization is utilizing separation levels, which pick how data got to comparably as balanced by one trade is by all accounts “bound from” various trades.
DB2 sees and reinforces the going with separation levels: Repeatable Read (RR) Read Stability (RS)

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

Q80) What is Ceil’s work?

Answer: The CEIL() work restores the most unassuming whole number worth that is a higher need than or comparable to a number. Note: This reason for the constraint is obscure from the CEILING() work.