Pages

Monday, September 16, 2013

IBM Mainframe technical notes : JCL, VSAM, COBOL and DB2

===========================================================================
+++++++++++++++ MAINFRAME TECHNICAL NOTES  (JCL, VSAM AND COBOL)+++++++++++
===========================================================================

Easytrieve - used for creating reports, file processing, etc.

Endeavor - source code management and release management tool

Panvalet - old source code management tool

Xpeditor - is used to debug a program


VSAM reference book link
========================


maximum alternate key length in AIX 
Max mum of alternate keys possible for a single KSDS is 255 - check

//       INCLUDE MEMBER=MEM1(INCLUDE GROUP)

JOBLIB, STEPLIB & JCLLIB
++++++++++++++++++++++++

JCLLIB - It is used to specify the private library or system library in which the catalogued procedures are to be retrieved.

PDSE - IS CALLED AS LIBRARY AND IT IS SIMILAR TO PDS

//       JCLLIB ORDER=LIB1,LIB2

JCL QUICK SYNTAX

UTILITY : IEFBR14 
PURPOSE : DUMMY PROGRAM
OPERATIONS WE DO: CREATE PS, PDS, DELETE PS

//DD1      DD   DSN=TCHN220.CHNMF10.STEVE,
//         DISP=(NEW,CATLG,DELETE),       
//         LIKE=TCHN220.MASTER.PS         
===========================================================================


UTILITY : IEBGENER
PURPOSE : COPY DATA (SYSUT1,SYSUT2)
OPERATIONS WE DO: COPY INLINE TO PS/MEMBER OF A PDS, COPY PS TO PS/MEMBER OF A PDS
IF THE OUTPUT IS STORED IN A MEMBER OF PDS, DSN AND DISP=SHR ARE ENOUGH.
THE MEMBER WILL BE CREATED AUTOMATICALLY

THE DISP=MOD IS USED TO APPEND DATA TO A PS
TO COPY MORE THAN ONE PS TO A PS WE NEED TO INCLUDE DD STATEMENT FOR EACH PS IN THE SYSUT1

REFORMATING DATA WHILE COPYING
+++++++++++++++++++++++++++++++

GENERATE MAXFLDS=2,MAXLITS=6
RECORD   FIELD=(,,CH,)
===========================================================================

UTILITY : IDCAMS
PURPOSE : TO CREATE, MODIFY, REMOVE VSAM CLUSTERS, LIST CATALOG information, 

DEFINE CLUSTER (                         -
       NAME()        -
       TRACK( )              -
       RECORDSIZE( )   -
       CISZ( )                                      -
       INDEXED/NONINDEXED/LINEAR/NUMBERED/UNIQUEKEY/NONUNIQUEKEY)  
       
