Thursday 14 May 2020

DB2 data types


DATE:

The ISO format date in DB2 is CCYY-MM-DD in the range of 0001-01-01 to 9999-12-31.

COBOL host variable equivalent: PIC X(10)

DB2 supports ISO data format CCYY-MM-DD.

Example:

CREATE TABLE reading_lists(
     user_id INT NOT NULL,
    book_id INT NOT NULL,
    added_on DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY(user_id, book_id)

);

INSERT INTO reading_lists(user_id, book_id) VALUES(1,1);

Converting COBOL field data to DB2 host variable format:

Example: 20200514

01 WS-DATE.
   05 WS-YEAR     PIC X(04).
   05 FILLER           PIC X VALUE ‘-‘.     
   05 WS-MONTH     PIC X(02).
   05 FILLER           PIC X VALUE ‘-‘.
   05 WS-DAY        PIC X(02).
   05 FILLER           PIC X VALUE ‘-‘.

MOVE 2020 TO WS-YEAR
MOVE 05     TO WS-MONTH
MOVE 14     TO WS-DAY

MOVE WS-DATE TO WS-HOST-VARIABLE

When date is 0, we need to insert NULL value in date field. Then please code like below

IF WS-DATE = ZEROS
    MOVE ‘0001-01-01’    TO WS-HOST-VARIABLE  => Moving null

TIME:

The ISO format time in DB2 is HH.MM.SS in the range of 00.00.00 to 24.00.00

Example:

CREATE TABLE daily_routines(
    routine_id INT
    GENERATED BY DEFAULT AS IDENTITY 
    NOT NULL PRIMARY KEY,
    routine VARCHAR(100) NOT NULL,
    start_at TIME NOT NULL
);

INSERT INTO daily_routines(routine, start_at) VALUES
    ('Get up','06:00'),
    ('Brush your teeth','06:05'),
    ('Have breakfast','06:15'),
    ('Go to school','06:45'),
    ('Go home','17:00');

COBOL host variable equivalent: PIC X(8).

01 WS-TIME.
   05 WS-HOURS         PIC X(02).
   05 FILLER           PIC X VALUE ‘.‘.     
   05 WS-MINUTES       PIC X(02).
   05 FILLER           PIC X VALUE ‘.‘.
   05 WS-SECONDS        PIC X(02).
   05 FILLER           PIC X VALUE ‘.‘.

Converting COBOL field data to DB2 host variable format:

After moving time to ws-time, then

MOVE WS-DATE TO HOST-VARIABLE

TIMESTAMP:

The ISO format time in DB2 is HH.MM.SS in the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000

Example:

CREATE TABLE logs(
    log_id INT GENERATED ALWAYS AS IDENTITY NOT NULL,
    message VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(log_id)
);


INSERT INTO logs(message)
VALUES('Testing timestamp');

COBOL host variable equivalent: PIC X(26).

VARCHAR:

This data type in db2 is used to store VARIABLE-LENGTH character strings.

Syntax:  column-name VARCHAR(n)

'n' is positive integer which represents maximum length of bytes a column can store. It should be greater than 0 and less than 32740.

Example:

CREATE TABLE db2_varchars (
     v VARCHAR(20) NOT NULL
);

INSERT INTO db2_varchars (v) VALUES ('Db2 Tutorial');

Converting COBOL field data to DB2 host variable format:

VARCHAR field representation in COBOL (provided by DCLGEN) is like below (COBOL host variable equivalent)

01 column-name.
    49 column-len                  pic S9(4) comp.
    49 column-text                 pic x(n).

How to move data to a varchar field in DB2 from COBOL:

Rules: 

o   Remove padded spaces from the text
o   Populate the actual length of the text

Example:

77  WS-INDI             PIC X(7).
77  WS-INDCTR           PIC S9(4) COMP.
77  WS-ACT-INDI         PIC X(7).

MOVE 'A Z H' TO WS-INDI
INSPECT FUNCTION REVERSE(WS-INDI) TALLYING WS-INDCTR
                 FOR LEADING SPACES  => COUNT THE LEADING SPACES 
DISPLAY 'WS-INDCTR1: ' WS-INDCTR                        
SUBTRACT WS-INDCTR FROM 7 GIVING WS-INDCTR => SUBTRACTING LEADING SPACES FROM THE TOTAL LENGTH AND STORE THE ACTUAL LENGTH (ACTUAL LENGTH-LEADING SPACES)
DISPLAY 'WS-INDCTR2: ' WS-INDCTR        
MOVE WS-INDI(1:WS-INDCTR) TO WS-ACT-INDI => MOVING ACTUAL VALUE WITHOUT LEADING SPACES TO HOST-VARIABLE            
DISPLAY 'WS-ACT-INDI: ' WS-ACT-IND

Output:

