DB2-2

Q.  What will the EXPLAIN do?

A       EXPLAIN obtains information (which indexes are used, whether sorting is necessary, which level of locking is applied) about how SQL statements in the DBRM will be executed, inserting this information into "X".PLAN_TABLE where "X" is the authorization ID of owner of the plan.

Q.  What is a foreign key?

A       A foreign key is a column (or combination of columns) in a table whose values are required to match those of the primary key in some other table.

Q.  What will the FREE command do to a plan?

A       It will drop (delete) that existing plan.

Q.  What will the GRANT option do?

A       It will grant privileges to a list of one or more users. If the GRANT option is used in conjunction with the "PUBLIC" option, then all users are granted privileges. Also, you can grant privileges by objects and types.

Q.  What does the term "grant privileges" mean?

A       Grant privileges means giving access/authority to DB2 users.

Q.  What is a host variable?

A       This is a data item that is used in an SQL statement to receive a value or to supply a value. It must be preceded by a colon (:) to tell DB2 that the variable is not a column name.

Q.  What is an image copy?

A       It is an exact reproduction of all or part of a tablespace. DB2 provides utility programs to make full-image copies (to copy the entire tablespace) or incremental image copies to copy only those pages that have been modified since the last image copy.

Q.  What is meant by an index?

A       An index is a set of row identifiers (RIDS) or pointers that are logically ordered by the values of a column that has been specified as being an index. Indexes provide faster access to data and can enforce uniqueness on the row in a table.

Q.  What is an index key?

A       It is a column or set of columns in a table used to determine the order of index entries.

Q.  What is meant by an index scan?

A.      When an entire index (or a portion thereof) is scanned to locate rows, we call this an index scan. This type of access can be used, for example, to select rows of a table in some order and avoid sort for a query.

Q.  What is meant by indicator variable?

A       An indicator variable is an integer variable used to show whether its associated host variable has been assigned a null value.

Q.  What is a join?

A       A join is a relational operation that allows retrieval of data from two or more tables based on matching column values.

Q.  What is meant by locking?

A       Locking is a process that is used to ensure integrity of data. It also prevents concurrent users from accessing inconsistent data. The data (row) is locked until a commit is executed to release the updated data.

Q.  What is a “nonleaf” page?

A       This is a page that contains keys and page numbers of other pages in the index.  Nonleaf pages never point to actual data.

Q.  What is meant by null?

A       This is a special value that indicates the absence of data in a column. This value is indicated by a negative value, usually -1.

Q.  What is an object?

A       An object is anything that is managed by DB2 (that is, databases, tablespaces, tables, views, indexes, or synonyms), but not the data itself.

Q.  What will the DB2 optimizer do?

A       The optimizer is a DB2 component that processes SQL statements and selects the access paths.

Q.  What is a page?

A       This is the unit of storage within a tablespace or indexspace that is accessed by DB2.

Q.  What is a pagespace?

A       Pagespace refers either to an unpartitioned table to an index space, or to a single partition of a partitioned table of index space.

Q.  What is a predicate?

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

Q.  Describe a primary key?

A       A primary key is a key that is unique, nonnull and is part of the definition of a table. A table must have a primary key to be defined as parent.

Q.  What is a recovery log?

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

Q.  What is a Resource Control Table (RCT)?  Describe its characteristics?

A       The RCT is a table that is defined to a DB2/CICS region. It contains control characteristics which are assembled via the DSNCRCT macros. The RCT matches the CICS transaction ID to its associated DB2 authorization ID and plan ID (CICS attachment facility).

Q.  Where are plans stored?

A       Each plan is defined uniquely in the SYSIBM.SYSPLAN table to correspond to the transactions that are to execute that plan.

Q.  Describe referential integrity?

A       Referential integrity refers to a feature in DB2 that is used to ensure consistency of data in the database.

Q.  What is meant by a repeatable read?

A       When an application program executes with repeatable read protection, rows referenced by the program cannot be changed by other programs until the program reaches a commit point.

Openings at Broadridge

Work Location: Hyderabad


