Repository: DB2
DB2(DATABASE 2)
Ø Relational Database Management System(RDBMS)
Ø Universal Database(UDB)
This is one of the sub systems in Mainframes.
Any number of Sub Systems can be created in Mainframes as per the requirements.
Hierarchy of DB2 Sub system:
TS – Table Space INS – Index Space
T – Table IND – Index
V – view
Maximum storage space for a Table Space is 64 million bytes.
SQL can be used to Create, Delete, and Update and Query the Objects
SQL queries can be executed by the following techniques
Application programmingTools like QMF (Query Management Facility)
SPUFI (SQL Processor User File Input)
FILE -AID
DATA TYPES :Integer -- 4 bytes
Small int -- 2 bytes
Char(n) – N bytes
Varchar(n) – N+2 bytes
Graph(n) – 2n bytes
Vargraph(n)– 2N+2 bytes
Date – 10 bytes
Time – 8 bytes
Timestamp – 26 bytes
NORMALIZATION :Arranging the data in the Database in organized manner.
1NF: Avoiding multiple values or set of values in one column.
2NF: Avoiding repeated rows by defining primary key.
3NF: Separating functionally dependent and non-functionally dependent columns
Primary key :-Ø Uniquely identified row
Ø Which can be formed with single or multiple columns
Ø Does not allow duplicate records
Ø Cannot contain Null
Foreign key : -Ø Another identifier which can be used to build relationship between the tables
Ø Must be the primary key of parent table with same data type & length
Ø Can consists of single or multiple columns
Ø Can contain Null or duplicate rows
Ø Multiple foreign keys can be defined in one table
Ø Foreign key should be defined at the time of defining child table in the create command by “WITH REFERENCES” option.
CREATE TABLE ITEM(
INO INTEGER,
INAME CHAR(15),
CNO INTEGER,
PRIMARY KEY IS INO,
FOREIGN KEY IS CNO
WITH REFERENCES CUST)
REFERENCE INTEGRITY:The relationship between two tables which can be achieved by defining foreign key.
PRIMARY KEY FOREIGN KEY1. Cannot contain Null values or duplicate 1. Can contain NULL values or rows duplicate rows
2. Cannot be updated 2. Can be updated
3. Can be defined as a foreign key 3. which must be primary key of
in other table another table.
4. only one primary key can be defined for 4. multiple foreign keys can be
one table defined for one table.
SQL(Structured Query Language)
Ø DDL (Data Definition Language)
Create, alter, drop
Ø DML (Data Manipulation Language)
Insert, update, select & delete
Ø DCL (Data Control Language)
Grant, Revoke
Ø TCL (Transaction Control Language)
Commit, Rollback
Static insertion: Insert into cust(cno, cname, cloc) values (10, “xyz”, “hyd”)
Dynamic insertion: Insert into cust(cno, cname, cloc) values (v1, v2, v3)
v1,v2, v3 are host variables to be defined in working storage section.
Delete from cust
Delete from cust where cno = 20
Update cust set cname = “ xyz” where cno = 20
Select cno,cname from cust
Select * from cust
Select * from, cust where cno = v1
Select * from cust wehre cno=v1 and cname =v2
Select * from cust where cno between 20 and 60
Select * from cust where cname like “%y%”
Column functions:
Select max(sal) from emp
Select min(sal) from emp
Select avg(sal) from emp
Select sum(sal) from emp
To avoid duplicate rows : select distinct cno,cname from cust
To get total no. of rows : select count(*) from cust
SUBQUERY:Ø Query within Query
Ø First inner query executes & out query executes based on the result of inner query
Ø Max of 15 sub queries can be coded
Ø To simplify sub queries, logic can be built with combination of COBOL + SQL statements
To retrieve second maximum salary from emp table:
Select max(sal) from emp where sal <(select max(sal) from emp)
To retrieve third maximum salary from emp table:
Select max(sal) from emp
where sal < (select max(sal) from emp
Where sal < (select max(sal) from emp))
CO-RELATED SUBQUERY:Ø For every row of outer query, inner query must executes at least once
Ø First outer query executes & then inner query executes
Ø Practical examples : to fine top 2,3 or n salaries
Select a. sal from emp a
where 0 = (select count(*) from emp b
Where a.sal < b.sal)
0 -- max
1 – 2nd max
2 – 3rd max
3 – 4th max
DCLGEN :Declaration Generator . a tool to generates the equivalent COBOL variables.
Which can be used to generate host variables with equivalent data types of DB2 columns.
DCLGEN
DB2 Table COBOL
Host variables:Ø Can be used to pass the data from cobol program to DB2 table or DB2 table to COBOL program.
Ø When host variables are coded with sql statements it must be prefixed with : like :hv-cname.
Ø Table name must be supplied as input to DCLGEN & partition dataset should be as output.
Ø After creating DCLGEN variables which must be copied to application program in WORKING-STORAGE SECTION by using include command i.e.
Exec sql
Inlcude custDCL
End-exec.
Ø Include & copy have the same functionality
SQLCODE :Ø Predefined numeric number which can be used to check SQL statements for successful , unsuccessful execution.
Ø SQLCODE can be stored in SQLCA(SQL Communication Area)
Ø Copy SQLCA in WORKING-STORAGE SECTION
Ø System defined variable
Ø Evaluate or if statement must be coded to check the SQLCODE immediately after SQL statement.
Ø SQLCODE =00 ---- successful
= +100 --- end of table or record not found.
Sample program:WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
INCLUDE CUSTDCL
END-EXEC.
01 WS-SQL-CODE PIC S9(4)
88 88-SUCCESS VALUE 00
88 88-NOTFOUND VALUE 100
88 88-FORIENG KEY VOILATION VALUE –532
88 88- MULITPLE ROW VALUE –811
PROCEDURE DIVISION.
UPDATE CUST
SET CNAME = :HV-CNAME
WHERE CNO=:HV-CNO
MOVE SQLCODE TO WS-SQLCODE.
EVALUE WS-SQL-CODE
WHEN 88-SUCCESS
DISPLAY “SUCCESSFULLY UPDATED”
WHEN 88-NOTFOUND
DISPLAY “ RECORD NOT FOUND”
WHEN 88-FOREIGNKEYVOILATION
DISPLAY “ FOREIGN KEY VOILATION”
WHEN OTHER
DISPLAY “ ERROR OCCURRED IN UPDATE”
STOP RUN
END-EVALUATE.
STOP RUN.
CURSOR:To retrieve multiple rows for a given condition.
Let us take the following example:
Exec sql
Select cno,cname,cloc
into :hv-cno,:hv-cname,:hv-cloc
from cust where cloc =:hv-cloc
end-exec.
If the condition satisfy for one row it executes successfully. If the condition satisfy for multiple rows it wont work. It returns –811 as SALCODE. For this we use cursors.
Ø Cursors can be used to retrieve multiple rows for a given condition
Ø Cursor cycle is declare open fetch close
Ø Declare: declares or define name for cursor against a table
Can be coded in working-storage section or procedure division
For better readability code in working-storage section.
Ø Open: can be used to open a cursor with rows for a given conditions inbuffer.
Retireves data in to buffer
Must be coded in the procedure division only
Where condition value must be supplied before opening a cursor.
Ø Fetch: can be used to retrieve rows one by one from buffer into application prog.
Which must be coded in procedure divison after open.
Must be coded with hostvariables
No of host variables in fetch & no of columns in the declare must be same
Canbe executed multiple times using perform. i.e. till EOT or record not found which can be identified by SQLCODE = 100
Ø Close : used to close the cursor
Must be coded in procedure division only
Must be executed after open statement.
Practical examples : Can be used to retrieve the data based on loc, date, products.
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT CNO,CNAME FROM CUST
WHERE CNAME=:HV-CNAME
END-EXEC.
EXEC SQL
OPEN C1.
END-EXEC.
PERFORM UNTIL SQLCODE= 100
EXEC SQL
FETCH C1 INTO :HV-CNO,:HV-CNAME
END-EXEC
END-PERFORM.
EXEC SQL
CLOSE C1
END-EXEC
Ø Which can be used to update row by row when multiple rows are satisfied.
Ø Before update cursor has to be declared with for update of column option.
Ø Where current of cursor name option must be used with update command
Ø Withhold: this option can be used to remain cursors open even after commit statement.
Ø Must be coded with cursor statement
EXEC SQL
DECLARE C2 CURSOR WITH HOLD FOR
SELECT CNO,CNAME FROM CUST
WHERE CNAME=:HV-CNAME
FOR UPDATE OF CNAME
END-EXEC.
EXEC SQL
OPEN C1.
END-EXEC.
EXEC SQL
FETCH C2 INTO :HV-CNO,:HV-CNAME
END-EXEC
EXEC SQL
UPDATE CUST SET CNAME=”ABC” WHERE CURRENT OF C2.
EMD=EXEC.
EXEC SQL
CLOSE C1
END-EXEC
INDEX:
Ø Index allows duplicate values
Ø unique index doesn’t allow duplicate rows
Ø cross reference between index table & table is called clustered index.
Ø Create index in1 on cust(cno)
PRIMARY KEY INDEX UNIQUE INDEX
1. Uniquely identified row. Record identified based on Records identified based on
the index the index
2. No duplicated rows, no Duplicate rows, null values No duplicate rows
null values are allowed
3. Can consist of single or Dan consist of single or Can consist of single or
multiple columns multiple columns columns
4. Which will be stored in Which is stored in SYSINDEX Which is stored in sysindex
SYSKEYS.
VIEWS:
CREATE VIEW CVIEW(VCNO,VCNAME,VCLOC) AS
(SELECT CNO,CNAME,CLOC FROM CUST WHERE
CNAME LIKE “%X%)
Ø Logical representation of the table
Ø Stored in virtual memory
Ø Can be derived from single table or multiple tables
Ø Views are updateable if they are derived from single table without any column functions , group by
Ø Multiple views can be generated from single table.
Ø Views are stored in sysviews
Advantages of Views:
Ø Data security
Ø Data correctness
Ø Logical data independence
Ø Part of the information can be visible to the sers
Ø Accessing can be faster.
DELETE RULES:
Ø Delete rules can be applied for delete command against Database.
Ø Delete rules are 3 types
1. on delete cascade – all matching child rows will be deleted automatically when we delete parent row.
2. on delete restrict – all matching rows will be restricted when we delete parent row which is default.
3. on delete set null – all matching child row will be set to null when we delete parent row.
UNION:
Ø UNION is used to concatenate rows into one table from single or multiple tables.
Ø Rules : no. of columns & data type of both the queries must be same. column may be different
Ø UNION can be used to eliminate duplicate rows
Ø UNION ALL retrieved duplicate rows also.
SELECT CNO,CNAME FROM CUST WHERE CNO=10
UNION/UNIONALL
SELECT CNO,CNAME FROM ITEM WHERE INO=20
JOINS:
Ø JOINS can be used to concatenate columns from one table or multiple tables.
Ø JOIN types are :
1. left outer join : which can be used to retrieve matching, non matching rows from leftside table
2. right outer join: which can be used to retrieve matching, non matching rows from right side table.
3. full outer join: which can be used to retrieve matching, non matching rows from both the tables.
4. self join or inner join : can be achieved by defining alias for the table.
EXPLAIN :
It can be used to evaluate the performance of SQL queries.
It can be used to tune SQL queries.
Input is SQL queries and output is plan-table.
For every SQL query one plan-table will generate.
All plan-tables are stored in physical seq file.
Plan table
Query table no.of index no.of indexs owner join type groupby cputime
block no name cols
1 cust 10 in1 1 custc self y 10 min
DB2 CATALOG:
Ø Consists of Table pace, Index space, Index, unique index, Views, Alias, synonyms, keys.
Ø When we create table, the details of table are entered in Systable automatically.
SysIBM.SYSTABLE
Table Name
No.of cols
Owner name
Created by
Created date
Created time
Cust
10
Abc
Xyz
02-apr-2004
0850
Item
15
Mno
Rst
06-apr-2004
1020
SysIBM.SYSINDEX
Index name
Table Name
No.of cols
Owner name
Created by
Created date
Created time
In1
Cust
10
Abc
Xyz
02-apr-2004
0850
In2
Item
15
Mno
Rst
06-apr-2004
1020
SysIBM.SYSCOLS
Col name
Table name
Index name
Primary key
Foreign key
Cno
Cust
In1
Cno
-----
Cname
Cust
In1
Cno
Cloc
Cust
In2
cno
Ino
Item
Ino
Cno
Iname
Item
Ino
Cno
Ides
Item
Ino
cno
SysIBM.SYSKEYS: All primary & foreign keys.
Grant table syscols to all
Grant table syscols(select/delete/update) to user1,user2
Revoke table syscols from all.
CATALOG:
SYSTABLE, SYSCOL, SYSKEYS, SYSINDEX, SYSPKS, SYSFKS, SYSALIAS, SYSSYNONYMS, SYSINDEX, SYSVIEWS,SYSTABLESPACE, SYSINDEXSPACE.
DSNHPC --- IBM supplied utility used for precompilation.
Precompiler functions:
Ø Separates SQL & COBOL statements
Ø Check SQL syntaxs
Ø Replace all SQL statements with host language call statements in the compiled program.
Ø Which generates timestamp tokens
BIND:
BIND takes DBRM as input & generate package & application plan. The package will be loaded to the directory. Plan will be loaded to sysplans.
Bind functions:Ø Checks authorization for SQL statement
Ø Checks the syntax errors of SQL statements like
1. Missing co name in the select list & used in order by & group by
2. Mismatch columns host variables
3. Data type mismatch of columns & host variables
4. Indicator variables not declared
5. Data truncation.
BIND SUBCOMPONANTS/PARAMETERS:
OPTIMIZER:Ø It generates optimized access path by analyzing the statistics of SQL statements which will be stored.
Ø RUNSTATS utility is one of the ISPF panel option which is stored in DB2 defaults option.
Ø Optimized path is stored in package which is not executable module.
ISOLATION LEVEL:Which can be used to lock the database at the time of executing SQL statements.
Cusrsor stability(CS): It acquires the address of a row. Sets the pointer to a specified row based on SQL query & acquires the lock against that row. Then releases the klock after the transaction before commit.
Repeatable Read(RR): which acquires the address of a row & acquire lock against the page(1 page -4024 bytes) & then released the lock after the commit statements.
RUNTIME SUPERVISOR:Which is to oversee execution of SQL statements.
Statistics like no of tables, columns, indexes, keys
PLAN/APPLICATION PLAN:
It consists of executable module which is actual output of SQL statements which must be specified in the RUNJCL to execute SQL queries if the program is batch program. If the program is online which must be specified in RCT. Application plan will be loaded to load module with time stamp tokens.
COBOL COMPILATION:The compiler takes COBOL statement as input to generate object program, & loaded to the load module by line/edit with time stamp tokens.
COBOL + DB2
PRECOMPILER
COBOL
DBRM
BIND
PACKAGE
PLAN
OBJ PROG
OBJ PROG
APPL PLAN
Run time supervisor
UTILITIES USED:
DSNHPC : system utility pre compiler.
IKJEFT01 or IKJEFT01B --- BIND
IGYCRCTL or IKFCBLOO --- COBOL compilation
IEWL or HEWL --- link/edit
INTERVIEW QUESTIONS:What is RI ? where did u use RI in your project? Explain with example?
What is the difference between primary key, foreign key, index & unique index?
Can we create index when table has duplicate rows?
Can we create unique index when table has duplicate rows?
Can we create index or unique index on empty table?
What happens to the package when index is dropped? What’s the process or steps to be taken?
How to delete package?
Where package is stored? How to retrieve package?
Difference between plan & package?
What are the steps to be taken when SQL statements are changed without changing any COBOL statements?
Do we need to pre compile when index is dropped?
Can we bind when table is dropped?
Can optimized access path consist of multiple DBRMS.
What is the significance of timestamp tokens?
What is the significance of normalization?
Where do we specify run program, plan name, library & DB2 subsystem
IBM supplied utility to run COBOL + DB2
What is difference between DB2 & COBOL files & give some example for COBOL files & DB2 tables related to your project?
Can we load data from sequential file to table or table to sequential file?
What are the steps to be followed to develop DB2+ COBOL program?
Can we prepare a program/compile when DB2 system is down?
How to identify DB2 test or production system by seeing run JCL?
What is the output of explain?
What is the difference between correlated sub query & sub query?
How to find 4th max sal?
How to find nth max sal?
How to evaluate SQLcodes & where it is stored?
How to count total no of unique rows from the table?
How to sum repeated rows?
How to write a cobol program for above query? Retrieve row by row & use cobol logic to sum repeated rows?
What is the significance of DCLGEN?
Where DCLGEN is stored?
Difference between join & union?
difference between UNION & UNIONALL?
Can be have different col names in union?
How do u evaluate/tune/analyze SQL queries?
What are the JCL utilities for compile, pre compile, bind & link edit?
Wha5t is the significance of isolated levels?
Can we alter foreign key?
Can we alter primary key?
Can we alter data type & length?
What are the equivalent cobol variables for varchar?
What is the time stamp & its format?
We appreciate your feedback. Please click on Ads by Google
Ø Relational Database Management System(RDBMS)
Ø Universal Database(UDB)
This is one of the sub systems in Mainframes.
Any number of Sub Systems can be created in Mainframes as per the requirements.
Hierarchy of DB2 Sub system:
TS – Table Space INS – Index Space
T – Table IND – Index
V – view
Maximum storage space for a Table Space is 64 million bytes.
SQL can be used to Create, Delete, and Update and Query the Objects
SQL queries can be executed by the following techniques
Application programmingTools like QMF (Query Management Facility)
SPUFI (SQL Processor User File Input)
FILE -AID
DATA TYPES :Integer -- 4 bytes
Small int -- 2 bytes
Char(n) – N bytes
Varchar(n) – N+2 bytes
Graph(n) – 2n bytes
Vargraph(n)– 2N+2 bytes
Date – 10 bytes
Time – 8 bytes
Timestamp – 26 bytes
NORMALIZATION :Arranging the data in the Database in organized manner.
1NF: Avoiding multiple values or set of values in one column.
2NF: Avoiding repeated rows by defining primary key.
3NF: Separating functionally dependent and non-functionally dependent columns
Primary key :-Ø Uniquely identified row
Ø Which can be formed with single or multiple columns
Ø Does not allow duplicate records
Ø Cannot contain Null
Foreign key : -Ø Another identifier which can be used to build relationship between the tables
Ø Must be the primary key of parent table with same data type & length
Ø Can consists of single or multiple columns
Ø Can contain Null or duplicate rows
Ø Multiple foreign keys can be defined in one table
Ø Foreign key should be defined at the time of defining child table in the create command by “WITH REFERENCES” option.
CREATE TABLE ITEM(
INO INTEGER,
INAME CHAR(15),
CNO INTEGER,
PRIMARY KEY IS INO,
FOREIGN KEY IS CNO
WITH REFERENCES CUST)
REFERENCE INTEGRITY:The relationship between two tables which can be achieved by defining foreign key.
PRIMARY KEY FOREIGN KEY1. Cannot contain Null values or duplicate 1. Can contain NULL values or rows duplicate rows
2. Cannot be updated 2. Can be updated
3. Can be defined as a foreign key 3. which must be primary key of
in other table another table.
4. only one primary key can be defined for 4. multiple foreign keys can be
one table defined for one table.
SQL(Structured Query Language)
Ø DDL (Data Definition Language)
Create, alter, drop
Ø DML (Data Manipulation Language)
Insert, update, select & delete
Ø DCL (Data Control Language)
Grant, Revoke
Ø TCL (Transaction Control Language)
Commit, Rollback
Static insertion: Insert into cust(cno, cname, cloc) values (10, “xyz”, “hyd”)
Dynamic insertion: Insert into cust(cno, cname, cloc) values (v1, v2, v3)
v1,v2, v3 are host variables to be defined in working storage section.
Delete from cust
Delete from cust where cno = 20
Update cust set cname = “ xyz” where cno = 20
Select cno,cname from cust
Select * from cust
Select * from, cust where cno = v1
Select * from cust wehre cno=v1 and cname =v2
Select * from cust where cno between 20 and 60
Select * from cust where cname like “%y%”
Column functions:
Select max(sal) from emp
Select min(sal) from emp
Select avg(sal) from emp
Select sum(sal) from emp
To avoid duplicate rows : select distinct cno,cname from cust
To get total no. of rows : select count(*) from cust
SUBQUERY:Ø Query within Query
Ø First inner query executes & out query executes based on the result of inner query
Ø Max of 15 sub queries can be coded
Ø To simplify sub queries, logic can be built with combination of COBOL + SQL statements
To retrieve second maximum salary from emp table:
Select max(sal) from emp where sal <(select max(sal) from emp)
To retrieve third maximum salary from emp table:
Select max(sal) from emp
where sal < (select max(sal) from emp
Where sal < (select max(sal) from emp))
CO-RELATED SUBQUERY:Ø For every row of outer query, inner query must executes at least once
Ø First outer query executes & then inner query executes
Ø Practical examples : to fine top 2,3 or n salaries
Select a. sal from emp a
where 0 = (select count(*) from emp b
Where a.sal < b.sal)
0 -- max
1 – 2nd max
2 – 3rd max
3 – 4th max
DCLGEN :Declaration Generator . a tool to generates the equivalent COBOL variables.
Which can be used to generate host variables with equivalent data types of DB2 columns.
DCLGEN
DB2 Table COBOL
Host variables:Ø Can be used to pass the data from cobol program to DB2 table or DB2 table to COBOL program.
Ø When host variables are coded with sql statements it must be prefixed with : like :hv-cname.
Ø Table name must be supplied as input to DCLGEN & partition dataset should be as output.
Ø After creating DCLGEN variables which must be copied to application program in WORKING-STORAGE SECTION by using include command i.e.
Exec sql
Inlcude custDCL
End-exec.
Ø Include & copy have the same functionality
SQLCODE :Ø Predefined numeric number which can be used to check SQL statements for successful , unsuccessful execution.
Ø SQLCODE can be stored in SQLCA(SQL Communication Area)
Ø Copy SQLCA in WORKING-STORAGE SECTION
Ø System defined variable
Ø Evaluate or if statement must be coded to check the SQLCODE immediately after SQL statement.
Ø SQLCODE =00 ---- successful
= +100 --- end of table or record not found.
Sample program:WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC
EXEC SQL
INCLUDE CUSTDCL
END-EXEC.
01 WS-SQL-CODE PIC S9(4)
88 88-SUCCESS VALUE 00
88 88-NOTFOUND VALUE 100
88 88-FORIENG KEY VOILATION VALUE –532
88 88- MULITPLE ROW VALUE –811
PROCEDURE DIVISION.
UPDATE CUST
SET CNAME = :HV-CNAME
WHERE CNO=:HV-CNO
MOVE SQLCODE TO WS-SQLCODE.
EVALUE WS-SQL-CODE
WHEN 88-SUCCESS
DISPLAY “SUCCESSFULLY UPDATED”
WHEN 88-NOTFOUND
DISPLAY “ RECORD NOT FOUND”
WHEN 88-FOREIGNKEYVOILATION
DISPLAY “ FOREIGN KEY VOILATION”
WHEN OTHER
DISPLAY “ ERROR OCCURRED IN UPDATE”
STOP RUN
END-EVALUATE.
STOP RUN.
CURSOR:To retrieve multiple rows for a given condition.
Let us take the following example:
Exec sql
Select cno,cname,cloc
into :hv-cno,:hv-cname,:hv-cloc
from cust where cloc =:hv-cloc
end-exec.
If the condition satisfy for one row it executes successfully. If the condition satisfy for multiple rows it wont work. It returns –811 as SALCODE. For this we use cursors.
Ø Cursors can be used to retrieve multiple rows for a given condition
Ø Cursor cycle is declare open fetch close
Ø Declare: declares or define name for cursor against a table
Can be coded in working-storage section or procedure division
For better readability code in working-storage section.
Ø Open: can be used to open a cursor with rows for a given conditions inbuffer.
Retireves data in to buffer
Must be coded in the procedure division only
Where condition value must be supplied before opening a cursor.
Ø Fetch: can be used to retrieve rows one by one from buffer into application prog.
Which must be coded in procedure divison after open.
Must be coded with hostvariables
No of host variables in fetch & no of columns in the declare must be same
Canbe executed multiple times using perform. i.e. till EOT or record not found which can be identified by SQLCODE = 100
Ø Close : used to close the cursor
Must be coded in procedure division only
Must be executed after open statement.
Practical examples : Can be used to retrieve the data based on loc, date, products.
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT CNO,CNAME FROM CUST
WHERE CNAME=:HV-CNAME
END-EXEC.
EXEC SQL
OPEN C1.
END-EXEC.
PERFORM UNTIL SQLCODE= 100
EXEC SQL
FETCH C1 INTO :HV-CNO,:HV-CNAME
END-EXEC
END-PERFORM.
EXEC SQL
CLOSE C1
END-EXEC
For Update of where current of
Ø Which can be used to update row by row when multiple rows are satisfied.
Ø Before update cursor has to be declared with for update of column option.
Ø Where current of cursor name option must be used with update command
Ø Withhold: this option can be used to remain cursors open even after commit statement.
Ø Must be coded with cursor statement
EXEC SQL
DECLARE C2 CURSOR WITH HOLD FOR
SELECT CNO,CNAME FROM CUST
WHERE CNAME=:HV-CNAME
FOR UPDATE OF CNAME
END-EXEC.
EXEC SQL
OPEN C1.
END-EXEC.
EXEC SQL
FETCH C2 INTO :HV-CNO,:HV-CNAME
END-EXEC
EXEC SQL
UPDATE CUST SET CNAME=”ABC” WHERE CURRENT OF C2.
EMD=EXEC.
EXEC SQL
CLOSE C1
END-EXEC
INDEX:
Ø Index allows duplicate values
Ø unique index doesn’t allow duplicate rows
Ø cross reference between index table & table is called clustered index.
Ø Create index in1 on cust(cno)
PRIMARY KEY INDEX UNIQUE INDEX
1. Uniquely identified row. Record identified based on Records identified based on
the index the index
2. No duplicated rows, no Duplicate rows, null values No duplicate rows
null values are allowed
3. Can consist of single or Dan consist of single or Can consist of single or
multiple columns multiple columns columns
4. Which will be stored in Which is stored in SYSINDEX Which is stored in sysindex
SYSKEYS.
VIEWS:
CREATE VIEW CVIEW(VCNO,VCNAME,VCLOC) AS
(SELECT CNO,CNAME,CLOC FROM CUST WHERE
CNAME LIKE “%X%)
Ø Logical representation of the table
Ø Stored in virtual memory
Ø Can be derived from single table or multiple tables
Ø Views are updateable if they are derived from single table without any column functions , group by
Ø Multiple views can be generated from single table.
Ø Views are stored in sysviews
Advantages of Views:
Ø Data security
Ø Data correctness
Ø Logical data independence
Ø Part of the information can be visible to the sers
Ø Accessing can be faster.
DELETE RULES:
Ø Delete rules can be applied for delete command against Database.
Ø Delete rules are 3 types
1. on delete cascade – all matching child rows will be deleted automatically when we delete parent row.
2. on delete restrict – all matching rows will be restricted when we delete parent row which is default.
3. on delete set null – all matching child row will be set to null when we delete parent row.
UNION:
Ø UNION is used to concatenate rows into one table from single or multiple tables.
Ø Rules : no. of columns & data type of both the queries must be same. column may be different
Ø UNION can be used to eliminate duplicate rows
Ø UNION ALL retrieved duplicate rows also.
SELECT CNO,CNAME FROM CUST WHERE CNO=10
UNION/UNIONALL
SELECT CNO,CNAME FROM ITEM WHERE INO=20
JOINS:
Ø JOINS can be used to concatenate columns from one table or multiple tables.
Ø JOIN types are :
1. left outer join : which can be used to retrieve matching, non matching rows from leftside table
2. right outer join: which can be used to retrieve matching, non matching rows from right side table.
3. full outer join: which can be used to retrieve matching, non matching rows from both the tables.
4. self join or inner join : can be achieved by defining alias for the table.
EXPLAIN :
It can be used to evaluate the performance of SQL queries.
It can be used to tune SQL queries.
Input is SQL queries and output is plan-table.
For every SQL query one plan-table will generate.
All plan-tables are stored in physical seq file.
Plan table
Query table no.of index no.of indexs owner join type groupby cputime
block no name cols
1 cust 10 in1 1 custc self y 10 min
DB2 CATALOG:
Ø Consists of Table pace, Index space, Index, unique index, Views, Alias, synonyms, keys.
Ø When we create table, the details of table are entered in Systable automatically.
SysIBM.SYSTABLE
Table Name
No.of cols
Owner name
Created by
Created date
Created time
Cust
10
Abc
Xyz
02-apr-2004
0850
Item
15
Mno
Rst
06-apr-2004
1020
SysIBM.SYSINDEX
Index name
Table Name
No.of cols
Owner name
Created by
Created date
Created time
In1
Cust
10
Abc
Xyz
02-apr-2004
0850
In2
Item
15
Mno
Rst
06-apr-2004
1020
SysIBM.SYSCOLS
Col name
Table name
Index name
Primary key
Foreign key
Cno
Cust
In1
Cno
-----
Cname
Cust
In1
Cno
Cloc
Cust
In2
cno
Ino
Item
Ino
Cno
Iname
Item
Ino
Cno
Ides
Item
Ino
cno
SysIBM.SYSKEYS: All primary & foreign keys.
Grant table syscols to all
Grant table syscols(select/delete/update) to user1,user2
Revoke table syscols from all.
CATALOG:
SYSTABLE, SYSCOL, SYSKEYS, SYSINDEX, SYSPKS, SYSFKS, SYSALIAS, SYSSYNONYMS, SYSINDEX, SYSVIEWS,SYSTABLESPACE, SYSINDEXSPACE.
PRECOMPILATION PROCESS:Pre compiler takes COBOL+DB2 program as input & generates DBRM which will be stored in userdefined PDS as separate member.
DSNHPC --- IBM supplied utility used for precompilation.
Precompiler functions:
Ø Separates SQL & COBOL statements
Ø Check SQL syntaxs
Ø Replace all SQL statements with host language call statements in the compiled program.
Ø Which generates timestamp tokens
BIND:
BIND takes DBRM as input & generate package & application plan. The package will be loaded to the directory. Plan will be loaded to sysplans.
Bind functions:Ø Checks authorization for SQL statement
Ø Checks the syntax errors of SQL statements like
1. Missing co name in the select list & used in order by & group by
2. Mismatch columns host variables
3. Data type mismatch of columns & host variables
4. Indicator variables not declared
5. Data truncation.
BIND SUBCOMPONANTS/PARAMETERS:
OPTIMIZER:Ø It generates optimized access path by analyzing the statistics of SQL statements which will be stored.
Ø RUNSTATS utility is one of the ISPF panel option which is stored in DB2 defaults option.
Ø Optimized path is stored in package which is not executable module.
ISOLATION LEVEL:Which can be used to lock the database at the time of executing SQL statements.
Cusrsor stability(CS): It acquires the address of a row. Sets the pointer to a specified row based on SQL query & acquires the lock against that row. Then releases the klock after the transaction before commit.
Repeatable Read(RR): which acquires the address of a row & acquire lock against the page(1 page -4024 bytes) & then released the lock after the commit statements.
Default is RR.
RUNTIME SUPERVISOR:Which is to oversee execution of SQL statements.
Statistics like no of tables, columns, indexes, keys
PLAN/APPLICATION PLAN:
It consists of executable module which is actual output of SQL statements which must be specified in the RUNJCL to execute SQL queries if the program is batch program. If the program is online which must be specified in RCT. Application plan will be loaded to load module with time stamp tokens.
COBOL COMPILATION:The compiler takes COBOL statement as input to generate object program, & loaded to the load module by line/edit with time stamp tokens.
COBOL + DB2
PRECOMPILER
COBOL
DBRM
BIND
PACKAGE
PLAN
OBJ PROG
OBJ PROG
APPL PLAN
Run time supervisor
UTILITIES USED:
DSNHPC : system utility pre compiler.
IKJEFT01 or IKJEFT01B --- BIND
IGYCRCTL or IKFCBLOO --- COBOL compilation
IEWL or HEWL --- link/edit
INTERVIEW QUESTIONS:What is RI ? where did u use RI in your project? Explain with example?
What is the difference between primary key, foreign key, index & unique index?
Can we create index when table has duplicate rows?
Can we create unique index when table has duplicate rows?
Can we create index or unique index on empty table?
What happens to the package when index is dropped? What’s the process or steps to be taken?
How to delete package?
Where package is stored? How to retrieve package?
Difference between plan & package?
What are the steps to be taken when SQL statements are changed without changing any COBOL statements?
Do we need to pre compile when index is dropped?
Can we bind when table is dropped?
Can optimized access path consist of multiple DBRMS.
What is the significance of timestamp tokens?
What is the significance of normalization?
Where do we specify run program, plan name, library & DB2 subsystem
IBM supplied utility to run COBOL + DB2
What is difference between DB2 & COBOL files & give some example for COBOL files & DB2 tables related to your project?
Can we load data from sequential file to table or table to sequential file?
What are the steps to be followed to develop DB2+ COBOL program?
Can we prepare a program/compile when DB2 system is down?
How to identify DB2 test or production system by seeing run JCL?
What is the output of explain?
What is the difference between correlated sub query & sub query?
How to find 4th max sal?
How to find nth max sal?
How to evaluate SQLcodes & where it is stored?
How to count total no of unique rows from the table?
How to sum repeated rows?
How to write a cobol program for above query? Retrieve row by row & use cobol logic to sum repeated rows?
What is the significance of DCLGEN?
Where DCLGEN is stored?
Difference between join & union?
difference between UNION & UNIONALL?
Can be have different col names in union?
How do u evaluate/tune/analyze SQL queries?
What are the JCL utilities for compile, pre compile, bind & link edit?
Wha5t is the significance of isolated levels?
Can we alter foreign key?
Can we alter primary key?
Can we alter data type & length?
What are the equivalent cobol variables for varchar?
What is the time stamp & its format?
We appreciate your feedback. Please click on Ads by Google
Repository: CICS
CCS: Customer Information System
Command Level Language
Online Programs
Multi Tasting
Multithreading
BMS is used for Develop screens
Basic Mapping Support (Assembler Maoro)
SDF is a tool used for developing screens
Screen Definition Facility
MAPSET 1-8 Alpha Numeric – MAPSET --- DFHMSD
MAP1
MAP2
MAP3 (Defined Field
Hierarchy Mapset Defination)
1-8 --------- MAP ------ DFHMDI
1-30 ------ FIELDS ---- DFHMDF
Text Fields Nonenterable
MAP is nothing but screen
MAP Consists of Fields (Protect & Un Protect)
Text Fields Variables
User id Source BMSMACRO (BMS001)
72
User Defined ` Operands Parameters
Names
DEHMSD TYPE = MAP or DSECT or &SYSPARM X MODE = IN/OUT or INOUT X
MAPS 001(S) LANGUAGE = COBOL X
Mandotory CNTL=(FRSET, PRINT,FREEKB,ALARAM)
To release the lock for keyboard
or
To unlock Keyboard
TIOAPEX = YES
Terminal Inputoutput Area prefix – Used to reserve
12 bytes of storage for I/O commands
MAPS001 Compiler
Customer Details
Cust.No. ------
Cust.Name -----
VARIABLES
MAP (object Prog)
IOAFL Store in Load Stored in
Module Copy library
IOAFL as a member
Physical Map Symbolic Map
I = Input
O=Output Used by end user Used by Appln Programmer
A=Attribute
F=Field Stored in Loadlib Stored in Copylib
L=Length
Parameter:-
In Symbolic Map each variable is divided into 5 parts like I,O,A,FL
Type = MAP ----- Generates Physical map
Type = DSECT --- Generates Symbolic Map
Type = &SYSPARM --- Generates both Physical & symbolic Map
1. Physical MAP:-
It is the actual output of Assembler Macro . i.e.object program which consists of text & variables
It will be copied to load module
Can be used by End user
2. Symbolic MAP:-
Which Consists of variable (5 Variables generated for variable)
Stored in copy library i.e. user defined PDS as separate member which must be copied into Application program.
Can be used by Application Programmer to receive or send data to the user or to chage field Attributes or to position the cursor at required location.
MAPS00IS DFHMSD Type = MAP or DSECT or &SYSPARM, X
Mode = In/out or INOUT X
LAN = COBOL X
TIOAPFX = YES
CNTL = (FRSET,PRINT, FREEKB) X
MAP001M DFHMDI SIZE = (24,80) --- Default X
= (10,20)
10 = Rows, 20 = Cols.
DFHMDF Pos = (1,10) X
10= St Pos of Col, 1 = St pos of Row
Initial = “ Customer Details) X
Length = 25
DFHMDF Pos = (2,10) X
Initial = “………………” X
Length = 25 X
DFHMDF Pos = (4,1) X
Initial = “Cust.No” X
Length = 8 X
Mcust No DFHMDF Pos = (4,11) X
ATTRB = (Unprotect / Protect,IC, Red,Drk,Skip,Fset) X
Input Cursor or
Insert Cursor Color
Dark
To skip to next enterable field
PICIN = “9(S)” X
PICOUT = “Z(5)” X
Length = 16 X
DFHMDF Pos = (7,1) X
Initial = “Cust.Name” X
Length = 9 X
McustNam DFHMDF Pos = (7,12) X
PICIN = “x(10)” X
PICOUT = “x(10)” X
Attrb: (Unprotect/Red/Drk/Askip/FSET) X
USERID SOURCE COPLIB (MCT001M)
01 MCNST001I
02 MCNST NO I PIC 9(5)
02 Filler PIC X(12) ---- For Commands
02 MCNST NO L PIC S9(4)Comp --- Automatically Generated by system
02 MCNST NO A PIC X(1)
02 MCNST NO F Redefines Mcnst No A PIC X(1)
02 MCNSTNAMI PIC X(10)
02 Filler PIC X(12)
02 MCNSTNAML PIC S9(4)Comp
02 MCNSTNAMA PIC X(1)
02 MCNSTNAMF Redefines McnstNam A PIC X(1)
01 Mcnst0010 Redefines MCnst001I
02 McnstNoo PIC Z(5)
02 McnstNamO PIC X(10)
WORKING ---- STORAGE SECTION
Copy MCT001M
STEPS TO WRITE ASSEMBLER MACRO:-
Create PDS & Member then code Assembler Macro by using DFHMSD, DFHMD1, DFHMDF
Compiler Assembelr Macro by using JCL or ISPF panel
Go to Spool by = S.ST find for syntax errors
Type F MNOTE in command prompt or ERROR if there are any syntax error
COMPILE
ASSEMBLE
LINK EDITORS
Exec CICS
SEND TEXT
Form (“ Contact system Admin”) ……….. Like display “Contact system Admin”
Contact system Admin
End - Exec
Exec CICS
Send Text
From (WS – MSG) using Variable instead of Static Text
End – Exec
Exec CICS
Send CICS COBOL
MAP(“Mcnst001M”) SEND DISPLAY
MAPset(“Mcnst001S”) RECEIVE ACCEPT
End – Exec
Exec CICS
Send Variables
MAP (WS-MAP)
MAP set (WS-MAPSET)
End – Exec
Exec CICS
Receive
MAP (WS-MAP)
MAP set (WS-MAPSET)
End – Exec
Customer Details
Cust.No. = 10
Cust.Name = XYZ
DB2 Normal Conversation
CUSTOMER DETAILS
Cust.No …………………..
Cust Name ……
………….
Pr Psendo Conversation
Reinificati
Termination Reinifiation
At the time of waiting for response, release the resources i.e. terminate the transaction or program (Connection).After receiving the response, reinitiate the transaction or program . This Conversion is called psendo conversion.
In system view, it is two step process
v 1. Termination
v 2. Reinitiation
In user view, it is only single step
Exec CICS
Return
TransID (“TO01”) User Defined
End – Exec
PSEVDO CONVERSATION:-
TRANS
TRO1
ONLINE SYSTEM
MAP DisplayedNormal Conversation Psendo Conversation
When message or Map sent to the terminal, transaction will be terminated & waiting for the users response.
After receiving response from the user reinitiate the transaction (acquirer all the resources)
In system view, multitasking
In user view, single task.
ADVANTAGES:-
Releases all the resources at the time of waiting for the users response
COMMUNICATION AREA:-
At the time of terminating the transaction, the working storage – section variables are stored into a variable declared in linkage section
This area is called communication area
It is global
The max six of this variable is 6JK.
(linkage Section)
01 DFHCOMMAREA PIC X (65,53J)
At the time of reinitiation the data is retrieve from the variable
Communication Area can be used to pass the data from on transaction to another.
For the first entry of transaction the Communication Area is empty
For next entry, it must not be zero.
PROCE4DURE DIVISION
If EIBCALEN = 0 To check the length of Communication Area, it is zero for first entry, non zero
For next entry.
Exec CICS
Send
MAP (MAPNAME)
MAPSET (MAPSET)
End – exec
Exec CICS
Return
Transid (“TR01”)
End-Exec
Else
Exec CICS
Receive MAP (MAPNAME)
MAPSET (MAPSET)
End – Exec
End-it
Cust.No. …….
Cust Name ……..
Message: ………
Enter F2 F3 F4If EIBCALEN = 0
Exec CICS
Send
MAP(MAPNAME)
MAPSET(MAPSETNAME)
End – Exec
Exec CICS Functionlays
Return
Transid)”TR01”) Stored
EIBAID
End – Exec.
End – If DFHAID Attention Identifers
(F1 –F24)
Perform Receive- Map – Para three Receive – Map – Exit
Perform Function – Keys – Para three Function – Keys – Exit
Receive – Map – Para
Exec CICS
Receive
MAP(MAPNAME) Copy DFHAID in WS - Section
MAPSET(MAPSETNAME)
End – Exec 01 EIBAID PIC X(4)
88 DFHPF1 Value “F1”
Receive – Map – Exit 88 DFHPF2 Value “F2”
88 DFHEnter Value “Enter” Exit
Function – Keys – Para
Evaluate EIBAID
When DFHEnter 88 DFHPF24 Value “F24”
Perform validation – logic three validation – logic – exit
When DFHPF3
Pefrom Exit – logic three exit – logic –Exit
When DFHPF10
Perform Insert – logic three Insert – logic – Exit
When DFHPF11
Perform update – logic three update – logic – Exit
When Other
Perform Invalid – Key three Invalid – Key – Exit
End – Evaluate
Function – keys – exit In map
1 line --- Header
Exit 23line --- Messages
24 or last --- Footer
Validation – logic
If McnstNoI = Zeroes
Move “Invalid cust. No.” to MMESGO
Exec CICS Move - 1 to McnstNol – Dynomic way to position the cursor
Send
MAP (MAP NAME)
MAPSET(MAPSETNAME)
End – Exec
Exec CICS To Dynamically position the
cursor
Return 1. Move – 1 to Mcnst NoL
Transid(“TR01”) i.e. Lengthfield
2. Send
End – Exec Map (…………….)
End if Map (…………….)
Cursor (200) relative
If Mcnst NoI No is not Numeric cursor position
Move Spaces to MMESGO
Move “Must be Numeric” to MMESGO
Move - 1 to Mcnst NoL
Exec CICS
Send
MAP (MAPNAME)
MAPSET(MAPSETNAME)
End –exec
Exec CICS Send Para
Return
Transid(“TR01”)
End – Exec
End if
If Mcnst NoI (1:1) < > 1
Move Spaces To MMESGO
Move “First digit Must be 1” to MMESGO
Move – 1 To McnstNoL
Exec CICS
Send
MAP(MAPNAME)
MAPSET(MAPSET NAME)
End – Exec
Exec CICS
Return
Transid (“TR01”)
End-Exec End-If
Move McnstNoI to (DFHCommArea(1:5)) Ws-Cust-No
If McnstNamI = Spaces
Move “Invalid Name” to MMESGO
Move – 1 toMcnstNamL
Perform Send – Para
End If
Move McnstNamI to(DFHCommArea(6:10)) Ws-Cust-Name
Move Ws-Comm-area to DFHcommArea
CURSOR TECHINQUES:-
Three Types of Cursor techniques
(i) Static –By specifying i.e in Assembler macro, Cursor can be positioned at
required field.
(ii) Dynamic Cursor position –Dynamically cursor can be positioned by assigning - 1 to the length field from symbolic map.This technique is used in application program more frquently
Eg:- Involid Data validation or to jump from one field to another field
(iii) Relative cursor position – Cursor can be positioned at required location by specifying relative position relative to zero in the Cursor option with send map command.
DFH Comm Area:
System defined global variable which must be declared in linkage section with 01 level no
Which can be used to pass the data from on program to another program, on transaction to another transaction.
Max size area for DFH Comm Area is 65Kbytes
PROCEDURE DIVISION :-
Move Spaces to DFH Comm Area
I nitialise DFH Comm Area ----- Aliphanumaric, Numaeric
To transfer the control from one program to another
We use XCIL
Exec CICS
XCTL
Program (“CICSOOL”)
CommArea(DFHcommArea)
Length (Comm-Length)
End- Exec
We can use link to transfer the control
The diff between XCTL &LINK is when we use XCTL the control is not Expected to back to the screen but when we use link it will expect that the control will back
Exec CICS
LINK
Program(“CICS001”)
CommAre(DFHCommArea)
Length(Comm – length)
End Exec
If we use F8 Key Page up & F9 for Page down, data is to be transferred from database to screen.But we use temp area for storing this info.this is call queene
01 Ws-Comm-Area
02 Transfer - data
03 CustNo. PIC 9(5)
02 Save – data
03 CustNo. PIC 9(5)
03 CustName PIC X(20)
02 Multiple – rows
03 Ws-data PIC X(5) access 1,00,000times
LINK:-
Which can be used to transfer the control from one prog to another prog
Control is expected back to original prog.(no need of explicit CICS command to transfer back)
Ef:- Validations
Socket programming
Complex business functionalities
XCTL:-
Which can be used to transfer the control from one prog. To another Prog.
Control is not expected back to original prog
To get the control back to main prog XCITL command must be used in sub program
Eg:- Transfering the control from main menu (prog) to sub prog or subprog to main menu(Prog)
Link --- to lower level
XCTL --- to same level
Return --- to Higher level
MOVE 30 TO WS_TSO_LEN
Exec CICS Ws - Section
01 Ws -TSQ - AREA
Write Ts 02 Ws –TSQ-CusfNo Pic 59(5)
QUEUE(“TS001”) 02 Ws- TSQ – CustName Pic X(10)
From (Ws-TSQ-AREA) 02 Ws-TSQ – CustLoc Pic X(10)
LENGTH (Ws-TSQ-LEN) 01 Ws-TSQ-LEN Pic 59(4) comp
ITEM(Ws-TSQ-Item)
Main/Aux
If we want to define that Quene auxiliary memory.We have to define Quene id TST table it is CICS supplied table.
End-Exec
Add + 1 to Ws-TSQ-Item
To define queid in TST exate member &write the following assembler maoro.
USEID SOURCE DFHTST (TS0001)
DFHTST Type = “Queue” TST = Temporary Storage Table
QueID = “TS001”
TO Retrieve the data from queue to structure:-
Move +1 to Ws-TSQ-ITEM
Perform until Qzero (End of Queue)
Exec CICS
ReadQ TS
QUEUE (“TS001”)
Into(Ws-TSQ-AREA)
Length(Ws-TSQ-LEN)
Item(Ws-TSQ-Item)
End – Exec
Add +1 to Ws – TSQ – Item
End – Perform
Response Code:- To find the end of the queue
Deleting the Queue:
Exec CICS
DeleteQTS We cannot delete single row in queue
QUEUE(“TS001”) When we apply delete command the queue
End - Exec becomes empty
Queue is global
Up dating The Queue:-
Exec CICS
RewriteQts
From (Ws-TSQ-Area)
Queue(“TS001”)
Length(Ws-TSQ-Len)
Item(Ws-TSQ-Item)
End – Exec
TSQ:- Temporary storage queue
Which is similar to physical sequential file
Records entered in entry – sequential order with relative record number
Records can be sequentially or Random by using item number which must be defined in Working – Storage Section as separate field
The Queue name must be user defined i.e. 1-8 Alph numeric characters must be unique in system
TSQ can be storage in main memory or Auxiliary memory (QueueID or Queue Name must be defined in TST(Temp Storage Table) which is CICS supplied table
TSQ can be used to pass the data within CICS
TSQ can be used to reduce no. of DB2 calls & improve program efficiency
TSQ can be used for page up, Page down logic
TSQ can be used for reporting printing online
Entire TSQ data can be deleted by using delete command (No specific row deletion)
TDQ:- Transient Data Queue
Used to pass the data from one CICI to another CICS region
Used to pass the data from CICS to Batch Program
TDQ
Intra Partition Extra Partition CICS to CICS region CICS to Batch Delete, Write Delete Write Apply
While using TSQ, we can read the records any no of times
While using TDQ, multiple read against TSQ is not possible
Read is destructive i.e. when we read the queue the data is lost
Updation is not possible
Delete can apply to intra partition but not to extra position.
TDQ can be stored in main or auziliary.
If we store in Auxiliary we need to define
Queue ID DCT Table (Destination Control Table)
DFHDCT Type = QUEUF
Type = Intra/ Extra
Queid = “TD001”
Trigerleve = 1000
Program = Cobol
01 Ws-Hd
02 Ws – Sub – He – 1 PIC X(100) value “Customer Details”
02 Ws – Sub – He – 2 PIC X (100) value “Page No”
01 Ws-line PIC X(100) value A;;” ---“
PROCEDURE DIVISION
WRITE Cust - Rep from Ws-Sub-He-1
WRITE Cust - Rep from Ws-Line
WRITE Cust – Rep from Ws-Sub – He – 2
TSQ TDQ
Records entered in entry Sep. order Records entered in entry sep.order with
With relative record number out relative record number
Multiple read commands can be applied Only one read command can be applied
Against rows against rows (Read is destructive)
Records can be accessed Sepertially Only sequential access
Or random
Queue must be defined in TST if the Queue must be defined m DCT
Data store in auxiliary memory (Destination Control Table)
Can be used to pass the data from Can be used to pass data withinCICS
One prog to another within CICS region region or outside of CICS region like
Batch
Eg:- Report printing, Trigessing Batch Prog
Bb specifying Trigger level
Updation is passible Updation is not possible
CICS Supplied Tables
PCT -- Program control Table --- Transaction
PPJ -- Processing Prog Table -- Program / Mapsets
TCT – Terminal Control Table
DFHPCT TYPE = TRANSACTION
Transid = “TR01”
Program = “CICS001”
Program = “CICS002”
DFHPPT TYPE = Program Type = MAPSET
Program = “CICS001” MAPSET = Mcnstcol
TransID = “TR01” Tra
Command Level Language
Online Programs
Multi Tasting
Multithreading
BMS is used for Develop screens
Basic Mapping Support (Assembler Maoro)
SDF is a tool used for developing screens
Screen Definition Facility
MAPSET 1-8 Alpha Numeric – MAPSET --- DFHMSD
MAP1
MAP2
MAP3 (Defined Field
Hierarchy Mapset Defination)
1-8 --------- MAP ------ DFHMDI
1-30 ------ FIELDS ---- DFHMDF
Text Fields Nonenterable
MAP is nothing but screen
MAP Consists of Fields (Protect & Un Protect)
Text Fields Variables
User id Source BMSMACRO (BMS001)
72
User Defined ` Operands Parameters
Names
DEHMSD TYPE = MAP or DSECT or &SYSPARM X MODE = IN/OUT or INOUT X
MAPS 001(S) LANGUAGE = COBOL X
Mandotory CNTL=(FRSET, PRINT,FREEKB,ALARAM)
To release the lock for keyboard
or
To unlock Keyboard
TIOAPEX = YES
Terminal Inputoutput Area prefix – Used to reserve
12 bytes of storage for I/O commands
MAPS001 Compiler
Customer Details
Cust.No. ------
Cust.Name -----
VARIABLES
MAP (object Prog)
IOAFL Store in Load Stored in
Module Copy library
IOAFL as a member
Physical Map Symbolic Map
I = Input
O=Output Used by end user Used by Appln Programmer
A=Attribute
F=Field Stored in Loadlib Stored in Copylib
L=Length
Parameter:-
In Symbolic Map each variable is divided into 5 parts like I,O,A,FL
Type = MAP ----- Generates Physical map
Type = DSECT --- Generates Symbolic Map
Type = &SYSPARM --- Generates both Physical & symbolic Map
1. Physical MAP:-
It is the actual output of Assembler Macro . i.e.object program which consists of text & variables
It will be copied to load module
Can be used by End user
2. Symbolic MAP:-
Which Consists of variable (5 Variables generated for variable)
Stored in copy library i.e. user defined PDS as separate member which must be copied into Application program.
Can be used by Application Programmer to receive or send data to the user or to chage field Attributes or to position the cursor at required location.
MAPS00IS DFHMSD Type = MAP or DSECT or &SYSPARM, X
Mode = In/out or INOUT X
LAN = COBOL X
TIOAPFX = YES
CNTL = (FRSET,PRINT, FREEKB) X
MAP001M DFHMDI SIZE = (24,80) --- Default X
= (10,20)
10 = Rows, 20 = Cols.
DFHMDF Pos = (1,10) X
10= St Pos of Col, 1 = St pos of Row
Initial = “ Customer Details) X
Length = 25
DFHMDF Pos = (2,10) X
Initial = “………………” X
Length = 25 X
DFHMDF Pos = (4,1) X
Initial = “Cust.No” X
Length = 8 X
Mcust No DFHMDF Pos = (4,11) X
ATTRB = (Unprotect / Protect,IC, Red,Drk,Skip,Fset) X
Input Cursor or
Insert Cursor Color
Dark
To skip to next enterable field
PICIN = “9(S)” X
PICOUT = “Z(5)” X
Length = 16 X
DFHMDF Pos = (7,1) X
Initial = “Cust.Name” X
Length = 9 X
McustNam DFHMDF Pos = (7,12) X
PICIN = “x(10)” X
PICOUT = “x(10)” X
Attrb: (Unprotect/Red/Drk/Askip/FSET) X
USERID SOURCE COPLIB (MCT001M)
01 MCNST001I
02 MCNST NO I PIC 9(5)
02 Filler PIC X(12) ---- For Commands
02 MCNST NO L PIC S9(4)Comp --- Automatically Generated by system
02 MCNST NO A PIC X(1)
02 MCNST NO F Redefines Mcnst No A PIC X(1)
02 MCNSTNAMI PIC X(10)
02 Filler PIC X(12)
02 MCNSTNAML PIC S9(4)Comp
02 MCNSTNAMA PIC X(1)
02 MCNSTNAMF Redefines McnstNam A PIC X(1)
01 Mcnst0010 Redefines MCnst001I
02 McnstNoo PIC Z(5)
02 McnstNamO PIC X(10)
WORKING ---- STORAGE SECTION
Copy MCT001M
STEPS TO WRITE ASSEMBLER MACRO:-
Create PDS & Member then code Assembler Macro by using DFHMSD, DFHMD1, DFHMDF
Compiler Assembelr Macro by using JCL or ISPF panel
Go to Spool by = S.ST find for syntax errors
Type F MNOTE in command prompt or ERROR if there are any syntax error
COMPILE
ASSEMBLE
LINK EDITORS
Exec CICS
SEND TEXT
Form (“ Contact system Admin”) ……….. Like display “Contact system Admin”
Contact system Admin
End - Exec
Exec CICS
Send Text
From (WS – MSG) using Variable instead of Static Text
End – Exec
Exec CICS
Send CICS COBOL
MAP(“Mcnst001M”) SEND DISPLAY
MAPset(“Mcnst001S”) RECEIVE ACCEPT
End – Exec
Exec CICS
Send Variables
MAP (WS-MAP)
MAP set (WS-MAPSET)
End – Exec
Exec CICS
Receive
MAP (WS-MAP)
MAP set (WS-MAPSET)
End – Exec
Customer Details
Cust.No. = 10
Cust.Name = XYZ
DB2 Normal Conversation
CUSTOMER DETAILS
Cust.No …………………..
Cust Name ……
………….
Pr Psendo Conversation
Reinificati
Termination Reinifiation
At the time of waiting for response, release the resources i.e. terminate the transaction or program (Connection).After receiving the response, reinitiate the transaction or program . This Conversion is called psendo conversion.
In system view, it is two step process
v 1. Termination
v 2. Reinitiation
In user view, it is only single step
Exec CICS
Return
TransID (“TO01”) User Defined
End – Exec
PSEVDO CONVERSATION:-
TRANS
TRO1
ONLINE SYSTEM
MAP DisplayedNormal Conversation Psendo Conversation
When message or Map sent to the terminal, transaction will be terminated & waiting for the users response.
After receiving response from the user reinitiate the transaction (acquirer all the resources)
In system view, multitasking
In user view, single task.
ADVANTAGES:-
Releases all the resources at the time of waiting for the users response
COMMUNICATION AREA:-
At the time of terminating the transaction, the working storage – section variables are stored into a variable declared in linkage section
This area is called communication area
It is global
The max six of this variable is 6JK.
(linkage Section)
01 DFHCOMMAREA PIC X (65,53J)
At the time of reinitiation the data is retrieve from the variable
Communication Area can be used to pass the data from on transaction to another.
For the first entry of transaction the Communication Area is empty
For next entry, it must not be zero.
PROCE4DURE DIVISION
If EIBCALEN = 0 To check the length of Communication Area, it is zero for first entry, non zero
For next entry.
Exec CICS
Send
MAP (MAPNAME)
MAPSET (MAPSET)
End – exec
Exec CICS
Return
Transid (“TR01”)
End-Exec
Else
Exec CICS
Receive MAP (MAPNAME)
MAPSET (MAPSET)
End – Exec
End-it
Cust.No. …….
Cust Name ……..
Message: ………
Enter F2 F3 F4If EIBCALEN = 0
Exec CICS
Send
MAP(MAPNAME)
MAPSET(MAPSETNAME)
End – Exec
Exec CICS Functionlays
Return
Transid)”TR01”) Stored
EIBAID
End – Exec.
End – If DFHAID Attention Identifers
(F1 –F24)
Perform Receive- Map – Para three Receive – Map – Exit
Perform Function – Keys – Para three Function – Keys – Exit
Receive – Map – Para
Exec CICS
Receive
MAP(MAPNAME) Copy DFHAID in WS - Section
MAPSET(MAPSETNAME)
End – Exec 01 EIBAID PIC X(4)
88 DFHPF1 Value “F1”
Receive – Map – Exit 88 DFHPF2 Value “F2”
88 DFHEnter Value “Enter” Exit
Function – Keys – Para
Evaluate EIBAID
When DFHEnter 88 DFHPF24 Value “F24”
Perform validation – logic three validation – logic – exit
When DFHPF3
Pefrom Exit – logic three exit – logic –Exit
When DFHPF10
Perform Insert – logic three Insert – logic – Exit
When DFHPF11
Perform update – logic three update – logic – Exit
When Other
Perform Invalid – Key three Invalid – Key – Exit
End – Evaluate
Function – keys – exit In map
1 line --- Header
Exit 23line --- Messages
24 or last --- Footer
Validation – logic
If McnstNoI = Zeroes
Move “Invalid cust. No.” to MMESGO
Exec CICS Move - 1 to McnstNol – Dynomic way to position the cursor
Send
MAP (MAP NAME)
MAPSET(MAPSETNAME)
End – Exec
Exec CICS To Dynamically position the
cursor
Return 1. Move – 1 to Mcnst NoL
Transid(“TR01”) i.e. Lengthfield
2. Send
End – Exec Map (…………….)
End if Map (…………….)
Cursor (200) relative
If Mcnst NoI No is not Numeric cursor position
Move Spaces to MMESGO
Move “Must be Numeric” to MMESGO
Move - 1 to Mcnst NoL
Exec CICS
Send
MAP (MAPNAME)
MAPSET(MAPSETNAME)
End –exec
Exec CICS Send Para
Return
Transid(“TR01”)
End – Exec
End if
If Mcnst NoI (1:1) < > 1
Move Spaces To MMESGO
Move “First digit Must be 1” to MMESGO
Move – 1 To McnstNoL
Exec CICS
Send
MAP(MAPNAME)
MAPSET(MAPSET NAME)
End – Exec
Exec CICS
Return
Transid (“TR01”)
End-Exec End-If
Move McnstNoI to (DFHCommArea(1:5)) Ws-Cust-No
If McnstNamI = Spaces
Move “Invalid Name” to MMESGO
Move – 1 toMcnstNamL
Perform Send – Para
End If
Move McnstNamI to(DFHCommArea(6:10)) Ws-Cust-Name
Move Ws-Comm-area to DFHcommArea
CURSOR TECHINQUES:-
Three Types of Cursor techniques
(i) Static –By specifying i.e in Assembler macro, Cursor can be positioned at
required field.
(ii) Dynamic Cursor position –Dynamically cursor can be positioned by assigning - 1 to the length field from symbolic map.This technique is used in application program more frquently
Eg:- Involid Data validation or to jump from one field to another field
(iii) Relative cursor position – Cursor can be positioned at required location by specifying relative position relative to zero in the Cursor option with send map command.
DFH Comm Area:
System defined global variable which must be declared in linkage section with 01 level no
Which can be used to pass the data from on program to another program, on transaction to another transaction.
Max size area for DFH Comm Area is 65Kbytes
PROCEDURE DIVISION :-
Move Spaces to DFH Comm Area
I nitialise DFH Comm Area ----- Aliphanumaric, Numaeric
To transfer the control from one program to another
We use XCIL
Exec CICS
XCTL
Program (“CICSOOL”)
CommArea(DFHcommArea)
Length (Comm-Length)
End- Exec
We can use link to transfer the control
The diff between XCTL &LINK is when we use XCTL the control is not Expected to back to the screen but when we use link it will expect that the control will back
Exec CICS
LINK
Program(“CICS001”)
CommAre(DFHCommArea)
Length(Comm – length)
End Exec
If we use F8 Key Page up & F9 for Page down, data is to be transferred from database to screen.But we use temp area for storing this info.this is call queene
01 Ws-Comm-Area
02 Transfer - data
03 CustNo. PIC 9(5)
02 Save – data
03 CustNo. PIC 9(5)
03 CustName PIC X(20)
02 Multiple – rows
03 Ws-data PIC X(5) access 1,00,000times
LINK:-
Which can be used to transfer the control from one prog to another prog
Control is expected back to original prog.(no need of explicit CICS command to transfer back)
Ef:- Validations
Socket programming
Complex business functionalities
XCTL:-
Which can be used to transfer the control from one prog. To another Prog.
Control is not expected back to original prog
To get the control back to main prog XCITL command must be used in sub program
Eg:- Transfering the control from main menu (prog) to sub prog or subprog to main menu(Prog)
Link --- to lower level
XCTL --- to same level
Return --- to Higher level
MOVE 30 TO WS_TSO_LEN
Exec CICS Ws - Section
01 Ws -TSQ - AREA
Write Ts 02 Ws –TSQ-CusfNo Pic 59(5)
QUEUE(“TS001”) 02 Ws- TSQ – CustName Pic X(10)
From (Ws-TSQ-AREA) 02 Ws-TSQ – CustLoc Pic X(10)
LENGTH (Ws-TSQ-LEN) 01 Ws-TSQ-LEN Pic 59(4) comp
ITEM(Ws-TSQ-Item)
Main/Aux
If we want to define that Quene auxiliary memory.We have to define Quene id TST table it is CICS supplied table.
End-Exec
Add + 1 to Ws-TSQ-Item
To define queid in TST exate member &write the following assembler maoro.
USEID SOURCE DFHTST (TS0001)
DFHTST Type = “Queue” TST = Temporary Storage Table
QueID = “TS001”
TO Retrieve the data from queue to structure:-
Move +1 to Ws-TSQ-ITEM
Perform until Qzero (End of Queue)
Exec CICS
ReadQ TS
QUEUE (“TS001”)
Into(Ws-TSQ-AREA)
Length(Ws-TSQ-LEN)
Item(Ws-TSQ-Item)
End – Exec
Add +1 to Ws – TSQ – Item
End – Perform
Response Code:- To find the end of the queue
Deleting the Queue:
Exec CICS
DeleteQTS We cannot delete single row in queue
QUEUE(“TS001”) When we apply delete command the queue
End - Exec becomes empty
Queue is global
Up dating The Queue:-
Exec CICS
RewriteQts
From (Ws-TSQ-Area)
Queue(“TS001”)
Length(Ws-TSQ-Len)
Item(Ws-TSQ-Item)
End – Exec
TSQ:- Temporary storage queue
Which is similar to physical sequential file
Records entered in entry – sequential order with relative record number
Records can be sequentially or Random by using item number which must be defined in Working – Storage Section as separate field
The Queue name must be user defined i.e. 1-8 Alph numeric characters must be unique in system
TSQ can be storage in main memory or Auxiliary memory (QueueID or Queue Name must be defined in TST(Temp Storage Table) which is CICS supplied table
TSQ can be used to pass the data within CICS
TSQ can be used to reduce no. of DB2 calls & improve program efficiency
TSQ can be used for page up, Page down logic
TSQ can be used for reporting printing online
Entire TSQ data can be deleted by using delete command (No specific row deletion)
TDQ:- Transient Data Queue
Used to pass the data from one CICI to another CICS region
Used to pass the data from CICS to Batch Program
TDQ
Intra Partition Extra Partition CICS to CICS region CICS to Batch Delete, Write Delete Write Apply
While using TSQ, we can read the records any no of times
While using TDQ, multiple read against TSQ is not possible
Read is destructive i.e. when we read the queue the data is lost
Updation is not possible
Delete can apply to intra partition but not to extra position.
TDQ can be stored in main or auziliary.
If we store in Auxiliary we need to define
Queue ID DCT Table (Destination Control Table)
DFHDCT Type = QUEUF
Type = Intra/ Extra
Queid = “TD001”
Trigerleve = 1000
Program = Cobol
01 Ws-Hd
02 Ws – Sub – He – 1 PIC X(100) value “Customer Details”
02 Ws – Sub – He – 2 PIC X (100) value “Page No”
01 Ws-line PIC X(100) value A;;” ---“
PROCEDURE DIVISION
WRITE Cust - Rep from Ws-Sub-He-1
WRITE Cust - Rep from Ws-Line
WRITE Cust – Rep from Ws-Sub – He – 2
TSQ TDQ
Records entered in entry Sep. order Records entered in entry sep.order with
With relative record number out relative record number
Multiple read commands can be applied Only one read command can be applied
Against rows against rows (Read is destructive)
Records can be accessed Sepertially Only sequential access
Or random
Queue must be defined in TST if the Queue must be defined m DCT
Data store in auxiliary memory (Destination Control Table)
Can be used to pass the data from Can be used to pass data withinCICS
One prog to another within CICS region region or outside of CICS region like
Batch
Eg:- Report printing, Trigessing Batch Prog
Bb specifying Trigger level
Updation is passible Updation is not possible
CICS Supplied Tables
PCT -- Program control Table --- Transaction
PPJ -- Processing Prog Table -- Program / Mapsets
TCT – Terminal Control Table
DFHPCT TYPE = TRANSACTION
Transid = “TR01”
Program = “CICS001”
Program = “CICS002”
DFHPPT TYPE = Program Type = MAPSET
Program = “CICS001” MAPSET = Mcnstcol
TransID = “TR01” Tra
Impact of ENDEVOR on SDLC
ENDEVOR
ENDEVOR is an integrated set of management tools used to automate, control, and monitor your applications development process. ENDEVOR runs under MVS, within the TSO/ISPF environment, and in batch.
Using ENDEVOR, you can:
· Track your changes against production, creating an online change history, enabling you to know what was changed, by whom, when, and why.
· Prevent conflicting changes to the same system component.
· Browse and manipulate all components relating to an application from a
· a single screen, saving you time.
· Automate executables creation.
· Ensure that the source, executable, and any other form
· (for example, listings) of an element correspond.
· Apply the same procedures (including automating compiles, impact
· Analyses, and standards checking functions) to any component type.
· Put change packages and approvals online, eliminating paperwork.
· View or retrieve prior levels of any element.
· Report on element definition, content, and change history.
· Enforce change control procedures.
Software Life Cycle :
ENDEVOR allows you to automate and control the movement of software through your software life cycle. Life cycles are site-specific. A representative life cycle might consist of five stages:
* DEVELOPMENT - Programs are developed.
* TEST - Programs are unit tested.
* QA - Applications are system tested.
* EMERGENCY - Fixes are applied to production code.
* PROD - Production applications reside.
The ENDEVOR administrator might decide to put the TEST, QA, EMERGENCY and PROD stages under the control of ENDEVOR.
Basic :
Normal change procedures include:
* Retrieving from production to a development library.
* Making changes.
* Adding/updating into the test stage.
* Moving to QA.
* Moving back into Production.
<------------- retrieve -----------------------------------
******** ******** ******** *******
* TEST * * QA * * EMER * * PROD *
* * * * * * * *
****** ****** ******** *******
$$$$$$$
$ dev $ - move --> -------- move -------------->
$ --add->
$$$$$$$$
Emergency : Emergency change procedures include:
* Retrieving from production.
* Making changes.
* Adding/updating into the emergency stage.
* Moving to Production.
---- move ------->
******** ******** ******* *******
* TEST * * QA * * EMER * * PROD *
* * * * * * * *
******** ******** ******* *******
add $$$$$$$ retrieve
$ dev $
<----- <----
$$$$$$$$
Inventory Structure :
The ENDEVOR inventory structure allows you to:
· Work with program modules without having to know where they are physically located, or how they are compiled.
· List all the program components that make up an application, regardless of type.
· Determine the location(s) of an element simply by entering the element name on a display screen.
· Act on a cross section of your program inventory.
· For example,
you can list all COBOL code in your shop, or promote an entire new release of the payroll application with a single command.
ENDEVOR can produce lists, for example, of:
* All the JCL in the shop
* All the software currently in QA
* All the manufacturing software currently being unit tested
There are many other ways to use this structure to your advantage.
Job Function
*---------------------------------------------------*
Action Dev QA/Test Turnover Audit Mgmnt Admin
*----------------------------------------------------------------
Add/Update x x x
Archive x
Copy x
Delete x x
Display x x x x x x
Generate x
List x x
Move x x x x
Print x x x x x x
Restore x
Retrieve x x x
Signin x x x
Transfer x x
*-------------*---------------------------------------------------*
ENDEVOR helps to manage the software life cycle by providing a
consistent and flexible logical structure for classifying
software inventory.
There are six components to this inventory structure: environments, stages, systems, subsystems, types, and elements.
Environments, stages, systems, subsystems, and types are set up by the ENDEVOR administrator. The actual inventory is made up of elements. Users act on elements.
1 - Environments 5 - Types
2 - Stages 6 - Elements
3 - Systems 7 - Using the inventory structure
4 - Subsystems 8 - The software life cycle
Environment :
Environment is the ENDEVOR term for the functional areas of an organization. For example, there might be separate development and production environments.
There is no limit to the number of environments that may be defined. Environments are defined in the C1DEFLTS table. Environment information can be viewed on the Site/Environment Information panel.
Stage :
Stage is the ENDEVOR term for a stage in the software life cycle. For example, a site may have stages TEST, QA, FIX, and PROD in its life cycle There must be two stages for each environment.
Stages have a name, representing their place in the life cycle (for example TEST) and an ID (1 or 2). Stages are referred to as Stage 1 (the first stage in an environment) and Stage 2 (the second stage in an environment).
Stages are defined in the C1DEFLTS table. Stage information is displayed on the Stage Information panel.
Stages can be linked together to establish unique promotion routes for program inventory within and between environments. These routes make up the map for a site.
1 - Stage information panel 2 - Mapping
Current env - Name of the environment for which stage information is shown.
Next env - The name of the next environment on the map.
Stage ID - ID of the first Stage in the next environment.
Stage 1 and 2 Information.
ID - Stage ID.
Name - Stage name.
Title - Stage description.
MCF data set name - Data set name of the Master Control File for the Stage.
Part of implementing ENDEVOR is to define the stages in the software life cycles at your site, then organize these stages into environments. See Chapter 1 of the Administrator's Guide.
Applications in each life cycle follow a unique route through the environment/stage locations that you have defined. You can set up as many routes as you need to accommodate different life cycles at your site. These routes make up the map for your site.
ENDEVOR uses these routes to automate the adding, displaying, retrieving, moving, and generating of inventory in a given life cycle.
1 - One route map - example 5 - The map and actions
2 - Two route map - example 6 - The map and lists
3 - Establishing routes 7 - The map and signout
4 - Mapping inventory locations 8 - Implementation checklist
1 - One route map - example
In its simplest form, a series of environment/stage locations can be
chained together to form a one-route map.
***************** ***************** *****************
* DEV * * QA * * PROD *
***************** ***************** *****************
* UNIT * INT * * QA * HOLD * * FIX * PROD *
* * * * * * * * *
* -----> -----------> -----> ----------------------> *
* * * * * * * * *
***************** ***************** *****************
2 - Two route map - example
Route R1 (DEV, QA, and PROD) for production applications.
Route R2 (QFIX, PROD) to handle emergency fixes to the applications.
***************** ***************** *****************
* DEV * * QA * * PROD *
***************** ***************** *****************
* UNIT * INT * * QA * HOLD * * FIX * PROD *
* * * * * * * * *
R1 * -----> -----------> -----> --------------------> , *
***************** ***************** ****************
********************
* QFIX *
********************
* TSTFIX * PREPROD *
R2 * -----> ------------
********************
System :
System is the ENDEVOR term for the applications at a site. For example, a site might have financial (system: FINANCE) and manufacturing (system: MFG) systems.
A system must be defined to each environment where it will be used. You work with systems using the System Definition panel. You can clone system definitions within or across environments using the
System Definition - Clone panel.
1 - System definition panel 2 - System definition – clone
Subsystem is the ENDEVOR term for a particular application within a system. For example, there might be purchase order (subsystem: PO) accounts payable (subsystem: AP) applications within system FINANCE.
* There must be at least one subsystem per system.
* A subsystem must be defined to each system in which it will be used.
For example, if you plan to have subsystem PO within system FINANCE, and define system FINANCE to environments TEST, QA, and PROD, you must also define subsystem PO to system FINANCE in each environment.
* A subsystem can have the same name as the system to which you
define it.
Subsystem :
You work with subsystems using the Subsystem Definition panel. Type is the ENDEVOR term for categories of source code. For example, you might create the following types: COBOL (for COBOL code);
COPYBOOK (for copybooks); JCL(for JCL streams). You must define a type to each stage in which you want to use it.
Processors :
ENDEVOR uses JCL streams called processors to automate creating executables. You can associate a group of processors with each type. Each processor group includes the processors needed for a particular type of source. For example, the processor group for type COBOL might include processors for VS COBOL and COBOL II.
You work with types on the Type Definition panel.
Naming Conventions :
Consider using generic type names, such as COBOL. You can then create as many processor groups as you need to handle the variations within each type. For instance, for type COBOL, you could create processor groups to tell ENDEVOR what type of COBOL must be processed.
A list of suggested naming standards for types follows. This list is not complete and is presented here as a guideline only.
ASEMBLER LINKCARD SORTCNTL FORTRAN
BASIC MACRO SPECS RPG
CLIST MARKV TABLES JCL
COBOL NETRON TELON REPORTS
COPYBOOK PLI TRNSFORM
EASYTRE VPROC UTILITY
Element is the term for PDS members, Panvalet or Librarian, or
sequential data sets that have been placed under control of ENDEVOR.
The element name defaults to the member name.
ENDEVOR classifies elements according to the inventory structure you
set up. Each element is described uniquely in terms of its:
* Location in the software life cycle. This is determined by the environment and stage where it resides.
* Inventory classification. This is determined by the system, subsystem, and type with which it is associated.
Working With Elements
You manipulate ENDEVOR inventory by executing commands called actions.
Some actions are available in both foreground and in batch, while
others are available only in batch.. Batch actions are also available
when you build packages.
* The ENDEVOR/MVS User's Guide explains how to execute actions in
foreground and submit batch action requests.
* The ENDEVOR/MVS SCL Reference Manual contains the syntax for
ENDEVOR's Software Control Language (SCL). SCL allows you to code
batch action requests.
Signout/signin :
Signout/signin can be enabled on a system by system basis.
Action Impact on element Options
Add/Update Signs out Override signout
Move Signs in at target Retain signout, Signout to
Retrieve Signs out Override signout, No Signout
Generate No change Override signout
(no copyback)
Generate Signs out copied Override signout
(copyback) back element
Transfer Signs in at target Override signout, Retain signout
Signout to
Archive No change Override signout
Restore Signs in at target Override signout
Signin Signs in Override signout, Signout to
Copy No change Override signout
Delete No change Override signout
Package :
A package is a set of ENDEVOR actions that may require approval before being executed. You create a package by building batch SCL streams containing the actions to be executed.
1 - Creating packages 5 - Committing packages
2 - Casting packages 6 - Package Backout
3 - Reviewing packages 7 - Package shipment
4 - Executing packages 8 - Package action/status summary
Creating a package involves:
* Identifying the elements to be included in the package.
* Building a file of actions to be performed against the elements.
* Identifying the package as standard or emergency.
* Specifying dates between which the package must be executed.
You can also create a package by reusing an existing package. After creation, a package has a status of in-edit. You can modify a package as long as it has a status of in-edit.
Casting a package :
Casting a package prepares the package for review and subsequent execution. When you cast a package, ENDEVOR:
* Determines whether approvers have been assigned to the inventory area(s) included in the package.
* Makes sure that any approvers have authority to perform the package actions against the package inventory areas.
* Checks the integrity of the package components.
* Makes sure that the package contains the most recent versions of all components.
A package cannot be edited once it is cast.
1 - Determining approvers 3 - Security checking
2 - Component validation 4 - Integrity checking
The first step in casting a package is to determine if approvers are associated with the inventory areas referenced in the package.
Action Inventory Area
Add/Update Target
Restore Target
Generate:
With COPYBACK Target
Without COPYBACK Source
Move Target
Delete Source
Signin Source
Transfer:
With DELETE Source and Target
Without DELETE Target
Archive:
With DELETE Source
If component validation is active, ENDEVOR examines the component list associated with each element specified in a Move action. ENDEVOR does not allow a package to be cast if any components
* Reside at the same stage as the source of the Move action and have not been included in the package. In this case, ENDEVOR appends commented Move SCL for the omitted input component.
* Are missing or have been modified (the time stamp in the component list footprint does not correspond to either the Generate or Move time stamp in the MCF) since the element was generated. ENDEVOR issues error messages about this condition.
When a cast fails for either of these reasons, review the package and do one of the following:
* Generate the element to pick up the latest version of a component.
* Cast the package without validation (if allowed).
* Cancel the package if a component has been deleted.
For more information, see Chapter 7 of the User's Guide.
The ENDEVOR C1DEFLTS Table contains a flag*PKGCSEC*that indicates whether action s should be checked at package cast time, to determine whether the person casting the package has the authority to perform all actions contained in that package.
If PKGCSEC=Y, ENDEVOR checks each action in the package. If the person is not authorized to perform all actions, he/she cannot cast the package. If PKGCSEC=N, ENDEVOR does not check action security.
After the package is cast, it contains either a copy of the footprint of the source element or a checksum value for source members from an external data set.
Before executing the package, ENDEVOR compares this footprint or checksum value with the values stored in the package. If any differences exist at that time, the package is not executed.
A package must be reviewed if one or more approver groups are associated with the inventory area(s) included in the package. Once a package is in the review phase, only designated approvers can access the package and review its contents.
Reviewing Packages :
To be approved, a package must:
* Receive approval from at least the required approvers.
* Receive approval from a quorum of approvers.
* Not be denied approval by any approvers
Example: The approver group PKGQA consists of three approvers. The
approver group was established with a quorum size of 2, with one approver required. This means that in addition to the required approver, one of the two remaining members of the approver group
must approve the package in order for it to be executed.
Executing Packages
The package can be either executed online or submitted in batch. The user performing the execution must be an approver and must have the authority to perform the actions contained in the pac kage.
The outputs of packages that have been executed can be backed out, backed in, or shipped to remote locations.
Committing Packages
Package processing provides you with the ability to backout and and subsequently backin, change packages. The backout/backin option is available only after you have executed a package. All package event information, as well as backout/backin data, is maintained with the package until you commit the package.
Committing a package removes any backout/backin data while retaining package event information. A package should be committed only when you are sure that you will no longer need to back it in.
Package Backout
Should you discover a problem once you have executed the package, or if the execution failed, you can back out the package. You can also reinstate backed out packages, using the back in option.
When you back out a package, you must use the BSTCOPY utility, not IEBCOPY. BSTCOPY is discussed in Chapter 5 of the Administrator's Guide.
Note: Package backout deals with ENDEVOR output libraries, not base and delta libraries. If reverse or image delta format is being used, the base library member will not be backed out.
Why backout does not affect source
Package backout is designed to restore load modules and other executables to their pre-package execution state. Backout does not restore the source to its previous image, because the "bad" source is the audit trail of the change. This audit trail should not be disrupted for any reason, because it allows you to view change history and changes only online, facilitating problem resolution.
ENDEVOR "knows" that the executables have been backed out even if the source isn't, by flagging the element in the MCF. ENDEVOR warns users who subsequently attempt to retrieve the backed out element that they are working with a backed out copy.
Note: If you want to restore the prior level of source, you can do so online using the Summary of Levels panel on the Retrieve action. The prior level of source, after retrieval, can then be added back into ENDEVOR, creating a new change level and preserving the audit trail
of the bad change.
Backout and package dependencies
As with package commitment, package backout and backin takes into
consideration dependencies between packages.
Assume you execute package PKG1, containing elements COPYA, COPYB,
COPYC, and COPYF. You then execute package PKG2, also containing
element COPYF, as well as COPYD and COPYE.
You then decide to back out PKG1. Because the execution of PKG2 has
affected element COPYF, which is in both packages, you must back out
PKG2 first. Otherwise, COPYF will not be returned to its original,
pre-execution state.
The package shipment utility uses data transmission programs to
distribute package outputs (source, object, listing, or load modules),
or package backout members from a host site to another site. It is
designed for users who develop software at a central site and want to
transmit source, object, or executable code either locally or to other
(remote) sites.
Package Shipment:
To use the package shipment utility:
* Packages must be created with BACKOUT ENABLED=Y.
* Packages must have been executed, but not committed
* One of these data transmission packages must be available
- XCOM 6.2 (CAI)
- Bulk Data Transfer (IBM) Version 2, or via NJE/NJI
- NetView File Transfer Program Version 1 (IBM)
- Network DataMover (Sterling Software)
* Remote destinations must be defined
Package Action/Status Summary
If You Do This This Status Changes To Next Action Is
Create package None In-edit Modify or cast
Modify package In-edit In-edit Cast
Cast package
Approval In-edit In-approval Review
No Approval In-edit Approved Execute
Unsuccessful In-edit In-edit Correct and re-Cast
Review package
Approved In-approval Approved Execute
Denied In-approval Denied Reset and correct
Execute package
During execution Approved In-execution
Successful In-execution Executed Backout, Backin, Ship
Unsuccessful In-execution Exec-failed correct and re-execute
Commit package Executed Committed
Backout package Executed Executed Backin, Ship
Backin package Executed Executed Backout, Ship
Ship package Executed Executed Backout, Backin, Commit
This document lists the new features and enhancements available in
Release 3.9 of Endevor for OS/390. Select 1 - 10 for information about
the following topics:
1 - Element Locking for Packages
2 - ESORT Command for Sorting ISPF Selection Lists
3 - Full-Image Delta Format
4 - GENERATE-IN-PLACE Quick-Edit Option
5 - Monocase Text Search Option
6 - Prompt for Package ID or Element Name if Omitted Option
7 - Endevor API capability to Perform Element Actions
8 - OS/390 SYSPLEX VSAM Record Level Sharing (RLS)
9 - Endevor for OS/390 Agent Technology
10 - Documentation enhancements
ENDEVOR is an integrated set of management tools used to automate, control, and monitor your applications development process. ENDEVOR runs under MVS, within the TSO/ISPF environment, and in batch.
Using ENDEVOR, you can:
· Track your changes against production, creating an online change history, enabling you to know what was changed, by whom, when, and why.
· Prevent conflicting changes to the same system component.
· Browse and manipulate all components relating to an application from a
· a single screen, saving you time.
· Automate executables creation.
· Ensure that the source, executable, and any other form
· (for example, listings) of an element correspond.
· Apply the same procedures (including automating compiles, impact
· Analyses, and standards checking functions) to any component type.
· Put change packages and approvals online, eliminating paperwork.
· View or retrieve prior levels of any element.
· Report on element definition, content, and change history.
· Enforce change control procedures.
Software Life Cycle :
ENDEVOR allows you to automate and control the movement of software through your software life cycle. Life cycles are site-specific. A representative life cycle might consist of five stages:
* DEVELOPMENT - Programs are developed.
* TEST - Programs are unit tested.
* QA - Applications are system tested.
* EMERGENCY - Fixes are applied to production code.
* PROD - Production applications reside.
The ENDEVOR administrator might decide to put the TEST, QA, EMERGENCY and PROD stages under the control of ENDEVOR.
Basic :
Normal change procedures include:
* Retrieving from production to a development library.
* Making changes.
* Adding/updating into the test stage.
* Moving to QA.
* Moving back into Production.
<------------- retrieve -----------------------------------
******** ******** ******** *******
* TEST * * QA * * EMER * * PROD *
* * * * * * * *
****** ****** ******** *******
$$$$$$$
$ dev $ - move --> -------- move -------------->
$ --add->
$$$$$$$$
Emergency : Emergency change procedures include:
* Retrieving from production.
* Making changes.
* Adding/updating into the emergency stage.
* Moving to Production.
---- move ------->
******** ******** ******* *******
* TEST * * QA * * EMER * * PROD *
* * * * * * * *
******** ******** ******* *******
add $$$$$$$ retrieve
$ dev $
<----- <----
$$$$$$$$
Inventory Structure :
The ENDEVOR inventory structure allows you to:
· Work with program modules without having to know where they are physically located, or how they are compiled.
· List all the program components that make up an application, regardless of type.
· Determine the location(s) of an element simply by entering the element name on a display screen.
· Act on a cross section of your program inventory.
· For example,
you can list all COBOL code in your shop, or promote an entire new release of the payroll application with a single command.
ENDEVOR can produce lists, for example, of:
* All the JCL in the shop
* All the software currently in QA
* All the manufacturing software currently being unit tested
There are many other ways to use this structure to your advantage.
Job Function
*---------------------------------------------------*
Action Dev QA/Test Turnover Audit Mgmnt Admin
*----------------------------------------------------------------
Add/Update x x x
Archive x
Copy x
Delete x x
Display x x x x x x
Generate x
List x x
Move x x x x
Print x x x x x x
Restore x
Retrieve x x x
Signin x x x
Transfer x x
*-------------*---------------------------------------------------*
ENDEVOR helps to manage the software life cycle by providing a
consistent and flexible logical structure for classifying
software inventory.
There are six components to this inventory structure: environments, stages, systems, subsystems, types, and elements.
Environments, stages, systems, subsystems, and types are set up by the ENDEVOR administrator. The actual inventory is made up of elements. Users act on elements.
1 - Environments 5 - Types
2 - Stages 6 - Elements
3 - Systems 7 - Using the inventory structure
4 - Subsystems 8 - The software life cycle
Environment :
Environment is the ENDEVOR term for the functional areas of an organization. For example, there might be separate development and production environments.
There is no limit to the number of environments that may be defined. Environments are defined in the C1DEFLTS table. Environment information can be viewed on the Site/Environment Information panel.
Stage :
Stage is the ENDEVOR term for a stage in the software life cycle. For example, a site may have stages TEST, QA, FIX, and PROD in its life cycle There must be two stages for each environment.
Stages have a name, representing their place in the life cycle (for example TEST) and an ID (1 or 2). Stages are referred to as Stage 1 (the first stage in an environment) and Stage 2 (the second stage in an environment).
Stages are defined in the C1DEFLTS table. Stage information is displayed on the Stage Information panel.
Stages can be linked together to establish unique promotion routes for program inventory within and between environments. These routes make up the map for a site.
1 - Stage information panel 2 - Mapping
Current env - Name of the environment for which stage information is shown.
Next env - The name of the next environment on the map.
Stage ID - ID of the first Stage in the next environment.
Stage 1 and 2 Information.
ID - Stage ID.
Name - Stage name.
Title - Stage description.
MCF data set name - Data set name of the Master Control File for the Stage.
Part of implementing ENDEVOR is to define the stages in the software life cycles at your site, then organize these stages into environments. See Chapter 1 of the Administrator's Guide.
Applications in each life cycle follow a unique route through the environment/stage locations that you have defined. You can set up as many routes as you need to accommodate different life cycles at your site. These routes make up the map for your site.
ENDEVOR uses these routes to automate the adding, displaying, retrieving, moving, and generating of inventory in a given life cycle.
1 - One route map - example 5 - The map and actions
2 - Two route map - example 6 - The map and lists
3 - Establishing routes 7 - The map and signout
4 - Mapping inventory locations 8 - Implementation checklist
1 - One route map - example
In its simplest form, a series of environment/stage locations can be
chained together to form a one-route map.
***************** ***************** *****************
* DEV * * QA * * PROD *
***************** ***************** *****************
* UNIT * INT * * QA * HOLD * * FIX * PROD *
* * * * * * * * *
* -----> -----------> -----> ----------------------> *
* * * * * * * * *
***************** ***************** *****************
2 - Two route map - example
Route R1 (DEV, QA, and PROD) for production applications.
Route R2 (QFIX, PROD) to handle emergency fixes to the applications.
***************** ***************** *****************
* DEV * * QA * * PROD *
***************** ***************** *****************
* UNIT * INT * * QA * HOLD * * FIX * PROD *
* * * * * * * * *
R1 * -----> -----------> -----> --------------------> , *
***************** ***************** ****************
********************
* QFIX *
********************
* TSTFIX * PREPROD *
R2 * -----> ------------
********************
System :
System is the ENDEVOR term for the applications at a site. For example, a site might have financial (system: FINANCE) and manufacturing (system: MFG) systems.
A system must be defined to each environment where it will be used. You work with systems using the System Definition panel. You can clone system definitions within or across environments using the
System Definition - Clone panel.
1 - System definition panel 2 - System definition – clone
Subsystem is the ENDEVOR term for a particular application within a system. For example, there might be purchase order (subsystem: PO) accounts payable (subsystem: AP) applications within system FINANCE.
* There must be at least one subsystem per system.
* A subsystem must be defined to each system in which it will be used.
For example, if you plan to have subsystem PO within system FINANCE, and define system FINANCE to environments TEST, QA, and PROD, you must also define subsystem PO to system FINANCE in each environment.
* A subsystem can have the same name as the system to which you
define it.
Subsystem :
You work with subsystems using the Subsystem Definition panel. Type is the ENDEVOR term for categories of source code. For example, you might create the following types: COBOL (for COBOL code);
COPYBOOK (for copybooks); JCL(for JCL streams). You must define a type to each stage in which you want to use it.
Processors :
ENDEVOR uses JCL streams called processors to automate creating executables. You can associate a group of processors with each type. Each processor group includes the processors needed for a particular type of source. For example, the processor group for type COBOL might include processors for VS COBOL and COBOL II.
You work with types on the Type Definition panel.
Naming Conventions :
Consider using generic type names, such as COBOL. You can then create as many processor groups as you need to handle the variations within each type. For instance, for type COBOL, you could create processor groups to tell ENDEVOR what type of COBOL must be processed.
A list of suggested naming standards for types follows. This list is not complete and is presented here as a guideline only.
ASEMBLER LINKCARD SORTCNTL FORTRAN
BASIC MACRO SPECS RPG
CLIST MARKV TABLES JCL
COBOL NETRON TELON REPORTS
COPYBOOK PLI TRNSFORM
EASYTRE VPROC UTILITY
Element is the term for PDS members, Panvalet or Librarian, or
sequential data sets that have been placed under control of ENDEVOR.
The element name defaults to the member name.
ENDEVOR classifies elements according to the inventory structure you
set up. Each element is described uniquely in terms of its:
* Location in the software life cycle. This is determined by the environment and stage where it resides.
* Inventory classification. This is determined by the system, subsystem, and type with which it is associated.
Working With Elements
You manipulate ENDEVOR inventory by executing commands called actions.
Some actions are available in both foreground and in batch, while
others are available only in batch.. Batch actions are also available
when you build packages.
* The ENDEVOR/MVS User's Guide explains how to execute actions in
foreground and submit batch action requests.
* The ENDEVOR/MVS SCL Reference Manual contains the syntax for
ENDEVOR's Software Control Language (SCL). SCL allows you to code
batch action requests.
Signout/signin :
Signout/signin can be enabled on a system by system basis.
Action Impact on element Options
Add/Update Signs out Override signout
Move Signs in at target Retain signout, Signout to
Retrieve Signs out Override signout, No Signout
Generate No change Override signout
(no copyback)
Generate Signs out copied Override signout
(copyback) back element
Transfer Signs in at target Override signout, Retain signout
Signout to
Archive No change Override signout
Restore Signs in at target Override signout
Signin Signs in Override signout, Signout to
Copy No change Override signout
Delete No change Override signout
Package :
A package is a set of ENDEVOR actions that may require approval before being executed. You create a package by building batch SCL streams containing the actions to be executed.
1 - Creating packages 5 - Committing packages
2 - Casting packages 6 - Package Backout
3 - Reviewing packages 7 - Package shipment
4 - Executing packages 8 - Package action/status summary
Creating a package involves:
* Identifying the elements to be included in the package.
* Building a file of actions to be performed against the elements.
* Identifying the package as standard or emergency.
* Specifying dates between which the package must be executed.
You can also create a package by reusing an existing package. After creation, a package has a status of in-edit. You can modify a package as long as it has a status of in-edit.
Casting a package :
Casting a package prepares the package for review and subsequent execution. When you cast a package, ENDEVOR:
* Determines whether approvers have been assigned to the inventory area(s) included in the package.
* Makes sure that any approvers have authority to perform the package actions against the package inventory areas.
* Checks the integrity of the package components.
* Makes sure that the package contains the most recent versions of all components.
A package cannot be edited once it is cast.
1 - Determining approvers 3 - Security checking
2 - Component validation 4 - Integrity checking
The first step in casting a package is to determine if approvers are associated with the inventory areas referenced in the package.
Action Inventory Area
Add/Update Target
Restore Target
Generate:
With COPYBACK Target
Without COPYBACK Source
Move Target
Delete Source
Signin Source
Transfer:
With DELETE Source and Target
Without DELETE Target
Archive:
With DELETE Source
If component validation is active, ENDEVOR examines the component list associated with each element specified in a Move action. ENDEVOR does not allow a package to be cast if any components
* Reside at the same stage as the source of the Move action and have not been included in the package. In this case, ENDEVOR appends commented Move SCL for the omitted input component.
* Are missing or have been modified (the time stamp in the component list footprint does not correspond to either the Generate or Move time stamp in the MCF) since the element was generated. ENDEVOR issues error messages about this condition.
When a cast fails for either of these reasons, review the package and do one of the following:
* Generate the element to pick up the latest version of a component.
* Cast the package without validation (if allowed).
* Cancel the package if a component has been deleted.
For more information, see Chapter 7 of the User's Guide.
The ENDEVOR C1DEFLTS Table contains a flag*PKGCSEC*that indicates whether action s should be checked at package cast time, to determine whether the person casting the package has the authority to perform all actions contained in that package.
If PKGCSEC=Y, ENDEVOR checks each action in the package. If the person is not authorized to perform all actions, he/she cannot cast the package. If PKGCSEC=N, ENDEVOR does not check action security.
After the package is cast, it contains either a copy of the footprint of the source element or a checksum value for source members from an external data set.
Before executing the package, ENDEVOR compares this footprint or checksum value with the values stored in the package. If any differences exist at that time, the package is not executed.
A package must be reviewed if one or more approver groups are associated with the inventory area(s) included in the package. Once a package is in the review phase, only designated approvers can access the package and review its contents.
Reviewing Packages :
To be approved, a package must:
* Receive approval from at least the required approvers.
* Receive approval from a quorum of approvers.
* Not be denied approval by any approvers
Example: The approver group PKGQA consists of three approvers. The
approver group was established with a quorum size of 2, with one approver required. This means that in addition to the required approver, one of the two remaining members of the approver group
must approve the package in order for it to be executed.
Executing Packages
The package can be either executed online or submitted in batch. The user performing the execution must be an approver and must have the authority to perform the actions contained in the pac kage.
The outputs of packages that have been executed can be backed out, backed in, or shipped to remote locations.
Committing Packages
Package processing provides you with the ability to backout and and subsequently backin, change packages. The backout/backin option is available only after you have executed a package. All package event information, as well as backout/backin data, is maintained with the package until you commit the package.
Committing a package removes any backout/backin data while retaining package event information. A package should be committed only when you are sure that you will no longer need to back it in.
Package Backout
Should you discover a problem once you have executed the package, or if the execution failed, you can back out the package. You can also reinstate backed out packages, using the back in option.
When you back out a package, you must use the BSTCOPY utility, not IEBCOPY. BSTCOPY is discussed in Chapter 5 of the Administrator's Guide.
Note: Package backout deals with ENDEVOR output libraries, not base and delta libraries. If reverse or image delta format is being used, the base library member will not be backed out.
Why backout does not affect source
Package backout is designed to restore load modules and other executables to their pre-package execution state. Backout does not restore the source to its previous image, because the "bad" source is the audit trail of the change. This audit trail should not be disrupted for any reason, because it allows you to view change history and changes only online, facilitating problem resolution.
ENDEVOR "knows" that the executables have been backed out even if the source isn't, by flagging the element in the MCF. ENDEVOR warns users who subsequently attempt to retrieve the backed out element that they are working with a backed out copy.
Note: If you want to restore the prior level of source, you can do so online using the Summary of Levels panel on the Retrieve action. The prior level of source, after retrieval, can then be added back into ENDEVOR, creating a new change level and preserving the audit trail
of the bad change.
Backout and package dependencies
As with package commitment, package backout and backin takes into
consideration dependencies between packages.
Assume you execute package PKG1, containing elements COPYA, COPYB,
COPYC, and COPYF. You then execute package PKG2, also containing
element COPYF, as well as COPYD and COPYE.
You then decide to back out PKG1. Because the execution of PKG2 has
affected element COPYF, which is in both packages, you must back out
PKG2 first. Otherwise, COPYF will not be returned to its original,
pre-execution state.
The package shipment utility uses data transmission programs to
distribute package outputs (source, object, listing, or load modules),
or package backout members from a host site to another site. It is
designed for users who develop software at a central site and want to
transmit source, object, or executable code either locally or to other
(remote) sites.
Package Shipment:
To use the package shipment utility:
* Packages must be created with BACKOUT ENABLED=Y.
* Packages must have been executed, but not committed
* One of these data transmission packages must be available
- XCOM 6.2 (CAI)
- Bulk Data Transfer (IBM) Version 2, or via NJE/NJI
- NetView File Transfer Program Version 1 (IBM)
- Network DataMover (Sterling Software)
* Remote destinations must be defined
Package Action/Status Summary
If You Do This This Status Changes To Next Action Is
Create package None In-edit Modify or cast
Modify package In-edit In-edit Cast
Cast package
Approval In-edit In-approval Review
No Approval In-edit Approved Execute
Unsuccessful In-edit In-edit Correct and re-Cast
Review package
Approved In-approval Approved Execute
Denied In-approval Denied Reset and correct
Execute package
During execution Approved In-execution
Successful In-execution Executed Backout, Backin, Ship
Unsuccessful In-execution Exec-failed correct and re-execute
Commit package Executed Committed
Backout package Executed Executed Backin, Ship
Backin package Executed Executed Backout, Ship
Ship package Executed Executed Backout, Backin, Commit
This document lists the new features and enhancements available in
Release 3.9 of Endevor for OS/390. Select 1 - 10 for information about
the following topics:
1 - Element Locking for Packages
2 - ESORT Command for Sorting ISPF Selection Lists
3 - Full-Image Delta Format
4 - GENERATE-IN-PLACE Quick-Edit Option
5 - Monocase Text Search Option
6 - Prompt for Package ID or Element Name if Omitted Option
7 - Endevor API capability to Perform Element Actions
8 - OS/390 SYSPLEX VSAM Record Level Sharing (RLS)
9 - Endevor for OS/390 Agent Technology
10 - Documentation enhancements
Thursday, September 18, 2008
Which came first, the chicken or the egg?
Which came first, the chicken or the egg?
This question appears regularly in the question file, so let's take a shot at it.
In nature, living things evolve through changes in their DNA. In an animal like a chicken, DNA from a male sperm cell and a female ovum meet and combine to form a zygote -- the first cell of a new baby chicken. This first cell divides innumerable times to form all of the cells of the complete animal. In any animal, every cell contains exactly the same DNA, and that DNA comes from the zygote.
Chickens evolved from non-chickens through small changes caused by the mixing of male and female DNA or by mutations to the DNA that produced the zygote. These changes and mutations only have an effect at the point where a new zygote is created. That is, two non-chickens mated and the DNA in their new zygote contained the mutation(s) that produced the first true chicken. That one zygote cell divided to produce the first true chicken.
Prior to that first true chicken zygote, there were only non-chickens. The zygote cell is the only place where DNA mutations could produce a new animal, and the zygote cell is housed in the chicken's egg. So, the egg must have come first.
This question appears regularly in the question file, so let's take a shot at it.
In nature, living things evolve through changes in their DNA. In an animal like a chicken, DNA from a male sperm cell and a female ovum meet and combine to form a zygote -- the first cell of a new baby chicken. This first cell divides innumerable times to form all of the cells of the complete animal. In any animal, every cell contains exactly the same DNA, and that DNA comes from the zygote.
Chickens evolved from non-chickens through small changes caused by the mixing of male and female DNA or by mutations to the DNA that produced the zygote. These changes and mutations only have an effect at the point where a new zygote is created. That is, two non-chickens mated and the DNA in their new zygote contained the mutation(s) that produced the first true chicken. That one zygote cell divided to produce the first true chicken.
Prior to that first true chicken zygote, there were only non-chickens. The zygote cell is the only place where DNA mutations could produce a new animal, and the zygote cell is housed in the chicken's egg. So, the egg must have come first.
Friday, April 11, 2008
To resolve Soc7
To resolve Soc7
**************
A)
**************
A)
First recompile your pgm w/the LIST,NOOFF options. I find the simplest way to do
this is to preceed your pgm code with:
CBL LIST,NOOFF in col 8
When you re-run your pgm and it abends w/an 0C7 look at your sysmsg for the offset. You'll see a hex number like 06B4. This is the relative location in your pgm that contains the assembler instruction that caused the 0C7.
Now go to your compile listing. Now enter "f mvc" in the cmd line of your sdsf session. This brings you to the "list" part of the COBOL listing. Now enter "f 06B4".. This brings you to the assembler instuction that caused the 0C7. If you look to the right on that print
line you'll see data names from your pgm. One of them contains the data that caused the 0C7.
If you scroll up from there you'll see a cobol verb (e.g. ADD, COMPUTE, MOVE, etc.). That's the cobol verb that caused the 0C7. To the left of the verb you'll see the line# of the stmt. Use that to find the the actual stmt in your listing.
You can find the field in the dump, but that'll take too long to explain, even if I can remember it all.
The next best thing id to DISPLAY the field(s) just before you execute the stmt causing the 0C7.
P.S. If failing pgm is statically called, determining the the offset into your pgm is a bit more involved.
In this case the overall offset is displayed in sysmsg, not the offset into your pgm. For example, if the calling module's length is 500 bytes and the 0C7 occured 200 bytes into your pgm, the offset shown in sysmsg will be 700 not 200.
To determine the offset into your pgm subtract the size of ALL load modules linkedited into the loadset before yours. Use the linkedit listing to determine those values.B)
1. Get the offet of the abend from sysouత e.g lets say the offset is 0005DA
2. Go the compile listing and locate the offset. This will be present in the assembly listing of the program.
000067 MOVE
0005D6 4820 8018 LH 2,24(0,8) WS-COMP
0005DA 4E20 D100 CVD 2,256(0,13) TS2=0
0005DE F332 8020 D105 UNPK 32(4,8),261(3,13) WS-DEST
0005E4 96F0 8023 OI 35(8),X'F0' WS-DEST+3
000068 DISPLAY
0005E8 5820 905C L 2,92(0,9) TGTFIXD+92
0005EC 58F0 202C L 15,44(0,2) V(IGZCDSP )
0005F0 4110 A1F6 LA 1,502(0,10) PGMLIT AT +490
0005F4 05EF BALR 14,15
000069 DISPLAY
0005F6 58F0 202C L 15,44(0,2) V(IGZCDSP )
0005FA 4110 A1E0 LA 1,480(0,10) PGMLIT AT +468
0005FE 05EF BALR 14,15
3. Get the statement number from the listing, here the offet 0005DA is the expansion for the statrement number 000067.4. Using the statement number obtain the statement.
which would be something like this:
000067 MOVE WS-COMP TO WS-DEST.
Now this is the problematic statement.
Look in to this to resolve the abend.
SQLCODE = -904 ERROR:
UNSUCCESSFUL EXECUTION CAUSED BY AN
UNAVAILABLE RESOURCE. REASON RESOURCE UNAVAILABLE, TYPE OF
RESOURCE , AND RESOURCE NAME
Problem conclusion
Changed code to restore the resource unavailable reason code
after a timeout on a database lock so that the reason code would
be properly interpreted for the error message. Now, instead of
a -904 SQLCODE the user will receive a -911 or -913 SQLCODE for
the timeout.
Friday, April 4, 2008
DB2 Cursors
DB2 CURSORS
Using CURSORs in the COBOL programs.
In the part 1, we have seen how to access data using singleton select statement. using singleton select we can only select one record from the table. When there is a situation, like.. program needs to get all data in EMPLOYEE table and print all
employee names and their respective salaries. With singleton select statement (refer part1) it is not possible.
Using CURSORs we can get all records in the table.
Let us see step by step how we can do this in a cobol program.
TASK : Get all records from EMPLOYEE table and display EMPNAME and SALARY in the spool
STEP 1. DECLARE cursor in WORKING-STORAGE SECTION.
EXEC SQL
DECLARE EMPCUR CURSOR FOR
SELECT EMPNAME, SALARY
FROM EMPLOYEE
END-EXEC.
STEP 2. Open the cursor in PROCEDURE division.
EXEC SQL
OPEN EMPCUR
END-EXEC.
At this time, sql specified in the step1 executes and cursor is ready with data.
STEP 3. Get data from cursor record by record.
Following code should be in a loop till we get SQLCODE = 100. Let us assume there are 5 records in EMPLOYEE table, every time when you execute this code, we will get a record. 6th time when we execute the code we will get SQLCODE = 100, we can end the loop.
fetching/retrieving data from cursor.
EXEC SQL
FETCH EMPCUR
INTO :HV-EMPNAME,
:HV-SALARY
END-EXEC.
EVALUATE SQLCODE
WHEN 0
DISPLAY ' -----------------'
DISPLAY ' Employee name ' HV-EMPNAME
DISPLAY ' Employee salary ' HV-SALARY
DISPLAY ' -----------------'
WHEN 100
MOVE 'Y' TO END-OF-LOOP
WHEN OTHER
DISPLAY ' ERROR IN RETRIEVING THE DATA FROM EMPLOYEE TABLE'
DISPLAY 'SQLCODE : ' SQLCODE
END-EVALUATE.
TIP : SQLCODE = 100 is end of the cursor, in case of cursors.
SQLCODE = 100 is record not found in case of singleton select
STEP 4. Once retrieving records from table completed, we need to
close the cursor using following command.
EXEC SQL
CLOSE CURSOR
END-EXEC.
Other info about cursors....
Your program can have several cursors. Each cursor requires its own:
DECLARE CURSOR statement to define the cursor
OPEN and CLOSE statements to open and close the cursor
FETCH tatement to retrieve rows from the cursor's result table.
You can use cursors to fetch, update, or delete a row of a table,
but you cannot use them to insert a row into a table.
Dear Mr. Srinivas,
ReplyDeleteI found a mistake from the beginning. "Date – 10 bytes, Time - 8 bytes and Timestamp – 26 bytes". Its not correct. Its only 4 bytes, 3 bytes and 10 bytes respectively. Hope you realize the mistake.