The Activity section is where all of the actual processing takes place. The activity definition section of an Easytrieve program contains the CA-Easytrieve Plus statements that perform the tasks for which the program was created: reading in, processing, and writing out data.

The JOB Statement

As stated before, this should not be confused with the JOB card in JCL, the JOB statement in EasyTrieve identifies that the Library section has finished and that processing statements will follow.

There are 3 frequent parameters to the job statement, they are FILE, START and FINISH. An example of the JOB statement is shown below:-

001840 *

001900 JOB INPUT ( DUNS ) +

002000 START ( A-INITIALISE ) +

002100 FINISH( B-TERMINATE )

002200 *

The INPUT Parameter

As the name implies, this parameter identifies all of the files which are to be read into the process. It is possible to identify more than 1 file as input.

If there are no files being used as input then INPUT( NULL ) should be entered.

When a file name is supplied to the INPUT statement, the file is automatically opened and the first record is read in as the job starts. Thereafter, every time the logic flow of the module returns to the job statement (either by being specifically directed there with a GOTO command or because the logic has reached the end of the module), the next record on the file will be read in and processed. This happens until the last record on the file has been processed, at which point the processing stops and the FINISH parameter, if present, is processed.

The START Parameter

This parameter contains the name of a PROC (PROC is the name for an EasyTrieve subroutine, these are detailed later) which is to be run before any other processing starts.

This routine will only run once (unless specifically called later in the Activity section) and is useful to initialise variables before any ‘real’ processing starts.

If there is no procedure to run, this parameter may be omitted.

The FINISH Parameter

As with the start routine, the PROC defined by the FINISH parameter will be run once, at the end of the processing activity.

It is usually used to return information to the user regarding how many records were read in and processed.

It may be omitted if not required.

The DISPLAY Statement

This is the means by which the EasyTrieve module communicates with the user. Any information output by a DISPLAY command will appear in the data set identified by the DD name SYSPRINT in the JCL. The format of the command is as follows:-

DISPLAY [LITERAL] [FIELD]

The DISPLAY statement may show one or more fields or literals on a line. Each DISPLAY command prints the following fields and literals on the same line until a new DISPLAY command is found.

Exercise 1 - Hello World

As with learning any new language, the first exercise is to get the language communicating with the outside world.

Write an EasyTrieve module to display ‘HELLO WORLD’ surrounded by a box of asterisks.

There is no input file to be processed.

To perform this it will be necessary to create an EasyTrieve module and the JCL required to run it.

Reading And Writing Files

As has been previously stated, adding a file name to the INPUT parameter of a JOB statement causes records to be read in one at a time whenever the logic flow returns to the top of the job. This is a quick and effective method of getting information into the module. Passing processed records out of the job ready for the next process is just as simple.

To write a record to an output file, use the following statement:-

PUT FILENAME

Note that the FILENAME does not have to be defined as an output file anywhere else except for in the processing.

Exercise 2 - File Reformatting

Create an EasyTrieve module to perform the following:-

Read records from the data set ‘UEOE.VA00835.FILE0001.TAPEDATA’, records are 282 bytes long and contain alpha numeric data.

Each record contains 2 records (each 141 bytes long) which are presented side by side, split the incoming record into two records and write them to a dataset called USER‑ID.EASY.TRAINING.EX2.

Print control totals of the number of records read in and written out.

Assignment Statements

EasyTrieve has two methods of assigning values to variables. The first method used to initialise fields, while the second allows data to be manipulated before moving into the required fields.

The MOVE Statement

This is used to initialise fields before their use, it is possible to fill the field with either SPACES or ZEROS. The receiving field must be of the appropriate type to accept the characters with which it is to be initialised.

An example of the use of the MOVE statement is given below:-

FILE OTFILE

OT-RCD 1 310 A

MOVE SPACES TO OT-RCD

Arithmetic Functions

Arithmetic functions are available on all numeric type fields in EasyTrieve, the table below shows the functions available and their identifiers:-

FUNCTION	IDENTIFIER
Addition	+
Division	/
Multiplication	*
Rounding	ROUNDED
Subtraction	-
Truncation	TRUNCATED

The format of an arithmetic operation is as follows:-

FIELD [ROUNDED/TRUNCATED] = FIELD/LITERAL/ARITHMETIC-EXPRESSION

Arithmetic expressions are associative in the normal manner (Division and Multiplication are processed before Addition and Subtraction, otherwise process from left to right), the order of processing can be changed with the use of brackets.

Exercise 3 - Performing Calculations

This exercise is designed to introduce the user to field initialisation and arithmetic calculations in EasyTrieve and practise sorting methods in JCL.

File ‘UEOE.TRAINING.SALES.K350TOM1’ contains 7,664 records of length 376, bytes 263 to 311 contains sales data from the last 3 years as follows:-

START	LENGTH	FIELD-ID
263	15	SALES 1 YEAR AGO
279	15	SALES 2 YEARS AGO
295	15	SALES 3 YEARS AGO

Produce JCL and EasyTrieve to perform the following,

