FETCH FIRST N ROWS Vs OPTIMIZE FOR N ROWS in DB2

Very Often we use these two statements in our SQL query,ie, OPTIMIZE FOR N ROWS and FETCH FIRST N ROWS ONLY.
Although it may sound similar,but both of them have specific usage when it comes to the optimizer. This post we will try to see difference between the two.

As the name suggests FETCH FIRST 5 ROWS ONLY, it actually puts a limitation on the number of rows the query is returning.I am assuming here n=5. So, here user gets only 5 rows even though there can be 100 qualifying rows for that query.

OPTIMIZE FOR 5 ROWS will straight way influence the optimizer.It does not limit the number of rows SQL will return. So using this , we will get all the qualifying rows,may be incrementally.Intent here is to give priority to retrieve first few rows.Once optimizer understands this ,it will give preference to access plan that will minimize the response time.So,DB2 will send the rows in a single block. Consider the below scenario where this will come handy.

You have an online application where the screen can hold details of 5 customers at a time. Suppose you have the below query to fetch the customer details

Select Fname, Lname, Dept, Sal,Rollno
  From Dept table 
Order by Sal Desc;

We are assuming the index to be defined on Rollno. If a descending index also exists on SAL column, its likely to have low cluster ratio.
Without using the OPTIMIZE clause, DB2 will do a full table space scan and sort on sal column to fetch you the details.

Now on Adding  'ÓPTIMIZE FOR 5 ROWS' to the query, DB2 will use SAL index directly because it knows you need  the details of 5 highest paid employees.
So, it displays 5 rows to the screen and depending on user input will process the  next 5 or do something else.Here DB2 encourage matching index scan and would not prefer list or sequential prefetch. Access plan selected for 5 records may not be good for 100 rows.

Thus, this OPTIMIZE clause comes handy for queries where you can process incrementally.We can use it to get rows directly from table without going through any buffering operations like sorting. 

Including Date field in the output file using SORT

Including Date field in the output file:

Many a times it is required to include the date in the output file. This can be done using DATE parameter in SORT.
There are 3 DATE parameter option available, DATEn, DATEn(c) and DATEnP where n=1, 2 or 3.

Consider the input file INFILE,
1111111111111111111111111111
1111111111111111111111111111
1211111111111111111111111111
1311111111111111111111111111
1411111111111111111111111111
1511111111111111111111111111
1611111111111111111111111111
1711111111111111111111111111
1811111111111111111111111111
1911111111111111111111111111
2011111111111111111111111111
2111111111111111111111111111
The output date is in Zoned Decimal format.
The DATE1 occupies 10 bytes and gives the date in YYYYMMDD format.
The DATE2 occupies 6 bytes and gives the date in YYYYMM format.
The DATE3 occupies 7 bytes and gives year and Julian date (JDT) as YYYYJDT format.
The JCL’s below show the use of DATEn parameter. The current date August 26, 2010(Julian Date 238)

//STEP01 EXEC PGM=SORT
//SORTIN DD DSN=INFILE,DISP=SHR
//SORTOUT DD DSN=OUTFILE1,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
OUTREC FIELDS=(1,29,&DATE1)
//

The contents of OUTFILE are as below,
OUTFILE:
1111111111111111111111111111 20100826
1111111111111111111111111111 20100826
1211111111111111111111111111 20100826
............

On using DATE1 we have the current date in YYYYMMDD format.

//STEP01 EXEC PGM=SORT
//SORTIN DD DSN=INFILE,DISP=SHR
//SORTOUT DD DSN=OUTFILE2,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
OUTREC FIELDS=(1,29,&DATE2)
//

The contents of OUTFILE2 are as below,
1111111111111111111111111111 201008
1111111111111111111111111111 201008
1211111111111111111111111111 201008
.........
On using DATE2 we have the output date in YYYYJDT format

//STEP01 EXEC PGM=SORT
//SORTIN DD DSN=INFILE,DISP=SHR
//SORTOUT DD DSN=OUTFILE2,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
OUTREC FIELDS=(1,29,&DATE3)
//

The contents of the OUTFILE2 is as below,
1111111111111111111111111111 2010238

Using the DATEn(c) parameter:
On using the DATEn(C) parameter, the output date appears in formatted way wherein a character ‘/’ is placed between the year month and date fields.
DATE1(c) occupies 10 bytes and the format is YYYY/MM/DD.
DATE2(c) occupies 7 bytes and the format is YYYY/MM.
DATE3(c) occupies 7 bytes and the format is YYYY/JDT.

Using the DATEnP parameter:
On using DATEnP the output appears in Packed decimal format. So the number of bytes occupied is lesser than DATEn parameter. Other than this there is no difference between DATEn and DATEnP.
The DATE1 occupies 5 bytes and gives the date in YYYYMMDD format.
The DATE2 occupies 4 bytes and gives the date in YYYYMM format.
The DATE3 occupies 4 bytes and gives year and Julian date (JDT) as YYYYJDT format.
1111111111111111111111111111 2010238


Retrieving Information on records having older dates:
Consider a case wherein we need to retrieve records that have yesterday’s date.
The JCL is as below,

//STEP01 EXEC PGM=SORT
//SORTIN DD DSN=INFILE1,DISP=SHR
//SORTOUT DD DSN=OUTFILE3,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
INCLUDE COND=(30,8,CH,EQ,&DATE1-1)
OUTREC FIELDS=(1,40)
//

Splitting Input Files using Sort. Use of SPLIT ,SPLITBY,SPLIT1R commmands

SPLIT command spits the output records one record at a time among output datasets. This happens until all the output records are written. The split happens in rotation among the datasets mentioned in the OUTFIL.
The First record from the output records is written to first dataset mentioned in the OUTFIL group, the Second record from the output records gets written to the second dataset mentioned in the OUTFIL group and so on.
When each OUTFIL dataset has 1 record, the rotation starts again with the dataset mentioned first in the OUTFIL group.
The records are not contiguous in the OUTFIL datasets.
The Below JCL splits the data in INFILE and copies to OUTFILE1 and OUTFILE2 as mentioned above.

