DB2 Reviewer Part 2

What is DB2?
DB2 is a subsystem of the MVS operating system.  It is a Database Management System (DBMS) for that operating system.

What is an access path?
The path that is used to get to data specified in the SQL statements.

What is an alias?
It is an alternate name that can be used in SQL statements to refer to a table or view in the same or remote DB2 subsystem.

Explain what a plan is?
Plan is a DB2 object (produced during the bind process) that associates one or more database request module with a plan name,

What is a DB2 bind?
Bind is a process that builds "access paths" to DB2 tables. A bind uses the Database Request Module(s) (DBRM) from the DB2 pre-compile step as input and produces an application plan. It also checks the user's authority and validates the SQL statements in the DBRM(s).

What information is used as input to the bind process?
The database request module produced during the pre-compile. The SYSIBM.SYSSTMT table of the DB2 catalog.

What is meant by the attachment facility?
The attachment facility is an interface between DB2 and TSO, IMS.VS, CICS, or batch address spaces. It allows application programs to access DB2,

What is meant by AUTO COMMIT?
AUTO COMMIT is a SPUFI option that commits the effects of SQL statements automatically if they are successfully executed.

What is a base table?
A base table is a real table  - a table that physically exists in that there are physical stored records.

What is the function of buffer manager?
The buffer manager is the DB2 component responsible for physically transferring data between an external medium and (virtual) storage (performs the actual I/O operations). It minimizes the amount of physical I/O actually performed with the sophisticated buffering techniques (i.e, read-ahead buffering and look-aside buffering).

What is a buffer pool?
A buffer pool is main storage that is reserved to satisfy the buffering requirements for one or more tablespaces or indexes, and is made up of either 4K or 32K pages,

How many buffer pools are there in DB2?
There are four buffer pools: BP0, BP1, BP2 and BP3.

On the create tablespace, what does the CLOSE parameter do?
CLOSE physically closes the tablespace when no one is working on the object. DB2 (release 2.3) will logically close tablespaces.

What is a clustering index?
It is a type of index that (1) locates table rows and (2) determines how rows are grouped together in the tablespace.

What will the COMMIT accomplish?
COMMIT will allow data changes to be permanent. This then permits the data to be accessed by other units of work, When a COMMIT occurs, locks are freed so other applications can reference the just committed data.

What is meant by concurrency?
Concurrency is what allows more than one DB2 application process to access the same data at essentially the same time. Problems may occur, such as lost updates, access to uncommitted data and unrepeatable reads.

What is cursor stability?
It is cursor stability that "tells" DB2 that database values read by this application are protected only while they are being used. (Changed values are protected until this application reaches the commit point). As soon as a program moves from one row to another, other programs may read the first row.

What is the function of the Data Manager?
The Data Manager is a DB2 component that manages the physical databases. It invokes other system components, as necessary, to perform detailed functions such as locking, logging, and physical I/O operations (such as search, retrieval, update and index maintenance).

What is a Database Request Module (DBRM)?
A DBRM is a DB2 component created by the DB2 pre-compiler containing the SQL source statements extracted from the application program.DBRMs are input to the bind process.

What is a data page?
A data page is a unit of retrieval data, either 4K or 32K (depending on h ow the table is defined), containing user or catalog information.

What does DSNDB07 database do?
DSNDB07 is where DB2 does its sorting. It includes DB2's sort work area and external storage,

What will FREE command do to a plan?
It will drop(delete) the existing plan,

What will the DB2 optimizer do?
The optimizer is a DB2 component that processes SQL statements and selects the access paths.

What is a page?
This is the unit of storage within a table spaces or index space that is accessed by DB2.

What is pagespace?
Pagespace refers to either - unpartitioned table, index space, or to a single partition of a partitioned table of index space.

What is predicate?
A predicate is an element of search condition that expresses or implies a comparison operation.

What is a recovery log?
A recovery log is a collection of records that describes the sequence of events that occur in DB2. The information is needed for recovery in the event of a failure during execution.

What is a Resource Control Table (RCT)? Describe its characteristics.
The RCT is a table that is defined to a DB2/CICS region. It contains control characteristics which are assmbled via DSNCRCT macros. The RCT matches the CIC transaction ID to its associated DB2 authorization ID and plan ID (CICS attachment facility).

Where are plans stored?
Each plan is defined in the SYSIBM.SYSPLANS table to correspond to the transaction(s) that are to execute that plan.

What is a DB2 catalog?
The DB2 catalog is a set of tables that contain information about all of the DB2 objects (tables, views, plans)

In which column of which DB2 catalog would you find the length of the rows of all tables?
In the RECLENGTH column of SYSIBM.SYSTABLES

What information is held in SYSIBM.SYSCOPY?
The SYSIBM.SYSCOPY table contains information about image copies made of the tablespaces.

What information is contained in a SYSCOPY entry?
Included is the name of the database, the table space name and the image copy type (full or incremental) as well as the date and time each copy was made,

What information can you find in the SYSIBM.SYSLINKS table?
The SYSIBM.SYSLINKS table contains information about the links between tables created by referrential constraints.

Where would you find information about the type of databse authority held byt he user?
SYSIBM.SYSDBAUTH

Where could you look if you had a question about whether a column has been defined as an index?
SYSIBM.SYSINDEXES.

Once you createa view, where would information about the view be stored?
SYSIBM.SYSVIEWS.

 Assuming that a site's standard is that pgm name = plan name, what is the easiest way to find out which programs are affected by change in a table's structure?
Query the catalogue tables SYSPLANDEP and SYSPACKDEP.

MVS JCL Reviewer Part 2

What is GDG (Generation Data Group)?

•    Generation Data Groups or GDG’s are a group of data sets which are related to each other chronologically and functionally.
•    These related Data Sets share a unique Data Set Name.
•    Every GDG data set has a Generation number and Version number assigned to each data set.

   EXAMPLE --       'MYLIB.LIB.TEST.G0001V00'
                                'MYLIB.LIB.TEST.G0002V00'
                                'MYLIB.LIB.TEST.G0003V00'     <-- Current Version


                                Generation Number ->  GaaaaVnn
                                                  aaaa is between 0000 to 9999
                                                  nn   is between 00   to 99


    In JCL:
            We refer current version with 0 
                Example.  MYLIB.LIB.TEST(0)
                            New version going to create with  +1    
                Example.  MYLIB.LIB.TEST(+1)
                            Older versions , refer with -1 -2 -3 etc....
                                              Example.  MYLIB.LIB.TEST(-1)   <- OLDER VERSION


•    Example for where we can use this GDGs: Usually, In production environment, for every month we need to run jobs to create reports for that month.

            Let us suppose     for JAN, We can code it  MYLIB.LIB.TEST.JAN
                                        for FEB, We can code it  MYLIB.LIB.TEST.FEB
                                        for MAR, We can code it  MYLIB.LIB.TEST.MAR
                                                 
So, every month we need change dataset name in JCL, before submitting the job. Whenever we entered into another year, we need to delete old year’s data sets.

 We need to do above task carefully, If we use GDG, It will take care following things

o    It will maintain all generation of data sets
o    It will delete/uncatalog older generation
o    Very easily, we can refer current and older versions of data sets
o    No need of change the JCL every time when we submit


How do you create a GDG?

•    Before using GDG, we need to create GDG index and model.


How do you create a GDG index?

•    IDCAMS (the 'AMS' stands for Access Method Services), utility is used to create GDG index.

