Explore topic-wise InterviewSolutions in Current Affairs.

DBRM: Data BASE Request MODULE, has the SQL statements extracted from the host language PROGRAM by the pre-compiler.
PLAN: A RESULT of the BIND process. It has the executable code for the SQL statements in the DBRM.

DBRM: Data Base Request Module, has the SQL statements extracted from the host language program by the pre-compiler.
PLAN: A result of the BIND process. It has the executable code for the SQL statements in the DBRM.

SQL COMMAND USED to CHANGE the DEFINITION of DB2 objects.

SQL command used to change the definition of DB2 objects.

1. Based on the lock-size GIVEN while CREATING the tablespace
2. Programmer can direct the DB2 what lock-size to use
3. If lock-size ANY is specified, DB2 usually chooses a lock-size of PAGE

1. Based on the lock-size given while creating the tablespace
2. Programmer can direct the DB2 what lock-size to use
3. If lock-size ANY is specified, DB2 usually chooses a lock-size of PAGE

PAGE, TABLE, TABLESPACE

PAGE, TABLE, TABLESPACE

ISOLATION LEVEL is a PARAMETER for the BIND PROCESS.

ISOLATION LEVEL is a parameter for the bind process.

CS: RELEASES the lock on a page after use
RR: RETAINS all LOCKS acquired till END of transaction

CS: Releases the lock on a page after use
RR: Retains all locks acquired till end of transaction

CS: Cursor Stability
RR: REPEATABLE READ

CS: Cursor Stability
RR: Repeatable Read

1. Use SPUFI or QMF to EXPLAIN the DYNAMIC SQL STATEMENT
2. Include EXPLAIN command in the EMBEDDED dynamic SQL statements

1. Use SPUFI or QMF to EXPLAIN the dynamic SQL statement
2. Include EXPLAIN command in the embedded dynamic SQL statements

A NON MATCHING INDEX SCAN if ACCESSTYPE = I.

A non matching index scan if ACCESSTYPE = I.

In userid.PLAN_TABLE

In userid.PLAN_TABLE

MAKE SURE that the PLAN_TABLE is CREATED under the AUTHID.

Make sure that the PLAN_TABLE is created under the AUTHID.

EXPLAIN is USED to DISPLAY the access path as DETERMINED by the optimizer for a SQL statement. It can be used in SPUFI (for single SQL statement) or in BIND step (for embedded SQL ).

EXPLAIN is used to display the access path as determined by the optimizer for a SQL statement. It can be used in SPUFI (for single SQL statement) or in BIND step (for embedded SQL ).

CHECK the VALUE STORED in SQLERRD(3).

Check the value stored in SQLERRD(3).

SQLCODE, SQLERRM, SQLERRD

SQLCODE, SQLERRM, SQLERRD

1. Use DSN utility to run a DB2 batch program from NATIVE TSO.

An example is SHOWN:
DSN SYSTEM(DSP3)
RUN PROGRAM(EDD470BD) PLAN(EDD470BD) LIB('ED 01T.OBJ.LOADLIB')
END
2. Use IKJEFT01 utility program to run the above DSN COMMAND in a JCL.

1. Use DSN utility to run a DB2 batch program from native TSO.

An example is shown:
DSN SYSTEM(DSP3)
RUN PROGRAM(EDD470BD) PLAN(EDD470BD) LIB('ED 01T.OBJ.LOADLIB')
END
2. Use IKJEFT01 utility program to run the above DSN command in a JCL.

No. Because the PRECOMPILER does not REFER to the DB2 CATALOGUE TABLES.

No. Because the precompiler does not refer to the DB2 catalogue tables.

It not necessary to have DECLARE TABLE STATEMENT in DCLGEN. This is USED by the pre-compiler to validate the table-name, view-name, COLUMN name ETC., during pre-compile.

It not necessary to have DECLARE TABLE statement in DCLGEN. This is used by the pre-compiler to validate the table-name, view-name, column name etc., during pre-compile.

It is not mandatory to use DCLGEN.
USING DCLGEN, helps detect wrongly spelt column names etc. during the pre-compile stage itself ( because of the DECLARE TABLE ). DCLGEN being a tool, would generate accurate HOST variable definitions for the table reducing chances of error.

It is not mandatory to use DCLGEN.
Using DCLGEN, helps detect wrongly spelt column names etc. during the pre-compile stage itself ( because of the DECLARE TABLE ). DCLGEN being a tool, would generate accurate host variable definitions for the table reducing chances of error.

1. EXEC SQL DECLARE TABLE statement which GIVES the layout of the table/view in terms of DB2 datatypes.
2. A host LANGUAGE copy BOOK that gives the host variable definitions for the column names.

Declarations GENerator: USED to create the host LANGUAGE COPY books for the table DEFINITIONS. Also creates the DECLARE table.

Declarations GENerator: used to create the host language copy books for the table definitions. Also creates the DECLARE table.

PIC S9(9)V99 COMP-3.
NOTE: In DECIMAL(11,2), 11 indicates the size of the data TYPE and 2 indicates the PRECISION.

PIC S9(9)V99 COMP-3.
Note: In DECIMAL(11,2), 11 indicates the size of the data type and 2 indicates the precision.

