The Big Old Mainframe

Dynamic php code to get database fields in sql server

2016-07-31T13:29:00.002+05:30

Here is small code snippet to display php fields dynamically:

page.php

<?php

....

$sql = "SELECT * FROM xyz where student1 ='$user1'";

$stmt = sqlsrv_query($conn, $sql);

if( $stmt === false ) {

die( '<pre>'.print_r( sqlsrv_errors(), true).'</pre>' );

};

if( sqlsrv_fetch($stmt) === false ) {

die( '<pre>'.print_r( sqlsrv_errors(), true).'</pre>' );

};

....

page1.php

<?php
global $stmt;
col4=;
echo col4;
?>

Php1-Pass a variable from one page to other webpage

2016-07-31T13:12:00.000+05:30

How to pass a variable from one webpage to another?

Its the simple command to be given in both the pages:

Mypage.php

<?php
session start();
row=1;
?>

Mypage1.php

<?php

session start ();

echo row;

DFSORT - To search for the string from the file when position is not known

2015-10-20T21:16:00.001+05:30

Search for a string when its position not known in the file records

To Search for a particular string within a file when the exact position of that string within a record is not known and to write that record in the Output file, the following SORT card can be used.

//SYSIN DD *

SORT FIELDS=COPY

INCLUDE COND=(1,80,SS,EQ,C'PQ2222222222')

Here,

SS - Sub string,

1 - Starting position of the range

80 - Width of the search range

EQ - Comparison operators

Note:

Only EQ & NE can be used for comparison.
We can even use OMIT COND instead of INCLUDE

Consider input files:

ABC.SORTIN

PQ000003 1000001 PQ2222222222 123

PQ000002 1000002 EF1111111111

PQ000004 1000002 AB3333333333 530 PQ2222222222

PQ000003 1000001 CD5555555555 005

PQ000001 1000004 RQ1111111111

PQR.SORTOUT

PQ000003 1000001 PQ2222222222 123

PQ000004 1000002 AB3333333333 530 PQ2222222222

We can also use AND/OR to combine conditions

//SYSIN DD *

SORT FIELDS=COPY

INCLUDE COND=(1,80,SS,EQ,C'PQ2222222222',AND,

1,80,SS,EQ,C'AB3333333333')

XYZ.SORTOUT

PQ000004 1000002 AB3333333333 530 PQ2222222222

KSDS VSAM file creation in read-only mode

2015-10-14T21:17:00.000+05:30

KSDS VSAM file can be made read only By using INHIBIT along with ALTER command

Example:

//STEP1 EXEC PGM=IDCAMS

//SYSPRINT DD SYSOUT=*

//SYSIN DD *

ALTER -

READ1.KSDS.DATA -

INHIBIT

ALTER -

READ1.KSDS.INDEX -

INHIBIT

Notice that the ALTER command is used with DATA and INDEX and not with the cluster.

List possible compiler diagnostic messages

2015-10-14T21:10:00.001+05:30

Use of Compiler diagnostic messages

A complete listing of compiler diagnostic messages can be generated with their explanations, by compiling a program with the program-id of ERRMSG specified in the PROGRAM-ID paragraph.

Example:

IDENTIFICATION DIVISION

PROGRAM-ID.ERRMSG.

ENVIRONMENT DIVISION.

DATA DIVISION.

PROCEDURE DIVISION.

STOP RUN.

Trigger CICS transaction Batch programs

2015-09-25T20:04:00.004+05:30

There are many situations where user has to trigger CICS transaction via batch jobs. This becomes handy when any CICS program is being coded and situation occurs to allocate/de-allocate files.

However, there is one installation required up hand for CICS to accept the commands from batch; the console definition must be defined in the terminal table. Sample console definitions are provided by IBM supplied resource group DFH$CNSL. You would require your CICS administrator to set up if not done.

Use the MODIFY command to trigger the transaction from a batch job. MODIFY command is abbreviated to F.

Example: Following JCL launches the transaction TXN1 in the CICS region CICRGN1

//CICSBTCH JOB …

//STEP001 EXEC ...

// ………..

//STEP002 EXEC PGM=IEFBR14

// COMMAND 'F CICRGN11,TRA1'

//*

Note

i) The MVS command is directly executed without waiting for the completion of the STEP001.

ii) The above command triggers the transaction TRA1 in the region CICRGN11.

iii) The command must be placed between quotes.