1	Calculate the percentage change (increase or decrease) in sales between 1 and 2 years ago and between 2 and 3 years ago.

	Percentage change is defined as ( NEWEST SALES - OLDEST SALES ) / OLDEST SALES * 100.

	Output each input record in full to the output record and add both calculations in the following formats to the end of the file
	Numeric whole numbers only
	Numeric to 2 decimal places

	At the end of the process display a message stating the number of records read in, written out and the count of each type of calculation which has been successfully completed.

2	Sort the resulting file on the Numeric to 2 decimal places field descending.

PDS members should be called UK280PC, output files should begin USER‑ID.UK96280.

Conditional Functions

There are two conditional functions in EasyTrieve, both are reasonably common and their syntax is detailed below:-

IF... THEN... ELSE... END-IF

The IF... THEN... ELSE... END-IF statement is common to most languages and it’s use in EasyTrieve is no different. The IF statement is followed by a statement which can be resolved to true or false. If the statement is true, then commands following are processed. If the statement resolves to false, the statements following the ELSE statement are performed.

The THEN keyword is optional is usually omitted. It is not mandatory to have an ELSE statement, if there is no ELSE statement, the keyword must also be omitted.

However, it is mandatory to have the END-IF keyword to identify the end of the IF construct.

An example of the IF... THEN... ELSE... END-IF is shown below:-

IF ( IN-OOB-IND NOT SPACE ) +

OR ( IN-RPT-TYP = ' ' 'B' )

DISPLAY 'NOT REQUIRED TYPE - ' IN-DUNS

W10-WRONG-TYPE = W10-WRONG-TYPE + 1

GOTO JOB

END-IF

As can be seen, more than one statement can be used in the IF statement, combined using AND and OR commands.

When an ELSE statement is introduced, the statement looks like this:-

IF W30-UPD-DATE-YY > 80

EX-UPD-DATE-CC = 19

ELSE

EX-UPD-DATE-CC = 20

END-IF

The valid relational operators for use in the comparison statements are as follows:-

OPERATOR	RESULT
= or EQ	Is Equal To
¬= or NE	Is Not Equal To
> or GT	Is Greater Than
< or LT	Is Less Than
>= or GE	Is Greater Than Or Equal To
<= or LE	Is Less Than Or Equal To

Using a statement such as IN-RPT-TYP = ' ' 'B' adds an implicit ‘or’ between the last operands and is the equivalent of IN-RPT-TYP = ' ' OR IN-RPT-TYP = 'B'.

The keyword THRU can be used in an IF statement to identify a range of values which are to be selected, as follows:-

IF W20-REG-DATE = 19941001 THRU 19950930

OK-DUNS = CF-DUNS

OK-REG-DATE = W20-REG-DATE

PUT OKDUNS

W10-OK = W10-OK + 1

ELSE

D2-DUNS = CF-DUNS

D2-REG-DATE = CF-REG-DATE

PUT DRPDUNS2

W10-NOT-OK = W10-NOT-OK + 1

END-IF

Finally, it is possible to perform validation on the contents of fields before processing them. This can be used to ensure that expected fields contain numeric data, or to check that a field contains all spaces before processing it. Validation statements are shown below, followed by examples of their use.

VALIDATION	EFFECT
ALPHABETIC	Entire field contains characters in the range ‘A’ to ‘Z’
NUMERIC	Field contains a valid number
SPACE or SPACES	Entire field contains spaces
ZERO or ZEROS or ZEROES	Entire field contains all ‘0’ characters

IF IN-EMPL-C (WS-IN-SUB) NOT SPACES +

AND IN-EMPL-C (WS-IN-SUB) NUMERIC

WS-EMPLOYEES-C (WS-OUT-SUB) = IN-EMPL-N (WS-IN-SUB)

WS-OUT-SUB = WS-OUT-SUB - 1

END-IF

The CASE Statement

The CASE statement is similar to that used in COBOL and C, a single field is scrutinised and depending on its value various actions may be performed. This statement will be used in place of a series of IF statements against a single field.

The format of the statement is as follows:-

CASE FIELD-NAME

WHEN COMPARE-EXPRESSION-1

STATEMENTS-1

WHEN COMPARE-EXPRESSION-2

STATEMENTS-2

OTHERWISE

STATEMENTS-3

END-CASE

Each COMPARE-EXPRESSION may be a constant, a literal or a range of constants using the THRU keyword.

The OTHERWISE keyword will be processed if the logic reaches the OTHERWISE without finding a match on any of the WHEN statements. It is not mandatory and may be omitted if not required.

Note that there is no explicit terminator to the WHEN or OTHERWISE clauses. When a COMPARE-EXPRESSION is found to be true, the statements are processed until the next WHEN or OTHERWISE clause is encountered when processing immediately jumps to the END-CASE statement.

Use of the CASE statement is demonstrated below:-

CASE IN-EMPLOYEES

WHEN ' 0' THRU ' 200'

OT-EMPLOYEES = 'A'

WHEN ' 201' THRU ' 400'

OT-EMPLOYEES = 'B'

WHEN ' 401' THRU ' 800'

OT-EMPLOYEES = 'C'

