Thursday, 14 November 2019

Top 15 Useful Hack Tips On JOINKEYS With Examples!

In your day to day task, you might have come across a situation, where you need to produce some statistics of matched or non-matched records from two separate files based on keys fields. If you are dealing with RDBMS then this task would be pretty simple, you could have written an SQL statement to generate desired statistics. 

But eventually, this task is not simple if you are dealing with flat files. In such a situation either you write some Easytrieve or COBOL program or use SORT JOINKEYS. JOINKEYS in SORT JCL is usually preferred option despite writing a program. 

In laymen's terms, the term JOIN is used to combining rows/columns from two different files based on keys fields, for example, you want to get a list of the customer from two separate files based on key i.e. employee_no. Also, you can use various subparameters like REFORMAT, OMITS, INCLUDE/COPY/SORT, etc. along with usual join operations. 

In today's tutorial, you'll learn the application of JOINKEYS in SORT JCLs. You'll also get practical exposure to various real-time SORT JOINKEY example for better understanding. Also, you'll learn top hack tips and tricks associated with SORT JOINKEYS.   

What is JOINKEYS?

JOINKEYS is one of the most popular features of DFSORT/MFX. It enables the programmers to perform various join operations on two files. If you don't have a facility of SORT JOINKEYS in your shop then you need to write an Easytrive or a COBOL program to get desired results. Writing a simple program for the small day to day tasks is a tedious job.  

Hence, JOINKEYS feature joins transactions/records from two different files based on certain keys (i.e. fields with common information). The SORTJNF1 and SORTJNF2 DD statements were used to specify these files. 

By default, when the JOINKEYS operation is applied to two files having M and N records respectively, then records are joined using the reformat clause, producing M*N records as input to subsequent DFSORT/MFX SORT processing. This type of JOIN is called an INNER JOIN.  

How SORT JOINKEYS process work? 

The feature of JOINKEYS in sort JCL is designed to improve the productivity of the programmer by reducing the time of designing, testing and debugging applications. The SORT JOINKEYS is an extensive data processing utility. 

The join operation is controlled by three important control statements i.e. JOINKEYS, JOIN, and REFORMAT. When you apply a join operation on two files, each record from the first file (SORTJNF1) will be joined with the second file (SORTJNF2) based on key values. Thus, if m records from the left side have a given join key value, and n from the right side have the same join key value, the join results in m*n records with that join key value. 

JOINKEYS process flow diagram is a pictorial representation of the processing performed for a JOINKEYS application.

SORT JOINKEYS
JOINKEYS Process Flow Diagram.


There are primarily four basic steps involved in any join operations. 
  1. Use the REFORMAT control statement to specify the name of fields, that need to be included in the joined record. 
  2. Selecting or dropping records either from both files or from either of the file by using the INCLUDE/OMIT parameter of the JOINKEYS statement.
  3. Specify whether the input join data is already sorted as per JOINKEYS control fields by using SORTED parameters. The overall performance is of joinkeys is better if join input data is already sorted as per JOINKEYS fields. 
  4. Use JOIN statement to specify the nature of joins i.e. (Inner join, left outer join, right outer join, and full outer join)   

JOINKEYS Syntax. 

A SORT JOINKEYS join operation required two JOINKEYS statements: One for the first file i.e. F1 and other for the second file i.e. F2. Refer below pictorial representation for more details.  

SORT in JOINKEYS
JOINKEYS Statement

JOINKEYS Sample JCL.

Here is the sample JCL with the control statements for a simple inner join key application using SORT JOINKEYS.

//PRXXA37X JOB (99999),'MAINFRAME FORUM',CLASS=A,MSGCLASS=X, 
//              MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*
//*   SAMPLE JCL FOR JOINKEYS.
//* 
//STEP01  EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTJNF1 DD DSN=MXXP.EMP.PAY.DT001,DISP=SHR
//SORTJNF2 DD DSN=MXXP.EMP.PAY.DT002,DISP=SHR
//SORTOUT  DD SYSOUT=*
//SYSIN DD *
* Control statements for JOINKEYS operation.
  JOINKEYS FILE= F1,FIELDS=(10,2,A,17,4,A)
  JOINKEYS FILE= F2,FIELDS=(20,2,A,23,4,A)
  REFORMAT FIELDS= (F2:1,80,F1:1,80)