BASED ON THE CLUSTER TYPE THE ARGUMENTS WILL VARY,
IN KSDS,
      FREESPACE(
IN REPRO COMMAND,
IDS() ODS() COUNT() SKIP() 
FROMKEY
TOKEY
FROMADDRESS
TOADDRESS
FROMNUMBER
TONUMBER
REUSE/REPLACE

REPLACE - IT WILL REPLACE THE RECORDS IN ODS, MATCHING THE KEYS IN THE IDS. IN CASE OF ESDS, THE RECORDS ARE APPENDED.
REUSE - SHOULD BE GIVEN IF REUSE IS SPECIFIED DURING CREATION.
IN DEFINE AIX, AIX-NAME AND RELATE 
IN DEFINE PATH, PATH-NAME, PATHENTRY
GIVE FILE NAME IN IDS() AND DDNAME IN INFILE()
===========================================================================

UTILITY : SORT
PURPOSE : SORT AND COPY RECORDS (SORTIN, SORTOUT)
OPERATIONS WE DO: SORT/COPY RECORDS BASED ON A CONDITION, DISPLAY SORTED RECORDS BASED ON A CONDITION

CH-CHARACTER
BI-BINARY
PD-PACKED DECIMAL
FS-SIGNED NUMBERS
ZD-ZONED DECIMAL

USEFUL SYNTAX:

INREC FIELDS=(TO:FROM,LENGTH, , ....)
SORT  FIELDS=(STARTPOS,LENGTH,CH/BI/PD/FS/ZD,A/D)
SUM   FIELDS=NONE     -- is used to eliminate duplicates
SUM   FIELDS=(STARTPOS,LENGTH,ZD)
SORT  FIELDS=COPY     -- to copy all the records
SORT  FIELDS=COPY,SKIPREC=100,STOPREC=5  -- to copy the records from 100 to 105
SORT  FIELDS=COPY     -- to copy all the records

INCLUDE COND=(STARTPOS,LENGTH,TYPE,REL-OPER,VALUE)
GIVE DD NAMES LIKE 'SORTIN01' 'SORTIN02' 'SORTIN03' ... AND 'SORTOUT'
MERGE FIELDS=(STARTPOS,LENGTH,TYPE,A/D)

EXAMPLE
++++++++

SORT FIELDS=COPY
INCLUDE COND=(34,2,CH,EQ,C'AB')
===========================================================================

PROCEDURES IN JCL
+++++++++++++++++

INSTREAM PROCEDURE - THE PARAMETER "PROC" IN EXEC STATEMENT IS USED TO EXECUTE A PROCEDURE,
USING THE NAME (THE PROCEDURE NAME IS MANDATORY).

CATLOGED PROCEDURE - THE CAT PROC SHOULD BE WRITTEN INSIDE A MEMBER OF PDS.
THE PROC NAME IS OPTIONAL. 

THE JCLLIB ORDER=... IS USED TO MENTION THE PRIVATE LIBRARY.

TO OVERWRITE A PARAMETER WE USE 
++++++++++++++++++++++++++++++

PROCSTEPNAME.DDNAME DD STATEMENT (TRNS001P.DD1 DD DSN ...) &
PARAMETER-NAME.STEPNAME IN EXEC STATEMENT (COND.TRNS001)
WHILE PASSING PARAMETER TO PROCEDURES GIVE .. WHILE CONCATENATING TWO PARAMETERS

TO REFER BACK A PARAMETER
+++++++++++++++++++++++++

WE USE *.STEPNAME.DDNAME ( ALWAYS IT WILL POINT THE DSN ALONE)
===========================================================================


UTILITY: IEBCOPY
PURPOSE: TO COPY ENTIRE OR PARTIAL PDS
OPERATIONS WE DO: COPY ENTIRE/INCLUDE SOME/EXCLUDE SOME MEMBERS WHILE COPYING FROM PDS TO PDS

WE CAN GIVE ANY IDENTIFIER IN THE DD STATEMENT HOWEVER WE SHOULD MENTION THAT
COPY OUTDD=OUTDDNAME
     INDD=INDDNAME
EXCLUDE MEMBER=(MEM1,MEM2)

===========================================================================

GENERATION DATA GROUP (GDG)
+++++++++++++++++++++++++++

DEFINE GDG (                                   -
    NAME()                    -
    LIMIT()                                           -
    EMPTY/NOEMPTY                           -
    SCRATCH/NOSCRATCH                -
    )

SCRATCH - WE CAN'T RECOVER THE DELETED FILE
EMPTY     - ALL THE PREVIOUS VERSIONS ARE DELETED
NOEMPTY   - ONLY THE OLDEST VERSION IS DELETED

===========================================================================

NON VSAM DATASETS
++++++++++++++++++

PHYSICAL SEQUENTIAL
INDEXED SEQUENTIAL
DIRECT ACCESS
PARTITONED DATASETS

VSAM DATASETS
++++++++++++++

OPEN MODES: INPUT, OUTPUT, I-O, EXTEND

I-O    MODE IS FOR UPDATING, AND DELETING.
OUTPUT MODE IS FOR WRITING (FOR THE FIRST TIME WHEN THE FILE IS EMPTY)
INPUT  MODE IS FOR READING
EXTEND MODE IS FOR APPENDING

KSDS - HAS INDEX AND DATA COMPONENTS, HAS FREE SPACE
       SEQUENCE, RANDOM AND DYNAMIC ACCESS MODES ARE POSSIBLE
     - SIMILAR TO INDEXED SEQUENTIAL

ESDS - HAS NO INDEX, RECORDS CANT BE DELETED, RECORDS ARE STORED SEQUENTIALLY AND RETRIEVED IN ORDER OF HOW IT IS STORED,
       RECORDS ARE IDENTIFIED BY RBA - RELATIVE BYTE ORDER, NO FREE SPACE
     - SIMILAR TO PHYSICAL SEQUENTIAL 
         

RRDS - RECORDS ARE ACCESSED USING RRN - RELATIVE RECORD NUMBER, VARIABLE RECORD LENGHT IS POSSIBLE ONLY IN RRDS.
     - SIMILAR TO DIRECT ACCESS

LDS  - SEQUENCE OF BYTES
       
*****************************************

POINTS TO REMEMBER
+++++++++++++++++++

# WE SHOULD NOT USE 'EXTEND' OPEN MODE IN 'RANDOM' ACCESS MODE. (APPENDING MAY NOT BE POSSIBLE BECAUSE OF KEY VIOLATION) 

# INPUT OR I-O(FOR UPDATING/DELETING) SHOULD BE USED FOR FETCHING RECORDS IN RANDOM/DYNAMIC MODE

# WHILE DELETING TRY TO LOCATE THE RECORD USING 'READ' AND THEN DO A 'DELETE'

# A MAXIMUM OF 255 GDG VERSIONS CAN BE CREATED

# A MAXIMUM OF 255 STEPS CAN BE PLACED AFTER A JOB CARD IN A SINGLE JCL FILE

# If a new generation is created using (+1) in the fist step and we are accessing the generation in consecutive steps we should use (+1)

# VERIFY command is used to perform two functions
a) Close files that are open after abnormal termination
b) Syncs the index and data components of VSAM fiels