WHEN ' 801' THRU ' 1200'

OT-EMPLOYEES = 'D'

WHEN ' 1201' THRU ' 1600'

OT-EMPLOYEES = 'E'

WHEN ' 1601' THRU ' 2000'

OT-EMPLOYEES = 'F'

OTHERWISE

OT-EMPLOYEES = 'G'

END-CASE

Exercise 4 - Conditional Expressions

File ‘UEOE.UK96120.CD360K.FULL.EXTRACT’ contains details of 368,426 records with the following layout:-

START	LENGTH	DESCRIPTION
1	4	Standard Industrial Classification Code (SIC)
5	9	Number Of Employees

Read each record on the file and count the number of SIC codes in each of the following bands; 0001 to 0999, 1000 to 1999, 2000 to 2999, 3000 to 3999, 4000 to 4999, 5000 to 6999 and ‘OTHERS’.

When the file is complete, display a report of the number of records processed and the number of cases in each group.

PDS members should be called UK120PC, output files should begin USER‑ID.UK96120.

Use the FIND ALL option in 3.4 of ISPF to check the results.

Loop Functions

There is a single statement which allows EasyTrieve logic to perform a loop, that is the DO... END-DO statement. There are two versions of this command, one which will guarantee at least one pass through the loop and one which will not.

The DO UNTIL Statement

This form of the statement guarantees that the processing of the loop will take place at least once by performing the terminating check at the end of the loop, after the first set of processing has taken place.

An example of the use of the statement is shown below:-

DO UNTIL ( W20-STRIP-COUNT > 30 ) +

OR ( W20-OUT-COUNT > 20 )

IF W20-STRIP-CHAR( W20-STRIP-COUNT ) ALPHABETIC

ND-REFMAT-CHAR( W20-OUT-COUNT ) = W20-STRIP-CHAR( W20-STRIP-COUNT )

W20-OUT-COUNT = W20-OUT-COUNT + 1

END-IF

W20-STRIP-COUNT = W20-STRIP-COUNT + 1

END-DO

In this example, as long as the conditional expressions are false (that is the, counter values are less than 30 and 20 respectively), the loop will continue to process.

The DO WHILE Statement

In contrast with the above statement, a DO WHILE loop checks the terminating criteria before the loop is processed, so it is possible to the loop to be processed zero times.

The use of this statement is demonstrated below:-

DO WHILE ( W20-CUSTOMER-COUNT LE 5 ) +

AND ( TI-CUSTOMER-NBR( W20-CUSTOMER-COUNT ) NOT SPACE )

MT-CUSTOMER-NBR = TI-CUSTOMER-NBR( W20-CUSTOMER-COUNT )

PUT MATCH

W10-OUT-MATCH = W10-OUT-MATCH + 1

W20-CUSTOMER-COUNT = W20-CUSTOMER-COUNT + 1

END-DO

In this example, the terminating condition is checked first and the loop is processed only if the conditions are met. As soon as one or more of the conditions are not met, the loop will stop processing.

As can be seen, the terminating criteria is formatted the same way as the condition in the IF statement and may be combined in the same manner.

Exercise 5 - Loops

File ‘UUKB.UK960230.BEMSALL’ contains 12,782 records with the following layout;

START	LENGTH	DESCRIPTION
1	9	DUNS Number
10	7	Index Number
17	6	Second Index Number
23	172	Name and Address
195	11	Post Code

Split the incoming file into 2. Output those records where the DUNS number is not spaces to one file. The other file is to contain the records where the DUNS is spaces, take the first part of the Post Code (up to the first space) and place the result in a new 4 byte field at the beginning of the record. Be aware that some Post Codes are missing, display the names of the businesses where no Post Code exists and a report on the number of cases read and the number of cases routed to each file.

PDS members should be called UK230PC, output files should begin USER‑ID.UK96230.

File Synchronisation

One of the major advantages of EasyTrieve over COBOL is it’s excellent file handling capabilities.

Many of the jobs involve extracting information from databases and merging it with data from supplied customer files. In COBOL, this is a major task, but the file synchronisation routines of EasyTrieve make it much easier.

Other tasks rely on deduplicating incoming files (to prevent duplication of work), this is also made much easier using EasyTrieve.

File synchronisation relies on two major factors, the incoming file(s) must be sorted into ascending order on the key which is to be used and the key fields must be contiguous in the file, that is the key fields must follow one after another with no intervening non-key fields.

File deduplication is the easier of the two actions to describe and this will be covered first. The statements which are used in file deduplication can also be used in file merging, but not vice versa.

File Deduplication

To define a file for deduplication, a single key field must be defined in the Library section for the file and the keyword KEY and the key field name must be added to the INPUT file name on the job statement. Once this has been successfully completed, the deduplication commands will be available. An example of the preparation of a file for deduplication is shown below:-

FILE CSFILE

CS-DUNS 1 9 N

SIC-1 10 4 A

SIC-2 14 4 A

JOB INPUT ( CSFILE KEY CS-DUNS ) +

FINISH( DISPTOTS )

MOVE SPACES TO OT-DATA

