===========================================================================
+++++++++++++++ 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
++++++++++++++++
For Creating DB refer this link http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0001941.html
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:
For DB2, http://sync.in/85O1ZHHkwu
