Wednesday, March 10, 2010

Interview QA- COBOL

POPULAR INTERVIEW QUESTIONS

COBOL & COBOL II

Q1) Name the divisions in a COBOL program ?.

A1) IDENTIFICATION DIVISION, ENVIRONMENT DIVISION, DATA DIVISION, PROCEDURE DIVISION.

Q2) What are the different data types available in COBOL?

A2) Alpha-numeric (X), alphabetic (A) and numeric (9).

Q3) What does the INITIALIZE verb do? - GS

A3) Alphabetic, Alphanumeric fields & alphanumeric edited items are set to SPACES. Numeric, Numeric edited items set to ZERO. FILLER , OCCURS DEPENDING ON items left untouched.

Q4) What is 77 level used for ?
.
subdivided themselves. com

Q5) What is 88 level used for ?
A5) For condition names.
Q6) What is level 66 used for ?
A6) For RENAMES clause.
A4) Elementary level item. Cannot be subdivisions of other items (cannot be qualified), nor can they be

Q7) What does the IS NUMERIC clause establish ?

A7) IS NUMERIC can be used on alphanumeric items, signed numeric & packed decimal items and unsigned numeric & packed decimal items. IS NUMERIC returns TRUE if the item only consists of 0-9. However, if the item being tested is a signed item, then it may contain 0-9, + and - .

Q8) How do you define a table/array in COBOL?

A8) ARRAYS.

05 ARRAY1 PIC X(9) OCCURS 10 TIMES.
05 ARRAY2 PIC X(6) OCCURS 20 TIMES INDEXED BY WS-INDEX.
Q9) Can the OCCURS clause be at the 01 level?
A9) No.

Q10) What is the difference between index and subscript? - GS
A10) Subscript refers to the array occurrence while index is the displacement (in no of bytes) from the
beginning of the
array. An index can only be modified using PERFORM, SEARCH & SET. Need to have index for a
table in order to
use SEARCH, SEARCH ALL.
Q11) What is the difference between SEARCH and SEARCH ALL? - GS
A11) SEARCH - is a serial search.
IBMMAINFRAMESSEARCH ALL - is a binary search & the table must be sorted ( ASCENDING/DESCENDING KEY
clause to be used & data loaded in this order) before using SEARCH ALL.
Q12) What should be the sorting order for SEARCH ALL? - GS
A12) It can be either ASCENDING or DESCENDING. ASCENDING is default. If you want the search
to be done on an




Page 4 of 260 IBMMAINFRAMES.com


array sorted in descending order, then while defining the array, you should give DESCENDING KEY clause. (You

must load the table in the specified order).

Q13) What is binary search?

A13) Search on a sorted array. Compare the item to be searched with the item at the center. If it matches, fine else repeat the process with the left half or the right half depending on where the item lies.

Q14) My program has an array defined to have 10 items. Due to a bug, I find that even if the program access the com

11th item in this array, the program does not abend. What is wrong with it?

A14) Must use compiler option SSRANGE if you want array bounds checking. Default is NOSSRANGE.

Q15) How do you sort in a COBOL program? Give sort file definition, sort statement syntax and meaning. - GS
A15) Syntax: SORT file-1 ON ASCENDING/DESCENDING KEY key.... USING . file-2 GIVING file-3.

IBMMAINFRAMES USING can be substituted by INPUT PROCEDURE IS para-1 THRU para-2 GIVING can be substituted by OUTPUT PROCEDURE IS para-1 THRU para-2.

file-1 is the sort (work) file and must be described using SD entry in FILE SECTION.

file-2 is the input file for the SORT and must be described using an FD entry in FILE SECTION and SELECT

clause in FILE CONTROL.

file-3 is the out file from the SORT and must be described using an FD entry in FILE SECTION and SELECT

clause in FILE CONTROL.

file-1, file-2 & file-3 should not be opened explicitly.

INPUT PROCEDURE is executed before the sort and records must be RELEASEd to the sort work file from the input procedure.

OUTPUT PROCEDURE is executed after all records have been sorted. Records from the sort work file must be RETURNed one at a time to the output procedure.

Q16) How do you define a sort file in JCL that runs the COBOL program?

A16) Use the SORTWK01, SORTWK02,..... dd names in the step. Number of sort datasets depends on the volume of data

being sorted, but a minimum of 3 is required.

Q17) What is the difference between performing a SECTION and a PARAGRAPH? - GS

A17) Performing a SECTION will cause all the paragraphs that are part of the section, to be performed. Performing a PARAGRAPH will cause only that paragraph to be performed.

Q18) What is the use of EVALUATE statement? - GS

A18) Evaluate is like a case statement and can be used to replace nested Ifs. The difference between EVALUATE and

case is that no 'break' is required for EVALUATE i.e. control comes out of the EVALUATE as soon as one match is

made.

Q19) What are the different forms of EVALUATE statement?

A19)

EVALUATE EVALUATE SQLCODE ALSO FILE-STATUS

WHEN A=B AND C=D WHEN 100 ALSO '00'

imperative stmt imperative stmt



Page 5 of 260 IBMMAINFRAMES.com


WHEN (D+X)/Y = 4 WHEN -305 ALSO '32'
imperative stmt imperative stmt
WHEN OTHER WHEN OTHER
imperative stmt imperative stmt
END-EVALUATE END-EVALUATE
EVALUATE SQLCODE ALSO A=B EVALUATE SQLCODE ALSO TRUE
WHEN 100 ALSO TRUE WHEN 100 ALSO A=B
imperative stmt imperative stmt
WHEN -305 ALSO FALSE WHEN -305 ALSO (A/C=4)
imperative stmt imperative stmt
END-EVALUATE END-EVALUATE
Q20) How do you come out of an EVALUATE statement? - GS
A20) After the execution of one of the when clauses, the control is automatically passed on to the next
sentence after the .
EVALUATE statement. There is no need of any extra code.

com
Q21) In an EVALUATE statement, can I give a complex condition on a when clause?
A21) Yes.
Q22) What is a scope terminator? Give examples.
A22) Scope terminator is used to mark the end of a verb e.g. EVALUATE, END-EVALUATE; IF, END-
IF.
Q23) How do you do in-line PERFORM? - GS
A23) PERFORM ... ...

END-PERFORM
Q24) When would you use in-line perform?
A24) When the body of the perform will not be used in other paragraphs. If the body of the perform is a
generic type of code
(used from various other places in the program), it would be better to put the code in a separate Para
and use
PERFORM Para name rather than in-line perform.
Q25) What is the difference between CONTINUE & NEXT SENTENCE ?
A25) They appear to be similar, that is, the control goes to the next sentence in the paragraph. But, Next
Sentence would
take the control to the sentence after it finds a full stop (.). Check out by writing the following code
example, one if
sentence followed by 3 display statements (sorry they appear one line here because of formatting
restrictions) If 1 > 0
then next sentence end if display 'line 1' display 'line 2'. display 'line 3'. *** Note- there is a dot (.)
only at the end of
the last 2 statements, see the effect by replacing Next Sentence with Continue ***
IBMMAINFRAMESQ26) What does EXIT do ?
A26) Does nothing ! If used, must be the only sentence within a paragraph.
Q27) Can I redefine an X(100) field with a field of X(200)?
A27) Yes. Redefines just causes both fields to start at the same location. For example:
01 WS-TOP PIC X(1)



Page 6 of 260 IBMMAINFRAMES.com


01 WS-TOP-RED REDEFINES WS-TOP PIC X(2). If you MOVE '12' to WS-TOP-RED,

DISPLAY WS-TOP will show 1 while DISPLAY WS-TOP-RED will show 12.

A28) Can I redefine an X(200) field with a field of X(100) ?

Q31)1 Yes.

Q31)2What do you do to resolve SOC-7 error? - GS

Q31) Basically you need to correcting the offending data. Many times the reason for SOC7 is an un-
initialized numeric item.
Examine that possibility first. Many installations provide you a dump for run time abend’s ( it can be
generated also
by calling some subroutines or OS services thru assembly language). These dumps provide the
offset of the last
instruction at which the abend occurred. Examine the compilation output XREF listing to get the
verb and the line
number of the source code at this offset. Then you can look at the source code to find the bug. To
.
get capture the com

runtime dumps, you will have to define some datasets (SYSABOUT etc ) in the JCL. If none of
these are helpful, use
judgement and DISPLAY to localize the source of error. Some installation might have batch
program debugging
tools. Use them.
Q32) How is sign stored in Packed Decimal fields and Zoned Decimal fields?
Q32) Packed Decimal fields: Sign is stored as a hex value in the last nibble (4 bits ) of the storage.
Zoned Decimal fields: As a default, sign is over punched with the numeric value stored in the
last bite.

Q33) How is sign stored in a comp-3 field? - GS

Q33) It is stored in the last nibble. For example if your number is +100, it stores hex 0C in the last byte,
hex 1C if
your number is 101, hex 2C if your number is 102, hex 1D if the number is -101, hex 2D if the
number is -102 etc...
Q34) How is sign stored in a COMP field ? - GS
Q34) In the most significant bit. Bit is ON if -ve, OFF if +ve.
Q35) What is the difference between COMP & COMP-3 ?
Q35) COMP is a binary storage format while COMP-3 is packed decimal format.
Q36) What is COMP-1? COMP-2?
Q36) COMP-1 - Single precision floating point. Uses 4 bytes.
COMP-2 - Double precision floating point. Uses 8 bytes.
Q37) How do you define a variable of COMP-1? COMP-2?
Q37) No picture clause to be given. Example 01 WS-VAR USAGE COMP-1.
IBMMAINFRAMES
Q38) How many bytes does a S9(7) COMP-3 field occupy ?
Q38) Will take 4 bytes. Sign is stored as hex value in the last nibble. General formula is INT((n/2) + 1)),
where n=7 in this
example.
Q39) How many bytes does a S9(7) SIGN TRAILING SEPARATE field occupy ?