W10-IN = W10-IN + 1

IF NOT DUPLICATE CSFILE +

OR FIRST-DUP CSFILE

OT-DATA = OT-DUNS

PUT OT-FILE

ELSE

MOVE SPACES TO DUP-DATA

DUP-DATA = CS-DUNS

PUT DUPFILE

W10-DUPES = W10-DUPES + 1

END-IF

In the example, only one copy of the duns number is required on the output file but it is known that there are may be more then one occurrence of each duns on the incoming file.

Once the incoming file has been sorted into ascending duns number order, the EasyTrieve routine checks to see if each duns number on the file occurs only once (i.e. NOT DUPLICATE), in which case it is output straight away, or more than once, in which case only the first occurrence of the duns number (FIRST-DUP) is output.

The following statements are available when processing duplicates on a given file:-

STATEMENT	MEANING
DUPLICATE	Is there more than one occurrence of the key field on the file?
FIRST-DUP	If there is more than one occurrence of the key field on the file, is this the first such occurrence
LAST-DUP	If there is more than one occurrence of the key field on the file, is this the last such occurrence

File Merging

This function is similar to file deduplication, except that it allows the contents of 2 or more files to be combined based on a defined key field.

The key fields of all of the files are defined as before, each key field must be identical in type (numeric or alpha), but in the case of numeric keys it is possible to mix Binary and Packed Decimal layouts with Numeric fields.

Each file to be merged must be added to the INPUT parameter of the JOB statement in the same layout as for the deduplication process.

Note:

Only one of the input files may contain duplicate records based on the selected key, this must be placed last on the list of files defined on the INPUT files.

Once the files have been defined, the following commands are available:-

COMMAND	RESULT
FILE-NAME	Tests to see if there is a valid record from this file currently loaded
MATCHED FILE-1 FILE-2 [FILE-N]	Checks if the keys of 2 or more files are identical
EOF FILE-NAME	Checks to see if the end of file marker has been met for the given file

The following example shows how the file merging process works:-

FILE INFILE

IN-NAME 1 48 A

IN-INDEX 49 4 N

FILE EUROPA

EU-DUNS 1 9 N

EU-NAME 10 48 A

FILE DPLMSTR

DI-DATA 1 54 A

DI-NAME 1 48 A

FILLER-1 49 1 A

DI-INDEX 50 4 N

FILE OTFILE

OT-DATA 1 61 A

OT-NAME 1 48 A

OT-INDEX 49 4 N

OT-DUNS 53 9 N

*=======================================================================

JOB INPUT ( EUROPA KEY EU-NAME +

INFILE KEY IN-NAME )

IF EOF INFILE

STOP

END-IF

MOVE SPACES TO OT-DATA

MOVE SPACES TO DI-DATA

IF INFILE

OT-NAME = IN-NAME

OT-INDEX = IN-INDEX

IF DUPLICATE INFILE

DI-NAME = IN-NAME

DI-INDEX = IN-INDEX

PUT DPLMSTR

OT-DUNS = 999999999

ELSE

IF MATCHED INFILE EUROPA

OT-DUNS = EU-DUNS

END-IF

PUT OTFILE

END-IF

Exercise 6 - File Synchronisation

File ‘UEOE.UK96120.CD360K.FULL.EXTRACT’ contains 368,426 records. This is the same file as used in Exercise 4.

Deduplicate the SIC codes so that a new file contains only one record for each of the codes.

It is not known whether the file is correctly sorted or not.

Display a report of the number of records processed.

PDS members should be called UK120DED, output files should begin USER‑ID.UK96120.

File ‘UEOE.UK96174.CSDB.EXTRACT1’ contains details extracted from the Case Summary database as follows:-

DESCRIPTION	START	LENGTH
DUNS Number	1	9
Name	10	40
CRO Prefix	50	2
CRO Number	52	7
Sic	59	4
Employees	63	15

File ‘UEOE.UK96174.MERGE1’ contains information from other sources. It’s layout is:-

DESCRIPTION	START	LENGTH
DUNS Number	1	9
Registration date	10	8
Director’s name	18	40
Function code	58	3
Associate duns	61	9
Associate function code	70	3

Merge the 2 files together based on DUNS. The final file layout must be as follows:-

DESCRIPTION	START	LENGTH
DUNS Number	1	9
Registration Prefix	10	2
Registration Number	12	7
Registration date	19	8
Director’s name	27	40
Function code	67	3
SIC	70	4
Employees	74	15
Associate duns	89	9
Associate function code	98	3

PDS members should be called UK174MR3, output files should begin USER‑ID.UK96174.

File ‘UEOE.SEDGWICK.CUSTFILE.FEB96’ contains 660 records.

File ‘UEOE.UK96222.CSDB.EXTRACT’ contains 319 records.

File ‘UEOE.UK96222.GLOBAL.EXTRACT’ contains 149 records.

Merge the data from the last 2 files onto the first file using the DUNS numbers as a key. If a duplicate DUNS is found on the first file, only write the first occurrence to the output file.

If a DUNS number from the first file is not found on the second or third files, output the record to a FAILFILE with a similar layout to the first file.

