DB2 Architecture -What is Table Space, storage groups,OBID,PSID,index and Index spaces in DB2

When DB2 gets installed on Z/OS there are several other components that runs in parallel . Like all other Z/OS components, DB2 also runs as a formal subsytem and in different address spaces.Let's see the list of system objects which comprises the core DB2 architecture in z/os.

All System objects that DB2 works with in Z/Os architecture.

1. So,What is a DB2 DIRECTORY ?:
This is one of  the DB2 system objects which is  being  used during normal operation (like to gather information ,etc ) and are totally internal to DB2.We cannot access the Directory tables. There are  system databases like DSNDB01, DSNDB06, DSNDB07 on which all these system objects (as shown above)resides on.
DB2 Directory resides in DSNDB01 database and this database contains 5 main Table spaces as below:
SCT02
SPT01
DBD01
SYSUTILX
SYSLGRNGX .
SCT02: contains the skeleton cursor table for plans.When we do a DB2 bind for the SQL statements onto a PLAN, a db2 structure is in SCT02 structure.FREE PLAN removes the removes the table structure.
SPT01: Similarly when we execute a package Bind, db2 causes a structure to be created in SPT01 Table space..When we issue a FREE PACKAGE command, the SKT is removed from the SPT01.
DBD01 (DBD):Contains information about all the DB2 objects defined in the database.DB2 creates and updates DBDs whenever their corresponding databases are created or updated
SYSUTILX (Active Utilities): We run online DB2 utilites as part of normal day work. As each step progresses, its status gets updated and stored in
When the utility finishes normally or is terminated, all information about that utility is purged from SYSUTILX.
SYSLGRNGX (Logs):Tracks the opening and closing of table spaces, indexes, or partitions. This tracking information is associated with RBA.When a DB2 recovery is requested,the required logs are located and used for quick recovery.

Just For information:
DB2 Catalog resides in the DSNDB06 database.
DB2 Directory resides in the DSNDB01 database.
Work files reside in the DSNDB07 database.

2. What is DB2 CATALOG:
When we hear the term catalog,the thing that comes to our mind is 'information being stored/registered' in the z/os system. Exactly the same fact goes for DB2 catalog.
DB2 catalog is nothing but set of  system tables like SYSIBM.SYSTABLES, SYSIBM.SYSTABAUTH,SYSIBM.SYSTABAUTH....and lot more of other tables whose main function is to keep information of the DB2 objects that we create like Tables,columns,indexes, primary key,foreign keysViews,Indexes.Lets check an example and have an idea about :
How the Catalog and system tables used in DB2?
Whenever a DB2 object is created, dropped or altered by any DDL statement or whenever control over the object is changed by any DCL statement of sql, an entry is made in the appropriate catalog table.
We can query the CATALOG TABLES to get information, but can not modify these CATALOG tables.
Example: When we trigger a SQL query, db2 checks in catalog tables if the table exists or not. Then it will look for SYSCOLUMNS to check if the column exists or not. Further more it checks the SYSTABAUTH to check the authority of the person firing the SQL statement.
Similarly when we create a table into one table space, every details about the table, ie the Table name, Table space name, name of the database it is residing on, name of the owner is captured and a new row gets inserted into the corresponding tables.

3. What is a DB2 LOG:
As the name suggests, log is something which keeps track of all the changes done to the database.So every transaction (insert, update, delete) performed on the data is logged in the log files.These information comes useful in crash recoveries. A log file has large number of log records in it.A log sequence number identifies the log record in the log file. DB2 writes each log record on to a file and is known as Active log. Once the space is full, these logs are migrated to tape and becomes Archive Log ( so that they can be referred later when needed).
4. What is a Boot strap data set(BSDS):
Contains information which are critical to  DB2 , such as name of the logs.DB2 uses these information in doing system restarts.
5. What is a Buffer pool: 
We all know about the concept of cache memory.A buffer pool is nothing but cached database page in memory.We can view it as a a virtual storage space for table space or indexes,but this is temporary.When DB2 access the rows of a table,that data page containing that requested  row is written to the buffer pool.So next time when the data page is accessed, DB2 eliminates the need to go back to the disk to fetch the row, there by reducing the physical input/output access to the disk.
" For information:::The maximum size of a buffer pool is 1 TB  "

6. What is a Resource Limit Facility:
The name is self explanatory."Resource  Limit  Facility" which means DB2 has some kind of governing system which  helps to limit/control the use of DB2 resource. RLF limits the resource usage like bind operations in certain times of the day,to avoid contention; Set warning or error threshold when processing limit might be exceeded for particular dynamic SQL statement and inform the user
7. Work Database:
Simply these are the work files storage, used by DB2 to process SQL statements.( like we see SORTWK1...in dfsort /syncsort). The database for the workfiles is DSNDB07.


Now lets see brief about user defined Table spaces and other DB2 objects!!!
---------------------------------------------------------------------------------------------------
The data structure in db2 consists of Storage group, database, tablespace, tables, index, and index spaces. Among these only the storage group , Table space and index have the physical existence. Rest all are logical concept. Storage group is a physical DASD (direct Access storage device). A table space or an index is associated with one or more of these physical datasets.
Table and database have no actual physical representation.We can imagine a database to be big unit which contain tablespace and indexes. The tablespace in turn contains one or multiple table.
Since a Tablespace has physical existence ,we need to  specify the PRIQTY  and SECQTY while creating the table space because they actually allocate primary and secondary space in the DASD devices.

