Data Conversion

Data Conversion. 1

1       Summary. 1

2       Producing a new Conversion Spreadsheet. 3

a.      Template. 4

b.      Creating the spreadsheet 4

c.      Find Input 4

d.      Defining the Output Record. 6

e.      Multiple Record Definitions. 8

f.       Case Logic. 9

g.      Adding Multiple Output Records. 12

h.      Set Mapping. 13

i.        Saving and Reviewing the Spreadsheet 15

3       Getting File Definitions from a Conversion Spreadsheet 15

a.      Starting the Record definition. 16

b.      Processing Following Fields. 17

c.      Finishing the Definition. 18

d.      The Output Record. 19

4       Edit the spreadsheet. 20

a.      Editing the Input Definition within the Spreadsheet 21

a.      Editing the Output Definition. 21

b.      Adding a Field. 21

c.      Omitting a Field. 22

d.      Restructuring a Field. 22

e.      Aligning Fields. 23

5       Processing the Conversion Spreadsheet. 25

a.      Summary. 25

b.      Processing the Conversion Spreadsheet 26

c.      Error Checking Example. 27

d.      End-of-spreadsheet Checking. 31

6       Generate Program.. 31

a.      Option 1: Test Mode. 32

b.      Option 2: Select Records. 32

c.      Generating the program.. 33

d.      Editing your Jazz Program.. 35

 

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 make the process easier to manage with Excel Spreadsheets.

1                   Summary

Companies converting files often use Excel spreadsheets to plan a conversion.  Jazz 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 record 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, and so can be covered very quickly here: -

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.

 

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.

2                   Producing a new Conversion Spreadsheet. 

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

 

The spreadsheet name will also be the name of the conversion program.  Click [New].  Jazz responds: -

 

Here we can choose the spreadsheet template, and the columns that we want in the spreadsheet.  By default everything is checked.  Click [Create] when we’ve chosen the columns that we want. Check [  ] Replace if the spreadsheet already exists and we want to replace it.

a.            Template

You can skip this step if your template is already selected, as AttrVConv is above.  A template is a basic spreadsheet defining the header area with several named cells to put particular information like the file names.  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 Jazz to COBOL conversion program, creating a Jazz definition that can be put into the spreadsheet.

·         [Find Jazz Definition] will locate an existing Jazz definition.

·         [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]

 

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

 

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 (IN1O.jzc), Jazz will simply write the output definitions into the spreadsheet from the top, as it did for the input: -

 

This is not terribly useful.   The output record definition is different to the input, with RDKEY changed from GROUP to CHAR(4), and DateCommenced changed from CHAR(10) to GROUP.  This has thrown all of the fields out of alignment, with Name in Row 11 for input, but Row 8 for output.  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.

 

If we leave Align with its default value of 10 then Jazz will look ahead 10 lines in the output and input definitions to attempt to find a match.  This is the spreadsheet that results: -

 

With other data you’ll see blanks inserted on the left to space out the input fields. 

 

Jazz won’t always get it right.  This is a simple name match: Jazz 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.

Blank when =

Once the input and output columns have been filled in the spreadsheet columns are resized, and with the addition of the output fields columns to the right of Remarks are now hidden, and the checkbox Blank when = now works.  Before we added the output our spreadsheet looked like this: -

 

With output added it looks as above, and when Blank when = is checked the output columns are displayed as blank unless they differ from the input columns: -

 

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

e.            Multiple Record Definitions

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 organization might simply contain “Type A”, “Type B”, and “Type C” records.  Jazz conversion spreadsheets can handle files like this with multiple records, using CASE logic to handle several formats. 

 

Here’s an example where I’ve started created a spreadsheet with definitions CaseI and CaseO, each of which has three record layouts.  When CaseO is selected as about Jazz responds with the Align Layouts dialog where we can confirm that the records 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.

 

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

 

f.              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.  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,

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 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.  With each of them we see this dialog: -

 

Another button, [$Case(?)], appears on the Conversion Spreadsheet form: -

 

g.            [$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.  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 compount 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.

h.            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.  Here we’ve edited it to: -

 

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,

·         $F(name).  

·         INIT  (or INITxxxx or FILL),

·         OMIT,

·         a value,

·         or

 

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.

$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

i.              Saving and Reviewing the Spreadsheet

When we added input, output, or mapping [Save Spreadsheet] appeared.  We could have saved the spreadsheet and exited Jazz, returning later with [Find] to resume.

 

In a very simple case we might click [Process Spreadsheet] now to check the spreadsheet and generate a program.  With default mapping there are not likely to be any errors that Jazz can check and so the process will go straight to program generation.  However you are more likely to pause at this stage to review the spreadsheet.  This is standard Excel processing, detached from Jazz.

3                   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)’”.

 

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 DSNAME option and validation and display properties.  You will probably just click [Exit] to close the workbench and save the record. 

 

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, this message is produced: -

 