Ensure that all of the records from the first file are accounted for and display a report of the processing completed.

PDS members should be called UK222MRG, output files should begin USER‑ID.UK96222.

Look-Up Tables

A look-up table is a special use of a file which allows cross reference tables to be used in EasyTrieve. This was not mentioned in the Library section as it is considered an advanced topic and some EasyTrieve experience is required before attempting to use the feature.

General Requirements

Look-up tables may either be external files or defined within an EasyTrieve module. Whatever their location, a key field (known as the ARG) must be defined with a result field (called the DESC field).

The ARG fields must be unique and must be sorted into ascending order.

Instream Tables

Tables which are defined in the EasyTrieve module itself are termed as INSTREAM.

An INSTREAM table is defined in the Library section as follows:-

FILE EXCODES TABLE INSTREAM

ARG 1 2 A

DESC 4 30 A

04 COMPANY SECRETARY

05 PRESIDENT

06 PROPRIETOR

07 PARTNER

08 MANAGING DIRECTOR

09 OTHER DIRECTOR

10 GENERAL MANAGER

12 FINANCIAL DIRECTOR

13 COMMERCIAL DIRECTOR

14 TECHNICAL DIRECTOR

16 PERSONNEL DIRECTOR

21 SALES DIRECTOR

22 EXECUTIVE DIRECTOR

23 MARKETING DIRECTOR

24 CHAIRMAN

25 MANAGER

In this example, the ARG field is defined as 2 bytes starting in column 1, the DESC starts in column 4 and is 30 bytes long.

The ARG and DESC fields can be any length and start in any position, but their combined lengths must not exceed 72 bytes.

File Tables

Look-up tables which are held on file are defined in a similar manner:-

FILE SICTABLE TABLE (2000)

ARG 1 4 A

DESC 6 118 A

The value in brackets is the maximum number of records on the file.

As can be seen, there is no restriction on the length of the fields in the table when using a file instead of instream.

Searching Tables

Once the table has been defined (either as a file or instream) in the Library section, it can be searched in the Activity section in the following manner.

SEARCH SICTABLE WITH OT-USSIC-1 GIVING WS-SIC

IF SICTABLE

OT-USSIC-DESC-1 = W-USSIC-DESC

END-IF

The search field (in this case OT-USSIC-1) and target field (WS-SIC), must match the ARG and DESC field definitions exactly.

The test following the search command will return true if a match on the ARG was found, false otherwise.

Exercise 7 - Tables

Using the output file from Part 1 of Exercise 6, create a new file with the SIC code and it’s description.

The file which can be used as a table in this instance is held in ‘UEMS.SIC.CONVERT’. Details of this file are held in the Programmers Checklist & Standards (Section 9).

PDS members should be called UK120TBL, output files should begin USER‑ID.UK96120.

Database Access

Extracting data databases usually takes 2 forms. The first form is to use a key to extract specific records from the database. The second is to ‘sweep’ the database, selecting each record in turn and discarding or processing the records depending on set criteria.

The former is the most common way of extracting records, the latter is used less often and almost always against the Case Summary database.

JCL Requirements

Whichever type of access to the database is required, the database(s) used must be identified in the JCL. This is done by identifying that a database is to be used in the JCLLIB and JOBLIB statements and then identifying one or more databases to be INCLUDED in the appropriate step. The example below shows the inclusion of a database in the JCL, the mandatory statements are highlighted:-

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

//* *

//* CS ORDER : 146 EMS ORDER : ***** CLIENT : SEDGWICK LTD *

//* *

//* DESCRIPTION *

//* ~~~~~~~~~~~ *

//* *

//* EXTRACT CSDB DATA *

//* *

//* AUTHOR : DUNCAN SPENCE DATE :16TH FEB 1996 *

//* *

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

//JCLLIB JCLLIB ORDER=(SEDV.FIX.JCLLIB,SEDV.PRD.JCLLIB)

//JOBLIB DD DSN=SYS9.FASTSW.LOAD,DISP=SHR

// DD DSN=IDMS.PROD.LOAD,DISP=SHR

// DD DSN=IDMS.PROD.SCHEMA,DISP=SHR

//S040 EXEC PGM=EZTPA00,REGION=8M,COND=(0,NE),TIME=(0,10)

//EZTVFM DD UNIT=SYSDA,SPACE=(CYL,(20,02),,,ROUND)

//PCSUMDB INCLUDE MEMBER=PCSUMDB

//JOURN DD DUMMY

EasyTrieve Requirements

Once the database has been identified in the JCL, it is necessary to define the database and how it will be used in the EasyTrieve module itself. There is a certain amount of information which is mandatory and this is laid out in the following example:-

****LIBRARY SECTION

FILE DBASE IDMS( CS001R RESET )

RECORD CASE-REC 544 +

KEY ( NBR-DUNS 1 4 )

NBR-DUNS 1 4 B 0

IND-STOP-DISTRIBUTION 13 1 A

IND-OUT-BUSINESS 14 1 A

IND-BASE-CATEGORY 15 1 A

IND-IMPORT-EXPORT 16 1 A