Consider the contents of Input File - INFILE as below:

1111111111111111111111111111
1211111111111111111111111111
1311111111111111111111111111
1411111111111111111111111111
1511111111111111111111111111
1611111111111111111111111111
1711111111111111111111111111
1811111111111111111111111111
1911111111111111111111111111
2011111111111111111111111111
2111111111111111111111111111
Let us use the commands and see the outputs.

The Below JCL splits the data in INFILE and copies to OUTFILE1 and OUTFILE2 as mentioned above.

//STEP01 EXEC PGM=SORT
//SORTIN DD DSN=INFILE,DISP=SHR
//SORTOUT1 DD DSN=OUTFILE1,DISP=SHR
//SORTOUT2 DD DSN=OUTFILE2,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FNAMES=(SORTOUT1,SORTOUT2),SPLIT
/*

The contents of OUTFILE1 and OUTFILE2 would be as below,
OUTFILE1
1111111111111111111111111111
1311111111111111111111111111
1511111111111111111111111111
1711111111111111111111111111
1911111111111111111111111111
2111111111111111111111111111
OUTFILE2
1211111111111111111111111111
1411111111111111111111111111
1611111111111111111111111111
1811111111111111111111111111
2011111111111111111111111111
OUTFILE1 dataset contains records 1, 3, 5…so on.
OUTFILE2 dataset contains records 2, 4, 6…so on.
Note that the records in the output datasets are not contiguous.

SPLITBY Command:

SPLITBY splits the output records M records at a time in rotation among the datasets mentioned in the OUTFIL. This happens until all the output records are written.
The First Set of records from the output records gets written to first dataset mentioned in the OUTFIL group, the Second Set of records from the output records gets written to the second dataset mentioned in the OUTFIL group and so on.
When each OUTFIL dataset has the specified set of records, the rotation starts again with the dataset mentioned first in the OUTFIL group.
The syntax is SPLITBY=M, where M=1,2,3…so on
The records are not contiguous in the OUTFIL datasets.
SPLITBY=1 is equivalent to SPLIT.
The below JCL splits the data in INFILE and copies to OUTFILE3 and OUTFILE4 as mentioned above.

//STEP01 EXEC PGM=SORT
//SORTIN DD DSN=INFILE,DISP=SHR
//SORTOUT1 DD DSN=OUTFILE3,DISP=SHR
//SORTOUT2 DD DSN=OUTFILE4,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FNAMES=(SORTOUT1,SORTOUT2),SPLITBY=3
/*

The contents of OUTFILE3 and OUTFILE4 would be as below,
OUTFILE3
1111111111111111111111111111
1211111111111111111111111111
1311111111111111111111111111
1711111111111111111111111111
1811111111111111111111111111
1911111111111111111111111111
OUTFILE4
1411111111111111111111111111
1511111111111111111111111111
1611111111111111111111111111
2011111111111111111111111111
2111111111111111111111111111
OUTFILE3 contains records (1, 2, 3), (7, 8, 9).
OUTFILE4 contains records (4, 5, 6), (10, 11).
Note that the records in the output datasets are not contiguous.

SPLIT1R splits output records M records at a time in one rotation among the datasets mentioned in the OUTFIL. This happens until all the records are written. In SPLIT1R the rotation happens only once among the OUTFIL datasets.
If on reaching the last OUTFIL, more than M records from the output records is left, all of those would be move to last OUTFIL.
If the input has only M records, then all input records will get moved to the first OUTFIL. The remaining OUTFIL datasets will be empty.
The syntax is SPLIT1R=M, where M=1, 2, 3…so on.
The records are contiguous among the OUTFIL datasets.
The below JCL’s splits the data in INFILE,
JCL1:

//STEP01 EXEC PGM=SORT
//SORTIN DD DSN=INFILE,DISP=SHR
//SORTOUT1 DD DSN=OUTFILE5,DISP=SHR
//SORTOUT2 DD DSN=OUTFILE6,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FNAMES=(SORTOUT1,SORTOUT2),SPLIT1R=5

The output files contents are shown below,
OUTFILE5:
1111111111111111111111111111
1211111111111111111111111111
1311111111111111111111111111
1411111111111111111111111111
1511111111111111111111111111
OUTFILE6:
1611111111111111111111111111
1711111111111111111111111111
1811111111111111111111111111
1911111111111111111111111111
2011111111111111111111111111
2111111111111111111111111111
There are two output files, and M=5. The input INFILE contains 11 records.
The OUTFILE5 contains records 1, 2, 3, 4, 5.
The dataset OUTFILE6 contains records 6, 7, 8, 9, 10, 11(i. e all the remaining records)

JCL2:

//STEP01 EXEC PGM=SORT
//SORTIN DD DSN=INFILE,DISP=SHR
//SORTOUT1 DD DSN=OUTFILE7,DISP=SHR
//SORTOUT2 DD DSN=OUTFILE8,DISP=SHR
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FNAMES=(SORTOUT1,SORTOUT2),SPLIT1R=11
//

The output file contents are shown below:

OUTFILE7:
1111111111111111111111111111
1211111111111111111111111111
1311111111111111111111111111
1411111111111111111111111111
1511111111111111111111111111
1611111111111111111111111111
1711111111111111111111111111
1811111111111111111111111111
1911111111111111111111111111
2011111111111111111111111111
2111111111111111111111111111

OUTFILE8
empty as expected.

Convert VB file to FB and Convert FB file to VB using SORT

The below JCL copies the VB file to FB file.

//STEP01 EXEC PGM=SORT
//SORTIN DD DSN=INPUTVBFILE,DISP=SHR
//SORTOUT DD DSN=OUTPUTFBFILE,DISP=SHR
//*
//SORTWK01 DD SPACE=(CYL,10),UNIT=SYSDA
//SYSOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(5,76,CH,A)
OUTFIL FNAMES=SORTOUT,VTOF,BUILD=(5,76)
/*

The INPUTVBFILE is a VB file with record length 80.
The OUTPUTFFBFILE is a FB file of record length 76.
Before executing the JCL it is assumed that both the SORTIN and SORTOUT datasets exists.
SORT FIELDS=(5,76,CH,A) sorts the input VB file.
VTOF will handle copying the VB file to FB file.
It is essential to give BUILD or OUTREC parameter when VTOF parameter is used.

Below JCL will Convert FB file to VB

//STEP01 EXEC PGM=SORT
//SORTIN DD DSN=INPUTFBFILE,DISP=SHR
//SORTOF01 DD DSN=OUTPUTVBFILE,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// DCB=(LRECL=80,RECFM=VB,BLKSIZE=84),
// SPACE=(TRK,(3000,2000),RLSE)
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL FNAMES=SORTOF01,FTOV

It is not essential to give BUILD or OUTREC parameter when FTOV parameter is used.

Copy empty Vsam file using SORT without error - Use of parameter VSAMEMT=YES in SORT

Once came across a scenerio where we had to copy a vsam file to a flat file and then process the flat file in subsequent steps.This is pretty simple and can be achived with a SORT step.But Once the same job abended when the VSAM file was emprty.
Came across this parameter VSAMEMT=YES which can be used with sort to handle this scenerio.

//STEP3 EXEC PGM=SORT,PARM=’VSAMEMT=YES’
//*
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=XXX.TEST.VSAM,DISP=SHR
//SORTOUT DD DSN=TEST.FLATFILE.COPY,DISP=MOD
//SYSIN DD *
SORT FIELDS=COPY
/*

Sort JCL to split every alternate records

 This JCL will split the even and the odd number of records from the input file.

//STEP01   EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//SORTWK01  DD UNIT=DISK,SPACE=(CYL,(100,100))       
//SORTIN    DD *                                     
1111111111111111111111111                            
2222222222222222222222222                            
3333333333333333333333333                            
4444444444444444444444444                            
5555555555555555555555555                            
6666666666666666666666666                            
//ODD       DD DSN=TEST.ODD.OP1,
//          DISP=(,CATLG),UNIT=TEST,                 
//          SPACE=(CYL,(50,50),RLSE)                 
//EVEN       DD DSN=TEST.EVEN.OP2,
//          DISP=(,CATLG),UNIT=TEST,                 
//          SPACE=(CYL,(50,50),RLSE)                 
//SYSIN     DD *                                     
  SORT FIELDS=COPY                                   
  OUTFIL FNAMES=(ODD,EVEN),SPLIT                     
//*   

Here is the output of the ODD file:

******************************
1111111111111111111111111    
3333333333333333333333333    
5555555555555555555555555    
******************************

Here is the output of the EVEN  file:

**************************
2222222222222222222222222
4444444444444444444444444
6666666666666666666666666
**************************

SPLIT parameter to put the first record into OUTPUT1, the second record into OUTPUT2, the third record into OUTPUT1, the fourth record into OUTPUT2, and so on until you run out of records. SPLIT splits the records one at a time among the data sets specified by FNAMES.
Other options SPLITBY and SPLIT1R are also available. Do check out the usage for further info.

DB2 Explain and PLAN table column names in DB2

Whenever we run a query,DB2 creates an access plan that specifies how it will access the requested data. This is created whenevea the sql is compiled at bind time for static sql and before execution for dynamic sql.

(Btw, For Advanced topics on mainframe you can visit https://dbztech.blog/)

DB2 bases the access paths on the SQL statements and also on the statistics and configuration parameters of the system.
Even when an sql is made efficient, it can become inefficient as data grows. So, we need to run DB2 runstats  so as to keep updated statistics . DB2 config and storage can change and plans can be rebound. Db2 explain gives us the info for the plan, package, or SQL statement when it is bound. The output of the EXPLAIN  is stored in user-created table called Plan table. Whenever we want to tune a query, we need to go and check the plan table so as to get an idea of the access path DB2 optimizer is using.

How can we populate the PLAN Table ?
EXPLAIN(YES) option on the BIND/REBIND plan/package command
EXPLAIN ALL keyword in SPUFI or while running the query in batch.

Step 1. The SQL statement in blue is the main Query for which we want to know the access path.
So we wrap the statement with the 'EXPLAIN ALL SET QUERYNO = 1 FOR'  like below

EXPLAIN ALL SET QUERYNO = 1 FOR
SELECT CUSTNO, CUSTLNAME                       
FROM CUST                                                        
WHERE CUSTNO LIKE '%0A';
Once we execute the above query, optimizer first writes the access path onto the Plan Table and then gives the output. Step below depicts how we can get the information from plan table

Now, Let us check few of the columns in the PLAN table and its significance: Given in Blue are the names of the table columns.
QUERYNO: Query number assigned by the user
QBLOCKNO:A number that identifies each query block within a query.
APPLNAME:The name of the application plan for the row.
PROGNAME:The name of the program or package containing the statement being explained.Applies for the explain as a result of SQL queries embeded in application program.
TSLOCKMODE: Identifes the Tablespace lock mode.

These columns relate to the index usage:
ACCESSTYPE:Type of table INDEX usage as as follows:
R -Full table scan (uses no index) when the query is executed
I -Use an index. Data will be retrieved from index and not from table,
I1 -one-fetch scan (MIN or MAX) functions
N -Index scan (predicate uses an IN )
M -Multi-index scan followed
   MX By an index scan on the index named in ACCESSNAME
   MI By an intersection of multiple indexes
   MU By a union of multiple indexes
MATCHCOLS: For ACCESSTYPE I, I1, N or MX, the number of index keys used in an index scan; otherwise, 0.
ACCESSCREATOR:For ACCESSTYPE I, I1, N, or MX, the creator of the index; otherwise, blank.
ACCESSNAME: For ACCESSTYPE I, I1, N, or MX, the name of the index; otherwise, blank.
INDEXONLY: Whether access to an index alone is enough to carry out the step, or whether data too must be
accessed. Y=Yes; N=No

The plan table columns that relate to SORT usgae are as follows:
METHOD:
A number (0, 1, 2, 3, or 4) that indicates the join method used for the step:
0 First table accessed, continuation of previous table accessed, or not used.
1 Nested loop join. For each row of the present composite table, matching rows of a new table are
found and joined.
2 Merge scan join. The present composite table and the new table are scanned in the order of the
join columns, and matching rows are joined.
3 Sorts needed by ORDER BY, GROUP BY, SELECT DISTINCT, UNION, a quantified predicate, or an
IN predicate. This step does not access a new table.
4 Hybrid join. The current composite table is scanned in the order of the join-column rows of the
new table. The new table is accessed using list prefetch.

SORTN_UNIQ: Whether the new table is sorted to remove duplicate rows. Y=Yes; N=No.
SORTN_JOIN: Whether the new table is sorted for join method 2 or 4. Y=Yes; N=No.
SORTN_ORDERBY: Whether the new table is sorted for ORDER BY. Y=Yes; N=No.
SORTN_GROUPBY: Whether the new table is sorted for GROUP BY. Y=Yes; N=No.
SORTC_UNIQ: Whether the composite table is sorted to remove duplicate rows. Y=Yes; N=No.
SORTC_JOIN: Whether the composite table is sorted for join method 1, 2 or 4. Y=Yes; N=No.
SORTC_ORDERBY: Whether the composite table is sorted for an ORDER BY clause or a quantified predicate. Y=Yes;
N=No.
SORTC_GROUPBY: Whether the composite table is sorted for a GROUP BY clause. Y=Yes; N=No.
PREFETCH : Whether data pages are to be read in advance by prefetch.  If we dont want to use the sequentail prefetch for a particualr query,we need to add the clause
OPTIMIZE FOR 1 ROW to it.

 Read about basic DB2 Prefetch  

What we should be looking at:

1. Indexes enhance performance and and reduce costs. We need to look the ACCESSTYPE to see if
an index is being used.. An ACCESSTYPE of "R" means all the data must be scanned. and no
indexes are being used.
2. Look for MATCHCOLS to see how many  index keys are being ueed. The more the better.
3. Check for the column INDEXONLY . Value of 'Y' means data being retrieved from index and no table is involved. This is no doubt good in terms of performance. Booster will be to have the columns used in 'Where predicate' as indexes.
4. Avoid unnecessary sorts as auch as possible.
5.PREFETCH is good and will be in action when mostly the table space scan is used. Very effective when the table data is in clustered sequence.

Some Learnings
1. Don't misuse select statements
2. Use IN instead of multiple ORs
3. Join with as any of the index columns as possible.
4. Avoid Arithmetic expressions in where clause
5. Use NOT EXISTS instead of NOT IN for a suvbquery

DFSORT/SYNCSORT to include spaces, insert fixed strings and refortmat the records using OUTREC

Continuing with the Previous SORT examples, this section will have some SORT features to understand the INREC/OUTREC features and how they work.
In the following sort example, i am trying to insert spaces and insert fixed string in the input file and format the output record.
Since , we are trying to build the record, ie, manipulate the entire record structure here and there, we will go with OUTREC BUILD option. This gives us complete control over the record structure. We can pick up any record from any position and place it anywhere as per the requirement.
Here goes my input file.

----+----1----+----2----+----3----+-
********************************* To
A001MUKESHN                        
A002GRECHEN                        
A003STEVEEN                        
A003STEVEEN                        
A004STEVEEN                        
A004STEVEEN                        
******************************** Bottom

SORT JCL
//STEP0010 EXEC PGM=SORT                         
//SYSOUT    DD SYSOUT=*                          
//SORTWK01  DD UNIT=DISK,SPACE=(CYL,(100,100))   
//SORTIN    DD DSN=BHI522.SORT.TEST1,DISP=SHR    
//SORTOUT    DD DSN=BHI5122.TEST.SORT.OP3,        
//          DISP=(,CATLG),UNIT=TEST,             
//          SPACE=(CYL,(50,50),RLSE)             
//SYSIN     DD *                                 
  SORT FIELDS=COPY                               
  OUTREC BUILD=(1:1,4,5:2X,8:C'TST',13:5,7)     
//*                                               
Output:

----+----1----+----2----+----3----+----4----+----5----+--
********************************* Top of Data ***********
A001   TST  MUKESHN                                     
A002   TST  GRECHEN                                     
A003   TST  STEVEEN                                     
A003   TST  STEVEEN                                     
A004   TST  STEVEEN                                     
A004   TST  STEVEEN                                     
******************************** Bottom of Data *********

As we see here, OUTREC parameter, '1:1,4' tells sort to :Take record of length 4 bytes starting from 1st column and  place it in 1st column of the output file.
5:2X will put 2 byte of spaces.  X indicate spaces to be included. When we use 3X, that means 3 spaces to be put.
8:C'TST'   will tell sort to put the string 'TST' from 8th byte of the output record.
13:5,7 Will instruct sort to: Take the record of length 7 bytes from 5th column of the input file and put from 13th column in the output file.
Now match the output, and we can see the result!
A Point to remember : For INREC and OUTREC we can use FIELDS or BUILD. For OUTFIL , we can use  OUTREC or BUILD

2. Get the HEX Values using SORT
Using the Same input file, will use the OUTFIL OUTREC command to print the hex values
   ............... same as above JCL......
  SORT FIELDS=COPY                  
  OUTFIL OUTREC=(1:1,4,TRAN=HEX)     //*                                
Output will look like:

----+----1--
************
C1F0F0F1   
C1F0F0F2   
C1F0F0F3   
C1F0F0F3   
C1F0F0F4   
C1F0F0F4   
************

Will keep updating ........

SAS in Mainframes(z/Os) Tutorial with xamples - Part 2 ( Creating csv/excel file from mainframe dataset using SAS)

1. We will see how we can Merge two or more  input files in SAS and routing it to one output dataset and USE the same dataset to prepare a report in excel format
Creating .xls file on Z/os
(Refer to  Previous  posts to know basic steps in sas)
Lets take two input file, RXX.TEST.FILE3 and RXX.TEST.FILE4 with the fields
CITY,DATE,STATE,AMT in FILE3 and CITY,DATE,STATE,BANK in FILE4.We want to merge both the files so that the output contains CITY DATE STATE BANK.For achieving this, we need have atleast one common field in both the files based on which we can join these two datasets. We will be joining based on CITY.So the steps should be as follows.
Step1.Create the SAS dataset from input file 3 and SORT it on the key field
Step2.Create the SAS dataset from input file 4 and SORT it on the key field
Step3.create a new SAS dataset Using  the MERGE keyword in SAS along with the key field and finally
Step4. take the fields which we need

//SAS01     EXEC SAS                                
//POLIN    DD DSN=RXX.TEST.FILE3,DISP=SHR
//POLIN2   DD DSN=RXX.TEST.FILE4,DISP=SHR
//OUTFILE   DD DSN=RXX.TEST.FILEOUT,DISP=(,CATLG),
//             SPACE=(TRK,(20,20),RLSE),LRECL=180,RECFM=FB
//WORK      DD SPACE=(CYL,(50,10),RLSE)                   
//SYSIN     DD *   
OPTION NOCENTER;                   
OPTION SORTLIB='';                 
  DATA POLIN;                      
   INFILE POLIN;                   
   INPUT @01 CITY   $CHAR02.       
         @06 DATE   $CHAR08.       
         @14 STATE  $CHAR02.       
         @16 AMT    COMMA9.2;      
   PROC SORT DATA=POLIN NODUPS;    
     BY CITY;                                                          
  DATA POLIN2;                     
   INFILE POLIN2;                  
   INPUT @01 CITY   $CHAR02.       
         @06 DATE   $CHAR08.       
         @14 STATE  $CHAR02.       
         @16 AMT    COMMA9.2       
         @25 BANK   $CHAR5;        
                                   
   PROC SORT DATA=POLIN2 NODUPS;   
     BY CITY;                      
  DATA COMMON;                     
  MERGE POLIN(IN=D1) POLIN2(IN=D2);
  BY CITY;                         
  IF D1 AND D2 THEN OUTPUT;        
  PROC PRINT DATA=COMMON;          
     VAR CITY DATE STATE BANK;     
 RUN;                              
 DATA _NULL_;                           
    SET COMMON;                         
    FILE OUTFILE;                       
    PUT CITY ',' DATE ',' STATE ',' BANK;
RUN;                                    
Here goes the output for the same:
***********************
CA ,20130320 ,WB ,BANK1
CA ,20130120 ,TN ,BANK6
CA ,20130120 ,TN ,BANK6
CA ,20130320 ,KA ,BANK6
MI ,20130120 ,KA ,BANK1
MI ,20130320 ,AP ,BANK1
RR ,20130120 ,AP ,BANK8
************************
Why Do we use DATA _NULL_ in SAS ?  This simply is used when we want to make a report.
_NULL_  is a SAS keyword which does not create any SAS dataset.

2. Creating the excel report / CSV file from the mainframe dataset.
To add column headings in SAS to be used in excel sheet, we can use the DATA _NULL_ statement as well.
FILE OUTFILE  DLM=',';
IF _N_=1 THEN DO;
 PUT     'CITY,' 
         'DATE,'
         'STATE,' 
         'BANK'
;
END;

PUT  CITY 
     DATE
     STATE
     BANK
;
To Create a CSV File from a mainframe Dataset we can use the same above code with The delimiter option. DLM=','.(Imp point to remember. Delimeter is the key in creating .xls file)
 (If you remember we need to use delimited option in excel to prepare a formatted report from notepad. DLM option in SAS takes care of that ).
The line of code _N_=1 has special significance. We will check it later. However you can try running the program without using the specific line and see what happens.:)
So we  can download the dataset from command shell (option 6) in ISPF and use 'Receive from Host' option. Save the File in .csv format.
Or otherwise  put one FTP step (where u want to put the report) after the mainframe DATASET is created and save the file in filename_youwant.csv. No need to create a text file and convert it into excel sheet. The FTP location will contain the .xls file and ready to use!!

SAS in Mainframes(z/Os) Tutorial with xamples - Part 2

We have seen how the data step and Proc functions in the Part 1 of this SAS  blog. We need to remember that the sas datasets or variables created in One Data steps remains defined only to that step unless we specify some condition, using which we can refer to the SAS dataset created in prior steps.In the example below,we are creating the sas Dataset RYAN in the first step.The input file being used in POLIN. Using the statement  'PROC PRINT DATA=RYAN; '  we are printing the output in spool.
Using SET keyword in SAS,in DATA RYAN2,  we are referring to the dataset created in the first step and printing the same data in step RYAN2;  SET is the keyword in SAS.
  DATA RYAN;                              
   INFILE POLIN;                           
   INPUT @01 CITY   $CHAR02.
         @06 DATE   $CHAR08.
         @14 STATE  $CHAR02.
         @16 AMT    COMMA9.2;
   PROC SORT DATA=RYAN NODUPS;            
     BY CITY;                     
   PROC PRINT DATA=RYAN;      
                                     
   DATA RYAN2;                            
   SET RYAN; /* this is referring to the dataset created  above*/
   TITLE "I AM SHOWING SAME DATA OF RYAN";
   PROC PRINT DATA=RYAN2;                 
 RUN;                                      