Client: Broadridge Financial Services


Job description:


 Experience: 2 - 3 years
Expertise in development and maintenance of Mainframe applications using COBOL, JCL, CICS, VSAM, and DB2
Should have hands-on application development and troubleshooting experience
Should have strong communication and client interfacing skills
Should have good knowledge on SDLC and quality process
Should be willing to work in UK/US shift on rotation basis if required
Education: BE / B.Tech / MCA or equivalent is needed




Contact:-
www.broadridge.com
Anamika
040-44544529

DB2-1

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


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


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


Q. Explain what a plan is?
A. A plan is a DB2 object (produced during the bind process) that
     associates one or more database request modules with a plan 
     name.


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


Q. What information is used as input to the bind process?
A. 1. The database request model produced during the precompile.
     2. The SYSIBM.SYSSTMT table of the DB2 catalog.


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


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


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


Q. What is the function of Buffer Manager?
A. 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 
     sophisticated buffering techniques (i.e., read-ahead buffering and 
     look-aside buffering).


Q. What is a buffer pool?
A. 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.


Q. How many buffer pools are there in DB2?
A. There are four buffer pools namely : BPO, BPI, BP2, and BP32.


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


Q. What is a clustering index?
A. It is a type index that locates table rows Determines how rows 
     are grouped together in the tablespace.


Q. What will the COMMIT accomplish?
A. 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 that other applications can 
     reference the just-committed data.


Q. What is meant by concurrency?
A. 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.


Q. What is cursor stability?
A. 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 a 
     commit point.) As soon as a program moves from one row to 
     another, other programs may read or change the first row.


Q. What is the function of the Data Manager?
A. 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).


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


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


Q. What are data types?
A They are attributes of columns, literal and host variables. The data 
    types are SMALLINT, INTEGER, FLOAT, DECIMAL, CHAR, 
    VARCHAR, DATE and TIME.


Q. What is a Declarations Generator (DCLGEN)?
A. DCLGEN is a facility that is used to generate SQL statements 
     that describe a table or view. These table or view descriptions are 
     then used to check the validity of other SQL statements at 
     precompile time. The table or view declare are used by the DB2I 
     utility DCLGEN to build a host language structure which is
     used by the DB2 precompiler to verify that correct column 
     names and data types have been specified in SQL statement.


Q. What does DSNDB07 database do?
A. DSNDB07 is where DB2 does its sorting. It includes DB2's sort 
     workarea and external storage.


Q. What is meant by dynamic SQL?
A. Dynamic SQL are SQL statements that are prepared and 
     executed within a program while the program is executing. The 
     SQL source is contained in host variables rather than being "hard 
     coded" into the program. The SQL statement may change from 
     execution to execution.


Q. What is meant by embedded SQL?
A. They are SQL statements that are embedded within an 
     application program and are prepared during program preparation 
     process before the program is executed. After it is prepared, the 
     statement itself does not change (although values of host 
     variables specified within the statement might change).


Q. What is meant by entity integrity?
A.  Entity integrity is when the primary key is in fact unique and not 
      null.

JCL-6

Q)      What is a Generation Data Group (GDG)?

A)      Generation Data Group is a group of chronologically or functionally related datasets. GDGs are processed periodically, often by adding a new generation, retaining previous generations, and sometimes discarding the oldest generation.

Q)      How is a GDG base created?

A)       A GDG base is created in the system catalog and keeps track of the generation numbers used for datasets in the group. IDCAMS utility is used to define the GDG base.

Q)      What is model dataset label(Model DSCB)?

A)       A model dataset label is a pattern for the dataset label created for any dataset named as a part of the GDG group. The system needs an existing dataset to serve as a model to supply the DCB parameters for the generation data group one wishes to create. The model dataset label must be cataloged. The model DSCB name is placed on the DCB parameter on the DD statement that creates the generation data group.

Q)      How are GDGs concatenated?

