300+ [MOSK ASKED] Ibm Db2 Interview Questions and Answers

1. What Is The Difference Between Join And Union?

join is used to retrive data from different tables using a single sql statement.union is used to combine the results of two or more sql querries.

2. What Is A Page?

This is the unit of storage within a table space or index space that is accessed by DB2.

3. What Does Currentdata Option In Bind Indicate?

CURRENTDATA option ensures block fetch while selecting rows from a table. In DB2V4 the default has been changed to NO. Therefore it is necessary to change all the bind cards with CURRENTDATA(YES) which is default in DB2V3 & earlier to CURRENTDATA(NO).

4. What Is An Alias?

It is an alternate name that can be used in SQL statements to refer to a table or view in the same or remote DB2 subsystem.

5. What Will The Commit Accomplish?

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.

6. What Information Can You Find In Sysibm.syslinks Table?

The SYSIBM.SYSLINKS table contains information about the links between tables created by referential constraints.

7. What Are The Max. & Min. No. Of Partitions Allowed In A Partition Tablespace?

minimum is 4.maximum is 64.

8. In Which Column Of Which Db2 Catalog Would You Find The Length Of The Rows For All Tables?

In the RECLENGTH column of SYSIBM.SYSTABLES

9. When Does The Sql Statement Gets Executed When You Use Cursor In The Application Programming ?

sql statement gets executed when we open cursor.

10. What Is A Db2 Catalog?

The DB2 catalog is a set of tables that contain information about all of the DB2 objects(tables, views, pl etc.).

11. What Is The Command Used By Tso Users To Invoke Db2?

DSN RUN

12. What Is A Data Page?

A data page is a unit of retrievable data, either 4K or 32K (depending on how the table is defined), containing user or catalog information.

13. What Is Meant By The Attachment Facility?

The attachment facility is an interface between DB2 and TSO, IMS/VS, CICS, or batch address spaces. It allows application programs to access DB2.

14. How Do U Achieve Record Level Locking In Db2 Versions When Record Level Locking Is Not Allowed?

By having the length of the record greater than that of a page!

15. What Is The Size Of A Data Page?

4K to 8K

16. What Is The Format (internal Layout) Of “timestamp”?

This is a seven part value that consists of a date (yymmdd) and time(hhmmss and microseconds).

17. What Is The Sql Communications Area And What Are Some Of Its Key Fields?

It is a data structure that must be included in any host-language program using SQL. It is used to pass feedback about the sql operations to the program. Fields are return codes, error messages, handling codes and warnings.

18. Where Would You Find Information About The Type Of Database Authority Held By The User?

SYSIBM.SYSDBAUTH.

19. Explain What A Plan Is?

Plan is a DB2 object (produced during the bind process) that associates one or more database request modules with a plan name.

20. What Is Join And Different Types Of Join?

The ability to join rows and combaine data from two or more tables is one of the most powerful features of relational system.Three type of joins:

  1. Equi-join
  2. Non-equijoin
  3. self-join.

21. What’s The Percentage Free Space ?

ZERO

22. In A Db2-cics Program Which Is Acts As Co-ordinator And Which Is Participant?

DB2 – participant CICS- coordinator

23. What Is The Name Of The Default Db2 Catalog Database?

DSNDB06

24. What Is The Function Of The Data Manager?

The Data Manager is a DB2 component that manager the physical databases. It invokes other system components, as necessary, to perform detailed functions such as locking, logging, and physical I/O operations (such as search, retrieval, update, and index maintenance).

25. What Is The Function Of Buffer Manager?

The buffer manager is the DB2 component responsible for physically trferring 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).

26. Maxx Number Of Columns In A Db2 Table?

224

27. What Is Pagespace?

Pagespace refers to either to an unpartitioned table, to an index space, or to a single partition of a partitioned table of index space.

28. What Is A Corelated Subquerry?

In a subquerry, if the outer querry reffers back to the outcome of innerquerry it is called corelated subquerry. That’s why the outer querry is evaluated first unlike an ordinary subquerry.

29. What Does Dsndb07 Database Do?

DSNDB07 is where DB2 does its sorting. It includes DB2’s sort work area and external storage.

30. What Should Be Specified Along With A Cursor In Order To Continue Updating Process After Commit?

With Hold option.

31. The Only Place Of Vsam Ksds In Db2 Is?

BSDS is a VSAM KSDS.

32. Can All Users Have The Privilage To Use The Sql Statement Select * (dml)?

NO THE USER SHOULD BE GRANTED PRIVILAGE TO USE IT.

33. Can You Define An Index If The Table Size Less Than 10 Pages?

NO

34. What’s The Best Locksize That You Could Use When You Create A Tablespace?

The wer is Locksize = ANY.Unless you are Sure what’s the Purpose of tablespace ie.,Read-only or R/W.If you use lock size =any, Db2 would automatically determine what type of locks it should use.

35. What Is Meant By Concurrency?

Concurrency is what allows more than one DB2 application process to access the same data at essentially the same time. Problems may occur, such as lost updates, access to uncommitted data, and un-repeatable reads.

36. What Will The Db2 Optimizer Do?

The optimizer is a DB2 component that processes SQL statements and selects the access paths.

37. Describe What A Storage Group(stogroup) Is?

A STOGROUP is a named collection of DASD volumes to be used by tablespaces and index spaces of databases. The volumes of STOGROUP must be of the same device type.

38. What Is Declaration Generator(dclgen)?

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.

39. What Are The Functions Of Bind?

BIND mainly performs two things syntax checking and authorization checking.It binds together all packages into an application plan hence the name BIND.Apart from this bind has optimiser as a subcomponent.Its function is to determine the optimum access strategy.

40. What Is Meant By Repeatable Read?

When an application program executes with repeatable read protection, rows referenced by the program can’t be changed by other programs until the program reaches a commit point.

41. What Is A Resource Control Table(rct)? Describe Its Characteristics.

The RCT is a table that is defined to a DB2/CICS region. It contains control characteristics which are assembled via the DSNCRCT macros. The RCT matches the CICS traction ID to its associated DB2 authorization ID andplan ID(CICS attachment facility).

42. Where Could You Look If You Had A Question About Whether A Column Has Been Defined As An Index?

This information can be found in SYSIBM.SYSINDEXES.

43. Max. No Of Rows Per Page?

127

44. What Is A Host Variable?

This is a data item that is used in an SQL statement to receive a value or to supply a value. It must be preceded by a colon (:) to tell DB2 that the variable is not a column name.

45. Name The Different Types Of Table Spaces?

@Simple Table Space@Segmented Table Space and@Partitioned Table Space

46. What Are The Three Types Of Page Locks That Can Be Held?

Exclusive, update, and share.

47. What Is A Db2 Bind?

Bind is a process that builds “access paths” to DB2 tables. A bind uses the Database Request Modules(s) (DBRM(s)) from the DB2 pre-compile step as input and produces an application plan. It also checks the user’s authority and validates the SQL statements in the DBRM(s).

48. Where Are Pl Stored?

Each plan is defined uniquely in the SYSIBM.SYSPLANS table to correspond to the traction (s) that are to execute that plan.

49. What Is The Maximum Number Of Tables That Can Be Joined ?

fifteen

50. What’s The Error Code For Unique Index Voilation?

-803