//*
Output:
Obs    CITY      DATE      STATE       AMT   
 1      CA     20130320     WB      100000.55
 2      CA     20130320     WB      200000.55
 3      CA     20130120     TN      500000.55
 4      CA     20130320     KA      600000.55
 5      MI     20130120     KA      300000.55
 6      MI     20130320     AP      400000.55
 7      RR     20130120     AP      700000.55
 8      ST     20130320     TN      800000.55

I AM SHOWING SAME DATA OF POLIN             
Obs    CITY      DATE      STATE       AMT  
 1      CA     20130320     WB      100000.55
 2      CA     20130320     WB      200000.55
 3      CA     20130120     TN      500000.55
 4      CA     20130320     KA      600000.55
 5      MI     20130120     KA      300000.55
 6      MI     20130320     AP      400000.55
 7      RR     20130120     AP      700000.55
 8      ST     20130320     TN      800000.55

Continuing from the program above, we can add more datasets or add more data and carry on with creating the reports.

SAS in Mainframes(z/Os) Tutorial with xamples

To know the SAS Basics, check  Chapter 1  . It demonstrates the very basic working principle of sas.
With your understanding of  the basics in sas, we will start the SAS in mainframe (Z/OS) environment .
The very first thing to know:  Turning Raw Data Into Information is what SAS is all about !!!!

