Wednesday, 16 December 2020

What is SQL and why you need SQL? | Type of SQL | Introduction to SQL | Structured Query Language (SQL) Example - Quick Guide.

SQL stands for Structured Query Language.

SQL - Structured Query Language. 

COBOL+DB2 application program uses SQL statements. These SQL statements are used to select, insert, update, and delete data from the DB2 tables. These SQL statements must be embedded into the host language programs such as COBOL, JAVA, or REXX.

Welcome to today's session on "Structure Query Language (SQL)". In this session, discover what is SQL statements and why you need them in your COBOL+DB2 application programs. Learn why you need DB2 pre-compilation for COBOL+DB2 programs. So, without wasting any time let's get started with today's SQL tutorial. 

 Agenda. 

  • What is Data and why it is important?
  • What is SQL and why you need it?
  • Features of SQL ( ie. Structure Query Language).
  • What is the difference between SQL and Host language? 
  • What are the different types of SQL statements?
  • SQL statements examples. 


What is Data and why it is important?

Data is a collection of facts, that computers can understand and process. So, when you book your flight or pay utility bills, or share photographs on the social media platform. 

Your action generates tons of data. Different storage mediums such as databases, file systems, etc. store generated data for processing. Large scale enterprise applications generally use the database to store this valuable information. 

Proper storage and analysis of data not only improve the process but also boost the profit. 

Now, the question is how to store and access data from the database. The answer is SQL or structured query language. 

An Overview of SQL.

The term SQL stands for "Structured Query Language". it's popularly known as "SQL" or "SEQUEL" or “ESS-CUE-EL”. SQL is a powerful data manipulation tool that is supported by all the leading RDBMS products such as DB2, MS SQL Server, Oracle, MYSQL, etc.

"SQL is the language that communicates with the database." 

The programmer uses SQL statements to specifies what data to be fetched or updated, but does not specifies how to perform the operation. DBMS parse and analyze the SQL statement to find the optimal path to retrieve data from the tables. 

Features of SQL.

SQL is not just a query language but it's the complete package in itself. The structured query language (SQL) provides all most all features of a high-level programming language such as JAVA, COBOL, REXX, C++, etc. In fact, users can easily manipulate data without writing a huge chunk of code. Following are a few salient features of SQL. 
  • SQL is simple and easy to use. 
  • SQL is flexible and portable. 
  • SQL statements are used to retrieve data instead of lengthy code. 
  • SQL is a free-form structure. 

What is the difference between SQL and Host language?

A high-level programming language such as COBOL, JAVA, REXX, etc. is designed to processing one record-at-a-time. However, SQL is designed to process a set of records at-a-time. In set-level processing, all operations applied on the set and the output is a set of records. But, sometimes the result set can be an empty or single row. 

Host languages such as COBOL, JAVA, REXX, etc. can not handle many records in a single request. Thus, in the COBOL-DB2 or JAVA-DB2 application, you need a DB2 cursor and host variables to process many records.

SQL, Structured query language
SQL vs Host language.

Let's talk about the different types of SQL statements. 

What are the different types of SQL statements?

There are no specific criteria for categorizing SQL statements. SQL statements are generally categorized into two categories.

    - SQL Functionality.
    - SQL Execution.

The two categorized further sub-divided into many categories. Let's look at each of them one by one. 

Based on the Functionality the SQL statements are of three types: 
  1. Data Control Language (DCL): Control the user access to data. SQL statements such as Grant/Revok are used to grant or revoke user access to the data. 
  2. Data Manipulation Language (DML): Maniulupate data stored in the database tables. SQL statements such as Select, Insert, Update, and Delete is used to perform various data manipulation operations. 
  3. Data Definition Language (DDL): Create data objects such as tables, views, etc. SQL statements such as Create table, Drop table is used to create database objects.

SQL Types, Type of SQL statements

Type of SQL Statements.