What are Object Identifiers(OBID),PSID,DBID in DB2?
When we create a object in DB2 database, those objects are assigned with a identifier and stored in DB2 catalog.These object identifiers are called OBID. Going further a table space or index space are assigned with Page Set Id know as PSID. A database is assigned with Database identifier known as DBID.
DBD thus stores information about database whereas OBD is used to store information about the DB2 objets like TS,Table,Index etc....


What is a page in Db2: Db2 internally continues read/write operation in terms of equal size units known as pages.
The size can vary from 4k,8k,16l,32k.  The default page size is 4K.

How many types of table spaces are there in DB2?
Fundamentally the table space is the storage  unit for recovery and re-organization purposes; i.e. it is the unit that can be recovered  via the recover utility . If  the table space is very large , however , recovery and re-organization could take a very long time.
There can be Simple, Segmented, partioned, universal, XML, Large Object Table spaces in DB2.

Simple Table Space are obsolete now a day. It can contain rows  of one or more tables. When many tables are defined on a simple table space, one single page contains all the rows from the tables.A simple  TS can be created by issuing the CREATE TABLESPACE statement without specifying either the SEGSIZE or NUMPARTS clause.
One reason for having more than one is that stored records for different stored tables can be clustered together in such a way as to improve access times to logically related data.In  particular , certain join queries would then be handled more efficiently since the I/O operations would be reduced
Disadvantage of a simple table space is that it unnecessarily locks  rows of other tables which are there in that page even when operation is being performed on some other table.
Command to Create a Tablespace
CREATE DATABASE "DBTEST1"
   STOGROUP   STTEST1
   BUFFERPOOL BT0;

COMMIT;

CREATE TABLESPACE "REOT001" IN "DBTEST1"
   USING  STOGROUP   STTEST1
          PRIQTY     144000
          SECQTY     144000
          ERASE      NO
          BUFFERPOOL BP0
          LOCKSIZE   TABLESPACE
          CLOSE      NO
COMMIT;


Segmented Table space: Preferred way to build a table space.A table space that is segmented is useful for storing more than one table, especially relatively small tables. The pages hold segments, and each segment holds records from only one table. Segmented table spaces hold a maximum of 64 GB of data and can contain one or more VSAM data sets. The Basic Difference between simple table space and segmented Table spaces is ,segmented table spaces do not support any kind of cross table clustering- that is they do not allow records for different stored tables to be interleaved on a single page. Instead , they keep the tables  physically separated.
A segmented table space can be built using the  SEGSIZE clause. The value indicates the pagesize
in each segment.

CREATE TABLESPACE "REOT001" IN "DBTEST1"
   USING  STOGROUP   STTEST1
          PRIQTY     144000
          SECQTY     144000
          ERASE      NO
          BUFFERPOOL BP0
          LOCKSIZE   TABLESPACE
          CLOSE      NO
      SEGSIZE    24;

COMMIT;
We can keep some free pages in segment using the FREEPAGE clause with a value less than that of
SEGSIZE.Defining small tables in a table space with a large segment size could result in wasted DASD space.
If we specify the FREEPAGE to be greater than SEGSIZE, the FREEPAGE will be calculated as
SEGSIZE -1.

Partitioned tablespace: A table space that is partitioned stores a single table.
Partitioned table spaces are intended for stored tables that are sufficiently large. A partitioned table space thus contains exactly one stored table, partitioned in accordance with value ranges of a partitioning columns or column combination. Individual partitions of a partitioned table space are independent of each other in the sense that they can be independently recovered or reorganized.
They can also be associated with different storage groups i.e. it is possible to store different partitions on different devices and thereby spread the table space I/O load.

CREATE LARGE TABLESPACE test01TS  IN TestDB
  USING STOGROUP TestEX4
  PRIQTY 504000 SECQTY 504000
   FREEPAGE 0 PCTFREE 0
  GBPCACHE CHANGED
  TRACKMOD YES
  NUMPARTS  40
  BUFFERPOOL BPX
  LOCKSIZE ANY
  CCSID    EBCDIC
  MAXROWS 255

use of keyword  NUMPARTS indicates the partitioned tablespace.

INDEX SPACE : index space is to an index what a table space is to a stored table. However, since the correspondence between indexes  and index spaces is always one to one, there are no data definition statements for index spaces; instead, the necessary index space parameters
are specified on the corresponding index definition statements. Thus for e.g. there is no create
index space; instead the index space is created automatically when the corresponding index is created. Like Table spaces ,index spaces can be reorganized and recovered independently.

DB2 In Built Functions - Column function and Scalar Function

Column functions
1.Column functions can be executed in SELECT Statement
2. Each Column function returns only one value for the set of rows
3.If we apply a column function to one column in a select Statement,we must apply column functions to all the columns specified in the same select statement, unless the GROUP BY clause is used.
4. Result of the column function will have the same data type like the column on which it was applied.
5. Column function will never return a SQLCODE +100. It returns NULL value if the where clause does not fetches any data. 
But COUNT and COUNT_BIG function returns value, not NULL value. 
So lets look into the below query which theoretically is doing the same thing, but results will be different.
Thus AVG(SALARY)   and   SUM(SALARY)/ COUNT(*)  will be different.
The reason being, count function takes all the rows into account, whereas SUM function ignores the rows that has null values.