DATE-OUT-BUSINESS 17 4 P 0

DATE-EAA-STATEMENT 21 4 P 0

DATE-BASE 25 4 P 0

DATE-STATEMENT 41 4 P 0

TOTAL-EMPLOYEES 45 5 P 0

DATE-YEAR-STARTED 59 3 P 0

TOTAL-SALES 63 8 P 0

TOTAL-NET-WORTH 71 8 P 0

IND-WORTH 79 1 A

IND-TREND 85 1 A

NBR-CASE-TELEPHONE 86 14 A

TEXT-LINE-BUSINESS 106 50 A

NAME-PRIMARY 215 40 A

ADDR-PRIMARY-STREET-1 255 40 A

NBR-SIC 407 4 N OCCURS 6

Once the file has been identified as a database (DBASE), the name of the record must be identified and the field which is to be used as a key. Once the key field has been identified, it must be included in the record layout which follows.

Extracting Data By Key

To extract data using the selected key into the database, an input file must be defined with the required key in a similar format to the key defined for the database. Once this is complete, the first statement of the JOB section MUST be to read the database using the supplied key, as follows:-

FILE DBASE IDMS(CS001R RESET)

RECORD CASE-REC 544 +

KEY (NBR-DUNS 1 4)

NBR-DUNS 1 4 B 0

NBR-SIC 407 4 N +

OCCURS 6 +

INDEX NBR-SIC+INDEX

FILE INFILE

IN-DUNS 1 4 B 0

JOB INPUT ( DBASE ) +

FINISH( DISPTOTS )

RETRIEVE DBASE +

KEYFILE INFILE +

KEYVALUE IN-DUNS +

SELECT ( CASE-REC AREA 'CASE-SUMM-AREA' )

IF PATH-ID = 'NF'

W10-MISMATCH = W10-MISMATCH + 1

FAIL-DUNS = IN-DUNS

PUT FAILFILE

GOTO JOB

END-IF

Note that the file containing the incoming KEY field is not defined in the JOB statement, the retrieve statement automatically reads the next record in before interrogating the database.

The check against PATH-ID is to ascertain whether the given key exists on the database.

Sweeping The Database

When sweeping the database, it is not necessary to supply an input file with a key field. The EasyTrieve statements cause a fresh record from the database to be returned on each pass through the logic. It is sill necessary to supply a KEY field in the database definition as this is used to control the order in which the records are returned. The example on the following page shows how to code for a database sweep.

Once again, at this stage it is not necessary to go into great detail on the processes which take place, the BIND statements prepare the database ready for use, the STATUS-CHECK at each stage ensures that the database is being read correctly and the IDMS FINISH command closes the database once the sweep is finished.

It is important to remember the STOP command at the end of the processing because otherwise the module falls into a continuous loop and will never end!

JOB INPUT (NULL) START(INIT) FINISH(DISPTOT)

IDMS OBTAIN FIRST RECORD CASE-REC +

AREA 'CASE-SUMM-AREA'

DO WHILE IDMSSTATUS NE '0307'

PERFORM STATUS-CHECK

IDMS OBTAIN NEXT RECORD CASE-REC +

AREA 'CASE-SUMM-AREA'

END-DO

STOP

*================================================================*

INIT. PROC

IDMS BIND 'CS001R'

PERFORM STATUS-CHECK

IDMS BIND, FILE DBASE, RECORD CASE-REC

PERFORM STATUS-CHECK

IDMS READY, AREA 'CASE-SUMM-AREA'

PERFORM STATUS-CHECK

IDMS READY AREA 'INDEX-CTRL-AREA'

PERFORM STATUS-CHECK

IDMS READY AREA 'BUSINESS-INDX'

PERFORM STATUS-CHECK

IDMS READY AREA 'NAME-ADDR-INDX'

PERFORM STATUS-CHECK

IDMS READY AREA 'NAME-CNTRY-INDX'

PERFORM STATUS-CHECK

IDMS READY AREA 'POST-CODE-INDX'

PERFORM STATUS-CHECK

END-PROC.

*================================================================*

DISPTOT. PROC.

IDMS FINISH

END-PROC.

*================================================================*

STATUS-CHECK. PROC.

IF IDMSSTATUS NE '0000'

DISPLAY NEWPAGE

DISPLAY 'STATUS OF ' IDMSSTATUS

STOP

END-IF

END-PROC

Exercise 8 - Database Access

Sweep the Case Summary database and select the DUNS numbers of the first 1,000 cases which are shown as trading in the UK.

The country code field is found at offset 395 on the record, is 2 bytes alpha and contains ‘UK’ for United Kingdom cases.

PDS members should be called UK361CS, output files should begin USER‑ID.UK96361.

Using the DUNS numbers extracted above, select cases from the Case Summary database and output the following information on each one; Business Name, Primary SIC and Telephone Number.

Ensure that the data output falls in line with the Programmers Checklist & Standards.

Subroutines

A certain amount of information on EasyTrieve subroutines has already been covered, this section formalises that information.

As in many other programming languages, a subroutine contains code which may be called many times from main logic, or may be used to aid in the readability of code by simplifying the structure of a module.

