1. What is DB2?
DB2 is a database management system for the MVS Operating system where DB2 is a subsystem of the MVS operating system.It is designed to store, analyse and retrieve data efficiently.
2. Which components manage deadlocks in DB2?
Locking services are provided by locking services component known as “Internal Resource Lock Manager” (IRLM) and manages concurrency issues and deadlocks.
3. What is DBRM in DB2 database?
DBRM stands for Database Request Module.DBRM is the component inside DB2, which is created by the precompiler of DB2. DRBM consists of the SQL source statements which get extracted out of the application program.DBRMs form the inputs which are very helpful in the binding process.
4. What is the meaning concurrency in the DB2 database?
Concurrency is the period when more than one DB2 application process can access the same data at the same time.
5. What is the role of Data Manager in the DB2 database?
DB2 database has a component which manages the physical database, and also it requests the system components to perform the operations like locking, logging, etc. and also to perform I/O operations.
6. What is the Clustering Index in the DB2 database?
A clustering index is a type of index, which locates the roes of the table and determines how to group these rows in the tablespace.
7. What is the role of the Data page in the DB2 database?
Data page retrieves the data from the database. The database from which the data can be extracted is in the form of 4 kilobytes or 32 kilobytes. Data page also includes the information regarding the user that is part of the database.
8. 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).
9. 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.
10. How to find the number of rows in DB2 tables?
The user can find the number of rows in the DB2 tables by using the SELECT COUNT (*) on the DB2 query.
11. How can the duplicate values be eliminated from DB2 SELECT?
A user can eliminate the duplicate values from DB2 SELECT by using SELECT DISTINCT in DB2 query.
12. What is the use of DB2 Optimizer?
DB2 Optimizer is used to process the SQL statement. It can be used to enhance the performance of SQL.
13. Which component is used to execute the SQL statements?
Database Services component is used to execute the SQL statement. It also manages buffer pool.
14. Which component is responsible for DB2 startup and shutdown?
System Services component is responsible for handling DB2 startup and shutdown.
15. What is SQLCA?
SQLCA stands for Server Query Language Communication Area. Basically, it is a collection of variables that may be updated after the execution of every SQL statement. A program having SQL executable statement can provide maximum one SQLCA whereas in Java it is not applicable.
16. What is the maximum length of SQLCA?
136 is the maximum length of the SQLCA.
17. What is CHECK constraint in DB2?
Check constraint is a database rule that checks data integrity. Thus, only values from the domain for the attribute or column are allowed.
18. What is buffer Pool?
Buffer pool is part of main memory space. This space is allotted by the database manager. It cache table and index data from the disk.
19. What are the benefits of using the DB2 database?
- DB2 database is straightforward to use
- DB2 has a feature of automatic navigation in which the user only has to write their requirements and rest is done by DB2.
- DB2 us a very much interactive database due to which debugging or testing is done very quickly.
20. What is the role of UNION ALL and UNION?
The UNION command eliminates the duplicates and the UNION ALL command retains the duplicates while combining the results of the different SELECT statements.
21. What types of tables are there in the DB2 database?
There are three types of tables in the DB2 database:-
- Base Tables-These tables hold the persistent data
- Temporary Tables-These tables are used for the temporary work of the database operations
- Materialized Query Tables-These tables are used to increase the performance of queries
22. What is the function of Logging in the DB2 database?
The log files consist of the error logs, which are used to recover from the application errors. The logs keep the record of changes that happened in the database.
23. What are the two types of Logging in the DB2 database? Explain them.
The two types of logging are:-
1. Circular logging: When the user wants to allocate the new transaction log file then in Circular Clogging the old transaction logs are overwritten that means erasing the sequences of old log files and reusing them.
2. Archive logging: This method supports for the Online Backup and database recovery using the log files called roll-forward recovery.
24. What is the role of Schema in the DB2 database?
In a database user cannot create the multiple database objects with the same name. In this case, Schema provides a group which means a user can create various database objects with the same name in different schema groups.
25. What is a trigger in the DB2 database?
A trigger is a set of particular actions that are primarily performed to respond to the operations (INSERT, UPDATE or DELETE) on a specific table in the database. Triggers can be accessed and shared among the multiple applications.
26. What is Alter?
Alter is the SQL command used to change the translation of DB2 objects.
27. What is a collection?
A collection is a user-defined name that works as the anchor for packages but has no physical existence. It is used for the grouping of packages.
28. What is the Declaration Generator ( DCLGEN )?
DCLGEN is a facility that is used to generate SQL statements that describe a table or view. These tables 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.
29. What is a predicate?
A predicate is an element of a search condition that expresses or implies a comparison operation.
30. What is a recovery log?
A recovery log is a collection of records that describes the sequence of events that occur in DB2. The information is needed for recovery in the event of a failure during execution.
31. 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 that are assembled via the DSN CRCT macros. The RCT matches the CICS transaction ID to its associated DB2 authorization ID and plan ID ( CICS attachment facility).
32. 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.
33. Describe what a storage group (STOGROUP) is?
A STOGROUP is a named collection of DASD volumes to be used by table spaces and index spaces of databases. The volumes of STOGROUP must be of the same device type.
34. What is meant by a unit of recovery?
This is a sequence of operations within a unit of work, work done between commit points.
35. Can DB2 be accessed by TSO users? If yes, which command is used to invoke DB2?
DB2 can be invoked by TSO users by using the DSN RUN command.
36. What is a DB2 catalog?
The DB2 catalog is a set of tables that contain information about all of the DB2 objects tables, views, plans, etc .
37. What information is held in SYSIBM.SYSCOPY?
The SYSIBM.SYSCOPY table contains information about image copies made of the tablespaces.
38. What information is contained in an SYSCOPY entry?
Included is the name of the database, the tablespace name, and the image copy type full or incremental, etc. as well as the date and time each copy was made.
39. What information can you find in SYSIBM.SYSLINKS table?
The SYSIBM.SYSLINKS table contains information about the links between tables created by referential constraints.
40. What are the isolation levels possible?
Two isolation levels are possible: One is Cursor Stability and the other is Repeatable Read denoted as CS and RR, respectively.
41. What is the difference between CS and RR isolation levels?
CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of a transaction.
42. What is meant by Lock Escalation?
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.
43. What is meant by PACKAGES?
PACKAGES contain executable codes for SQL statements in respect of one DBRM.
44. What are the advantages of using PACKAGES?
When used, PACKAGES help avoid binding of large numbers of DBRM members in one plan. They also dispense with the cost of large BIND and avoid the entire transactions making them unavailable during BIND and automatic REBIND of the plan. Another advantage is that they minimize the fallback complexities when changes result in an error.
45. What is a collection?
A collection is a user-defined name that works as the anchor for packages but has no physical existence. It is used for the grouping of packages.
46. What is meant by EXPLAIN?
EXPLAIN is basically used to show the path of access by the optimizer basically for an SQL statement. Furthermore, EXPLAIN can also be brought to use in SPUFI or even in BIND step.
47. Before you give the EXPLAIN statement, what are the prerogatives?
Before giving the EXPLAIN statement, we need to make sure that PLAN_TABLE has already been created under AUTHID.
48. How does DB2 store NULL physically?
As an extra-byte prefix to the column value. Physically, the null prefix is Hex ’00” if the value is present and Hex ‘FF’ if it is not.
49. What is RUNSTATS?
A DB2 utility used to collect statistics about the data value in tables which can be used by the optimizer to decide the access path. It also collects statistics used for space management. These statistics are stored in DB2 catalog tables.
50. When will you choose to run RUNSTATS?
After a load, or after mass updates, inserts, deletes, or after REORG.