This is simply an error in the Position column – If LAST-MAINT-OPER starts in position 122 and is 3 characters, then FILLER should have started in position 125, not 123.  This message can be ignored.

4                   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)

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.  For example, the Jazz definition of the input record is

a.                 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.

a.            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 exampe: -

 

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.

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.

b.            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 actual 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.

c.            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: -

d.            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.

e.            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: 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.

5                   Processing the Conversion Spreadsheet. 

a.                                                Summary

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. 

 

Click [Process Spreadsheet] and Jazz will check the spreadsheet for errors and, if no errors are found, it will create a conversion program.  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.

 

If the end of the spreadsheet is reached with no errors then you may choose to have Jazz generate a conversion program from the spreadsheet.

 

If there are a lot of errors then you can abandon the processing by clicking [Cancel].

b.                 Processing the Conversion Spreadsheet

The button [Process Spreadsheet] becomes active when the input and output file definitions have been found and attached to the spreadsheet.  This will be the case if you have [re-]created the definitions from [Get Input File Definition] and [Get Output File Definition], or if you have created the spreadsheet from definitions as described in 4. Produce Conversion Spreadsheet.

 

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.

 

As with the earlier steps defining the input and output record you may choose to have [P] Step checked, in which case even if there are no issues requiring a response you’ll see a MessageBox like this as every spreadsheet line is processed: -

 

If you click [No] then the checkbox is unchecked, and the process will run to its end or until it requires a response.  If you click [Yes] you’ll get this MessageBox again with the next line.

 

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.

 

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 have checked Step Thru then you can see this process.  If not, then you may not see anything until either the process finishes (this may take a few minutes) or there’s an issue requiring a response from you.

c.                                                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 2)

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');

to

DEFINE IN1O VB DATA(

    RDKeyRenamed GROUP,

Issue #2: Type Change (Line 4)

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 5)

When Jazz encounters

on line 5 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 7)

Line 7 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.  Thus 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.

d.                                                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.

 

After the spreadsheet is closed and released the output definition is re-checked to ensure that it conforms with Jazz rules: 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.

6                   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 this 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 generated

          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 we’ve chosen option [P] Test Mode.

 

There may be a number of errors reported.  For example: -

PROGRAM Melb20161007 BATCH;

#158 S 'Melb20161007' is not valid as an external name

* You may need to edit these statements