WS-INDCTR1: +0002
WS-INDCTR2: +0005
WS-ACT-INDI: A Z H

SMALLINT:

To store small integers in 2 bytes.

Syntax: col-name SMALLINT

COBOL host variable equivalent: S9(4) COMP.

INT:

To store large integers in 4 bytes.

Syntax: col-name INT

COBOL host variable equivalent: S9(9) COMP.

BIGINT:

To store big integers in 8 bytes.

Syntax: col-name INT

COBOL host variable equivalent: S9(18) COMP.

Example:

CREATE TABLE db2_integers( 
             smallint_col SMALLINT
             int_col INT
             bigint_col BIGINT );

db2_integers represents table-name

INSERT INTO db2_integers (
    smallint_col, 
    int_col, 
    bigint_col )
VALUES (
    32767,
    2147483647,
    9223372036854775807
);

CHAR:

To store FIXED-LENGTH character strings in database.

Syntax: col-name CHAR(n)

COBOL host variable equivalent: PIC X(n)

Example:

CREATE TABLE db2_characters(
    char_col CHAR(3)
);

INSERT INTO db2_characters(char_col)VALUES('abc');

DECIMAL:

To store decimal type values in database.

The decimal number consists of two parts viz. the whole part and fractional part. They both are separated by comma.

Syntax: dec_col DECIMAL(m,n) or
        dec_col NUMERIC(m,n)

‘m’ represents whole part, ‘n’ represents fractional part.

ex. DECIMAL(5,3) => 12.345

Example:

CREATE TABLE db2_decimals(
    dec_col NUMERIC(5,3)
);

INSERT INTO db2_decimals(dec_col)
VALUES(12.345);

COBOL host variable equivalent: S9(m-n)V(n) COMP-3




Thursday 2 May 2019

How to check the limit of GDG file

Goto 3.4 and type in the GDG name. Press Enter.
Then where u give B for Browse , E for Edit

type in LISTCAT ENT(/) ALL and press ENTER.

Or

The ISPF Dataset Utility (=3.2) can generate the LISTCAT command for you and then execute it.

Take option 3.2
Enter the name of the GDG base and take option V (VSAM Utilities).
Under "Process Request" take option 3. Under "Data Type" you can leave it blank or take option 4 and hit enter.
On the next screen make sure there is a (/) slash next to "Edit IDCAMS command". Under "Name, History, Volume, ALLOcation, All" put ALL and hit enter.
This will generate a LISTCAT command essentially the same as used above:
/* IDCAMS COMMAND */
LISTCAT ENTRIES(ZZJR001.TEMP.GDG) -
GENERATIONDATAGROUP -


Then enter the EXEC command to execute it.

Or

Go to command prompt and type 
TSO LISTC ENT('GDG-BASE-NAME') ALL

Alter GDG file

The maximum limit of a GDG file is 255 generations.

Say, you have created a GDG base with the limit of 50 generations and later you want to increase its limit to maximum (255 generations), then you need to Alter GDG base like below.

//STEP01 EXEC PGM=IDCAMS      
//SYSPRINT DD SYSOUT=*        
//SYSIN    DD *               
  ALTER GDG.BASE.NAME LIMIT(255)