# For copying records to a KSDS from a PS through COBOL, the records should be in sorted order.

# To continue the input in more than one line in 'PARM' parameter we have to give any character from A to Z in column 72 and in the next line we have to start from 16th column

===========================================================================


COBOL 
++++++

ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER. 
OBJECT-COMPUTER.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
    SELECT FILENAME ASSIGN TO DDNAME ('AS' FOR ESDS CLUSTER TO DIFFERENTIATE FROM PS FILE)
  ORGANIZATION IS SEQUENTIAL/INDEXED/RELATIVE
  ACCESS MODE IS SEQUENTIAL/RANDOM/DYNAMIC
  [ RECORD KEY IS FS-KEY-VARIABLE ]
  FILE STATUS IS ....
DATA DIVISION.
FILE SECTION.
FD FILENAME.
01 GROUPNAME.
WORKING-STORAGE SECTION.
LINKAGE SECTION. 
PROCEDURE DIVISION.

REDEFINES CLAUSE
++++++++++++++++

# WE CAN REDEFINE A SINGLE VARIABLE OR A GROUP VARIABLE.
# THE REDEFINES HAS TO COME NEXT AFTER THE REDEFINING VARIABLE.(ie, IN THE SAME LEVEL NUMBER)

RENAMES CLAUSE
+++++++++++++++

# WE CAN'T USE 01 LEVEL VARIABLES IN LEVEL 66 RENAMES CLAUSE.
# WE DON'T NEED TO USE 'OF' KEYWORD IN LEVEL 88 CONDITION VARIABLES ( FOR LEVELS 02-49 
   WE NEED TO SPECIFY 'OF' KEYWORD)
# WE CAN RENAME A SINGLE VARIABLE AS WELL AS TWO OR MORE VARIABLES

LENGTH FUNCTION
+++++++++++++++

TO FIND THE LENGTH OF A STRING USE,
COMPUTE WS-LENGTH = FUNCTION LENGTH(WS-STRVAR)

LOWER CASE FUNCTION
++++++++++++++++++++

DISPLAY FUNCTION LOWER-CASE()

STRING VERB
+++++++++++

STRING
       DELIMITED BY '1'
       INTO
       NOT ON OVERFLOW 
               
       ON OVERFLOW
               
END-STRING

EXAMINE
+++++++
Examine is used in older versions of COBOL-I

