WHENEVER Clause

WHENEVER specifies the host language statement to be executed when an exception condition occurs.

Three types of WHENEVER :-

1.WHENEVER NOT FOUND

Example:

go to the label ENDDATA for any statement does not return

EXEC SQL
     WHENEVER NOT FOUND
        GO TO ENDDATA
END-EXEC.

NOT FOUND => Identifies any condition that results in an SQLCODE of +100.

2.WHENEVER SQLERROR

Go to label handler for any statement that produces an error

EXEC SQL
     WHENEVER SQLERROR
     GO TO HANDLER
END-EXEC.

SQLERROR => Identifies any condition that results in negative SQLCODE.

3.WHENEVER SQLWARNING 

Continue the processing when any statement produces a warning

EXEC SQL
     WHENEVER SQLWARNING
     CONTINUE
END-EXEC.

SQLWARNING => Identifies any condition that results in warning condition (SQLWARN0 is W) or that results in Positive SQLCODE other than +100.

IEBCOMPR Utility

IEBCOMPR utility is used to

• Compare two PS (Physical Sequential) Datasets
• Compare two PDS (Partitioned Data Set)/PDSE (Partitioned Data Set Extended)

If we want to compare two PS files using this utility, both must have same record length. 

Ex. Comparing two PS files

//STEP1  EXEC PGM=IEBCOMPR
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DSN=PSFILE1,DISP=SHR
//SYSUT2 DD DSN=PSFILE2,DISP=SHR
//SYSIN   DD *
  COMPARE TYPORG=PS
