Dynamic php code to get database fields in sql server

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>' );

};

....

....

?>

Php1-Pass a variable from one page to other webpage

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

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:

1. Only EQ & NE can be used for comparison.
2. 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

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

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

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

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

Advanced Cursors in DB2

                             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 

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

               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

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 

What is ECI?

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

                    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

Resolving error 37 of the Vsam file

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

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

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

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

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.