Cobol - DB2 bind process

Precompiler:

The DB2 Precompiler splits the program into two parts: a COBOL and a DB2 part. The embedded SQL is stripped out of the program and put into a partitioned data set (PDS) member, called a DBRM. Just as the COBOL part has to be compiled, the DBRM part has to go through BIND process to create the run-time executable code for the DB2 portion of the COBOL program. To help the COBOL and DB2 part to find each other later at run time, the precompiler engraves each with identical timestamps called consistency tokens. 


Bind plan:
You can BIND the DBRM into a PLAN (the old way), or you can BIND the instructions into a PACKAGE.


Plan:
A PLAN is an executable module containing the access path logic produced by the DB2 optimizer. The DBRMs of more than one program or PACKAGES can be bound into a PLAN.


Package:
A PACKAGE is a single, bound DBRM with optimized access paths. The DBRM of a single program is bound into a PACKAGE. To execute a PACKAGE, it should be included in the package list of a PLAN. PACKAGEs are not directly executed, they are only indirectly executed when the PLAN in which they are contained executes.

DBRM & Plan:
The relationship between a DBRM and a PLAN is one-to-many, the relationship between a DBRM and a PACKAGE is always one-to-one. 


Bind process:
As the number of DBRMs bound to a PLAN increases, binding the DBRM into a PLAN is not recommended. If we need to precompile and bind a new program or one of the programs changes and it is to be precompiled and bound again, all the programs (not just the modified/added program) will be rebound into the PLAN again. Then the BIND process could take hours to complete.

On the other hand, if a DBRM is bound to a PACKAGE and if the program is modified, only that PACKAGE would have to be rebound.


Collection:
A collection is simply a way of grouping PACKAGEs into meaningful groups. You could use COLLECTIONs to separate programs for different application areas, such as payroll and inventory. Another use might be to have customized set of BIND parameters associated with different COLLECTIONs.


Run Time:
At run time, the load module starts up and eventually hits a paragraph containing a CALL to DB2. Then the COLLECTIONs named in the PLAN are searched for the PACKAGE with the same name and consistency token. If you don't find it anywhere in DB2, you get an -805 error. If you're using the older technique of binding DBRMs directly into PLANs, then an unsuccessful search will result in an -818 error code.

Delete record using CURSOR

Using below COBOL program you could fetch a particular record from database and delete it using a query;

IDENTIFICATION DIVISION.

PROGRAM-ID. DELCUR.

ENVIRONMENT DIVISION.

DATA DIVISION.

WORKING-STORAGE SECTION.

                EXEC SQL

                    INCLUDE NMTAB

                END-EXEC.

                EXEC SQL

                    INCLUDE SQLCA

                END-EXEC.

PROCEDURE DIVISION.

                PERFORM 100-MAIN-PARA.

100-MAIN-PARA.

                EXEC SQL

                     DECLARE CUR CURSOR FOR

                     SELECT ID,NAME FROM NMTAB

                    WHERE ID =”ID1022” FOR UPDATE OF NMTAB

                END-EXEC.

                EXEC SQL

                    OPEN CUR

                END-EXEC.

                PERFORM 200-UPD-PARA.

                EXEC SQL

                    CLOSE CUR

                END-EXEC.

                STOP RUN.

200-UPD-PARA.

                EXEC SQL

                     FETCH CUR INTO :MID, :MNAME

                END-EXEC.

                IF MID=”ID1022”

                EXEC SQL

                  DELETE FROM NMTAB

                 WHERE CURRENT OF CUR

                END-EXEC.

                ELSE

                DISPLAY “THE RECORD IS NOT FOUND”.

In the above program we have declared a cursor CUR to select a particular record from table NMTAB. Open the cursor perform the para where the record will be deleted using the query once the record has been fetched. Once done close the cursor and stop the program.

TABLE:

ID                     NAME                 JOB

M1011                  KARTI                SOFTWARE

M1022                  RAVI                 POLICE

AFTER PROGRAM:

ID                     NAME                 JOB

M1011                  KARTI                SOFTWARE

Question on COMP in COBOL program

1. Sign storage in COMP-3 field,

The sign indicator is always stored at the last bit.
If your number is +100, it stores hex 0C in the last byte.
If your number is -101, it stores hex 1D in the last byte.

2. Sign stored in COMP field.

The Sign is stored in the Most significant bit.
Bit is ON if -ve.
Bit is OFF if +ve.

3. difference between COMP & COMP-3.

COMP is a binary storage format.
COMP-3 is a Packed decimal format.

4. difference between COMP-1 & COMP-2.

COMP-1 - Represented as Single floating point. Uses 4 bytes.
COMP-2 - Represented as Double floating point. Uses 8 bytes.

5. S9(7) COMP-3 field occupy?

According to the formula  INT((n + 1)/2) 
n=7
INT((7+1)/2)) = 4.
This will take 4 bytes.

6. S9(8) COMP field occupy?

4 bytes. As the number of bytes are divided by 2.

7. S9(7) SIGN TRAILING SEPARATE field occupy?

8 bytes as the sign will remain as 1 byte due to trailing separate.

Simple android program using onTouchEvent

This is the MainActivity which calls the TouchToView class,

package com.example.paintouch;

import android.app.Activity;
import android.os.Bundle;


public class MainActivity extends Activity {

    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
     //   setContentView(R.layout.activity_main);
        setContentView(new TouchToView(this));
    }

}

Here we have used onTouchEvent which will capture the motion of your finger when touching the screen and also we have defined the Paint and Path with its style, colour and width to get displayed.
The X and Y axis will be mapped with using the paint and path.

package com.example.paintouch;

import android.annotation.SuppressLint;
import android.content.Context;
import android.graphics.Canvas;
import android.graphics.Color;
import android.graphics.Paint;
import android.graphics.Paint.Join;
import android.graphics.Paint.Style;
import android.graphics.Path;
import android.view.MotionEvent;
import android.view.View;

public class TouchToView extends View{
private Paint alphanum = new Paint();
private Path trace = new Path();

public TouchToView(Context context) {
super(context);
// TODO Auto-generated constructor stub
alphanum.setAntiAlias(true);
alphanum.setStrokeWidth(6f);
alphanum.setColor(Color.RED);
alphanum.setStyle(Style.STROKE);
alphanum.setStrokeJoin(Join.ROUND);
}

protected void onDraw(Canvas canvas) {
canvas.drawPath(trace, alphanum);
}

@SuppressLint("ClickableViewAccessibility")
public boolean onTouchEvent(MotionEvent event) {
Float eventX = event.getX();
Float eventY = event.getY();

switch (event.getAction()) {
case MotionEvent.ACTION_DOWN:
trace.moveTo(eventX, eventY);
break;
case MotionEvent.ACTION_MOVE:
trace.lineTo(eventX, eventY);
break;
case MotionEvent.ACTION_UP:
// trace.lineTo(eventX, eventY); do nothing
break;
case MotionEvent.ACTION_OUTSIDE:
// trace.lineTo(eventX, eventY); do nothing
break;
default:
return false;
}
invalidate();
return true;

}

}

How to find special character in String using COBOL program

How to check special character in a string and replace with spaces using COBOL program.


01  TEMP-VAR                            PIC X(10) VALUE " INS#PECT ".
01  TEMP-VAR-LEN                   PIC 9(02) VALUE ZERO.
01  TEMP-VAR-COUNT             PIC 9(02) VALUE ZERO.
01  TEMP-CHAR-COUNT          PIC 9(02) VALUE ZERO.
01  TEMP-CHAR                         PIC X(10) VALUE SPACES.
01  TEMP-CHK-VAR                  PIC X(10) VALUE SPACES.


MOVE LENGTH OF TEMP-VAR TO TEMP-VAR-LEN.      

MOVE 0                   TO TEMP-VAR-COUNT.      
      