The COUNT function and COUNT_BIG function: The COUNT_BIG function is same as COUNT function except that it returns a decimal value.
Ex: select COUNT(*) from EMP_TBL   or Select COUNT_BIG(*) from EMP_TBL

The MAX and MIN functions: MAX will give the largest value in the expression and MIN will give the smallest one.The Result is of the same data type on which it is applied.
It can not be applied on data types like CLOB, BLOB, DBCLOB

STDDEV function returns the standard deviation of  a set of number
ex:  select stddev(salary)  from emp where dept='cse'

SUM function gives the total of values specified in the expression.
ex:  select SUM(SALARY+BONUS) FROM EMP  where month='Oct'

Scalar  functions :
Scalar functions operate on single value as compared to column function.
Below are the Queries along with their output for few scalar functions.

SELECT LEFT('THIS IS VALUE',2) FROM SYSIBM.SYSDUMMY1;
TH
                                                           
SELECT (RAND()*100) FROM SYSIBM.SYSDUMMY1;
+0.3388811137242620E+02

SELECT LOCATE('S','INDIA IS GREAT') FROM SYSIBM.SYSDUMMY1;
 8  
Returns the position of first occurrence of 'S' in the string

SELECT NEXT_DAY(CURRENT DATE,'FRIDAY') FROM SYSIBM.SYSDUMMY1;
2013-11-15-00.00.00.000000

SELECT REPLACE('BATATA','TA','NA')  FROM SYSIBM.SYSDUMMY1;
BANANA