Page 7 of 260 IBMMAINFRAMES.com


Q39) Will occupy 8 bytes (one extra byte for sign).

Q40) How many bytes will a S9(8) COMP field occupy ?

Q40) 4 bytes.
Q41) What is the maximum value that can be stored in S9(8) COMP?
Q41) 99999999
Q42) What is COMP SYNC?
Q42) Causes the item to be aligned on natural boundaries. Can be SYNCHRONIZED LEFT or RIGHT.
For binary data
items, the address resolution is faster if they are located at word boundaries in the memory. For
example, on main
frame the memory word size is 4 bytes. This means that each word will start from an address
divisible by 4. If my
first variable is x(3) and next one is s9(4) comp, then if you do not specify the SYNC clause, S9(4)
COMP will start
from byte 3 ( assuming that it starts from 0 ). If you specify SYNC, then the binary data item will
.
start from address 4. com

You might see some wastage of memory, but the access to this computational field is faster.
Q43) What is the maximum size of a 01 level item in COBOL I? in COBOL II?
Q43) In COBOL II: 16777215
Q44) How do you reference the following file formats from COBOL programs:
Q44) Fixed Block File - Use ORGANISATION IS SEQUENTIAL. Use RECORDING MODE

IS F, BLOCK CONTAINS 0 .

Fixed Unblocked - Use ORGANISATION IS SEQUENTIAL. Use RECORDING MODE
IS F, do not use BLOCK CONTAINS

Variable Block File - Use ORGANISATION IS SEQUENTIAL. Use RECORDING MODE
IS V, BLOCK CONTAINS 0. Do not code the 4 bytes for record length in FD ie JCL

rec length will be max rec length in pgm + 4
Variable Unblocked - Use ORGANISATION IS SEQUENTIAL. Use RECORDING MODE
IS V, do not use BLOCK CONTAINS. Do not code 4 bytes for record length in FD ie

JCL rec length will
be max rec length in pgm + 4.
ESDS VSAM file - Use ORGANISATION IS SEQUENTIAL.
KSDS VSAM file - Use ORGANISATION IS INDEXED, RECORD KEY IS, ALTERNATE
RECORD KEY IS RRDS File - Use ORGANISATION IS RELATIVE, RELATIVE KEY IS
Printer File - Use ORGANISATION IS SEQUENTIAL. Use RECORDING MODE
IS F, BLOCK CONTAINS 0. (Use RECFM=FBA in JCL DCB).
IBMMAINFRAMES Q45) What are different file OPEN modes available in COBOL?

Q45) Open for INPUT, OUTPUT, I-O, EXTEND.

Q46) What is the mode in which you will OPEN a file for writing? - GS

Q46) OUTPUT, EXTEND

Q47) In the JCL, how do you define the files referred to in a subroutine ?



Page 8 of 260 IBMMAINFRAMES.com


Q47) Supply the DD cards just as you would for files referred to in the main program.

Q48) Can you REWRITE a record in an ESDS file? Can you DELETE a record from it?

Q48) Can rewrite (record length must be same), but not delete.

Q49) What is file status 92? - GS

Q49) Logic error. e.g., a file is opened for input and an attempt is made to write to it.

Q50) What is file status 39 ?

Q50) Mismatch in LRECL or BLOCKSIZE or RECFM between your COBOL pgm com & the JCL (or the dataset label). You

will get file status 39 on an OPEN.

Q51) What is Static and Dynamic linking ?

Q51) In static linking, the called subroutine is link-edited into the calling program , while in dynamic linking, the subroutine & the main program will exist as separate load . modules. You choose

static/dynamic linking by choosing either the DYNAM or NODYNAM link edit option. (Even if you
IBMMAINFRAMES choose NODYNAM, a CALL identifier (as opposed to a CALL literal), will translate to a DYNAMIC call).A statically called subroutine will not be in its initial state the next time it is called unless you

explicitly use INITIAL or you do a CANCEL. A dynamically called routine will always be in its initial state.


Q52) What is AMODE(24), AMODE(31), RMODE(24) and RMODE(ANY)? (applicable to only MVS/ESA

Enterprise Server).

Q52) These are compile/link edit options. Basically AMODE stands for Addressing mode and RMODE for Residency

mode.

AMODE(24) - 24 bit addressing; AMODE(31) - 31 bit addressing

AMODE(ANY) - Either 24 bit or 31 bit addressing depending on RMODE.

RMODE(24) - Resides in virtual storage below 16 Meg line. Use this for 31 bit programs that call 24 bit programs.

(OS/VS Cobol pgms use 24 bit addresses only). RMODE(ANY) - Can reside above or below 16 Meg line.

Q53) What compiler option would you use for dynamic linking?

Q53) DYNAM.

Q54) What is SSRANGE, NOSSRANGE ?

Q54) These are compiler options with respect to subscript out of range checking. NOSSRANGE is the default and if chosen,

no run time error will be flagged if your index or subscript goes out of the permissible range.

Q55) How do you set a return code to the JCL from a COBOL program?

Q55) Move a value to RETURN-CODE register. RETURN-CODE should not be declared in your program.

Q56) How can you submit a job from COBOL programs?

Q56) Write JCL cards to a dataset with //xxxxxxx SYSOUT= (A,INTRDR) where 'A' is output class, and dataset should be

opened for output in the program. Define a 80 byte record layout for the file.

Q57) What are the differences between OS VS COBOL and VS COBOL II?



Page 9 of 260 IBMMAINFRAMES.com


Q57) OS/VS Cobol pgms can only run in 24 bit addressing mode, VS Cobol II pgms can run either in 24 bit or 31 bit

addressing modes.

I. Report writer is supported only in OS/VS Cobol.

II. USAGE IS POINTER is supported only in VS COBOL II.

III. Reference modification e.g.: WS-VAR(1:2) is supported only in VS COBOL II. IV. EVALUATE is supported only in VS COBOL II.

V. Scope terminators are supported only in VS COBOL II.

VI. OS/VS Cobol follows ANSI 74 stds while VS COBOL II follows ANSI 85 stds. VII. Under CICS Calls between VS COBOL II programs are supported.

Q58) What are the steps you go through while creating a COBOL program executable?

Q58) DB2 precompiler (if embedded SQL used), CICS translator (if CICS pgm), Cobol compiler, Link
editor. If DB2 .
program, create plan by binding the DBRMs.
Q59) Can you call an OS VS COBOL pgm from a VS COBOL II pgm ?

Q59) In non-CICS environment, it is possible. In CICS, this is not possible. com

Q60) What are the differences between COBOL and COBOL II?
A60) There are at least five differences:
COBOL II supports structured programming by using in line Performs and explicit scope
terminators, It introduces
new features (EVALUATE, SET. TO TRUE, CALL. BY CONTEXT, etc) It permits programs to be
loaded and
addressed above the 16-megabyte line It does not support many old features (READY TRACE,
REPORT-WRITER,
ISAM, Etc.), and It offers enhanced CICS support.

Q61) What is an explicit scope terminator?
A61) A scope terminator brackets its preceding verb, e.g. IF .. END-IF, so that all statements between the
verb and its scope terminator are grouped together. Other common COBOL II verbs are READ,
PERFORM, EVALUATE, SEARCH and STRING.
Q62) What is an in line PERFORM? When would you use it? Anything else to say about it?
A62) The PERFORM and END-PERFORM statements bracket all COBOL II statements between them.
The COBOL equivalent is to PERFORM or PERFORM THRU a paragraph. In line PERFORMs
work as long as there are no internal GO TOs, not even to an exit. The in line PERFORM for
readability should not exceed a page length - often it will reference other PERFORM paragraphs.
Q63) What is the difference between NEXT SENTENCE and CONTINUE?
A63) NEXT SENTENCE gives control to the verb following the next period. CONTINUE gives control
to the next verb after the explicit scope terminator. (This is not one of COBOL II's finer
implementations). It's safest to use CONTINUE rather than NEXT SENTENCE in COBOL II.
Q64) What COBOL construct is the COBOL II EVALUATE meant to replace?
A64) EVALUATE can be used in place of the nested IF THEN ELSE statements.
IBMMAINFRAMES
Q65) What is the significance of 'above the line' and 'below the line'?
A65) Before IBM introduced MVS/XA architecture in the 1980's a program's virtual storage was limited to
16 megs. Programs compiled with a 24 bit mode can only address 16 Mb of space, as though they
were kept under an imaginary storage line. With COBOL II a program compiled with a 31 bit mode
can be 'above the 16 Mb line. (This 'below the line', 'above the line' imagery confuses most
mainframe programmers, who tend to be a literal minded group.)



Page 10 of 260 IBMMAINFRAMES.com


