 |
Page
1, Page
2, Page
3
|
|
How do you leave the cursor open after
issuing a COMMIT? ( for DB2 2.3 or above only )
|
Use WITH HOLD option in DECLARE
CURSOR statement. But, it has not effect in psuedo-conversational CICS
programs.
|
Give
the COBOL definition of a VARCHAR field.
|
A VARCHAR column REMARKS would
be defined as follows:
10 REMARKS.
49 REMARKS-LEN PIC S9(4) USAGE COMP.
49 REMARKS-TEXT PIC X(1920).
|
What
is the physical storage length of each of the following DB2 data types:
|
DATE, TIME, TIMESTAMP?
DATE: 4bytes
TIME: 3bytes
TIMESTAMP: 10bytes
|
What
is the COBOL picture clause of the following DB2 data types:
|
DATE, TIME, TIMESTAMP?
DATE: PIC X(10)
TIME : PIC X(08)
TIMESTAMP: PIC X(26)
|
What
is the COBOL picture clause for a DB2 column defined as DECIMAL(11,2)?
|
PIC S9(9)V99 COMP-3.
Note: In DECIMAL(11,2), 11 indicates the size of the data type and 2
indicates the precision.
|
What
is DCLGEN ? -
|
DeCLarations GENerator: used to
create the host language copy books for the table definitions. Also
creates the DECLARE table.
|
What
are the contents of a DCLGEN? -
|
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.
|
Is it mandatory to use DCLGEN?
If not, why would you use it at all? -
|
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.
|
Is
DECLARE TABLE in DCLGEN necessary? Why it used?
|
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.
|
Will
precompile of an DB2-COBOL program bomb, if DB2 is down?
|
No. Because the precompiler does
not refer to the DB2 catalogue tables.
|
How
is a typical DB2 batch pgm executed ?
|
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
Use IKJEFT01 utility program to run the above DSN command in a JCL.
|
Assuming
that a site’s standard is that pgm name = plan name, what is the
easiest way to find out which pgms are affected by change in a table’s
structure ?
|
Query the catalogue tables
SYSPLANDEP and SYSPACKDEP.
|
Name
some fields from SQLCA.
|
SQLCODE, SQLERRM, SQLERRD
|
How
can you quickly find out the # of rows updated after an update
statement?
|
Check the value stored in
SQLERRD(3).
|
What
is EXPLAIN?
|
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 ).
|
| |
What
do you need to do before you do EXPLAIN?
|
Make sure that the PLAN_TABLE is
created under the AUTHID.
|
Where
is the output of EXPLAIN stored? –
|
In userid.PLAN_TABLE
|
EXPLAIN
has output with MATCHCOLS = 0. What does it mean? –
|
a nonmatching index scan if
ACCESSTYPE = I.
|
How
do you do the EXPLAIN of a dynamic SQL statement?
|
1. Use SPUFI or QMF to EXPLAIN
the dynamic SQL statement
2. Include EXPLAIN command in the embedded dynamic SQL statements
|
How
do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/QMF?
Give an example with a host variable in WHERE clause.)
|
Use a question mark in place of
a host variable ( or an unknown value ). e.g.
SELECT EMP_NAME
FROM EMP
WHERE EMP_SALARY > ?
|
What
are the isolation levels possible ? –
|
CS: Cursor Stability
RR: Repeatable Read
|
What
is the difference between CS and RR isolation levels?
|
CS: Releases the lock on a page
after use
RR: Retains all locks acquired till end of transaction
|
Where
do you specify them ?
|
ISOLATION LEVEL is a parameter
for the bind process.
|
When
do you specify the isolation level? How?
|
During the BIND process.
ISOLATION ( CS/RR )…
I use CS and update a page. Will the lock be released after I am done
with that page?
No.
|
What
are the various locking levels available?
|
PAGE, TABLE, TABLESPACE
|
How
does DB2 determine what lock-size to use?
|
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
|
What
are the disadvantages of PAGE level lock?
|
High resource utilization if
large updates are to be done
|
What
is lock escalation?
|
Promoting a PAGE lock-size to
table or tablespace lock-size when a transaction has acquired more
locks than specified in NUMLKTS. Locks should be taken on objects in
single tablespace for escalation to occur.
|
What
are the various locks available?
|
SHARE, EXCLUSIVE, UPDATE
|
Can
I use LOCK TABLE on a view?
|
No. To lock a view, take lock on
the underlying tables.
|
What
is ALTER ? –
|
SQL command used to change the
definition of DB2 objects.
|
What
is a DBRM, PLAN ?
|
DBRM: DataBase 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.
|
What
is ACQUIRE/RELEASE in BIND?
|
Determine the point at which DB2
acquires or releases locks against table and tablespaces, including
intent locks.
|
What
else is there in the PLAN apart from the access path? –
|
PLAN has the executable code for
the SQL statements in the host program
|
What
happens to the PLAN if index used by it is dropped?
|
Plan is marked as invalid. The
next time the plan is accessed, it is rebound.
|
What
are PACKAGES ? –
|
They contain executable code for
SQL statements for one DBRM.
|
|
Page
1, Page
2, Page
3
|
|
| |
|