/*

The two PS files which are to be compared must be given in SYSUT1 and SYSUT2

COMPARE TYPORG=PS specifies that the input data sets are PS files.

If they are PDS/PDSE files, then we should give COMPARE TYPORG=PO

Ex. Comparing two PDS files

//STEP1  EXEC PGM=IEBCOMPR
//SYSPRINT DD SYSOUT=*
//SYSUT1 DD DSN=PDSFILE1,DISP=SHR
//SYSUT2 DD DSN=PDSFILE2,DISP=SHR
//SYSIN   DD *
  COMPARE TYPORG=PO
/*

MVS Abbrevations

MVS - Multiple Virtual Storage, is an operating system from IBM that runs on system/370 and system/390 IBM mainframe computers.

TSO - Time Sharing Option, lets remote terminal users invoke MVS facilities interactively.

ISPF - Interactive System Productivity Facility, runs a part of TSO and utilizes full screen capability of 3270 terminals.

PDF - Program Development Facility, part of the ISPF.

RACF - Resource Access Control Facility, identifies both users and resources.

SMF - System Management Facility, facilitates billing and monitoring.
           Ex. CPU time used, amount of DASD etc..

SMS - Storage Management Subsystem, automates data management services of             MVS.

JES - Job Entry Subsystem, provides job management facilities on MVS.

DASD - Direct Access Storage Device

Copying PDS members to PS file

• Open the PDS
• In the command line, type save <name1> 
• Now you can see all the members saved in PS file                          USERID.<name1>.MEMBERS

You can use this file to export the members to excel.

QMF ( Query Management Facility)

QMF is an MVS based query tool which allows end users to enter SQL queries to produce a variety of reports and graphs as a result of the query. 

In other words, this tool is used to issue SQL commands against DB2 database. The data results can be formatted as reports, charts etc. 

There are three different ways you can supply input to QMF viz., 

1. Entering SQL in Query Editor
2. By using prompted queries
3. Query by example (QBE)

DB2 Isolation levels

UR (Uncommitted Read) : 
  
  - For read only queries. 
  - No record locking. 
  - Good for accessing read only tables.

CS (Cursor Stability) :

- Default isolation level. 
- It locks and unlocks each row , 1 at a time. 
- Guaranteed to only return the data which was committed at the time of read

RS (Read Stability):

- Keeps all the qualifying rows locked until the transaction is complete.
- Releases the lock on those rows which doesn't satisfy the query

RR (Repeatable Read):

- Not just the rows that satisfy the query, but keeps the entire table locked until  the unit   of work is done.
- No other application program can update, insert or delete the row from the  table.

MQSeries basics

MQSeries allows different applications to communicate asynchronously through queues across different operating systems, different processors and different application systems.

MQSeries includes Message Queue Interface (MQI), a common low level programming Application Program Interface (API). Applications use MQI to read and write messages to the queues.

What is MQSeries ?

A middleware product that implements a messaging and queuing framework.

Messaging - Programs communicate by sending data in messages rather than by calling each other directly

Queuing - Messages are put on queues in storage, eliminating the need for programs to be logically connected.

A messaging and queuing framework is inherently asynchronous. 

MQSeries Objects

1.1 Queue manager

A queue manager is the part of MQSeries product which provides messaging and queuing services to application programs through MQI program calls. 

It controls access to the queues and serves as transaction coordinator for all queue operations.

Queue manager names must be unique.

1.2 Queues

MQSeries defines four types of queues

Local queue - an actual queue for which storage is allocated

Remote queue - A definition of queue on a different queue manager

Alias queue - Another name for local or remote queue. Typically used to switch queue destinations without modifying the program code.

Model queue - A template whose properties are copied when creating a new dynamic local queue.

1.3 Channels

A channel provide communication path between queue managers.

1.4 Messages

Any arbitrary data that one program wants to send to another. This data is called application data.

A message need to include other information such as its destination and possibly a return address. This type of data is called message descriptor. 

There are four types of messages

A request message is used by one program requesting something to another program. A request message needs a reply

A reply message is used in response to the request message

A one way message doesn't need a reply though it carries data

A report message is used when something unexpected occurs. For example, if the reply message doesn't contain any data then receiving program might issue a report message.

• Most useful report messages are generated by queue manager. Ex., delivery confirmation.                                                                                                                      
• Every message has an expiry. The message that has not been reached before its expiration will be removed.                                                                                                 
• Message correlator - Select which message to get from queue.                                      
• Message priority - Retrieve messages in different order.                                                  
• Segmented messages - Allows ending of very large messages ( > 100 MB ).                  
• A message can contain "reply to" address (the name of the queue manager and queue). This tells the receiving application where any response should be sent.             
• Messages are added and removed from queues in units of work.                                    
• The smallest unit of work is one message.                                                                        
• When an app reads a message from queue, a message "appears" to be removed but in fact, it's still in storage until the app "commits" the unit of work.

1.5 Pic : Message flow between applications

   

Frequently used APIs’ in Application Programs

(MQI – The MQSeries Programming Interface)

• MQCONN – Connect to queue manager 
• MQDISC – Disconnect from queue manager 
• MQOPEN – Open object
• MQCLOSE – Close object
• MQPUT – Put message
• MQPUT1 – Put one message
• MQGET – Get message
• MQBEGIN – Begin unit of work
• MQCMIT – Commit
• MQBACK – Back out
• MQINQ – Inquire about object attributes
• MQSET – Set object attributes

The application program can put many messages in queue before it closes or gets disconnected.

API command sequence for sending MQ messages

Step-1:

MQCONN - The MQCONN call connects the application program to a queue manager

Syntax :

MQCONN(QMgrName, Hconn, CompCode, Reason)

QMgrName(MQCHAR48) - input => Queue manager name

Hconn(MQHCONN) - output => Connection handle

CompCode(MQLONG) - output => Completion code

Reason(MQLONG) - output => Reason code qualifying CompCode

Step-2:

MQOPEN - The MQOPEN call establishes access to an object.

Syntax:

MQOPEN(Hconn,ObjDesc,Options,Hobj,CompCode,Reason)

Hconn(MQHCONN) - Output => Connection Handle

ObjDesc(MQOD) - Input/Output => Object descriptor

Options(MQLONG) - Input =>Options that control the action of MQOPEN

Hobj(MQHOBJ) - output => Object handle

CompCode(MQLONG) - Output => Completion Code

Reason(MQLONG) - Output => Reason code qualifying compcode 

Step-3:

The MQPUT call puts message on queue or distribution list. The queue or distribution list must already be open.

Syntax:

MQPUT(Hconn, Hobj, MsgDesc, PutMsgOpts,BufferLength,Buffer, CompCode, Reason)

Hconn (MQHCONN) – output =>Connection handle.

Hobj (MQHOBJ) – input => Object handle.

MsgDesc (MQMD) – input/output => Message descriptor.

PutMsgOpts (MQPMO) – input/output => Options that control the action of MQPUT.

BufferLength (MQLONG) – input => Length of the message in Buffer 

Buffer (MQBYTE×BufferLength) – input => Message data.

CompCode (MQLONG) – output => Completion code.

Reason (MQLONG) – output => Reason code qualifying CompCode 

Step-4:

The MQCLOSE call relinquishes access to an object.

Syntax:

MQCLOSE(Hconn,Hobj,Options,CompCode,Reason)

Hconn (MQHCONN) – output => Connection handle.
Hobj (MQHOBJ) – input/output => Object handle.
Options (MQLONG) – input => Options that control the action of MQCLOSE.
CompCode (MQLONG) – output => Completion code.
Reason (MQLONG) – output => Reason code qualifying CompCode

Step-5:

The MQDISC call breaks the connection between the queue manager and application program.

Syntax:

MQDISC(Hconn,CompCode,Reason)

Hconn (MQHCONN) – output => Connection handle.
CompCode (MQLONG) – output => Completion code.
Reason (MQLONG) – output => Reason code qualifying CompCode

 API Command sequence for receiving MQ messages

Step-1:

MQCONN - The MQCONN call connects the application program to a queue manager

Syntax :

MQCONN(QMgrName, Hconn, CompCode, Reason)

QMgrName(MQCHAR48) - input => Queue manager name

Hconn(MQHCONN) - output => Connection handle

CompCode(MQLONG) - output => Completion code

Reason(MQLONG) - output => Reason code qualifying CompCode

Step-2:

MQOPEN - The MQOPEN call establishes access to an object.

Syntax:

MQOPEN(Hconn,ObjDesc,Options,Hobj,CompCode,Reason)

Hconn(MQHCONN) - Output => Connection Handle

ObjDesc(MQOD) - Input/Output => Object descriptor

Options(MQLONG) - Input =>Options that control the action of MQOPEN

Hobj(MQHOBJ) - output => Object handle

CompCode(MQLONG) - Output => Completion Code

Reason(MQLONG) - Output => Reason code qualifying compcode

Step-3:

The MQGET retrieves a messages from a local queue that has been opened using the MQOPEN call.

Syntax:

MQGET (Hconn, Hobj, MsgDesc, GetMsgOpts, BufferLength, Buffer,DataLength, CompCode, Reason)

Hconn (MQHCONN) – output => Connection handle.

Hobj (MQHOBJ) – input => Object handle.

MsgDesc (MQMD) – input/output => Message descriptor

GetMsgOpts (MQGMO) – input/output => Options that control the action of MQGET

BufferLength (MQLONG) – input => Length of the message in Buffer 

Buffer (MQBYTE×BufferLength) – input => Message data

CompCode (MQLONG) – output => Completion code

Step-4:

The MQCLOSE call relinquishes access to an object.

Syntax:

MQCLOSE(Hconn,Hobj,Options,CompCode,Reason)

Hconn (MQHCONN) – output => Connection handle.
Hobj (MQHOBJ) – input/output => Object handle.
Options (MQLONG) – input => Options that control the action of MQCLOSE.
CompCode (MQLONG) – output => Completion code.
Reason (MQLONG) – output => Reason code qualifying CompCode

Step-5:

The MQDISC call breaks the connection between the queue manager and application program.

Syntax:

MQDISC(Hconn,CompCode,Reason)

Hconn (MQHCONN) – output => Connection handle.
CompCode (MQLONG) – output => Completion code.
Reason (MQLONG) – output => Reason code qualifying CompCode