Question:- What is a CURSOR and what is its use?
Answer:- A CURSOR is a programming device that helps the SELECT statement find out a set of rows but displays them one at a time. This is because the host language can deal with only one row at a time.
Question:- How to retrieve rows from a DB2 table in Embedded SQL?
Answer:- Rows from a DB2 table in Embedded SQL can be retrieved using the single-row SELECT statement. Another way is by using the CURSOR statement. However, the first process is the preferred one.
Question:- What is the result of the OPEN CURSOR statement?
Answer:- In the simple OPEN CURSOR statement, the CURSOR is placed on the first row of a table. On the other hand, when the ORDER BY clause is added, rows would be fetched, sorted, and would be available for the FETCH statement.
Question:- Can there be more than one cursor open for any program?
Answer:- Yes! The user can have more than one cursor open for any program.
Question:- How would the VARCHAR column REMARKS be defined?
Answer:- The VARCHAR column REMARKS would be defined as follows: • 10 REMARKS • 49 REMARKS-LEN PIC S9(4) USAGE COMP • 49 REMARKS-TEXT PIC X(1920)
Question:- What is the physical storage length of each of the data types, DATE, TIME, and TIMESTAMP?
Answer:- The physical storage length of DATE is 4 bytes, TIME is 3 bytes, and TIMESTAMP is 10 bytes.
Question:- What is meant by DCLGEN?
Answer:- DCLGEN refers to Declaration Generator that is used to create the host language copy books for the table definitions. It also creates the DECLARE table.
Question:- What are the contents of DCLGEN?
Answer:- DCLGEN has the EXEC SQL DECLARE TABLE statement that gives the layout of a table/view in terms of DB2 data types. It can also be defined as the host language copy book giving the host variable definitions for column names.
Question:- What are some of the main fields in SQLCA?
Answer:- Major fields in SQLCA are SQLCODE, SQLERRM, and SQLERRD.
Question:- What is meant by EXPLAIN?
Answer:- EXPLAIN is used for displaying the access path as determined by the optimizer for SQL statements. It can also be used in the case of SPUFI for single SQL statements or in the BIND step for Embedded SQL.
Question:- How to perform EXPLAIN for any Dynamic SQL statement?
Answer:- Users can use SPUFI or QMF statements to perform EXPLAIN for Dynamic SQL statements. They can also include the EXPLAIN command in the Embedded Dynamic SQL statements.
Question:- What are the isolation levels possible?
Answer:- Two isolation levels are possible: One is Cursor Stability and the other is Repeatable Read denoted as CS and RR, respectively.
Question:- What is the difference between CS and RR isolation levels?
Answer:- CS would release the lock on the page after its use. RR would retain all the locks acquired till the end of a transaction.
Question:- What is meant by Lock Escalation?
Answer:- 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.