An EasyTrieve subroutine is known as a PROC and usually appears at the end of the module. The structure of a PROC is shown in the example below:-

CHECK-EXCLUDES. PROC.

DO WHILE ( W30-END-EXCLUDED = 'N' ) +

AND ( NBR-DUNS > W30-EX-DUNS )

GET EXCLFILE

IF EOF EXCLFILE

DISPLAY 'AT END OF EXCLUDE FILE'

W30-END-EXCLUDED = 'Y'

W30-EX-DUNS = 0

ELSE

W30-EX-DUNS = EX-DUNS

END-IF

END-DO

END-PROC

Note that the full stops are important in the definition of the PROC and that no parameters may be passed into or out of the subroutine.

To call a PROC code the statement PERFORM PROC-NAME in the code.

EasyTrieve Macros

Macros in EasyTrieve can be closely allied to macros in C, they are used to substitute code for an identifier. This allows blocks of reusable code to be created to simplify program logic or perform identical actions in several different modules.

Macros are generally used to identify file layouts for some of the most common files and ‘pseudo’ databases which exist and also to perform ‘generic’ actions on data such as expanding addresses or manipulating text.

This section will only cover the use of pre-defined macros in EasyTrieve modules, details of the creation of macros is outside the scope of this guide.

JCL Requirements

To call a macro into an EasyTrieve module, the location of the macro to be used must be defined in the JCL step where it will be used. This is achieved by coding a MACRO1 DD name and adding the correct PDS where the macro is kept, as follows:-

//S040 EXEC PGM=EZTPA00,REGION=8M,COND=(0,NE),TIME=(3,00)

//EZTVFM DD UNIT=SYSDA,SPACE=(CYL,(20,02),,,ROUND)

//MACRO1 DD DSN=UEOE.PROD.MACLIB,DISP=SHR

// DSN=UEMS.EAZY.MACLIB,DISP=SHR

Note that it is possible to add more than one macro library to the DD statement if required.

EasyTrieve Requirements

To call a macro into the EasyTrieve module, prefix the macro name (which is identical to the PDS member name) with a %. Some of the generic macros also allow parameters to be passed. Examples of both an ordinary macro call and a parameterised macro call are shown below:-

FILE INFILE

IN-DUNS 1 5 P 0

*MACRO TO CALL IN PRE-GENERATED FILE LAYOUT

%DASHGENR

IF DG-MIDL-NAME NOT SPACES

%EMCONCAT OT-EXEC-NAME 1 WS-MIDL-CHAR

%EMCONCAT OT-EXEC-NAME 1 DG-SURNAME

ELSE

%EMCONCAT OT-EXEC-NAME 1 DG-SURNAME

END-IF

Details of generic macros for file layouts and data manipulation are held in the Programmers Checklist & Standards document.

APPENDIX I - Numerical Formats

There are several different ways in which numerical values can be stored on the mainframe system depending on the way in which the values are to be used.

Each must be viewed and interpreted in a different way. The following is a discussion of how the numerical values may be stored and how they may be viewed.

Numeric Values

This is the easiest form to explain and view. The drawback of using this method of storing values is that it is the most inefficient way of storing and processing numbers. With a maximum of 18 bytes allowed for a numeric field this gives a range of values from -999,999,999,999,999,999 to +999,999,999,999,999,999.

When the layout of the field is defined as x N (where x is the number of digits in the field), each byte in the output layout represents a single digit in the number.

LIST ON

FILE OTFILE

FIELD-1 1 5 N

JOB INPUT( NULL )

FIELD-1 = +250

PUT OTFILE

STOP

Returns the following information on the OTFILE:-

----

00250

FFFFF

00250

----

*****

Note that the above output is achieved by browsing the file in ISPF Option 3.4 and typing HEX at the command line. The emboldened line is the contents of the byte, the two lines below show the value of the two word sized values which make up the byte.

It should be noted that negative numbers will lose their sign when stored in a field defined as above. To ensure that the sign of a value is preserved the field must be defined as it is below.

LIST ON

FILE OTFILE

FIELD-1 1 5 N

FIELD-2 7 5 N

FIELD-3 13 5 N 0

FIELD-4 19 5 N 0

JOB INPUT( NULL )

FIELD-1 = +250

FIELD-2 = -250

FIELD-3 = +575

FIELD-4 = -575

PUT OTFILE

STOP

This produces entries on a file as follows:-

----------------------

00250 00250 00575 0057N

FFFFF4FFFFF4FFFFF4FFFFD

00250000250000575000575

----------------------

When interpreting the fields output by this EasyTrieve module, the most significant word of the last byte will contain F if the value is positive and D if it is negative. The least significant word will contain the least significant digit of the value.

To introduce decimal places into a numeric field, the field definition must be changed as follows:-

LIST ON

FILE OTFILE

FIELD-1 1 5 N

FIELD-2 7 5 N

FIELD-3 13 5 N 2

FIELD-4 19 5 N 2

JOB INPUT( NULL )

FIELD-1 = +250

FIELD-2 = -250

