Data Transformation

Data Conversion is, in concept, a simple process. Some (or all) records from a file are read, transformed in some way, and written to an output file. You can do this with the standard batch facilities described in previous Users’ Guide chapters, or you can use the special facilities described in this chapter which have been developed to allow the process to be managed precisely with Excel Spreadsheets.

This brief video, https://www.jazzsoftware.co.nz/Videos/Conversion/Conversion.html will give you a quick overview. View this first, then read below to get the detail that you need to actually use this facility.

1 Summary

Companies converting files often use Excel spreadsheets to plan a conversion. MANASYS can provide a lot of help in creating these spreadsheets, and then automatically generate a program from the conversion spreadsheet after it has been edited and validated. The essential concept is that Jazz descriptions of the input and output records are prepared, and the correspondence between the input and output records are defined by the conversion spreadsheet.

Under the menu New/Data Conversion you’ll find these facilities: -

The first three options are also found under the Data menu: -

1. Define Data from Spreadsheet. A spreadsheet is read and a Jazz definition prepared from it (if possible).

2. Import COBOL definition. A COBOL record definition is read, and a Jazz definition produced. This feature is described here.

3. Validate definition. This validates a definition by creating a program that attempts to read and print (in FIELDTABLE format) the first record from the file. It also adds Zero to all packed (DECIMAL and MONEY) fields in an attempt to force a data check. Refer to New Batch/Validate File Definition for more information.

2 Getting Started

A data conversion starts by preparing and validating a definition of the input record. Once we have ensured that we have a valid definition of the input we continue with Conversion Spreadsheet. Each conversion spreadsheet deals with the conversion of one file. The file may have several different record layouts.

For our first example we’ll convert file FILEV1 into file FILEV2. Here is the Jazz definition of FILEV1: -

COPY Types;

DEFINE FILEV1 VB DATA(

RDKey GROUP,

Region DECIMAL(3) RANGE (1:20),

District DECIMAL(3),

end GROUP,

Name CHAR(40),

SalesThisMonth MONEY(7,2),

SalesYTD MONEY(7,2),

BillingCycle LIKE Types.Month,

DateCommenced DATE DPIC 'dd mmm yyyy')

DSNAME 'JAZZUSER.FILES.IN1';

Here is the output file, FILEV2. Compared to FILEV1, highlighting shows that the formats of some fields have been changed. DateCommenced has been converted to display formats, and redefined so that the date components can be separately referenced.

DEFINE FILEV2 FB DATA(

Region SMALLINT RANGE (1:20),

District SMALLINT,

Name CHAR(50),

SalesThisMonth MONEY(7,2),

SalesYTD MONEY(7,2),

DateCommenced PIC '99999999',

DateCommencedR REDEFINES FILEV2.DateCommenced GROUP,

YEAR PIC '9999',

Month PIC '99',

Day PIC '99',

end GROUP)

DSNAME 'JAZZUSER.FILES.FILEV2';

3 Producing a new Conversion Spreadsheet.

Click Conversion Spreadsheet and Jazz responds with the Conversion Spreadsheet form. To create a new spreadsheet, we enter a name and click [New]: -

The spreadsheet name will become the name of the conversion program. It must be a valid program name (up to 8 characters, no special characters or blanks), and be different to the file names.

Click [New] and Jazz responds: -

The template xlsTemplate.xls will be found in your Jazz Programs folder. For versions of Jazz prior to 16.4.279, this template was called AttrVconv. Click [Find] if you want to choose another template. Check Replace if the spreadsheet already exists and you want to replace it. Uncheck any columns that you don’t want. Click [Create].

a. Template

You can skip this step if your template is already selected, as xlsTemplate is above. A template is a basic spreadsheet, with a header area with several named cells where particular information like the file names is put, and a files area where the file layouts are displayed, and the mappings to transform from Input to Output defined. Here is an example: -

If you create your own template then you should start from the supplied template. You can easily change the spreadsheet title and add a logo, but you must be careful not to remove any of the defined range names, which are CBX, ErrorColour, ErrorMessage, Headings, HighlightColour, Input, Name, NormalColour, and Output. “Headings” is row 5, which has grey highlighting.

b. Creating the spreadsheet

On clicking [Create] some more buttons appear on the Conversion Spreadsheet form: -

and an empty spreadsheet is created with the spreadsheet name and columns that we selected: -

c. Find Input

Click [Get Input File Definition] to locate the definition of the input record. This form appears: -

These options are: -