This is the basic principle of how SAS works. The raw data(input file in JCL) is read into SAS through INFILE keyword.This has the same name as the DD name in the JCL.
Once the file is read, the next step is to structure/format the data in SAS Dataset through INPUT keyword as explained below.
 
DATA RYAN;
  INFILE POLIN ;
  INPUT @10  FNAME    $CHAR10.
              @24  ACCTNO   $CHAR05;

Here the keyword DATA  implies the starting of DATA step. RYAN is the name of the Data step.It can be any name.
INFILE POLIN; This is where the raw data is read by sas. POLIN is the JCL DD name for the input.
INPUT @ FNAME $CHAR10...; These statements structures the input dataset read above(here POLIN) and creates a dataset which is internal to SAS(commonly called the SAS data set). Internally SAS would be using this data structure created.
So here the SAS dataset contains FNAME (10 bytes) which is @ 10th position in input file and ACCTNO(5 bytes) which is @ 24th position in input.It wont consider the other records which might be present in the input dataset.
After that we manipulate the data as per our needs and do various functions.In all SAS programs only two steps are of utmost importance namely the DATA step and PROC step.

SAS Syntax Rules:  (A few very handy rules to make life simple!)Can begin and end in any column.
One or more blanks or special characters can be used to separate words.
A single statement can span multiple lines.
Several statements can be on the same line.
/* to begin a comment and */ to end it
 A SAS program generally Mostly comes with the default installation of Z/OS just like DFSORT.Only u need to know the library.