FIELD-3 = +575.2

FIELD-4 = -575.2

PUT OTFILE

STOP

When executed, this gives the following results:-

----------------------

00250 00250 57520 5752}

FFFFF4FFFFF4FFFFF4FFFFD

00250000250057520057520

----------------------

As can be seen, it is vital to know how many decimal places were defined when the field was populated to ensure that the value is interpreted as 575.20 and not, for example, 5.7520.

Once again, the sign of a number is stored in the most significant word of the last byte as a D for negative and F for positive values.

Packed Decimal Values

This data type is the middle ground of the EasyTrieve data types, being more efficient in storage space and calculation time at the expense of readability of the final output.

Up to 10 bytes can be assigned for a packed decimal value giving a range of values from ‑9,999,999,999,999,999 to +9,999,999,999,999,999,999. The following example repeats those from the Numeric Values section and shows the output received.

LIST ON

FILE OTFILE

FIELD-1 1 5 P

FIELD-2 7 5 P

FIELD-3 13 5 P 0

FIELD-4 19 5 P 0

FIELD-5 25 5 P 2

FIELD-6 31 5 P 2

JOB INPUT( NULL )

FIELD-1 = +250

FIELD-2 = -250

FIELD-3 = +575

FIELD-4 = -575

FIELD-5 = +575.2

FIELD-6 = -575.2

PUT OTFILE

STOP

----------------------------------

..... ..... ....* ....) ..... .....

00020400020400055400055400550400550

0005F00005F00007C00007D00072C00072D

----------------------------------

As can be seen, browsing the file in ‘normal’ mode will show many unprintable values, it is necessary to switch to the HEX view to see the true value of the fields.

The figures are interpreted top line to bottom (most significant word to least significant) and then left to right. In contrast to the numeric fields detailed before, the sign field of the value (when available) is stored in the least significant word of the last byte, with a C denoting a positive value and a D indicating a negative.

Unsigned Packed Decimals

These react in a similar fashion to the ordinary Packed Decimal values.

The acceptable range given by the 9 bytes which Unsigned Packed Decimals are allowed to cover is zero to +999,999,999,999,999,999.

The following examples show how to code for Unsigned Packed Decimals and the result of using them with positive and negative values.

LIST ON

FILE OTFILE

FIELD-1 1 5 U

FIELD-2 7 5 U

FIELD-3 13 5 U 0

FIELD-4 19 5 U 0

FIELD-5 25 5 U 2

FIELD-6 31 5 U 2

JOB INPUT( NULL )

FIELD-1 = +250

FIELD-2 = -250

FIELD-3 = +575

FIELD-4 = -575

FIELD-5 = +575.2

FIELD-6 = -575.2

PUT OTFILE

STOP

----------------------------------

....& ....& ..... ..... ..... .....

00005400005400007400007400072400072

00020000020000055000055000550000550

----------------------------------

It can be seen that the values always lose their sign. Interpreting the figures in HEX mode is similar to Packed Decimal except that the least significant word of the last byte is now the least significant digit of the value instead of the sign indicator.

Unsigned Packed Decimals are not generally used.

Binary Values.

Binary numbers (which are also called hexadecimal because of the way in which they are represented in output files) are the most efficient way to store numerical information on the mainframe. Not only do the values take up the least space, but arithmetic processing is also at it’s fastest when fields are defined as Binary.

However, these values are the hardest to interpret, requiring an hexadecimal calculator to convert from the binary representation into decimal.

The range of values which can be stored in a binary field are -2,147,483,648 to +2,147,483,647.

It is worth noting that if the sign is not retained by coding a digit after the data type, the maximum positive value IS NOT doubled. If the figure becomes greater than +2,147,483,647, figures will simply be stored incorrectly.

The following repeats the previous examples with binary numbers:-

LIST ON

FILE OTFILE

FIELD-1 1 4 B

FIELD-2 7 4 B

FIELD-3 13 4 B 0

FIELD-4 19 4 B 0

FIELD-5 25 4 B 2

FIELD-6 31 4 B 2

JOB INPUT( NULL )

FIELD-1 = +250

FIELD-2 = -250

FIELD-3 = +575

FIELD-4 = -575

FIELD-5 = +575.2

FIELD-6 = -575.2

PUT OTFILE

STOP

---------------------------------

.... .... .... ...A ..\. ...&

000F44000F44000344FFFC4400EB44FF15

000A00000A00002F00FFD100000000FFF0

---------------------------------

The example shows that each word of each byte contains an hexadecimal digit (1 to 9 or A to F), values are read in the same manner as for packed decimal values (highest to lowest significant word and left to right).

The binary versions of the values follow the 2’s compliment order of calculation so, when negative values are being represented, the value of the field on the file will be greater than 7FFFFFFF.

The Windows calculator (usually found in the Accessories folder) has decimal and hexadecimal conversion function when in scientific mode and there is a hex converter function on the mainframe (enter TSO HEX on any command line to start this function).

Sunday, January 22, 2017

Easytrieve : The ACTIVITY Section

Reading And Writing Files

Loop Functions

No comments:

Post a Comment