Syntax:
+++++++

EXAMINE TALLYING ALL / UNTIL FIRST / LEADING

INSPECT
+++++++

IT HAS 3 USES
1) FINDING THE NUMBER OF OCCURRENCE OF A CHARACTER
    
TO COUNT THE OCCURRENCE OF A STRING EXACTLY ON A SOURCE STRING USE,
     INSPECT TALLYING FOR ALL
TO COUNT THE NUMBER OF CHARACTERS BEFORE A SEARCH STRING,
     INSPECT TALLYING FOR CHARACTERS BEFORE INITIAL   
TO COUNT THE OCCURRENCE OF A STRING AFTER SOME INITIAL OCCURRENCE OF ANOTHER STRING, 
     INSPECT TALLYING FOR ALL AFTER INITIAL
TO COUNT THE LEADING OCCURRENCE OF A CHARACTER IN A STRING,
     INSPECT TALLYING FOR LEADING
    
2) REPLACING ONE CHAR WITH ANOTHER
TO REPLACE A CHARACTER WITH ANOTHER CHARACTER, (THE LENGTH SHOULD BE SAME OTHERWISE IT WILL THROW A COMPILATION ERROR)

     INSPECT REPLACING ALL '' BY ''    
     
3) CONVERTING ONE SEQUENCE WITH ANOTHER
     INSPECT CONVERTING '' TO ''     
THE TWO STATEMENTS ARE EQUAL,
     INSPECT CONVERTING 'AB' TO '12'
     INSPECT REPLACING ALL 'A' BY '1'
                                ALL 'B' BY '2'      
NOTE: FOR REPLACING 'ALL' SHOULD COME BUT FOR CONVERTING 'ALL' IS NOT NEEDED

===========================================================================

SORT UTILITY IN COBOL
+++++++++++++++++++
GIVE SD INSTEAD OF FD.
USE TEMPORARY DATASET FOR WORKFILE (&&TEMP)
SORT ON ASCENDING KEY USING GIVING
SIMILARLY, WE CAN GIVE 'MERGE' INSTEAD OF 'SORT'


TABLE HANDLING
++++++++++++++
# WE CAN'T DEFINE 'OCCURS' CLAUSE IN LEVEL 01,66,77,88
# WE SHOULD DECLARE 'INDEXED BY' CLAUSE FOR EACH 'OCCURS' CLAUSE
# WE SHOULD USE THE INNERMOST VARIABLE NAME FOR ACCESSING ARRAY ELEMENTS, GROUP NAME SHOULD NOT BE USED.
# WHILE MOVING VALUE FROM AN INDEX VARIABLE TO A WORKING STORAGE VARIABLE USE 'SET' STATEMENT INSTEAD OF 'MOVE'

SEARCH VERB
++++++++++++

FIRST INIT THE INDEX
SEARCH
    AT END
    WHEN
    ....
END-SEARCH

SEARCH ALL VERB
+++++++++++++++

IN THE 'OCCURS' CLAUSE STATEMNT GIVE 'ASCENDING/DESCENDING KEY IS ' PHRASE WITH KEY FOR THE TABLE.

SEARCH ALL  
    AT END
    WHEN
    ....
END-SEARCH

USING SUB-PROGRAMS


Transfers control from one object module 


FILE STATUS CODE
+++++++++++++++
  • 00 - SUCCESS
  • 10 - END OF FILE
  • 22 - DUPLICATE KEY FOUND
  • 23 - RECORD NOT FOUND
  • 37 - OPEN MODE ERROR
  • 39 - ATTRIB MISMATCH ERROR DURING OPEN
  • 47 - WRONG READ MODE
  • 48 - WRONG WRITE MODE
  • 49 - WRONG DELETE OR REWRITE MODE

ERROR CODE EXPLANATION

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ERROR CODE    |                DESCRIPTION                                                                                   |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SX37          | RELATED TO SPACE ALLOCATION PROBLEM. CHECK ENOUGHSPACE IS AVAILABLE OR NOT
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
S000 U4038        |   GIVE STOP RUN IN THE PROGRAM TO PROPERLY TERMINATE IT 
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Try to find the answer for these questions
++++++++++++++++++++++++++++++++++++++++++