Based on the Execution the SQL statements are of two types:
  1. Static SQL or Embedded SQL statements: These SQL statements are hardcoded into application programs such as COBOL+DB2.  
  2. Dynamic SQL statements: These SQL statements are prepared by the application program during the run-time.    

SQL statements examples.


Following are some SQL statement examples. 





What is SQL and why you need SQL?

This SQL tutorial is linked to a youtube video for better understanding. Please do visit and consider subscribing to my channel. 


Conclusion. 

In this SQL tutorial, you'll why is SQL and why you need it. You also learn the basics of SQL, type of SQL statements based on functionality and execution. Followed by SQL examples for better understanding. 

►Subscribe to Topictrick & Don't forget to press THE BELL ICON to never miss any updates. Also, Please visit below mention the link below to stay connected with Topictrick and the Mainframe forum on - 

► Youtube
Facebook 
Reddit

Thank you for your support. 
Mainframe Forum™

Sunday, 1 November 2020

CA7 Command Reference - LQ Command example. (LQ,LREQ,LRDY,LACT,LQ,JOB=JOBNAME) | Mainframe job scheduler tools.

Mainframe job scheduler tool, CA 7 command reference
CA 7 Workload Automation.

Let's say you are working for a multinational Bank. The Bank infrastructure relies on the mainframe to host its core-banking application. They are using the CA 7 Workload scheduler to schedule, and run their daily batch jobs to processing the millions of transactions daily. 

Now, being an operator you should know how to use the LQ command to view the status of jobs in the queue. 

Ladies and gentlemen welcome back to another exciting tutorial on CA 7 commands. In this session, you'll learn LQ commands and their different formats. You'll get an overview of the CA7 screen and different types of queues in the CA 7 workload scheduler. So, without wasting any time, let's look at today's agenda. 

CA 7 Command Reference - Agenda. 

  1. CA7 Workload Automation Introduction. 
  2. LQ Command Overview. 
  3. LQ Command Format and variants.

CA 7 Workload Automation Introduction.

CA 7 Workload Automation is a Broadcom product that is used to schedule, and run jobs on the mainframe. It actually automates the daily batch workload from small to large enterprise applications. 

LQ Commands Overview.

The LQ command is very helpful in viewing the status and determining the current queue of the job (JCL). Generally, each batch job has to go through the following queues:
  1. Request Queue.
  2. Ready Queue.
  3. Active Queue. 
A job/JCL move to Request Queue and wait till all the requirements are completed. The job will only move to the Ready Queue only after the completion of all requirements. 

Finally, they are submitted to the initiators. When the jobs start executing they moved to Active Queue.

Important Point: CA 7 will show only details related to an activity that is active and know to CA 7 Scheduler.

LQ Command Format. 

The LQ Command has the following format: 

[LQ|LQUE}[,AGENT={*|agent|mask}]
         [,CLS=x]
         [,CPUID={*|smfid|mask}]
         [,DRCLASS={*|@ANY|@NONE|drclass|mask|}]
         [,DRMODE={*|NO|YES}]
         [,{JOB={*|jobname|jobnumber|mask}|JOBL={*|longjobname|mask}}]
         [,LIST=option]
         [,NODE={*|nodename|mask}]
         [,SCHID={0|nnn}]
         [,SEG={xxxxxxxx|(xxxxxxxx,yyyyyyyy)}]
         [,SEQ={NATV|sequence}]
         [,ST=option]
         [,SYS={*|systemname|mask}]
         [,TZ={CA7|EXEC}]

Now, to view the list of jobs in the request, ready and active queues, you need to type the LQ command on the top line of the CA 7 window and hit enter. Following is the screen print for your reference. 
 

CA 7 Command Reference, CA7 command reference cheatsheet
LQ Command Reference


Now, If you look at the screen above, there are many columns which are shown on the screen. Most of them are self-explanatory but I would like to explain a couple of them. 
  • Queue Name: This column shows the queue name where the job is resiting when you print the list of jobs by using the LQ command. The abbreviation RDY is for ready queue, REQ is for request queue, and ACT for the active queue.   
  • Entry Mode: This column, shows how the mainframe job got into the respective queue. It means how these jobs are triggered i.e. on-demand, ad-hoc request, or by some other jobs.  
  • JOB Status: Finally, the last column shows the current status of the job in that queue. 