Lets start with few of the basic inbuilt functions and see the output in spool.No input file is required here.Lets see the DATE Function in SAS
//X15122RY  JOB (10,&SYSUID),'RYAN',CLASS=T,
//    MSGCLASS=V,NOTIFY=&SYSUID
//*                                   
//SAS01     EXEC SAS
//WORK      DD SPACE=(CYL,(50,10),RLSE)
//SYSIN     DD *                                            
OPTION NOCENTER;                                                  
OPTION SORTLIB='';
  DATA RYAN;       
    THISYEAR = YEAR(TODAY());           
    THISMONTH= MONTH(TODAY());          
    THISDAY  = DAY(TODAY());            
    LASTYEAR = YEAR(TODAY()) - 1;       
    DATE = TRIM(LEFT(THISYEAR))|| '1101';
  PROC PRINT DATA= RYAN;
   VAR THISYEAR LASTYEAR THISMONTH DATE THISDAY
//*
Output:
The SAS  System
Obs    THISYEAR    LASTYEAR    THISMONTH      DATE      THISDAY
 1       2013        2012          6        20131101       20   
Looks Cool!
Now , as we can see the value of  THISMONTH is 6. What if we want to get and display like 06
So we need to modify the date function output to get two digits output .
THISMONTH= PUT(MONTH(TODAY()),Z2.);
HDR = TRIM(LEFT(THISYEAR))|| PUT(LASTMONTH,Z2.);
Explanation:
Option NOCENTER; is SAS statement which aligns the output. Lets not be bothered about that.
We can see the data step starts with DATA RYAN;
Since no input file is used here, so we do not have infile and input statements here.
Today() is a function in SAS. It can be used with various combinations to give us the dates we want.
PROC PRINT DATA=RYAN is the proc step. We are using the keyword VAR to include or select the variables from  the data step to print.