/*

Once the above job is successful, the limit of GDG file GDG.BASE.NAME will be increased from 50 to 255.

Friday 15 February 2019

Removing duplicates from VB file


Consider I have a VB file of length 321 bytes (4 for RDW + 317 actual length) and wanted to remove duplicate records from a file.

Here is the sample JCL: 

//REMODUPL   EXEC PGM=SORT                                     
//SORTIN   DD DSN=INPFILE1,DISP=SHR                 
//SYSIN    DD *                                                
  OPTION VLSHRT                                                
  SORT FIELDS=(5,317,CH,A)                                     
  SUM FIELDS=NONE                                              
/*                                                             
//SORTOUT  DD DSN=OUTFILE1,DISP=(NEW,CATLG,DELETE),           
//         RECFM=VB,LRECL=321,AVGREC=K
//SYSOUT   DD SYSOUT=* 

VLSHRT tells DFSORT to temporarily replace any missing control field bytes with binary zeros.

For eg.       

As you are aware that VB file will have records of different lengths and the maximum length of the record in the VB file will be treated as LRECL of that VB file.

During the sort say i have two records one with length 300 and another with 317, then the record with 300 bytes will be temporarily replaced with extra 17 binary zeros (300+17 binary zeros) to run the sort smoothly.  These binary zeros are temporary and will NOT be copied to output dataset. 

The default OPTION is NOVLSHRT (reverse of VLSHRT) which terminates the sort if it finds shorter record than the one specified in SORT FIELDS.          

Sunday 13 January 2019

SDSF

SDSF means System Display and Search Facility

It is a utility that allows user to monitor, control or view the output of jobs in the system.

It is a component of IBM's mainframe operating system, z/OS.

Once the job is submitted, it is common to use SDSF to check the status of the job like completed/running/failed...

To start using SDSF, 

type SDSF;ST or S;ST on the start window/command line

ST :Displays current status of all the jobs

Following are the most commonly used SDSF options:

DA : Displays Active/currently running jobs

I : Input Queue - Shows jobs waiting for execution 

H : Jobs on HOLD - either waiting to be released into input or output.

PR : Displays printers

INIT : Displays initiators (areas where jobs execute or run)

On the ST panel, SDSF supports following options for each batch Job. The user should type the desired option against the job and press enter to see the result

C : Cancel the job

S : Select the job (view only)

SJ : Show Job (View the original JCL of the job) - In this mode, you can edit the JCL as you required and SUBmit it in case of failures.

P : Purge job (Remove the job)

SE : Select Edit job (view in edit mode) 

XDC : Writes the spool content of the job into a data set. The data set name should be provided in the dialog window that gets popped up after pressing enter.

Following operations can be performed on SDSF panel. The desired command should be provided in "COMMAND INPUT" and press enter 

OWNER * - Displays all the jobs submitted by the owner if any.

OWNER ABC* - Filter jobs with owner name starting with ABC

PRE XYZ* - Filter jobs starting with XYZ.
PRE represents PREFIX

PRE AB%%PQ - Filter jobs starting with AB and ends with PQ

WHO - Provides basic information about SDSF user

SET CONFIRMATION ON/OFF - This command will enable/disable the confirmation for any action like P(Purge), C(Cancel)….

In SDSF, we can purge multiple jobs at a time using Block //. The jobs to purge should be in sequence to perform the same.
How - Say I have jobs JOB1, JOB2, JOB3, JOB4, JOB5 in SDSF. To purge JOB3, JOB4 and JOB5 at a time, Type //p against JOB3 and // against JOB5 and press enter.


Friday 4 January 2019

IEBEDIT(Edit Job stream) Utility:

1. It is used to run selected job step(s) in particular JCL.

Ex. I have a JCL with 10 steps and wanted to run only STEP10, 


//IEBEDITX JOB (MVSQuest),'IEBEDIT TEST',
//            CLASS=N,MSGCLASS=H,NOTIFY=7SYSUID
//*
//SUBMIT   EXEC PGM=IEBEDIT
//SYSUT1   DD DSN=USERID.TEST.JCL(JCLINP),DISP=SHR
//SYSUT2   DD SYSOUT=(*,INTRDR)
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
 EDIT START=JOBA,TYPE=INCLUDE,STEPNAME=(STEP10)
//*

JCLINP contains 10 steps

START => specifies job name of JCLINP

TYPE=INCLUDE => runs only those steps specified in STEPNAME

If TYPE=EXCLUDE, it runs all the steps except for the one specified in STEPNAME

If i want to run step09 and step10, then

EDIT START=JOBA,TYPE=INCLUDE,STEPNAME=(STEP09,STEP10)

2. Copies the complete job including all its steps to the output data set
    
    EDIT START=JOBA

3. Copies different steps from different jobs to output data set
    
    Example: If i have 3 jobs (JOBA, JOBB,JOBC)  in input dataset and wanted to copy different steps from all the 3 jobs

EDIT START=JOBA,TYPE=INCLUDE,STEPNAME=(STEPC,STEPD)
EDIT START=JOBB,TYPE=INCLUDE,STEPNAME=STEPE
EDIT START=JOBC,TYPE=INCLUDE,STEPNAME=STEPJ   

Points to remember:


Instream procedure should be defined before EXEC statement.

Example:

//INSTPROC  PROC
//    statements
//    ----
//    ----
//          PEND
//STEP01 EXEC PGM=pgm1
//FILE1  DD DSN=file-name
//STEP02 DD INSTPROC
//STEP03 DD INSTPROC

Cataloged Procedure:

Cataloged procedures will be stored in separate PDS. This PDS name should be specified in JCLLIB ORDER. If the procedure is not found in the specified library, then SYS1.PROCLIB will be checked.

We can add/modify the parmeters in steps of Cataloged procedure without even changing it. 

Ex. I have a cataloged proc name MYPROC and wanted to add Region parameter to STEP02 in that
proc, i can code like below in corresponding JCL

//MYSTEP EXEC MYPROC, REGION.STEP10=56K

To apply region parameter for all the steps in MYPROC

//MYSTEP EXEC MYPROC, REGION=56K

To nullify a parameter of a particular step in MYPROC, override the same through JCL and just don't give any value like below

//MYSTEP EXEC MYPROC, TIME.STEP10=

- The DSN and UNIT parameters must be coded for new generation data sets.