DATE: PIC X(10)
TIME : PIC X(08)
TIMESTAMP: PIC X(26)

DATE: PIC X(10)
TIME : PIC X(08)
TIMESTAMP: PIC X(26)

DATE: 4bytes
TIME: 3bytes
TIMESTAMP: 10BYTES

DATE: 4bytes
TIME: 3bytes
TIMESTAMP: 10bytes

USE WITH HOLD option in DECLARE CURSOR statement. But, it has not effect in psuedo-conversational CICS programs.

Use WITH HOLD option in DECLARE CURSOR statement. But, it has not effect in psuedo-conversational CICS programs.

Yes.

Yes.

Yes.

Yes.

No.

No.

If there is an ORDER BY clause, rows are FETCHED, sorted and made available for the FETCH statement. Other WISE simply the cursor is placed on the first ROW.

If there is an ORDER BY clause, rows are fetched, sorted and made available for the FETCH statement. Other wise simply the cursor is placed on the first row.

USE DECLARE CURSOR statement either in working storage or in procedure division(before OPEN cursor), to SPECIFY the SELECT statement. Then use OPEN, FETCH ROWS in a loop and finally CLOSE.

Use DECLARE CURSOR statement either in working storage or in procedure division(before open cursor), to specify the SELECT statement. Then use OPEN, FETCH rows in a loop and finally CLOSE.

EITHER by USING the single ROW SELECT statements, or by using the CURSOR.

Either by using the single row SELECT statements, or by using the CURSOR.

Cursor is a programming DEVICE that ALLOWS the SELECT to find a set of ROWS but return them one at a time.
Cursor should be used because the host LANGUAGE can DEAL with only one row at a time.

Cursor is a programming device that allows the SELECT to find a set of rows but return them one at a time.
Cursor should be used because the host language can deal with only one row at a time.

A subquery in which the inner ( nested ) QUERY refers back to the table in the OUTER query. Correlated SUBQUERIES MUST be evaluated for each qualified row of the outer query that is REFERRED to.

A subquery in which the inner ( nested ) query refers back to the table in the outer query. Correlated subqueries must be evaluated for each qualified row of the outer query that is referred to.

For THREE reasons:

• If the table STRUCTURE is changed ( a field is ADDED ), the program will have to be modified.
• Program MIGHT retrieve the COLUMNS which it might not use, leading on I/O over head.
• The chance of an index only scan is lost.

For three reasons:

SELECT SUM(SALARY)
FROM EMP
WHERE QUAL=‘MSC’;
NULL

SELECT SUM(SALARY)
FROM EMP
WHERE QUAL=‘MSC’;
NULL

SELECT EMPNO
FROM EMP
WHERE PROJECT IS NULL;

SELECT EMPNO
FROM EMP
WHERE PROJECT IS NULL;

GROUP BY PARTITIONS the selected rows on the distinct values of the column on which you group by.
HAVING selects GROUPs which match the CRITERIA specified

GROUP BY partitions the selected rows on the distinct values of the column on which you group by.
HAVING selects GROUPs which match the criteria specified

MATCH for any SINGLE CHARACTER.

Match for any single character.

To do partial SEARCH e.g. to search employee by name, you NEED not specify the COMPLETE name; using LIKE, you can search for partial STRING matches.

To do partial search e.g. to search employee by name, you need not specify the complete name; using LIKE, you can search for partial string matches.

LIKE is USED for PARTIAL string MATCHES. ‘%’ ( for a string of any character ) and ‘_’ (for any single character ) are the two wild card CHARACTERS.

LIKE is used for partial string matches. ‘%’ ( for a string of any character ) and ‘_’ (for any single character ) are the two wild card characters.

Yes.

Yes.

BETWEEN SUPPLIES a RANGE of VALUES while IN supplies a LIST of values.

BETWEEN supplies a range of values while IN supplies a list of values.

It has to be in a CURSOR.

It has to be in a CURSOR.

UNION : ELIMINATES duplicates
UNION ALL: RETAINS duplicates
Both these are USED to COMBINE the results of different SELECT statements.

UNION : eliminates duplicates
UNION ALL: retains duplicates
Both these are used to combine the results of different SELECT statements.

1. AVOID -ve SQLCODEs by handling nulls and zeroes in computations
2. Substitute a numeric VALUE for any nulls USED in computation

1. Avoid -ve SQLCODEs by handling nulls and zeroes in computations
2. Substitute a numeric value for any nulls used in computation

SELECT FIRSTNAME || ‘ ‘ || LASTNAME FROM EMP;

SELECT FIRSTNAME || ‘ ‘ || LASTNAME FROM EMP;

Because SALARY is not declared to have NULLS and the EMPLOYEES for whom the salary is not known are ALSO counted.

Because SALARY is not declared to have NULLs and the employees for whom the salary is not known are also counted.

YES.

YES.

Built-in MATHEMATICAL FUNCTIONS for USE in SELECT CLAUSE.

Built-in mathematical functions for use in SELECT clause.

SQL QUERY : SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP;

SQL Query : SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP;

USE SELECT MAX(...) .. in DB2 QUERY

Use SELECT MAX(...) .. in db2 query