The Convert Data Base File Format command allows a more flexible
function than the system CPYF command when converting fields from one
format to another.
CVTDBFFMT is designed for the situation where:
** A field must be converted from character to decimal or vice
versa. CPYF only maps from character to character or from
decimal to decimal.
** A date field format needs to be changed. For example, you
want to convert from an MMDDYY format to CYYMMDD or YYYYMMDD.
** A field has been renamed. The data should be moved to the new
field.
A typical command to convert the field DATE to a new format would be:
CVTDBFFMT FROMFILE(ABC) TOFILE(DEF) MBROPT(*REPLACE) +
LIST((DATE *FROMFIELD *DATCVT *MDY6 *CYMD7 +
*NO *NO))
The To file must have been created with the DATE field declared as 7
bytes (or digits). The CVTDBFFMT command would map all of the fields
that have the same definition in the FROMFILE to the TOFILE and use
the LIST parameter to determine what conversions to make. In this
case, the DATE field is in *MDY format as a 6 byte (or digit) field
and is to be converted to a *CYMD format in a 7 byte (or digit
field).
DATE could have been a character field in the FROMFILE and a decimal
field in the TOFILE. CVTDBFFMT can map between most common data
types.
Note that the LIST parameter is used for exceptions. Only those
fields where the definition differs between the two files or a field
requiring date conversion must be specified.
Options exist on the command to allow the handling of invalid data
when converting from character to decimal or in invalid data or dates
during date conversions.
The From file is always processed in arrival sequence. A logical
file may be specified for either the From or To file, but may only
have a single format.
Sample use of a date conversion
-------------------------------
Assume that you have a data base file with a date field in an *MDY
format and want to convert to a *CYMD format. The following steps
would be used.
** A function such as the TAA Tool DUPDBN would be used to
duplicate the physical and logical files in the data base file
network to a work library.
** The DDS would be changed.
** A function like the TAA Tool RPLPF would be used to re-create
the data base network with the new definition.
** The CVTDBFFMT command would be used (such as shown previously)
to reformat a sample of the production data to test with.
CVTDBFFMT supports the capability to copy beginning at a
specific relative record number and you can specify the number
of records to copy.
** A list of the programs that use the data base network would be
determined. A tool such as the TAA Tool DSPWHRUSE could be
used.
** The programs (and any associated display or printer files)
would be changed and re-created in the work library.
** Final testing would occur.
To cutover to the new objects:
** CVTDBFFMT would be used to reformat all of the physical data
to the work library.
** The TAA Tool MOVLIBOBJ would be used to move the objects from
the work library to the production library. The
DLTEXIST(*YES) option would be used to delete any
corresponding objects.
Date handling
-------------
When a date is to be converted, the LIST parameter describes both the
From and To field names as well as the format of both fields.
The * values used to describe the date formats all describe a length.
For example, *MDY6 means the value is in the format of a 6 byte (or 6
digit) field with no separation characters. The length assists in
defining the command and understanding the error messages. For
example, if you have an 8 byte field and specify *MDY6, an error
message will appear stating that the type and field length are not
compatible.
Date fields may be declared as character, decimal, or the DDS defined
date fields (Type = L). If the field is decimal, it may be kept in
either packed, signed, or binary format.
When *DATCVT is used, some checking occurs to ensure that the data is
valid. For example, the month value must be 1-12, September cannot
have a day of 31, etc.
The DATCVTERR option provides a method of forcing all invalid dates
to several different date forms. Not all of the special values are
valid for date type fields (L type) and some special values may not
provide meaningful results for the *CYMD or 2 digit year formats.
See the discussion of the parameter values.
If a date is converted from a format of 6 (such as *MDY6), the
century information is assumed based on:
Year 40 to 99 = 20th century (1940 -19999)
Year 00 to 39 = 21st century (2000 - 2039)
The *CYMD format century is generated by adding 1 to the 2nd digit of
the 4 digit year. Therefore, the format is only useful when dealing
with dates from 1900 to 2899.
The following shows the various supported date types and the values
for July 31, 2009. The third column indicates whether the date
separator character is used if the field is a 'To field'. The date
separator is used for the 'From field' (a '/' is shown in the
samples).
Date type Sample Uses DATSEP
*MDY6 063109
*DMY6 310709
*YMD6 090721
*MDY8 07/31/09 Yes
*DMY8 31/07/09 Yes
*YMD8 09/07/31 Yes
*Y2 09
*YM4 0907
*YY4 2009
*YYM6 200907
*JUL5 09213
*JUL6 09/213 Yes
*LONGJUL 2009213
*CYMD7 1090731
*CYMD9 109/07/31 Yes
*MDYY8 07312009
*DMYY8 31072009
*YYMD8 20090731
*MDYY10 07/31/2009 Yes
*DMYY10 31/07/2009
*YYMD10 2009/07/31 Yes
*ISO10 2009-07-31
*USA10 07/31/2009
*EUR10 31.07-2009
*JIS10 2009-07-31
Command parameters *CMD
------------------
FROMFILE The qualified file name of the file to copy from.
The library value defaults to *LIBL. *CURLIB may be
specified.
TOFILE The qualified file name of the file to copy to. The
library value defaults to *LIBL. *CURLIB may be
specified. The file must exist.
MBROPT Whether to replace or add records in the To file.
This is a required parameter. *REPLACE or *ADD must
be specified.
FROMMBR The member to be copied from. The default is
*FIRST.
TOMBR The member to be copied to. The default is *FIRST.
The member must exist.
FROMRCD The record to begin copying from. Arrival sequence
is always used. The default is 1. This parameter
can be helpful when creating test data or for
recovery. See the section on Recovery.
CPYNBR The number of records to copy. The default is *ALL
meaning all records in the From file. This
parameter can be helpful in creating test data.
LIST The LIST parameter allows you to name up to 40
fields to be converted. If the fields do not have
the same definition in both the From file and To
file, you must use a LIST entry to describe how to
convert the field. If a field is being added or
dropped, you must also describe an entry. If a date
field format is to be converted to the same length
and type field, a LIST entry is also required.
Note that you do not need a LIST entry for every
field in the file. Only the exceptions must be
specified.
FROMFIELD The name of the field in the From file. The
special value *NONE must be used if a field is
being added to the To file and does not exist
in the From file. It is valid to use the same
From field for multiple entries.
TOFIELD The name of the field in the To file. The
default is *FROMFIELD meaning the same name as
the From field. A field name may only be used
once as a To field.
OPTION The option that describes how to reformat the
To field. The following are valid:
*MAP. Use *MAP when the field names are the
same, but not the field definitions. Some
conversion is needed, and it is not one of the
special date conversions. If a decimal To
field is specified, no decimal alignment
occurs. You can map from a smaller to a larger
field, from character to decimal, or decimal to
character. To map from a larger to a smaller
field requires a special option. See the
option *TRUNCATE.
*DROP. Use *DROP when a field in the From file
does not exist in the To file.
If a field is to be dropped (does not exist in
the To file), declare the From field, use the
default (*FROMFIELD) for the To field, and the
Option *DROP.
*ADD. Use *ADD when a field does not exist in
the From file, but is being added to the To
file.
If a field is to be added (does not exist in in
the From file) specify *NONE for the From
field, use the To field name, and the Option
*ADD.
The value placed in the field will be blanks or
zeros for normal character or decimal fields.
If a Date type field (Type = L) is defined, the
date when the command began will be placed in
the correct format specified for the To field.
If a Time type field (Type = T) is defined, the
time at the beginning of the command will be
placed in every record added. If a Timestamp
type field (Type = Z) is defined, the same
timestamp as generated at the beginning of the
command will be placed in every record.
*RENAME. Use *RENAME when a field is renamed,
but has the same attributes. The data is moved
to the new field location.
If the only change is a rename of a field, CPYF
may be used with FMTOPT(*NOCHK). If however, a
field is renamed along with other changes,
*NOCHK cannot be used and you should consider
*RENAME.
*TRUNCATE. Use *TRUNCATE when mapping from a
larger field to a smaller field. Data will be
truncated as you have specified without any
errors occurring. If a decimal To field is
specified, no decimal alignment occurs.
The *TRUNCATE option is the same function as
*MAP. A different option is required so you
may acknowledge that data may be lost.
*DATCVT. Use *DATCVT when the field value is a
date that must be reformatted. When *DATCVT is
used, the next two values of the entry must be
specified (From/To date format).
FROMDATFMT If the Option is *DATCVT, specify the format of
the date to be converted from. The valid
values are *MDY6, *YMD6, *DMY6, *MDY8, *YMD8,
*DMY8, *JUL5, *JUL6, *LONGJUL (yyyyddd),
*CYMD7, *CYMD9, *MDYY8, *YYMD8, *DMYY8,
*MDYY10, *YYMD10, *DMYY10, *Y2, *YM4, *YY4,
*YYM5, *ISO10, *USA10, *EUR10, and *JIS10.
Each of the values describes a length. For
example, *MDY6 means the value exists in a 6
byte (or 6 digit) field with no separation
characters. Values such as *MDYY10 can only be
character and would appear as MM-DD-YYYY
separated by the date separator specified on
the command (the default is to use the system
value).
For date formats that do not include a month or
day (such as *Y2 or *YM4), a month of 01 and a
day of 01 are assumed. For example, if you
convert from *Y2 and a value of 97 to *YMD6,
the value will be 970101.
TODATFMT If the Option is *DATCVT, specify the format of
the date to be converted to. The valid values
are *MDY6, *YMD6, *DMY6, *MDY8, *YMD8, *DMY8,
*JUL5, *JUL6, *LONGJUL (yyyyddd), *CYMD7,
*CYMD9, *MDYY8, *YYMD8, *DMYY8, *MDYY10,
*YYMD10, *DMYY10, *Y2, *YM4, *YY4, *YYM5,
*ISO10, *USA10, *EUR10, and *JIS10.
Each of the values describes a length. For
example, *MDY6 means the value will exist in a
6 byte (or 6 digit) field with no separation
characters. Values such as *MDYY10 can only be
character and would appear as MM-DD-YYYY
separated by the date separator specified on
the command (the default is to use the system
value).
For date formats that do not include a month or
day (such as *Y2 or *YM4), a month of 01 and a
day of 01 are assumed. For example, if you
convert from *Y2 and a value of 97 to *YMD6,
the value will be 970101.
IGNZERODAT Whether to ignore dates with all zeros. The
default is *NO in which case a date of all
zeros is considered an error.
*YES may be specified to cause zeros to be
output.
IGNBLKDAT Whether to ignore dates with all blanks. The
default is *NO in which case a date of all
blanks is considered an error.
*YES may be specified to cause blanks to be
output.
CHRDECERR How to handle conversions from character to decimal
when invalid data is found. This parameter is only
used for the Options *MAP or *TRUNCATE. It does not
apply to the Option *DATCVT.
Valid data is considered to be blanks (converted to
zeros) and digits 0-9.
*NORMAL is the default which means that any other
characters that are found are considered invalid and
an escape message occurs.
*ALWEDTCHR may be specified to allow the editing
characters comma (','), period ('.'), or plus ('+')
to be bypassed. The minus sign ('-') found anywhere
in the field will also be bypassed and causes the
field to be set to a negative value.
For example, a value of 1.234 would be converted to
1234. No decimal alignment would occur.
*FRCZERO may be specified to allow any invalid
characters to be set to zero.
*ALLZEROS may be specified to cause the entire field
to be set to zeros if any invalid character is
found.
*ALLNINES may be specified to cause the entire field
to be set to 999... if any invalid character is
found. The intent of the *ALLNINES value is to
allow you to convert the data and then review the
fields that contain all 9s. This assumes that your
current data does not have all 9s. If you are
converting to a binary field, the *ALLNINES value
cannot be used because the decimal value does not
have a binary equivalent.
DATCVTERR How to handle conversions of dates where the data is
invalid or the dates are invalid. This parameter is
only used for the Option *DATCVT.
Valid data is considered to be blanks (converted to
zeros) and digits 0-9.
For most formats, any year value is considered
valid. Any month value must be between 01 and 12.
Any day value must be between 1 and 31 (or 1 and 30
for certain months). A February day is considered
valid between 1 and 29. No checking for leap years
occurs.
If the To date is an L type date field, the system
will reject any invalid February 29ths or year 0000.
*NORMAL is the default which means 'bad dates' or
'bad data' will cause an escape message.
*BLANKS may be specified to force any bad dates or
bad data to blanks. If the To field is character,
blanks will appear and any separator characters. If
the To field is decimal, zeros will appear. If the
To field is a *CYMD decimal format, the century will
be '1'. If the To field is a *CYMD character
format, the century will be blank.
If an the To field is an L type date field, *BLANKS
cannot be specified because the system will reject
the value with a data mapping error.
*ZEROS may be specified to force any bad dates or
bad data to 0000-00-00. If the To field is
character, zeros will appear and any separator
characters. If the To field is decimal, zeros will
appear. If the To field is a *CYMD format, the
century will be '1'.
If the To date is an L type date field, *ZEROS
cannot be specified because the system will reject
the value with a data mapping error.
*FRC0001 may be specified to force any bad dates or
bad data to 0001-01-01. This format is valid to
convert to any To field format, but would normally
be used with a format that contained a 4 digit year.
For example, if converted to a 6 position date, the
date would appear as 010101 and could be misleading
as it may be confused with valid dates. If the To
field is a *CYMD format, the century will be '1'.
*FRC1940 may be specified to force any bad dates or
bad data to 1940-01-31. This date is chosen as
unlikely to appear in your data and is valid to
convert to any To field format.
DATSEP The date separator to use if a date is being
reformatted to a format which uses a date separator.
The default is *SYSVAL which means to use the value
of the system value QDATSEP.
Some types such as *ISO have a standard separator
and do not use the command value.
Recovery
--------
When character data is mapped to decimal data or when date fields are
converted, errors can occur if the data is not valid. For example,
you may not have valid numeric data or the date field may be invalid.
The CHRDECERR and DATCVTERR parameters allow you to specify what
should occur if errors are found. The default for both parameters is
to send an escape message and end the command on the first
occurrence.
In many situations, you may not expect any errors. If you take the
defaults for CHRDECERR and DATCVTERR, some number of records may have
been converted to the To File before the error is found.
CVTDBFFMT allows a fairly simple method of recovery. If you have
already converted a significant number of records to the To file, do
the following:
1. Use the TAA Tool DSPMBRD to determine how many records exist
in the To file. This represents the number that have been
successfully converted.
2. Determine what value should be specified for either CHRDECERR
or DATCVTERR (or both). Depending on what the field is, you
may want to change the values after you have converted the
file. You can use one of the parameters to specify a unique
value (or mostly unique) in the data to allow you to find the
records after conversion.
For example, for character to decimal errors you could specify
CHRDECERR(*NINES). This will place an all 9s value into the
decimal field and allow you to find the data record after
conversion.
For date conversion errors, you must consider the type of
format you are writing to. For example, if it is a 6 byte (or
digit) field, a date of zeros may make sense, but this is an
invalid value if the field is a date type (L type).
3. Use CVTDBFFMT again and specify the FROMRCD parameter as one
greater than the number of records in the To file. This is
the record that caused the failure. Specify the CHRDECERR or
DATCVTERR (or both) value you have selected. Specify
MBROPT(*ADD) to add records to the existing file.
4. If you need to correct the data, a good solution is to run
OPNQRYF and follow it with a program created by the CRTPRTPGM
TAA Tool. This tool will allow you to print any fields from
the file. You can use OPNQRYF to select those records where
the unique (or mostly unique) value was placed in the field
and then print enough information about the record (such as
the key fields) to allow you to find and modify the error
conditions with your own programs or with DFU.
Restrictions
------------
** The maximum record size allowed is 9999 bytes.
** The maximum number of fields that are valid in either the From
file or To file is 200.
** The only support for Floating point (Type = F) or Hex fields
(Type = H) is to allow them to be copied (no reformatting) to
the To file or dropped.
** A logical file may be used for either the From or To file, but
may only have a single format.
** No decimal alignment occurs.
Prerequisites
-------------
The following TAA Tools must be on your system:
EDTVAR Edit variable
EXTLST Extract list
FILEFDBCK File feedback
HLRMVMSG HLL Remove message
RPGSTSDS RPG status data structure
RTVFLDARR Retrieve field array
SNDESCMSG Send escape message
SNDSTSMSG Send status message
SNDCOMPMSG Send completion message
Implementation
--------------
None, the tool is ready to use.
Objects used by the tool
------------------------
Object Type Attribute Src member Src file
------ ---- --------- ---------- ----------
CVTDBFFMT *CMD TAADBHN QATTCMD
TAADBHNC *PGM CLP TAADBHNC QATTCL
TAADBHNC2 *PGM CLP TAADBHNC2 QATTCL
TAADBHNC3 *PGM CLP TAADBHNC3 QATTCL
TAADBHNR *PGM RPG TAADBHNR QATTRPG
The TAADBHNC2 and TAADBHNC3 CL programs are called from TAADBHNR and
are used for conversion from/to Julian date formats.
|