Q66) What was removed from COBOL in the COBOL II implementation?
A66) Partial list: REMARKS, NOMINAL KEY, PAGE-COUNTER, CURRENT-DAY, TIME-OF-DAY,
STATE, FLOW, COUNT, EXAMINE, EXHIBIT, READY TRACE and RESET TRACE.
Q67) Explain call by context by comparing it to other calls.
A67) The parameters passed in a call by context are protected from modification by the called program. In
a normal call they are able to be modified.
Q68) What is the linkage section?
A68) The linkage section is part of a called program that 'links' or maps to data items in the calling
program's working storage. It is the part of the called program where these share items are defined.
Q69) What is the difference between a subscript and an index in a table definition?
A69) A subscript is a working storage data definition item, typically a PIC (999) where a value must be
moved to the subscript and then incremented or decrements by ADD TO and SUBTRACT FROM
statements. An index is a register item that exists outside the program's working storage. You SET
an index to a value and SET it UP BY value and DOWN BY value. .

com
Q70) If you were passing a table via linkage, which is preferable - a subscript or an index?
A70) Wake up - you haven't been paying attention! It's not possible to pass an index via linkage. The
index is not part of the calling programs working storage. Those of us who've made this mistake,
appreciate the lesson more than others.
Q71) Explain the difference between an internal and an external sort, the pros and cons, internal
sort syntax etc.
A71) An external sort is not COBOL; it is performed through JCL and PGM=SORT. It is understandable
without any code reference. An internal sort can use two different syntax’s: 1.) USING, GIVING
sorts are comparable to external sorts with no extra file processing; 2) INPUT PROCEDURE,
OUTPUT PROCEDURE sorts allow for data manipulation before and/or after the sort.
Q72) What is the difference between comp and comp-3 usage? Explain other COBOL usage’s.
A72) Comp is a binary usage, while comp-3 indicates packed decimal. The other common usage’s are
binary and display. Display is the default.
Q73) When is a scope terminator mandatory?
A73) Scope terminators are mandatory for in-line PERFORMS and EVALUATE statements. For
readability, it's recommended coding practice to always make scope terminators explicit.
Q74) In a COBOL II PERFORM statement, when is the conditional tested, before or after the
perform execution?
A74) In COBOL II the optional clause WITH TEST BEFORE or WITH TEST AFTER can be added to
all perform statements. By default the test is performed before the perform.
Q75) In an EVALUTE statement is the order of the WHEN clauses significant?
A75) Absolutely. Evaluation of the WHEN clauses proceeds from top to bottom and their sequence can
determine results.
IBMMAINFRAMESQ76) What is the default value(s) for an INITIALIZE and what keyword allows for an override of
the default.
A76) INITIALIZE moves spaces to alphabetic fields and zeros to alphanumeric fields. The REPLACING
option can be used to override these defaults.
Q77) What is SET TO TRUE all about, anyway?



Page 11 of 260 IBMMAINFRAMES.com


A77) In COBOL II the 88 levels can be set rather than moving their associated values to the related data
item. (Web note: This change is not one of COBOL II's better specifications.)
Q78) What is LENGTH in COBOL II?
A78) LENGTH acts like a special register to tell the length of a group or elementary item.
Q79) What is the difference between a binary search and a sequential search? What are the
pertinent COBOL
commands?
A79) In a binary search the table element key values must be in ascending or descending sequence. The
table is 'halved' to search for equal to, greater than or less than conditions until the element is found.
In a sequential search the table is searched from top to bottom, so (ironically) the elements do not
have to be in a specific sequence. The binary search is much faster for larger tables, while sequential
works well with smaller ones. SEARCH ALL is used for binary searches; SEARCH for sequential.
Q80) What is the point of the REPLACING option of a copy statement?
A80) REPLACING allows for the same copy to be used more than once in the same code by changing the
replace value. .

com
Q81) What will happen if you code GO BACK instead of STOP RUN in a stand alone COBOL
program i.e. a
program which is not calling any other program.
A81) The program will go in an infinite loop.
Q82) How can I tell if a module is being called DYNAMICALLY or STATICALLY?
A82) The ONLY way is to look at the output of the linkage editor (IEWL)or the load module itself. If the
module is being called DYNAMICALLY then it will not exist in the main module, if it is being
called STATICALLY then it will be seen in the load module. Calling a working storage variable,
containing a program name, does not make a DYNAMIC call. This type of calling is known as
IMPLICITE calling as the name of the module is implied by the contents of the working storage
variable. Calling a program name literal (CALL
Q83) What is the difference between a DYNAMIC and STATIC call in COBOL.
A83) To correct an earlier answer: All called modules cannot run standalone if they require program
variables passed to them via the LINKAGE section. DYNAMICally called modules are those that are
not bound with the calling program at link edit time (IEWL for IBM) and so are loaded from the
program library (joblib or steplib) associated with the job. For DYNAMIC calling of a module the
DYNAM compiler option must be chosen, else the linkage editor will not generate an executable as
it will expect u address resolution of all called modules. A STATICally called module is one that is
bound with the calling module at link edit, and therefore becomes part of the executable load module.
Q84) How may divisions are there in JCL-COBOL?
A84) SIX
Q85) What is the purpose of Identification Division?
A85) Documentation.
Q86) What is the difference between PIC 9.99 and 9v99?
IBMMAINFRAMESA86) PIC 9.99 is a FOUR-POSITION field that actually contains a decimal point where as PIC 9v99 is
THREE- POSITION numeric field with implied or assumed decimal position.
Q87) what is Pic 9v99 Indicates?
A87) PICTURE 9v99 is a three position Numeric field with an implied or assumed decimal point after the
first position; the v means an implied decimal point.




Page 12 of 260 IBMMAINFRAMES.com

Q88) What guidelines should be followed to write a structured Cobol prg'm?

A88)

1) use 'evaluate' stmt for constructing cases.

2) use scope terminators for nesting.

3) use in line perform stmt for writing 'do ' constructions.

4) use test before and test after in the perform stmt for writing do-while constructions.

Q89) Read the following code. 01 ws-n pic 9(2) value zero. a-para move 5 to ws-n. perform b-para
ws-n times. b-para.
move 10 to ws-n. how many times will b-para be executed ?
A89) 5 times only. it will not take the value 10 that is initialized in the loop.
Q90) What is the difference between SEARCH and SEARCH ALL? What is more efficient?
A90) SEARCH is a sequential search from the beginning of the table. SEARCH ALL is a binary search,
continually dividing the table in two halves until a match is found. SEARCH ALL is more efficient
for tables larger than 70 items. .
Q91) What are some examples of command terminators?

A91) END-IF, END-EVALUATE com

Q92) What care has to be taken to force program to execute above 16 Meg line?
A92) Make sure that link option is AMODE=31 and RMODE=ANY. Compile option should never have
SIZE(MAX). BUFSIZE can be 2K, efficient enough.
Q93) How do you submit JCL via a Cobol program?
A93) Use a file //dd1 DD sysout=(*, intrdr)write your JCL to this file. Pl some on try this out.
Q94) How to execute a set of JCL statements from a COBOL program
A94) Using EXEC CICS SPOOL WRITE(var-name) END-EXEC command. var-name is a COBOL host
structure containing JCL statements.
Q95) Give some advantages of REDEFINES clause.
A95) 1. You can REDEFINE a Variable from one PICTURE class to another PICTURE class by using

the same memory
location.
2. By REDEFINES we can INITIALISE the variable in WORKING-STORAGE Section itself.
3. We can REDEFINE a Single Variable into so many sub variables. (This facility is very useful in
solving Y2000
Problem.)
Q96) What is the difference between static call & Dynamic call
A96) In the case of Static call, the called program is a stand-alone program, it is an executable program.
During run time we can call it in our called program. As about Dynamic call, the called program is
not an executable program it can executed through the called program
Q97) What do you feel makes a good program?
A97) A program that follows a top down approach. It is also one that other programmers or users can
follow logically and is easy to read and understand.
IBMMAINFRAMES
Q98) How do you code Cobol to access a parameter that has been defined in JCL? And do you code
the PARM
A98) parameter on the EXEC line in JCL?

1) using JCL with sysin. //sysin dd *here u code the parameters(value) to pass in to cobol program /* and in program



Page 13 of 260 IBMMAINFRAMES.com


you use accept variable name(one accept will read one row)/.another way.

2) in jcl using parm statement ex: in exec statement parm='john','david' in cobol pgm u have to code linkage section in that for first value you code length variable and variable name say, abc pic x(4).it will take john inside to read next value u have to code another variable in the same way above mentioned.

Q99) Why do we code S9(4) comp. Inspite of knowing comp-3 will occupy less space.

A99) Here s9(4)comp is small integer ,so two words equal to 1 byte so totally it will occupy 2 bytes(4
words).here in s9(4) comp-3 as one word is equal to 1/2 byte.4 words equal to 2 bytes and sign will
occupy 1/2 byte so totally it will occupy 3 bytes.
Q100) The maximum number of dimensions that an array can have in COBOL-85 is ----------- ?
A100) SEVEN in COBOL - 85 and THREE in COBOL - 84
Q101) How do you declare a host variable (in COBOL) for an attribute named Emp-Name of type
VARCHAR(25) ? .
A101) 01 EMP-GRP.


49 E-LEN PIC S9(4) COMP. com