How to store the similar records to a separate file using SORT utility?

To delete a member inside a PDS use IEBCOPY with Scratch option.

Compiler Option
+++++++++++++++

DYNAM - Dynamic linking
NODYNAM - Static linking
SSRANGE - It will make the program to abend if arrary overflow occurs.
NOSSRANGE - It will not make the program to abend, we can access outside the memory   we have allocated for the program. If we really want to do efficient program we should use, SSRANGE


FAQs
++++

TIOT     - Task Input Output Table
DFSMS    - Data Facility Storage Management Subsystem 
HURBA    - High Used Relative Byte Address

  • JCL statement is coded in 80 byte records. The first 71 columns are used for coding the JCL and we give a blank space in the 72nd column and the last 8 columns contains the sequence number.
  • Maximum primary key length in ksds,AIX is 255
  • The terminal we are using is 3278, total no of PF keys 24, 
  • The Share Option in VSAM file is specified for cross-region(within one computer) and cross-system(between two or more computers). The default is (1,3). The cross-system won't also 1 or 2.
  • 1 - Total integrity, 2 - Write but no Read integrity
  • 3 - No Integrity, 4 - same as option 3 but refreshes buffer after every read.
  • The functions of IDCAMS utility are
     Creating VSAM objects
     Alter Dataset attributes
     Delete VSAM objects,
     Loading/Unloading(Import/Export), 
     Print Datasets
     List catalog information and           
     Copying (REPRO)
     
  • The Export/Import done using IDCAMS differs from typical REPRO command since it backups the catalog information also. PERMANENT, INHIBITSOURCE, INHIBITTARGET, PURGE, ERASE(inserts binary zeros before deletion). During Import if the catalog has no information it creates new cluster by the name. If it is present in the catalog then................. 
  • The PRINT command prints both VSAM and non-VSAM datasets.
  • The LISTCAT is used to list contents of a master or user catalogs.
  • A maximum of 3273 DD statements can be placed inside a job step.
  • Max no of CONCATENATED sequential dataset is 255 and for PDS it is 16
  • Concatenation has meaning only for sequential processing  - true
  • In VSAM the Minimum size of one CA is one Track and the maximum size is 15 or 16 (when stripped)
  • If two jobs have same job class and priority, they will be executed as if they are submitted - True (I have done this)
  • Accounting information max size : 142
  • Formatted Dump of the process program and system control blocks: SYSABEND
  • Step will always be executed within or not a preceding step abnormally terminates when coded as COND=EVEN
  • Region specifes the largest amount of main memory for any job step within a job.
  • REGION = 0K / 0M means all available space allocated to the job.
  • jOB or STEP has unlimited amount of time if TIME=1440
  • For variable length, un-blocked records logical record-length equals to BLOCKSIZE - False
  • IDCAMS is used to create a GDG.
  • In GDG same JCL code can be used to process every cycle with a single application - True
  • DFSORT is used for sorting and merging records
  • We can't give Exit Program in main program since it wont give control back to system. The Stop Run statement terminates the program (closes all the opened files and performs some cleaning activity) and gives control back to OS.
  • Maximum record size of non-spanned control interval size is 32761 and the maximum data component control interval size is 32768 (32761+7).
  • If the record size is greater than CISZ we have to use 'SPANNED' option and the maximum record size is MAXRECL = CI/CA * (CISZ - 10)
  • The Edited Picture clause 'P' (Numeric Place Holder) is used to change the precision. Ex 9PP can hold values like 1, 100 to 199 however when we print the variable only the most significant digit will be shown. Similarly, they can be used to tell the precision of decimal digits.
  • The ',' is used to print comma after the numeric digits. We can give the comma in normal pic clause variable.
  • If the value stored in a numeric variable (PIC 9(3)CR.) is negative, it will show the CR after the end otherwise blank is displayed.
  • The '*' is used to display '*' in the leading zero positions only. 
  • If we try to access a table/array element like arr[0] during compilation the cobol compiler will find that as error. However, if we give a index 0 at run-time, the program will work without any abend. I don't know how this is working.