•    Example JCL for creating GDG index.

           //MYJOB   JOB  (W234),’MYNAME’
           //STEP1   EXEC PGM=IDCAMS         
           //SYSIN   DD   *
             DEFINE GDG(NAME(MYLIB.LIB.TEST)    -
                        LIMIT(10)               -
                        NOEMPTY                 -
                        SCRATCH)
           /*
           //

           In this example, IDCAMS utility is used to create an index
           for a GDG called  MYLIB.LIB.TEST. The number of generations
           that can exist in this GDG is limited to ten. NOEMPTY parameter
           is used to specify , Once the limit is reached, the system is
           instructed to un-catalog the oldest generation data set within the
           GDG. SCRATCH parameter is used to specify to physically delete
           the data set which was un-catalogued.

    Parameters we can pass to IDCAMS.

o    NAME: This parameter is used to specify the name of the data set that is to be created.
o    LIMIT: This parameter is used to specify the total number of generations that the GDG may contain.
o    EMPTY/NOEMPTY: These two parameters are mutually exclusive. EMPTY specifies that all existing generations of the GDG are to be un-catalogued whenever the generations of GDG reached the maximum limit. NOEMPTY specifies that only the oldest generation of the GDG is to be un-catalogued if the limit is reached.
o    SCRATCH/NOSCRATCH: These two parameters are mutually exclusive. SCRATCH parameter specifies that whenever entry of the GDG is removed from the index, it should be deleted physically and catalogued.  NOSCRATCH parameter specifies that whenever entry of the GDG is removed from the index, it should be catalogued, not physically deleted.

        Note: SCRATCH and NOEMPTY are default parameters.


How do you create a GDG model?

•    Once the index has been created, a model data set must be created. This model data set contains specifications for the DCB sub-parameters for all data sets that will belong to that GDG. Programmer can override this default values if he want.

       EXAMPLE JCL

       //MYJOB  JOB   (W983),'TESTJOB'
       //STEP1  EXEC   PGM=IDCAMS
       //SYSIN  DD     *
         DEFINE GDG(                         -
                     NAME(MYLIB.LIB.TEST)    -
                     LIMIT(10)               -
                     NOEMPTY                 -
                     SCRATCH)
       //STEP2  EXEC PGM=IEFBR14
       //MODEL1 DD   DSN=MYLIB.LIB.TEST,
       //            DISP=(NEW,KEEP,DELETE),
       //            UNIT=SYSDA,
       //            SPACE(TRK,0),
       //            DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)
       //

       Using IEFBR14, we have created the model.

          
How do you use a GDG?

•    To use created GDG in our JCL, we need to use name (with +1 for new generation) which we used in DEFINE GDG command. (i.e. MYLIB.LIB.TEST).

     EXAMPLE JCL       
      
     //MYJOB   JOB  (SD345),’MYNAME'
     //STEP1   EXEC PGM=COBPROG
     //INFILE  DD   DSN=MYLIB.LIB.TEST(+1),
     //             DISP=(NEW,CATLG,DELETE),
     //             UNIT=SYSDA,
     //             SPACE=(TRK,(20,10),RLSE),
     //             DCB=(MODEL.DCB,RECFM=FB,
     //             LRECL=80,
     //             BLKSIZE=800)
     //

     The program COBPROG is executed. A new generation data set is
     created via the statement

         //INFILE  DD  DSN=MYLIB.LIB.TEST(+1)

     Since we used (+1) with GDG name, it creates a new generation
     data set.

     The DISP parameter must be set to CATLG for all new generation
     data sets , DISP=(NEW,CATLG,DELETE)


     We used MODEL.DCB in DCB parameter to instruct system to use
     Sub-parameters specified in model GDG.

Note: The DSN and UNIT parameters must be coded for all new generation data sets.


In the JCL, In step1 it is going to create a new generation data set with name GDGNAME(+1). In step2, if you want to use same data set created from previous step? What number should you give to refer to that data set (i.e. 0 or +1 or +2)?   (STEP1 EXECUTED SUCCESSFULLY). Why?

•    Use number +1 to refer to the dataset created by the previous step.
•    Even step1 executed successfully, it is not become the current generation of GDG. At the end of the job only it will become the current version of GDG. So within the job we need to refer it as new generation only, even that step completed successfully. 

COBOL Reviewer Part 2

What is the COBOL SORT verb?

•    The SORT verb takes one or more files of input records and sorts them in ascending or descending order by one or more programmer specified keys.

•    The result is a single sorted file.

•    Format: 
SORT  sd-file-name
            ON ASCENDING/DESCENDING KEY  sd-key-name

            |USING input-file-name                         |
            |INPUT PROCEDURE IS routine-name|

            |GIVING sorted-file-name                         |
            |OUTPUT PROCEDURE IS routine-name|.


   sd-file-name      a temporary sort work file
                             SD in the DATA DIVISION
                             SELECT statement in the ENVIRONMENT DIVISION
                             MUST be CLOSED when the sort is called


   sd-key-name     key field from the SD record description


   USING input-file-name        this is the file that will be sorted
                                    FD in the DATA DIVISION
                                    SELECT stmt in the ENVIRONMENT DIVISION
                                    MUST be CLOSED when the sort is called
  
  
   INPUT PROCEDURE IS routine-name
  
      - routine-name is a programmer coded routine that will
        select which records are to be used in the sort
     
      - input procedure pseudocode:
     
              OPEN input-file-name
        
              READ the first record
         
              While there are input records
                    Modify the current record
                    MOVE record to sd-file-record
                    RELEASE sd-file-record
                    READ next record
              Endwhile
         
              CLOSE input-file-name

         
       - To release a record to the sort:
      
              RELEASE sd-file-record [FROM ws-area].
         
 this is equivalent to a WRITE in COBOL except that its performed on an SD file and is used ONLY in an input procedure
           
   GIVING sorted-file-name     this is the resulting sorted file
                                    FD in the DATA DIVISION
                                    SELECT stmt in the ENVIRONMENT DIVISION
                                    MUST be CLOSED when the sort is called
  
  
   OUTPUT PROCEDURE IS routine-name
  
      - routine-name is a programmer coded routine that will
        manipulate the records AFTER they are sorted

      - output procedure pseudocode:
     
              OPEN sorted-file-name
        
              RETURN the first sd-file-record
         
              While there are input records
                    Modify the sorted record
                    MOVE sd-file-record to sorted-file-record
                    WRITE sorted-file-record
                    RETURN the next sd-file-record
              Endwhile
         
              CLOSE sorted-file-name

         
       - To return a record:
      
              RETURN sd-file-name [INTO ws-area]
                    [AT END do something]
                    [NOT AT END do something else]
              END-RETURN.
         
         this is equivalent to a READ in COBOL except that it is performed on an SD file and is used ONLY in an output procedure

•    The COBOL SORT verb calls SYNCSORT to do its work.
•    There are some "special" registers that are set by the SORT verb and can be set or tested by a programmer.

SORT-RETURN        after a SORT this will contain a return code that can be tested
              
                       0        success
                       16       failure
              
 if a programmer moves a non-zero value to this register, the sort will stop on the next RETURN or RELEASE
              
SORT-FILE-SIZE  equivalent to SYNCSORTs FILSZ

SORT-MESSAGE  can use this one to specify an 8 byte ddname for SYNCSORT to write its messages to
              
                       In COBOL:  MOVE 'SORTOUT' TO SORT-MESSAGE.
              
                       In JCL:  //SYSOUT   DD  SYSOUT=*
                                    //SORTOUT  DD  SYSOUT=*


How do you SORT in a COBOL program? Give sort file definition, sort statement syntax and meaning.

•    The COBOL SORT statement is used in the PROCEDURE DIVISION to place records from an input file in a temporary work file where their order will be rearranged. This work file is referred to as the sort work file. Once rearranged, the records can be placed back in the original input file, placed in a separate output file, or processed by the program directly from the sort work file. 

•    ENVIRONMENT DIVISION entries
The input file, the sort file, and, if used, the output file must each be defined by a SELECT statement in the INPUT-OUTPUT SECTION, FILE-CONTROL paragraph. As with any other file, the SELECT statement for the sort file specifies both the internal and external filenames for the program and for the Operating System.

    SELECT SORT-FILE ASSIGN TO DISK “SORTWORK.TMP”.

In this example, the internal filename is SORT-FILE and the external filename is SORTWORK.TMP. These are programmer-supplied words, as with any file definition, and are chosen because they are descriptive. The DOS file extension TMP is chosen to remind you that this is a temporary file.

•    DATA DIVISION entries
In the FILE SECTION, the input and output files would be described as usual, using FD and record description entries. For the SORT-FILE, in place of an FD entry, a sort work file has a similar SD entry, which stands for Sort Description. The record definition following the SD entry is called the sort record and must be broken down enough to show the position and size of the sort key field(s). Multiple keys can be defined. All other fields can be defined as filler entries if not used in the Procedure Division. The total number of characters (sum of the PIC sizes) within this record must match the number of characters in the records to be sorted.

    SD    SORT-FILE.
    01    SORT-RECORD.
        02                          PIC X(20).
        02    SORT-KEY   PIC X(5).
        02                          PIC X(30).
        02    SR-UNITS    PIC 999.
        02    SR-COST     PIC 9(5)V99.
        02                         PIC X(15).

In this example, SD is used instead of FD to describe the sort file in this division. The file name must be the same as the internal file name in the Environment Division. The record is described to define the name, position, data type, and size of the key field for use with the SORT verb in the Procedure Division. Other fields that may be referenced by the program’s procedures are also defined.

•    PROCEDURE DIVISION statement
The sort work file and the sort key field(s) are among the entries named in a SORT statement. The name of the work file must follow the verb SORT. This name must be the same as the name used in the SELECT and SD entries. Records can be arranged in either ASCENDING or DESCENDING sequence by key field(s) specified. When multiple key fields are used, they must be listed from major to minor. USING and GIVING clauses are used to specify the name of the input data file and the name of the output data file respectively.

    SORT SORT-FILE
        ASCENDING SORT-KEY,
        USING INPUT-FILE,
        GIVING OUTPUT-FILE.

In this example, the SORT verb would perform these procedures:
1.    The file named INPUT-FILE would automatically be opened for input, records read, rearranged, and stored in the sort’s work file in ascending sequence according to the values in the key field of each record, and the file closed.
2.    The file named OUTPUT-FILE would automatically be opened for output, the sorted records written to the file, and the file closed.

To sort the records, the programmer does not code any OPEN, CLOSE, READ, or WRITE statements. The SORT causes all these operations to occur. After the SORT verb is complete, the programmer may open the resulting sorted file for input and process the records normally (by using the verbs OPEN, READ, CLOSE). This may be accomplished in the same program or in a separate one.


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

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


What is the SORT collating sequence in mainframe COBOL?

•    Sorting takes place on the relative binary values of the characters in the key field(s) of the sort record.
•    Mainframe code sets is EBCDIC (Extended Binary Coded Decimal Interchange Code). Collating sequence is Space, a-z, A-Z, 0-9.

MVS Utilities Reviewer Part 2

AN OVERVIEW ON SYNCSORT 

1. OVERVIEW

Syncsort belongs to Syncsort IncC that can sort data, merge data, selectively process data, reformat data, create summary records from data and create extensive reports from input data. It can also perform any combination of the above mentioned functions and more. This document tries to explain in brief the various items related to processing data using Syncsort. This document describes a subset of the Syncsort functions and does not claim to be a replacement nor does the author guarantee the exactness of details or syntax.

2. SYNCSORT COMMANDS

2.1 SORT The SORT statement can be used sort a dataset or concatenated datasets. The SORT statement requires the sort sequence for the data. The list of fields and their formats must be specified for this statement. The output records are sorted in the specified sequence. The multiple records contain the same sort sequence key, then the options specified will determine if the input order is maintained for such records. The format of the SORT statement is as follows.

SORT FIELDS=({begcol},{length},{fieldtype},{D|A}[,{begcol}, {length},{fieldtype},{D|A}]...)

or

SORT FIELDS=({begcol},{length},{D|A}[,{begcol},{length}, {D|A}]...),{fieldtype}

The beginning column is specified in bytes, starting with 1 for the first byte. The length of the field must be specified in bytes, irrespective of the field type. There are many field types. The frequently used ones are CH for character, BI for binary (COMP fields of COBOL), PD for packed decimal (COMP-3 fields of COBOL), ZD for zone decimal and AQ for alternate collating sequence (refer to later section on alternate collating sequence). The second form of the SORT statement can be used when all the fields specified for the sort sequence are of the same type.

2.2 MERGE

The MERGE statement can be used merge two or more pre-sorted datasets. The MERGE statement requires the sort sequence for the data. The list of fields and their formats must be specified for this statement. The format of the MERGE statement is as follows.

MERGE FIELDS=({begcol},{length},{fieldtype},{D|A}[,{begcol}, {length},{fieldtype},{D|A}]...)

or

MERGE FIELDS=({begcol},{length},{D|A}[,{begcol},{length}, {D|A}]...),{fieldtype}

Please refer to SORT statement above for description of the statement.

2.3 INCLUDE AND OMIT

The INCLUDE statement can be used to specify the conditions for inclusion of records from the input during processing. The OMIT statement can be used to specify the conditions for exclusion of records from the input during processing. Both statements cannot be used together. The different formats of the INCLUDE and OMIT statements are shown below.

INCLUDE COND=({begcol1},{length1},{fldtype1},{comp.oper}, {begcol2},{length2},{fldtype2})

The above statement can be used to compare two fields within the same record. The valid comparison operators are EQ, NE, GT, LT, LE and GE.

INCLUDE COND=({begcol},{length},{fldtype},{comp.oper}, {constant})

The above statement can be used to compare a field in the record with a character, decimal or hexadecimal constant.

Using the convention that {cond.stmt} is the part of statement between the parenthesis in either of the statements mentioned above, compound statements can be constructed as follows.

INCLUDE COND=({cond.stmt},[{OR|AND},{cond.stmt}]...)

Parenthesis can be used to group conditional statements to form complex conditions. For OMIT statements replace the INCLUDE verb by OMIT verb in the above examples.

2.4 INREC AND OUTREC

The INREC statement can be specified to reformat the input records before SYNCSORT processes them for SORT or MERGE. The OUTREC statement can be specified to reformat the processed records into the required layout for the output records or the report that is generated. The format for the INREC and OUTREC statements are similar.

INREC FIELDS=([{outpos}:]{begcol},{length} [,[{outpos}:]{begcol},{length}]...)

The Outpos description is optional. This specifies the position in the output record where the field must be placed. The default is to place it at the current position in the output record, placing the first field specified at column 1.

The individual numeric fields can also be reformatted from any form to zone decimal. An example syntax is given below.

INREC FIELDS=({begcol},{length},{fldtype},EDIT=M#)

 or

INREC FIELDS=({begcol},{length},{fldtype},EDIT=SIII,IIT.TT)

Default picture clauses are provided and named M0 to M9. Customized picture clauses can be specified by using appropriate syntax. In the example above, the S character is for sign field, I is equivalent of Z PIC clause of COBOL and T is equivalent of 9 PIC clause. Conventions for the sign displayed for numeric fields can be specified after the edit parameter, using SIGNS parameter.

Constant fields can also be introduced in the record. For example, spaces or zeroes can be placed in the record at specific positions.

NOTE: When using INREC fields, the column positions and lengths in the SORT, MERGE and SUM statements must reflect the output from the INREC processing.

2.5 SUM

The SUM statement can be used to summarize data based on the SORT statement. One record will be produced for each unique key present in the input. If numeric fields are specified for summation, those fields will be summed up. The SYNCSORT software does not guarantee unique records if numeric fields are required to be summed up. Whenever there is an overflow of a numeric field, more than one record may be created. The syntax is as follows.

SUM FIELDS=NONE

or

SUM FIELDS=({begcol},{length},{fldtype}[,{begcol},{length}, {fldtype}]...)

The first format is used when duplicate records need to be removed and no numeric summation is required. The second format is used when numeric summation is required when duplicate records exist.

2.6 ALTERNATE COLLATING SEQUENCE

The alternate collating sequence can be specified using the ALTSEQ statement. This will help the user in sorting records in a different sequence than the EBCDIC character set. This may be required in situations where the character codes for fields, that the user intends to sort on, are not in EBCDIC collating sequence.

ALTSEQ CODE=({hexcode}{newhexcode}[,{hexcode}{newhexcode}]...)

The new hex code will be used for the sorting or merging process only for the appropriate hex code specified for it. The new hex code will not replace the hex codes in the output or the reports.

2.7 OPTION STATEMENT

The option statement can be used to control parameters during SORT, MERGE and SUM processing.

2.7.1 EQUALS AND NOEQUALS

The default of NOEQUALS specifies that SYNCSORT need not retain the order of input data when duplicate record keys are found. OPTION EQUALS should be used if the order of input data must be maintained during the SORT processing.

This parameter will affect the non-summation data in SUM processing. When EQUALS is used the data for the non key fields are taken from the first input record for that key value. When NOEQUALS is specified the data for the non keyfields are unpredictable.

2.7.2 RECORD

The record option of the OPTION statement can be used to specify if the input data to be processed are Variable length records or Fixed length records. This is required when both the input and output from the SORT or MERGE processing are VSAM files. The valid values are RECORD=V and RECORD=F.

2.7.3 SKIPREC

The SKIPREC parameter of the OPTION statement can be used to specify the number of records of input to skip before any processing should begin. SKIPREC=20 specifies that the first 20 records of the input must be skipped.

2.7.4 STOPAFT

The STOPAFT parameter of the OPTION statement can be used to specify the number of records to be included for processing. STOPAFT=100 specifies that SYNCSORT stop taken any more input after 100 records that match the criteria are selected.

2.7.5 COPY The COPY parameter can be used if a simple COPY operation is required. If neither SORT processing nor MERGE processing is required this is ideal to use. The parameter when combined with SKIPREC and STOPAFT helps copy selected records to output based on number of records. If COPY is combined with INCLUDE or OMIT condition statements, selected records can be copied to output based on specific conditions in field values. When COPY is combined with INREC or OUTREC processing (INREC is more efficient in this case), a reformatted output can be produced.

2.8 OUTFIL

The OUTFIL statement can be used to produce multiple output datasets. This statement must be used if elaborate formatting is required, like producing reports. One OUTFIL statement is required for each output dataset. The FILE parameter specifies the DD name suffix to be used for the dataset output.

Each output dataset can have its own INCLUDE or OMIT condition and its own INREC and OUTREC parameters. Further, report formatting is available, including 3 levels of HEADER#, 3 levels of TRAILER#, summation, Section processing and Section breaks. For example, this statement can help create separate reports for each department into a different dataset or sysout and route them to the appropriate destination.

The SYNCSORT manual should be referred if the OUTFIL statement is required.

3. JCL REQUIREMENTS

The different DD statements required for the SORT step are as follows.

                3.1.1.1 SYSIN               It should point to the SYNCSORT control statements mentioned above.

SYSOUT It should point to a dataset or SYSOUT. This is where the SYNCSORT messages are placed.

SORTIN This dataset should point to the input dataset(s) for the sort process.

SORTIN## These statements should refer to the individual datasets to be merged. These individual datasets are required to be in pre-sorted order.

SORTOUT This dataset should point to the dataset where the output must be placed.

SORTOUT# These datasets should point to the individual output datasets referred in the OUTFIL statements.

SORTOT## The same as SORTOUT#. There must be a one to one correspondence between the FILES parameter in the OUTFIL statement and the list of DD statements specified.

SORTWK## These statements should refer to temporary volumes with appropriate space parameters depending on the volume of data to be processed.

4. PROCESSING ORDER

The processing order of the control statements by SYNCSORT is as follows.

                1. INCLUDE or OMIT condition statement processing.

                2. INREC statement processing.

                3. SORT, MERGE or COPY processing (including alternate collating sequence processing for SORT and MERGE).

                4. SUM statement processing.

                5. OUTREC processing.

The processing order will drastically change if OUTFIL statement is present in the SYSIN of SYNCSORT. The processing is very complex if the OUTFIL statements use different INREC statements and different INCLUDE or OMIT statements.

CICS Reviewer Part 4

What is the difference between getting the system time with EIBTIME and ASKTIME command?
The ASKTIME command is used to request the current date and time. Whereas, the EIBTIME field have the value at the task initiation time.

What does it mean when EIBCALEN is equal to zeros?
When the length of the communication area (EIBCALEN) is equal to zeros, it means that no data was passed to the application.

How can the fact that EIBCALEN is equal to zeros be of use to an application programmer?
When working in psuedo-conversational mode, EIBCALEN can be checked if it is equal to zero to determine first time usage of the program.

When you do the START command what will be the value of EIBCALEN?
Zero

What is the meaning and use of the EIBAID field?
EIBAID is a key field in the execute interface block; it indcates which attention key the user pressed to initiate the task.

When you compile a CICS program, the pre-compiler puts an extra chunk of code. Where does it get included and that is it called?
DFHEIBLK, DFHCOMMAREA

Name some important fields in the EIB block?
EIBRESP, EIBCALEN, EIBRRCDE, EIBTASK, EIBDATE, EIBTIME, EIBRCODE

Which is the EIB field that gives the date when a task was started?
EIBDATE

The EIB field which gives the last CICS command executed?
EIBRCODE

What is the default commarea size?
65K

How do you handle error in CICS programs?
Check EIBRESP after the call or use the HANDLE condition.


What does the following transactions do?
CEDF: CICS-supplied Execution Diagnostic Facility transaction. It provides interactive program execution and debugging functions of a CICS programs.
CEMT: CICS-supplied Extended Master Terminal transaction. It displays or manipulates CICS control environment interactively.
CEBR: CICS-supplied Temporary Storage Browse transaction. It displays the content of Temporary Storage Queue (TSQ).
CECI: CICS-supplied Command Interpreter transaction. It verifies the syntax of a CICS command and executes the command.

Name and explain some common CICS abend codes?
AEI_ - indicates an execute interface program problem - the abending program encountered an exceptional condition.
APCT - the program could not be found or disabled.
ASRA - CICS interrupt code, the equivalent of SOC7 or SOC4 MVS abend code. It indicates a program check, identified by a one-byte code in the Program Status Word in the dump.
AKCP - the task was cancelled; it was suspended for a period longer than transaction's defined deadlock timeout period.
AKCT - the task was cancelled because it was waiting too long for terminal input.
AICA -  Runaway task.

CICS Reviewer Part 3

What are the CICS commands available for program control services?
1. LINK: To pass control to another program at the lower level, expecting to be returned.
2. XCTL: To pass control to another program at the same level, not expecting to be returned.
3. RETURN: To return to the next higher-level program or CICS.
4. LOAD: To load a program.
5. RELEASE: To release a program.

Explain the various ways data can be passed between CICS programs.
Data can be passed in three ways - COMMAREA, Transient Data Queue (TDQ) and Temporary Storage Queue (TSQ)

Data can be passed to a called program using the COMMAREA option of the LINK or XCTL command in a calling program. The called program may alter the data content of COMMAREA and the changes will be available to the calling program after the RETURN command is issued in the called program. This implies that the called program does not have to specify the COMMAREA option in the RETURN command.

If the COMMAREA is used in the calling program, the area must be defined in the Working Storage Section of the program (calling), whereas, in the called program, the area must be defined as the first area in the Linkage Section, using reserved name DFHCOMMAREA.

What are the differences between TSQ and TDQ?
Temporary Storage Queue names are dynamically defined in the application program, while TDQs must first be defined in the DCT (Destination Control Table). When a TDQ contains certain amount of records (Trigger level), a CICS transaction can be started automatically. This does not happen when using a TSQ. TDQ (extra partition) may be used by batch application; TSQ cannot be accessed in batch. The Transient Data Queue is actually a QSAM file. You may update an existing item in a TSQ. A record in a TDQ cannot be updated. Records in TSQ can be read randomly. The TDQ can be read only sequentially. Records in Temporary Storage can be read more than once, while records in Temporary Data Queues cannot, With TDQs it is a "one read" only.

Explain the basic difference between the Intra partition TDQ and Extra partition TDQ.
Intra Partition TDQs is a group of sequential records which are produced by the same and/or different transactions within a CICS region. These Qs are stored in only one physical file (VSAM) in a CICS region, which is prepared by the system programmer. Once a record is read from a queue, the record will be logically removed from the queue; that is the record cannot be read again.
Extra Partition TDQs is a group of sequential records which interfaces between the transactions of the CICS region and the system outside of CICS region. Each of these TDQs is a separate physical file, and it may be on the disk, tape, printer or plotter,

CICS Reviewer Part 4 is continued here.

CICS Reviewer Part 2

What is the function of the EXEC CICS HANDLE CONDITION command?
To specify the paragraph or program label to which control is to be passed if the "handle condition" occurs.

How many conditions can you include in a single HANDLE CONDITION command?
No more than 16 in a single handle condition. If you need more, then you must code another HANDLE CONDITION command.

What is the EXEC CICS HANDLE ABEND?
It allows the establishing of an exit so cleanup processing can be done in the event of abnormal task termination.

What is the difference between EXEC CICS HANDLE CONDITION and an EXEC CICS IGNORE command?
A HANDLE CONDITION command creates a "go-to" environment. An IGNORE command does not create a go-to environment; instead, it gives control back to the next sequential instruction following the command causing the condition. They are opposites.

What happens when a CICS command contains the NOHANDLE option?
No action is going to be taken for any exceptional condition occuring the execution of this command. The abnormal condition that occurred will be ignored even if an EXEC CICS HANDLE condition exist. It has the same effect as the EXEC CICS IGNORE condition except that it will not cancel the previous HANDLE CONDITION for any command.

When a task suspends all the handle conditions via the PUSH command, how does the task reactivate all the handle conditions?
By coding an EXEC CICS POP HANDLE command.

Explain re-entrancy as applies to CICS.
Reentrant program is a program which does not modify itself so that it can reenter to itself and continue processing after an interruption by the operating system which, during the interruption, executes other OS tasks including OS tasks of the same program. It is also called "reenterable" program or "serially reusable" program.

A quasi-reentrant program is a reentrant program under the CICS environment. That is, the quasi-reentrant program is a CICS program which does not modify itself. That way it can reenter to iself and continue processing after an interruption by CICS which, during the interruption, executes other tasks including CICS tasks of the same program. In order to maintain the quasi-reentrancy, a CICS application program must follow the following convention:

Constants in Working Storage: the quasi-reentrant program defines only constants in its ordinary data area (e.g. working storage section). These constant will never be modified and shared by the tasks.

Variable in the Dynamic Working Storage: The quasi-reentrant program acquires a unique storage are (called Dynamic Working Storage - DWS) dynamically for each task by issuing the CICS macro equivalent GETMAIN. All variables will be placed in this DWS for each task. All counters would have to be initialized after the DWS has been acquired.

Restriction on Program Alteration: The program must not alter the program itself. If it alters a CICS macro or command, it must restore the alteration before the subsequent CICS macro or command.

CICS Reviewer Part 3 is continued here.

CICS Reviewer Part 1

Customer Information Control System (CICS)

How can you start a CICS transaction other than by keying the Transaction ID at the terminal?
By coding an EXEC CICS START in the application program
   1. By coding the Trans ID and a trigger level on the Destination Control Table (DCT)
   2. By coding the Trans ID in the EXEC CICS RETURN command
   3. By associating an attention key with the Program Control Table (PCT)
   4. By embedding the TRANSID in the first four position of a screen sent to the terminal
   5. By using the Program List Table (PLT)

What is the purpose of the PLT?
The Program List Table records the set of application programs that will be executed automatically at CICS start-up time.

What are the differences between EXEC CICS XCTL and EXEC CICS START command?
The XCTL command transfer control to another application (having the same Transation ID), while the START command initiates a new transaction ID (therefore a new task number). The XCTL continues task on the same terminal. START can initiate a task on another terminal.

What are the differences between an EXEC CICS XCTL and EXEC CICS LINK command?
The XCTL command transfer control to an application at the same logical level (do not expect to control back), while the LINK command passes control to an application program at the next logical level and expects control back.

What happens to resources supplied to a transaction when an XCTL command is executed?
With an XCTL, the working storage and the procedure division of the program issuing the XCTL are released. The I/O areas, the GETMAIN areas and the chained Linkage Section areas (Commarea from a higher level) remain. All existing locks and queues also remain in effect. With a LINK, however, program storage is also saved, since the ttransaction expects to return and use it again.

What CICS command do you need to obtain the used logon-id?
You must code EXEC CICS ASSIGN with the OPERID option.

What is a resident program?
A program or map loaded into the CICS nuclues so that it is kept permanently in main storage and not deleted when CICS goes on "Short On Storage".

What is EIB. How it can be used?
CICS automatically provides some system-related information to each task in a form of EXEC Interface Block (EIB), which is unique to the CICS command level. We can use all the fields of EIB in our application programs right away.

What are some of the information available in the EIB area?
1. The cursor position in the map
2. Transaction ID
3. Terminal ID
4. Task Number
5. Length of communication area
6. Current date and time
7. Attention identifier

What information can be obtained from the EIBRCODE?
The EIBRCODE tells the application program if the last CICS command was executed successfully and, if not, why not.

What is the effect of including the TRANSID in the EXEC CICS RETURN command?
The next time the end user presses an attention key, CICS will start the transaction specified in the TRANSID option.

CICS reviewer Part 2 is continued here.

DB2 Reviewer Part 1

What is a DB2 Utility and what are the different kinds?
•    Allow you to control the operation of DB2 utility programs, which run separately from the DB2 processor. Using the task panel, you can create and submit a utility job, create and edit a utility job, display the STATUS of or STOP a utility.

• CHECK DATA    -    Indicates which rows in the table space are to be checked for referential constraints.

• CHECK INDEX     -    Tests whether indexes are consistent with the data they index, and issues a warning message if it finds an error.

• CHECK LOB     -    Identifies any structural defects in a LOB table space and any invalid LOB values.

• COPY     -    Copies data from a table space to a sequential data set. It can be used to copy the entire space or only the data that changed since the last copy was made.

• DIAGNOSE     -    Generates information useful in diagnosing problems.

• LOAD     -    Loads data from a sequential data set to DB2 tables.

• MERGECOPY     -    Merges several partial copies of a table space into one copy and merges partial copies with a full image copy to make a new full image copy.

• MODIFY     -    Deletes a row from the SYSIBM.SYSCOPY catalog table and the SYSIBM.SYSLGRNG directory table.

• QUIESCE    -    Establishes a point of recovery for a table space, partition, table space set or a list of table spaces and table space sets.

• REBUILD INDEX    -    Reconstructs indexes from the table they reference.

• RECOVER     -    Recovers data to the current state or to a previous point in time by restoring to an image copy, then applying log records.

• REORG INDEX     -    Reorganizes indexes & unloads data in the format acceptable to the LOAD utility.

• REORG TABLESPACE     -    Reorganizes table spaces & unloads data in the format acceptable to the LOAD utility.

• REPAIR     -    Allows you to change specific values within a DB2 database without using SQL.

• REPORT     -    Reports table space information, including recovery history from SYSIBM.SYSCOPY catalog table, log ranges from SYSIBM.SYSLGRNG, volume serial numbers where archive log datasets reside, and name of all table spaces in a table space set.

• RUNSTATS     -    Gathers summary information about the characteristics of data in table spaces and indexes.

• STOSPACE    -    Updates DB2 catalog columns that indicate how much space is allocated for storage groups and related table spaces and indexes.

• UNLOAD    -    Unloads data from one or more table spaces or image copy data sets to one or more sequential datasets in an external format.

Explain different utilities used:
•    LOAD – is used to load one or more tables of a table space. It loads records into the tables and builds or extends any indexes defined on them. If the table space already contains data, you can choose whether you want to add the new data to the existing data (LOAD RESUME) or replace the existing data (LOAD REPLACE). The loaded data is processed by any edit or validation routine associated with the table, and any field procedure associated with any column of the table

•    RUNSTATS - The RUNSTATS online utility gathers summary information about the characteristics of data in table spaces, indexes, and partitions. DB2 records this information in the DB2 catalog and uses it to select access paths to data during the bind process. It is available to the database administrator for evaluating database design and to aid in determining when table spaces or indexes must be reorganized.  There are two formats for the RUNSTATS utility: RUNSTATS TABLESPACE and RUNSTATS INDEX. RUNSTATS TABLESPACE gathers statistics on a table space and, optionally, on indexes or columns; RUNSTATS INDEX gathers statistics only on indexes.  To update the catalog statistics, run the RUNSTATS utility after a LOAD or REBUILD INDEX job.

•    REPAIR - The REPAIR online utility repairs data. The data can be your own data, or data you would not normally access, such as space map pages and index entries.  It can be used to:
o    Reset a pending status on a table space or index
o    Verify the contents of data areas in table spaces and indexes
o    Replace the contents of data areas in table spaces and indexes
o    Delete a single row from a table space
o    Produce a hexadecimal dump of an area in a table space or index
o    Delete an entire LOB from a LOB table space
o    Dump LOB pages

•    QUIESCE - The QUIESCE online utility establishes a quiesce point (the current log RBA or log record sequence number (LRSN)) for a table space, partition, table space set, or list of table spaces and table space sets, and records it in the SYSIBM.SYSCOPY catalog table. A successful QUIESCE improves the probability of a successful RECOVER or COPY. You should run QUIESCE frequently between regular executions of COPY to establish regular recovery points for future point in time recovery

After loading a table, what other steps needs to be done?
•    You need to run RUNSTATS. RUNSTATS update the DB2 catalog of the table. It provides statistics on the table for use for optimization during bind

When loading the table, what will happen to the current records in the table?
•    It depends on the LOAD parameter you will use. If you use LOAD RESUME, the records loaded will be appended to the existing records. If you use LOAD REPLACE, the records loaded will overlay the records.

What is EXPLAIN?
•    EXPLAIN is used as a BIND option [EXPLAIN (YES)]
•    Can be invoked by executing the SQL EXPLAIN statement either statically or dynamically (QMF or SPUFI).
•    EXPLAIN is a monitoring tool that obtains information about the access paths for all explainable SQL statements in a package or the DBRMs of a plan (the information appears in table PLAN_TABLE); obtains information on estimated cost of executing an SQL SELECT, INSERT, UPDATE or DELETE statement (the information appears in table DSN_STATEMENT_TABLE); obtains information on User-defined functions referred to in a SQL statement, which includes the specific name and schema (the information appears in table DSN_FUNCTION_TABLE).
Information gathered by EXPLAIN can be of help for designing databases, indexes, and application programs.  This information can also tell when to rebind an application or can be of help in determining the access path chosen for a query. 

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.

What is SPUFI?
•    SPUFI is short for SQL Processor Using File Input.
•    SPUFI processes SQL statements that are not embedded in a program. It is especially useful for granting an authorization or creating a table when a host language is not necessary and for testing statements that are to be embedded in a program later. You may name a new member of a previously allocated library. Using the ISPF/PDF editor, you may enter one or more SQL statements to be processed. Each statement must end with a semicolon. Other choices on the task panel allow you to process the statement(s), automatically commit (or not) any changes to the database, and browse the output.

What is QMF?
•    QMF is short for Query Management Facility.
•    Query Management Facility is an interactive query product that allows you to create reports and charts from relational data under MVS/XA and MVS/ESA. With QMF, you can:
o    Access data kept in DB2 tables
o    Perform calculations on that data
o    Insert new data and change or delete existing data
o    Produce and print customized reports for your data
o    Create and format charts
o    Communicate with other products

What are the different DB2 SQLCODES you have encountered?
•    +100 - No row met the search conditions specified in an UPDATE or DELETE statement; The result of a SELECT INTO statement was an empty table; A FETCH statement was executed when the cursor was positioned after the last row of the result table; The result of the subselect of an INSERT statement is empty.
•    +0     -    found condition / no error condition
•    -803  -    Execution of the requested INSERT or UPDATE would result in duplicate values occurring on the unique index.  The update might also be caused by a DELETE operation of a parent row that cascades to a dependent row with a delete rule of SET NULL.
•    -811 -    Execution of an embedded SELECT statement or a subselect in the SET clause of an UPDATE statement has resulted in a result table that contains more than one row. Alternatively, a subquery contained in a basic predicate has produced more than one value.
•    -818 -    The precompiler-generated timestamp in the load module is different from the bind timestamp in the DBRM.
•    -805 -    An application program attempted to use a DBRM or package that was not found on the plan.
•    -911 -    The current unit of work was the victim in a deadlock, or experienced a timeout, and had to be rolled back
•    -904 – Unsuccessful execution caused by unavailable resource.

How would you correct an SQLCODE of –803?
•    Make sure that a unique index is used
•    Change the error handling logic inside the application (e.g. If SQLCODE= -803 Then Exec SQL Update…)
•    In the application, do a SELECT prior to INSERT or UPDATE.  If row is found, do an UPDATE. Otherwise, do an INSERT.

How would you handle an SQLCODE of –811?
•    Although not advisable, the easiest way of eliminating a –811 is by using the DISTINCT clause on the SELECT statement (e.g. Exec SQL Select DISTINCT…)
•    Check if the WHERE clause used on the SELECT comprises the unique index of the table. If not unique, either ALTER the index of the table or change the WHERE clause on the application such that it uses all columns of the unique index.
•    If multiple rows is really expected, Utilize a CURSOR

What are the different logical steps in employing a cursor?
•    DECLARE CURSOR
•    OPEN
•    Perform a FETCH routine until SQLCODE is +100
•    CLOSE CURSOR

When will you use SELECT or CURSOR?
•    If the query returns only one row, use SELECT. To ensure one row is returned, WHERE clause should be using the unique index columns.
•    When the query returns multiple rows and you want to process each row, use a CURSOR, if you just want to check if at least one row exists, use SELECT with a clause of FETCH FIRST 1 ROW ONLY or using DISTINCT function.

What will happen if I issue a COMMIT on a CURSOR?
•    CURSOR will be close when COMMIT is issued.
•    CURSOR will NOT be closed is the CURSOR has a WITH HOLD option.

What would you do if you encountered an SQLCODE +100 on OPEN cursor?
•    There is no SQLCODE +100 on OPEN cursor

What is SQLCODE –502?  Would the application abend if not handled?
•    Error is raised by DB2 when an OPEN cursor is executed to an already open cursor
•    No data changes, the application does not abend

What is an SQLCODE –501?  Would the application ABEND if not handled?
•    SQLCODE is raised when a CLOSE cursor is executed on a cursor that is not open
  No data changes, the application does not ABEND.

What causes an SQLCODE –818?  How would you handle/correct an SQLCODE –818?
 •  The SQL pre-compiler places timestamp in the DBRM and in the load module of the application program. At run-time, the two timestamps are compared with each other. If the two timestamps do not match, the DBRM and the load module were not the result of the same pre-compile.  An –818 condition is then raised.
•    Recompile and bind the program

What are the different causes of an SQLCODE –805?  How would you handle/correct this error?
•    The DBRM name was not found in the member list of the plan and there is no package list for the plan.
•    The package name was not found because there is no package list for the plan.
•    The 'location-name' in the package list was not correct when the application was bound.
•    The application was not connected to the proper location.
•    Rebind would correct the error
•    If due to wrong ‘location name’ or connected to the wrong location, make sure that the application connects to the right location at runtime.

What is deadlock (give a scenario)?  What can you do with an SQLCODE –911?
•    PROGRAM A has ROW Y locked and PROGRAM B has ROW X locked.  Later on, PROGRAM A requests for ROW X and was suspended since PROGRAM B currently locks the row.  Then PROGRAM B request for ROW Y and was suspended since PROGRAM A currently locks the row.  At this point, PROGRAM A and PROGRAM B are in DEADLOCK.  DB2 would then try to rollback changes made by both programs.  If the rollback is successful, an SQLCODE –911 is raised.  Otherwise, an SQLCODE –913 is raised.
•    Rerun the program and hope that the scenario above is not repeated.
•    Use the WITH UR clause on the SELECT statements on both programs for minimal row locking.

What causes an SQLCODE –904? How do you fix it?
•    Unsuccessful execution caused by unavailable resource.
•    Somebody must have loaded the table and is still in the error status. Possible error status are CHECK PENDING, RECOVER PENDING, COPY PENDING. Other possibility is the LOAD job is still running.
•    Tablespace or index is in STOP status.
•    There could be several reasons for this, but usually it is one of three:
1. An image copy needs to be taken. DB2 turns the copy pending flag on if the DB2 load utility has been run with the parameter "log no". Since "log no" does not log transactions to the system log, DB2 requires an image copy be performed. This gives DB2 a starting place in case recovery needs to be run at some later time. If an image copy was not taken, the rows inserted with the load utility (with "log no") would be lost.

2. Contention with another program. DB2 allows concurrency. This means more than one process can update a table at the same time. But long running update programs can change this. If an update program is started, every time an update happens a lock is placed on the CI (or row if row level locking is being used). DB2 only has a certain amount of locks. Once this maximum threshold of locks is used, DB2 escalates to a tablespace lock. This means only the process that caused the escalation can use the table. All other processes will receive a -904.

3. A DB2 utility is being executed. Certain DB2 utilities require exclusive control of the table. If a process is started while one of these is executing, a -904 is issued.

What is the difference between BIND, REBIND and FREE?
•    Modifies the relationship between the DB2 plan/package and the associated application.
o    Bind - Establishes or replaces an application plan/package.
o    Rebind - Modifies an existing application plan/package.
o    Free - Deletes an application plan/package.

•    BIND (not to be confused with REBIND) uses as input the SQL/DBRM in the DBRM Library. The assumption with BIND is that the program source code has been changed and that a new DBRM exists.

•    REBIND uses the current copy of the DBRM in the DB2 Catalog. The assumption is that the program has not changed but that something external to the program has. REBINDs are normal for situations where indexes are added or dropped or RUNSTATS has been run and the statistics have changed significantly. Probably the most dramatic external change is the installation of a new version of DB2

What does BIND do?
•    Check authorizations
•    Check DB2 access paths
•    Creates the most effective path

When do you need to REBIND?
•    Table change
•    DB2 upgrade

What is a plan? When will it be created?
•    A PLAN is a control structure that is used to execute SQL statements.  The control structures can be thought of as the bound or operational form of SQL statements taken from a database request module (DBRM).  It can also contain a list of package names.  It relates an application process to a local instance of DB2, specifying the processing options.
•    The control structure that is contained inside a plan is generated when a BIND PLAN or a BIND PACKAGE is executed.  Thus, a PLAN is created after the successful execution of a DB2 BIND subcommand.

What is COMMIT and ROLLBACK?
•    Both operations ends a unit of work
•    COMMIT is an operation that ends a unit of work by releasing locks so that the database changes that are made by that unit of work can be perceived by other processes
•    ROLLBACK is the process of restoring data changed by SQL statements to the state at its last commit point. All locks are freed.

On a batch program that writes a record to a file and at the same time updates a DB2 table, what will happen when a DB2 error occurs? What will happen to the records written into the sequential file?
•    The DB2 updates are rolled back but the sequential file inserts stays.

Can we use EXEC SQL COMMIT or EXEC SQL ROLLBACK on a CICS/DB2 application?
•    You must not use EXEC SQL COMMIT or EXEC SQL ROLLBACK commands in a CICS application. Since CICS is the coordinator of the two phase commit process, you must use EXEC CICS SYNCPOINT or EXEC CICS SYNCPOINT ROLLBACK commands instead.

On a CICS program that updates a VSAM file and at the same time updates a DB2 table, what will happen when a DB2 error occurs? What will happen to the records inserted into the VSAM file?
•    The DB2 updates and VSAM updates are rollbacked.
•    Issue EXEC CICS SYNCPOINT to commit both records. Exec CICS Syncpoint command is called a two phase Commit because CICS will first commit changes to the resources under its control like VSAM files, before DB2 changes are committed. Usually CICS signals DB2 to complete the next phase and release all the locks.

What is the basic difference between a JOIN and a UNION?
•    A join selects columns from 2 or more tables.
•    A union selects rows from 2 or more queries.

What is the difference between UNION and UNION ALL?
•    A UNION does not return duplicates.
•    A UNION ALL returns duplicates.

When you do UNION, are the results ordered?
•    No, it is not ordered.

What are the different types of JOINS?
•    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.
•    Outer join : Is one in which you want both matching and non matching rows to be returned. DB2 Database has no specific operator for outer joins, it can be simulated by combining a join and a correlated sub query with a UNION.

What is COALESCE? What is VALUE?
•    The COALESCE function returns the value of the first no null expression.
•    The arguments are evaluated in the order in which they are specified, and the result of the function is the first argument that is not null. The result can be null only if all arguments can be null. The result is null only if all arguments are null.
•    VALUE can be specified as a synonym for COALESCE.
Example: This example selects title and price for all books. If the price for a given title is NULL, the price shown is 0.00.
SELECT title, COALESCE(price, 0.00) AS price FROM titles;

What are null indicators? and what are the possible values in the variable and what do they mean?
•    A NULL INDICATOR is an indicator variable that could be used to determine:
o    whether the value of an associated output host variable is null or indicate that an input host variable value is null. Value = -1.
o    the original length of a character string that was truncated during assignment to a host variable. Value = +n. Where n is the original length of the field.
o    The value returned is not null. Value = 0.

•    Null indicators are defined as binary, PIC S9(4) COMP.
Example: This example selects title and author for all books and the author for a given title is NULLABLE. Ws-author-null-indicator is the null variable.
SELECT title, author
INTO :ws-title
         ,:ws-author:ws-author-null-ind
FROM titles;

What will happen if you forgot NULL indicator is missing?
•    If a null indicator is not used, an SQLCODE -305 is returned.

What SQLCODE will you get if you have a NULL INDICATOR coded?
•    It will always be zeroes.

In the sample query below, if ACCOUNT is NULL, will it be returned?
SELECT ACCOUNT
FROM TABLE_1
WHERE ACCOUNT < ‘00000000’
•    No, it will not be returned. You should add addition criteria in the WHERE clause of OR ACCOUNT IS NULLS.

What is GTT?
•    GTT is short for Global Temporary Table. It is use store immediate SQL results when you don’t want to store it in a permanent table.
•    Two types of GTT are Created Temporary Table and Declared Temporary Table.

•    Created Temporary Tables
A created temporary table exists only as long as the process that uses it. Temporary tables are created using the CREATE GLOBAL TEMPORARY TABLE statement. When created, the schema for the table is stored in the DB2 system catalog (SYSIBM.SYSTABLES) just like any other table, but the TYPE column is set to 'G' to indicate a global temporary table. Created temporary tables are sometimes referred to as global temporary tables – but this is confusing since declared temporary tables are also referred to as global declared tables.

It is important to remember that a created global temporary table must be created using a DDL CREATE statement before it can be used in any program.

A created temporary table is instantiated when it is referenced in an OPEN, SELECT INTO, INSERT, or DELETE statement, not when it is created. Each application process that uses the temporary table creates a new instance of the table for its use. When using a created temporary table, keep the following in mind:
o    Because they are not persistent, some typical database operations including locking, logging, and recovery do not apply to created temporary tables.
o    Indexes can not be created on created temporary tables so all access is by a complete table scan.
o    Constraints can not be created on created temporary tables.
o    A null is the only default value permitted for columns of a created temporary table.
o    Created temporary tables can not be referenced by DB2 utilities.
o    Created temporary tables can not be specified as the object of an UPDATE statement.
o    When deleting from a created temporary table, all rows must be deleted.
o    Although views can be created on created temporary tables, the WITH CHECK OPTION can not be specified.

Work file data sets are used to manage the data of created temporary tables. The work database (DSNDB07) is used as storage for processing SQL statements that require working storage – not just for created temporary tables. So if you are using created temporary tables be sure to examine the DB2 Installation Guide for tactics to estimate the disk storage required for temporary work files.
When a temporary work file result table is populated using an INSERT statement, it uses work file space. No other process can use the same work file space as that temporary work file table until the table goes away. The space is reclaimed when the application process commits or rolls back, or when it is deallocated, depending which RELEASE option was used when the plan or package was bound. It is a good idea to keep the work files in a separate buffer pool to make it easier to monitor. IFCID 0311 in performance trace class 8 can be used to distinguish these tables from other uses of the work file.

•    Declared Temporary Tables
With Version 7 of DB2, IBM introduced declared temporary tables. Actually, to be more accurate, declared temporary tables were made available in the intermediate DB2 Version 6 refresh.
This new type of temporary table is different than a created temporary table and overcomes many of their limitations. The first significant difference between declared and created temporary tables is that declared temporary tables are specified using a DECLARE statement in an application program – and not using a DDL CREATE statement. Because they are not persistent they do not have descriptions in the DB2 Catalog. 

Additionally, declared temporary tables offer significant features and functionality not provided by created temporary tables. Consider:
o    Declared temporary tables can have indexes and CHECK constraints defined on them.
o    You can issue UPDATE statements and positioned DELETE statements against a declared temporary table.
o    You can implicitly define the columns of a declared temporary table and use the result table from a SELECT.

So declared temporary tables are much more functional than created temporary tables. To “create” an instance of a declared temporary table you must issue the DECLARE GLOBAL TEMPORARY TABLE statement inside of an application program. That instance of the declared temporary table is known only to the process that issues the DECLARE statement. Multiple concurrent programs can be executing using the same declared temporary table name because each program will have its own copy of the temporary table.
But there is more work required to use a declared temporary table than there is to use a created temporary table. Before you can declare temporary tables you must create a temporary database and table spaces for them to use. This is accomplished by specifying the AS TEMP clause on a CREATE DATABASE statement. Then, you must create segmented table spaces in the temporary database. Only one temporary database for declared temporary tables is permitted per DB2 subsystem.

When a DECLARE GLOBAL TEMPORARY TABLE statement is issued, DB2 will create an empty instance of the temporary table in the temporary table space. INSERT statements are used to populate the temporary table. Once inserted, the data can be accessed, modified, or deleted. When the program completes, DB2 will drop the instance of the temporary table. Also, be aware that users of temporary tables must have been granted USE authority on the temporary table space.

What is the difference between CHAR and VARCHAR?
•    The char is a fixed-length character data type, the varchar is a variable-length character data type.
•    Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column.
•    You can use char when the data entries in a column are expected to be the same size. Maximum length of 254.
•    You can use varchar when the data entries in a column are expected to vary considerably in size. Maximum length is 32,672.

How do you define the different DB2 data-types?
•    SMALLINT or BINARY PIC S9(4) COMP.
•    INTEGER            PIC S9(9) COMP.
•    DECIMAL(m,n)       PIC S9(m)v9(n) COMP-3.
•    CHAR(n)            PIC X(n)
•    VARCHAR(n)         01 VARCHAR           
                           49 LENGTH    PIC S9(4) COMP.
                           49 TEXT      PIC X(n).
•    DATE               PIC X(10).
•    TIME               PIC X(8).
•    TIMESTAMP          PIC X(26).

What are SCALAR and COLUMN functions?
•    Column functions are an operation that derives its result by using values from one or more rows and produces a single value.
Example:
AVG
COUNT
MAX
MIN
SUM
DISTINCT

•    Scalar functions return values to each row.
Example:
CHAR
COALESCE
DATE
DAY
DAYS
DECIMAL
DIGITS
FLOAT
HEX
HOUR
INTEGER
LENGTH
MICROSECOND
MINUTE
MONTH
SECOND
SUBSTR
TIME
TIMESTAMP
VALUE
VARGRAPHIC
YEAR
What is SUBSTR function?
•    The SUBSTR function returns a substring of a string.
•    SUBSTR is used for string manipulation with column name, first position and string length used as arguments.  Eg. SUBSTR (NAME, 1 3) refers to the first three characters in the column NAME.
•    Example:
SELECT * FROM PROJECTS
WHERE SUBSTR(PROJNAME,1,10) = 'CONVERSION';
Will INDEX be use if we use the function SUBSTR?
•    Yes.

What does GROUP BY means?
•    The GROUP BY clause specifies a result table that consists of a grouping of the rows

What does HAVING means?
•    The HAVING clause filters results obtained by the GROUP BY clause.
•    Example:
SELECT DEPT, AVG(SALARY)
   FROM Q.STAFF
GROUP BY DEPT
HAVING COUNT(*) > 4

What is the difference between IN and BETWEEN?
•    The BETWEEN predicate determines whether a given value lies between two other given values specified in ascending order. The given value will be inclusive to the two other given values.
•    The IN predicate compares a value or values with a set of values.

What is LIKE and how is it being used?
•    The LIKE predicate searches for strings that have a certain pattern.
•    The underscore character (_) represents any single character.
•    The percent sign (%) represents a string of zero or more characters.
•    Any other character represents a single occurrence of itself.

In using the LIKE predicate, how do you do the following:
•    Retrieve NAME that starts with ‘A’?
o    WHERE NAME LIKE ‘A%’

•    Retrieve NAME whose 2nd character is ‘B’?
o    WHERE NAME LIKE ‘_B%’

•    Retrieve NAME whose 2nd to the last character is ‘R’?
o    WHERE NAME LIKE ‘%R_’

•    Retrieve NAME whose last character is ‘Z’?
o    WHERE NAME LIKE ‘%Z’

•    Retrieve NAME that has ‘X’ in their name?
o    WHERE NAME LIKE ‘%X%’

What is correlated sub-query?
•    A correlated subquery is an inner query that must be evaluated for each row of the outer query. The query uses aliases to identify the table references.
•    Example:
SELECT   EMPNO, LASTNAME, FIRSTNME, SALARY
FROM     DSN8710.EMP A
WHERE 10 > (SELECT COUNT(*)
            FROM   DSN8710.EMP B
            WHERE  A.SALARY < B.SALARY
            AND    B.SALARY IS NOT NULL)
ORDER BY SALARY DESC;
o    The alias “A” identifies the table in the outer query, so that in the subquery, the A.SALARY identifies that column as belonging to the outer query's table. The alias “B” is used for the subquery table (though it is not required).               

What is referential integrity (R.I.)?
•    The state of a database in which all values of all foreign keys are valid. Maintaining referential integrity requires the enforcement of referential constraints on all operations that change the data in a table upon which the referential constraints are defined.

Explain Primary Key and Foreign Key concepts
•    PRIMARY KEY is a unique, non-null key that is part of the definition of a table. A table cannot be defined as a parent unless it has a unique key or primary key.
•    FOREIGN KEY is a column or set of columns in a dependent table of a constraint relationship. The key must have the same number of columns, with the same descriptions, as the primary key of the parent table. Each foreign key value must either match a parent key value in the related parent table or be null.

What will happen if I delete a row with R.I.?
•    You cannot delete a row of the parent table if records exist in the dependent table.
•    If you specify CASCADE, delete the row of parent table will delete the row if the child at the same time.

What are VIEWS?
•    A view is a virtual table made up of data from base tables and other views, but not stored separately.
•    We use views if we want to hide other columns to the users.
•    Views can be updated if there is only one base table.

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

When do you specify the isolation level? How?
•    During the BIND process(ISOLATION LEVEL is a parameter for 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 Database determine what lock-size to use?
•    There are three methods to determine the lock-size.  They are:
1. Based on the lock-size given while creating the tablespace
2. Programmer can direct the DB2® Database what lock-size to use
3. If lock-size ANY is specified, DB2® Database usually chooses a lock-size of PAGE

What are the three lock types?
•    The three types are shared, update and exclusive. 
•    Shared locks allow two or more programs to read simultaneously but not change the locked space. 
•    An exclusive lock bars all other users from accessing the space. 
•    An update lock is less restrictive; it allows other transactions to read or acquire shared locks on the space.

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.

Can I use LOCK TABLE on a view?
•    No.  To lock a view, take lock on the underlying tables.

DB2 Reviewer Part 2 can be viewed here.