LQ Command Examples

  • LQ,ST=LATE: This command is used to lists only late jobs. 
  • LQ,ST=RSTR: This command list all jobs that require a restart.
  • LREQ: This command lists the jobs in the request queue (i.e. waiting for requirement or restart or completed)
  • LRDY: This command list all jobs that are submitted to the CPU but are not yet active.
  • LACT:  This command lists the jobs that are currently executing on a CPU 
  • LQ,JOB=NAMED: This command display queue information of the job
  • LQ,JOB=NAMED,LIST=RQMT: This command list the requirement of the job is waiting on. 
  • LQ,JOB=JOBNAME,LIST=Q: This command shows if the JCL came from the override lib.

CA 7 Command Reference: LQ Command quick Guide. 

This tutorial is associated with a youtube video to clearly explain the LQ command and its formats. Please consider subscribing to our channel. 


Conclusion. 

LQ Command in CA7 is very useful in determining the current status of a job which is either waiting or active or completed. CA7 LQ has multiple variants, that provide enough flexibility to operators to view jobs as per requirements. You can use LQ, LRDY, LACT, LQ,JOB=JOBNAME, etc as per your need. CA7 workload automation is a powerful scheduling tool from Broadcom. Please do leave your feedback and don't forget to subscribe to our channel for more such videos.  

► Please visit below mention the link to stay connected with Topictrick on -

Topictrick Linkedin:                    
Reddit Group                    
Mainframe Blog:        

Saturday, 30 November 2019

IBM Watson: How To Make More Machine Learning For Z/OS!

In today's tutorial, I'll discuss the most trending topic of the IT Industry. I'm sure that you've already aware of the term. Also, you'll be using this technology in your day to day activities. Can you guess the topic name?

Well, your guess is correct, it's Machine learning and Artifical Intelligence. These days everyone is talking about machine learning and AI.

The majority of IT companies such as Microsft, Google, Apple, IBM, Amazon, etc. are investing a lot of money in the field of Machine Learning and AI. Also, they have built various AI platforms such as Azure, IBM Watson, AWS, etc.

You'll  AI platforms leverage the capabilities of cloud computing.

You might be wondering why I'm discussing cloud computing, machine learning, AI, IBM Watson in today's mainframe forum post.

Well, the answer is, you can leverage the capabilities of various Machine Learning models on IBM Z15 Mainframe with the help of IBM Watson for Z/OS.

Now, you know the contest of today's blog spot. Let's discuss the application of Machine Learning and AI. How various predictive models can be deployed into critical mainframe applications?

What Is Machine Learning?

In laymen term, Machine learning is a field of AI that enables a machine to learn from existing data. Machine learning uses a wide variety of algorithms to learn, and predict outcomes from the data.

Machine Learning is not a simple process, rather it is a complex and CPU intensive process. Machine Learning has its own limitations and challenges.

Machine Learning is applied in all most all areas whether is Banking and financial services, the medical sector, social media, etc.

What is IBM Watson?

IBM Watson is a cloud-based cognitive-computing platform being employed across a wide range of real-world scenarios. Cognitive-computing systems simulate the pattern-recognition and decision-making capabilities of the human brain to “learn” as they consume more data.

Watson’s broad range of web services and provide a hands-on Watson treatment, demonstrating many Watson capabilities. The table on the next page shows just a few of the ways in which organizations are using Watson.


IBM Watson: Machine Learning and AI for Z/OS.


What is IBM Watson Studio?

IBM Watson Studio is an integrated environment, that is designed to make it easy to build, train, and manage predictive models, as well as deploy AI-powered applications. You can use the neural network modeler and deep learning experiments in IBM Watson Studio to solve the most challenging and computationally intensive problems with clarity and ease.

Machine Learning.
IBM Watson Studio.


