Define
Data from Spreadsheet
1b. Locate the Spreadsheet Data
2. Starting from Import Conversion
Spreadsheet
4. Continue the Jazz Definition: Fields
and Groups
4d. Spreadsheet Codes and Jazz Types –
more detail
4e. Some situations where Jazz gets it
wrong
This form
is invoked
·
From
the menu New/Data/Define Data from Spreadsheet
·
From
the menu New/Data Conversion/Define Data from Spreadsheet
·
From
[Get Input File Definition] or [Get Output File Definition] from the form
Import Conversion Spreadsheet.
Its
function is to read an Excel spreadsheet and generate a Jazz data definition
from it. The definition should be
reviewed and probably edited before use.
1.
Starting
from the Menus.
1a. Find the spreadsheet
When first
seen the form looks like this: -
Click
[Find] for a Windows Explorer dialog to locate and open the spreadsheet. When a spreadsheet is selected more controls
appear on the form: -
and the
selected spreadsheet is displayed: -
By the way,
the spreadsheet might be displayed underneath other windows. Click the Excel icon in the task bar to bring
it to the front, and arrange your screen so that you can see both it and the
Jazz form. If your PC has an extension
screen it is especially convenient to have the spreadsheet open in one screen
and Jazz in the other.
1b. Locate the Spreadsheet Data
If there is
a row containing column headings, like row 2 in the example above then Jazz can
probably figure out where to find everything it needs, so the first thing to do
is to tell Jazz where to find this column.
If there is no suitable row in your spreadsheet then you’ll need to fill
in all the locations yourself. Enter
First Data Row as a number, and the other values as single letters, A to Z.
If a
Heading Row number is given, then Jazz calculates the locations like this: -
·
First
Data Row. Heading Row + 1
·
Field
Name. This is the first column in
the Heading Row that contains “name”.
This is column A. Note that
“name” could be “Name”, “NAME”, or any other variation: the test is not
case-sensitive. Also the test just looks
for “name”, this might be part of a longer value (“Field Name” in this
example), or even part of a word – perhaps “COBOLNAMEVALUE”.
·
Level. There might be a separate column containing
the level numbers, or they may prefix the name column, as here. Jazz tests to see if the first data value in
the Name column is preceded by a number: if so then the Level column is the
same as the field name. Otherwise Jazz
looks for a column containing “level”.
·
Occurs, Redefines,
Type, Length, and Position are all similarly found by searching the heading
line for a title containing the relevant word.
In the case of Occurs and Redefines only the length underlined is
needed.
Values for
Field Name, Level, Type, and Length are required. Occurs, Redefines, and Position are optional.
In this
example we simply enter 2 in Heading Row, and Jazz responds: -
Check the
column assignments. If the columns don’t
follow the rules above Jazz won’t have sorted it our correctly.
Before
going on with [Create Definition], you may like to set these checkboxes: -
[ ]
Replace Jazz Definition. Check
this if your spreadsheet is going to define “MyJazzRecord.jzc”, and this
already exists. If you don’t check this and, at the end of the process Jazz
finds that MyJazzRecord.jzc does exist then you will be prompted about whether
you want to overwrite it.
[ ]
Define 2 records. Click this if
the spreadsheet contains two sets of columns and you want to generate both.
[ ]
Replace 2nd Jazz Definition. This
functions like “Replace Jazz Definition”.
[ ] Show
Jazz on SprdSht. Check this if you want
the spreadsheet to be updated to put the definition into Jazz format, including
adding extra lines for END GROUP statements.
Processing continues with Step 3.
2. Starting from Import Conversion
Spreadsheet
We start
this process from Import Conversion Spreadsheet. Suppose that we import a spreadsheet like
this that contains the record layouts in a COBOL-like notation, and we don’t
yet have Jazz definitions of these records: -
Because the
Jazz definitions don’t exist [Process Spreadsheet] is disabled and we can’t
continue to generate a conversion program: -
We need to
attach Jazz file definitions to the spreadsheet, which we do by clicking the
[Get Input File Definition] and [Get Output File Definition]. When we do this we are offered options: -
and we
click [Create Data from Spreadsheet] to read the definitions within the
imported spreadsheet. We are starting
with a spreadsheet that has been formatted with an appropriate template, and so
Jazz already knows where to find the heading and data rows, and which columns
to use.
Before
continuing with [Create Definition], you may like to set these checkboxes: -
[ ] Replace
Jazz Definition. Check this if your
spreadsheet is going to define “MyJazzRecord.jzc”, and this already exists. If
you don’t check this and, at the end of the process Jazz finds that
MyJazzRecord.jzc does exist then you will be prompted about whether you want to
overwrite it.
[ ] Show
Jazz on SprdSht. Uncheck this if you
don’t want the spreadsheet to be updated to put the definition into Jazz
format. Here the default is checked,
unlike the default when Define from Spreadsheet is invoked form the general
Jazz menu.
3. Start [Create Definition]
Until
you’re familiar with this process leave the [P] Step check box checked.
Click
[Create Definition]. Jazz responds by
highlighting the first data line: -
The first line
must be an 01 level, and it will become the name of the file.
The
spreadsheet, like a COBOL record layout, doesn’t include file type information
so Jazz asks us what file type to use.
Default is VB, possible values are V, VB, F, FB, U, VSAM, XIO, and
WORK. Values are not sensitive to case:
for example VB, vb, Vb, and vB are all equivalent: -
If click
[OK] with this value, or any other valid value except XIO and WORK, then the
file name must be valid according to IBM’s rules. AMBS-R2 is not, so next we see: -
Jazz has
suggested a file name, but if this is not the name we want we can change it. Click
[Return] and Jazz returns to the Define Data form with he DEFINE statement
displayed: -
4. Continue the Jazz Definition: Fields and
Groups
The first
line had dealt with the level-1 definition, and this has become the file name
and type. Subsequent lines deal with
fields and groups within this file. With
[P] Step checked, every time we click [Next] the
highlighting will move down one line, and the next Jazz line will be displayed
before it’s written into the definition.
4a. Preamble
Before
continuing with fields and groups have a look at the checkboxes towards the
bottom of the form: -
Length. Suppose the spreadsheet contains a repeating
field, say
Is the
length of 5 the total length of all 5 occurrences, or the length of each
occurrence of FILLER? If we check
[ ] Table then Jazz used the first rule,
generating a definition FILLER (5) CHAR(1).
If we check [ ] Field then Jazz
uses the second rule, generating FILLER(5) CHAR(5).
[ ]
Step. Uncheck this when you get
tired of stepping through every line, and Jazz will then loop through each line
without stopping unless there is something that needs your input.
The other
checkboxes – Prompt for Type, Prompt on Redefines, Prompt on Index, and Prompt
Occurs not Nbr – cover various situations where the spreadsheet doesn’t follow
the strict rules that Jazz would like and so Jazz makes some guesses. Leave them checked until you become familiar
with this process, but like Step you can uncheck them when the dialogs become
irritating.
4b. The
First Field
Line 4
contains
On clicking
[Next] Jazz displays: -
Here Jazz
has encountered type “9” for the first time.
It makes a guess that this is equivalent to the Jazz type “PIC ‘9(1)’”
(in this case the field length is 1), meaning a single numeric character. See below
for more information on the way that Jazz interprets the Type column. If Jazz has made a mistake then we can give a
different value for Jazz Type. We won’t
see this message for other lines with type “9”, but unless we uncheck [P] Show message again we’ll see this when we
encounter another type value.
On clicking
[Close] we see: -
Note
1.
The
name is “NOM-ACH-AMT-PCT-FLAG”, not “AMBS-NOM-ACH-AMT-PCT-FLAG”. Jazz remembers the prefix from the first
line, and removes this from every following line, except in cases like
“AMBS-1ST” where removing it would create an invalid name. “1ST” is not a valid Jazz or COBOL field name
because it starts with a number.
4c. Jazz
Formats
Continuing
with [P] Step checked we click [Next]. On line #5 we encounter a new type code: -
Jazz
responds: -
When we
encounter a new Type code Jazz tries to guess the equivalent format, and it
displays this form. If it guesses
incorrectly we can enter the correct result.
In this case the Jazz format “DECIMAL(17)” is correct: this format means
that the value is a packed number stored as 2 digits per byte with a sign in
the last half byte.
As we
continue down the spreadsheet, clicking [Next] for each spreadsheet row, Jazz
will respond with the “JazzTypeFromXLS” form whenever it encounters a new
code. Here are some more examples: -
In both
cases Jazz has made the correct guess, and we don’t need correct it. You should check that the code is correct:
see below for some situations where Jazz gets it wrong/
4d. Spreadsheet
Codes and Jazz Types – more detail
You can use
any code you like, and assign whatever Jazz Type you like to that code, but you
may find it easier if you use codes that Jazz is likely to guess
correctly. Codes are usually COBOL
pictures, but they can be any unique string.
Jazz will trim blanks from the beginning and end of the spreadsheet
value, but not from within the value, so “S999
COMP-3” with three blanks between “S999” and “COMP-3” is different to
“S999 COMP-3” with one blank. Simply
“COMP-3” is also perfectly OK to Jazz.
You may end up with several codes meaning “DECIMAL(3). The values do not have to be valid COBOL.
Jazz logic
for guessing a code is: -
1.
Case
doesn’t matter: for example “IF the code starts with ‘GR’” could mean “GR”,
“gr”, “Gr”, or “gR”.
2.
IF
the code starts with “GR” THEN value = GROUP.
This means that the name starts a group item
3.
(ELSEIF)
the code is “9” THEN value = PIC ‘9(@Lth)’.
This means that the field is a number in character format.
4.
(ELSEIF)
the code is “X” THEN value = CHAR(@Lth).
5.
(ELSEIF)
the code contains “COMP-3” THEN
a. Value = “DECIMAL(N1[,N2])
b. N1 is calculated from the field
length, as N1 = Length * 2 – 1. Note
that Jazz uses the field length, not the COBOL picture.
c. If the code contains “V” then “,N2”
is added to Value. N2 is calculated as
the number of characters from “V” to the next blank. For example, if the code is “S9(5)V99 COMP-3”
and the field has length 4 bytes then Jazz will use format “DECIMAL(7,2)”
6.
(ELSEIF)
the code contains “COMP-1” THEN
a. IF length = 4 THEN Value = FLOAT
b. ELSE Value = FLOAT LENGTH INVALID
7.
(ELSEIF)
the code contains “COMP-2” THEN
a. IF length = 8 THEN Value = LONG
b. ELSE Value = LONG LENGTH INVALID
8.
(ELSEIF)
the code contains “COMP” then
a. IF the Length = 2 THEN value =
SMALLINT
b. ELSEIF length = 4 THEN Value =
INTEGER
c. ELSEIF length = 8 THEN Value =
BIGINT
d. ELSE value = BINARY LENGTH INVALID
If an
earlier line has used JazzTypeFromXLS to create a code/format equivalence and
then another line uses the same code, but has a different length, then Jazz
will add “LENGTH INVALID” to the format.
For example,
Jazz
guessed resulting in
VI-SPENDING-CATEGORY SMALLINT,
The same
code was encountered on line #36, but with a different length
Jazz added
“INVALID LENGTH”, ensuring that this error is reported when the definition is checked
in the Jazz workbench.
MC-CAB-PT-ENTRY SMALLINT
INVALID LENGTH,
4e. Some
situations where Jazz gets it wrong
1.
Length
entered was the whole array length when you meant individual field length, or
vice versa. The Jazz form includes: -
This tells
Jazz how to interpret the length.
With Field
interpretation Jazz thinks that a field of length 24 occurs 12 times, resulting
in: -
In fact we
should have set the Table option. While
we can correct this to
DECIMAL(3,1)
in other
situations we may have fields calculated as CHAR(@Lth) and there’ll be no
message. If you get something like this
and you’re stepping through the spreadsheet you can change the Table/Field
option and continue – but don’t forget to check the Jazz definition before this
point. If you’ve unchecked [ ] Step then stop, and restart with correct
options.
2.
Jazz
may not be able to guess the format: -
This is numeric-character data, which in
Jazz is format PIC. Enter the format as
PIC 'S9(11)V9999', or if you prefer PIC 'S9(11)V9(4)'. Note that Jazz requires quotes around the
picture value.
4f. Other
Issues
If various
situations are encountered, then Jazz will handle the issues. For some issue types the fix suggestion can
be suppressed: like recognition of Excel Type values you can uncheck these when
you have developed some experience with this tool and confidence that it is
making the right decisions.
Line #6 is
contains REDEFINES in the field name, and in Occurrences where Jazz is
expecting a number it has the word “REDEFINES”.
When Jazz
recognizes that the “Name” contains a blank then after sorting out that the
name is “NOM-ACH-AMT it attempts to sort it out what follows by looking for
“REDEFINES” and “OCCURS”. In this case it
finds “REDEFINES” and saves this as part of the field definition. It would have put the redefinition object,
NOM-ACH-PCT-AMT into the Redefines column had there been such a column.
Next Jazz
discovers that the Occurs column doesn’t simply contain a number. It responds with
Jazz
applies this rule: -
·
If
the text starts with “Redef” then it assumes is should be blank.
·
If
the text contains “OCCURS nn TIMES” then it removes “Occurs” and “Times” to
leave just the number.
Click [OK]
and Jazz will remove it and continue.
Here’s
another example. In this row the Occurs
column is not a number: -
Jazz
responds with
As before,
click [OK] to continue with the suggested value
Another
situation where Jazz can recover correctly: in this row
spreadsheet
the “Name” contains an INDEXED clause, which is COBOL, not Jazz, and would
result in an error: -
Jazz
responds with
Especially
with a big spreadsheet replying [OK] to situations that Jazz has already corrected
will become tedious, so you’ll probably uncheck
Even when
these are all unchecked Jazz will continue to report other situations. For example, here it can’t remove the name
prefix from the Redefines object because, without a blank to separate
“REDEFINES” and the next word it can’t tell where the redefinition name
starts. In this case we edit the line
ourselves before clicking [OK].
Sometimes
there may be no problem.
The
spreadsheet row contained: -
and Jazz
has replaced the “.” with a blank. This
is correct, but Jazz cannot be sure, so it displays the amended field name for
you to verify.
Jazz is
also checking for unexpected level changes.
On Line #8 it encounters: -
If this has
level value 5, then the preceding line should have defined a GROUP. Jazz responds
and on
clicking [Return] the spreadsheet is corrected: -
On Line #15
Jazz encounters the reverse error: -
Line #14 is
a group, so line #15 should have a higher level number. Jazz responds with
Although
the spreadsheet is not changed in this case, CHGOFF-RSN1 is treated as if the
level number were 5.
4g Converting to Jazz Layout
If the
checkbox [ ] Show Jazz on SprdSht is
checked then as the spreadsheet is checked each COBOL-like line is replaced
with a properly formatted Jazz definition.
This checkbox defaults to checked when the Define Data from Spreadsheet
function is invoked from Import Conversion Spreadsheet, as it works best when
there are all the fields that are standard in conversion spreadsheets.
The
checkbox can only be changed before the Level 1 line is processed, as the whole
spreadsheet should be processed in the same way. Here are the results of processing
spreadsheet VConv2a2.xls with this option: -
In the
absence of explicit Level and Redefines columns this spreadsheet doesn’t show
this information any more, although the information is present in the Jazz
definition. Extra lines have been
inserted (10, 21, 21) for END GROUP, which is Jazz’s way of defining
level. Also, you can infer the level
numbers from the name indentation. Names
are now shown without prefix.
5. Review the Jazz Definition
On reaching
the end of the spreadsheet it is closed, and Jazz opens a workbench session
where the new definition is displayed and checked: -
Scroll down
to check for messages: there may be spreadsheet errors that Jazz hasn’t
repaired. Here are some examples from
various test spreadsheets: -
1.
Spreadsheet
line #278 contained “REDEFINESAMBS-NOM-ACH-PCT-AMT”.
This would
have been reported but we must have clicked [OK] without really reading the
message, resulting in
NOM-ACH-PCT-AMT DECIMAL(17),
NOM-ACH-AMT REDEFINESAMBS-NOM-ACH-PCT-AMT DECIMAL(17),
#002 W
"REDEFINESAMBS-NOM-ACH-PCT-AMT" is longer than 25 characters
#021 E "REDEFINESAMBS-NOM-ACH-PCT-AMT"
not recognized as a keyword
#029 E 'REDEFINESAMBS-NOM-ACH-PCT-AMT'
is invalid here
We can correct it to here to
NOM-ACH-AMT REDEFINES NOM-ACH-PCT-AMT DECIMAL(17),
and on
clicking [Check] this will become
NOM-ACH-AMT
REDEFINES
AMBS-RECORD.NOM-ACH-PCT-AMT DECIMAL(17),
2.
Line
#279 had the same error. But there’s
another error also: line #280 has a lower level number, so line #279 should
have been a group, not another DECIMAL(17) field
Jazz has
produced: -
NOM-ACH-PCT-R
REDEFINESAMBS-NOM-ACH-PCT-AMT DECIMAL(17),
#002 W
"REDEFINESAMBS-NOM-ACH-PCT-AMT" is longer than 25 characters
#021 E
"REDEFINESAMBS-NOM-ACH-PCT-AMT" not recognized as a keyword
#029 E 'REDEFINESAMBS-NOM-ACH-PCT-AMT'
is invalid here
FILLER CHAR(5),
NOM-ACH-PCT DECIMAL(7),
END
GROUP,
#205 E Negative indentation: excessive END statements
or similar errors
We change
line 279 to
NOM-ACH-PCT-R
GROUP REDEFINES NOM-ACH-PCT-AMT,
which, on
clicking [Check], becomes
NOM-ACH-PCT-R GROUP REDEFINES AMBS-RECORD.NOM-ACH-PCT-AMT,
FILLER CHAR(5),
NOM-ACH-PCT DECIMAL(7),
END
GROUP,
correcting
all these errors.
3.
A
spreadsheet contained several fields called “DUMMY”. Except for the first of these, Jazz reports
the duplication error: -
DUMMY PIC '9(1)',
#203 S Invalid: Field name DUMMY is duplicated
DUMMY DECIMAL(7),
#203 S Invalid: Field name DUMMY is duplicated
INS-SUSPENDED-PREM DECIMAL(17),
INS-INTLZED-FLAG PIC
'9(1)',
INS-INTLZED-PROD
PIC '9(1)',
INS-RATE-METHOD CHAR(2),
DUMMY CHAR(15),
#203 S Invalid: Field name DUMMY is duplicated
DUMMY CHAR(1),
#203 S Invalid: Field name DUMMY is duplicated
These
should probably be renamed to “FILLER”.
However [Replace all] may lead to further errors: in at least one case
from this spreadsheet one of these DUMMY items is redefined, and a reference to
a FILLER field would be an error.
4.
At
the end of the spreadsheet further errors may be reported: -
USER-FILLER
CHAR(2),);
#339 S EX-DATA1 REDEFINES AMBS-RECORD.FI-RESERVED-DATA is
invalid: base object is too short
#339 S EX-EVENT-CODES-R REDEFINES
AMBS-RECORD.EX-EVENT-CODES is invalid: base object is too short
#491 E Too few END GROUP items
These
should be corrected. The incorrect comma
after CHAR(2), and message #491, are a
consequence of the #2 error where a GROUP definition was missing.
5.
Depending
on the file type we may need to add some more information: -
·
If
this is a type WORK definition then we won’t be able to read and write to
it.
·
If
it has type XIO then we will need to add definitions of the I/O routines before
we can invoke these routines. Until this
is done we won’t be able to read and write to it
·
If
we want Jazz to generate JCL accessing this file then as well as the file type
we need to add a DSNAME option.
We can make these changes now or later.
Once we
have corrected any errors in the Jazz definition we should validate the file
layout with the option New/Data Conversion/Validate Definition. This will generate a program that will read
one record, print it in FIELDTABLE format, and
validate the position of DECIMAL fields by
attempting to add zero to them (forcing a data check if they are in the wrong
position).