Create
Routine to Load a Table
Create
Routine to Load a Table
Step
2. Selecting the input source
This form is invoked when you click the [LProc] button from a program with property EZT. Dialog LProc allows you to select a table, and an input file from which it can be loaded, then select the field(s) from the input file to supply the key and data values for the table.
Initiating
dialog LProc
The dialog is initiated by clicking [LProc]: -
Minimum requirements are: -
· This must be a batch program, with property EZT. EZT causes
o The [Services] button to be changed to [LProc]
o
The batch program is generated with various
markers. The markers *# Copy Files here ==> and *#Copy Routines here ==> are
used by LProc.
· The program must contain a definition of the table to be loaded. A table is a repeating field or group, in working storage
o If the table is a repeating group, then the group should contain a key field identified with property SKEY or TKEY, or several key fields identified with TKEY. The group must also contain at least one value field. However if no fields have these properties, then the dialog will insert them for you.
· The program must also contain a definition of an input file from which the data will be loaded.
In the example above, a table and file definition are both present from COPY EZT-ETable2-DATA;
Click [LProc] and the dialog opens: -
Step 1. Find Table
Click [Find Table] and a treeview is displayed showing the DEFINE statements within this program that could contain tables. The treeview shows all working-storage definitions in the program, but the definitions can’t be expanded to locate tables unless they contain repeating fields or groups. Navigate to the table that we want to initialize and click it to move to the next step. The table that we want to initialize is defined like this: -
DEFINE EZT-ETable2-Data DATA(
GROUP1 GROUP,
W-TABLE-GROUP
GROUP,
W-TABLE(5000) CHAR(5),
END
GROUP,
W-TABLE-REDEF
(5000) GROUP REDEFINES EZT-ETable2-Data.GROUP1.W-TABLE-GROUP,
T-XYZ
CHAR(4),
T-TP
CHAR(1),
END
GROUP,
If we made a mistake by selecting something that is not a table, like EZT-ETable2-Data, then there will be an error message. If we selected a valid table but not the one we want, just click [Find Table] again.
We want to be able to look up the table by T-XYZ, to get a value from T-TP, so we select table W-TABLE-REDEF: -
Group tables like W-TABLE-REDEF should have one or more fields with property SKEY or TKEY. Because the table does not contain any fields with these properties, the dialog responds with: -
Selecting Table Keys
Expand the table definition (click [+]) and select the key field, T-XYZ. The message changes to
Uncheck the Select Key(s) checkbox. The messages disappear we continue with Step 2. Selecting the input source
Step 2. Selecting the input source
Once the table keys have been identified, either with Selecting Table Keys or because the definition
contains SKEY or TKEY properties, then the form looks like this: -
Click [Find File/Fld] to locate the file and its fields from which the table will be loaded. The tree view will display all the files defined from which we can read data – definitions with type VSAM, ESDS, F, FB, V, VB, U, or SQL. In this case there is only one, VSM1, which is defined like this: -
DEFINE VSM1 VSAM DATA(
GROUP1 GROUP,
V-XYZ CHAR(4) CAPS KEY,
FILLER
CHAR(41),
V-TP CHAR(1),
End GROUP)
DSNAME 'JAZZUSR.VSAM.VSM1';
Click [+] to expand the node, and select the field corresponding to the table key, EZT-ETable2-Data.T-XYZ. This is V-XYZ: -
The dialog responds by showing us the first assignment, and changing the field name to the next field that we want, EZT-ETable2-Data.T-TP. We select V-TP. Another assignment line appears. Because this has completed the list of fields in W-TABLE-REDIF, the Finish controls appear.
[Undo]
As we are selecting fields the dialog is checking that the data formats are correct: they must be the same type as the table fields. In this case they are, as T-XYZ and V-XYZ are both CHAR(4), and T-TP and V-TP are both CHAR(1). If we select something invalid, like PK or FILLER or any of the $ fields then there will be an error message and we can’t proceed until we select something else. However if we’ve selected something “valid”, but then realise that it was the wrong choice, click [Undo] to go back a step.
Step 3. Finish
With all fields selected the routine name is displayed, and [Finish] button exposed: -
If you wish, you can
· Change the routine name.
· Change the action to be taken if there too much input data – in this case, more than 5000 records. The default action is to set a return code (default value 8) and continue the program. There will be a message like “Table limit (5000) Exceeded” displayed on SYSOUT, and the excess values will be ignored. You can choose a different return code, and if you check Abort[ü] the your program will be aborted.
· Uncheck Close Input[ü].
· In the example above, the key field is T-XYZ, and it is loaded from V-XYZ, but T-XYZ was not identified with SKEY until we clicked [OK] to the message below (Click [Finish]). If SKEY had already been edited into the table definition, and the table [RB1] is being loaded from a VSAM file, then another checkbox appears in the Finish section: -
This will be checked if the field from the VSAM file has property KEY, which indicates that it is the VSAM file’s primary key, and so we don’t need a sort. If we’ve loaded the SKEY field from a VSAM file but this is not the VSAM primary key, this will be unchecked. We can change this if we want to change the default processing. This checkbox does not appear for file types other than VSAM.
Click [Finish].
· If you got here through Selecting Table Keys then there is a message that your table definition (e.g. EZT-ETable2-DATA.jzc) has been saved. Click [OK]. This saves the table definition with the SKEY or TKEY properties that you selected with that dialog.
·
The dialog disappears, and a PERFORM statement
is generated into the program at location *# Copy Files here ==>
PERFORM LOAD-EZT-ETable2-Data;
*# Copy Files here ==>
· The Load routine is generated into the program at location *#Copy Routines here ==>
PROGRAM ETable2 BATCH EZT;
COPY EZT-ETable2-DATA;
PERFORM LOAD-EZT-ETable2-Data;
*# Copy Files here ==>
*#Copy Process logic here ==>
ROUTINE LOAD-EZT-ETable2-Data;
PROCESS
VSM1 ORDER(VSM1.V-XYZ)COUNT JZ2.IXLD SID(23);
IF
JZ2.IXLD >
5000;
DISPLAY('Table limit (5000) Exceeded. Load Terminated');
COBOL.RETURN-CODE
= 8;
CLOSE
VSM1;
EXIT
LOAD-EZT-ETABLE2-DATA;
END
IF;
EZT-ETable2-Data.T-XYZ(JZ2.IXLD) = VSM1.V-XYZ;
EZT-ETable2-Data.T-TP(JZ2.IXLD) = VSM1.V-TP;
END PROCESS VSM1;
CLOSE
VSM1;
JZ2.IXLD
+= 1;
FOR JZ2.IXL2 = JZ2.IXLD TO 5000;
EZT-ETable2-Data.T-XYZ(JZ2.IXL2) = HIGH-VALUES;
END FOR;
EXIT
LOAD-EZT-ETABLE2-DATA;
END ROUTINE LOAD-EZT-ETable2-Data;
*#Copy Routines here ==>
Notes
1. You may need to move PERFORM LOAD-EZT-ETable2-Data; to somewhere more appropriate within your program.
2. You can edit the Load routine if necessary. For example, in this case it was generated with PROCESS VSM1 ORDER(VSM1.V-XYZ) but we don’t need ORDER if V-XYZ is the primary key.
3. For sequential and ESDS files an ORDER option will be needed to ensure that sequential data is loaded in the right order. However ORDER is not needed if the table key has property TKEY, not SKEY.
4. The load routine won’t load more records than the maximum allowed by the table. A message is displayed and Return Code (= 8 unless you changed it) set, but the program continues unless you checked Abort [ü].
5. If there are insufficient records, then the unused key values are set to HIGH-VALUES for CHAR keys, or 9’s (to the number of digits) for numeric keys.
Clear and Cancel
[Clear] will clear the form, and you can start again by selecting a table.
[Cancel] exits the form immediately, without continuing to generate a load routine.
Notes
SKEY and TKEY properties
A table can have one field with property SKEY, or 1 or more fields with property TKEY. Fields with these properties are used to search the table, other fields are called “Value fields” and provide a value corresponding to the key field(s).
If SKEY is used, then the table must be loaded in ascending order of this field, and a binary search will be used. For large tables, like our example above with 5000 rows, binary searching can be much more efficient, but for much smaller tables a sequential search is simpler and so they are generally used for tables with less than 50 rows as there is little difference in search efficiency for tables this small, and the table doesn’t have to be sorted into order for the search to work. When relevant, MANASYS will choose whether to use SKEY or TKEY for the key fields based on its default setting which will be 50 unless you have reset the default (Configuration, COBOL tab).
Searching Tables
You will use a SEARCH statement to look up a table and return the matching result, as this is simpler and probably more efficient than writing your own logic. Note that you don’t have to specify whether to use a sequential or binary search: this is determined automatically. If the table’s key has property SKEY then COBOL SEARCH ALL, which is a binary search, will be used, but TKEY will just use SEARCH. COBOL’s SEARCH ALL only allows one search argument, this is the reason for the rule that if there are two or more table keys they must use TKEY, not SKEY.
Tables and Files
The dialog applies strict rules: for every table field there must be a corresponding file field with the same format. In the example above, T-XYZ was loaded from V-XYZ, both were CHAR(4), and T-TP was loaded from V-TP, both CHAR(1). There can be unused fields in the input file, but there must be enough, and their formats must match exactly: you cannot assign a longer or shorter CHAR variable, or change a number’s format (e.g. SMALLINT to DECIMAL).
The input file may contain fields that you cannot assign. For example, selecting data from VSM1 we saw: -
You will get error messages if you attempt to assign FILLER, PK (this is a special name meaning “Primary Key”), or any of the control names like $Found.