Ibm Db2 Interview Questions And Answers
Question: 1. What Is Db2 (ibm Database 2)?
Answer:
DB2 is a subsystem of the MVS operating machine. It is a Database Management System (DBMS) for that running gadget.
Question: 2. What Is An Access Path?
Answer:
The route that is used to get to records specified in SQL statements.
IBM Websphere Interview Questions
Question: 3. What Is An Alias?
Answer:
It is an change call that can be utilized in SQL statements to refer to a table or view inside the equal or faraway DB2 subsystem.
Question: four. Explain What A Plan Is?
Answer:
Plan is a DB2 object (produced during the bind technique) that associates one or extra database request modules with a plan call.
IBM Websphere Tutorial
Question: five. What Is A Db2 Bind?
Answer:
Bind is a method that builds “get admission to paths” to DB2 tables. A bind uses the Database Request Modules(s) (DBRM(s)) from the DB2 pre-collect step as enter and produces an application plan. It additionally checks the user’s authority and validates the SQL statements within the DBRM(s).
IBM-REXX Interview Questions
Question: 6. What Information Is Used As Input To The Bind Process?
Answer:
The database request module produced in the course of the pre-assemble. The SYSIBM.SYSSTMT desk of the DB2 catalog.
Question: 7. What Is Meant By The Attachment Facility?
Answer:
The attachment facility is an interface among DB2 and TSO, IMS/VS, CICS, or batch cope with spaces. It permits software packages to access DB2.
IBM-REXX Tutorial IBM Cognos Interview Questions
Question: eight. What Is Meant By Auto Commit?
Answer:
AUTO COMMIT is a SPUFI choice that commits the results of SQL statements robotically if they are successfully carried out.
Question: 9. What Is A Base Table?
Answer:
A base table is a real table – a table that bodily exists in that there are physical saved statistics.
J2EE Interview Questions
Question: 10. What Is The Function Of Buffer Manager?
Answer:
The buffer supervisor is the DB2 element answerable for physically moving facts among an outside medium and (digital) garage (plays the actual I/O operations). It minimizes the quantity of physical I/O truly finished with state-of-the-art buffering strategies(i.E., examine-in advance buffering and appearance-apart buffering).
IBM Cognos Tutorial
Question: 11. What Is A Buffer Pool?
Answer:
A buffer pool is most important garage that is reserved to meet the buffering necessities for one or more tablespaces or indexes, and is made from both 4K or 32K pages.
COBOL Interview Questions
Question: 12. How Many Buffer Pools Are There In Db2?
Answer:
There are 4 buffer swimming pools: BP0, BP1, BP2, and BP32.
IBM Websphere Interview Questions
Question: 13. On The Create Tablespace, What Does The Close Parameter Do?
Answer:
CLOSE bodily closes the tablespace whilst no person is operating on the object. DB2 (release 2.3) will logically close tablespaces.
IBM DB2 Tutorial
Question: 14. What Is A Clustering Index?
Answer:
It is a sort of index that (1) locates table rows and (2) determines how rows are grouped together inside the tablespace.
Question: 15. What Will The Commit Accomplish?
Answer:
COMMIT will allow data adjustments to be permanent. This then lets in the statistics to be accessed with the aid of different gadgets of work. When a COMMIT happens, locks are freed so other applications can reference the just dedicated statistics.
IBM-JCL Interview Questions
Question: sixteen. What Is Meant By Concurrency?
Answer:
Concurrency is what permits a couple of DB2 utility method to get right of entry to the same facts at basically the equal time. Problems may additionally occur, which includes misplaced updates, get right of entry to to uncommitted records, and un-repeatable reads.
J2EE Tutorial
Question: 17. What Is Cursor Stability?
Answer:
It is cursor balance that “tells” DB2 that database values study by using this application are blanketed only at the same time as they’re getting used. (Changed values are covered till this software reaches the commit factor). As soon as a application movements from one row to any other, different packages may additionally study or the first row.
DB2 Using SQL Interview Questions
Question: 18. What Is The Function Of The Data Manager?
Answer:
The Data Manager is a DB2 element that supervisor the physical databases. It invokes different device additives, as necessary, to perform specified capabilities including locking, logging, and bodily I/O operations (inclusive of seek, retrieval, replace, and index maintenance).
IBM-REXX Interview Questions
Question: 19. What Is A Database Request Module(dbrm)?
Answer:
A DBRM is a DB2 thing created by means of the DB2 pre-compiler containing the SQL source statements extracted from the utility application. DBRMs are enter to the bind process.
COBOL Tutorial
Question: 20. What Is A Data Page?
Answer:
A facts web page is a unit of retrievable facts, both 4K or 32K (relying on how the desk is defined), containing user or catalog records.
IBM WAS Administration Interview Questions
Question: 21. What Is Declaration Generator(dclgen)?
Answer:
DCLGEN is a facility that is used to generate SQL statements that describe a desk or view. These desk or view descriptions are then used to test the validity of different SQL statements at precompile time. The desk or view proclaims are utilized by the DB2I software DCLGEN to construct a host language shape, that’s used by the DB2 precompiler to verify that accurate column names and statistics sorts were unique in the SQL assertion.
Question: 22. What Does Dsndb07 Database Do?
Answer:
DSNDB07 is where DB2 does its sorting. It consists of DB2’s kind paintings vicinity and outside garage.
IBM-JCL Tutorial
Question: 23. What Will The Free Command Do To A Plan?
Answer:
It will drop(delete) that current plan.
IBM WebSphere Administration Interview Questions
Question: 24. What Is A Host Variable?
Answer:
This is a data object this is used in an SQL declaration to acquire a fee or to supply a value. It have to be preceded by means of a colon (:) to inform DB2 that the variable isn’t a column call.
IBM Cognos Interview Questions
Question: 25. What Will The Db2 Optimizer Do?
Answer:
The optimizer is a DB2 issue that techniques SQL statements and selects the get entry to paths.
DB2 Using SQL Tutorial
Question: 26. What Is A Page?
Answer:
This is the unit of garage inside a desk area or index area that is accessed by using DB2.
Database Administration Interview Questions
Question: 27. What Is Pagespace?
Answer:
Pagespace refers to either to an unpartitioned table, to an index area, or to a single partition of a partitioned desk of index area.
J2EE Interview Questions
Question: 28. What Is A Predicate?
Answer:
A predicate is an element of a search situation that expresses or implies a assessment operation.
Question: 29. What Is A Resource Control Table(rct)? Describe Its Characteristics.
Answer:
The RCT is a desk that is defined to a DB2/CICS region. It consists of control characteristics that are assembled through the DSNCRCT macros. The RCT suits the CICS transaction ID to its associated DB2 authorization ID andplan ID(CICS attachment facility).
DB2 SQL Programming Interview Questions
Question: 30. Where Are Plans Stored?
Answer:
Each plan is defined uniquely inside the SYSIBM.SYSPLANS desk to correspond to the transaction (s) which might be to execute that plan.
Question: 31. What Is Meant By Repeatable Read?
Answer:
When an application application executes with repeatable examine protection, rows referenced by way of this system can’t be changed by other programs until the program reaches a commit factor.
Question: 32. Describe What A Storage Group(stogroup) Is?
Answer:
A STOGROUP is a named collection of DASD volumes to be utilized by tablespaces and index areas of databases. The volumes of STOGROUP must be of the equal device kind.
Mainframe DB2 Interview Questions
Question: 33. What Is The Format (inner Layout) Of “timestamp”?
Answer:
This is a seven component fee that includes a date (yymmdd) and time(hhmmss and microseconds).
COBOL Interview Questions
Question: 34. What Is Meant By A Unit Of Recovery?
Answer:
This is a chain of operations within a unit of labor(i.E., work completed between commit factors).
Question: 35. Can Dasd Types Assigned To Storage Groups Be Intermixed(i.E., 3350s And 3380s)?
Answer:
No
Question: 36. What Are The Three Types Of Page Locks That Can Be Held?
Answer:
Exclusive, replace, and percentage.
IBM-JCL Interview Questions
Question: 37. Can Db2 Be Accessed By Tso Users? If Yes, Which Command Is Used To Invoke Db2?
Answer:
DB2 may be invoked by means of TSO customers by the use of the DSN RUN command.
Question: 38. How Are Write I/os From The Buffer Pool Executed?
Answer:
Asynchronously.
Question: 39. What Is A Db2 Catalog?
Answer:
The DB2 catalog is a hard and fast of tables that comprise records approximately all of the DB2 objects(tables, views, plans and so forth.).
Question: 40. In Which Column Of Which Db2 Catalog Would You Find The Length Of The Rows For All Tables?
Answer:
In the RECLENGTH column of SYSIBM.SYSTABLES
DB2 Using SQL Interview Questions
Question: 41. What Information Is Held In Sysibm.Syscopy?
Answer:
The SYSIBM.SYSCOPY desk contains records approximately image copies product of the tablespaces.
Question: forty two. What Information Is Contained In A Syscopy Entry?
Answer:
Included is the call of the database, the table space call, and the photo replica type(complete or incremental and so forth.,) as well as the date and time each replica turned into made.
IBM WAS Administration Interview Questions
Question: forty three. What Information Can You Find In Sysibm.Syslinks Table?
Answer:
The SYSIBM.SYSLINKS table includes information about the hyperlinks between tables created by way of referential constraints.
Question: forty four. Where Would You Find Information About The Type Of Database Authority Held By The User?
Answer:
SYSIBM.SYSDBAUTH.
Question: forty five. Where Could You Look If You Had A Question About Whether A Column Has Been Defined As An Index?
Answer:
This records can be found in SYSIBM.SYSINDEXES.
Question: forty six. Once You Create A View, Where Would Information About The View Be Stored?
Answer:
When a view is created, gadget data about the view is saved in SYSIBM.SYSVIEWS.
Question: 47. What Is The Sql Communications Area And What Are Some Of Its Key Fields?
Answer:
It is a facts structure that must be blanketed in any host-language application the usage of SQL. It is used to skip comments approximately the sq. Operations to the program. Fields are return codes, mistakes messages, managing codes and warnings.
Question: 48. Maxx Number Of Columns In A Db2 Table?
Answer:
224
Question: forty nine. Explain The Use Of The Where Clause?
Answer:
It directs DB2 to extract facts from rows in which the fee of the column is the same as the cutting-edge value of the host variable.
Question: 50. What Is A Foreign Key?
Answer:
It identifies a releated row in every other table and establishes a logical relationship among rows in two tables.
Question: fifty one. What Technique Is Used To Retrieve Data From More Than One Table In A Single Sql Statement?
Answer:
The Join statement combines records from more that tables.
Question: 52. What Is The Maximum Number Of Tables That Can Be Joined ?
Answer:
fifteen
Question: 53. What Are The Max. & Min. No. Of Partitions Allowed In A Partition Tablespace?
Answer:
minimum is four.Maximum is sixty four.
Question: fifty four. Name The Different Types Of Table Spaces?
Answer:
1. Simple Table Space2. Segmented Table Space and3. Partitioned Table Space
Question: fifty five. What Is The Maximum Number Of Tables That Can Be Stored On A Partitioned Table Space ?
Answer:
ONE
Question: fifty six. How Do You Declare A Host Variable (in Cobol) For An Attribute Named Emp-name Of Type Varchar(25) ?
Answer:
01 EMP-GRP. 49 E-LEN PIC S9(four) COMP. Forty nine E-NAME PIC X(25).
Question: fifty seven. How To See The Structure Of Db2 Table?
Answer:
Using QMF.
Question: fifty eight. What Is The Difference Between Where And Having Clause?
Answer:
WHERE is for Rows and HAVING is for Groups
Question: 59. What Does Dml Stand For And What Are Some Examples Of It?
Answer:
Data Manipulation Language. Some examples are SELECT, INSERT, DELETE, REPLACE.
Question: 60. In A Db2-cics Program Which Is Acts As Co-ordinator And Which Is Participant?
Answer:
DB2 – player CICS- coordinator
Question: 61. How Do U Achieve Record Level Locking In Db2 Versions When Record Level Locking Is Not Allowed?
Answer:
By having the period of the report greater than that of a web page!
Question: 62. What Are The Levels Of Isolation Available With Db2v4?
Answer:
CS RR UR( brought new for DB2V4 which stands for uncommited examine which permits to retreive statistics from the space which has special locks also but statistics integrity might be affected if this selection is used )The pleasant available choice for records integrity & data concurrency is CS.
Question: 63. What Is The Difference Between Type 1 Index & Type 2 Index?
Answer:
TYPE 1 & TYPE 2 are designated whilst an index is created at the table. TYPE 2 index is the option which comes with DB2V4. With TYPE 2 index information can be retreived quicker as only the statistics pages are locked and not the index pages. Hence TYPE 2 index is recommended.
Question: sixty four. What Does Currentdata Option In Bind Indicate?
Answer:
CURRENTDATA alternative ensures block fetch whilst deciding on rows from a desk. In DB2V4 the default has been changed to NO. Therefore it’s miles important to exchange all of the bind playing cards with CURRENTDATA(YES) that is default in DB2V3 & earlier to CURRENTDATA(NO).
Question: sixty five. When Does The Sql Statement Gets Executed When You Use Cursor In The Application Programming ?
Answer:
square declaration receives carried out whilst we open cursor.
Question: sixty six. What’s The Maximum Number Of Volumes That Can Be Added To A Stogroup?
Answer:
The answer is 133.Usually it is going to be tough display more than three or four volumes to a Stogroup.
Question: 67. Can You Define An Index If The Table Size Less Than 10 Pages?
Answer:
NO
Question: sixty eight. What’s The Percentage Free Space ?
Answer:
ZERO
Question: sixty nine. What’s The Error Code For Unique Index Voilation?
Answer:
-803
Question: 70. What’s The Best Locksize That You Could Use When You Create A Tablespace?
Answer:
The answer is Locksize = ANY.Unless you’re Sure what is the Purpose of tablespace ie.,Read-best or R/W.If you use lock length =any, Db2 would routinely determine what form of locks it must use.
Question: seventy one. What Is The Size Of A Data Page?
Answer:
4K to 8K
Question: 72. Can All Users Have The Privilage To Use The Sql Statement Select * (dml)?
Answer:
NO THE USER SHOULD BE GRANTED PRIVILAGE TO USE IT.
Question: 73. The Only Place Of Vsam Ksds In Db2 Is?
Answer:
BSDS is a VSAM KSDS.
Question: 74. Max. No Of Rows Per Page?
Answer:
127
Question: seventy five. What Are The Functions Of Bind?
Answer:
BIND specifically performs two matters syntax checking and authorization checking.It binds collectively all applications into an utility plan hence the call BIND.Apart from this bind has optimiser as a subcomponent.Its function is to determine the most useful get right of entry to approach.
Question: seventy six. What Is A Corelated Subquerry?
Answer:
In a subquerry, if the outer querry reffers lower back to the final results of innerquerry it is known as corelated subquerry. That’s why the outer querry is evaluated first in contrast to an everyday subquerry.
Question: seventy seven. What Is The Difference Between Join And Union?
Answer:
be part of is used to retrive statistics from exceptional tables the usage of a unmarried square announcement.Union is used to mix the results of or greater sq. Querries.
Question: 78. When Can You Be Sure That A Query Will Return Only One Row?
Answer:
When you use the number one key and best the primary key inside the wherein clause.
Question: seventy nine. What Is The Name Of The Default Db2 Catalog Database?
Answer:
DSNDB06
Question: eighty. What Should Be Specified Along With A Cursor In Order To Continue Updating Process After Commit?
Answer:
With Hold option.
Question: eighty one. What Is The Command Used By Tso Users To Invoke Db2?
Answer:
DSN RUN
Question: eighty two. B37 Abend During Spufi?
Answer:
The b37 abend within the spufi is because of space necessities , the question has ended in so many rows that the spufi.Out record is not big sufficient to address it, growth the space allocation of spufi.Out record.
Question: eighty three. How Many Bufferpools Are There In Db2 And What Are They?
Answer:
There are four Bufferpools.They are BP0,BP1,BP2 and BP32.
Question: 84. What Is Join And Different Types Of Join?
Answer:
The ability to join rows and combaine records from two or extra tables is one of the maximum effective capabilities of relational machine.Three type of joins:
Equi-be part of
Non-equijoin
self-join.
Question: 85. How Many Subqueries Can You Combine Together ?
Answer:
Total 16 queries and subqueries are 15