49 E-NAME PIC X(25).
Q102) What is Comm?
A102) COMM - HALF WORD BINARY
Q103) Differentiate COBOL and COBOL-II. (Most of our programs are written in COBOLII, so,
it is good to know,
how, this is different from COBOL)
A103) The following features are available with VS COBOL II:
1. MVS/XA and MVS/ESA support The compiler and the object programs it produces can be
run in either
24- or 31-bit addressing mode.
2. VM/XA and VM/ESA support The compiler and the object programs it produces can be run
in either
24- or 31-bit addressing mode.
3. VSE/ESA support The compiler and the object programs it produces can be run under
VSE/ESA.
Q104) What is PERFORM ? What is VARYING ? (More details about these clauses)
A104) The PERFORM statement is a PROCEDURE DIVISION statement which transfers control to
one or more specified procedures and controls as specified the number of times the procedures are
executed. After execution of the specified procedures is completed (i.e., for the appropriate
number of times or until some specified condition is met), control is transferred to the next
executable statement following the PERFORM statement. There are 5 types of PERFORM
statements:
IBMMAINFRAMES
a) Basic PERFORM
b) PERFORM TIMES
c) PERFORM UNTIL
d) PERFORM VARYING
e) IN-LINE PERFORM

Q105) How many sections are there in data division?.

A105) SIX SECTIONS 1.FILE SECTION 2.WORKING-STORAGE SECTION 3. LOCAL-STORAGE SECTION 4.SCREEN SECTION 5.REPORT SECTION 6. LINKAGE SECTION




Page 14 of 260 IBMMAINFRAMES.com

Q106) What is Redefines clause?

A106) Redefines clause is used to allow the same storage allocation to be referenced by different data names .

Q107) How many bytes does a s9(4)comp-3 field occupy?
A107) 3Bytes (formula : n/2 + 1))
Q108) What is the different between index and subscript?
A108) Subscript refers to the array of occurrence , where as Index represents an occurrence of a table
element. An index can only modified using perform, search & set. Need to have an index for a
table in order to use SEARCH and SEARCH All.
Q109) What is the difference between Structured COBOL Programming and Object Oriented
COBOL
programming?
A109) Structured programming is a Logical way of programming, you divide the functionalities into
modules and code logically. OOP is a Natural way of programming; you identify the objects first,
and then write functions, procedures around the objects. Sorry, this may not be an adequate
.
com
answer, but they are two different programming paradigms, which is difficult to put in a sentence
or two.
Q110) What divisions, sections and paragraphs are mandatory for a COBOL program?
A110) IDENTIFICATION DIVISION and PROGRAM-ID paragraph are mandatory for a compilation
error free COBOL
program.
Q111) Can JUSTIFIED be used for all the data types?
A111) No, it can be used only with alphabetic and alphanumeric data types.
Q112) What happens when we move a comp-3 field to an edited (say z (9). ZZ-)
A112) the editing characters r to be used with data items with usage clause as display which is the
default. When u tries displaying a data item with usage as computational it does not give the
desired display format because the data item is stored as packed decimal. So if u want this
particular data item to be edited u have to move it into a data item whose usage is display and then
have that particular data item edited in the format desired.
Q113) What will happen if you code GO BACK instead of STOP RUN in a stand-alone COBOL
program i.e. a program which is not calling any other program ?
A113) Both give the same results when a program is not calling any other program. GO BACK will give
the control to the system even though it is a single program.
Q114) what is the difference between external and global variables?
A114) Global variables are accessible only to the batch program whereas external variables can be
referenced from any batch program residing in the same system library.
Q115) You are writing report program with 4 levels of totals: city, state, region and country. The
codes being used can be the same over the different levels, meaning a city code of 01 can be
in any number of states, and the same applies to state and region code so how do you do your
IBMMAINFRAMESchecking for breaks and how do you do add to each level?
A115) Always compare on the highest-level first, because if you have a break at a highest level, each level
beneath it must also break. Add to the lowest level for each record but add to the higher level only
on a break.
Q116) What is difference between COBOL and VS COBOL II?.




Page 15 of 260 IBMMAINFRAMES.com


A116) In using COBOL on PC we have only flat files and the programs can access only limited storage,
whereas in VS COBOL II on M/F the programs can access up to 16MB or 2GB depending on the
addressing and can use VSAM
files to make I/O operations faster.
Q117) Why occurs can not be used in 01 level ?
A117) Because, Occurs clause is there to repeat fields with same format, not the records.
Q118) What is report-item?
A118) A Report-Item Is A Field To Be Printed That Contains Edit Symbols
Q119) Difference between next and continue clause
A119) The difference between the next and continue verb is that in the continue verb it is used for a
situation where there in no EOF condition that is the records are to be accessed again and again in
an file, whereas in the next verb the indexed file is accessed sequentially, read next record
command is used.
Q120) What is the Importance of GLOBAL clause According to new standards of COBOL
.
com
A120) When any data name, file-name, Record-name, condition name or Index defined in an Including
Program can be referenced by a directly or indirectly in an included program, Provided the said
name has been declared to be a global name by GLOBAL Format of Global Clause is01 data-1 pic
9(5) IS GLOBAL.
Q121) What is the Purpose of POINTER Phrase in STRING command
A121) The Purpose of POINTER phrase is to specify the leftmost position within receiving field where
the first transferred character will be stored
Q122) How do we get current date from system with century?
A122) By using Intrinsic function, FUNCTION CURRENT-DATE
Q123) What is the maximum length of a field you can define using COMP-3?
A123) 10 Bytes (S9(18) COMP-3).
Q124) Why do we code s9 (4) comp? In spite of knowing comp-3 will occupy less space?
A124) Here s9(4)comp is small integer, so two words equal to 1 byte so totally it will occupy 2 bytes(4
words).here in s9(4) comp-3 as one word is equal to 1/2 byte.4 words equal to 2 bytes and sign will
occupy 1/2 byte so totally it will occupy 3 bytes.
Q125) What is the LINKAGE SECTION used for?
A125) The linkage section is used to pass data from one program to another program or to pass data from
a PROC to a program.
Q126) Describe the difference between subscripting and indexing ?
A126) Indexing uses binary displacement. Subscripts use the value of the occurrence.

1. What R 2 of the common forms of the EVALUATE STATEMENT ?
2. What does the initialize statement do ?
3. What is the reference modification.
IBMMAINFRAMES4. Name some of the examples of COBOl 11?
5. What are VS COBOL 11 special features?
6. What are options have been removed in COBOL 11?
7. What is the file organization clause ?
8. What is a subscript ?
9. What is an index for tables?
10. What are the two search techniques ?



Page 16 of 260 IBMMAINFRAMES.com


11. What is an in-line perform ?

12. What is CALL statement in COBOL?

13. When can the USING phrase be included in the call statement ?

14. In EBCDIC, how would the number 1234 be stored?

15. How would the number +1234 be stored if a PIC clause of PICTUREs9(4) comp-3 were used?

16. What is Alternate Index ? How is it different from regular index ?

Friday, January 29, 2010

DB2

DB2 Tutorial :-

Introduction to Databases

What is data?

A representation of facts or instruction in a form suitable for communication.

What is a database?

It is a repository for stored data.

What is a database System?

An integrated and shared repository for stored data or collection of stored operational data used by application systems of some particular enterprise.

Nothing more than a computer-based record keeping system

Advantages of DBMS over File Management Systems

Data redundancy

Multiple Views

Shared data

Data independence (logical/Physical)

Data dictionary

Search versatility

Cost effective

Security and Control

Recovery, restart & Backup

Concurrency

Types of Databases ( or Models)

Hierarchical Model

Network Model

Relational Model

Object-Oriented Model

HIERARCHICAL MODEL

Top down structure resembling an upside-down tree.

Parent child relationship

First logical database Model

Available on most of the Mainframe computers

Examples: IMS

NETWORK MODEL

Does not distinguish between parent and child. Any record type can be associated with any number of arbitrary record types Enhanced to overcome limitations of other models but in reality there is minimal difference due to frequent enhancement

Example: IDMS

RELATIONAL MODEL

Data stored in the form of table consists of multiple rows and columns.

Examples: DB2,ORACLE, SYBASE

OBJECT-ORIENTED MODEL

Data attributes and methods that operate on those attributes are encapsulated instructions called objects.

Types of Integrity

Entity Integrity

Referential Integrity

Domain Integrity

Entity Integrity

Is a state where no column that is part of a primary key can have a null values.

Referential Integrity

Is a state where every foreign key in the first table must either match a primary key value in the second table or must be wholly null.

Domain Integrity

Integrating of information allowed in column.

Entity Relationship Model

E-R model is a logical representation of data for a business area.

Represented as entities relationship between entities and attributes of both relationships and entities.

E-R models are outputs of analysis phase.

Example of relational Structure
CUSTOMER places ORDERS
ORDER has PRODUCTS

Each order relates to only one customer (one-to-one)

Many orders can contain many products (many-to-many)

A customer can place any number of orders ( one-to-many)

In last example customer,order & product are called entities

An entities may transform into tables.

The unique identity for information stored in an entity is called a primary key

Attributes which define the characteristics of the table.

DB2

OBJECTS

Stogroup(Storage group)

Database

Table Space

Table

View

Index

Stogroup ( Storage Group)

It is a collection of direct access volume, all of the same device type

The option is defined as a part of table space definitions

When a given space needs to be extended, storage is acquired from the appropriate stogroup.

Database

A collection of logically related objects – like table spaces, index spaces, tables etc.

Not a physical kind of object, may occupy more than one disk space.