· [Create Jazz from Spreadsheet]. Use this option if the spreadsheet already contains input definitions in the COBOL-ish format described here. Because this spreadsheet already has the rows and columns identified in a standard way the early part of the standard “Define from Spreadsheet” process is skipped. The process is fully described here.

· [Create Jazz from COBOL] This runs the COBOL conversion program, creating a Jazz definition that can be put into the spreadsheet.

· [Find Jazz Definition] will locate an existing Jazz definition. It will be in your project’s Jazz_CopyDefs folder.

· [New Jazz Definition] will open a workbench session where you can write a new Jazz definition. This would be your last resort, as the other options are easier.

Here we’ve used the option [Find Jazz Definition], and navigated to the definition FILEV1.jzc.

We can enter a name or click on the definition that we want. Here we’ll click “FILEV1.jzc”. Click [Open] and our program resumes with the name of the input file filled in. There are no other changes: -

d. Defining the Output Record

We follow the same process from [Get Output File Definition], with two changes. Firstly, when we click this button there is a new textbox where we can set Align Limit: -

Secondly, selecting an output file doesn’t just put the output name into the spreadsheet header, it also causes both the input and output data names to be copied into the body of the spreadsheet.

If we set Align Limit to 0 and select the output record, Jazz will not attempt to align the input and output fields, but instead simply write the input and output definitions into the spreadsheet from the top, after the headings line (as shown here with different files): -

This is usually not terribly useful, as we’d have to edit the spreadsheet to align Name and the other mismatched fields. It’s easier to let Jazz do this for us. But Jazz won’t always get it right: it uses a simple name match, and will be confused if there are many fields with the same name (typically “FILLER”). And if the records do not use the same names and you want Input.FieldX to correspond to Output.FieldY then you may have no alternative than to set Align Limit to 0 and edit the spreadsheet by hand.

If we set Align to 5 then Jazz will look ahead 5 lines in the output and input definitions to attempt to find a match. This is the spreadsheet that results. Notice that the layouts have been aligned so that field names match. Notice also that columns R to X, which contained copies of the output column names, have been hidden: -

Blank when =

Once the input and output columns have been set, the spreadsheet columns are resized, the columns to the right of Remarks are now hidden, and the checkbox Blank_when_= now works. Now if we check Blank_when_=, the output columns are displayed as blank unless they differ from the input columns: -

Blank_when_= gives a quick and easy way of seeing where the input and output data differs.

e. Set Mapping

With input and output layouts defined into the spreadsheet click [Set Mapping] to set default values for mapping, and have differences noted in Remarks.

Default mapping is only an initial guess by Jazz, and you may need to edit it. Not only are Jazz default mappings sometimes wrong, there are other mapping options that we might want to use.

Mapping Values

Mapping values may be

· blank,

· SAME,

· DPIC

· $F(name).

· INIT (or INITxxxx or FILL),

· OMIT,

· a value.

Usually mapping is specified for group elements and mapping for the group itself is left blank. Similarly mapping of redefined fields is usually blank as the base field will have been mapped. When there are multiple records, mapping of the common area is left blank in following records.

SAME

The input and output fields must be compatible, but they don’t have to have the same format. Thus you cannot move data between numeric and non-numeric formats, but you could move data to/from DECIMAL from/to INTEGER and so on. If mapping SAME is specified for two groups then the input group will be copied to the output group as if they were two CHAR fields, without regard to the group structuring. This carries a risk of error if the groups are differently structured, and so is not the default.

DPIC

DPIC assigns numeric and coded fields to PIC or CHAR fields in their display format, i.e. as they would be displayed in a print line or display. For example, in this example fields BillingCycle and DateCommenced are mapped with DPIC: -

In the input BillingCycle is defined as

BillingCycle LIKE Types.Month,

So it inherits definition

DEFINE Types SYSTEM DATA(

Month CODES(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec),

This makes it a TINYINT field in which 1 means Jan, 12 means Dec. Assigning it to the CHAR(3) field in the output will give it a value of Jan to DEC unless the value is outside the range of 1 to 12, in which case the output will be set to ***.

DateCommenced is defined

DateCommenced DATE DPIC 'dd mmm yyyy')

so with this DPIC a date of 28^th May 1958 would be formatted as 28 May 1958

$F(Reference)

Fields might “cross over”, for example one row containing In.A containing Out.B and another row containing In.B and Out.A. There’s no way that you can arrange the spreadsheet so that corresponding fields are on the same line. $F allows you to map these situations. Examples: -

This means that in the output program there are assignments: -

Out.A = In.B;

Out.B = In.A;

Out.C = In.A;