* Control statements for main-task (joined records)
SORT FIELDS=COPY
/*

JOIN Statement. 

JOIN control statement in SORT JOINKEYS is an important parameter and if you don't specify this parameter then by default, only paired records from both F1 and F2 files are processed by the main task as joined transaction records. This type of join operation is called an INNER JOIN.

You can use the join statement to specify which records need to be included and processed by the main task. Also, you must specify the UNPAIRED operand. However, F1, F2 and ONLY are optional parameters. 

SORT JOINKEYS
JOIN Statement in JOINKEYS
When you specify the JOIN operands, then SORT JOINKEYS operation will retain the joined records and these records will be processed by the main task as follows:

  • UNPAIRED,F1,F2 or UNPAIRED: Retain unpaired records from both F1 & F2 files along with paired records. This type of join is called as FULL OUTER JOIN.
  • UNPAIRED,F1: Retain unpaired records from the F1 file along with paired records. This type of join is called a LEFT OUTER JOIN.
  • UNPAIRED,F2: Retain unpaired records from the F2 file along with paired records. This type of join is called RIGHT OUTER JOIN.
  • UNPAIRED,F1,F2,ONLY or UNPAIRED,ONLY: Retain unpaired records from F1 and F2 files. 
  • UNPAIRED,F1,ONLY: Retain unpaired records from F1 file. 
  • UNPAIRED,F2,ONLY: Retain unpaired records from F2 file. 

JOINKEYS: SORTED/NOSEQCK parameters.

By default, DFSORT/MFX will sort the input files based on the specified keys. If you know the input file records are already sorted order, you can use the SORTED parameter to tell DFSORT/MFX to copy file records despite sorting them again. Also, use NOSEQCH operand to tell DFSORT not to check for the order of the records. For example, if you specify:

JOINKEYS FILE=F1,FIELDS=(22,3,A),SORTED,NOSEQCH 
JOINKEYS FILE=F2,FIELDS=(15,3,A),SORTED

JOINKEYS: TYPE parameter.
Another important parameter is TYPE, which is used to specify the processing length for a VSAM input file. For example, if you specify :


  • TYPE=V: DFSORT would use variable-length processing for the VSAM file. 
  • TYPE=F: DFSORT would use fixed-length processing for the VSAM file.

JOINKEYS F1=VSAM1,FIELDS=(23,6,A),TYPE=V
JOINKEYS F2=VSAM2,FIELDS=(16,6,A),TYPE=F

JOINKEYS: INLUCDE/OMIT Parameters.

The performance of SORT JOINKEYS can be improved by using two key parameters i.e. INCLUDE/OMIT. These two parameters are widely used either to included or exclude records from the file during the join operations.
JOINKEYS OMIT
JOINKEYS INCLUDE/OMIT Parameters.
Both parameters can be specified at JOIN statement of JONKEYS. But you should always specify INCLUDE/OMIT parameter in JNF1CNTL or JNF2CNTL. For example:

//*
//* INCLUDE/OMIT Specified at JOIN statements.
//*  
  JOINKEYS FILE=F1,FIELDS=(35,8,A),
     OMIT=(10,15,CH,EQ,C'MAINFRAME FORUM')
  JOINKEYS FILE=F2,FIELDS=(37,8,A),
     INCLUDE=(1,20,SS,EQ,C'TRAN')

//*
//* INLCLUDE/OMIT specified at JFN1CNTL/JNF2CNTL 
//*
//SYSIN DD *
  JOINKEYS FILE=F1,FIELDS=(35,8,A)
  JOINKEYS FILE=F2,FIELDS=(37,8,A)
...
//JNF1CNTL DD *
  OMIT COND=(10,15,CH,EQ,C'MAINFRAME FORUM')
//JNF2CNTL DD *
  INCLUDE COND=(1,20,SS,EQ,C'TRAN')

 Example 1: Generate output report with paired transaction records from both F1/F2 without duplicates.

//PRXXA37X JOB (99999),'MAINFRAME FORUM',CLASS=A,MSGCLASS=X, 
//              MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*
//*   SAMPLE JCL FOR JOINKEYS.
//* 
//STEP01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
  00100       ROGGER    $1000.00
  00200       MURPHY    $2000.00
  00300       CIARA     $3000.00
/*
//SORTJNF2 DD *
  00100       ROGGER    $1000.00  JAN
  00200       MURPHY    $2000.00  JAN
  00300       CIARA     $3000.00  JAN
  00300       CIARA     $3000.00  JAN
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
* Control statements for JOINKEYS application
  JOINKEYS FILE=F1,FIELDS=(1,05,A)
  JOINKEYS FILE=F2,FIELDS=(1,05,A)
  REFORMAT FIELDS=(F1:1,5,13,10,F2:33,03,F1:23,8)
* Control statements for main task (joined records)
  OPTION COPY
  OUTFIL REMOVECC,
  HEADER2=(1:'EMPID',11:'F-NAME',26:'EP-SAL',36:'SL-MNTH',/,
  1:7'-',11:14'-',26:7'-',36:7'-'),
  BUILD=(1:1,5,11:13,10,26:23,08,36:33,03)
/*

Output. 

EMPID     F-NAME         EP-SAL   SL-MNTH
-------   -------------- -------  -------
00100     ROGGER         $1000.00 JAN
00200     MURPHY         $2000.00 JAN 
00300     CIARA          $3000.00 JAN

SUMMARY.

DFSORT/MFX are powerful data manipulation tools available on the mainframe platform. These tools have the ability to sort, merge, copy, generate the report for business analysis. JOINKEYS in SORT JCL supports a various number of feature such as JOINKEYS, JOIN, REFORMAT, JKFROM, TOJUL, TOGREG, WEEKDAY, DT, DTNS, MERGE operator, MERGEIN ect.

Sunday, 30 June 2019

SEARCH in COBOL.| COBOL SEARCH | SEARCH ALL.

COBOL SEARCH
SEARCH ALL 



In today's tutorial, you will learn how to search the array/tables in COBOL. This tutorial examines the operation of the SEARCH and SEARCH ALL verbs. The term SEARCH in COBOL is used for linear searches, and SEARCH ALL in COBOL is used for binary searches. Also, you will learn mandatory entries for Data Division and Procedure Division.

Tables are extremely important in COBOL, as they are in any other computer language. You can refer Master ARRAY In COBOL In Just A Few Minutes! if you are not familiar with declaring an array in COBOL. So, let's start with the tutorial.
What is a search in COBOL?

In COBOL, or in any other programming language the term search is used to find specific value in an array/table. The task of searching a table/array to determine whether it contains a particular value is a common operation. The method used to search a table depends heavily on the way the values are organized in the table/array.

What is a linear search in COBOL?


In COBOL, If you define a one dimension array and values are not ordered, then the only strategy available is a linear search. A linear search starts at the first element and then examines each succeeding element until the item is found or until the end of the table is reached (an item not found).

Linear Search Syntax.

SEARCH identifier [VARYING {identifier-2/index-name}]
[AT END statement]
[WHEN condition statements]
[END-SEARCH]

Note: The SEARCH statement does not initialize the search index. You must do this with a SET statement (described earlier in the chapter) or by using some other programmatic way to ensure that it has a valid starting value before you start the search.

What is a binary search in COBOL?

In COBOL, A binary search works by dividing the table in half and determining whether the item sought is in the top half of the table or the bottom half. This process continues until the item is found or it is determined that the item is not in the table.
COBOL has special verbs that let you search for tables using either strategy. The SEARCH verb is used for linear searches, and the SEARCH ALL verb is used for binary searches.


SEARCH ALL Syntax.


SEARCH ALL identifier-1
AT END imperative statement-1
WHEN equal-condition-1 and equal-condition-2
{imperative statement-2}
{NEXT SENTENCE}
END-SEARCH

Note: In COBOL, If the values are ordered, then you have the option of using either a linear search or a binary search.

COBOL SEARCH VS COBOL SEARCH ALL


One advantage of using SEARCH or SEARCH ALL rather than a handcrafted search is that because these are specialized instructions, their operation can be optimized. Part of that optimization involves creating a special subscript to be used when searching the table. You create this special subscript using an extension to the OCCURS clause called the INDEXED BY clause.

INDEXED BY Clause


Before you can use SEARCH or SEARCH ALL to search a table, you must define the table as having an index item associated with it. Using an index makes searching more efficient. Because the index is linked to a particular table, the compiler—taking into account the size of the table—can choose the most efficient representation possible for the index. This speeds up the search.

Using SET to Manipulate the Table Index


A table index is a special data item. It has no PICTURE clause, it is associated with a particular table, and the compiler defines the index using the most computationally efficient representation possible. Because of its special binary representation, the table index cannot be displayed and can only be assigned a value, or have its value assigned, by the SET verb. Similarly, the SET verb must be used to increment or decrement the value of an index item.

SEARCH and SEARCH ALL in COBOL with example.
SEQUENTIAL SEARCH in COBOL example

Here is an example of using this format of the SEARCH statement:

SET PRICE-TABLE-INDEX TO 1.
MOVE "N" TO PTABLE-EOF-SWITCH.
PERFORM UNTIL PTABLE-EOF
  SEARCH PRICE-GROUP
    AT END
       MOVE "Y" TO PTABLE-EOF-SWITCH
    WHEN ITEM-PRICE (PRICE-TABLE-INDEX) = ITEM-PRICE
       DISPLAY "ITEM NUMBER: " ITEM-NUMBER (PRICE-TABLE-INDEX)
  END-SEARCH
  SET PRICE-TABLE-INDEX UP BY 1
END-PERFORM.

SEARCH ALL IN COBOL with example.


Here is an example of using this format of the SEARCH statement:

01 STATE-SALES-TAX.
  03 TAX-TABLE OCCURS 50 TIMES INDEXED BY STATE-INDEX
     ASCENDING KEY IS STATE.
     05 STATE   PIC XX.
     05 TAX    PIC 99V999.

01 RECORD-X.
  03 NAME PIC X(20)
  …
  03 STATE PIC XX.

  SEARCH ALL TAX-TABLE
    AT END
      DISPLAY “No Such State”
      GO TO BAD-STATE
  WHEN STATE OF TAX-TABLE(STATE-INDEX) = STATE OF RECORD-X
    CONTINUE
  END-SEARCH.

* At this point, STATE-INDEX points to the table entry for this
* state
  MOVE TAX(STATE-INDEX) TO …

Conclusion.

Since tables are commonly used in business programs, all COBOL programmers need to be adept at using them. Although you can use either subscripts or indexes to work with tables, the use of indexes generally leads to more efficient processing and code that’s easier to understand. When you use indexes, you can also perform binary searches when the table entries are sorted by the key field. This can dramatically improve searching efficiency in a large table. For these reasons, you should use indexes for all but the simplest tables.

Friday, 24 May 2019

REST APIs, SOAP vs REST APIs, REST APIs Smackdown!

rest apis example
REST APIs Tutorial

APIs are a trending topic, energetically debated by business-people, IT managers, and developers alike. In this tutorial, I'll describe the REST APIs (Application Programming Interface). So let's look at an overview of REST API. 


What is REST APIs?


REST stands for REpresentational State Transfer APIs. REST APIs are an architectural style defined to help create and organize distributed systems. REST, however, is the newcomer on the block and for web service access protocols. REST APIs objective is to provide a truly simple, lightweight method for accessing web services.

History of REST APIs.


Roy Fielding an American computer scientist has made tremendous contributions to the IT Industry, especially regarding the Internet, but according to my perception, his research thesis is the thing that received the most attention and highlights his name among a lot of people who otherwise wouldn't have heard of him.

He was born in 1965 and he is one of the main authors of the HTTP protocol, the protocol that the entire Web infrastructure is based on. 

In the year 2000, Fielding presented his doctoral dissertation, Architectural Styles and the Design of Network-based Software Architecture. In its, he coined the term REST, an architectural style for distributed hypermedia systems.

REST APIs Tasks.


Let's look at some REST API tasks. The most REST (Representational State Transfer) services or most web services that are using REST APIs rely exclusively on obtaining the needed information using a URL. Therefore, REST APIs can use four different HTTP verbs to perform tasks.

  1. GET, which is used to retrieve it.
  2. POST, which can be used to create. 
  3. PUT, which can be used to update. 
  4. DELETE, which is used to delete. 


JSON
REST APIs Architecture 

JSON stands for JavaScript Object Notation. JSON is a simple and language-independent way of formatting programming language data structures (numbers, arrays, hashes, and so on) as strings. JSON makes a lightweight alternative to XML-based approaches to data serialization, like XML Schema.

Example of JSON Feed. 

{
    "id": 10,
    "name": "A Black Table",
    "price": 42.80,
    "tags": ["Office", "Garden"]
}
-

SOAP vs REST.


The Simple Object Access Protocol or SOAP and Representational State Transfer or REST are two answers to essentially the same question. 
  • SOAP is a standards-based web services protocol that's been around for a while. And it enjoys all the benefits of long-term use. It was originally developed by Microsoft. And it's not as simple as it may sound. 
  • REST, however, is the newcomer on the block and for web service access protocols. And it aims to address SOAP's shortcomings. Its aim is really to provide a truly simple, lightweight method for accessing web services.
Many developers found working with SOAP cumbersome and hard to use. 

For example, working with SOAP in JavaScript meant writing a ton of code to perform extremely simple tasks because you'd have to create the required XML structure absolutely every time. 

So to simplify that, REST relies on a simple URL, in most cases, as opposed to any kind of complex XML structuring. 

So unlike SOAP, REST doesn't have to use XML to provide the response. So you can find REST-based services that output the data in command-separated values or CSVs, JavaScript Object Notation, JSON, or in RSS, Really Simple Syndication. So the point is that you can obtain the output you need in a form that's easy to parse within the language you need for your application using REST.

REST API Advantages over SOAP.


Now, let's look at REST's API advantages over SOAP.
  • REST is easier to use for the most part and it's more flexible. It's got some advantages. So it's designed closer to other web technologies in terms of their philosophy. 
  • REST APIs don't require expensive tools to interact with the web service. It's just a URL. It's easier to learn. It has a smaller learning curve. And it's fast because there's no excessive programming or processing required. 
  • It's also more efficient because it uses smaller message formats in comparison to SOAP XML, which has got bloated XML message formats. So that's REST APIs.

Sunday, 19 May 2019

SORT INCLUDE and OMIT Statements | JCL INCLUDE | JCL OMIT Example.

OMIT JCL
SORT INCLUDE and OMIT statements

In today's tutorial, I will discuss SORT INCLUDE and OMIT control statement. These are very important control statements that are used to improve the efficiency of a SORT job. So, let's start with our tutorial.   

What is JCL INCLUDE and OMIT Statements? 

Every day millions of jobs run on the mainframe to SORT or MERGE data from different files. These jobs use various control statements as per requirements.  INCLUDE and OMIT are two such parameters that are used to improve SORT efficiency and these parameters provide some more processing capabilities. 

The SORT INCLUDE and SORT OMIT statements is used to identify the records that should be included or omitted from the sort or merge process.

The syntax of the INCLUDE and OMIT statements 

INCLUDE COND=(field,comparison,{field   }, [ {AND}, ]…)
                               {constant}    {OR }
OMIT    COND=(field,comparison,{field   }, [ {AND}, ]…)
                               {constant}    {OR }

Important Points: 

  • field:: A field to be used in a comparison. The field is specified as in the SORT or MERGE statement: position, length, format.
  • comparison:: One of these comparison operators:
                 EQ :- Equal        GE Greater than or equal to
                NE :- Not equal LT Less than
               GT :- Greater than LE Less than or equal to
  • constant:: A literal value in one of these formats:
             Decimal         5               +104               -39
            Character      C’CA’      C’JONES’      C’X24'
            Hexadecimal    X’00'       X’F9F9'         X’40404040'
  • AND  and OR :: Operator will be used for include additional conditions.
INCLUDE Example: Include only the records that have an A in byte 1.

//RCTX021D JOB (JOB CARD.....)
//STEP010  EXEC PGM=SORT      
//SYSOUT   DD SYSOUT=*             
//SORTIN   DD DSN=RC01.PAYROL.FILE,DISP=SHR 
//SORTOUT  DD SYSOUT=* 
//SYSIN DD *
  INCLUDE COND=(1,1,CH,EQ,C'A'),
  SORT FIELDS=(1,14,CH,A)
/* 


Include and Omit Examples.


Example 1: INCLUDE only the records with a packed-decimal value in bytes 72-76 that’s greater than or equal to 1000.

INCLUDE COND=(72,5,PD,GE,1000)

Example 2: INCLUDE only the records with a packed-decimal value in bytes 72-76 that’s greater than or equal to 1000 or less than 10.

INCLUDE COND=(72,5,PD,GE,1000,OR,72,5,PD,LT,10)

Example 3: OMIT the records that have an A in byte 1.

OMIT COND=(1,1,CH,EQ,C'A')

Sunday, 12 May 2019

The Ultimate Secret Of APPLICATION PROGRAMMING INTERFACE, APIs.

Application Programming Interface APIs

Welcome back to my blog. Today I'll be discussing APIs. APIs stands for Application Programming Interface. APIs are the most trending topic in the industry. This tutorial will cover various aspects of the Application Programming Interface. So, let's start with the tutorial.

What is an API?


In the Layman term, API is the abbreviation used for  Application Programming Interface. APIs are the set of protocols, micro-services, web services that enable two applications to communicate and exchange data with each other. 

You would be amazed to know that every day a million users across the globe make APIs call to get their job done. Every-time you use mobile apps like Twitter, Facebook you're actually calling an API in the background. 

API
Application Programming Interface APIs


Categories of Application Programming Interface [API].


APIs selection is a critical task. Good API would definitely make a difference to the business growth. But to understand a good API you need to understand the uniqueness of the business.  Application Programming Interface (APIs) can be categories into two categories i.e.

  • Interaction APIs
  • System APIs

Why are APIs so popular these days?


Application Programming Interfaces (APIs) are the building block of the cloud, mobile platforms and the Internet of Things revolution. There is tremendous growth in openly available APIs. As per ProgrammableWeb statistics, there are over 17,000 public APIs in production. There is two import factor behind API popularity: 
  • The ease of access and portability of data exchanged through APIs are the key reasons of popularity. The default data exchange format in an API is JSON (JavaScript Serial Object Notation).
  • Mobile application deeply depends on APIs to get data to and from the device to back-end systems. Thus the number of applications (apps) in the app stores grew, the number of APIs needed to support those apps also grew.

What do you think? Are APIs Considered Products or a Service?


The application programming interface is like any other product or software. But the IT professional has a different opinion. Some think APIs are a term while others think APIs as a product. The term API is smarty crafted term so that it should have wide acceptability among developer and big clients.

A modern API represents a package of capabilities that's attractive to an audience independent of any specific piece of software running in your back end.

What Is the API Economy?


Giant IT companies turn application programming interface (API) into a money-making machine. API economy emerges when APIs become part of the business model. Public and partner APIs have been strategic enablers for several online business models.

For example, companies like Google, Microsoft, IBM, Twitter, Facebook provide a number of APIs that can be utilized by any user at a nominal cost. WordsAPI-API is one of the most popular APIs that let you fetch information about an English word.

Top 5 trending publicly available APIs.

  1. Facebook API.
  2. Google API.
  3. Twitter API
  4. Youtube API
  5. Microsoft API    

Is it possible to use APIs in Legacy Mainframe Application?  


Yes, the big enterprise that deeply relies on mainframe applications can expose their legacy application with the help of APIs. IBM has done an excellent modernizing z/OS and I must mainframe is still giving a tough fight to new technology.

IBM API Connect is a perfect example of mainframe modernization. It offers the base for building, safeguarding, supervise, communicate, and consuming APIs to support digital applications.


What is the REST APIs?