PERFORM VARYING TEMP-CHAR-COUNT FROM 1 BY 1                
            UNTIL TEMP-CHAR-COUNT > TEMP-VAR-LEN        

  MOVE  TEMP-VAR(TEMP-CHAR-COUNT:1) TO TEMP-CHAR  

  ADD 1                  TO TEMP-VAR-COUNT                

  EVALUATE TEMP-CHAR                                  
     WHEN ‘#’                                  
        MOVE SPACES TO TEMP-CHG-VAR(WS-VAR-COUNT:1)
     WHEN OTHER
        MOVE TEMP-CHAR TO TEMP-CHK-VAR(WS-VAR-COUNT:1)
  END-EVALUATE

END-PERFORM.


Here the logic is:
To get the length of the string and perform until the complete string has been checked, each word is checked using the EVALUATE and if the special character is found it will be replaced with spaces or the same character will be moved into the variable using reference modification. 

How to remove spaces in a String

How to remove front and back spaces in a variable and write it in middle of two variable,

01  VAR-SPACE            PIC X(10) VALUE '   DATA   '.
01  LEAD-SPACE         PIC 9(04)  COMP.
01  TRAIL-SPACE        PIC 9(04)  COMP.
01  LENG-SPACE         PIC 9(04)  COMP.
01  FIRST-VAR             PIC X(07) VALUE 'VALID -'.
01  TRAIL-VAR            PIC X(10) VALUE '- REQUIRED'.
01  FINAL-VAR            PIC X(21) VALUE SPACES.


INSPECT              VAR-SPACE
      TALLYING     LEAD-SPACE          
      FOR LEADING SPACES    
                 
INSPECT    FUNCTION REVERSE(VAR-SPACE)
     TALLYING    TRAIL-SPACE          
     FOR LEADING SPACES      
                
COMPUTE  SPACE-LENG =                        
                  LENGTH OF  VAR-SPACE - LEAD-SPACE - TRAIL-SPACE     
          
ADD    1                    TO   LEAD-SPACE    

STRING FIRST-VAR DELIMITED BY SIZE  
       VAR-SPACE (LEAD-SPACE:SPACE-LENG)
                                DELIMITED BY SIZE
       TRAIL-VAR   DELIMITED BY SIZE  
       INTO FINAL-VAR.    

DISPLAY "The result will be: " FINAL-VAR.


The result will be: VALID -DATA- REQUIRED.

Here the spaces which are present in front and back of 'VAR-SPACE' variable has to be removed.
So we have used INSPECT function to check for number of spaces in front and back using TALLYING function.
Then using reference modification only the DATA is moved after removing the leading and trailing spaces.

How to check whether a file is empty using JCL?

We can check whether a file is empty using the below methods,

USING IDCAMS

//STEP01 EXEC PGM=IDCAMS                          
//SYSPRINT DD  SYSOUT=*                            
//SYSOUT   DD  SYSOUT=*                            
//DDIN     DD  DSN=&TEST.DATA.FILE,DISP=SHR
//SYSIN    DD*
    PRINT INFILE(DDIN) COUNT(1) CHAR