Lets go to the Next step will be to add an input file and print its contents.
//SAS01     EXEC SAS
//POLIN    DD DSN=I15122.TEST.FILE,DISP=SHR <== input dataset
//WORK      DD SPACE=(CYL,(50,10),RLSE)
//SYSIN     DD *                                            
OPTION NOCENTER;                                                  
OPTION SORTLIB='';
DATA RYAN;       
  INFILE POLIN;
  INPUT @01 CITY   $CHAR02.
        @06 DATE   $CHAR08.
        @14 STATE  $CHAR02.
        @16 AMT    COMMA9.2;
  PROC PRINT DATA= RYAN;
This is how the input looks like.
Input File:
----+----1----+----2----+-
**************************
CA00020130320WB100000.55 
CA00020130320WB200000.55 
MI00020130120KA300000.55 
MI00020130320AP400000.55 
CA00020130120TN500000.55 
CA00020130320KA600000.55 
RR00020130120AP700000.55 
ST00020130320TN800000.55 
**************************
Output:
The SAS System
Obs    CITY      DATE      STATE       AMT  
 1      CA     20130320     WB      100000.55
 2      CA     20130320     WB      200000.55
 3      MI     20130120     KA      300000.55
 4      MI     20130320     AP      400000.55
 5      CA     20130120     TN      500000.55
 6      CA     20130320     KA      600000.55
 7      RR     20130120     AP      700000.55
 8      ST     20130320     TN      800000.55

