DIRECT YES:
When we use DIRECT YES option, BMC Unload Plus utility (ADUUMAIN) reads data directly from the table space data set or image copy data set and uses a SELECT-like syntax for data selection.
We can unload all data from a table-space without requiring written SELECT statements.
The data can be sorted by clustering key or partitioning key.
DIRECT NO:
When we use DIRECT NO option, BMC Unload Plus utility uses DB2 dynamic SQL to process the SELECT statement.
Data type conversions can be performed using DIRECT NO (which is not available with DIRECT YES).
STRING WS-VAR1 ',' WS-VAR2 DELIMITED BY SIZE INTO WS-STRING
UNSTRING ws-comma-separated
DELIMITED BY ','
INTO WS-parm1
, WS-parm2
, WS-parm3
, WS-parm4
, WS-parm5
END-UNSTRING
- DIS or -Display : Display a status of tablespace (ie READ-WRITE / COPY-PENDING / CHECK-PENDING / READ ONLY)
-DIS DATABASE(dbname) SPACENAM(tbspace)
more on Display Database
Stop / Start a tablespace / indexspace:
-STOP DATABASE(dbname) SPACENAM(tbspace)
-START DATABASE(dbname) SPACENAM(tbspace) ACCESS(FORCE)
Start: publib.boulder.ibm.com/../bjncstdb999243.htm
Stop: publib.boulder.ibm.com/../bjncstod999243.htm
How to find a table’s Database name and tablespace name?
SELECT DBNAME, TSNAME FROM
SYSIBM.SYSTABLES WHERE NAME = [table name]
AND OWNER = [qualifier] ;
SELECT DBNAME, TSNAME FROM
SYSIBM.SYSTABLES WHERE NAME = 'SYSDUMMY1' AND
OWNER = 'SYSIBM';
Terminating a BMC (or any DB2) utility:
issue the Terminate Utility command:
-TER UTILITY([util id])
TERM: publib.boulder.ibm.com/../ctermu.htm
Symbolic parameters used in JCL can be assigned a value using the SET statement.
// SET INFILE=MY.DATA.SET.HERE
You can also give an eight character (upto 8 char) name to this statement.
//MYVARS SET INFILE=MY.DATA.SET.HERE
Multiple variables can be defined in a SET statement – separated by comma.
//MYVARS SET INFILE1=MY.DATA.SET.HERE,PARM1='KARTHIK'
Learn more
Let’s say we have a file with first byte having 2 different values ‘A’ or ‘B’ or any other character.
We are going to create a file with all records starting with A (DD name SORTXXA), a second file with all records starting with B (DD name SORTXXB), a third file (DD name SORTXXX) with remaining records.
SORT FIELDS=COPY
OUTFIL FNAMES=SORTXXA,INCLUDE=(1,1,CH,EQ,C'A')
OUTFIL FNAMES=SORTXXB,INCLUDE=(1,1,CH,EQ,C'B')
OUTFIL FNAMES=SORTXXX,SAVE
SYMNAMES is a means of defining variables in SORT. When you have a complex sortcard, it becomes hard to have your sort control card ‘readable / understandable’. It is a good practice to always use SYSNAMES DD to define variables and then use the logical variable names in Sort card.
Below is an example with the SYMNAMES DD (variables declared) and SYSIN (the ‘sort control card’).
//SORTSTEP EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DISP=SHR,
// DSN=MYDSN.NAME
//SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,2000)
//SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,2000)
//SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,2000)
//SYMNAMES DD *
Name,1,10,CH
Age,11,2,ZD
/*
//SYSIN DD *
SORT FIELDS=(Age,D)
INCLUDE FILEDS=(Age,GT,25)
/*
//SORTOUT DD DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(250,100),RLSE),
// UNIT=DISK,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=0),
// DSN=MYDSN.OUTPUT.NAME
read more on SYMNAMES
Updating/ REWRITING a VSAM record:
VSAM declaration should look like,
FILE2 is the DD name – must have DISP=OLD
Reading VSAM file by Key and updating the record
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
LIST ON
FILE FILE1 FB(80 200)
IN-REC 1 80 A
IN-KEY 1 8 A
FILE FILE2 VS UPDATE
F2-REC 1 18 A
F2-KEY 1 8 A
F2-VALUE 9 10 N
JOB INPUT NULL
GET FILE1
DO WHILE NOT EOF FILE1
READ FILE2 KEY IN-KEY STATUS
IF FILE2 : FILE-STATUS EQ 0
F2-VALUE = F2-VALUE + 5
WRITE FILE2 UPDATE
ELSE
DISPLAY IN-KEY ' NOT PRESENT IN VSAM FILE'
END-IF
GET INFILE
END-DO
STOP
//STEP010 EXEC PGM=SORT
//SORTIN DD DISP=SHR,DSN=my.file
//SORTOUT DD DISP=(,CATLG,DELETE),
//DSN=new.file,
//LIKE=my.file
//SYSIN DD *
SORT FIELDS=COPY
OUTFIL REMOVECC,
TRAILER1=(C'COUNT:',COUNT-1=(EDIT=(TTTTTTTT)))
/*
Delete a VSAM file (Cluster):
//STEP01 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
DELETE my.vsam.file CLUSTER
/*