A STOGROUP & BUFFER POOL must be defined for each database.

In a given database, all the spaces need not have the same STOGROUP.

TABLE SPACES

Logical address space on secondary storage to hold one or more tables.

It is the storage unit for recovery and reorganizing purpose

Three types of table spaces are :

Simple

Partitioned

Segmented

Simple Table Spaces

Can contain more than one stored tables

Depending on application, storing more than one tables might enable faster retrieval for joins using these tables.

Usually only one table is preferred. This is because a single page can contain rows from all tables defined in the database.

Segmented table spaces

Can contain more than one stored tables, but in a segmented space.

A ‘segment’ consists of a logically contiguous set of ‘n’ pages.

No segment is allowed to contain records for more than one table.

Sequential access to a particular table is more efficient.

Lock Table on table locks only the table, not the entire table space.

If a table is dropped, the space for that table can be reclaimed within minimum reorganization.

Partitioned Table Space

Primarily used for very large tables

Only one table in a partitioned table space

Individual partitions can be independently recovered and reorganized.

Different partitions can be stored on different storage groups for efficient access.

TABLES

A table is a collection of rows and columns.

The data is stored on magnetic disks in a series of TABLES.

Each COLUMN contains some specific information about suppliers and each ROW contains all the information about a particular supplier.

For example SUPPLIER table looks like :

S#

SNAME

STATUS

CITY

001

PRASAD

20

CHENNAI

002

VASU

30

DELHI

003

SIVA

10

BOMBAY

VIEWS

Views can be very practical ways of simplifying queries by reducing the number of different tables.

Views can also hide sensitive data from users who don’t need access to it.

CREATE VIEW EMP_VIEW
AS
SELECT EMPNO,NAME,DEPT,JOB FROM EMP

SYNONYMS

Synonym is like a nick name to a table name and when no longer needed it can be dropped. Synonym access is specific to the user who has created it.

STRUCTURED QUERY LANGUAGE(SQL)

A powerful database management language that performs the function of data manipulation, data definition and data control.

A non-procedural language.

The capability to act on a set of data and the lack of need to know how to retrieve it.

It allows to specify WHAT the result should be, NOT HOW the result obtained.

SQL TYPES(Based on functionality)

DDL - CREATE, ALTER and DROP

DML - SELECT,INSERT, UPDATE & DELETE

DCL - GRANT and REVOKE

SQL TYPES(Others)

Static or Dynamic SQL

Embedded or Stand-alone SQL

HOW DO YOU UTILIZE SQL?

It is usually accessed on-line.

It can be used interactively, such as SPUFI,QMF or it can be embedded in COBOL,PL/I,ASSEMBLER or FORTRAN program.

WHAT MIGHT YOU DO WITH SQL?

As an end-user you will be using SQL on-line to access data and produce formatted reports.

As an application programmer, you will use SQL to extract data from one or more tables and pass these data to another program for additional processing.

SQL task is center about four basic functions & these functions correspond to the four basic SQL command (SELECT, UPDATE, INSERT, DELETE)

HOW TO CREATE A SIMPLE SQL QUERY?

Search the table of your choice

Select specific columns to be displayed from a table

Select specific rows to be displayed from a table

Specify the order in which to display data

How do you specify which tables you want to select from?

Which tables contain data?

Which columns to display from the table?

Which rows to display?

In which order to display the columns and/or rows.

Any SELECT statement must specify a table

If the table belongs to another user, you may have to prefix the table name with the other user’s ID.

How do you specify which COLUMNS you want to display?

Listing the name of the columns on a SELECT statement, separated by commas.

SELECT

SNAME, STATUS

FROM

SUPPLIERS

Why not use the ‘*’ in all your queries?

Using the ‘*’ to indicate all columns should be displayed is inefficient if you don’t need to see all the columns.

You can put the columns in the order you want them.

It is better to specify just those column you want.

How do you specify which rows you want to display?

Using WHERE keyword.

The format is WHERE followed by a condition or list of conditions.

SELECT

SNAME

FROM

SUPPLIER

WHERE

STATUS=30

Can you put the data in Order?

Using ORDER BY keyword to your query

SQL automatically orders data in ascending order if you have any ORDER BY statement in your query.

If you want your data in descending order, use the DESC keyword.
SELECT STATUS FROM SUPPLIERS ORDER BY STATUS DESC

Can you order by more than one column?

You can order by as many columns as you want.

Simple add the columns you want to sort to the ORDER BY clause, separated by comma.

Example:
SELECT SNAME ,STATUS, CITY FROM SUPPLIERS ORDER BY STATUS,SNAME

Use of ‘AND’ and ‘OR’ to make a query more specific.

Sometimes a single search condition is not precise enough to select only the rows you need. So you need to use the logical operator AND and OR.

You can link as many search expression as you want using AND and OR.

Using both operator together, searched expression linked by AND are paired up first. Parentheses can be used to force expression to be paired together.

How do you avoid duplicate data in your output?

By adding the keyword DISTINCT immediately following SELECT , you suppress any duplicates. SELECT DISTINCT STATUS FROM SUPPLIERS

What are the relational operators?

Here is the full list of operators:

SYMBOL

CONDITION

=

equal to

>

greater than

<

less than

>=

greater than or equal to

<=

lesser than or equal to

<>

less than or greater than

LIKE

containing certain characters

BETWEEN

within a range of values

IN

one of a set of values

Each condition also has a negative version.

IN

Lets you retrieve data from each row whose columns has a value equal to one of the several listed values.

Example: WHERE STATUS IN (20,10,30)

Any row with STATUS column value of 20,10, 30 is selected.

Each character value must be in quotes.

Values are separated by comma & values list is enclosed in parenthesis.

BETWEEN

  • Lets you retrieve data from each row whose columns has a value with two limits.
  • Example: WHERE STATUS BETWEEN 10 AND 30
  • Any row with STATUS column value of 10 thru 30 is selected.
  • It can be used for both numeric and character values.
  • The two values must be separated by AND, the lower value must be the first value.

LIKE

  • Lets you retrieve data from each row whose columns has a value similar to the value specified in the WHERE CLAUSE.
  • Example: WHERE SNAME LIKE ‘B%C’
  • Any row with name column beginning with ‘B’ followed by ANY VALUE of ANY LENGTH, and ending with ‘C’ is selected.
  • A ‘%’ represents any value of any length, where as an underscore ‘_’ represents any single character LIKE can’t use for any numeric column.

What is NULL?

  • A NULL is a column value that does not exists.
  • It is not a blank, not is it zero – it is a ‘non-value’.
  • The columns must predefined to the DBMS as a column that may contain NULL values.

HOW DOES SQL TREAT NULL VALUES?

  • Generally, NULL values are ignored.
  • If you perform a calculation using a column with NULL values, those rows containing NULL's are ignored and a dash will output as the result.
  • NULL means “VALUE UNKNOWN”, so it cannot meet any of the standard search conditions.

Example of NULL values

  • To select all rows with a NULL value in the COMM column, you must use WHERE COMM IS NULL as your search condition
  • To select all rows that do NOT have a NULL value, you must use WHERE COMM IS NOT NULL as your search condition
  • Note:

WHERE COMM = 0 …

Who has earned no commission?

WHERE COMM IS NULL

Who is ineligible for commission?

ARITHMETRIC EXPRESSIONS

  • An arithmetic expression is a calculation using numeric values and one or more of the arithmetic operators :addition (+), subtraction (-), multiplication (*), and division (/).
  • Example: SELECT SALARY + COMM,NAME,DEPT
  • Arithmetic expressions can also be used in a WHERE clause: WHERE SALARY + COMM >5000.00

RULES FOR CODING ARITHMETIC EXPRESSIONS

  • Spaces on either side of the arithmetic symbols are optional.
    SALARY/12 OR SALARY / 12
  • You can combine column names and constants in arithmetic expressions.
    1998 - JOIN_YR, 1.0*SALARY

HOW DOES SQL EVALUATE ARITHMETIC EXPRESSIONS?

  • SQL works from left to right, evaluating multiplication and division before addition and subtraction (*, / , +, -).
  • However any expressions in parentheses are evaluated first.

BUILT IN FUNCTION

  • SUM
  • MAX
  • MIN
  • AVG
  • COUNT(*)
  • COUNT(DISTINCT col name)

SUM

  • Calculate the total value for a given column or arithmetic expression
    SELECT SUM(SALARY) FROM EMP

MAX and MIN

  • Display either the highest or lowest value found in the rows selected by the query. SELECT MAX(SALARY),MIN(SALARY) FROM EMP

AVG

  • Display the average of all non-NULL columns
    SELECT AVG(SALARY) FROM EMP
  • AVG disregards any rows with NULLs in any column involved in the averaging expression
  • Integer columns are not rounded, so result may be inaccurate.
  • To convert an integer column to a decimal column multiply by 1.0 as part of the AVG expression.
    AVG(1.0 * STATUS)

COUNT(*)

  • It counts all the rows selected by a query
    SELECT COUNT(*) FROM SUPPLIERS

COUNT(DISTINCT col name)

  • It counts only those rows that have a unique value in the specified column
    SELECT COUNT(DISTINCT STATUS) FROM SUPPLIERS
  • COUNT (*) counts all the rows selected
  • COUNT(DISTINCT col name) does not count rows with a duplicate value in the specified column.
  • COUNT(DISTINCT col_name) does not count rows that have NULLS in the specified column.