To avoid the execution of the command before the completion of STEP001, launch the MVS command in separate job.

Example:

//BTCHSUB JOB ......

//STEP001 EXEC ......

// --------

//STEP002 EXEC PGM=IEBGENER

//SYSPRINT DD SYSOUT=*

//SYSUT1 DD DATA,DLM=@@

//CICSBTCH JOB ……

//STEPR EXEC PGM=IEFBR14

// COMMAND 'F CICRGN11,TRA1'

//SYSUT2 DD SYSOUT=(A,INTRDR)

//SYSIN DD DUMMY

//*

In the above job, STEP002 submits the batch interface job (CICSBTCH) to internal reader upon the completion of the step STEP001. Thus the transaction TRA1 is triggered only after the completion of STEP001.

Trigger JCL via SPOOL option in CICS

2015-09-25T17:32:00.001+05:30

CODE SNIPPET

IDENTIFICATION DIVISION.

PROGRAM-ID. SAMPLE.

AUTHOR.

DATE-WRITTEN. 04/01/XXXX.

DATE-COMPILED. 04/01/XXXX.

*******************************************************************

* CODE SNIPPET TO FORM JCL VIA CICS. *

* ONLINE PROGRAM CALLED THROUGH TRANSACTION *

*******************************************************************

ENVIRONMENT DIVISION.

CONFIGURATION SECTION.

DATA DIVISION.

WORKING-STORAGE SECTION.

01 WS-T PIC X (8).

01 JCL-CARDS.

05 FILLER PIC X (80) VALUE '//SAMPLE JOB (XYZ),"BATCH RUN"','.00

05 FILLER PIC X(80) VALUE '// CLASS=E,MSGLEVEL=(1,1),REGION=0M,MSGCLASS=X'.

05 FILLER PIC X(80) VALUE '//THREAD EXEC PGM=IKJEFT01,DYNAMNBR=20'.

05 FILLER PIC X(80) VALUE '//STEPLIB DD DSN=CEE.SCEERUN,DISP=SHR'.

05 FILLER PIC X(80) VALUE '// DD DSN=CEE.SCEECICS,DISP=SHR'.

05 FILLER PIC X(80) VALUE '// DD DSN=CEE.SCEELKED,DISP=SHR'.

05 FILLER PIC X(80) VALUE '// DD DSN=SYS1.DSNXXX.SDSNLOAD,DISP=SHR'.

05 FILLER PIC X(80) VALUE '// DD DSN=DSNXXX.DBXX.RUNLIB.LOAD,DISP=SHR'.

05 FILLER PIC X(80) VALUE '// DD DSN=ABCD.XXX.COBLOAD,DISP=SHR'.

05 FILLER PIC X(80) VALUE '// DD DSN=ABCD.XXX.ASMLOAD,DISP=SHR'.

05 FILLER PIC X(80) VALUE '//SYSTSPRT DD SYSOUT=*'.

05 FILLER PIC X(80) VALUE '//SYSPRINT DD SYSOUT=*'.

05 FILLER PIC X(80) VALUE '//SYSABOUT DD SYSOUT=*'.

05 FILLER PIC X(80) VALUE '//SYSDBOUT DD SYSOUT=*'.

05 FILLER PIC X(80) VALUE '//SYSUDUMP DD SYSOUT=*'.

05 FILLER PIC X(80) VALUE '//SYSOUT DD SYSOUT=*'.

05 FILLER PIC X(80) VALUE '//SYSIN DD *'.

05 WS-THREAD-DTLS.

10 FILLER PIC X(12) VALUE 'CTRANSAC 0001'.

10 FILLER PIC X(68) VALUE ' '.

05 FILLER PIC X(80) VALUE ' '.

05 FILLER PIC X(80) VALUE '/*'.

05 FILLER PIC X(80) VALUE '//SYSTSIN DD *'.

05 FILLER PIC X(80) VALUE ' DSN SYSTEM(DBXX)'.

05 FILLER PIC X(80) VALUE ' RUN PROGRAM(PXXXXXX)-'.

05 FILLER PIC X(80) VALUE ' PLAN(PXXXX)'.

05 FILLER PIC X(80) VALUE 'END'.

05 FILLER PIC X(80) VALUE '//*'.
05 FILLER PIC X(80) VALUE '/*EOF'.

01 FILLER REDEFINES JCL-CARDS.

