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

Maximum counts in JCL

Maximum number of generations allowed per GDG file are 255

Maximum number of EXEC statements possible per job are 255

Maximum number of DD statements possible per Job step are 3273

Maximum number of in-stream procedures we can code per JOB are 15

Maximum size permitted for the key of a VSAM KSDS is 255

Maximum number of characters we can give as PARM to a program are 100

Maximum record length possible for a data is 32760 for FB, 32756 for VB

Maximum size permitted for an alternate key is 255

Maximum number of duplicates permitted in an ALT index are 32768

Maximum value of the return code we can check for is 4095

Maximum conditions that can be coded in a single COND is 8

Maximum number of sequential datasets that can be concatenated together is 255

Maximum number of Partitioned datasets (PDS) concatenated together are 16

  

How to specify VSAM dataset as dummy in JCL ?

There will be a situation where we need to dummy our VSAM dataset. Here is how we can do that..

//VSAMFLNM  DD DUMMY, AMP='AMORG'

AMP='AMORG'  - This parameter tells the OS to treat the file as VSAM file.

Database basics

Relational database - A database structured to recognize relations between stored items of information. 

Data redundancy in database means that some data fields are repeated in the database.This data repetition may occur either if a field is repeated in two or more tables or if the field is repeated within the table.Data can appear multiple times in a database for a variety of reasons. For example, a shop may have the same customer’s name appearing several times if that customer has bought several different products at different dates.

Clustered index determines the physical order of data in a table. Only one clustered index is possible per table. But one clustered index can have multiple columns. The data in the table is arranged based on the clustered index column. 

Non-clustered index is like an index in the text book where the data is stored in one place and index is stored in another place.Since the actual data is stored 
separately, one table can have multiple non-clustered indexes just like a text book will have an index for chapters and an index for common words.

The sharing of Resources by multiple users or application programs at the same time is called CONCURRENCY.


Differences between Primary Key and Foreign key:

Primary key uniquely identify a record in the table.
Foreign key is a field in the table that is primary key in another table.

Primary Key can't accept null values.
Foreign key can accept multiple null value.

By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.

We can have only one Primary key in a table.
We can have more than one foreign key in a table.

Difference between COPY and INCLUDE

• COPY is used in simple COBOL program and INCLUDE is used in COBOL-DB2 Program.
• COPY statements are executed at compile time. INCLUDE statements are executed at run time.

SORT in COBOL

External Sort is used to sort the file externally, like through JCL.

Internal sort is used to sort the file inside COBOL program using SORT verb.

Three files are used in the sort process in COBOL:

Input file – The file which needs to be sorted either in ascending or descending order.

Work file – The file which is used to hold the records during sort process. Input file records are transferred to this file for the sorting process. This file needs to be defined in File section under SD entry.

Output file – Final file which contains the records in the desired sorted order.

Syntax: 

SORT work-file ON ASCENDING-KEY/DESCENDING-KEY

      USING input-file GIVING output-file

Once the sorted records are moved to output file, the sort file will be deleted.

//INFILE DD DSN=input-file

//OUTFILE DD DSN=output-file

//SORTWK DD DSN=&&TEMP

Work file should be opened in I-O mode.