Explore topic-wise InterviewSolutions in .

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.

Now that we have so much knowledge from the questions that we have studied, LET US now move to practice some of the MCQs that will help test your knowledge too.

• Interview Questions on MainFrame
• Interview Questions on COBOL
• Interview Questions on SQL
• Interview Questions on DBMS
• Interview Questions on Oracle

DSNDB07 is a database where the DB2 performs its sorting operations. It includes the SORT work-area of DB2 and EXTERNAL STORAGE. 

The SELECT statement is not preferred in EMBEDDED SQL programs for 3 reasons:

• If there is a CHANGE in the structure of the table by addition or deletion of a field, the PROGRAM gets modified and using the SELECT statement MIGHT retrieve those columns that the user may not even USE.
• The use of SELECT in the embedded SQL programs can lead t the input-output overhead and,
• The chances of an index-only scan are eliminated by the use of SELECT in embedded SQL programs. 

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:

• After a load.
• After a mass update.
• After any MAJOR DELETIONS, insertions, or
• After REORG the table.

There can be 2 POSSIBILITIES:

• RUN STATS is not done and it seems that the program is using an incorrect/wrong index due to incorrect/wrong stats.
• It MIGHT also be the case that the RUN STATS has been done but the optimizer has CHOSEN a wrong access path based on the latest statistics.

• SPUFI: SPUFI is an EXECUTION ENGINE for SQL where the SQL COMMANDS are executed. Several SQL queries can be executed at once and the result of the queries can be stored in PS or PDS MEMBERS. Also, the SQL is known to us even after the execution of the query.
• QMF: It is a reporting environment and supports the formatting of reports. More than one query cannot be executed and the result of the query cannot be persisted. Also, the SQL code of the query cannot be known. 

In a COBOL-DB2 PROGRAM, all the STATEMENTS are CODED in AREA B.

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.

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.

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.

The MAXIMUM LENGTH of SQLCA is 136.

YES, FORTRAN APPLICATIONS REQUIRE more than one SQLCA.

Only one SQLCA is needed for an application that CONTAINS executable SQL STATEMENTS. HOWEVER, SQLCA is not applicable to Java applications.

YES, more than ONE CURSOR can be OPENED in the same PROGRAM.