05 CARD OCCURS 27 TIMES INDEXED BY CARD-INDEX PIC X(80).

01 WS-JCL-LINES PIC 9(3) VALUE 27.

PROCEDURE DIVISION.

A-MAIN-PARA.

PERFORM B-MAIN-PROCESS THRU B-MAIN-PROCESS-EXIT

PERFORM C-TERMINATION THRU C-TERMINATION-EXIT

A-MAIN-PARA-EXIT.

EXIT.

B-MAIN-PROCESS.

EXEC CICS

SPOOLOPEN OUTPUT

USERID('INTRDR')

NODE('XXXX')

TOKEN(WS-T)

NOHANDLE

END-EXEC

PERFORM VARYING CARD-INDEX FROM 1 BY 1 UNTIL CARD-INDEX > WS-JCL-LINES

EXEC CICS SPOOLWRITE

TOKEN(WS-T)

FROM(CARD(CARD-INDEX))

FLENGTH(80)

NOHANDLE
END-EXEC

END-PERFORM

EXEC CICS SPOOLCLOSE

TOKEN(WS-T)

NOHANDLE

END-EXEC.

B-MAIN-PROCESS-EXIT.

EXIT.

C-TERMINATION.

GOBACK.

C-TERMINATION-EXIT.

EXIT.

Sample JCL:

//SAMPLE JOB (XYZ),'BATCH RUN',0.0
//CLASS=E,MSGLEVEL=(1,1),REGION=0M,MSGCLASS=X
//THREAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=CEE.SCEERUN,DISP=SHR
// DD DSN=CEE.SCEECICS,DISP=SHR
// DD DSN=CEE.SCEELKED,DISP=SHR
// DD DSN=SYS1.DSNXXX.SDSNLOAD,DISP=SHR
// DD DSN=DSNXXX.DBXX.RUNLIB.LOAD,DISP=SHR
// DD DSN=ABCD.XYZ.COBLOAD,DISP=SHR
// DD DSN=ABCD.XYZ.ASMLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSABOUT DD SYSOUT=*
//SYSDBOUT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
CTRANSC 0001
/*
//SYSTSIN DD *
DSN SYSTEM(DBXX)
RUN PROGRAM(PXXXXXX)-
PLAN(PXXXX)
END
//*

Advanced Cursors in DB2

2015-09-25T16:10:00.001+05:30

CURSORS IN DB2

Use of DB2 Cursors in Application Program

Mechanism that allows the SELECT statement to find a set of rows from table
It return rows one at a time
Used as similar to a File which can be Opened, Read and Closed
‘Fetch’ terminology is used instead of ‘Read’
Can be declared in both ‘Working Storage’ and ‘Procedure Division’
Should always be declared before the ‘Open’ Cursor statement

Types of Cursors

- Row Positioned Cursor
- RowSet Positioned Cursor
- Scrollable
- Non Scrollable
- Held
- Non-Held
- Returnable
- Non-Returnable

Brief Description for cursors

Row Positioned Cursor : Standard common cursor used for DB2-COBOL programs. Returns on row from the table at a time into host variables. The steps for this cursor are Declare, Open, Fetch and Close operations.

RowSet Positioned Cursor: RowSet positioned cursor retrieves 0, 1 or many records at a time into host variables array. This array will be declared within application program. The steps for this cursor are Declare, Open, Fetch and Close operations.

Scrollable: This is identified with SCROLL keyword in the Declare statement of cursor. The behaviour of the scrollable cursor is governed based on the sensitivity with cursor is declared. It can be row positioned or row set positioned. The steps for this cursor are Declare, Open, Fetch and Close operations. There are subtypes to this one as insensitive and sensitive.

Non-Scrollable: Do not have key word SCROLL. A simple Row or rowset cursor postioned cursor without a scroll clause is a non-scrollable cursor

Held: This type of cursor is not closed explicitly after commit operation is complete. It returns only 1 row at a time into host variables.The steps for this cursor are Declare, Open, Fetch and Close operations. Application programmer can issue close or rollback operation to the held cursor; if not issued cursor is closed when the application program terminates.

Non-Held: This is similar to row positioned cursor. This is closed after a Commit operation is complete.

Returnable: This type of cursors are generally used in stored procedures. This are mainly used to return set of records directly in application program. All the rows selected by the cursor are output at the same time.The steps for this cursor are only open and close statements. More than 1 cursor can be present in single application program.

Non-Returnable: This is similar to non-held cursor. It returns only 1 row at a time.

What is MRO - Multi Region Operation! Why is it useful! How is TOR, AOR and FOR linked?

2015-09-13T15:00:00.003+05:30

Using Intercommunication Features in CICS

MRO - Multi Region Operation is used connect CICS region running on the same processor.

Each System has its own address space independently, hence if any region goes down others are not affected.

Typical MRO includes

TOR - Terminal Owning Region which owns all the Terminal
AOR - An Application Owning Region that owns all the Applications
FOR - File Owning Region that owns all the Files

How transaction routes from TOR to AOR?

The PCT entry in the Terminal Owning Region is defined as below:

Terminal Owing Region (PRVT)

Transaction: ABC1
Type: Remote
Sysid: PRVA

Application Owing Region(PRVA)

Transaction: ABC1
Type: Local
Program: XYZ123P

- Any transaction is triggered in the TOR terminal. It checks PCT entry for type and sysid for AOR
- Transactions then routes to the AOR where its defined local in PCT and hits program defined in it.
- Any I/O transaction to be done is routed back to TOR

How transaction is shipped from AOR to FOR?

In Application Owing Region(PRVA)

XYZ123P
Reads Customer1

File Control Table Definition in AOR
Type: Remote
File : Customer1
SYSID: PRVF
Reclen: 258
Key: 25

In File Owing Region(PRVF)

File Control Table Definition in FOR
Type: Local
File : Customer1
Access: VSAM KSDS

Function shipping allows to access resource defined in the other region.
If Program defined in AOR, needs to access file which is defined local in other region, FCT entry is defined with sysid of the FOR region and file type is defined as remote
Once the program needs to read this file, the control is transferred to the FOR region where file is residing as local.
Program accesses required information and passes control back to AOR region.

Separate CICS Business Application Transactions and CICS Default Communication Transactions

2015-09-13T13:56:00.003+05:30

Separate Default and Application Level Transaction

This is kind of good practice to always separate Default CICS Transaction from the application level of Transaction.

Once the TCPIP connections are established between Web Services and CICS;

- All the CICS services runs under CIEP transaction as its defined and linked to the TCP IP service and port/host of the Web Services

- All the services running under default is copied to the mirror transaction as its defined as communication parameter for Web Services

- Also when messages are serviced from the Web Services, generally CICS receives program which needs to be invoked and that application level program runs under default transaction

- In these cases, if any application level service is invoked it runs under default transaction CIEP which creates problem if any user needs to debug

The Flow is

Web Service Intiates communication via defined port and CICS CPMI Mirror transaction
Port is linked to the CICS default transaction CIEP
Any service that runs under CIEP will be copied on mirror transaction CPMI
Application program is invoked (Example : PKAABC) as its received in the message from Web Service
This transaction ideally runs under CIEP if specific application level transactions is not linked

Hence, always have separate transaction defined for the application level program and separate it from the communication parameters

CEDA DEFINE TRANSACTION(XYZ1) PROGRAM(XYZ2) GROUP(AAA2)

Hence this program XYZ2 received in the message from Web Services will under XYZ1 transaction. And this transaction will run under CIEP CICS default transaction. As explained above complete CIEP transaction will be mirrored under CPMI transaction linked to Web Service by TCP IP set up.

Read Related Article:

External Call Interface

Establish TCP IP Service Between Web and CICS

What is ECI?

2015-09-13T12:56:00.000+05:30

External Call Interface

ECI calls are the way of communication between CICS and Web Services which uses TCP IP connections.

Client Side or Web Service need not use BMI setups to connect to CICS online transactions.

ECI calls are set up and linked the CICS default transaction. And on the client side or Web Service side can be set up in a way where it can run on mirror transactions

Read Blog for more reference : TCPIP CONNECTION BETWEEN CICS AND WEB SERVICES

Establishing TCP IP service between Web Services and CICS via CIEP Default transactions

2015-09-13T12:53:00.001+05:30

TCPIP Connection between CICS and Web Services

If you have any Java front end connected to CICS via Web Services following parameters needs to be taken care of:

Currently, I have tested with the Web Services using WSDL objects. There are Web services use the standard web protocols HTTP, XML, SOAP, WSDL, and UDDI. WSDL (Web Services Description Language) is an XML-based language used to define web services and to describe how to access them.

- Connection between CICS and Web Method Serivces via TCP IP protocols

- Define TCP IP Service and link it to the port number

Command in CICS

tcpips(WEBM234) Ope Por(01234) ECI Nos Trans(CIEP)

This port is connected to the listener transaction in CICS CIEP which invokes default listener program DFHIEP. This would enable the ECI (External Call Interface) calls via TCP/IP provided the client is setup.

On the client side that is on Web Services Methods there are communication parameters to be defined that is

Host Name - Example pkam.mf.xyz.net
Port Name - The above mentioned host name is linked to CICS port defined 01234
CICS Mirror Transaction CPMI - Any service which runs under CIEP transaction defined above is linked here and it runs under mirror transaction CPMI

Read Related Blog:

Separate CICS Default Transactions and Business Application Logic Transactions

Resolving error 37 of the Vsam file

2014-08-12T11:40:00.001+05:30

Resolving error 37 of the Vsam alternate index

Whenever we define Vsam Alternate Index File we might face error 37 when we use to process the file via JCL first time:

The solution mentioned to resolve this is to initialize Vsam file once before directly opening in the program. But still many times you must have faced same error again and again whenever you try to read alternate index vsam file via a cobol program and through JCL.

Whenever we define VSAM alternate index file we have following components:

Cluster: OLDMF.DATA.KSDALT

Data:              OLDMF.DATA.KSDALT.DAT

INDEX:            OLDMF.DATA.KSDALT.INX
AIX :               OLDMF.DATA.KSDALT1
PATH:             OLDMS.DATA.KSDALT.PATH

This filename and alternate index is used in the JCL to read the Cobol program and in JCL it is placed with its logical name as:


//OLDMF     DD   DSN= OLDMF.DATA.KSDALT,DISP=OLD

//OLDMFD    DD   DSN=OLDMF.DATA.KSDALT1,DISP=OLD



The problem is in the way we write our logical name. Even if you try to initialize file and you may still face the error. It will be resolved by the way we write logical name for the alternate index in the JCLs.
Correct declaration:

//OLDMF     DD   DSN= OLDMF.DATA.KSDALT,DISP=OLD


//OLDMF1    DD   DSN=OLDMF.DATA.KSDALT1,DISP=OLD




Always the logical name will be appended with the next sequence number starting from 1.

Writing MQ Batch Program and MQ JCL

2013-05-11T18:01:00.003+05:30

How to start MQ batch program and MQ JCL?

Message Queuing is the bridge between agile world today. Today there are lot of variations in programming language. There is need of hour to make different systems integrate; talk with each other and to make this happen MQ series is one of the best option.

I have written couple of blogs on MQ efficiency use etc.Today I will write MQ blog to understand how easy is to use MQ .

There are few steps which MQ batch program/JCL requires it:

MQ JCL

1. There is MQ load library which has in build objects which is needed by any MQ JCL and its is mostly named as from MQM*. in your mainframe system

2. Check if the Queue Manager is passes to get connected to Queue Manager.Typically, all the mainframe system as SySplex.

Now what is Sysplex? Sysplex is the word derived from system complex. This means generally the mainframe server are divided into one or more LPARs. LPARs are logical partition. This is done to use mainframe server efficiently. I will not go in this details now.

So coming back to point 2, due to Sysplex environment each LPAR has its Queue Manager. And generally MQ admin group, mergers all the Queue Manager into one. So always confirm for Queue Manager from MQ admin. Due to this, it gives flexibility to MQ JCL to run against any Queue Manager available. Even if one is down, job will not fail it will run against next avaliable.

3. Make sure you have correct Message Queue Names.

Logic is all the Message Queues are controlled by its Queue Manager. Queue Manager has couple of setting for Queues.

MQ Program

1. Check to include IBM MQ copy books. Its for MQ Manager Check, Message Queue Check, Parameters for both of them and return code checks. These copybooks are available on IBM site and they are pretty much standard for all the MQ programs.

2. Check that you have placed calls correctly. All the call along with parameters are available at IBM website.

Generally to read MQ it goes as:

a. Connect Queue Manager

b. Open Queue

c. Browse Message Queue - Keep it destructive read preferbly

d. Process the message

e. Close Queue

f. Disconnect Queue Manage

Generally to write MQ it goes as:

Make sure you read the file from where you want to write message to Queue into working storage variable passed to Queue.

a. Connect Queue Manager

b. Open Queue

c. Write Message into Queue

e. Close Queue

f. Disconnect Queue Manage

Best practice is to check return code after every call.

3. Also check do MQDISCONNECT at the end. This helps to free resource immediately.

DB2 Performance Tips - EXPLAIN

2013-04-21T19:24:00.001+05:30

DB2 EXPLAIN Explained

What is DB2 Explain?

It is an DB2 aid for optimization.

What does DB2 Explain does?

All the SQL statements are passed through DB2 optimizer and the access path are externalized. This means any DBA doing performance test can get correct access path by using EXPLAIN. All the access path are recorded in PLAN_TABLE.

What is PLAN_TABLE?

Important coloumns for PLAN_TABLE are as following:

QUERYNO : This is the query number user should assigned to identify query.
QBLOCKNO: Position of query in the statement being EXPLAINED
APPLNAME : This is PLAN Name
PROGNAME : Program/Package Name
METHOD : There are 4 options and each number signifies type of join

0 Table Access

1 Nested Loop

2 Merge Scan

3 Sort needed by ORDER BY, GROUP BY, SELECT, DISTINCT or UNION

4 Hybrid
CREATOR : User id
TNAME : Tablename
TABNO : position and reserved for IBM only
ACCESSTYPE :

I - by index

N - indexscan when predicate is IN

R - Table Space Scan

M - Multiple Index Scan

MX - Index Scan

MI - Intersection of multiple index

MU - Union of multiplex index
MATCHCOLS : number of keys used in index scan

ACCESSCREATOR : Creator of Index

ACCESSNAME : Name of Index

INDEXONLY : Y/N ( Yes is index is sufficient to get required data)

SORTN_UNIQ : Y/D (Yes if Sort performed to remove duplicates )

SORTN_JOIN : Sort is performed on table when method is 2 or 4

SORTN_ORDERBY : if query results in order by

SORTN_GROUPBY : if query results in group by

TSLOCKMODE : Lock Mode of table or tablespace

IS : Intent Share Lock

IX : Intent Exclusive Lock

S : Share Lock

U : Update Lock

X : Exclusive Lock

SIX: Share with intent exclusive lock

N : UR isolation; no lock

TIMESTAMP : Timestamp at which explain statement is bound

PREFETCH : List Prefetch or Sequential Prefetch

MIXOPSEQ : Sequence

DB2 performance tips - PART 1

2013-04-21T18:54:00.003+05:30

DB2 Queries that can optimize performance

1. Select columns which are required
2. Use where clause for queries where ever possible
3. Avoid using NOT in Queries as NOT is non-indexable
4. Use DATE Functions wherever possible to get number of days.
Eg: Select DATE('25-04-1986') - DATE('04-09-1995')
5. Details on Index and EXPLAIN:

Before and After creating index always use EXPLAIN functionality of DB2 to measure how and index is working.If the index you created is being used then you will get that in your query once explain runs.

If you want to see when your index is last used you can do that by this query:

SELECT * FROM SYSIBM.SYSINDEXES where NAME = 'your index name';

I will give details on EXPLAIN in my next DB2 blog.

SYNCSORT or DFSORT - PART 3

2013-03-24T16:00:00.001+05:30

Problem Statement

//*********************************************************************

//* I have two files.
//* FILEA contains only timestamp X(26).
//* FILEB contains Office X(3), Account X(6), timestamp X(26) and Identity number X(3)
//* I want all records of FILEB for timestamp values not present in FILEA

//*********************************************************************

SORT JCL Used:

Step 1: Sorted FILEB with timestamp as first field.

//SORT01 EXEC PGM=SORT

//SORTIN DD DSN= REC.FILE, ---FILE B(WITH LAYOUT GIVEN)

// DISP=SHR

//SORTOUT DD DSN=OUTOUT.FILE,

// DISP=(OLD,CATLG,DELETE),

// SPACE=(CYL,(10,50),RLSE),VOL=(,,,40),

// DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//SYSOUT DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SORTLIST DD SYSOUT=*

//SYSIN DD *

INREC FIELDS=(10,26,1,9,36,3)

SORT FIELDS=(1,26,CH,A)

Step 2 Remove/Discard duplicates on from the sorted file and the FILE A which is sorted on timestamp.

//SORT02 EXEC PGM=ICETOOL

//INPUT DD DSN=OUTOUT.FILE,DISP=OLD

// DD DSN=TMSTMP.FILE,DISP=OLD --FILE A(TIMESTAMPS)

//OUTPUT DD DSN=FILE.DISCARD,

// DISP=(OLD,CATLG,DELETE),

// SPACE=(TRK,(1,5),RLSE),

// DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//FINAL1 DD DSN=FINAL.FILE,

// DISP=(OLD,CATLG,DELETE),

// SPACE=(TRK,(1,5),RLSE),

// DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//TOOLMSG DD SYSOUT=*

//DFSMSG DD SYSOUT=*

//TOOLIN DD *

SELECT FROM(INPUT) TO(OUTPUT) ON(1,26,CH) ALLDUPS DISCARD(FINAL1)

ICETOOL is used here to sort on timestamp and remove dupicate timestamp records.

Step 3 Sorted as per layout in FILE B.

//SORT03 EXEC PGM=SORT

//SORTIN DD DSN=FINAL.FILE,

// DISP=SHR

//SORTOUT DD DSN=FINAL1.FILE,

// DISP=(OLD,CATLG,DELETE),

// SPACE=(CYL,(10,50),RLSE),VOL=(,,,40),

// DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//SYSOUT DD SYSOUT=*

//SYSPRINT DD SYSOUT=*

//SORTLIST DD SYSOUT=*

//SYSIN DD *

INREC FIELDS=(27,9,1,26,36,3)

SORT FIELDS=(1,26,CH,A)

/*
Sorted again in original file form.

Please refer PART1 and PART2 here.

JCL System Abend: SOC 1 and SOC 7

2013-03-22T10:58:00.002+05:30

SYSTEM ABENDS

I am sure everyone is aware of system ABENDS. I have tried to list ABENDS for all the possible reasons. Hope you all find it useful.

SOC 1

This abend can be faced in following situations. Rule out following possibilities:

Any file doesn't have open statement and directly trying to read it
The subscript it may be out of boundary for a defined COBOL table
Review JCL for missing or misspelled DD name
Problem in parm passed to subroutine related to addressing mode or Data mode
Recording mode may be wrong
Sub program called is not found problem in dynamic link or static link
Check load module there can be a possibility if subroutine program id and program name not same. This means bad load module.
An uncontrolled loop moved data on top of instructions.
Problem can be same name for array and subroutine

SOC 7

This is a data exception and can only occur when decimal (packed) instructions are used.

When alphanumeric data moved to packed decimal data (COMP 3).

When Programmer use a variable, which is not defined in working storage.

Period missing after imperative statements within AT END clause.

Binary field in an arithmetic operation is not large enough to accept result.

Failure to initialize a counter.

Invalid incoming data (e.g., blanks, decimal points, or commas in a numeric field).

Exceeding a table via a subscript (index) error, causing a reference to invalid data.

Moving zeroes or low-values to a group field defined as numeric.

An omitted or erroneous usage clause.

Passing parameters between programs in the wrong order.

Options to recover deleted flat files or member for PDS

2013-03-17T16:57:00.002+05:30

Options to recover deleted files

There is option on mainframe to recover flat files. Here are the various commands to
do that:

Go to Start 6
HLIST BCDS DSN('filename')
Wait for system response
Issue command HRECOVER 'filename' and wait for system response.

There are various ways to use HRECOVER as mentioned below:

HRECOVER 'filename' recovers with same name
HRECOVER ‘old filename’ NEW ('new filename') recovers and assigns new name
HRECOVER 'file name' REPLACE recovers replacing a file with same name

The recovery might take some time. HSM dump(s) of the volume(s) where the dataset
resided for HRECOVER should be present to recover files successfully. If those dump(s)
are not avaliable user will recieve a message "BACKUP/DUMP COPY DOES NOT EXIST".

Options to recover member from PDS

HRECOVER 'PDS.NAME' NEWNAME('PDS.RESTORED.NAME')

OR

In 3.4, access the dataset and enter the following command next to it

PDS / RES XXX FIND(?info inside the lost member?) REPEAT NOPROMPT
PDS / RES XXX FIND(?????) REPEAT NOPROMPT

Members will be restored in the PDS with XXX00001, XXX00002, etc.

OR we too have TSO Command:

TSO HLIST BCDS DSN ('Dataset Name')
USed incase backup are taken in generations.
TSO HRECOVER 'Original Dataset Name' GEN(005) NEWNAME

Introduction to most useful SYSIBM DB2 tables - PART 2

2013-03-17T16:44:00.001+05:30

Here comes some more SYSIBM tables

SYSIBM.SYSSTMT : This table is input to bind process. It contains statements for every plan known in DB2.

SYSIBM.SYSPLANS: contains information on every plan known to DB2. The plan name is unique in the DB2 subsystem.

SYSIBM.SYSFOREIGNKEYS: contains information foreign keys for table.

http://supportline.microfocus.com/documentation/books/sx51/rhsqst12.htm

SYSIBM.SYSDBAUTH: Contains information on database level authorizations for each user of each database. Entries are made into this table when database-related GRANT or REVOKE commands are issued for the location.

http://supportline.microfocus.com/documentation/books/sx51/rhsqst0y.htm

Please refer PART1 link to get more information on SYSIBM tables.

Useful free webinars for COBOL developers

2013-03-17T16:09:00.000+05:30

Hello COBOL Developers!!

Here is amazing site for COBOL development, here there are web seminars where we can register and view them:

https://www.microfocus.com/resources/events/VCwebinars/VCwebinars.aspx

Topics included range from Visual COBOL, Using COBOL with JAVA, .NET etc.

XML with COBOL Basics

2013-03-17T15:39:00.005+05:30

Introduction to XML coding with COBOL

By the growing Global transactions, its need of an hour to introduce common language that can be used cross platform. Here comes introduction to XML on COBOL. This blog gives brief introduction on XML usage with COBOL. It includes:

1. XML Introduction

2. XML Tags

I have focus on how actually developer can code in COBOL language as you can find lot of literature online.

XML :

It is Extensible Markup Language. It can be used cross platform hence universal in nature.It has tag language.Tags can be single or nested depending upon requirements.

Eg: <a>...</a>

Tag documents can be designed in free flow way.Tags can't overlap each other. Eg <p><r>... </p></r> <-- This is not allowed. There are few more rules which I will point it out in my example.

Example:

<?xml version = "1.0"?>

<p>

</p>

As you can see above XML are:

1. Text based can be saved as .txt file or .xml file or as PS file in Mainframe environments.

2. It is case sensitive

3. Contains one root

4. Starts and end with matching tags

5. Any attribute value should be in quotes

6. Properly nested

You will find usage of XML Parser and XML-COBOL coding in my up coming blogs.

EBCDIC characters required in COBOL programing

2013-03-17T15:19:00.000+05:30

Here is the chart for EBCDIC characters

Creating Different Dimension Tables in COBOL

2013-03-16T16:27:00.003+05:30

As we saw single dimension table in my blog linked here . There are many other ways to use COBOL table.

Another Way of Single Dimension TABLE:

COBOL Declaration:

01 A1-TABLE.

05 A2-TABLE OCCURS 100 TIMES

15 P1-NO PIC X(05).

15 P1-NAME PIC X(10).

15 P1-TEL NO.

25 LAND-LINE PIC 9(10).

25 MOBILE PIC 9(11).

This declarlation will work in COBOL as table described below:

Interview Questions for COBOL

2013-03-16T15:43:00.001+05:30

How do you do in-line PERFORM?

PERFORM ... <UNTIL> ...

END-PERFORM

When would you use in-line perform?

When the body of the perform will not be used in other paragraphs. If the body of the perform is a generic type of code (used from various other places in the program), it would be better to put the code in a separate Para and use

PERFORM Para name rather than in-line perform.

What is the difference between CONTINUE & NEXT SENTENCE ?

They appear to be similar, that is, the control goes to the next sentence in the paragraph.

But, Next Sentence would take the control to the sentence after it finds a full stop (.).

Check out by writing the following code example, one if sentence followed by 3 display statements

If 1 > 0 then next sentence end if display 'line 1' display 'line 2'. display 'line 3'. Note- there is a dot (.) only at the end of the last 2 statements, see the effect by replacing Next Sentence with Continue.

What does EXIT do ?

Does nothing ! If used, must be the only sentence within a paragraph.

Explain the difference between an internal and an external sort, the pros and cons, internal sort syntax etc.

An external sort is not COBOL; it is performed through JCL and PGM=SORT.

An internal sort can use two different syntax’s:

1) USING, GIVING sorts are comparable to external sorts with no extra file processing;

2) INPUT PROCEDURE, OUTPUT PROCEDURE sorts allow for data manipulation before and/or after the sort.