What are the benefits of IBM Watson Machine Learning to your business?

Well, building a Machine Learning predictive model is fairly simple then deploying predictive models into live production environments.  The task of deploying a machine learning predictive model into the legacy Mainframe Production environment is absolute a big challenge. But, surprising IBM was able to bridge this gap with IBM Watson. 


IBM Watson Machine Learning for IBM z/OS brings Artificial Intelligence to your most critical business applications on IBM Z. It offers an end-to-end machine learning platform that is used to generate predictive models along with core IBM Z qualities of service.   

Watson Studio Architecture and Layout.

IBM Watson is a cognitive system that allow a brand new partnership between the people and machines. It is the cognitive computing offering from IBM Watson.

IBM Watson combine the five core capabilities:  

  • IBM Watson allows natural interaction between people and machines, based on person's preference.
  • Rapidly ingests key business materials, partnering with specialists to scale and elevate expertise.
  • Qualify new products and services to sight, reason and learn about their users and the world around them.
  • Utilized data to improve existing business processes and forecasting. Also, increasing operational effectiveness.
  • Enrich exploration and discovery, by uncovering unique patterns from the data.

The architecture of IBM Watson is fairly straight forward and center around the concept of Projects. The project included various resources such as collaborators, data-sets and various analytics tools that would be used to discover pattern from underlying data. 


You can use cognitive computing features in your legacy application by using IBM Watson Language, Vision, Speech, and Data APIs. IBM Watson APIs (application service) are delivered via IBM Bluemix, which is the cloud platform as a service (PaaS) developed by IBM. The following IBM Watson APIs are currently available:
  • Language.
  • Speech.
  • Vision.
  • Data Insights.
Refer below snapshot of IBM Watson services in the IBM Bluemix catalog.


Machine Learning.
IBM Watson services in IBM Bluemix Catalog.


IBM Watson Machine Learning for Z/OS.

IBM Watson Machine Learning for z/OS allow you to build predictive machine learning models using your integrated development environment (IDE) and platform of choice and then quickly deploy scoring services within CICS transaction applications. You can also monitor machine learning model on IBM Z, while maintaining the service level agreements (SLAs). IBM Watson offers an end-to-end machine learning platform that readily utilized the power of predictive models while benefiting from core IBM Z qualities of service. 

IBM Watson machine learning for z/OS ensures the lowest latency and the highest performance, security and resiliency. IBM Watson Machine Learning for z/OS brings actionable, real-time insight to your online legacy transaction applications.

Following are salient feature of IBM Watson for Machine Learning for z/OS. 
  • Flexible model development.
  • Enhanced model accuracy.
  • Improved productivity.
  • Production-ready machine learning.
  • Enterprise-ready AI model deployment.
Fraud Detection Example:


Deploying machine learning into transnational applications

IBM Watson applied to industries, businesses, and science

Today, industries has huge potential to double their growth, if they analysed and discover hidden information from the available customer/transactions data. IBM Watson enable business to integrate new cognitive capabilities into their business applications. 

You would be surprised to know that more than 9 billion connected devices operate in the world today, and they generate 2.5 they are generating Quintilian bytes of new data daily. 

Making sense of data embedded in intelligent devices is creating a significant market opportunity that is expected to reach $1.7 trillion by 20201.

The following list describes areas where Watson is applied to solve real problems in several industries, businesses, and science:IBM Watson Health.
  • IBM Watson Commerce.
  • IBM Watson Education.
  • IBM Watson for Genomics.
  • IBM Watson for Oncology.
  • IBM Watson Care Manager.
  • IBM Watson Cognitive Video.
  • IBM Watson Internet of Things.
  • IBM Watson for Cyber Security.
  • IBM Watson for Drug Discovery.
  • IBM Watson Financial Services.
  • IBM Watson in the Insurance industry.
  • IBM Watson Health Patient Engagement.
Are you looking for more DEMO example? Click the below link: 

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 the 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.

//TMXXAXXX 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.

//TMXXXSDX 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.