Out.D = In.A;

Syntax: value is a positive or –ve integer, a word, or qualified reference. The value is interpreted as a field in the input file. In all valid cases the reference is resolved to a field in the input file, and the $F(xxx) reference will be converted to the explicitly qualified form.

INIT and Constants

INIT set the output field to its default value: normally blanks or zeros, unless the field has been defined with a VALUE clause in which case this value will be used. Any word starting with “INIT”, e.g. “Initialise”, “Initialize”, or even “Initxxxx” is equivalent to INIT, as is “FILL”.

A constant value is any number, or any text other than mapping words (SAME, $F(…, INIT…, FILL, and OMIT). Constants must be compatible with the Type of the Output Field.

With INIT or a constant, if an input field is present on this line, it will be ignored.

OMIT

OMIT, like blank, means that nothing will be assigned. You can use OMIT when there is an INPUT field but no output field and Jazz would otherwise produce an error message when processing the spreadsheet

SUM Values

MANASYS will generate a program that will convert the files, and produce a brief control report. By default, this report will count the number of records read and written, but you may want to calculate control totals by summing various numeric values. If so, you can indicate this by setting a value in the SUM column

SUM can have values LEFT, RIGHT, or BOTH, depending on whether you want to accumulate Input, Output, or Both values for the control report that will be printed when the program terminates. SUM must be blank unless the corresponding field is numeric.

f. Saving and Reviewing the Spreadsheet

Once input, output, and mapping have been set, [Save Spreadsheet] and [Process Spreadsheet] appear

Click [Save Spreadsheet]. You can now exit from this form by clicking [Cancel] and come back to it after the spreadsheet has been reviewed, and edited. To learn what happens when you click [Process Spreadsheet], click Processing the Conversion Spreadsheet.

4 Files with Multiple Record Formats

a. Introduction

Input and output files may contain several different record formats. For example, files with a header record, detail records, and a trailer record are common. The detail records might have customer header, transactions, and customer trailer. Or the file might simply contain “Type A”, “Type B”, and “Type C” records. Jazz conversion spreadsheets can handle files like this with multiple record formats, using CASE logic to distinguish between formats.

Here’s an artificial example where I’ve started created a spreadsheet with definitions CaseI and CaseO, each of which has three record layouts. Here’s an abbreviated version of copy book CASEI: -

* Test Input Record for job 13.7:$Case.

DEFINE CaseI VB DATA(

Common-Header GROUP,

RType CHAR(1),

CommonData CHAR(15),

END GROUP,

Header-Info GROUP,

HDR-Fld1 INTEGER,

END GROUP)

DSNAME 'CASEI';

DEFINE IN1 DATA(

Header LIKE CaseI.Common-Header,

SalesThisMonth DECIMAL(7,2),

SalesYTD DECIMAL(7,2),

BillingCycle TINYINT,

DateCommenced CHAR(10));

DEFINE LMRDRL REDEFINES DATA(

Header LIKE CaseI.Common-Header,

KEY GROUP,

…

In a real situation, the definition of CASEI might use COPY for the 2^nd and following records instead of the layouts being explicitly written here.

An important thing is that there is a field in the header region that can distinguish which record layout is being processed. Here it is field RType.

When there are several record layouts then they must all redefine the first – Jazz will insert REDEFINES if necessary, with a message like this: -

When the output file CaseO is selected as about Jazz responds with the Align Layouts dialog where we can confirm that the record layouts are correctly matched: -

In this case they are, so we simply click [Close], and Jazz creates a spreadsheet with data arranged so that the level 1 line of each layout is aligned, and the different layouts are separated by command lines.

See JazzWKAlignLayouts.htm for more detail about this form and its options.

b. Case Logic

We’ll need to define logic to tell Jazz when to use record CASEI, when to use INI, and when to use LMRDRL. You’ll need to understand how the data is organised.

The CaseI record contains a record type, and other stuff (CommonData) that is in every record. It also contains some header information that is not in every record – in a real case this would be data like the time last written. The following records start with the Common-Header, then their own fields follow.

We are asked how to handle the first record: -

Click [All Common] when the whole first record is common, so we want this spreadsheet section to be handled in all cases.

Click [Part Common] if the first record is partly common and partly specific. Jazz responds by displaying the Select Data form. We tell Jazz how much of the form is common by selecting the first field or group after the common section: -

Click [Normal Case] if we want the Case1 record to be an alternative, like all the other records.

With the first record handled as [Part Common] Jazz creates a spreadsheet like this. Before CaseI.Header-Info, and before the other record layouts, there are lines with command $Case(?).

There will be three Case conditions in our spreadsheet. When the spreadsheet has been created and the Conversion Spreadsheet form refreshes, you’ll see that it has another button, [$Case(?)]: -

c. [$Case(?)]. Defining conditions

Click the button [$Case(?)] and Jazz finds the $Case(?) commands. For each of these the Select Data dialog appears for us to define the conditions that determine whether we’re processing a CaseI record, an IN1 record, or a LMRDRL record. In this example we’re using the value of RType, a CHAR(1) field, which has value ‘A’ for CaseI, ‘B’ for IN1, and ‘C’ for LMRDRL.

When the Select Data dialog appears for the first time we select the field RType: -

This puts the field name into the Output textbox. We complete the condition by adding “=‘A’”. Similarly we add “=‘B’” in the second case, and “=‘C’” in the third. Note that we use single quotes, not double quotes, because Jazz and COBOL don’t use double quotes.

The comment line

$Case(?). R:12, L:CaseI

identifies the spreadsheet row and layout name.

The output of Select Data is copied back into the spreadsheet, and will become the condition used by the Jazz program: -

When a field has type BOOLEAN, or a CODES or CONDITIONS property, then the SelectDialog data can write the whole condition. Such fields are identified with an “=” symbol after their name, and there is another level showing their possible values. For example, field AFF2 is defined with CONDITIONS (the Jazz equivalent of COBOL’s Level 88).

Clicking one of these values creates a condition like

AMCQIN.AFF=MC-STD-CR

Click several values and a compound condition is created: -

AMCQIN.AFF=MC-STD-CR|AMCQIN.AFF=MC-GOLD-CR

Since the Output textbox is editable, a complex condition with other comparison and Boolean operators, and parentheses to control how individual conditions are related, can be created. Or you may prefer to edit the condition in the generated Jazz program.

5 Getting File Definitions from a Conversion Spreadsheet

Jazz has a general facility to create Jazz definitions from a spreadsheet. If we click the option [Get Jazz from Spreadsheet] then this is invoked from the Conversion Spreadsheet program. Because a conversion spreadsheet has a known format Jazz already knows where to find the input and output columns and other information, so it is able to skip some of the initiation steps of the general process.

Here we have selected the spreadsheet Melb20161007 that has been prepared using the conversion spreadsheet template, but using COBOL-like field definitions. No Jazz record definitions exist at this point.

We click [Get Input File Definition], and then [Create Jazz from Spreadsheet] to have Jazz create a record definition from the information that is already in the spreadsheet. This starts the process “Define Data from Spreadsheet”, which starts with its control form: -

Because this is using a standard conversion template Jazz already knows where to find the input fields, so we can simply click [Create Definition] to proceed. We’ll leave [P] Step checked. As Jazz processes each spreadsheet line the input definition will be replaced by a correct Jazz definition.

a. Starting the Record definition

On clicking [Create Definition] this appears: -

Since this is going to produce a conversion program the Jazz data definitions should not be working data, but should describe a file. They should therefore have type V, VB, F, FB, U, VSAM, or XIO. Change the type if necessary, and click [OK].

The next issue: Jazz displays this: -

Jazz has suggested a file name. If we’re happy with this, just click [Return]. Jazz shows us the definition line that it will generate: -

Click [Next] to accept this and advance to the next line. The spreadsheet is amended to put AMGL in the Input field (cell E2), and the first data row (Cells A6:G6): -

b. Processing Following Fields

As we click [Next] Jazz steps down each spreadsheet line, converting it to a Jazz-format definition, indenting the field name, and so on.

When Jazz encounters a type code that it doesn’t recognise it will display a form like this: -

giving us the chance to correct the Jazz Type if Jazz has guessed incorrectly. As it works down the spreadsheet Jazz builds up a table of code/Jazz format equivalents: from now on “9” will mean “PIC ‘9(@Lth)’”. In the substitution from Excel to Jazz, this will become (for example) PIC ‘9(3)’ if the field length is 3 bytes.

Jazz uses GROUP/END GROUP rather than level numbers to express field hierarchy. Here Jazz has inserted line #11 with END GROUP: -

c. Finishing the Definition

When the definition is completed Jazz displays the Jazz record definition in the workbench: -

This is an opportunity to add more record information like a DSNAME option, and validation and display properties. You will probably just click [Exit] to close the workbench and save the record, but if you know the data set name of the input or output file you should add DSNAME to the definition to avoid the need to edit the generated JCL later.

Jazz now checks to see if the positions given in column D agree with the positions calculated from the record definition. In this case they do, and Jazz produces this message: -

d. The Output Record

Clicking [Get Output File Definition] follows the same process. Because blank spreadsheet lines were inserted for END GROUP when the Input file was defined, these blank lines can be used for END GROUP for the output record as well.

When the output record is finished, a message like this may be produced: -

This is simply an error in the Position column, caused by a typo in the conversion spreadsheet that we started with. If LAST-MAINT-OPER starts in position 122 and is 3 characters, then FILLER should have started in position 125, not 123. In this case, the message can be ignored.

6 Edit the spreadsheet.

When you have produced a conversion spreadsheet as above you can immediately edit it before saving it, or you can save it and edit it later. Spreadsheet editing occurs separately from Jazz, but in the following step you’ll import your edited spreadsheet back into Jazz and use it to control the generation of the conversion program. Jazz will validate it, and errors (that aren’t corrected) will prevent the conversion program from being generated.

In the following notes there are many places where rules are given, for example “A new name must be valid by the normal Jazz rules”, but these rules are not checked when you are editing the Excel spreadsheet, but can only be checked when you import the spreadsheet. You may choose to validate your spreadsheet several times by importing it, but not continuing with the next step of generating a conversion program from it, as this allows Jazz to check for errors and to recalculate level, position, etc.

A conversion spreadsheet consists of four sections
1. The input record (up to 7 columns)

2. The output record (another 7 columns, display controlled by the checkbox Blank when =)

3. 3 general columns (Mapping, Sum, Remarks)

4. A hidden set of output record fields: columns R, S, T, U, V, W, and X in this example: -

In the file sections each line either describes the next field, or is blank.

d. Editing the Input Definition within the Spreadsheet

DON’T! If you need to make changes, for example changing REGION to DECIMAL(5), then you should edit the Jazz definition of the input record, and then re-generate the conversion spreadsheet.

e. Editing the Output Definition

You may change the Output Field Name and Type, and for new fields you can also give Redefines. You can also change the Mapping and Sum values. The fields that you can change are highlighted in this example: -

You do not change the Level, Position, Length, or Occurs fields: these will be recalculated when you make other changes. If output field details are changed: -

· A new name must be valid by the normal Jazz rules.

· A new data type must be a valid Jazz type description, and must be compatible with the corresponding input field. Thus we could have changed the type of Region to SMALLINT or INTEGER, but not to CHAR.

· If REDEFINES is given it must name the preceding output field or group at the same level. Thus the only valid redefinition for District would have been Region. Also, if there is a corresponding input field, it must also be redefined

· You cannot change Occurs. If you must, exit the Conversion Spreadsheet process and edit the Jazz record definition. You may need to regenerate the spreadsheet, restarting from one of the steps above.

Mapping can be set to a value as described above.

f. Adding a Field

To add a field

1. Insert a line at the appropriate point: -

a. Click the row number at the left of the spreadsheet

b. Excel menu Insert/Rows. This creates an empty line.

2. Enter the field name and its type. Don’t copy/paste or Copy/Down the type of another field: if you do then you’ll find that the Type column remains stubbornly blank. This is because the type values that you see are actually Excel formulas, referring to values in the hidden columns to the right of the Remarks column. So here we’ve typed “decimal(3)”

3. There is no input field so we can’t use Mapping SAME. The mapping value must be either INIT or a value. We probably don’t want to SUM this field, but if we did we’d give RIGHT as the SUM value.

Note that we added a whole line, ensuring that the input and output fields are kept in synch, and vitally the displayed output columns are kept in synch with the hidden columns which we’ve probably forgotten about. While we may sometimes need to add cells, pushing either the input or output columns down, this must be done carefully and we will NEVER edit the hidden columns.

g. Omitting a Field

Giving mapping value “OMIT” means that there is no corresponding output field. For example, here we’ve decided to OMIT the two Sales figures: -

When this spreadsheet is imported Jazz will remove these fields from the Output definition, and recalculate the position of the following fields – for example BillingCycle will now be at position 20 because the two four-byte fields have been removed. Sum will become blank.

If you OMIT a group, for example RDKey, then all the fields of the group will be removed.

You may choose to write OMIT for all lines down to (and including) the closing END: this is equivalent to the example above: -

h. Restructuring a Field

To restructure a field from CHAR to a GROUP is a combination of editing the Type, and then adding more lines for the output elements. We can leave the original definition unchanged and add a redefinition: -

Alternatively we could change the output field’s type to GROUP and add the rest of the definition

This would move the 10 characters of FieldA to GroupA (extending it with a blank?), and then the first two characters are set to 'AB'. The extra byte would show up in the positions of FieldB and following fields.

We could outlaw this, using redefinitions when necessary to achieve the result above, like this: -

The reverse restructure, from GROUP to CHAR is similar: -

Again, Group to CHAR behaves like CHAR to CHAR, with the value being truncated (in this case) or extended with blanks.

i. Aligning Fields

We could end up with a situation like this: -

This spreadsheet can’t be fixed by inserting whole lines, because its basic problem is that fields from Name down are out of alignment. We need to insert cells in the Input section to push Name down 3 lines relative to the output section. To do this,

1. First we select three rows of the Input area, starting with Name: -

2. Now Excel menu : - Insert/Cells. Select option Shift Cells Down. The spreadsheet appears to be corrected, as now Name, SalesThisMonth, etc are all correctly aligned. But there is a problem, which we see when we click the checkbox “Blank when =” on and off: -

3. Lines 12 to 16 should have been blank in the output section, because the input and output values are the same. Why aren’t they? The reason is that the output values are not simple values, but are formulae like this one from cell I7,

=IF(OR(AND(CBX=TRUE, B7 = S7),S7=""),"",S7),

which displays value “IN1O”. These formulae are correct for rows 7 and 8, but from rows 9 down they are incorrect because they were adjusted by Excel when the cells A9:G13 were pushed down, becoming cells A12:G16. For example, the formula in I9 (display “ Region”) is now

=IF(OR(AND(CBX=TRUE, B12 = S9),S9=""),"",S9)

not =IF(OR(AND(CBX=TRUE, B9 = S9),S9=""),"",S9)

To correct this error we select the output area from (including) a correct line. In this example we’ve selected the whole output area, but if we’d made edits to some of the output fields we’d want to start our selection below these changes: -

We now use <ctrl>D to “Copy Down” the formula from the top row. Now the spreadsheet shows the correct result when the checkbox is checked: -

The converse situation is similar, with the added complication that you must also move the hidden columns to the right down at the same time. Here we need to move Name etc down in the output column

1. As in the previous case we start by selecting three rows of the Output area, starting with Name, but this time not keeping to the Output Area but extending to the right to include the general columns and the hidden area. Remember that the worksheets contain a copy of the original output records beyond the Remarks column, so columns R to X have been hidden: -

Here is the area we’ve selected. We’ve been careful to include column Y: -

2. Menu Insert/Cells/Shift Cells Down aligns the spreadsheet, but leaves the moved formulae incorrect so that the checkbox doesn’t work as it should: -

3. This is easily fixed, as before, by selecting the output area (NOT THE HIDDEN AREA) and <Ctrl>D to copy the formula from a correct line downwards.

7 Processing the Conversion Spreadsheet.

a. Processing the Spreadsheet

If we’ve edited the spreadsheet then, when we’ve finished, we open the Conversion Spreadsheet program and find the spreadsheet. If no edits were necessary, we can continue with this step straight after creating it. Either way we’re now seeing this form.

On clicking [Process Spreadsheet] Jazz reads the conversion spreadsheet and validates that it can find several named ranges indicating where various values can be found. If these checks are passed then it opens the worksheet and starts processing each line in it. If you wish to, Check [ ] Step Thru and Jazz will pause on every line: -

Click [No] and Jazz will run through the rest of the spreadsheet without pausing, unless there are issues such as new fields and invalid spreadsheet values which invoke dialogs where you can correct errors and set appropriate values. As the spreadsheet is processed Jazz expects to find each field in the definition in turn. If either the record definitions or the spreadsheet have been edited independently it is possible that the record and spreadsheet are out of step, in which case there will be various messages.

The input record definition cannot be changed from the spreadsheet in this step, but the output record can be. If you have made changes to the output record definition – for example you may have changed a field’s format, or added or removed fields – then you’ll see that Jazz processes the spreadsheet twice, first recording the changes that you make, and then recalculating the record layout so that Position and Length are correct.

b. Error-Checking Example

Here is an example spreadsheet from a test, with the highlighted line indicating that Jazz is about to process line #8. This spreadsheet contains a number of issues to illustrate and test various features: -

Notice the message

Don’t update or close spreadsheet until Jazz has finished with it

Jazz inserts this at the beginning of this process, and will remove it when it finishes. The spreadsheet has been opened normally, and you may be able to update it while Jazz is paused on a line, as here. DON’T!

The most common errors will be reported as Jazz processes each line of the spreadsheet, but some errors are not detected until the record layouts are analysed at the end of this process. Here are various errors as the spreadsheet above is processed.

Issue #1. Name change (line 8)

The group RDKey has been renamed (in the spreadsheet) to RDKeyRenamed. In a normal situation “RDKeyRenamed” would be the second line of the Jazz DEFINE statement, so Jazz checks the name of the next field in the DEFINE but finds that this is “RDKey”. Do we want to add a field before RDKey, or do we want to rename it? Jazz reacts by displaying this form: -

We click [Rename Next Field]. Jazz changes the definition from

DEFINE IN1O VB DATA(

RDKey GROUP,

Region DECIMAL(3),

District DECIMAL(3),

END GROUP,

Name CHAR(15),

SalesThisMonth DECIMAL(7,2),

SalesYTD DECIMAL(7,2),

BillingCycle LIKE Types.month,

DateCommenced CHAR(10))

DSNAME('IBMUSER.FILES.IN1');

DEFINE IN1O VB DATA(

RDKeyRenamed GROUP,

…

Issue #2: Type Change (Line 10)

Field District has been re-typed as SMALLINT. This change is accepted without comment, as SMALLINT is a valid Jazz data type.

Issue #3: New field (Line 11)

When Jazz encounters

on line 11 it reacts with the Output Issue dialog box again: -

This time we click [Insert Field]. Jazz inserts the field into the record definition.

DEFINE IN1O VB DATA(

RDKeyRenamed GROUP,

Region DECIMAL(3),

District SMALLINT,

Area DECIMAL(3),

…

Issue #4: Invalid Type Change (Line 13)

Line 13 contains: -

CHR(20) is not a valid Jazz data type, so Jazz responds with

We enter a valid type, “CHAR(20)” and click [OK], and Jazz tries with this. If we enter something that is not a valid Jazz definition then we’ll see this form again.

Issue #5. OMIT, but Output Field Present

With mapping OMIT there should be an input field, but no output field. If mapping value OMIT is given and both fields are present: -

you’ll see a form like this: -

If you click [Clear Output Field] then in the spreadsheet the output columns are cleared. The record layout will then be updated to remove the output field, so that there is no longer 4 bytes taken up by a DECIMAL(7) field at this position. The following SUM field will be cleared also.

If you want the field position to be retained but you don’t want the input value to be copied into it, change the mapping to FILL or a value and click [Correct]

Issue #6. Jazz Language Errors

Some errors are detected by Jazz language checking. For example, here we have attempted to add a partial redefinition of DateCommenced, but we got the name wrong in Redefines: -

As Jazz attempts to create a field called Year its checking discovers this error, and reacts with the kind of error message that you’d see in a Jazz program and right-clicked the error message to get its Help: -

On returning to the spreadsheet you’ll see that the line has been flagged as an error, with message “Insert Failed”

This error you’ll have to correct later: after you’ve closed this Import Conversion Spreadsheet process edit the spreadsheet with Excel to correct the Redefines value, and also edit the Jazz definition (Jazz workbench, as normal) to correct it.

c. End-of-spreadsheet Checking

On reaching the end of the spreadsheet Jazz recognises that changes have been made to the record definition, so the spreadsheet is re-processed to recalculate level, position, and length, and give names the correct indentation. The message

Don’t update or close spreadsheet until Jazz has finished with it

is removed, and the spreadsheet closed and released for further Excel editing or printing.

If any errors have been left in the spreadsheet – you may have clicked [Ignore for Now] or a Jazz Language Error may have been detected – then the message will be left as

Correct errors in Spreadsheet and/or Record definitions before generating the program.

The output definition is re-checked to ensure that it conforms with Jazz rules after the spreadsheet is closed and released. There may still be some errors. For example, when this test was run: -

no errors were reported as the spreadsheet was processed, but when the Jazz Workbench was displayed: -

You will need to correct this in the Jazz definition INIO.jzc, then import the spreadsheet again to re-coordinate the spreadsheet and record definition.

8 Generate Program

When the [Process Conversion Spreadsheet] process has completed with no residual errors you save the spreadsheet and Jazz closes it and changes the Conversion Spreadsheet form to display a [Generate Program] button and a few checkboxes: -

If you click [Generate Program] then Jazz will generate a conversion program for you based on the conversion spreadsheet. This shows the process: -

a. Option 1: Test Mode

If this is checked then the generated logic will convert and print one record, using the FEILDTABLE format. As FEILDTABLE is very bulky – at least one line for each field of each record – you wouldn’t want to use this format for the production version. It is useful however as an initial test that everything is working as you expect before you commit to a full-scale conversion.

b. Option 2: Select Records

If this is checked then Jazz will generate

WHERE input.? = ?

into the PROCESS statement. Jazz will then recognise the two “?” and a dialog will ask you to complete the condition.

c. Generating the program

Clicking [Generate Program] generates a Jazz program, which is then processed and displayed by the Jazz workbench. Here is the program generated from Exampl1, our initial example: -

PROGRAM Exampl1 BATCH;

COPY FILEV1;

COPY FILEV2;

DEFINE Copy-Stats DATA(

INPUT GROUP,

Count INTEGER,

END GROUP,

OUTPUT GROUP,

Count INTEGER,

END GROUP);

PROCESS FILEV1 COUNT Copy-Stats.INPUT.Count;

FILEV2.Region=FILEV1.RDKey.Region;

FILEV2.District=FILEV1.RDKey.District;

FILEV2.Name=FILEV1.Name;

FILEV2.SalesThisMonth=FILEV1.SalesThisMonth;

FILEV2.SalesYTD=FILEV1.SalesYTD;

FILEV2.DateCommenced=FILEV1.DateCommenced;

WRITE FILEV2 FROM(FILEV2.*);

#378 W Batch WRITE used - you may need to edit the JCL

Copy-Stats.OUTPUT.Count += 1;

END PROCESS FILEV1;

COPY JZSMth;

PRINT (Copy-Stats.*) FIELDTABLE;

The example above shows the general structure of a conversion program: -

1. Its logic is basically a PROCESS loop.

2. Every field pair with mapping option “SAME” creates an assignment.

3. As input is processed to output, the input and output records are counted and a very basic control report printed. This report could also include more control data if we’d used the SUM column of the spreadsheet.

The data printed by PRINT (Copy-Stats.*) FIELDTABLE; is: -

Printed at 10 Mar 2017, 21:05:37 Report1 Page 1

* Field Name * LENGTH VALUE

Copy-Stats.Copy-Stats.INPUT.Count : 14* 314

Copy-Stats.Copy-Stats.OUTPUT.Count: 14* 314

* * * END OF Report1 * * *

d. Editing your Jazz Program

The Jazz program can be edited just like a program that you write yourself. For example, while we could not use mapping “System Date” in the spreadsheet, we can write

Amglout.last-maint-date = $today;

We could write logic of any level of complexity, including defining ROUTINE logic that we PERFORM. It may be convenient to remember than in Jazz PERFORM and ROUTINE can have parameters, like a CALL to an external subprogram.

You may also want to edit the file definitions and logic. As a minimum a file type is needed for the Jazz to be valid (otherwise it won’t generate a COBOL program), and a data set name is needed if you will want Jazz to generate JCL to run your program. Other Jazz information such as validation rules, CODES, and so on are optional, but the more that is added the greater the productivity gains in ongoing development.

9 Further Development

This feature was developed by working with a customer in 2017 to meet the needs of a particular project. When this project finished (very successfully*), we went on with other development, leaving us with some ideas to make this facility even more useful. Here are some ideas. With customer support we’d be keen to make these further enhancements.

* The customer’s feedback: “In using Jazz, programs that take hours or days can be done in minutes, data mapping that takes a day or more to complete can be done in minutes”).

1. If the input record contains repeating groups, these could be written to separate files, normalising the data.

2. Write output as SQL tables. The customer’s project dealt only with PSAM and VSAM files, and at that time MANASYS Jazz did not support SQL. It would now be easy to enhance the data conversion feature to handle DB2 tables as input and/or output.

3. Other output formats, such as Comma-delimited data, tab-delimited data, XML and JSON.

4. Other input formats. As we support more database types, for example DL/1, a conversion spreadsheet should be able to read these records and write them out in some other format.

5. More mapping options. For example

a. = expression. The expression could be any expression that could be calculated and assigned to the output field.

b. R(name). This would invoke a function returning a value that could be assigned to the field, which would be written as a Routine in Jazz. This would require some Jazz programming, but it would offer very flexible calculation logic.

c. DPIC. Allow assignment from numbers, dates, etc to CHAR fields as if the field is being printed. DPIC, which stands for “Display Picture”, defines how a field is formatted for reports, screens, and web pages.

6. Interactive alignment dialogs. The algorithm that is used for name alignment worked well for the use case of the initial customer, but it doesn’t deal well with different field names and inserted fields. An interactive dialog option allowing users to manage alignment could be useful.

All these options are currently available by editing the Jazz program, but the suggested enhancements integrate them into the spreadsheet.