The fist column is inserted by SAS in the output called observation column,ie number of rows processed.Use of NOOBS in the proc statement will suppress the First column.
If we would run it with NODUPKEYS with Key on the CITY, then it would remove all the duplicate key values.The below code shows how PROC SORT can be used to sort out the data and filter out duplicate.. We need to give one key value if we use NODUPKEYS. If we do not give we will get the below error.
ERROR: No BY statement used or no BY variables specified. A BY statement must be used with variable names to sort on.
DATA RYAN;       
  INFILE POLIN;
  INPUT @01 CITY   $CHAR02.
        @06 DATE   $CHAR08.
        @14 STATE  $CHAR02.
        @16 AMT    COMMA9.2;
  PROC SORT DATA= RYAN NODUPKEYS;
       BY CITY;
     PROC PRINT DATA= RYAN
Output:
Obs    CITY      DATE      STATE       AMT
 1      CA     20130320     WB      100000.55
 2      MI     20130120     KA      300000.55
 3      RR     20130120     AP      700000.55
 4      ST     20130320     TN      800000.55
How ever we can also use NODUPS; This will check the entire observation and filter out only if entire observation is duplicate.

Limiting the number of observations in SAS
Suppose You have a input of million records , but you want to proceed or test your code with 10 records. In this scenario, it is advisable to use OBS=number-of-records  along with the infile statement. This will restrict the number of records to 10
DATA RYAN;       
  INFILE POLIN OBS=10 ;
....

Using a simple IF loop and THEN OUTPUT in SAS
lets add the below line before we print the SAS data using PROC PRINT.
IF(( CITY NE 'RR') OR (CITY='SS')) THEN OUTPUT;
PROC PRINT DATA=RYAN;
It wil print all the records except the observation with city value of RR.

Next,Lets do some more modifications to make the output dataset more meaningful:
Say, we want to add one more column which will tell us the city name in full, ie for the observation where the CITY is MI, it should say MICHIGAN.
 So we need to add one more variable which will be added in the output. 
INFILE POLIN;
  INPUT @01 CITY   $CHAR02.
        @06 DATE   $CHAR08.
        @14 STATE  $CHAR02.
        @16 AMT    COMMA9.2;
LENGTH CITY_NAME $20;
IF CITY = 'RR' THEN CITY_NAME ='ROCK VINE' ;
ELSE                                        
IF CITY = 'MI' THEN CITY_NAME ='MICHIGAN' ; 
ELSE                                        
IF CITY = 'CA' THEN CITY_NAME ='CALIFORNIA'; 
     PROC PRINT DATA= RYAN;
       VAR CITY DATE STATE AMT CITY_NAME; 
Here goes the output:

CITY      DATE      STATE       AMT       CITY_NAME
 CA     20130320     WB      100000.55    CALIFORNI
 CA     20130320     WB      200000.55    CALIFORNI
 MI     20130120     KA      300000.55    MICHIGAN 
 MI     20130320     AP      400000.55    MICHIGAN 
 CA     20130120     TN      500000.55    CALIFORNI
 CA     20130320     KA      600000.55    CALIFORNI
 RR     20130120     AP      700000.55    ROCK VINE
 ST     20130320     TN      800000.55             

Why did we add the line LENGTH CITY_NAME $20; ???. Ideally our job would have run without that part also, but the City name would have been truncated. Using that line we are making sure it takes 20 bytes and no truncation happens.!
To be continued.....  SAS in Mainframes(z/Os)  - Part 2  !! drop a note if u liked it.

DB2 Performance Issues and Tuning - part 1

There are many reasons as to why DB2 might perform poorly. Some of the most obvious reasons are
1.Inadequate Index : There might be non-matching index as compared to the select statement in query.Explained below ,how placing proper columns in index avoids DB2 sort when ORDER BY clause is present. Later we will check here how to improve index effectively to optimize query.
2.Lock Waits : Application might be waiting for  long time before acquiring lock on a resource which is currently being held by other processes
3. Wrong Clustering Sequence:  A wrong index was chosen as a clustering sequence.As a result there are unnecessary sorts in application.
4.Environmental issues: The size of buffer pool, Disk cache,and others can also impact the query response time.
5. Runstats Out-of-Date:  Runstats generally gathers information about the table space,partitions,index, index spaces.We need these in designing the databases.These information should be updated so as to gain efficient usage of the DB2 resources while designing a new database.Optimizer uses this information in building efficient access path.

There can be many more reasons for poor DB2 performance.Lets see how DB2 performance tuning can be done step by step with simple examples.

1.Effective Index can upgrade the Query response time:

Suppose we have one table CUST having the columns CUSTNO, FNAME,LNAME,CITY.  We  defined index(X1) on the column CITY.
We run a simple query as below:
SELECT LNAME,CUSTNO from CUST 
where FNAME=:FNAME 
            and CITY=:CITY 
order by LNAME.

While executing this, DB2 first performs a SORT and materialize the whole query when the cursor is OPENED because ORDER BY clause is present with LNAME.This sort operation because of the 'order by' clause can take long time when data is huge.
To prevent DB2 from sorting the data, what we can do is declare a new index X2 on the columns CITY,LNAME or modify the current index and add the column LNAME to it, since cursor contains 'ORDER BY LNAME'. Now Optimizer will see the data in requested order without a sort.
So now,DB2 will materialize the query during the FETCH time, ie the Disk I/O s will take place during the fetch time  and not during Open time because results will be in requested order without a sort.

So,How long does it take to run a SQL Statement? How is the response time calculated?
There are many components that contribute to the response time like network time,Disk IO time,cpu time, lock wait etc....
The major components that constitute the Local Response time(LRT)  are the the time spent on performing the IO operations(from disk, disk cache, buffer pool ), processing time in CPU, and wait times.(certain other items like package loading, authorization are insignificant)
LRT can be calculated with VQUBE2 method.(Very quick upper bound estimate v2). We will check that in other posts.
So here we see that there are two indexes.Now the optimizer can choose one index and fix the access path. We can view which Access path is chosen by optimizer with the SQL EXPLAIN.