A)       Generation Data  Groups are concatenated by specifying each dataset name and the generation number for all generations of the generation data group.  Otherwise to have all generations of a generation data group, omit the generation number. The DD statement will refer to all generations. The result is the same as if all individual datasets were concatenated. If generations are not on the same volume, this will not work.

Q)      How is a new GDG coded?

A)       A new GDG is coded as (+1) after the dataset name as follows: DSN=JAN.DATA(+1). This will cause all generations to be pushed down one level at the end of the job.

Q)      When should DISP=MOD is used?

A)       DISP=MOD is used to either extend an existing sequential dataset or to create a dataset if it does not exist. If the dataset exists, then records are appended to the dataset at the end of the existing dataset. If the dataset does not exist, the system treats MOD as if it were NEW, provided that the volume parameter has not been used. If the volume parameter is used, the system terminates the job and does not create the new dataset. MOD can be used to add to a dataset that extends onto several volumes. Always specify a disposition of CATLG with MOD for cataloged datasets, even if they are already cataloged, so that any additional volume serial numbers will be recorded in the catalog.

Q)      How is a dataset passed from  one step to another?

A)       A dataset is passed from one step to another based on what is coded on the DISP parameter. The dataset can only be passed to subsequent steps if PASS was used on the disposition parameter.

Q)      How are datasets concatenated?

A)       Datasets are concatenated by writing a normal DD statement for the first dataset and then adding a DD statement without a DDNAME for each dataset to be concatenated in the order they are to be read. The following is an example of three datasets concatenated:

//YEARDAT            DD      DSN=JAN.DATA,DISP=SHR

//                   DD      DSN=FEB.DATA,DISP=SHR

//                   DD      DSN=MAR.DATA,DISP=SHR

Q)      What is the difference between the JOBLIB and the STEPLIB statements?

A)   The JOBLIB statement is placed after the JOB statement and is effective for all job steps. It cannot be placed in a

         cataloged procedure. The STEPLIB statement is placed after the EXEC statement and is effective for that job step

         only. Unlike the JOBLIB statement, the STEPLIB can be placed in a cataloged procedure.

Q)  Name some of  the JCL statements that are not allowed in procs.?

A) Some of the JCL statements which are not allowed in procedures are:

JOB, Delimiter(/*), or Null statements

2. JOBLIB or JOBCAT DD  statements

3. DD * or DATA statements

4. Any JES2 or JES3 control statements

Q)  What is primary allocation for a dataset?

A)  The space allocated when the dataset is first created.

Q)  What is the difference between primary and secondary allocations for a dataset?

A)  Secondary allocation is done when more space is required than what has already been allocated.

Q)  How many extents are possible for a sequential file ? For a VSAM file ?

A)        16 extents on a volume for a sequential file and 123 for a VSAM file.

Q)  What does a disposition of (NEW,CATLG,DELETE) mean? 

A)        That this is a new dataset and needs to be allocated,  to CATLG the dataset if the step is successful and to delete the dataset if the step abends.

Q)  What does a disposition of (NEW,CATLG,KEEP) mean?

A)        That this is  a new dataset and needs to be allocated, to CATLG the dataset if the step is successful and to KEEP but not CATLG the dataset if the step abends. Thus if the step abends, the dataset would not be catalogued and we would need to supply the vol. ser the next time we refer to it.

Q)  How do you access a file that had a disposition of KEEP? 

A)        Need to supply Volume Serial  Number VOL=SER=xxxx. 

Q)  MOD, DELETE;  What does a disposition of (,DELETE) mean ?

A)        The MOD will cause the dataset to be created (if it does not exist),  and then the two DELETEs will cause the dataset to be deleted whether the step abends or not. This disposition is used to clear out a dataset at the beginning of a job.

Q)  What is the DD statement for a output file?

A)        Unless allocated earlier, will have the following parameters:  DISP=(NEW,CATLG,DELETE), UNIT , SPACE & DCB

Q)  What do you do if you do not want to keep all the space allocated to a dataset?

A)        Specify the parameter  RLSE ( release ) in the SPACE e.g. SPACE=(CYL,(50,50),RLSE)

Q)  What is DISP= (NEW,PASS,DELETE)?

A)        This is a new file and create it, if the step terminates normally, pass it to the subsequent steps and if step abends, delete it.  This dataset will not exist beyond the JCL.

Q)  How do you create a temporary dataset?  Where will you use them?

A)        Temporary datasets can be created either by not specifying any DSNAME or by specifying the temporary file indicator as in DSN=&&TEMP. We use them to carry the output of one step to another step in the same job. The dataset will not be retained once the job completes.

Q)  How do you restart a PROC from a particular step?

A)        In job card, specify RESTART=PROCSTEP.STEPNAME where PROCSTEP = name of the JCL step that invoked the PROC and STEPNAME = name of the PROC step where you want execution to start

Q)  How do you skip a particular step in a proc/JOB? 

A)        Can use either condition codes or use the jcl control statement IF (only in ESA JCL).

Q)  A PROC has five steps.  Step 3 has a condition code.  How can you override/nullify this condition code? 

A)        Provide the override on the EXEC stmt in the JCL as follows:

//STEP001 EXEC procname, COND.stepname=value

All parameters on an EXEC stmt in the proc such as COND, PARM have to be overridden like this.

Q)  How do you override a specific DDNAME/SYSIN in PROC from a JCL?

A)        //<STEPNAME.DD>    DSN=...

Q)  What is NOTCAT 2?

A)        This is an MVS message indicating that a duplicate catalog entry exists. E.g., if you already have a dataset with dsn = 'xxxx.yyyy' and u try to create one with disp new, catlg, you would get this error. the program open and write would go through and at the end of the step the system would try to put it in the system catalog. at this point since an entry already exists the catlg would fail and give this message. you can fix the problem by deleting/uncataloging the first data set and going to the volume where the new dataset exists(this info is in the msglog of the job) and cataloging it.

Q)  What is 'S0C7' abend?

A)        Caused by invalid data in a numeric field.

Q)  What is a S0C4 error ?

A)        Storage violation error - can be due to various reasons. e.g.: READING a file that is not open, invalid address referenced due to subscript error.

JCL-5

Q)      What are SD37, SB37, SE37 abends?

A)        All indicate dataset out of space. SD37 - no secondary allocation was specified. SB37 - end of vol. and no further volumes specified.  SE37 - Max. of 16 extents already allocated.

Q)      What is S322 abend ?

A)        Indicates a time out abend. Your program has taken more CPU time than the default limit for the job class. Could indicate an infinite loop.

Q)      Why do you want to specify the REGION parameter in a JCL step? 

A)        To override the REGION defined at the JOB card level.  REGION specifies the max region size. REGION=0K or 0M or omitting REGION means no limit will be applied.

Q)      What does the TIME parameter signify ? What does TIME=1440 mean ?

A)        TIME parameter can be used to overcome S322 abends for programs that genuinely need more CPU time. TIME=1440 means no CPU time limit is to be applied to this step.

Q)      What is COND=EVEN ?

A)        Means execute this step even if any of the previous steps, terminated abnormally.

Q)      What is COND=ONLY ?

A)        Means execute this step only if any of the previous steps, terminated abnormally.

Q)      How do you check the syntax of a JCL without running it?

A)        TYPERUN=SCAN on the JOB card or use JSCAN.

Q)      What does IEBGENER do?

A)        Used to copy one QSAM file to another. Source dataset should be described using SYSUT1 ddname. Destination dataset should be described using SYSUT2. IEBGENR can also do some reformatting of data by supplying control cards via SYSIN.

Q)      How do you send the output of a COBOL program to a member of  a PDS?

A)        Code the DSN as PDS (member) with a DISP = SHR. The DISP applies to the PDS and not to a specific member.

Q)  I have multiple jobs ( JCLs with several JOB cards ) in a member.  What happens if I submit it?

A)        Multiple jobs are submitted (as many jobs as the number of JOB cards).

Q)  I have a COBOL program that Accepts some input data.  How do you code the JCL statement for this?

( How do you code instream data in a JCL? )

A)        //SYSIN DD*

input data

input data

/*

Q)  Can you code instream data in a PROC ?

A)        No.

Q)  How do you overcome this limitation ?

A)        One way is to code SYSIN DD DUMMY in the PROC, and then override this from the JCL with instream data.

Q)  How do you run a COBOL batch program from a JCL?  How do you run a COBOL/DB2 program?

A)        To run a non DB2 program,

//STEP001 EXEC PGM=MYPROG

To run a DB2 program,

//STEP001 EXEC PGM=IKJEFT01

//SYSTSIN DD *

DSN SYSTEM(....)

RUN PROGRAM(MYPROG)

PLAN(.....)  LIB(....)  PARMS(...)

/*

Q)  What is STEPLIB, JOBLIB?  What is it used for?

A)        Specifies that the private library (or libraries) specified should be searched before the default system libraries in order to locate a program to be executed. STEPLIB applies only to the particular step, JOBLIB to all steps in the job.

Q)  What is order of searching of the libraries in a JCL? 

A)        First any private libraries as specified in the STEPLIB or JOBLIB, then the system libraries such as SYS1.LINKLIB. The system libraries are specified in the link list.

Q)  What happens if both JOBLIB and STEPLIB is specified ?

A)        JOBLIB is ignored.

Q)  When you specify mutiple datasets in a JOBLIB or STEPLIB, what factor determines the order? 

A)        The library with the largest block size should be the first one.

Q)  How to change default PROCLIB?

A)        //ABCD  JCLLIB ORDER=(ME.MYPROCLIB,SYS1.PROCLIB)

Q)  The disp in the JCL is MOD and the program opens the file in OUTPUT mode. What happens ? The DISP in the JCL is SHR and the program opens the file in EXTEND mode. What happens ?

A)        Records will be written to end of file (append) when a WRITE is done in both cases.

Q)  What are the valid DSORG values ?

A)        PS - QSAM, PO - Partitioned, IS - ISAM

           

Q)  What are the differences between JES2 & JES3 ?

A)        JES3 allocates datasets for all the steps before the job is scheduled. In JES2, allocation of datasets required by a step are done only just before the step executes.

 

Q)  What are the causes for S0C1, S0C4, S0C5, S0C7, S0CB abends ?

A)        S0C1-May be due to 1.Missing or misspelled DD name 2.Read/Write to unopened dataset 3.Read to dataset opened

         output 4.Write to dataset opened input 5.Called subprogram not found

S0C4-may be due to 1.Missing Select statement(during compile) 2.Bad Subscript/index 3.Protection Exception

         4.Missing parameters on called subprogram 5.Read/Write to unopened file 6.Move data from/to unopened file

S0C5-May be due to 1.Bad Subscript/index 2.Closing an unopened dataset 3.Bad exit from a perform 4.Access to I/O

                  area(FD) before read

S0C7-may be due to 1.Numeric operation on non-numeric data 2.Un-initialize working-storage 3.Coding past the

      maximum allowed sub script

S0CB-may be due to 1.Division by Zero

Q)  What are the kinds of job control statements?

A)        The JOB, EXEC and DD statement.

Q)  What is the meaning of keyword in JCL?  What is its opposite?

A)        A keyword in a JCL statement may appear in different places and is recognized by its name, eg. MSGCLASS in the JOB statement.  The opposite is positional words, where their meaning is based on their position in the statement, eg. in the DISP keyword the =(NEW,CATLG,DELETE) meanings are based on first, second and third position.

Q)  Describe the JOB statement, its meaning, syntax and significant keywords? 

A)        The JOB statement is the first in a JCL stream.  Its format is // jobname, keyword JOB, accounting information in brackets and keywords, MSGCLASS, MSGLEVEL, NOTIFIY, CLASS, etc.

Q)  Describe the EXEC statement, its meaning, syntax and keywords?

A)        The EXEC statement identifies the program to be executed via a PGM= program name keyword.  Its format is //jobname EXEC PGM= program name.  The PARM= keyword can be used to pass external values to the executing program.