This chapter describes the runtime arguments for the DEFGEN utility. These arguments are entered at the command line when you run the utility. The DEFGEN utility produces a file defining the layouts of the source files and tables. The definitions are used by Collector and Replicat and, in some cases, Extract.
For more information about using DEFGEN, see the Administering Oracle GoldenGate for HP NonStop (Guardian).
DEFGEN accepts the following arguments.
| Argument | Description |
|---|---|
EXCLUDESYSTEM | INCLUDESYSTEM |
Instructs |
EXPANDDDL |
Supplies options to manipulate output for Enscribe record definitions. |
NCHARCOMPATIBILITY |
Sets the datatype to 0 from 2 for multibyte nchar and 64 from 66 for multibyte nvarchar. |
RECORDNAMEPROMPTING |
Supplies the name of an existing record definition to use when generating a definition for a new table. |
Use EXCLUDESYSTEM and INCLUDESYSTEM to omit or include the NonStop system name from the files and tables for which definitions are being generated. Must precede the EXPANDDDL argument.
INCLUDESYSTEM
EXCLUDESYSTEM | INCLUDESYSTEM
Use EXPANDDDL options to manipulate output for Enscribe record definitions containing arrays and redundant field names. This feature is primarily useful when mapping Enscribe files to SQL tables. It can also be useful when generating SQL tables based on Enscribe definitions using the DDLGEN utility.
EXPANDDDL is not necessary when the source database is NonStop SQL. If used, it must be the last argument entered.
EXPANDDDL separator option [, ...]
separatorA character separator for defining array output. See "Defining a separator".
optionFor information about the options, see:
Information on inheriting OCCURS on "Inheriting OCCURS".
Use separators for defining array output into columns. If any option besides NOEXPANSION is specified, a distinct field is output for each occurrence of a given field and a grouping field with the original name.
For example, consider the DDL definition:
03 FIELDX PIC 9(5) OCCURS 2 TIMES.
Normally, the array is output as a single field with three occurrences. However, arrays do not exist in an SQL environment, so it may be desirable to create a column for each occurrence of FIELDX. If the USEDASH option is specified, the following fields are output:
FIELDX FIELDX-1 FIELDX-2
FIELDX references all occurrences as a group item, while -1 and -2 are the individual occurrences.
If you are specifying a separator it must be the first option in the command string.
USEDASH
EXPANDDDL separator
separator is one of the following.
USEBRACKETSUse brackets [ ].
USEDASHUse a dash -.
USEUNDERSCOREUse an underscore _.
USETWOUNDERSCORESUse two underscores __.
NOEXPANSIONDo not use a separator.
Use the EXPANDGROUPARRAYS option to append indexes to fields that do not necessarily occur multiple times, but which are part of groups that occur multiple times.
NOEXPANDGROUPARRAYS
EXPANDDDL USEUNDERSCORE EXPANDGROUPARRAYS
For the following definition:
03 A OCCURS 2 TIMES. 04 B PIC 9(5). 03 X OCCURS 4 TIMES. 04 Y PIC 9(5) OCCURS 4 TIMES.
B within the second occurrence of A becomes B_2. The third occurrence of Y within the fourth occurrence of X becomes Y_4_3.
Resolving duplicate field names
You can resolve duplicate file names by specifying a duplicate field option for:
Unique numerical index to the end of a duplicate field or column.
Unique alpha character index to the end of a duplicate field or column.
Group name at the beginning of a duplicate field or column.
NORESOLVEDUPS
EXPANDDDL dup_field_option
dup_field_option is one of the following.
RESOLVEDUPINDEXAppends a numerical index to the end of each duplicate field or column. For example, END-DATE.YY would become YY-2 in the output since it is the second occurrence of YY in the definition.
RESOLVEDUPALPHAINDEXAppends an alpha character index to the end of redundant fields or columns; BEGIN-DATE.MM would become MM-A (occurrences are designated A-Z).
RESOLVEDUPGROUPPrefixes a duplicate field name with its group name and separator.
RESOLVEDUPFULLNAMEUse only when RESOLVEDUPGROUP cannot resolve duplicates. RESOLVEDUPFULLNAME triggers the system to fully qualify the field name so that the redundancy is resolved.
NORESOLVEDUPSDo not resolve duplicate field names.
Consider the following definition.
03 BEGIN-DATE. 04 YY PIC 99. 04 MM PIC 99. 04 DD PIC 99. 03 END-DATE. 04 YY PIC 99. 04 MM PIC 99. 04 DD PIC 99.
BEGIN-DATE and END-DATE duplicate the YY, MM and DD field names. Normally these duplications would be resolved by referencing the higher level group item, such as BEGIN-DATE.DD or END-DATE.DD. The RESOLVEDUPGROUP duplicate field option prefixes each duplicate field name with its group name, as in BEGIN-DATE-DD and END-DATE-DD.
In this example, the first non-redundant group above the elementary item is used for the prefix. For example, END-DATE.DATE-DEF.YY would become END-DATE-YY (DATE-DEF is omitted).
03 BEGIN-DATE. 04 DATE-DEF. 05 YY PIC 99. 05 MM PIC 99. 05 DD PIC 99. 03 END-DATE. 04 DATE-DEF. 05 YY PIC 99. 05 MM PIC 99. 05 DD PIC 99.
Omitting or including redefined fields
Use OMITREDEFS or INCLUDEREDEFS to omit or include redefined fields in the output definition. Including redefinitions has the following consequences.
DDLGEN outputs a column for every elementary field item in the source definition, regardless of whether or not it redefines another field.
When the Extract program specifies FORMATASCII or FORMATSQL, the Collector outputs an ASCII field for each redefined field.
OMITREDEFS
EXPANDDDL [OMITREDEFS | INCLUDEREDEFS]
OMITREDEFSOmit redefined fields.
INCLUDEREDEFSInclude redefined fields.
Use MAXCOLNAMELEN to manage long field names. By default, the maximum field name length is 30. You can change this value with the MAXCOLNAMELEN option.
When creating new field names to resolve duplicate occurrences, names occasionally exceed the length specified by MAXCOLNAMELEN. When this happens, you are prompted for an alternative column name. To avoid the prompt, specify the NOFIXLONGNAMES option after MAXCOLNAMELEN.
MAXCOLNAMELEN is invoked only with EXPANDGROUPARRAYS or RESOLVEDUPGROUP.
FIXLONGNAMES (prompt for an alternative column name)
EXPANDDDL [MAXCOLNAMELEN length | [NOFIXLONGNAMES]]
lengthThe field name length.
NOFIXLONGNAMESSpecify to prevent prompting for a new column name. A warning message is issued.
Use ZEROFILL to expand output definitions to a constant width, similar to:
EXPANDDDL USEUNDERSCORE ZEROFILL 3 EXPANDDDL USEUNDERSCORE ZEROFILL ARRAYWIDTH
ZEROFILL 1
EXPANDDDL {ZEROFILL width} | {ARRAYWIDTH}
widthA value indicating the character width of the output.
ARRAYWIDTHSpecifies that the output width match the number of elements in the array.
If ZEROFILL ARRAYWIDTH is specified for definition 03 FLAG PIC X OCCURS 500 TIMES, then occurrence 7 becomes FLAG-007 and occurrence 423 becomes FLAG-423. The width is equal to the occurrence frequency (500) and is three characters wide.
Using the definition from the previous example and specifying ZEROFILL 5 results in occurrence 7 becoming FLAG-00007 and occurrence 423 becoming FLAG-00423.
Use NCHARCOMPATIBILITY to cause DEFGEN to specify single byte data types for columns that are multibyte. The data type is changed from 2 to 0 for multibyte nchar columns and from 66 to 64 for multibyte nvarchar columns.
No change to the datatypes
NCHARCOMPATIBILITY
Use RECORDNAMEPROMPTING to trigger DEFGEN to prompt for the name of an existing record definition instead of a file. Use this parameter to enter the record name when the same definition is to be used for multiple tables with identical definitions made up of the same columns, column order, and data types.
File name prompting
RECORDNAMEPROMPTING