So,How to use SQL EXPLAIN and PLAN_TABLE to view the access path chosen by DB2?
We need to wrap the SQL EXPLAIN statement around the targeted SQL Query. The Optimizer  writes the access path to the PLAN_TABLE. This PLAN_TABLE is a shared table, or every user can have their own schema of the plan table.
Some of the important columns in the PLAN_TABLE which provides us the desired information are
1.QUERYNO
2.ACCESSTYPE
3.MATCHCOLS
4.ACCESSNAME
5.INDEXONLY
6.SORTx_ORDERBY
7.PREFETCH

Step 1. The SQL statement in blue is the main Query for which we want to know the access path.
So we wrap the statement with the 'EXPLAIN ALL SET QUERYNO = 1 FOR'  like below

EXPLAIN ALL SET QUERYNO = 1 FOR
SELECT CUSTNO, CUSTLNAME                           
FROM CUST                                                            
WHERE CUSTNO LIKE '%0A';
Once we execute the above query, optimizer first writes the access path onto the Plan Table and then gives the output. Step below depicts how we can get the information from plan table

Step 2. Below statement queries the plan table using the QUERYNO used above in step1.

SELECT QUERYNO,METHOD,ACCESSTYPE,MATCHCOLS,
ACCESSNAME,INDEXONLY,PREFETCH
FROM PLAN_TABLE
WHERE QUERYNO = 1;


Brief explanation of the output columns of the the PLAN Table with its possible values: 
QUERYNO:  Gives the Queryno which we set in step1
ACCESSTYPE:  This is of much importance. Value of  'I' indicates data fetching is done by using an Index.Value of 'R' indicates Table scan or Range Scan.
MATCHCOLS :  It indicates the number of matching columns in the index.
ACCESSNAME:  Indicates the Name of the index if index access is selected.
INDEXONLY: Can have value of 'Y' or 'N'.  Value of 'Y' indicates that all data requested in the select query is present in index.  Value of 'N' indicates table access is also needed.
PREFETCH: Value of 'D' is Dynamic prefetch. 'S' is sequential prefetch.

Continue reading the below links if this interests u..!
2. Db2 Prefetch
3.working towards a better index 

OUTREC BUILD and OUTREC OVERLAY in SORT

We sometimes tend to get confused between the BUILD and OVERLAY of OUTREC statement.
Lets see  how each works and figure out when to use BUILD and OUTREC in SORT.
Scenario 1.
Input file is same as we used before

---+----1----+----2----+----3----+
************************  
HARLEY   123456MEXICO   
DAVID    658999CANADA    
*********************** 

We want to add date to the records from 35th position using BUILD.Sort syntax to do it with use of OUTREC BUILD would be:

SORT FIELDS=COPY                         
OUTREC BUILD=(1:1,32,35:&DATE)    

Output:

----+----1----+----2----+----3----+-          
*********************************
HARLEY   123456MEXICO   03/02/14
DAVID    658999CANADA   03/02/14 
********************************  

However,we can get the same output by use of OVERLAY in OUTREC as well like below:

SORT FIELDS=COPY                   
OUTREC OVERLAY=(35:&DATE)

Thus we can see, while using BUILD, we had to build the output records specifying the starting position,and records needed in output by using the syntax '1:1,32' and then specify '35:&date' which puts the date into 35th position.

However, OVERLAY reduced the coding effort.We specified only where the change needs to be done, keeping the entire record structure same.This becomes very handy when handling large and complex sort conditions since it eliminates the need to build the output record by record.

Editing Numeric Fields in DFSORT/SYNCSORT

EDIT Feature of OUTREC in DFSORT/SYNCSORT gives us great flexibility to mask and represent the data in our very own format.We can play around with PD or ZD data format.We can insert commas,hyphens,slashes,signs with this edit feature. I have tried out some functions randomly.

Suppose this is how the input looks like with the file structure like NAME(14 bytes) ,SALARY(6 bytes),COUNTRY(8bytes)

1. Lets put in some commas(,) in the salary part after first 4 digits.

//STEP02   EXEC PGM=SORT
//SORTIN   DD DSN=R0XXC.TEST.SORTIN,DISP=SHR 
//SORTOUT  DD DSN=R0XXC.TEST.SORT.OUT1,
//            DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,
//            DCB=*.SORTIN,
//            SPACE=(TRK,(100,200),RLSE)
//SYSOUT   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
      SORT FIELDS=COPY
      OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,EDIT=(IIII,II))
/*
Output:

Similarly the Below SYSIN Card will put the comma after first 2 bytes.
OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,EDIT=(II,IIII))

2 .Now , lets put a dollar sign '$' using EDIT in Outrec
we need to use the below statements.

//SYSIN    DD *
 SORT FIELDS=COPY
OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,EDIT=($II,IIII))
/*
Output:

3. Include the Decimal point using EDIT.
We need to use the below SYSIN card to place a decimal point using EDIT.
OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,EDIT=(IIII.II)) 
Output:
HARLEY    1234.56
DAVID       6589.99

4. Include a positive sign ('+') before the digits using EDIT.
OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,EDIT=(SIIII.II),SIGNS=(+))
Output:
HARLEY   +1234.56
DAVID      +6589.99

Note: There are  for 27 predefined Edit masks available;  M0 -M26. They can be straight way used in our code instead of coding 'EDIT= ' parameter. Like we can write the control card like:

OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,M6)   instead of writing
OUTFIL  OUTREC=(1:1,6,11:10,6,ZD,EDIT=(III-TTT-TTTT))
Both giving the same output result:
HARLEY        012-3456
DAVID           065-8999

Explanation: 
'I' is used to display digits (1-9) and blanks for leading zeros.
'T' is used to display digits (0-9)
'S' indicates Sign which can be leading or Trailing.

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.