COPY AMGLIN;          [This is from an earlier test, where the record names were

COPY AMGLOUT;      [not AMGL/AMGLO

DEFINE Copy-Stats DATA(

    INPUT GROUP,

        Count INTEGER,

        END GROUP,

    OUTPUT GROUP,

        Count INTEGER,

        END GROUP);

PROCESS AMGLIN UNTIL(Copy-Stats.Input.Count > 0) INDEX Copy-Stats.Input.Count;

    #346 E DSNAME option missing

    #199 W Reference to a field in the current file expected

    AMGLOUT=AMGLIN;

    #468 E AMGLOUT = AMGLIN assigned as if CHAR and extended with blanks

    AMGLOUT.KEY=AMGLIN.KEY;

    #468 W KEY = KEY assigned as if CHAR

    AMGLOUT.ORG-LOGO=AMGLIN.ORG-LOGO;

    #468 W ORG-LOGO = ORG-LOGO assigned as if CHAR

    AMGLOUT.ORG-X=AMGLIN.ORG-X;

    #468 W ORG-X = ORG-X assigned as if CHAR

    AMGLOUT.ORG=AMGLIN.ORG;

    AMGLOUT.LOGO-X=AMGLIN.LOGO-X;

    #468 W LOGO-X = LOGO-X assigned as if CHAR

    AMGLOUT.LOGO=AMGLIN.LOGO;

    AMGLOUT.STORE-X=AMGLIN.STORE-X;

    #468 W STORE-X = STORE-X assigned as if CHAR

    AMGLOUT.STORE=AMGLIN.STORE;

    AMGLOUT.PLAN-X=AMGLIN.PLAN-X;

    #468 W PLAN-X = PLAN-X assigned as if CHAR

    AMGLOUT.PLAN=AMGLIN.PLAN;

    AMGLOUT.TXN-CODE-X=AMGLIN.TXN-CODE-X;

    #468 W TXN-CODE-X = TXN-CODE-X assigned as if CHAR

    AMGLOUT.TXN-CODE=AMGLIN.TXN-CODE;

    AMGLOUT.ADD-STATUS=AMGLIN.ADD-STATUS;

    AMGLOUT.D-DB-ACCT=AMGLIN.D-DB-ACCT;

    AMGLOUT.D-CR-ACCT=AMGLIN.D-CR-ACCT;

    AMGLOUT.D-DB-ACCT-PL=AMGLIN.D-DB-ACCT-PL;

    AMGLOUT.D-CR-ACCT-PL=AMGLIN.D-CR-ACCT-PL;

    AMGLOUT.D-REPORT-DESC=AMGLIN.D-REPORT-DESC;

    AMGLOUT.STATUS=0;

    AMGLOUT.LAST-MAINT-OPER=SPACES;

    Copy-Stats.Output.Count += 1;

    PRINT (AMGLOUT.*) FIELDTABLE;

    WRITE AMGLOUT;

    #346 E DSNAME option missing

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

END PROCESS AMGLIN;

PRINT (Copy-Stats.*) FIELDTABLE;

 

Message #158

#158 S 'Melb20161007' is not valid as an external name

is because the name is longer than 8 characters.  This name is taken from the name in the spreadsheet, which by default is the name of the .xls file.  We can correct it here.  The error would have been prevented if we’d changed the name within the spreadsheet  (Cell I2): -

 

With the name changed to M171007 the COBOL program will compile, although we won’t be able to run it automatically unless we provide DSNAME options for the input and output files, as warned by message #346: -

    #346 E DSNAME option missing

 

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

 

1.             Logic is basically a PROCESS loop.  Because we’ve checked [P] Test mode, this include UNTIL to terminate the loop after the first record.

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

    AMGLOUT=AMGLIN;

    #468 E AMGLOUT = AMGLIN assigned as if CHAR and extended with blanks

    AMGLOUT.KEY=AMGLIN.KEY;

    #468 W KEY = KEY assigned as if CHAR

    AMGLOUT.ORG-LOGO=AMGLIN.ORG-LOGO;

    #468 W ORG-LOGO = ORG-LOGO assigned as if CHAR

    AMGLOUT.ORG-X=AMGLIN.ORG-X;

    #468 W ORG-X = ORG-X assigned as if CHAR

    AMGLOUT.ORG=AMGLIN.ORG;

    AMGLOUT.LOGO-X=AMGLIN.LOGO-X;

    #468 W LOGO-X = LOGO-X assigned as if CHAR

Every GROUP => GROUP assignment produces message #468 because Jazz will assign the data with CHAR => CHAR rules, as does COBOL.  Actually it’s recommended that SAME mapping is omitted from groups, as the data will be assigned with correct formatting by the following field assignments anyway.

 

Field to field assignments may reformat the fields, for example if the input field has one numeric format and the output field has another.  If a field has dimensions, either explicitly or because it’s within a dimensioned group, then a loop will be generated to assign each element.  Field dimensions must be identical in both number-of-dimensions and extent.

 

3.             Fields that are named FILLER or are only defined in the output file can not use SAME, but must using mapping blank, FILL, or a value: -

3

  STATUS

PIC '9(1)'

117

1

 

 

0

3

  LAST-MAINT-DATE

DECIMAL(7)

118

4

 

 

 

3

  LAST-MAINT-OPER

CHAR(3)

122

3

 

 

FILL

3

  FILLER

CHAR(54)

123

54

 

 

FILL

Status is given value zero (we could have used FILL here for the same result), and LAST-MAINT-OPER and FILLER are set to blanks.

 

4.             LAST-MAINT-DATE had mapping “System Date”, but this was regarded by Jazz as a literal character value and rejected with an error message.

5.             Because this spreadsheet didn’t use any SUM options the only record statistics recorded are the input and output record counts, which will be equal.  SUM can be Left, Right, or Both: depending on this setting a value from the input, output, or both records will be accumulated and reported in the statistics printed by

        PRINT (Copy-Stats.*) FIELDTABLE;

at the end.  Here is a sample report from this statement in a test program. Without any SUM options Jazz had created Copy-Stats as

DEFINE Copy-Stats DATA(

    INPUT GROUP,

        Count INTEGER,

        END GROUP,

    OUTPUT GROUP,

        Count INTEGER,

        Count-1-IN1O INTEGER,

        END GROUP);

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

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

0*           Field Name           *  LENGTH  VALUE       

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

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

 Copy-Stats.Count-1-IN1O           :     14*           314

 * * * END OF Report1 * * *                               

 !! END OF JES SPOOL FILE !!

d.           Editing your Jazz Program

The Jazz program can be edited just like a program that you wrote 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.