The GET Statement

FRegion is a reference file containing Region Names. The GET statement gets the record for which the value of FRegion.Region matches the value read from InFile.

Statement Format

In Batch programs

GET File-Name Key-option [FIRST] [ORDER] [UPDATE [ONCHANGE]] ENDGET

Key-Option is {WHERE (condition) | KEY(key [OR key]…) | FREEKEY [KEY(key)] | SSA(ssa[,ssa]…}

In CICS programs with VSAM or SQL (DB2 etc)

GET File-Name Key-option [{SAVECOPY | SAVESUM} | UpdateOptions

[FIRST | {TS(nbr) [AUTO | MANUAL]}] [RESETFUNCTION] [INTO Name] [ENDGET]

UpdateOptions ::= UPDATE [{CHECKCOPY name | CHECKSUM name}] CONTINUE

In CICS programs with TS (Temporary Storage) files

GET File-Name NBR recordnumber;

Statement Options

Key-Option

A GET statement gets one or more specific records from a file, and thus must contain one of the possible key-option alternatives

GET File-Name WHERE (condition) ….

GET File-Name KEY (Key list)

GET File-Name FREEKEY CREATE

GET File-Name SSA (SSA list)

Or GET File-Name NBR

The rules about the way in which these options may be used depend on the file type and the situation. For example: -

· SSA is only valid within DL1 programs. Refer to DL1 below.

· NBR is only valid when the file has type TS. TS is only valid in a CICS program.

WHERE

WHERE(condition). The condition must have the form

field-in-file = value [& field2-in-file = value2]…

Thus whereas a condition in PROCESS …. WHERE(condition) is a general filter and can be any condition that can be written in an IF statement, the condition in a GET statement is more like a series of assignment statements to key fields in the file. The WHERE condition must be sufficient to specify a single record.

For VSAM: -

· field-in-file must be a field or group defined with a KEY option (KEY, UKEY, or DKEY)

· The comparison operator must be “=”. GET FREGION WHERE (FREGION.REGION > 5) is therefore invalid.

· The value may be a constant, or a reference to a field defined outside this file.

· If there are several parts to the condition, they are linked with & (AND). You cannot use the Boolean operator | (OR). All of the field-in-file names must be defined together within the same group. Thus with a file defined like this: -

DEFINE RDFile TYPE(VSAM) DATA(

KEYFIELDS GROUP KEY,

REGION FIXED(3),
DISTRICT FIXED(3),

ENDGROUP,

NAME CHAR(30));

a GET statement such as

GET RDFile WHERE (RDFile.REGION = INPUT.REGION & RDFile.DISTRICT = INPUT.DISTRICT)

is valid. However it is not valid if the fields REGION and DISTRICT are not defined within the same group, or if there are other fields also defined within this group.

For SQL, the WHERE condition is more flexible and may be anything that is valid within a SQL WHERE clause. It is not checked by Jazz, except that an error is detected when the program runs if the GET attempts to return more than one record unless FIRST is specified.

KEY (key [keytype] [OR|AND key [keytype]]…)

KEY indicates a field or group within the record to be used as the key for direct access. For example, in this program the ACCEPT statement has already validated the screen field CICS4S.Keyfield and (if valid) moved it to Custf.Keyfield where it has the correct format to be used as the key for Custf. It seems pointless to write

GET Custf WHERE(Custf.Keyfield= Custf.Keyfield);

instead we use KEY: -

ACCEPT (CICS4S.Function, CICS4S.Keyfield);

CASE (CICS4C.Function);

WHEN (Enquiry);

GET Custf KEY(Custf.Keyfield) SAVECOPY CICS4C.SAVE;

…

The field/group named must be defined within the record. Groups are given as generic references, e.g. Custf.KeyGroup.*

Keytype applies to CHAR and VARCHAR key fields only, and does not apply in batch programs if the filetype is VSAM. If present it may have value GENERIC, FULL, or WILDCARD. See below for more information.

Multiple keys (alternate indexes): using OR

A record may be defined with alternate indexes using DKEY and UKEY options. For example, here is the definition of CustF. You can look up records by Account or by Name: -

*# Last Updated by IBMUSER at 29/11/2014 5:57:18 p.m.

COPY Types;

DEFINE CustF VSAM DATA(

Account PIC '999999' HEADING 'Account Number' KEY,

Region DECIMAL(3),

District DECIMAL(3) RANGE(1:10),

Name CHAR(30) DKEY 'jazzuser.vsam.custf1',

SalesThisMonth MONEY(7,2),

SalesYTD MONEY(7,2),

Billingcycle LIKE Types.Month,

DateCommenced DATE)

DSNAME 'JAZZUSER.VSAM.CUSTF';

Thus to read CustF with a particular Account value you’d write either

GET CustF WHERE(Custf.Account = value);

or GET CustF KEY(Custf.Account);

while to read CustF with a particular Name value you’d write

GET CustF WHERE(Custf.Name = value);

or GET CustF KEY(Custf.Name);

Where there are several possible keys you can use KEY(key1 OR key2), like this: -

GET CustF KEY(Custf.Account OR Custf.Name);

OR uses the first value found that has a non-default value (normally blank or zero, unless there’s a VALUE clause). Thus the GET statement above is equivalent to: -

IF Custf.Account <> ' ' THEN;

GET CustF KEY(Custf.Account);

ELSEIF Custf.Name <> ' ' THEN

GET CustF KEY(Custf.Name);

ELSE;

INITIALIZE Custf;

Custf.$Found = ‘N’;

END IF;

Note the implied rule: you cannot GET file KEY (KeyName OR KeyName2) for records with keys having their default value if the GET statement uses OR. There is no restriction in GET … KEY statements without OR , nor with GET … WHERE so it is possible to have such records in your file, but if you have such records you won’t be able to take advance of the convenience of the automatically-generated GET file KEY (KeyName OR KeyName2) and you’ll have to change this code to your own logic and use GET … WHERE.

You’ve probably written a preceding ACCEPT statement that also uses OR: for example, here we are asking users to enter either an account number or customer name in the screen, and then looking up the file based on whichever value they enter: -

ACCEPT (CICS1S.Account OR CICS1S.Name);

GET custf KEY(CustF.Account OR CustF.Name);

There may be many possible keys: -

GET CustF KEY(Key1 OR Key2 OR Key3 …);

SQL Compound Keys: using AND

A SQL record (or “row”) may have several fields as its key, and these fields do not have to be contiguous or in a GROUP. In fact, you cannot use a GROUP with SQL. For example, table PROJACT in IBM’s Sample database is defined to Jazz like this: -

DEFINE PROJACT SQL PREFIX PRO DATA(

PROJNO CHAR(6) REQUIRED KEY,

ACTNO SMALLINT REQUIRED KEY PART 2,

ACSTAFF DECIMAL(5,2),

ACSTDATE DATE REQUIRED KEY PART 3,

ACENDATE DATE);

To uniquely identify a record a GET statement must give a value for the three parts of the key, PROJNO, ACTNO, and ACSTDATE, with a GET statement like this: -

GET PROJACT KEY(PROJACT.PROJNO AND PROJACT.ACTNO AND PROJACT.ACSTDATE);

If all key parts are named then this GET returns one (or zero) records, and does not need to use a FIRST option. If only part of the key is given then FIRST may be required, or else scrolling options may be generated.

AND is not valid unless the file type is SQL: it cannot be used for VSAM, XIO, or other (future) file types that permit direct access.

Multiple Compound Keys.

As the VSAM example above showed a GET statement can use OR to attempt a lookup with one key, then another. OR can also be used with compound keys. Each key set is grouped with AND, then OR introduces the next key set. For example, this record is defined with a DKEY (non-unique index) on ACSTAFF, and another on (ACSTFFirstName and ACSTFFamName): -

DEFINE SQLRow SQL PREFIX SQR DATA(

PROJNO CHAR(6) REQUIRED KEY,

ACTNO SMALLINT REQUIRED KEY PART 2,

ACSTAFF DECIMAL(5,2) DKEY 'XSQR2',

ACSTFFirstName CHAR(20) DKEY 'XSQR3',

ACSTFFamName CHAR(20) DKEY 'XSQR3' PART 2,

ACSTDATE DATE REQUIRED KEY PART 3,

ACENDATE DATE);

To attempt access by the primary key, then the first DKEY, then the second, you’d write: -

GET SQLRow KEY (SQLRow.PROJNO AND SQLRow.ACTNO AND SQLRow.ACSTDATE

OR SQLRow.ACSTAFF OR SQLRow.ACSTFFirstName AND SQLRow.ACSTFFamName);

With OR, the next key is used if the first has its default value – normal blanks or zeros. With compound keys this same rule is applied to each key set: if any one of the key set has the default value, then the key set is considered absent and the next used. With SQL OPTIONAL fields the default value is $NULL. Thus the logic of this GET is

IF SQLRow.PROJNO <> SPACE & SQLRow.ACTNO <> 0 & SQLRow.ACSTDATE <> 0;

GET SQLRow KEY (SQLRow.PROJNO AND SQLRow.ACTNO AND SQLRow.ACSTDATE);

ELSEIF SQLRow.ACSTAFF <> 0;

GET SQLRow KEY (SQLRow.ACSTAFF);

ELSEIF SQLRow.ACSTFFirstName <> JAZZ.$Null & SQLRow.ACSTFFamName <> JAZZ.$Null;

GET SQLRow KEY ( SQLRow.ACSTFFirstName AND SQLRow.ACSTFFamName);

END IF;

FREEKEY | NEXTKEY

Freekey and NextKey are not available in batch program, they are only valid in CICS programs.

FREEKEY or NEXTKEY are used when, to add another record when you don’t know or care what its key value will be, as long as it is not a key already in use. For example: -

WHEN (Add);

GET Custf NEXTKEY CREATE;

The program will read the record with the highest key, then add one to this value: -

· If the key is a group field (VSAM) or a compound key, then the “addition” is applied to the last field of the key.

· If the key field is not a number, then it “Adds one” to a character value by treating the character string as if it were a hexadecimal number, adding X’01’ until the next “Alphanumeric character” (letter or number) is found. Thus, with an ASCII system, key “abc” becomes “abd”, and “abz” becomes “ac0”. Different results may be obtained with EBCDIC. The Jazz support subprogram JZNXTKY must have been compiled into the appropriate program library.

· FREEKEY and NEXTKEY behave the same way EXCEPT that FREEKEY requires that the record’s key value is currently absent. Typical ADD code is: -

WHEN (Add);

ACCEPT (DEPARTMENT.DEPTNO=IJSPG3.DEPTNO) MESSAGE OJSPG3.Error;

GET DEPARTMENT FREEKEY CREATE;

This logic allows you to specify the key with which a new record is created. Of course, you must specify a value that is not already in use. This logic behaves: -

o If a value is given in IJSPG3.DEPTNO then this will become the value of DEPARTMENT.DEPTNO, and the GET will attempt to read this record. An error will be reported if this record is found.

o If the value is not given, FREEKEY will find a free value as described above.

In contrast, if NEXTKEY is used, any value already in DEPARTMENT.DEPTNO is ignored, and a free value is found.

SSA

SSA is an alternative to WHERE and KEY options, but it applies only to retrieval from a DL1 (IMS) database. It is particularly likely to be used when your program has been generated by conversion from Easytrieve. If you’re using this option then you will be familiar with DL1 databases, and you will know what an SSA (Segment Search Argument) is and how to use it, but click the link if you need a refresher. Here’s a code fragment from a DL1 Program: -

*# Last Updated by JAZZUSR at 21/09/2024 2:20:44 PM

PROGRAM DLITst BATCH DATABASE V05 DL1 EZT;

COPY JZDL1 JAZZ;

*# SSA's go here ==>

COPY EZT-OAADN6MN-DATA;

*# Copy Files here ==>

…

GET V05P2A UPDATE SSA(EZT-OAADN6MN-Data.SV05P1A, EZT-OAADN6MN-Data.SV05P2A);

…

To access DL1 data your program statement will include the option DATABASE xxx DL1. This causes various definitions to be created, and the correct COBOL program structure to be generated so that this batch program will compile and run. xxx is the name of the PSB, which will appear in all the CALL CBLTDLI statements that are used to retrieve and update records (called “Segments” in DL1). Program DLITst was created by copying code from converted program OAADN6MN, changing the program name and chopping out unwanted logic.

The GET statement automatically checks the DL1 status code, and will set V05P2A.FOUND to True or False, providing the same behavior as other file types.

The GET statements names the record (segment) that it wants, in this case V05P2A which is a segment from the database and is defined in EZT-OAADN6MN-DATA as

DEFINE V05P2A DL1 DATA(

SEGMENT CHAR(350),

GROUP1 GROUP REDEFINES V05P2A.SEGMENT,

…

This definition results from EZT conversion, and so there is a general record area called SEGMENT and multiple redefinitions giving field detail.

The GET statement does not name the database (PSB), as this is known from the PROGRAM statement. There is another record within COPY EZT-OAADN6MN-DATA that defines V05.

The SSA option names 2 SSA’s, SV05P1A and SV05P2A. As the qualification shows, these have been located in the definition EZT-OAADN6MN-Data. You must give at least 1 ssa, you can give several, separated by commas. Jazz will check that these exist, and are CHAR or GROUP fields at least 9 bytes long. Jazz doesn’t check that they are actually correctly formatted as valid SSA’s, but if the program has been generated from EZT conversion you can assume that this is so. The first eight characters must be the segment name, and if there are multiple SSA’s they must appear in hierarchical order.

If an SSA is qualified, then before the GET statement is executed then there will be statements assigning data to field values before the GET statement is reached.

Record not found

When GET attempts to read a record but there is no record with the requested key value then Jazz creates a record for you in memory with the key value sought, and other field values set to their default values. For example, in my test file there was no value for Region=9, so when

GET FRegion WHERE (Fregion.Region = InFile.Region);

is executed with InFile.Region=9 no record is found. A dummy record is created with FRegion.Region = 9 and other fields like FRegion.Name set to their default values, Blank or zero. This allows the PRINT statement to work normally.

If you want to test for “record not found” use the special value $Found: -

IF FRegion.$Found THEN ….

If you want to report that there was no FRegion record then an easy way of doing this is to set a suitable Value clause: -

DEFINE FRegion TYPE(VSAM) DATA(

REGION PIC ‘999’,

NAME CHAR(30) VALUE ‘Not Recorded’);

This avoids the need for code such as: -

GET …

IF FRegion.$Found THEN

FRegion.Name = ‘Not Recorded’;

END IF;

PRINT ….

Zero (or default) Key Values

Avoid situations where there could be a real record with the default key value! For example, with FRegion defined

DEFINE FRegion TYPE(VSAM) DATA(

Region PIC '999' KEY,

Name CHAR(30) Value('No Record found'),

Fill CHAR(47));

the default value of Fregion.Region is zero. If you have a real record on the file that has Fregion.Region with this value then your GET may not retrieve this real value, but instead will use the initialised record. For example: -

GET FRegion KEY Fregion.region OR Fregion.Name;

With OR the default value is used to decide if a value has been assigned and so which key field should be used for the lookup. You can use WHERE:

GET FRegion WHERE Fregion.region = Input.region;

But now you’ll have to write your own logic to if you want something like the standard KEY logic.

GET and Multiple Records

GET is designed to get a single record. The archetypal situation: you look up a record using its primary key: -

CustF.Account = 23;

GET custf KEY(CustF.Account);

You either return a record or you don’t. If there is a record with Account = 23 it will be returned. If there isn’t Jazz will initialise the record area: Custf.Account will be set to 23, everything else will be set to default values – normally zeros and blanks.

What if there might be several possible records? For example, with Custf.Name defined like this there might be several records with name 'BARNES': -

DEFINE CustF TYPE(VSAM) DATA(

…

Name CHAR(15) DKEY 'ibmuser.vsam.custf1' ,

…

ACCEPT (CICS1S.Name);

GET custf KEY(CustF.Name);

GET can only return one record from GET File KEY(File.KeyField); when

File.KeyField is defined with property KEY or UKEY, and its format is not CHAR, or
File.KeyField is defined with property KEY or UKEY, its format IS CHAR, and also property FULL is specified. For example,
Name CHAR(15) KEY FULL,

GET can return one of several records from GET File KEY(File.KeyField); in other cases, i.e.

File.KeyField is defined with property DKEY
File.KeyField has format CHAR and you have not specified property FULL.

KeyType

When CHAR fields are used as keys then one of the key options “GENERIC, WILDCARD ‘character’, and FULL may be given.

Apart from Batch VSAM, the default is GENERIC, but for Batch programs with VSAM FULL is always applied and if a KeyType option is given it will be ignored. KeyType options may be given in either the DEFINE statement or the GET statement, with the option from the GET statement taking precedence. For example, with Name defined as above: -

Name CHAR(15) KEY FULL,

you might write a GET statement like this: -

GET custf KEY(CustF.Name GENERIC);

in which case the GET statement will use GENERIC rules, using only the length of the value given.

The GET statement can only return one record, and in a batch program this is all you get. With a CICS program Jazz will generate logic that returns the first record, but enables PF10 as “Previous” and PF11 as Next when relevant. With SQL you can use ORDER to specify the retrieval sequence and so which is the first record. Here Employee is a table from a DB2 database: -

GET employee WHERE (EMPLOYEE.EMPNO = W.EMPNO) ORDER(EMPLOYEE.EMPNO);

However ORDER is invalid with VSAM. In the absence of ORDER it is not clear which record will be the first.

With the default of GENERIC the GET searches for records matching the search value for the length entered. Thus if you entered value “Apthorpe” (using my CustF test data) the system could return several records: -

APTHORPE, Alice

APTHORPE, Ben

APTHORPE, John

…

APTHORPE, WILLIA

The GET statement initially returns the first (Alice), but then with PF11 you can read the next. Once you’ve moved beyond the first record PF10 is also enabled, and you can go forwards and backwards through the list: -

With WILDCARD ‘x’ you specify that a special character is used to delimit the string. This is typically “*” or “%”, although you can use any character that doesn’t appear in the data. Thus if you wrote

GET custf KEY(CustF.Name WILDCARD '*’);

the user would enter “Apthorpe*” to return the same records as above.

By writing: -

GET custf KEY(CustF.Name FULL);

you force the program to use the full length of the field, so that you’ll only find records with key value

APTHORPEbbbbbbb

(“b” represents a blank)

For key fields that do not have CHAR format, like Custf.Account, you cannot use these options, and effectively the statement uses the FULL option.

FIRST

In CICS programs: handle only the first record, do not provide next/previous paging.

In Batch programs: generate code that will handle multiple records without error, but return only the first to your program. This is the default, but specifying FIRST explicitly suppresses some error messages when analysis of SQL definitions would complain about ambiguity.

ORDER

This option is only valid for SQL definitions. If GET can return many records then which is returned first is undefined. ORDER specifies an order, defining which record you’ll get with GET …. FIRST. Use this option sparingly: it may create a lot of processing.

TS(nbr)

It is highly unlikely that you will write this option yourself, more likely Jazz has inserted it. It only applies to classical CICS programs, not to batch or Web Service[1] programs.

Suppose you write this in a CICS program:-

GET custf KEY(CustF.Name);

There may be zero, 1, or a large number of records matching this key. Your program needs a way of keeping track of these records so that you can easily go forward or back through them with PF11 and PF10. Jazz does this by creating an in-memory TS file containing the record keys, and it will add a definition of this file to your program, and this option to the GET statement. Thus the statement above becomes: -

DEFINE TS1 TYPE(TS) DATA(

Account LIKE CustF.Account);

GET custf KEY(CustF.Name) TS(1);

This is all automatic, and Jazz looks after creating this file, writing and reading records to it, using it to read particular records from Custf, and creating the visual clues like

at the bottom of your screen.

GET-related statements: END GET and GET processing.

When a GET statement might return several records you may want to group some of the following statements to tell Jazz that they are to be executed with it. You do this by writing one or more statements after the GET, followed by END GET. For example: -

GET custf KEY(CustF.Account OR CustF.Name) ENDGET;

Statements to be executed after every Read from custf

END GET custf;

You have to add an option to the GET statement to let Jazz know that the following statements form a GET group. You can add the option ENDGET, as above, but except for file type DL1 ENDGET is implied by any of the update options (UPDATE, CREATE, REWRITE), INTO, and TS. The following statements, to END GET, are executed whenever GET attempts to retrieve a record. If no record was found, then they will be executed with an initialised record which has the key value requested, and all other fields set to their default values.

For example a GET statement with TS may return multiple records, implying the possibility of Next/Previous logic invoked by PF11/PF10. When you get a new record your program may need to get other data as well. For example, here a program reads a CustF record and also the related Orders records. Like the GET statement the PROCESS statement needs to use a TS file to allow for the possibility of there being more 2nd-level records than can be put on the screen at once: -

CASE (CICS3C.Function);

WHEN (Enquiry);

ACCEPT (CICS3S.Account OR CICS3S.Name);

DEFINE TS1 TS DATA(

Account LIKE CustF.Account);

GET custf KEY(CustF.Account OR CustF.Name) SAVECOPY CICS3C.SAVE RESETFUNCTION TS(1);

#373 I GET statement returns one record at a time for Name

DEFINE TS2 TS DATA(

OrdNbr LIKE Orders.OrdNbr);

PROCESS Orders WHERE (orders.ordcustid = custf.account) TS(2) INDEX;

#082 W Default name 'JZ-INDEX' used for INDEX option

CICS3S.OrdNbr(JZ.JZ-Index) = Orders.OrdNbr;

CICS3S.Orddate(JZ.JZ-Index) = Orders.ordDate;

CICS3S.Ordpart(JZ.JZ-Index) = Orders.ordpart;

CICS3S.OrdQty(JZ.JZ-Index) = Orders.OrdQty;

CICS3S.OrdDiscount(JZ.JZ-Index) = Orders.OrdDiscount;

CICS3S.OrdStatus(JZ.JZ-Index) = Orders.OrdStatus;

END PROCESS orders;

Obviously the Orders records are related to the CustF record, and if PF10/11 gets another CustF record then the Orders records have to be refreshed also. The above logic is incorrect: while it would work when records are initially read, it would not work when PF10/11 is used to move backwards and forwards through the CustF records. We need a way of telling Jazz that the statements following the GET are “part of the GET process”, so that it can ensure that whenever we get a record, including paging, the related statements are executed correctly. We do this by writing them within GET … END GET, like this: -

GET custf KEY(CustF.Account OR CustF.Name) SAVECOPY CICS3C.SAVE RESETFUNCTION TS(1);

#373 I GET statement returns one record at a time for Name

DEFINE TS2 TYPE(TS) DATA(

OrdNbr LIKE Orders.OrdNbr);

PROCESS Orders WHERE (orders.ordcustid = custf.account) TS(2) INDEX;

#082 W Default name 'JZ-INDEX' used for INDEX option

CICS3S.OrdNbr(JZ.JZ-Index) = Orders.OrdNbr;

CICS3S.Orddate(JZ.JZ-Index) = Orders.ordDate;

CICS3S.Ordpart(JZ.JZ-Index) = Orders.ordpart;

CICS3S.OrdQty(JZ.JZ-Index) = Orders.OrdQty;

CICS3S.OrdDiscount(JZ.JZ-Index) = Orders.OrdDiscount;

CICS3S.OrdStatus(JZ.JZ-Index) = Orders.OrdStatus;

END PROCESS orders;

END GET custf;

You may write logic following END GET which will not be re-executed by PF10/11.

Because of the possibility of related logic, END GET is required if TS etc is present even when there are no following statements. Future Jazz releases should eliminate this requirement: Jazz should know that END GET is unnecessary in situations like: -

GET custf KEY(CustF.Account OR CustF.Name) RESETFUNCTION TS(1);

END GET custf;

Classical CICS Programs with Several GET Statements

Your program logic might use several GET statements. If only one of these could potentially return several records then programming is straightforward: -

GET FRegion KEY (Fregion.Region);

GET FDist KEY (FDist.District);

GET Custf KEY(CustF.Account OR CustF.Name) … TS(1);

#373 I GET statement returns one record at a time for Name

…

END GET Custf;

However what if one of these other GET statements were to handle multiple records also? Jazz will give each multi-record GET its own TS file, and require an END GET statement to close off the GET logic. For example: -

GET FRegion KEY (Fregion.Region);

GET FDist KEY (FDist.District) TS(1);

#373 I GET statement returns one record at a time for District

…

END GET FDist;

GET Custf KEY(CustF.Account OR CustF.Name) … TS(2);

#373 I GET statement returns one record at a time for Name

…

END GET Custf;

What about the automatic PF Key handling? Should PF10/11 control FDist or CustF? It can hardly control both! You’ll need to indicate which is controlled automatically: you do this by choosing one of the options FIRST, AUTO, or MANUAL.

FIRST, AUTO, and MANUAL Options

GET …. FIRST specifies that, even if there might be many possible records satisfying the GET key criteria only the first will be returned. Temporary Storage files are not used, there is no mechanism for getting other records, and no need for END GET. As noted above, it can be uncertain which of several records is “the first”. The code above becomes: -

GET FRegion KEY (Fregion.Region);

GET FDist KEY (FDist.District) FIRST;

GET Custf KEY(CustF.Account OR CustF.Name) … TS(1);

#373 I GET statement returns one record at a time for Name

…

END GET Custf;

When there are several GET statements using TS files then only one may use the automatic PF10/11 logic. You may designate which by writing AUTO with this GET, and MANUAL with the others: -

GET FRegion KEY (Fregion.Region);

GET FDist KEY (FDist.District) TS(1) MANUAL;

#373 I GET statement returns one record at a time for District

…

END GET FDist;

GET Custf KEY(CustF.Account OR CustF.Name) … TS(2) AUTO;

#373 I GET statement returns one record at a time for Name

…

END GET Custf;

By default Jazz will apply AUTO to the first GET statement with TS, and MANUAL to all the others.

To handle GET … MANUAL, since you can’t use the automatic PF10/11 logic you have to write the equivalent yourself, either using free PF keys with HANDLE, or using data values such as further Function values. In your logic you will read the TS file explicitly using GET, and then use the retrieved key to GET the data record.

RESPOND

RESPOND is designed for situations like a web service provider that might return several records. As noted above,

GET is designed to GET a single record: -

CustF.Account = 23;

GET custf KEY(CustF.Account);

There either exists a record with this Account value, in which case it will be retrieved and Custf.* will hold the data from this record, or else it doesn’t exist and Custf.* will have initialised values.

However GET might return several records. For example, with

GET custf KEY(CustF.Account OR CustF.Name);

retrieval using CustF.Name could return any number of records. In a classical CICS program Jazz deals with this by reading the keys of all qualifying records into a temporary storage file, returning the first, and implementing logic so that if the user clicks PF11 then the next record is returned, while PF10 returns the previous. The classical CICS program will only deal with one record at a time, and so every time you want to move forward or backward through the file you go back to the program and read the next (or previous) record.

Imagine a program whose job it is to retrieve CustF records and provide their data via a JSON message to a requesting program. Unlike a 3270 screen, a web service message is not restricted to 1920 characters (24 lines * 80 characters), and so instead of returning one record at a time, requiring the requesting program to keep track of record position and read records as necessary, it might be much more convenient to simply return all the possible records at once, allowing the requesting program to populate a combo box, display them as a table, or do whatever it wants with the set of records. To achieve this, add RESPOND record.* to the GET statement, and/or the END GET statement. For example

GET custf KEY(CustF.Account OR CustF.Name) RESPOND OWSPG1;

END GET CustF RESPOND OWSPG2;

GET custf KEY(CustF.Account OR CustF.Name);

END GET CustF RESPOND OWSPG1;

If the GET statement uses RESPOND then there MUST be an END GET file RESPOND statement.

The response is prepared at the END statement, and therefore will include any changes to the record that have been made between GET and END GET.

RESPOND is designed to be used with the logic and data structures that are generated from New/Logic/WebService. Refer to JazzUGSOA4.htm to see how to generate such programs. If you create the logic and data manually, then you must ensure that the data structures are correct. The record named by RESPOND must be a record or a group with a structure that follows these rules: -

· There must be two SMALLINT control variables, named JZ-filename-ReadTo and JZ-filename-NbrReturned

· Then an array of records: minimum dimension 1, maximum 1000.

· Each record may be a complete copy of the database record or a selection of fields from the record.

· The fields must include the record’s primary key.

· Any TINYINT fields in the corresponding record should be defined as SMALLINT: this is to avoid a problem where some hexadecimal values, such as X‘03’, can cause SOAP message errors.

Here is a valid example, created automatically by Jazz: -

DEFINE OWSPG1 SERVICE DATA([Output message

ERROR VARCHAR(80) [make longer if necessary],

ViewState LIKE IWSPG1.ViewState,

JZ-custf-ReadTo SMALLINT VALUE 0,

JZ-custf-NbrReturned SMALLINT VALUE 0,

custf (3) GROUP,

Account LIKE custf.Account,

Region LIKE custf.Region,

District LIKE custf.District,

Name LIKE custf.Name,

SalesThisMonth LIKE custf.SalesThisMonth,

SalesYTD LIKE custf.SalesYTD,

Billingcycle SMALLINT,

DateCommenced LIKE custf.DateCommenced,

GET … RESPOND will read as many of the qualifying records as possible into the array of records, and set the control variables. For example: -

If there are three qualifying records, then

· JZ-custf-ReadTo will be set to 3

· JZ-custf-NbrReturned will be set to 3

· The first three rows of OWSPG1.custf will contain values from these three records.

If there are more records than can be returned, say 5, then the maximum number, 3, are returned. The input message contains a Skip field named (in this case) JZ-custf-Skip and client programs can use this to implement paging logic. For example if you set JZ-custf-Skip to 3 then records 4 and 5 will be returned. You can test if there are more records by testing that Skip + Returned < ReadTo. For example, in the Visual Basic program used to invoke a demonstration invoking program WSPG1 that could return up to 3 records: -

If Iwspg1.jz_custf_Skip + OWspg1.jz_custf_NbrReturned < OWspg1.ViewState.jz_custf_ReadTo Then

' There are more records

Else ' No more records

End If

Iwspg1 is the service’s input message (the invoking program’s request message), and OWspg1 is the service’s output message (the response).

Updating Records with GET UPDATE

By adding UPDATE to the GET statement you can retrieve a record from VSAM or SQL, make changes to it, and then update the record in the database. For example, this is a simple batch update program that will update FRegion.Name values from an input file.

PROCESS InFile ….

GET FRegion WHERE (FRegion.Region = InFile.Region) UPDATE;

FRegion.Name = InFile.Name;

END GET FRegion UPDATE;

END PROCESS InFile;

Similarly, this is part of a CICS program updating the CustF file: -

GET custf WHERE(CustF.Account=CICS2C.Save.Account) UPDATE CHECKCOPY(CICS2C.SAVE);

ACCEPT (CICS2S.Region,CICS2S.District,CICS2S.Name,CICS2S.SalesThisMonth,CICS2S.SalesYTD,

CICS2S.Billingcycle,CICS2S.DateCommenced);

END GET custf UPDATE;

GET will create an initialised record (mostly blanks and zeros) in memory when it doesn’t find the record that it’s looking for. GET file UPDATE will add this record to the file, so that the program above can create new FRegion records from InFile.

The END GET must repeat UPDATE keyword.

ONCHANGE

Normally updating happens at END GET. However in batch programs you may choose to use ONCHANGE, like this: -

PROCESS InFile ORDER InFile.Region;

GET FRegion WHERE (FRegion.Region = InFile.Region) UPDATE ONCHANGE;

FRegion.NbrInFile += 1;

END PROCESS InFile;

ONCHANGE is designed for a batch situation where the GET … UPDATE is written within a loop and there may be several records with the same matching key. Normal logic would read the record at the GET, add one to the value of FRegion.NbrInFile, and then update the record. If there could be dozens of records for each FRegion.Region value then there is a lot of unnecessary I/O. Adding ONCHANGE causes Jazz to generate logic that only reads and writes records when the WHERE value (FRegion.Region) changes. Updating occurs before the GET attempts to read the next record, and also when the program terminates (so that the last record read is updated). If the previous GET created a new record in memory, then this new record is inserted into the database. Thus several records may update the same GET record, but I/O is minimized with an appropriate input sequence. $Found remains set true or false from the last actual I/O.

Without ONCHANGE the GET … UPDATE will require an END GET, and updating will take place when the END is reached. Each iteration of the PROCESS loop will cause a record to be read, and then updated.

Preventing Updates: Update Cancel

What if you’ve set up an update with GET … UPDATE but in some conditions you don’t want to update the record? You can turn off updating with UPDATE filename CANCEL. For example

GET Vfile WHERE (Vfile.charfield = r2.charfield) UPDATE;

…

IF Vfile.Region = 5;

UPDATE Vfile CANCEL;

END IF;

UPDATE … CANCEL does not undo an update. This would have no effect: -

GET custf WHERE(CustF.Account=CICS2C.Save.Account) UPDATE CHECKCOPY(CICS2C.SAVE);

ACCEPT (CICS2S.Region,CICS2S.District,CICS2S.Name,CICS2S.SalesThisMonth,CICS2S.SalesYTD,

CICS2S.Billingcycle,CICS2S.DateCommenced);

END GET custf UPDATE;

IF CICS2S.Region = 3;

UPDATE Custf CANCEL; <= This is completely pointless

END IF;

Note that you always write UPDATE … CANCEL whatever the update type: you don’t write REWRITE … CANCEL or CREATE … CANCEL.

Updating Key Values

When updating a record you may change any of its fields except its KEY field. Thus

GET custf WHERE(CustF.Account=CICS2C.Save.Account) UPDATE CHECKCOPY(CICS2C.SAVE);

ACCEPT (CICS2S.Account, … );

END GET custf UPDATE;

is incorrect, as it can change the value of the record’s key. This also applies to attempts to change the key through a redefinition: it is just as invalid to attempt to change the key through field Custf.AccountAsChar: -

DEFINE CustF VSAM DATA(

Account PIC '999999' HEADING 'Account Number' KEY,

AccountAsChar CHAR(6) REDEFINES CustF.Account,

If you want to change the record key, you must delete the record with the old key, and create a new record with the new key value. However there is no problem with alternate index keys: you may change the values of DKEY and UKEY fields just like any other fields.

Updating Records Through an Alternate Index

You can define fields as UKEY or DKEY: for VSAM this uses an alternate index to look up the record. UKEY means “Unique Key”, and DKEY means “Duplicate key”. You’d use UKEY for situations like recording a person’s social security number, while you’d use DKEY for their name: -

DEFINE CustF TYPE(VSAM) DATA(

Custid PIC '999999' KEY,

SocialSecurity CHAR(15) UKEY,

Name CHAR(30) DKEY,

other data …

You can GET records based on these UKEY or DKEY values: -

GET CustF WHERE(Custf.SocialSecurity = value);

or GET CustF WHERE(Name = value);

When updating records through an alternate index you can only update existing records, you cannot create new ones. Thus you cannot use the UPDATE option, instead you use REWRITE. If there can be several records for a key value, then only the first will be retrieved and updated unless your CICS program provides a Next function (PF11) to navigate through the possible records.

If you use GET file KEY(key1 OR key2) UPDATE then there will be a message and the program will use REWRITE even when the record is retrieved by its primary key.

CICS and Record Locking

In an on-line environment where many users may be updating the database at the same time it is possible that between the time that GET retrieves a record and the time that following UPDATE is to be done that another user may have updated the record. To prevent this we could lock the database when we first read the record, releasing the lock when we update it. However if we hold a lock for too long the database could quickly become unusable if there are many other users also holding locks. So we want to get the benefits of locking the database when the transaction starts without the costs of holding a lock for all this time. CICS provides a solution, called “pseudolocking”.

When the GET first reads the record we don’t lock the database, but

· In a classical CICS program we save a copy of the record in its original form in the COMMAREA.

· In a web service we can’t save a copy in COMMAREA, instead we create a CheckSum hash which becomes part of the output message.

Later, when the UPDATE is to be done the program re-reads the record, this time locking the database. The program then compares: -

· Classical CICS: compare the re-read record with the saved copy in COMMAREA

· Web Service: compare the recalculated Checksum with the Checksum returned with the update message.

If they’re the same then our update is done and the lock removed. However if they differ the update is rejected. Either way the lock is held only briefly. In the event of failure the user will have to re-apply their updates, but database integrity is preserved: the extremely rare occurrence of having to re-do an update is much less of an inconvenience than having the database freeze up from time to time due to large numbers of long-duration locks.

Jazz takes care of all of this for you with the options SAVECOPY and CHECKCOPY for classical CICS, and SAVESUM and CHECKSUM for web services.

Classical CICS

In a classical CICS program the initial GET uses SAVECOPY to save a copy of the record in COMMAREA. The record is not locked. Later the GET … UPDATE uses CHECKCOPY to compare the saved copy with the latest record. For example: -

CASE (CICS1C.Function);

WHEN (Enquiry);

ACCEPT (CICS1S.Account OR CICS1S.Name);

…

GET Custf KEY(CustF.Account OR CustF.Name) SAVECOPY CICS1C.SAVE TS(1);

…

END GET Custf RESETFUNCTION;

WHEN (Update);

GET Custf WHERE(CustF.Account=CICS1C.SAVE.Account) REWRITE CHECKCOPY(CICS1C.SAVE);

ACCEPT (CICS1S.Region,CICS1S.District,CICS1S.Name,CICS1S.SalesThisMonth,CICS1S.SalesYTD,CICS1S.Billingcycle,CICS1S.DateCommenced);

#562 I CICS1S.Error used as message field

END GET Custf REWRITE RESETFUNCTION;

…

END CASE;

For the update GET Custf REWRITE CHECKCOPY (CICS2C.SAVE); reads the record for update (locking it), and checks it against the saved copy from the previous enquiry to ensure that no other users have updated it. The program will exit with a message if there is a problem. Then ACCEPT then gets information from the input screen, validates it, and updates the record in memory. Again, there will be messages and the program will exit if there are problems. In the absence of errors the program will reach the UPDATE statement, where the record is actually updated. Note that in a CICS program, unlike a BATCH program, an explicit UPDATE statement must be written. With the different structure of an on line program Jazz can not be completely certain where the UPDATE should go, so it is left to the programmer.

Web Services

In a web service we can’t use COMMAREA in this way as web services are completely stateless, and any information saved in COMMAREA by the first part is lost, and can’t be recovered in the 2nd. Instead information has to be passed between the provider and requester in the messages. Instead of passing a copy of the record to/from the client an encrypted record digest is passed. Any change to this digest by the client, or any change to the record by another user or process, means that when GET Custf UPDATE CHECKSUM is executed a difference the recalculated record digest will differ from the returned digest, causing the update to fail.

Here is an example: a web service to update a VSAM record has been generated. When Jazz created the input and message formats it added a field called Viewstate to both messages, like this: -

DEFINE IWSPG1 SERVICE DATA([Input message

…

ViewState GROUP, [Must not be changed

CheckSum-Custf CHAR(40),

END GROUP);

DEFINE OWSPG1 SERVICE DATA([Output message

ERROR VARCHAR(80),

ViewState LIKE IWSPG1.ViewState,

…

In the Enquiry transaction, the program uses GET … SAVESUM to calculate a record digest and save this in the output ViewState field: -

PROGRAM WSPG1 WEBSERVICE MySvce CONTAINER DFHWS-DATA WSDL;

…

WHEN (Enquiry);

ACCEPT (CustF.Account = IWSPG1.Account OR CustF.Name = IWSPG1.Name) MESSAGE OWSPG1.ERROR;

GET Custf KEY(CustF.Account OR CustF.Name) SAVESUM OWSPG1.CheckSum-Custf;

END GET Custf RESPOND OWSPG1;

The client program will receive the output message which contains the CustF data and Viewstate. A new input message is prepared with updated Custf data, a copy of the Viewstate value received, and an Update function code. This is then received in the next phase of the transaction: -

WHEN (Update);

ACCEPT (CustF.Account=IWSPG1.Account) MESSAGE OWSPG1.ERROR;

GET Custf KEY(CustF.Account) UPDATE CHECKSUM IWSPG1.CheckSum-Custf;

…

END GET Custf UPDATE RESPOND OWSPG1;

GET … CHECKSUM causes the record digest to be recalculated and compared with the digest received in Viewstate. If another user has changed CustF between the Enquiry and Update transactions then the recalculated value will be different to the previously calculated value, and the transaction will be rejected, the program behaving like a classical CICS program when the saved record copy is different.

CONTINUE

When a GET … UPDATE CHECKSUM (or CHECKCOPY) detects that the record has changed the normal action is for the program to produce a message and immediately terminate. If previous statements like ACCEPT have used CONTINUE then they may have produced messages: these prior messages will be overridden by the message produced by GET.

You can specify that the program will continue with following Jazz statements by adding CONTINUE to the GET statement. Thus without CONTINUE:-

GET custf WHERE(CustF.Account=IWSPG2.Account) UPDATE CHECKSUM IWSPG2.CheckSum;

ACCEPT (IWSPG2.Region,IWSPG2.District,IWSPG2.Name,IWSPG2.SalesThisMonth,

IWSPG2.SalesYTD,IWSPG2.Billingcycle,IWSPG2.DateCommenced)

TO custf MESSAGE OWSPG2.ERROR;

#447 I IWSPG2.Region,IWSPG2.District,IWSPG2.SalesThisMonth,IWSPG2.SalesYTD,IWSPG2.Billingcycle are already numbers

END GET custf UPDATE RESPOND OWSPG2;

This will immediately terminate with a message if the re-calculated CHECKSUM is not the same as that in the input record. The ACCEPT is not executed, and the response message will show initialised record fields, not the CustF record read from the file. However, with

GET custf WHERE(CustF.Account=IWSPG2.Account) UPDATE CHECKSUM IWSPG2.CheckSum CONTINUE;

ACCEPT (IWSPG2.Region,IWSPG2.District,IWSPG2.Name,IWSPG2.SalesThisMonth,

IWSPG2.SalesYTD,IWSPG2.Billingcycle,IWSPG2.DateCommenced)

TO custf MESSAGE OWSPG2.ERROR;

#447 I IWSPG2.Region,IWSPG2.District,IWSPG2.SalesThisMonth,IWSPG2.SalesYTD,IWSPG2.Billingcycle are already numbers

END GET custf UPDATE RESPOND OWSPG2;

execution continues with the ACCEPT statement, and the response message will show the “updated” record: values read from CustF modified by the ACCEPT statement. However the file update is not performed: it’s as if an UPDATE CustF CANCEL; has been executed.

Adding Records: CREATE and FREEKEY

Records are added with GET … CREATE, using a key that doesn’t already exist in the file. You may know a free key value from a preceding GET: -

CASE (CICS1C.Function);

WHEN (Enquiry);

ACCEPT (CICS1S.Account OR CICS1S.Name);

…

GET Custf KEY(CustF.Account OR CustF.Name) SAVECOPY CICS1C.SAVE TS(1);

…

END GET Custf RESETFUNCTION;

…

WHEN (Add);

GET Custf WHERE(CustF.Account=CICS1C.SAVE.Account) CREATE;

ACCEPT (…);

#562 I CICS1S.Error used as message field

END GET Custf CREATE RESETFUNCTION;

…

END CASE;

A user attempts an enquiry for Account = 123456, but this is not found, so the user selects the Add function. Now the GET … CREATE is expected to “fail”, not finding an error but creating a record in memory with the account number sought, everything else set to default values. Because CREATE is present, an error is reported if in fact this record is now found.

FREEKEY avoids the necessity to know the value of key to be added. Write

GET Custf FREEKEY CREATE;

FREEKEY finds the next free value and uses this as the record key. For a simple key like CustF.Account the last (highest) key in use is found, and incremented. For a SQL compound key only the last element of the key is incremented, and preceding elements can be specified with a KEY option. For example,

DEFINE BLOCK SQL PREFIX BLK DATA(

REGION SMALLINT REQUIRED KEY,

DISTRICT SMALLINT REQUIRED KEY PART 2,

BLOCK SMALLINT REQUIRED KEY PART 3,

…);

GET Custf KEY (REGION AND DISTRICT) FREEKEY CREATE;

This increments BLOCK within the specified REGION and DISTRICT.

FREEKEY can “Add to a letter” if the key field is CHAR or VARCHAR, “counting” through ‘a’ to ‘z’, ‘A’ to ‘Z’, and ‘0’ to ‘9’. When there are no more letters and numbers available, the character is set to ‘0’ and the character to its left “increased”: for example ‘abcz’ becomes ‘abd0’.

NBR

TS records can be retrieved by number. Thus if you have defined a TS file like this: -

DEFINE TS TYPE(TS) DATA(

RECORD LIKE Orders.*)

and you have written 20 records to it with

PROCESS ORDERS WHERE …

TS.Record.* = Orders.*;

WRITE TS;

END PROCESS;

then you can read records by number using NBR, or simply by enclosing the number in brackets. For example, to read the 17th record write

GET TS (17);

IX = 17; [IX should be a SMALLINT field]

GET TS (IX);

As with GET WHERE, if the record number is too large or too small the GET returns a NOT FOUND condition and the record area is initialised.

This option is only valid with PSAM files, i.e. File Type F, FB, V, VB, or U. If used, it must be the only option.

GET TEST1 NEXT; reads the next record from TEST1 unless there are no more records, in which case TEST1-ENDFILE is set True.

[1] Strictly speaking a TS file CAN be used in a web service, but it can’t be held from one request/response to the next, so in web service programs Jazz doesn’t use TS files automatically, and it is very unlikely that you’ll do so yourself.