HOW DOES SQL CREATES SUMMARY DATA

  • When you request a built-in summary functions, SQL goes through several steps to reach a result.
  • First, all the rows and columns are selected. This intermediate result is kept in a temporary table.
  • Next, the system process your GROUP BY request, creating a temporary table for each GROUP and putting the appropriate rows in each table.
  • Then the built-in functions are calculated.
  • If, for example you requested an AVG, then the average for each temporary sub group is calculated.

GROUP BY

  • GROUP BY outputs a summary table – one line for each group.
  • GROUP BY can only be used in conjunction with built-in functions.
  • GROUP BY follows FROM and WHERE and precedes ORDER BY
  • Example: SELECT DEPT, MAX(SALARY) FROM EMP GOUP BY DEPT
  • It will produce a table listing the maximum salary value for each department.

HAVING

  • It is similar in function to the WHERE clause
  • Its purpose is to further define a ‘group’ specified in the GROUP BY statement SELECT DEPT, AVG(SALARY) FROM EMP GROUP BY DEPT HAVING COUNT(*)>3
  • HAVING can only be used in conjunction with GROUP BY
  • HAVING must immediately follow the GROUP BY clause
  • HAVING can only compare built-in functions not individual columns

JOINING

  • Joining tables involves matching the rows of one table with related rows of another table to produce a table consisting of rows with columns from both the original tables.

BASIC PRINCIPLES OF JOINING TABLES

  • It will combine related information from two tables into a single row.
  • If a value appears once in the “joining column” of one table, but several times in the other, the higher number of occurrences will appear in the output table.
  • If a value exists in the “joining column” of one table, but not in the other, no row appears in the output table with that value.

JOINING COLUMN

  • It is a column that is common to all the tables being joined and contains the row values that tie one table to next.

HOW DO YOU ACTUALLY TIE THE TABLES TOGETHER?

  • With a special type of WHERE clause called the joining condition
    WHERE EMP.DEPTNO = DEPT.DEPTNO
  • Example: SELECT ITEM,PRICE,COLOR FROM Table1,Table2 WHERE Table1.ITEM = Table2.ITEM

MERGING

  • Merging tables also involves combining data from two tables, but the rows are not joined together.
  • Example:SELECT *FROM EMP UNION SELECT * FROM SUPPLIER

MERGING QUERY CODING

  • Select data from one table
  • Specify UNION or UNION ALL to indicate MERGE
  • Select data from second table
  • Example: SELECT * FROM Table_1 UNION SELECT * FROM Table_2
  • The UNION or UNION ALL keyword must be entered after the first SELECT statement

UNION vs UNION ALL

  • UNION sorts the result tables, UNION ALL does not. In other words, UNION mixes rows together in the output. UNION ALL outputs all the rows from the first table that meet the search condition. UNION removes duplicate rows, UNION ALL does not.

WHEN TO MERGE TWO TABLES

  • Each column selected from first table must be compatible with the corresponding columns from the second table.
  • A numeric column are compatible with numeric column, a character column to a character column, and so on.
  • Numeric data types need not be the same column length You must select the same number of columns from each tables.

CREATING AND UPDATING TABLES

  • To create a new table you must use the SQL statement CREATE TABLE. You must specify the table name and the names and attributes of each column.

RULES FOR TABLE NAME

  • Maximum 18 characters
  • Permissible characters
    • letters of the alphabet
    • @, #, and $
    • numbers
    • underscore (_)
  • The first character must be alphabetic or @,#, or $

DATA TYPES

Data type depends on the nature of the data itself and will always be one of two types – NUMERIC or CHARACTER

Types of CHARACTER column

CHAR – Allows any values to be entered in the column. All entries are the same length

VARCHAR – Allows any values to be entered in the column. All entries are the varying length

Types of NUMERIC columns

INTEGER - For very large integers up to 2,147,483,647

SMALLINT - For small integer up to 99999 Table Space

DECIMAL - For numbers with a fixed number of decimal places after the decimal point. The number can have a total of 15 digits

FLOAT -For very large numbers with an undetermined number of decimal places after the decimal point

Types of DATE/TIME columns

DATE - Dates are stored in YYYYMMDD format

TIME - Times are stored on a 24-hour clock in HHMMSS format

TIMESTAMP – TIMESTAMP columns contain both date and time information, with the time value accurate to the millisecond.

GRANT & REVOKE

  • Privileges in DB2 to control accessing data
  • To grant access to a table or view you must run a query that specifies three things in the following order
    • The privileges you are granting (SELECT,INSERT,DELETE,UPDATE or ALL)
    • The name of the table or view
    • The user or users to whom you are granting access STOGROUP(Storage group)
  • To grant everything, use GRANT ALL
  • To grant a privilege to EVERYBODY , you grant TO PUBLIC

PRIVILEGES

  • GRANT ALL ON TABLE EMP TO PUBLIC
  • REVOKE ALL ON EMP TO PUBLIC

CAN YOU CHANGE A TABLE OR VIEW AFTER YOU CREATE IT?

  • The only change you can make to a table or view after it has been created is to add an additional column.
  • This is done with the SQL command ALTER
    • ALTER TABLE EMP ADD COMM DECIMAL (7 ,2)

DROP

  • When you no longer need a TABLE, VIEW, or SYNONYM you can DROP them.
    • DROP TABLE SUPPLIER
    • DROP VIEW EMP_VIEW
    • DROP SYNONYM S