DB2 SQL Commands
++++++++++++++++


Create table
+++++++++

CREATE TABLE TB_EMPLOYEE(                                     
                       EMPID VARCHAR(10) PRIMARY KEY NOT NULL,
                       EMPNAME CHAR(20),                      
                       EMPSALARY NUMERIC(7,2)                 
                      ) IN DBCHN01.TSCEP01;       
                      
 Create Index
 +++++++++
 CREATE UNIQUE INDEX EMPID_IDX ON TB_EMPLOYEE(EMPID);

Create Table without primary key then alter the table 
+++++++++++++++++++++++++++++++++++++++
CREATE TABLE TB_EMPLOYEE(                                  
                         EMP_ID          INTEGER  NOT NULL,
                         EMP_NAME        CHAR(10) NOT NULL,
                         EMP_DESIGNATION CHAR(5)  NOT NULL,
                         EMP_DEPT        CHAR(3)  NOT NULL 
                         ) IN DBTCHN01.TSCEP02;            
CREATE UNIQUE INDEX IN_TB_EMPLOYEE                         
              ON TB_EMPLOYEE(EMP_ID);                      
ALTER TABLE TB_EMPLOYEE                                    
                       ADD PRIMARY KEY (EMP_ID);          
                       
Note:
++++
      The  PRIMARY Key field should be given NOT NULL otherwise it will not accept the alter command.

 Unique Key
+++++++++
CREATE UNIQUE INDEX ON ()

The pre-requisites for the above statement is 
1) The table can have only one NULL value
2) It should be unique with no duplicate values

'NULL' is also considered as a value here.

                      
Add a new field to an existing table
+++++++++++++++++++++++++
                                 
ALTER TABLE TB_EMPLOYEE ADD         
                        EMPADDR VARCHAR(50);                    

Using ALTER TABLE we can do the following
  • Add New Column
  • Add/Drop constraints
  • Increase the length of a column
However, we cant rename a column name


Add Foriegn Key
++++++++++++

CREATE TABLE TB_WAH(                                               
                   EMPID VARCHAR(10) REFERENCES TB_EMPLOYEE(EMPID),
                   POINTS INTEGER                                  
                  ) IN DBCHN01.TSCEP01;                            

ALTER   TABLE TB_WAH ADD             
                     AWARDED_ON DATE;

Using GROUP BY
+++++++++++++

SELECT MAX(EMPSALARY), EMPADDR FROM TB_EMPLOYEE GROUP BY EMPADDR;

Select Timestamp
+++++++++++++

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

CREATE TABLE TB_COURSE_REG(                             
SCORE DECIMAL(5,2) CHECK (SCORE BETWEEN 60 AND 99),
       AGE SMALLINT CHECK (AGE IN (18,19,20)),     
GENDER CHAR(1) CHECK (GENDER IN ('M','F'))         
                     ) IN DBTCHN01.TSCEP02;        

Static SQL
++++++++

Using Cursor to select, update, delete multiple records


Declaring a Cursor for select query
+++++++++++++++++++++++++

EXEC SQL
         DECLARE
                      
                        CURSOR
                        [WITH HOLD]
                        FOR
                       
                        [FOR UPDATE OF]
                       
END-EXEC

Opening a Cursor
++++++++++++

EXEC SQL
         OPEN
END-EXEC

Fetch a Cursor
+++++++++++

EXEC SQL
         FETCH
                     INTO
                          
END-EXEC


Closing a Cursor
++++++++++++

EXEC SQL
         OPEN
END-EXEC

Procedures in DB2
+++++++++++++++

CREATE PROCEDURE proc1 
                           (IN var1 VARCHAR(10), OUT rc INTEGER) 
                           SPECIFIC myproc LANGUAGE SQL
                           
To call the above procedure from the CLP (Command Language Processor) we have to use 

CALL PROC1('ATHIRUBAN', ?)

Isolation Level
++++++++++++

Repeatable Read - Other transactions has to wait for the lock release acquired by the current transaction for all the rows it refers. 
                            Terminated by either COMMIT/ROLLBACK