SELECT DATE('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013-11-15

SELECT WEEK('2013-12-25-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
52
SELECT YEAR('2013-12-25-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013

SELECT DAY('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
15 

SELECT LAST_DAY('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
2013-11-30


SELECT MONTH('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
11

SELECT TIME('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
00.00.00

SELECT HOUR('2013-11-15-23.22.21.056788') FROM SYSIBM.SYSDUMMY1;
23

SELECT SECOND('2013-11-11-00.00.13.0000') FROM  SYSIBM.SYSDUMMY1
13

SELECT MICROSECOND('2013-11-15-00.00.00.889924') FROM SYSIBM.SYSDUMMY1;
889924 

SELECT JULIAN_DAY('2013-11-15') FROM SYSIBM.SYSDUMMY1;
2456612

SELECT DAYOFWEEK('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
6 
Represents the day of the week.Like 1 for Sunday,2 Monday

SELECT DAYOFYEAR('2013-11-15-00.00.00.000000') FROM SYSIBM.SYSDUMMY1;
319 

SELECT GRAPHIC('319') FROM SYSIBM.SYSDUMMY1;
.â3â1â9.

SELECT LCASE('JIM') FROM SYSIBM.SYSDUMMY1;
jim

SELECT LTRIM('     JIM') FROM SYSIBM.SYSDUMMY1;
JIM
Trims the space from the string.

SELECT HEX('RI5') FROM SYSIBM.SYSDUMMY1;
D9C9F5

SELECT INT(123.90) FROM SYSIBM.SYSDUMMY1;
123 

SELECT CHAR(CURRENT DATE,USA) FROM SYSIBM.SYSDUMMY1
11/11/2013
Displays  the current date in the USA date format 

SELECT CONCAT('CON','CAT') FROM SYSIBM.SYSDUMMY1
CONCAT

SELECT INSERT('JIM  PRAT', 5, 0 ,'NORM') FROM SYSIBM.SYSDUMMY1 
JIM NORM PRAT 
Accepts 4 arguments. Returns the string which contains the 4th argument inserted into the 1st argument at the position specified by the 2rd argument. 3rd arg specifies the number of bytes to delete

SELECT QUARTER('2013-11-11-00.00.00.0000') FROM  SYSIBM.SYSDUMMY1
4
Vaue of 1=1s quarter,2=2nd quarter,3=3rd quarter,4=4th quarter

Lock Escalation and Lock Promotion in DB2

Lock escalation:
 Locks are data structures that indicate which part of the system (row, page, table, tablespace, database, ...) is protected in some way.  DB2 remembers this information in memory. Blocking the whole database all the time when an application accesses a small part of it doesn't make much sense. So if you have an application that deals with a lot of data, it potentially has to have many locks. Those locks accumulate and need space in memory.

So when the list is almost full DB2 does a "lock escalation". That means, many row-level locks held by one transaction on a table are escalated to a single table-level lock, for example. Instead of having 1000s or millions of locks (per row), it is better to have lock on that table , there by relieving the pressure on the lock list.

When lock Escalation Happens:
As the number of locks held by any one application reaches near to MAXLOCKS percentage on the create tablespace statement.(If we remeber we use this paramter on table space DDL statement). Database manager will trigger lock escalation and will continue until the percentage of the lock list held is below the value of MAXLOCKS. Lock escalation also occurs if the LOCKLIST runs out of space.  

How DB2 Handles this scenario:
The database manager determines which locks to escalate by looking through the lock list for the application and finding the table with the most row locks.
Row locks will be replacing with a single table lock. It will keep repeating till MAXLOCKS value is no longer exceeded.

Summary  :
Lock escalation can occur for objects defined with LOCKSIZE ANY, PAGE, or ROW. Value ANY just gives DB2 the choice of what lock to take initially.
If we use LOCKSIZE ANY, LOCKMAX SYSTEM , it means means it uses the number set by the NUMLKTS DSNZPARM.We can turn off lock escalation by setting LOCKMAX to zero.If we choose this approach, be sure the applications that access the objects are committing frequently, and adjust DSNZPARM NUMLKTS to permit more locks to be taken. Otherwise, we risk hitting negative SQL codes when you reach the maximum number of locks.
Lock escalation is thus a way to protect from taking excessive system resources, so if we turn it off, we need to control it.
If we remember the locking modes and the lock sizes, this is how it works:
Page lock and Row lock escalate to Table level and then to Tables space level for Segmented tables space.
But for partioned tables spaces, the page/row lock escalates straight to the Tables space level.
A page and Row lock occurs at the same level.They can not be escalated to each other, ie ROW<-->PAGE

Lock promotion:
Lock promotion is the action of exchanging one lock on a resource for a more restrictive lock on the same resource, held by the same application process. We can say, it is the process of changing the lock mode or lock size to a more restrictive level. See the below example .


Example:An application reads data, which requires an IS lock on a table space. Based on further calculation, the application updates the same data, which requires an IX lock on the table space. The application is said to promote the table space lock from mode IS to mode IX.(ie Intent share to intent Exclusive)
Note:  LOCKSIZE ANY will cause DB2 to do page level locking
Want to refresh about the lock modes and intent lock? check out Locking  again.

Inportant information about DB2 LOCK
1. DSNZPARM starts up the parameters for DB2.
2.The LOCKMAX parameter of the create  or ALTER TABLESPACE can be seen in the
MAXROWS column of the SYSIBM.SYSTABLESPACE.
3. If a single user accumulates more page locks than allowed by DSNZPARM, we get a SQLCODE of -904.(resource unavailable)
4.All the page locks held for data in segmented table space are escalated to table locks.
 All the page locks held for data in partitioned table space are escalated to table space locks.

5. Why do we get SQLCODE -904 ?
NUMLKUS parameter in DSNZPARM defines the threshold for the total number of page locks across all table space that can be held concurrently by a single DB2 application.When any other application tries to attempt lock that cause the application to exceed the NUMLKUS threshold,the application receives SQLCODE -904.

DB2 locking - Locks and lock modes

 Read about DB2 locking here 
We know about the 4 isolation levels in DB2, namely RR,RS,CS,UR. One thing which all these isolation level have in common is Locking. Locks in Databases serves the same purpose as they do in our house or cars.They Decide who can or can not access a resource like Table, Table Space,Rows etc. The DB2 Database manager imposes locks to prohibit "owning" transaction from accessing uncommitted data written by other application, and also to prevent other transactions to make any change to the currently owned rows.
Data Base manager locks have these 4 basic attributes:
1. SIZE -  It indicates the physical size of the portion of data resource that is being locked.
Possible sizes are : Table space, Table, Partition, pages.
2. MODE -  Type of access allowed for lock owner as well as the type of access permitted for concurrent users of the locked object
3. OBJECT -  Resources being locked. The only type of explicitly lockable objects are tables. The database manager also imposes locks on other type of resources such as Rows,tables, and Table spaces.
4. DURATION - The length of time a lock is held. LOCK DURATIONS are affected by Isolation level.
ex:  LOCK acquired by for RR(repeatable Read) transaction is likely to be more than lock acquired by CS transaction.
 
1.SIZE:The DB2 resources on which lock can be places are as below in ascending order of their sizes.
From Highest (Most data locked)  to lowest( least data locked),the most common lock sizes are
A. TS
B. TABLE.
C.PAGE
D.ROW
Point to Note: Locks can be taken at any level in the locking hierarchy without taking a lock at the lower level. However, locks cannot be taken at the lower levels without a compatible higher-level lock also being taken. For example, DB2 can take a table space lock without taking any other lock, but DB2 cannot take a page lock without securing TS lock.
One common batch abend involving DB2 locking is  like below:
DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = Tables-space-name
DSNUGASU - IRLM LOCK REQUEST FAILED,
IRLM RETURN CODE = X'00000008'
IRLM REASON CODE = X'2000'

This happens because one job was trying to acquire a DB2 resource, but that is being locked by other job. IRLM sets a timeout period, after that it terminates the execution and job fails with above mentioned error-code. DBA can provide information about the resource being used and the offending job what is causing the problem.Once that job is complete, we can restart our job from abend step. 

2.Lock Modes:
Lock Modes  at  Table level are as follows:
IS : INTENT SHARE:  The Lock owner can read any data in the table if an S lock can be obtained on the target rows or page.
IX: INTENT EXCLUSIVE: The lock owner can read or change any data in the table provided an X lock can be obtained on rows or pages to be changed and a U or S lock can be obtained on rows or pages to be read.
S : SHARE :  The lock owner can read any data in the table and will not obtain row or page locks
U: UPDATE:  The lock owner can read any data in the table and may UPDATE OR CHANGE data if an X lock on the table is obtained. No page or row locks are obtained.
X:EXCLUSIVE: The lock owner can read or update data in the table. Row or Page locks are not obtained. 
ROW or PAGE lock modes: 
S : SHARE : The Row or Page is being READ by one application and is available for READ-ONLY by other applications.
U:UPDATE :  Row or page is currently READ by one application and it might get changed by that application itself.  U lock will support cursors that are opened with FOR UPDATE OF clause.
X: EXCLUSIVE:  The Row or PAGE is being changed by application and IS NOT available for other application, except those that permit UNCOMMITTED READ.

Indexes are not locked, because their serialization is controlled by latches and concurrency is controlled by data locking. 

Skip Locked Data
The SKIPPED LOCKED DATA option lets a transaction skip rows that are incompatibly locked by other transactions. This option can help improve the performance of some applications by eliminating lock wait time. You should use this technique only for applications that don't require unavailable or uncommitted data. Transactions using this option will not read or modify data that is unavailable, uncommitted, or held by locks.
We can specify the SKIP LOCKED DATA option in a SELECT, SELECT INTO, PREPARE, searched UPDATE, or searched
DELETE statement. You can also use it with the UNLOAD utility. The option works with isolation level CS or RS. It is ignored for isolation levels UR and RR.
The next example returns a count of only the rows that are not uncommitted (if no index exists on CUST_NO).
SELECT COUNT(*)
FROM CUSTOMER
WHERE CUST_NO >= 500
SKIP LOCKED DATA

Different forms of INSPECT clause in cobol

Below are the different INSPECT with REPLACE clause and their usage in cobol.

INSPECT WS-TEXT      REPLACING ALL LOW-VALUES BY SPACE
INSPECT WS-TEXT      REPLACING ALL ' ' BY '0'
INSPECT WS-TEXT      REPLACING ALL LOW-VALUES BY SPACES
INSPECT WS-TEXT      REPLACING FIRST '*' BY '%'
INSPECT WS-TEXT      REPLACING ALL '~' BY WS-INSPECT-VAL
INSPECT WS-TEXT      REPLACING ALL WS-INSPECT-VAL  BY '('
INSPECT WS-TEXT      REPLACING ALL  '('   BY    ')'     AFTER INITIAL '('

**
01 VAR-STRING PIC X(20) VALUE 'THIS IS COBOL'.
01 VAR-COUNT PIC 9(2).

INSPECT VAR-STRING TALLYING VAR-COUNT FOR ALL 'S'
O/P : VAR-COUNT = 2
**
VAR-STRING = "THIS IS COBOL"
INSPECT VAR-STRING TALLYING VAR-COUNG FOR ALL "S" AFTER INITIAL SPACE.

O/P : VAR-COUNT = 1 (Since first 'S' wont be counted . All the 'S' after the first space will be counted.)
**
VAR-STRING = "MY COBOL PROGRAMMING COURSE"
INSPECT VAR-STRING TALLYING VAR-COUNT FOR ALL LEADING 'M'

O/P : VAR-COUNT = 1 (ONLY THE FIRST 'M' WILL BE TAKEN INTO ACCOUNT)
**
01 WS-INPUT3='PTEST'
INSPECT WS-INPUT3 CONVERTING 'PTHASI' TO '3090RI'.
O/P:  30ES0
The exact number of characters need to be compared and mapped. Also duplicate is not
allowed in the parameters.
ex: INSPECT WS-INPUT3 CONVERTING 'PTPTSI' TO '3090RI' will result in error
because 'P' and 'T' is duplicated.
**

Samle Remote stored procedure in CICS/Cobol (RSP)

A Typical RSP Structure
  (check the CICS link for more informtion on RSP)
LINKAGE SECTION.
01 DFHCOMMAREA.
COPY SPAREAC.
01 LK-INPUT-PIPE                    PIC X(100).
01 LK-RETURN-PIPE.
   05 LK-RPLY-SEND-AREA             PIC X(200).

PROCEDURE DIVISION.

PERFORM OPEN-INPUT-PIPE   THRU OPEN-EXIT
PERFORM GET-INPUT-PARM    THRU GET-INPUT-EXIT
PERFORM OPEN-OUTPUT-PIPE  THRU OPEN-OUTPUT-EXIT
PERFORM PROCESS-DATA      THRU PROCESS-EXIT
PERFORM CLOSE-PIPE        THRU CLOSE-EXIT
PERFORM FREE-STORAGE      THRU FREE-EXIT
PERFORM RETURN-TO-FRONT-END.

OPEN-INPUT-PIPE.
MOVE 'OUTPUT'    TO SPMODE.
MOVE 'STD'       TO SPFORMAT.
MOVE  200        TO SPMAXLEN.

CALL 'OPENPIPE' USING SPAREA.
IF SPRC NOT = '000'
MOVE 'OPEN PIPE ERROR IN DRIVER' TO SPMSG
PERFORM PIPE-ERR-RTN  THRU PIPE-EXIT
END-IF.

GET-INPUT-PARM
IF SPVARLEN > 0
   SET ADDRESS OF LK-INPUT-PIPE TO SPVARTXT
   MOVE LK-INPUT-PIPE           TO WS-INPUT-AREA
ELSE
PEROFRM-ERROR-PARA
END-IF.

OPEN-OUTPUT-PIPE.

EXEC CICS GETMAIN
SET (ADDRESS OF LK-RETURN-PIPE)
LENGTH(200)
INITIMG(WS-INITIMG)
RESP (WS-RESP)
END-EXEC

IF WS-RESP = DFHRESP (LENGERR)
MOVE 'CICS LENGTH ERROR IN RETURN AREA'
                                      TO LK-ERR-DESC
END-IF.

PROCESS-DATA
...... PROCESS THE DATA LIKE UPDATE/FETCH WHCH WE GOT FROM FRONT-END
 THRU LINKAGE SECTION INTO OUR WS-VARIABLES.
 PERFORM RESULT-LINKAGE-OUTPUT

RESULT-LINKAGE-OUTPUT
  MOVE WS-OUTPUT-AREA   TO LK-RPLY-SEND-AREA
  SET SPFROM TO ADDRESS OF LK-RETURN-PIPE
  PERFORM PUT-PIPE      THRU EXIT.

PUT-PIPE.
CALL 'PUTPIPE' USING SPAREA.
IF SPRC NOT = '000'
   MOVE 'PUT-PIPE ERROR ' TO SPMSG
   PERFORM PIPE-ERR-RTN
END-IF.

CLOSE-PIPE
CALL 'CLOSPIPE' USING  SPAREA.
IF  SPRC  NOT = '000'
    MOVE  'CLOSE-PIPE ERROR' TO  SPMSG
END-IF.
MOVE  'OK'   TO SPSTATUS
CALL  'STATUS'  USING SPAREA.

FREE-STORAGE.
EXEC CICS FREEMAIN
     DATA (LK-RETURN-PIPE)
END-EXEC

RETURN-TO-FRONT-END.
EXEC CICS 
     RETURN
END-EXEC.

Explanation:
First we need to include SPAREAC in LINKAGE SECTION. SPAREAC includes all pointers,codes that the RSP needs to exchange the data with the front end via the Direct connect.It sets the pointer to the communication channel.
CICS put pipe error might come if the pipe is opened properly .Also if the middleware( like Direct connect or somthing) server is down, CICS program can give put pipe error. So we need to initialize the storage pipes properly and  remove the put pipe,get pipe error.
(For further clarifications leave ur questions in comments section..)
Structure of SPAREAC::::
STORED PROCEDURE COMMUNICATION AREA
03    SPAREA.
05    SPHEADER    PIC X(8).
05    SPRESRVD    PIC X(33).
05    SPTRCOPT    PIC X(1).
05    SPSTATUS    PIC X(2).
05    SPCODE    PIC X(8).
05    SPFORMAT    PIC X(3).
05    SPMODE    PIC X(6).
05    SPRC    PIC X(3).
05    SPFROM    USAGE IS POINTER.
05    SPINTO    REDEFINES SPFROM USAGE IS POINTER.
05    SPSQLDA    REDEFINES SPINTO USAGE IS POINTER.
05    SPVARTXT    USAGE IS POINTER.
05    SPVARTAB    USAGE IS POINTER.
05    SPROWS    PIC S9(8) COMP.
05    SPMAXLEN    PIC S9(4) COMP.
05    SPRECLEN    REDEFINES SPMAXLEN PIC S9(4) COMP.
05    SPVARLEN    PIC S9(4) COMP.
05    SPPREFIX    PIC X.
05    SPMSG    PIC X(100).
05    FILLER    PIC X(3).
05    SPSQL    USAGE IS POINTER.
05    SPATTACH    PIC X(8).
05    SPUSERID    PIC X(8).
05    SPPWD    PIC X(8).
05    SPCMPOPT    PIC X(1).
05    SPIND    PIC X(1).
05    SPDATE    PIC X(8).
05    SPTIME    PIC X(8).
05    SPCONFIG    PIC X(4).
05    SPSERVER    PIC X(30).
05    FILLER    PIC X(32).

The input pipe is opened for communication.In the GETINPUT para, the pointer is set, so that linkage-varibales get the variables passed from front-end via the SPVARTEXT.Then we bring the variables from  likage to our WS-VARIABLES for processing. Once processing is over, we move the Result from WS-OUTPUT to linkage-output and then on to FRONT-END.

Mainframe ISPF Tricks

ISPF tips

f p'###'  to find a string of 3 numeric characters

f p'.'    to find character that can not be displayed.

f p' #'   to find blank followed by a numeric character.

f p'#D'   to find numeric character followed by ‘D’. Give any character in place of ‘D’ like ‘VB’ etc

FIND OUT MVS VERSION

Open SDSF and give  WHO.

Remove all comments from the program
Type the following in the command line opening the program in view mode

X ALL '*' 7  -> This will remove all the comments. Then do
DEL X ALL

 

Difference between syncsort and Dfsort

As a beginner, we are often stuck with the doubt ' Are SYNCSORT and DFSORT same?'.

DFSORT IS IBM’S PRODUCT AND SYNCSORT IS PRODUCT OF SYNCSORT COMPANY.

The basic functions of both are almost same like SORT,MERGE,COPY and other benefits.

ICETOOL is the utility for DFOSRT.

SYNCTOOL is in the same way a tool for SYNCSORT.

For getting syncsort manual you need to give the licensed CPU serial number. DFOSRT manual is freely available.

The easiest way to know if your z/os supports syncsort or dfsort is to look at the messages in sysout for the SORT step.

DFSORT message begins with ICE*

SYSNCORT message begins with WER*.

Some more SYNCSORT/DFSORT Examples with JCL

Earlier we have seen the basic Sort example to start of with. Here lets cover some more typical   SORT examples

ALTSEQ in SYNCSORT/DFSORT

//**************************************************

//STEP02   EXEC PGM=SYNCSORT

//SYSOUT   DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SORTIN   DD DSN=TEST.SORT.INPUT,DISP=SHR

//SORTOUT  DD DSN=TEST.SORT.OUTPUT,

//            DISP=(NEW,CATLG,DELETE),UNIT=DISK,

//            DCB=(RECFM=FB,LRECL=400,BLKSIZE=8800),

//            SPACE=(TRK,(350,200),RLSE)

//SYSOUT   DD SYSOUT=*

//SYSIN    DD *

      SORT FIELDS=COPY

       ALTSEQ CODE=(E340,C940,4D40,5D40)

       OUTREC FIELDS=(1,80,TRAN=ALTSEQ)

/*

EXPLANATION:

ALTSEQ will replace the character in the INPUT File with that specified.Here in ALTSEQ code we are specifying 

ALTSEQ CODE=(E340,C940,F540,5D40).

'E3' is the hexadecimal value for alphabet 'I'.

'40' is the hex equivalent for SPACE. 

So ALTSEQ CODE=E340 will replace 'I' with 'SPACE'. 

Like wise we can do for any characters provided we know the HEX equivalent of that character.

Similarly '4D40' will replace '5' with 'SPACE'. So in OUTPUT we will see 'I' and '5' getting replaced by SPACE.

INPUT:

**************

INDIA    MIKA

INDIA    1500

SWEDEN   2500

SPAIN    1096

TURKEY   2000

BRAZIL   6700

HOLLAND  3456

NEPAL    1209

OUTPUT:

SYNCSORT TO GET COUNT OF RECORDS

//STEP02   EXEC PGM=SYNCSORT

//SYSOUT   DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SORTIN   DD DSN=TEST.SORT.INPUT,DISP=SHR

//SORTOUT  DD DSN=TEST.SORT.OUTPUNT,

//            DISP=(NEW,CATLG,DELETE),UNIT=DISK,

//            SPACE=(TRK,(350,200),RLSE)

//SYSOUT   DD SYSOUT=*

//SYSIN    DD *

      SORT FIELDS=(1,3,CH,A)

      OUTFIL REMOVECC,NODETAIL,

      TRAILER1=('NO OF RECORDS:',COUNT=(M11,LENGTH=8))

/*

REMOVECC omits the ANSI carriage control character from all of the report records.

NODETAIL generates a report with no data records.
SYNCSORT TO PRINT A LINE AFTER EVERY  RECORDS

//STEP02   EXEC PGM=SYNCSORT

//SYSOUT   DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SORTIN   DD DSN=TEST.SORT.INPUT2,DISP=SHR
//SORTOUT  DD DSN=TEST.SORT.OUTPUNT,

//            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,

//            SPACE=(TRK,(350,200),RLSE)

//*ORTOF02 DD DUMMY

//SYSOUT   DD SYSOUT=*

//SYSIN    DD *

      SORT FIELDS=COPY
      OUTFIL BUILD=(1,80,/,80C'-')

/*

SYNCSORT TO EXTRACT A RECORD USING SUB STRING CONDITION.

//STEP02   EXEC PGM=SYNCSORT

//SYSOUT   DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SORTIN   DD DSN=TEST.SORT.INPUT2,DISP=SHR

//SORTOUT  DD DSN=TEST.SORT.OUTPUNT,

//            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,

//            SPACE=(TRK,(350,200),RLSE)

//*ORTOF02 DD DUMMY

//SYSOUT   DD SYSOUT=*

//SYSIN    DD *

      INCLUDE COND=(1,20,SS,EQ,C'IS')

      SORT FIELDS=COPY

/*

SS looks for the sub string 'IS' in the position 1 to 20 in the input file and puts that reocrd in the output. 

SYNCSORT TO CONVERT PACKED DECIMAL TO ZONNED DECIMAL

//STEP02   EXEC PGM=SYNCSORT

//SYSOUT   DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SORTIN   DD DSN=TEST.SORT.INPUT2,DISP=SHR

//SORTOUT  DD DSN=TEST.SORT.OUTPUNT,

//            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,

//            SPACE=(TRK,(350,200),RLSE)

//*ORTOF02 DD DUMMY 
//SYSOUT   DD SYSOUT=*
//SYSIN    DD * 
      SORT FIELDS=COPY
      OUTREC FIELDS=(1,5,PD,ZD) 
/*

input:

----+

*****

1223A

23434

*****

Output:

----+----1

**********
.1.2.2.3.
.2.3.4.3
**********

JOINKEYS  for SYNCSORT

Use sort to filter out matched and unmatched record

//SYSOUT   DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SORTJNF1 DD DSN=TEST.SORT.INPUT2,DISP=SHR

//SORTJNF2 DD DSN=TEST.SORT.INPUT3,DISP=SHR

//SORTOF01 DD DSN=TEST.SORT.OUTPUNT,

//            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,

//            DCB=(*.SORTJNF1),
//            SPACE=(TRK,(350,200),RLSE)

//SORTOUT  DD DUMMY

//SYSOUT   DD SYSOUT=*

//SYSIN    DD *

      JOINKEYS FILES=F1,FIELDS=(1,5,A)

      JOINKEYS FILES=F2,FIELDS=(1,5,A)

      JOIN UNPAIRED,F1,ONLY

      REFORMAT FIELDS=(F1:1,5)

      SORT FIELDS=COPY

      OUTFIL FILES=01,BUILD=(1,5) 
/

EXPLANATION:The above JCL will filter out the unmatched record from INPUT2 by comparing with INPUT3. 

To find out the matching record we need to use.

//SYSIN    DD *

      JOINKEYS FILES=F1,FIELDS=(1,5,A)

      JOINKEYS FILES=F2,FIELDS=(1,5,A)

      REFORMAT FIELDS=(F1:1,5)

      SORT FIELDS=COPY

      OUTFIL FILES=01,BUILD=(1,5)

/*
DFSORT  TO WRITE HEADER ,TRAILER RECORDS
SYSIN DD*
OPTIONS COPY
OUTFIL REMOVECC,
TRAILER1=('TOTAL:',TOT=(10,6,ZD))
Adding the length and mask sub parameter:
SORT FIELDS=COPY                             
OUTFIL REMOVECC,NODETAIL,                    
TRAILER1=('TOTAL:',TOT=(10,6,ZD,M1,LENGTH=9)) 

To Write more than one Trailer we need to use the keywords "Trailer1,Trailer2,Trailer3."
we will see how to write trailer for count of records and total of records.

OPTION COPY                                                   
OUTFIL REMOVECC,                                               TRAILER1=('TOTAL:',TOT=(10,6,ZD,LENGTH=10,EDIT=(TTTTTTTTTT))),
TRAILER2=('COUNT:',COUNT=(LENGTH=10))                          

Explanation of the keywords used: TRAILER1,TRAILER2,TRAILER3,COUNT,TOT are the keywords for SORT cards.OUTFIL is used to print the reports.REMOVECC in sort is used to remove the Cariage control inserted by DFSORT in first position.
The value of '1' in the first position of a record tells the printer to start a new page.To remove these carriage control, we need to use REMOVECC in OUTFIL statement.
TOT=(10,6,ZD) will make the total on 6 digits starting in 10th column.
If we use NODETAIL,then we would see only the trailer and header records.Other records would not be shown in output.
Omitting NODETAIL in OUTFIL would ensure we see all records along with trailer and header.

Output:
----+----1----+----2----+----3-- 
******************************** 
HARLEY   123456MEXICO            
DAVID    658999CANADA            
COUNT:        2                  
TOTAL:0000782455                 
********************************     

To Add Header in SORT using HEADER1 parameter :
OPTION COPY                                                   
OUTFIL REMOVECC,                                              
HEADER1=('REPORT GENERTED AS ON:',&DATE,//,22C'-'),           
TRAILER1=('TOTAL:',TOT=(10,6,ZD,LENGTH=10,EDIT=(TTTTTTTTTT))),
TRAILER2=('COUNT:',COUNT=(LENGTH=10))                         

Output:
*********************************
REPORT GENERTED AS ON:03/02/13   
---------------------            
HARLEY   123456MEXICO            
DAVID    658999CANADA            
COUNT:        2                  
TOTAL:0000782455                 
******************************** 

DFSORT  TO COMPARE THE HEXCODE/ASCII OF CHARACTER ALPHABETS.

//STEP02   EXEC PGM=SORT 
//SORTIN   DD DSN=TEST.SORTINC,DISP=SHR
//SORTOUT  DD DSN=TEST.SORT.OUT1,
//            DISP=(NEW,CATLG,DELETE),UNIT=(SYSDA,59),
//            DCB=*.SORTIN, 
//            SPACE=(TRK,(50,100),RLSE) 
//SYSOUT   DD SYSOUT=* 
//SYSPRINT DD SYSOUT=* 
//SYSIN    DD *
SORT FIELDS=COPY  
INCLUDE COND=(3,1,AC,GE,X'41',AND,3,1,AC,LE,X'4F')
Input:
00B0000
00A1462
00C1850
00D2108
00E2109
00FM006
00ZM007
00ZM008
00YM023
00CM050

Output:
00B0000
00A1462
00C1850
00D2108
00E2109
00FM006
00CM050
Explanation: The above sort card checks for the characters from A to O.  All other characters will be eliminated. '41' hex of 'A' and '4F' is hex of 'O' in ASCII.
DFSORT  TO INSERT/ADD  DELIMITER/CHARACTERS AFTER EVERY RECORD.

//SYSIN    DD *                             
SORT FIELDS=COPY                            
INREC BUILD=(1,60,SQZ=(SHIFT=LEFT,MID=C'~'))

Explanation:MID=C'`' tells DFSORT to insert the character between the fields.

SORT  TO REMOVE SPACES BETWEEN CHARACTERS
SQZ operator in DFSORT/SYNCSORT can be used to remove spaces between characters.
Input:Q WE R T Y 
Expected Output: QWERTY
We can use SQZ operator to remove the spaces and format the field.
OPTION COPY                           
OUTREC FIELDS=(1,40,SQZ=(SHIFT=LEFT)) 
Explanation: We are squeezing out the blanks and  shifting the characters to the left for all the data in thje positions 1 to 40.