INSERTING A TABLE

  • There must be one value for each column
  • Separate values with commas
  • Put character values in single quotes
  • Numeric values do not require quotes
  • If you want enter the values in a different order, there is an alternative way of inserting a row, by listing the columns along with their assigned values
    • INSERT INTO SUPPLIER (S#,STATUS,SNAME) VALUES(‘S1’,30,’VAST’)

UPDATE

  • SQL provides two commands for updating row values – UPDATE and SET.
  • UPDATE tells SQL which table you want to update and SET provides the name of the column to update and the new value for the column.
  • You tell SQL which row to update with a WHERE clause can be very specific, so that a single row gets changed, or it can be very general, so that many rows get updated.
    • <B>UPDATE SUPPLIER SET STATUS = 40 WHERE S# = ‘S1’
    • UPDATE EMP SET SALARY = SALARY + SALARY *.01

DELETE

  • Using the DELETE command you can delete rows and using WHERE clause you can specify which rows you want to delete.
    • DELETE FROM SUPPLIER WHERE S# = ‘S1’

Complex SQL’s

  • One terms a SQL to be complex when data that is to be retrieved comes from more than one table.
  • SQL provides two ways of coding a complex SQL
    • Subqueries and Join

SUB QUERIES

  • Nested select statements.
  • Specified using the IN or NOT IN predicate, equality or non-equality predicate and comparative operator.
  • When using the equality, non-equality or comparative operators the inner query should return only a single value.
  • Nested loop statements gives the user flexibility for querying multiple tables.
  • A specialized form is a correlated sub query. It works on top-bottom-top fashion.
  • Non correlated sub query works in bottom to top fashion.

JOINS

  • OUTER JOIN
  • INNER JOIN

OUTER JOIN

  • For one or more tables being joined both matching and the non-matching rows are returned.
  • Duplicate columns may be eliminated.
  • Non-matching columns will have nulls.

INNER JOIN

  • Here there is a possibility one or more of the rows from either or both tables being joined will not be included in the table that results from the join operation

QMF- Query Management Facility

  • It is an MVS and VM-based query tools
  • Allows end users to enter SQL queries to produce a verity of reports and graphs as a result of this query.
  • QMF queries can be formulated in several ways: by direct SQL statements, by means of relational prompted query interface

SPUFI (SQL Processing Using File Input)

  • Supports the on-line execution of SQL statements from a TSO terminal.
  • Used for developers to check SQL statements or view table details.
  • SPUFI menu contains the input file in which the SQL statements are coded, option for default settings and editing and output file.

Program Preparation

  • Precompile
  • Compile & Link
  • Bind
    • Package
    • Plan

Precompile

  • Searches all the SQL statements and DB2 related INCLUDE members and comments out every SQL statement in the program.
  • The SQL statements are replaced by a CALL to the DB2 runtime interface module along with parameter.
  • All SQL statements are extracted and put in a DBRM.
  • Places a time stamp in the modified source and the DBRM so that these are tied.
  • All DB2 related INCLUDE statements must be placed between EXEC SQL & END-EXEC key words for the precompiler to recognize them.

Compile & Link

  • Modified precompiler COBOL output is complied.
  • Complied source is link edited to an executable load module.

Bind

  • A type of compiler for SQL statement.
  • It reads the SQL statements from the DBRM and produces a mechanism to access data as directed by the SQL statements being bound.
  • Checks syntax, check for correctness of table & column definitions against the catalog information & performs authorization validation.

Package

  • It is a single bound DBRM with optimized access paths.
  • It also contains a location identifier a collection identifier and a package identifier.
  • A package can have multiple versions, each with it’s own version identifier.

Advantages of Package

  • Reduced bind time.
  • Versioning.
  • Provides remote data access.
  • Can specify bind options at the programmer level.

Plan

  • An application plan contains one or both of the following elements
    • A list of package names.
    • The bound form of SQL statements taken from one or more DBRM.
  • Every DB2 application requires an application plan.
  • Plans are created using the DB2 sub commands BIND PLAN.

DB2 Optimizer

  • Analyzes the SQL statements and determines the most efficient way to access data, gives physical data independence.
  • It evaluates the following factors: CPU cost, I/O cost, DB2 catalogue statistics & the SQL statements.
  • It estimate CPU time, cost involved in applying predicates traversing pages & sorting.
  • It estimates the cost of physically retrieving and writing data.

Steps involved in creating a DB2 Application

  • Using Embedded SQL
  • Using Host Variables (DCLGEN)
  • Using SQLCA
  • Precompile
  • Compile & Linkedit the program
  • Bind

Embedding SQL Statements

  • It is like the file I/O
  • Normally the embedded SQL statements contain the host variables coded with the INTO clause of the SELECT statement.
  • They are delimited with EXEC SQL & END-EXEC
  • Example: EXEC SQL
    SELECT eno, ename INTO :h-eno,:h-ename
    FROM employee WHERE eno=1001
    END-EXEC.

Host Variables

  • These are variables defined in the host language to use the predicates of a DB2 table. These are referenced in the SQL statement.
  • A means of moving data from and to DB2 tables.
  • DCLGEN produces host variables the same as the columns of the tables.

DCLGEN

  • Issued for a single table.
  • Prepares the structures of the table in a COBOL copy book.
  • The copy book contains a SQL DECLARE TABLE statement along with a working storage host variable definition for the table.

SQLCA

  • An SQLCA is a structure or collection of variables that is updated after each SQL statement executes.
  • An application program that contains executable SQL statements must provide exactly one SQLCA.

CURSOR

  • Used when more than one row are to be selected.
  • Can be used for modifying data using ‘FOR UPDATE OF’ clause.

The Four Cursor control Statements

  • DECLARE : name assigned for a particular SQL statement.
  • OPEN: Builds the result table.
  • FETCH : Returns data from the results table one row at a time and assign the value to the specified host variables.
  • CLOSE: Releases all resources used by the cursor.

DECLARE:
EXEC SQL
DECLARE empcur CURSOR FOR
SELECT empno, ename, dept, job
FROM emp WHERE dept=‘D11’
FOR UPDATE OF job
END-EXEC.

OPEN: for the OPEN statement
EXEC SQL
OPEN empcur
END-EXEC.

FETCH: for the FETCH statement
EXEC SQL
FETCH empcur
INTO :empno, :ename, :dept, :job

END-EXEC.

WHENEVER: for the WHENEVER clause
EXEC SQL
WHENEVER NOT FOUND
GO TO close-empcur
END-EXEC.

UPDATE: for the update statement using cursors
EXEC SQL
UPDATE emp
SET job=:new-job
WHERE CURRENT OF empcur
END-EXEC.

DELETE: for the delete statement using cursor.
EXEC SQL
DELETE FROM EMP
WHERE CURRENT OF empcur
END-EXEC.

DB2 LOCKING

  • Locking is used to provide multiple user access to the same system.
  • DB2 uses locking services provided by an MVS subsystem called IMS Resource Locking Manager(IRLM).

Explicit Locking Facilities

  • The SQL statement LOCK TABLE
  • The ISOLATION parameter on the BIND PACKAGE command – the two possible values are RR(Repeatable Read) & CS (Cursor Stability).
  • CS is the value specified if the application program is used in an on-line environment.
  • The LOCKSIZE parameter – physically DB2 locks data in terms of pages or tables or table spaces. This parameter is specified in CREATE or ALTER table space option ‘LOCKSIZE’. The options are Tablespace, Table, Page or Any.
  • The ACQUIRE / RELEASE parameters on the BIND PLAN command specifies when table locks to be acquired and release.
    • ACQUIRE USE / ALLOCATE
    • RELESE COMMIT / DEALLOCATE

Catalogue Tables

  • Repository for all DB2 objects – contains 43 tables
  • Each table maintains data about an aspects of the DB2 environment
  • The data refers to information about table space, tables, indexes etc…
  • SYSIBM.SYSTABLES, SYSIBM.SYSINDEXS, SYSIBM.SYSCOLUMNS etc…

DB2 Utilities:-

UTILITIES

  • CHECK
  • COPY
  • MERGECOPY
  • RECOVER
  • LOAD
  • REORG
  • RUNSTATS
  • EXPLAIN

CHECK

  • Checks the integrity of DB2 data structures
  • Checks the referential integrity between two tables and also checks DB2 indexes for consistency.
  • Can delete invalid rows and copies them to a exception table.

COPY

  • Used to create an image copy for the complete table space or a portion of the table space- full image copy or incremental image copy.
  • Every successful exception of COPY utility places in the table, SYSIBM.SYSCOPY, at least one row that indicates the status of the image copy.

MERGECOPY

  • The MERGECOPY utility combines multiple incremented image copy data sets into a new full or incremental image copy dataset.

RECOVER

  • Restore Db2 table spaces and indexes to a specific instance.
  • Data can be recovered for a single page, pages that contain I/O errors, a single partition or an entire tablespace.
  • Standard unit of recovery is table space.

LOAD

  • To accomplish bulk inserts into DB2 table
  • Can replace the current data append to it
  • If a job terminates in any phase of LOAD REPLACE, the utility has to be terminated and rerun

REORG

  • To reorganize DB2 tables and indexes and there by improving their efficiency of access re-clusters data, resets free space to the amount specified in the ‘create DDL’ ,statement and deletes and redefines underlying VSAM datasets for stogroup defined objects.

EXPLAIN

  • Explain can be used to obtain the details about the access paths chosen by the DB2 optimizer for SQL statements.
  • Used specifically for performance monitoring.
  • When EXPLAIN is requested the access paths that the DB2 chooses are put in coded format into the table PLAN_TABLE, which is created in the default database by the user.
  • The other method is specifying EXPLAIN YES with BIND command
  • The PLAN_TABLE is to be queried to get the required information
  • Since the EXPLAIN results are dependent on the DB2 catalogue, it is better to run RUNSTAT before running EXPLAIN.

Db2 FAQ's frame:-

DB2 FAQS

1. How would you find out the total number of rows in a table?

o Use SELECT COUNT(*) ...

2. How do you eliminate duplicate values in SELECT?

o Use SELECT DISTINCT ...

3. How do you select a row using indexes?

o Specify the indexed columns in the WHERE clause.

4. What are aggregate functions?

o Built-in mathematical functions for use in SELECT clause.

5. How do you find the maximum value in a column?

o Use SELECT MAX(...)

6. Can you use MAX on a CHAR column?

o Yes.

7. My SQL statement SELECT AVG(SALARY) FROM EMP yields inaccurate results. Why?

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

8. How do you retrieve the first 5 characters of FIRSTNAME column of EMP table?

o SELECT SUBSTR(FIRSTNAME,1,5) FROM EMP;

9. How do you concatenate the FIRSTNAME and LASTNAME from EMP table to give a complete name?

o SELECT FIRSTNAME ¦¦ ' ' ¦¦ LASTNAME FROM EMP;

10. What is the use of VALUE function?

o Avoid -ve SQLCODEs by handling nulls and zeroes in computations

o Substitute a numeric value for any nulls used in computation

11. What is UNION,UNION ALL?

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

12. Suppose I have five SQL SELECT statements connected by UNION/UNION ALL, how many times should I specify UNION to eliminate the duplicate rows?

o Once.

13. What is the restriction on using UNION in embedded SQL?

o It has to be in a CURSOR.

14. In the WHERE clause what is BETWEEN and IN?

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

15. Is BETWEEN inclusive of the range values specified?

o Yes.

16. What is 'LIKE' used for in WHERE clause? What are the wildcard characters?

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

17. When do you use a LIKE statement?

o 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.

18. What is the meaning of underscore ( '_' ) in the LIKE statement?

o Match for any single character.

19. What do you accomplish by GROUP BY ... HAVING clause?

o GROUP BY partitions the selected rows on the distinct values of the column on which you group by.

o HAVING selects GROUPs which match the criteria specified

20. Consider the employee table with column PROJECT nullable. How can you get a list of employees who are not assigned to any project?

o SELECT EMPNO
FROM EMP
WHERE PROJECT IS NULL;

21. What is the result of this query if no rows are selected:

o SELECT SUM(SALARY)
FROM EMP
WHERE QUAL='MSC';
NULL

22. Why SELECT * is not preferred in embedded SQL programs?

o For three reasons:

1. If the table structure is changed ( a field is added ), the program will have to be modified.

2. Program might retrieve the columns which it might not use, leading on I/O over head.

3. The chance of an index only scan is lost.

23. What are correlated sub queries?

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

24. What is a cursor? why should it be used?

o Cursor is a programming device that allows the SELECT to find a set of rows but return them one at a time.

o Cursor should be used because the host language can deal with only one row at a time.

25. How would you retrieve rows from a DB2 table in embedded SQL?

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

26. Apart from cursor, what other ways are available to you to retrieve a row from a table in embedded SQL?

o Single row SELECTs.

27. How do you specify and use a cursor in a COBOL program?

o 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.

28. What happens when you say OPEN CURSOR?

o 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.

29. Is DECLARE CURSOR executable?

o No.

30. Can you have more than one cursor open at any one time in a program?

o Yes.

31. When you COMMIT, is the cursor closed?

o Yes.

32. How do you leave the cursor open after issuing a COMMIT? (for DB2 2.3 or above only )

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

33. Give the COBOL definition of a VARCHAR field.

o 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).