Read Stability -   If some 1000 records are referred and 100 rows are processed, locks are held only for the 100 rows and other transaction can add/iinsert new rows.
Cursor Stability - If some 1000 records are referred and 100 rows are processed, lock is held by the current row pointed by the cursor. 
                         This is useful for running concurrent transaction
Uncommitted read - No locks are acquired

Security
++++++

We can restrict the user to update on a single column alone using the following statement

GRANT UPDATE () ON TABLE TO

To give user the permission to drop an index,  use the following statement

GRANT CONTROL ON INDEX TO

There is a difference between the following two statements

GRANT ALL PRIVILEGES ON TO PUBLIC/  (except CONTROL)

GRANT CONTROL ON TO (includes all)

The specific use of CONTROL allows the user to drop a table and also revoke a privilege from others + the normal privileges

The person who creates a table automatically receives the CONTROL privilege, only the DB/SYS Admin can give the CONTROL privilege to other users

Pre-compilation and bind process
+++++++++++++++++++++++++++

  • The DCLGEN is used to check the SQL statements
  • The embedded SQL statements in COBOL are stripped and put into DBRM
  • The SQL code in COBOL is converted into COBOL equivalent calls
  • During BIND, the DBRM is converted into PLAN after checking the SQL in DBRM with SYSCATLG and optiimization (this needs a BIND authority)
  • If we have more than one programs we have to use PACKAGE

IRLM - Inter System Resource Lock Manager - controls locking process in DB2

Types of table Space
++++++++++++++++
Simple Table Space (rows are stored in not a sequence order)
Segmented Table Space (pages are grouped in segments)
Partitioned Table Space (pages are grouped in partitions)

Maximum no of tables that can be joined ?
Collections in DB2 - name for a set of logically related packages ?
Secondary key in RI ?
Grant command in SQL - refer
Subquery operator compares a single value to every member of set of value - Any ?
Explain purpose in DB2 - allows the user to obtain information regarding the optimizer's choice of access strategy for a given sql statements ?
Stored procedure is stored in which location?
SYSADM ?
SYSCAT.PACKAGES
What is BINDADD, CREATE_EXTERNAL_ROUTINE? (They are database privileges)

FAQs
++++

  • All plans are stored in SYSIBM db2 directory SYSPL
  • GroupBy doesn't do any sorting
  • Maximum no of tables that can be joined is 15 tables
  • SQL Code -922 Authorization failure
  • Storage group is a db2 object
  • Storage group is a set of volume on DASD.
  • Maximum no of volumes per storage group is 133
  • In DB2, data is physically stored in VSAM LDS
  • Different modes of locking ? - shared, exclusive and update
  • Every SQL statement is not always executable
  • We can use MAX on CHAR type
  • PACKAGE is a single bound DBRM with optimized access path
  • SUM returns NULL and COUNT,  MAX returns zero
  • DBRM - Database Request Module
  • UNION operator eliminates the duplicate rows
  • If we try to SELECT from two tables, the result will contain a cartesian product of the two tables.
  • If a CURSOR is declared with the WITH HOLD option it is not closed by an explicit COMMIT statement.
  • A sub-query doesn't return NULL value (present in the table) to the result set
  • A VIEW created with a CHECK OPTION, is used ensure the inserted rows conforms with the defnition
  • The NULL values are displayed as a '-' in DB2 and 'NULL' in MySQL
  • It is possible to access data using VIEW, ALIAS or SYNONYM from a source table
  • After a failure, the DB Manager tries to rollback the transaction (that are not yet committed) after a restart 
  • SELECT * FROM tab1 FETCH FIRST 50 ROWS ONLY - is the syntax to fetch n rows from a table regardless of how many rows present in the table
  • The wildcard '%' stands for any alphanumeric character and '_' stands for a single alphanumeric character
  • FULL OUTER JOIN combines INNER JOIN, RIGHT OUTER JOIN AND LEFT OUTER JOIN
  • LCASE('string') / LOWER('string') - is for converting string to lower-case form
  • USE is a tablespace privilege
References:

http://sync.in/XyzlXB2vmQ

For DB2, http://sync.in/85O1ZHHkwu