/*    


IF RETURN CODE IS EQUAL TO 4 THE FILE IS EMPTY


USING SELCOPY

//STEP01 EXEC PGM=SELCOPY                          
//SYSPRINT DD  SYSOUT=*                            
//SYSOUT   DD  SYSOUT=*                            
//DDIN     DD  DSN=&TEST.DATA.FILE,DISP=SHR
//DDOUT    DD  DUMMY
//SYSIN    DD*
     READ DDIN
     WRITE DDOUT
/*    

IF THE RETURN CODE IS = 16 THEN THE FILE IS EMPY

USING ICETOOL

//STEP01 EXEC PGM=ICETOOL                          
//TOOLMSG  DD  SYSOUT=*                            
//DFSMSG   DD  SYSOUT=*                            
//DDIN     DD  DSN=&TEST.DATA.FILE,DISP=SHR
//TOOLIN   DD  DATA
     COUNT FROM(DDIN) EMPTY
/*    

IF THE RETURN CODE IS = 0 THEN THE FILE IS NOT EMPY ELSE EMPTY


USING IKJEFT01

//STEP01  EXEC PGM=IKJEFT01,DYNAMNBR=25

COUNTS THE NUMBER OF RECORDS IN THE FEED FILE

IF RETURN CODE IS LESS THEN 4 THE FILE IS EMPTY ELSE THE FILE HAS DATA.

COND in JCL

COND=(0,EQ) OR COND=(0,EQ,STEP01) OR COND=ONLY OR COND=EVEN

0             = RETURN CODE 
EQ         = LOGICAL OPERATOR (WE CAN GIVE CONDITIONS LIKE (GT - GREATER THEN,                  LT - LESSER THEN, GE - GREATER THEN OR EQUAL TO, LE - LESSER THEN OR                  EQUAL TO, EQ - EQUAL TO, NE - NOT EQUAL TO)
STEP01 = STEP NAME WHICH RETURN CODE HAS TO BE CONSIDERED.


EQUAL TO & NOT EQUAL TO

Eg:
//STEP01  EXCE  PGM=IKJEFT01
//
//STEP02  EXEC  PGM=IDCAMS,     
//              COND=(0,EQ,STEP01)


HERE THE STEP02 WILL BE BY PASSED IF THE STEP01 ENDS WITH RETURN CODE 0
IF THE CONDITION IS TRUE THEN THE STEP WILL GET BY PASSED IF THE CONDITION IS FALSE THEN THE STEP WILL GET EXECUTED.

Eg:
//STEP01  EXEC  PGM=IKJEFT01
//
//STEP02  EXEC  PGM=IDCAMS,     
//              COND=(0,NE,STEP01) 

HERE THE STEP02 WILL GET EXECUTED IF THE STEP01 ENDS WITH RETURN CODE > 0

LESS THEN & GREATER THEN

Eg:
//STEP01  EXEC  PGM=IKJEFT01
//
//STEP02  EXEC  PGM=IDCAMS,     
//              COND=(04,LT,STEP01)


HERE THE STEP02 WILL BE BY PASSED IF THE STEP01 ENDS WITH RETURN CODE 0
IF THE CONDITION IS TRUE THEN THE STEP WILL GET BY PASSED IF THE CONDITION IS FALSE THEN THE STEP WILL GET EXECUTED.

Eg:
//STEP01  EXEC  PGM=IKJEFT01
//
//STEP02  EXEC  PGM=IDCAMS,     
//              COND=(0,GT,STEP01) 

HERE THE STEP02 WILL GET EXECUTED IF THE STEP01 ENDS WITH RETURN CODE < 0

EVEN & ONLY
Eg:
//STEP01  EXEC  PGM=IKJEFT01
//
//STEP02  EXEC  PGM=IDCAMS,     
//              COND=EVEN 

HERE THE STEP02 WILL GET EXECUTED EVEN IF THE PREVIOUS STEP ABENDS OR SUCCESSFUL

Eg:
//STEP01  EXEC  PGM=IKJEFT01
//
//STEP02  EXEC  PGM=IDCAMS,    
//              COND=ONLY 

HERE THE STEP02 WILL GET EXECUTED ONLY IF THE PREVIOUS STEPS ABENDS

Overriding only one dataset in 3 concatenated datasets

Mention the override step with specific DD names with spaces for which we are not going to specify with any overrides from JCL.

For eg:

//STEP1.PRC   DD DSN=
//DDF1              DD DSN=MLOGI.DATA.FILE2,DISP=SHR
//DDF2              DD DSN=

Do not specify DD names with DUMMY as the datasets will be considered as DUMMY.

For eg:

//STEP1.PRC   DD DUMMY
//DDF1              DD DSN=MLOGI.DATA.FILE2,DISP=SHR
//DDF2              DD DUMMY