34. What is the physical storage length of each of the following DB2 data types: DATE, TIME, TIMESTAMP?

DATE:

4bytes

TIME:

3bytes

TIMESTAMP:

10bytes

35. 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)

36. What is the COBOL picture clause for a DB2 column defined as DECIMAL(11,2)?

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

37. What is DCLGEN?

o DeCLarations GENerator: used to create the host language copybooks for the table definitions. Also creates the DECLARE table.

38. What is JOIN and different types of JOIN.

o The ability to join rows and combine data from two or more tables is one of the most powerful features of relational system. Three types of joins:

1. Equi-join

2. Non-equijoin

3. self-join

39. Can I alter a table (e.g. adding a column) when other user is selecting some Columns or updating some columns from the same table?

o yes possible. until the updation or selection is committed db2 table will not be restructured. new column definition will be there but it will not be included until all the tasks on the table are committed.

40. What are the different methods of accessing db2 from tso?

o There are three ways in establishing tso/db2 connection

1. SPUFI

2. QMF

3. CATALOG VISIBILITY

41. How is the connection established between TSO & DB2?

o A thread between TSO & DB2 is established while attempting to make Connection between tso & db2.

42. What is sqlcode -922?

o Authorization failure

43. How do you do the EXPLAIN of a dynamic SQL statement?

o Use SPUFI or QMF to EXPLAIN the dynamic SQL statement

o Include EXPLAIN command in the embedded dynamic SQL statements

44. How is a typical DB2 batch pgm executed?

o 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

('EDGS01T.OBJ.LOADLIB')

END

o Use IKJEFT01 utility program to run the above DSN command in a JCL.

45. Is it mandatory to use DCLGEN? If not, why would you use it at all?

o 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.

46. Name some fields from SQLCA.

o SQLCODE, SQLERRM, SQLERRD

47. How does DB2 determine what lock-size to use?

o Based on the lock-size given while creating the table space

o Programmer can direct the DB2 what lock-size to use

o If lock-size ANY is specified, DB2 usually choses a lock-size of PAGE

48. What is the difference between CS and RR isolation levels?

o CS: Releases the lock on a page after use

o RR: Retains all locks acquired till end of transaction

49. Where do you specify them?

o ISOLATION LEVEL is a parameter for the bind process.

50. How do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/QMF? Give an example with a host variable in WHERE clause.

o Use question mark in place of a host variable (or an unknown value). e.g.

SELECT EMP_NAME
FROM EMP
WHERE EMP_SALARY > ?

51. What is ACQUIRE/RELEASE in BIND?

o Determine the point at which DB2 acquires or releases locks against table and Table spaces, including intent locks.

52. In SPUFI suppose you want to select max. of 1000 rows, but the select returns only 200 rows. What are the 2 sqlcodes that are returned?

o 100 (for successful completion of the query), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).

53. How would you print the output of an SQL statement from SPUFI?

o Print the output data set.

54. What does it mean if the null indicator has -1, 0, -2?

o -1 : the field is null

o 0 : the field is not null

o -2 : the field value is truncated

55. How do you retrieve the data from a nullable column?

o Use null indicators. Syntax ... INTO :HOSTVAR:NULLIND

56. What else is there in the PLAN apart from the access path?

o PLAN has the executable code for the SQL statements in the host program

57. What is lock escalation?

o Promoting a PAGE lock-size to table or table space lock-size when a transaction has aquired more locks than specified in NUMLKTS. Locks should be taken on objects in single table space for escalation to occur.

58. When is the access path determined for dynamic SQL?

o At run time, when the PREPARE statement is issued.

59. What are the various locks available?

o SHARE, EXCLUSIVE, UPDATE

60. What is sqlcode -811?

o SELECT statement has resulted in retrieval of more than one row.

61. What are the advantages of using a PACKAGE?

o Avoid having to bind a large number of DBRM members into a plan

o Avoid cost of a large bind

o Avoid the entire transaction being unavailable during bind and automatic rebind of a plan

o Minmize fallback complexities if changes result in an error.

62. What is REORG? When is it used?

o REORG reorganizes data on physical storage to re-cluster rows, positioning oveflowed rows in their proper sequence, to reclaim space, to restore free space. It is used after huge updates, inserts and delete activity and after segments of a segmented table space have become fragmented.

63. How does DB2 store NULL physically?

o as an extra-byte prefix to the column value. physically, the nul prefix is Hex '00' if the value is present and Hex 'FF' if it is not

64. What is CHECK PENDING?

o When a table is LOADed with ENFORCE NO option, then the table is left in CHECKPENDING status. It means that the LOAD utility did not perform constraint checking.

65. When do you specify the isolation level? How?

o During the BIND process. ISOLATION (CS/RR)...

66. What is a DBRM, PLAN?

o DBRM: DataBase Request Module, has the SQL statements extracted from the host language program by the pre-compiler.

o PLAN: A result of the BIND process. It has the executable code for the SQL statements in the DBRM.

67. Is DECLARE TABLE in DCLGEN necessary? Why it used?

o 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.

68. What do you need to do before you do EXPLAIN?

o Make sure that the PLAN_TABLE is created under the AUTHID.

69. What is a collection?

o a user defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages.

70. How can you quickly find out the # of rows updated after an update statement?

o Check the value stored in SQLERRD(3).

71. What is EXPLAIN?

o 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 ).

72. Where is the output of EXPLAIN stored?

o In userid.PLAN_TABLE

73. Suppose I have a program which uses a dynamic SQL and it has been performing well till now. Off late, I find that the performance has deteriorated. What happened?

o Probably RUN STATS is not done and the program is using a wrong index due to incorrect stats. Probably RUNSTATS is done and optimizer has chosen a wrong access path based on the latest statistics.

74. What are the contents of a DCLGEN? - GS

o EXEC SQL DECLARE TABLE statement which gives the layout of the table/view in terms of DB2 data types.

o A host language copy book that gives the host variable definitions for the column names.

75. Will pre-compile of an DB2-COBOL program bomb, if DB2 is down?

o No. Because the pre-compiler does not refer to the DB2 catalogue tables.

76. What are the isolation (also called isolation parameters) levels possible?

o CS: Cursor Stability

o RR: Repeatable Read

77. What is the picture clause of the null indicator variable?

o S9(4) COMP.

78. EXPLAIN has output with MATCHCOLS = 0. What does it mean?

o A non-matching index scan if ACCESSTYPE = I.

79. I use CS and update a page. Will the lock be released after I've done with that page?

o No.

80. What are the various locking levels available?

o PAGE, TABLE, TABLESPACE

81. What is ALTER?

o SQL command used to change the definition of DB2 objects.

82. Can I use LOCK TABLE on a view?

o No. To lock a view, take lock on the underlying tables.

83. What are the disadvantages of PAGE level lock?

o High resource utilization if large updates are to be done

84. What are PACKAGES ?

o They contain executable code for SQL statements for one DBRM.

85. Lot of updates have been done on a table due to which indexes have gone haywire. What do you do?

o Looks like index page split has occured. DO a REORG of the indexes.

86. What is dynamic SQL?

o Dynamic SQL is a SQL statement created at program execution time.

87. What is IMAGECOPY ?

o It is full backup of a DB2 table which can be used in recovery.

88. What is QUIESCE?

o A QUIESCE flushes all DB2 buffers on to the disk. This gives a correct snapshot of the database and should be used before and after any IMAGECOPY to maintain consistency.

89. What does the sqlcode of -818 pertain to? - GS

o This is generated when the consistency tokens in the DBRM and the load module are different.

90. What happens to the PLAN if index used by it is dropped?

o Plan is marked as invalid. The next time the plan is accessed, it is rebound.

91.

o

92. What is FREEPAGE and PCTFREE in TABLESPACE creation?

o PCTFREE: percentage of each page to be left free

o FREEPAGE: Number of pages to be loaded with data between each free page

93. Are views updatable?

o Not all of them. Some views are updatable e.g. single table view with all the fields or mandatory fields. Examples of non-updatable views are views which are joins, views that contain aggregate functions (such as MIN), and views that have GROUP BY clause.

94. What is COPY PENDING status?

o A state in which, an image copy on a table needs to be taken, in this state, the table is available only for queries. You cannot update this table. To remove the COPY PENDING status, you take an image copy or use REPAIR utility.

95. What is an inner join, and an outer join?

o Inner Join: combine information from two or more tables by comparing all values that meet the search criteria in the designated column or columns of one table with all the values in corresponding columns of the other table or tables. This kind of join which involve a match in both columns are called inner joins.

o Outer join is one in which you want both matching and non matching rows to be returned. DB2 has no specific operator for outer joins, it can be simulated by combining a join and a correlated sub query with a UNION.

96.

o

97. What is the difference between primary key & unique index?

o Primary: a relational database constraint. Primary key consists of one or more columns that uniquely identify a row in the table. For a normalized relation, there is one designated primary key.

o Unique index: a physical object that stores only unique values. There can be one or more unique indexes on a table.

98. When do you use the IMAGECOPY?

o To take routine backup of tables.

o After a LOAD with LOG NO. After REORG with LOG ON.

99. What is RUNSTATS?

o A DB2 utility used to collect statistics about the data values 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.

100. How do you insert a record with a nullable column?

o To insert a NULL, move -1 to the null indicator

o To insert a valid value, move 0 to the null indicator