3 Using Flat Files as Sources or Targets

Defining Character Data Files

You can import metadata from various types of files including character data set files and COBOL copybooks.

To define flat file metadata, complete the following steps:

1. Create a flat file module.

   Create a module for each unique directory or path in your file system from which you want to import file metadata. See "Creating Flat File Modules".

2. Define the structure of the file.

   The Flat File Sample Wizard enables you to view a sample of the flat file and to define record organization and file properties. The wizard enables you to sample and define common flat file formats such as string and ASCII. See "Using the Flat File Sample Wizard".

   For files with complex record structures, the Flat File Sample Wizard may not be suitable for sampling the data. In such cases, you must create a flat file and define its structure accordingly. See "Using the Create Flat File Wizard" for creating and defining the structure of a flat file.

   For COBOL copybooks, use the COBOL import dialog box to import metadata from copybooks. You can also set the import options depending on the copybook. See "Importing Metadata Definitions from COBOL Copybooks" for more details.

About External Tables

An external table is a read-only table that is associated with a single record type in a flat file. External tables represent data from a non-relational source in a relational table format. When you use an external table in a mapping, column properties are based on the SQL properties defined when importing the flat file. For more information about SQL properties for flat files, see "SQL Properties".

When you use an external table as a source table in a mapping, you can use it as a regular source table. Oracle Warehouse Builder generates PL/SQL code to select rows from the external table. You can also get parallel access to the file through the table. You also have access to additional relational function operators.

You can either import an existing external table from another database as described in"Importing an External Table" or define a new external table as described in "Creating a New External Table Definition".

Choosing Between External Table and Flat File Operators

You can introduce source data from a flat file into a mapping either through an external table or a flat file operator. In general, external tables are the preferred method of loading large volumes of data from flat files.

Note the following details when comparing external tables and flat files:

• External table operators and PL/SQL mappings provide for maximum performance, including exploitation of database parallelism during load. The full range of transformation operators is available, because the mappings are PL/SQL mappings. Over time, ETL mappings built with external tables takes advantage of further performance improvements at the database level.

• Flat file operators and SQL*Loader mappings are a fully supported method of loading flat files. Oracle Warehouse Builder generates native SQL*Loader code for a SQL*Loader mapping. A more limited range of operators is supported in SQL*Loader mappings. You must stage data in an intermediate table and then use a PL/SQL mapping to transform it further before loading into a final target.

For more information about differences between external tables and SQL*Loader (flat file operators), see Oracle Database Utilities.

For more information about different types of mappings, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Describing the Flat File Module

Enter a name and an optional description for the flat file module on the Name and Description page.

Defining Locations for Flat File Modules

Locations for flat file modules identify the paths in the file system from which you sample existing files or to which you create new files. You can define a new location or select an existing location on the Connection Information page.

Flat file modules have metadata and data locations. When you specify a location for a module, both metadata location and data location point to the same location. You can specify different locations for the metadata and the data by editing the module.

To import metadata from flat files located in different directories or paths in the system then for ease of use, create separate Oracle Warehouse Builder modules for each path. For example, suppose the files are located at the following paths, c:\folder1 and c:\folder1\subfolder. You can create two file modules C_FOLDER1 and C_FOLDER1_SUBFOLDER and associate them with the corresponding paths. However, associating a module with a path does not restrict you from importing metadata definitions of files residing in a different path. You can define a path as the default, and later import files from a different path.

A data location identifies only a folder in the file system and does not include subfolders.

Connection Information Page

The Connection page is displayed with a default location name that is based on the module name that you entered in the Name and Description page. If you do not want to create a location, then select from the list of existing locations.

On the Connection Information page, click Edit to open the "Edit File System Location Dialog Box" and specify the location details. This location becomes the metadata and data location.

Edit File System Location Dialog Box

On the Edit File System Location dialog box, enter the fully qualified directory, including the drive letter.

Figure 3-1 shows the Edit Location dialog box.

Figure 3-1 Edit File System Location Dialog Box

Description of "Figure 3-1 Edit File System Location Dialog Box"

Record Organization

Indicate how to organize the records in the file. Select between the two options to indicate how the length of each record in the file is determined:

• Records delimited by: Select this option to designate the end of each record by a delimiter. Then specify that record delimiter. You can accept the default record delimiter, new line (\n), or you can enter a new value. You can provide multiple characters and hexadecimal characters as a record delimiter. The hexadecimal character format is x'<hexadecimal string>' or X'<hexadecimal string>'. Using hexadecimal characters is useful if the delimiter character is not a new line character (\n) or carriage return<CR>. For example, to specify the pipe symbol (|) as the delimiter, use its hexadecimal value x'7C'.

• Record length (in characters): Select this option to create a file with all records having the same length. Then specify the number of characters in each record. For files with multibyte characters, count a multibyte character as one character.

Logical Record Definition

By default, the wizard creates a file in which each physical record corresponds to one logical record. You can override the default to create a file composed of logical records that correspond to multiple physical records.

• Number of physical records for each logical record: The data file contains a fixed number of physical records for each logical record.

  PHYSICAL_RECORD1
  PHYSICAL_RECORD2
  PHYSICAL_RECORD3
  PHYSICAL_RECORD4
  

  In the preceding example, if the number of physical records for each logical record is 2, then PHYSICAL_RECORD1 and PHYSICAL_RECORD2 form one logical record and PHYSICAL_RECORD3 and PHYSICAL_RECORD4 form the second logical record.

• End character of the current physical record: The data file contains a variable number of physical records with a continuation character at the end that signifies that the record is continued in the next physical record.

  In the following example, the continuation character is a percentage sign (%) after the record.

  PHYSICAL_RECORD1%
  PHYSICAL_RECORD2       end log rec 1
  PHYSICAL_RECORD3%
  PHYSICAL_RECORD4       end log rec 2
  

• Start character of the next physical record: The data file contains a variable number of physical records with a continuation character at the beginning of each physical record that signifies that the record continues from the previous physical record.

  The following example shows two logical records with a continuation character at beginning of the record.

  PHYSICAL_RECORD1
  %PHYSICAL_RECORD2      end log rec 1
  PHYSICAL_RECORD3
  %PHYSICAL_RECORD4      end log rec 2
  

  More than two records can be joined with this technique. The following example shows four physical records for each logical record using continuation at beginning.

  PHYSICAL_RECORD1
  %PHYSICAL_RECORD2
  %PHYSICAL_RECORD25
  %PHYSICAL_RECORD26   end log record 1
  PHYSICAL_RECORD3
  %PHYSICAL_RECORD4
  %PHYSICAL_RECORD45
  %PHYSICAL_RECORD46 end log record 2
  

Number of Rows to Skip

When defining an existing file, indicate the number of records to skip at execution time in Skip rows. This is useful for skipping over header and field name records.

When creating a new target file, you can leave this value blank.

Field Format

Select between Fixed Length and Delimited formats for the file.

To create a delimited file, specify the following properties:

• Field delimiter: Field delimiters designate where one field ends and another begins. You can enter a field delimiter or select one from the list. The list displays common field delimiters. However, you may enter any character as a delimiter except the ones used for enclosures. The default is the comma (,). You can provide multiple characters and hexadecimal characters as a delimiter. The hexadecimal character format is x'<hexadecimal string>' or X'<hexadecimal string>'. For example, to specify the pipe symbol (|) as the delimiter, use its hexadecimal value x'7C'.

• Enclosures (Left and Right): Some delimited files contain enclosures that denote text strings within a field. If the file contains enclosures, enter an enclosure character in the text box or select one from the list. The list displays common enclosures. However, you may enter any character. The default for both the left and right enclosure is the double quotation mark ("). You can specify multiple characters and hexadecimal characters as field enclosures.

Figure 3-2 File Properties Page

Description of "Figure 3-2 File Properties Page"

SQL*Loader Properties

The first set of properties the wizard displays are for the SQL*Loader utility. When you use the flat file directly as a source in a mapping, SQL*Loader and the properties you set here are used. SQL*Loader properties include details of how the following are mapped to a relational table: "Type", "Length", "Precision", "Scale", "Mask", "NULLIF", and "DEFAULTIF". See Oracle Database Concepts for more details.

Type

Describes the data type of the field for SQL*Loader. You can use the wizard to import many data types such as CHAR, DATE, DECIMAL EXTERNAL, FLOAT EXTERNAL, INTEGER EXTERNAL, ZONED, and ZONED EXTERNAL. For complete information about SQL*Loader field and data types, see Oracle Database Utilities.

Length

For delimited files, specifies the maximum field length to be used by SQL* Loader.

Precision

Specifies the number of digits for certain data types such as Zoned and Float. See Oracle Database Utilities for more details.

Scale

Specifies the number of decimal digits for certain data types such as Zoned and Float. See Oracle Database Utilities for more details.

Mask

SQL*Loader uses DD-Mon-YY as its default date mask. You can override this default by entering a valid date mask when you describe the file. For example, if the input data has the format DD-Mon-YYYY rather than SQL*Loader default, you can enter the true format as a mask.

NULLIF

You can override the default action of SQL*Loader by placing a NULLIF condition on a field. For example, when a character field contains all blanks, you can direct SQL*Loader to mark the field as null rather than storing the blanks. Valid syntax for this field includes =BLANKS, ='quoted string', =X'ff' to indicate hexadecimal values, and != for 'not equal to' logic.

DEFAULTIF

You can override the default action of SQL*Loader by placing a DEFAULTIF condition on a field. For example, when a numeric or DATE field contains all blanks, SQL*Loader rejects the entire record. To override this action, type =BLANKS in the DEFAULTIF property. When SQL*Loader evaluates this condition, it sets the numeric field to zeros and loads the record. Valid syntax for this field includes =BLANKS, ='quoted string', =X'ff' to indicate hexadecimal values, and != for 'not equal to' logic.

SQL Properties

These properties specify how the fields in a flat file translate to the columns in a relational table. They are used to define the characteristics of an external table. They are also used to automatically generate fields for mapping to relational operators. Similarly, if the flat file is used as a target, then these properties are used to generate the PL/SQL code.

The SQL properties you set here have the following implications for mapping design, validation, and generation:

• External table: If you create an external table based on a single flat file record type, the columns properties are based on the SQL properties you defined for the flat file. For more information about external tables, see "Using External Tables".

• Populating an Empty Mapping Object: In a mapping, if you populate an empty relational object with the metadata, then the object inherits the SQL properties you defined for the flat file source.

• Flat file target: If you use the flat file as a target in a mapping, the target does not inherit the SQL properties. Instead, all fields inherit the default SQL*Loader data type.

SQL Type

Oracle Warehouse Builder supports many SQL data types such as CHAR, DATE, FLOAT, and BLOB.

The wizard assigns a default value for the SQL type based on SQL*Loader properties you set. If you accept the default SQL type, the type is updated if you later change SQL*Loader properties. However, if you override the SQL type by selecting a new SQL type from the list, it then becomes independent of the flat file SQL*Loader data type.

SQL Length

This property defines the length for the SQL column, if appropriate.

SQL Precision

This property defines the precision for the SQL column, if appropriate. For example, when defining NUMBER and FLOAT fields, the precision may be set.

SQL Scale

This property defines the scale for the SQL column, if appropriate. For example, when defining NUMBER and FLOAT fields, the scale may be set.

Select Automatically update start and end positions for all fields if you want all the field positions to be automatically recalculated based on changes made to any field.

Once you define the metadata of the new flat file, you can use it as a source or target file, or create an external table using a record from the file.

Flat File Wizard for Simple Flat Files

For simple flat files that are delimited and contain a single record type, the Flat File wizard guides you through the following tasks:

• "Describing the Flat File"

• "Specifying the Record Organization"

• "Specifying Field Properties"

Describing the Flat File

Use the Name page to describe the flat file you are sampling.

• Name: This name uniquely identifies the file in the module. By default, the wizard creates a name based on the name of the source file by replacing invalid characters with an underscore. For example, if the file name is myfile.dat, the wizard assign the workspace name myfile_dat.

  If you rename the file, do not include a space or any punctuation in the name. You can include an underscore. You can use uppercase and lowercase letters. Do not start the name with a digit. Do not start a name with the reserved prefix OWB$.

• Description: You can enter an optional description for the file.

• Character set: Character sets determine what languages can be represented in database objects and files. The default Globalization Support character set matches the character set defined for the computer hosting Oracle Warehouse Builder. If the character set differs from that of the source file, the data sample might appear unintelligible. You can display the data sample in the character set native to the source by selecting it from the list. For complete information about NLS character sets, see Oracle Database Globalization Support Guide.

• Number of characters to sample: This value specifies the number of characters that is read and displayed. The number of characters that is read cannot be canceled, so ensure that you pick a reasonable number of characters. If you are sampling a multi-record file, then ensure that the sample is large enough to include at least one of each type. By default, the wizard samples the first 10000 characters. To determine an optimum value for this field, see "Example: Flat File with Multiple Record Types".

• Advanced: Do not click the Advanced button for simple flat files. The advanced option is required only for complex flat files as described in "Flat File Wizard For Complex Flat Files".

Click Next to continue with "Specifying the Record Organization". At all steps, the wizard updates the sample displayed at the bottom of the wizard page. You can use the scroll bars to see the sample data.

Specifying the Record Organization

Specify the following properties:

• Records delimited by: Select this option if the end of each record is designated by a delimiter. Then specify that record delimiter. You can accept the default record delimiter, carriage return (<CR>), or you can enter a new value. For symbols other than \n and <CR>, specify the hexadecimal value of the character used as the delimiter.

• Record length (in characters): Select this option if each record in the file is the same length. Then specify the number of characters in each record. For files with multibyte characters, count a multibyte character as one character.

• Field delimiter: Field delimiters designate where one field ends and another begins. You can enter a field delimiter or select one from the list. The list displays common field delimiters. However, you may enter any character as a delimiter except the ones used for enclosures. The default is the comma (,). You can also specify multiple characters and hexadecimal characters as a field delimiter.

• Enclosures (Left and Right): Some delimited files contain enclosures that denote text strings within a field. If the file contains enclosures, enter an enclosure character in the text box or select one from the list. The list displays common enclosures. However, you may enter any character. The default for both the left and right enclosure is the double quotation mark ("). Multiple characters and hexadecimal characters can be specified as field enclosures.

Specifying Field Properties

Use the Field Properties page in the Flat File Sample Wizard to define properties for each field. The wizard assigns a name to each field. It assigns 'C1'to the first field, 'C2' to the second, and so on. To rename fields, click a field and enter a new name.

For single record file types, you can instruct the wizard to use the first record to name the fields. Indicate this by selecting the Use the first record as the field names box.

The Field Properties page shows both "SQL*Loader Properties" and "SQL Properties". Use the scroll bar to scroll to the right and view all the properties.

The wizard deactivates properties that do not apply to a given data type. For example, you can edit the length for a CHAR, but precision and scale are not available. Deactivated properties are grayed out.

Flat File Wizard For Complex Flat Files

For complex files, the Flat File wizard guides you through the following tasks. The advanced mode enables you to define files with fixed length fields (in addition to delimited), files that contain multiple record types, or files that use logical records (multiple physical records per logical record).

• "Describing the Flat File"

• "Selecting the Record Organization"

• "Selecting the File Format"

• "Selecting the File Layout"

• "Selecting Record Types (Multiple Record Type Files Only)"

• "Specifying Field Lengths (Fixed-Length Files Only)"

• "Specifying Field Properties"

Describing the Flat File

Use the Name page to describe the flat file you are sampling.

• Name: This name uniquely identifies the file in the workspace. By default, the wizard creates a name based on the name of the source file by replacing invalid characters with an underscore. For example, if the file name is myfile.dat, the wizard assign the workspace name myfile_dat.

  If you rename the file, do not include a space or any punctuation in the name. You can include an underscore. You can use uppercase and lowercase letters. Do not start the name with a digit. Do not start a name with the reserved prefix OWB$.

• Description: You can enter an optional description for the file.

• Character set: Character sets determine what languages can be represented in database objects and files. The default Globalization Support character set matches the character set defined for the computer hosting Oracle Warehouse Builder. If the character set differs from that of the source file, the data sample might appear unintelligible. You can display the data sample in the character set native to the source by selecting it from the list. For complete information about NLS character sets, see Oracle Database Globalization Support Guide.

• Number of characters to sample: You can indicate the number of characters for the wizard to sample from the data file. By default, the wizard samples the first 10000 characters. To determine an optimum value for this field, see "Example: Flat File with Multiple Record Types".

Click Advanced to continue with "Selecting the Record Organization". At all steps, the wizard updates the sample displayed at the bottom of the wizard page. You can use the scroll bars to see the sample data.

Selecting the Record Organization

Use the Record Organization page to indicate how records are organized in the file you are sampling. Select between the two options to indicate how the length of each record in the file is determined:

• Records delimited by: If the end of each record is designated by a delimiter, then specify that record delimiter. You can accept the default record delimiter, carriage return (<CR>), or enter a new value. You can specify multiple characters and hexadecimal characters as a record delimiter. If the delimiter is a symbol other than \n or <CR>, then specify the hexadecimal character of the symbol. The hexadecimal character format is x'<hexadecimal string>' or X'<hexadecimal string>'.

• Record length (in characters): Select this option if each record in the file is the same length. Then specify the number of characters in each record. For files with multibyte characters, count a multibyte character as one character.

Selecting the File Format

Use the File Format page to select between Fixed Length and Delimited formats for the fields in the file. The Flat File Sample Wizard does not sample multibyte character files with a fixed record format. For such files, use the Create Flat File wizard. For more details, see "Using the Create Flat File Wizard".

When you select a file format, the wizard updates the sample displayed at the bottom of the wizard page. You can use the scroll bars to navigate the sample data.

Fields in a file can either be of fixed length or delimited.

For fixed length fields, select Fixed Length. If you select this option then you must define the field lengths in the Field Lengths page. See "Specifying Field Lengths (Fixed-Length Files Only)".

When the fields are delimited, specify the following properties:

• Field delimiter: Field delimiters designate where one field ends and another begins. You can enter a field delimiter or select one from the list. The list displays common field delimiters. However, you may enter any character as a delimiter except the ones used for enclosures. The default is the comma (,). You can also specify multiple characters and hexadecimal characters as a field delimiter. The hexadecimal character format is x'<hexadecimal string>' or X'<hexadecimal string>'.

• Enclosures (Left and Right): Some delimited files contain enclosures that denote text strings within a field. If the file contains enclosures, enter an enclosure character in the text box or select one from the list. The list displays common enclosures. However, you may enter any character. The default for both the left and right enclosure is the double quotation mark ("). Multiple characters and hexadecimal characters can be specified as field enclosures.

Click Next to continue with the wizard.

Selecting the File Layout

Use the File Layout page to specify the number of rows to skip and to select between a single record type versus multiple record types.

Indicate the number of records to skip in Skip rows. This is useful for skipping over unwanted header information. If one of the records includes field names, skip the preceding header records so that the record containing field names is displayed as the first record in the file. Later in the wizard, on the Field Properties page, you can instruct the wizard to use that record for field names if you are defining a single record file type.

Indicate whether the file contains a single record type or multiple record types. Later in the wizard you can instruct the wizard to scan the file for the record types. For more information about multiple record types, see "Selecting Record Types (Multiple Record Type Files Only)".

Selecting Record Types (Multiple Record Type Files Only)

Use the Record Types wizard page to scan the flat file for record types, add or delete record types, and assign type values to the record types.

Example: Flat File with Multiple Record Types

In files with multiple record types, one of the fields distinguishes one record type from the next. When you use the Flat File Sample Wizard, you instruct the wizard to scan a specified field of every record for the record type values.

Figure 3-4 shows an example of a comma delimited file with two record types, "m" and "f". In this case, instruct the wizard to scan the third field. The wizard returns "m" and "f" as the type values.

Figure 3-4 Example of a File with Multiple Record Types

When you use the wizard to sample flat files with multiple record types, ensure that the sample size you specified on the Name page is large enough to include each record type at least once. The default is 10000 characters.

If you do not see all of the required record types in the display area, you must specify a larger sample size on the Name page. Ensure that the sample size is large enough to include all record types. If all record types do not appear within a reasonable number of characters, you can mock up a sample file with rows selected from different parts of the master file to provide a representative set of data. If you know the record layout well, you can scan a representative sample and then manually add new record types.

Specifying Field Lengths (Fixed-Length Files Only)

When you use the Flat File Sample Wizard to define a fixed-length flat file, you must define the length of each field in the file.

You can define field lengths by entering in the field lengths or by using the ruler.

If you know the length of each field, enter the field length in Field Lengths. Separate each length by commas. The wizard displays the changes to the sample at the bottom of the wizard page.

To use the ruler, click any number or hash mark on the ruler. The wizard displays a red check mark on top of the ruler and marks the boundary with a red line. If you make a mistake, double-click the marker to delete it or move the marker to another position. Use the ruler to create markers for each field in the file.

Specifying Field Properties

Use the Field Properties page in the Flat File Sample Wizard to define properties for each field. The wizard assigns a name to each field. It assigns 'C1'to the first field, 'C2' to the second, and so on. To rename fields, click a field and enter a new name.

For single record file types, you can instruct the wizard to use the first record in the file to name the fields. Indicate this by selecting the Use the first record as the field names box.

The Field Properties page shows both "SQL*Loader Properties" and "SQL Properties". Use the scroll bar to scroll to the right and view all the properties.

The wizard deactivates properties that do not apply to a given data type. For example, you can edit the length for a CHAR, but precision and scale are not available. Deactivated properties are grayed out.

COBOL Data Types

The USAGE and PICTURE clauses are used to define the format and characteristics of data elements. If a USAGE clause is not specified, the data is in DISPLAY format, either external numeric or external character. When considered, the picture and usage identify the data type. Detailed information concerning COBOL data types and how they are mapped to relational data types is described inTable 3-1.

Arrays Defined on Fields

COBOL provides support for both arrays and varying arrays. These complex structures are identified by the use of an OCCURS clause. Varying arrays are defined with the additional specification of a DEPENDING ON clause. For arrays, the OCCURS clause indicates the number of elements in the array. For varying arrays, the occurs specification includes a range of elements from x TO y and the DEPENDING ON clause identifies a field that contains the actual number of elements in the array. An array or a varying array can be defined on an elementary field or a group. Example 3-2 provides an example of an array defined on elementary fields.

01 EMPLOYEE-RECORD.
   05 EMP-ID PIC 9(6).
   05 EMP-REGION PIC 9.
   05 EMP-DEPT PIC 999.
   05 EMP-HIRE-DATE.
      10 EMP-HIRE-DATE-MM PIC 99.
      10 EMP-HIRE-DATE-DD PIC 99.
      10 EMP-HIRE-DATE-YYYY PIC 9999.
   05 EMP-SALARY PIC 9(9).
   05 EMP-NAME PIC X(15).
   05 EMP-SKILL-LEVEL PIC 99 OCCURS 4 TIMES.
   05 EMP-SKILL-ID PIC 9(4) OCCURS 4 TIMES.

Two independent arrays are defined, one on EMP-SKILL_LEVEL and one on EMP-SKILL_ID. In this example, the record contains four occurrences of EMP-SKILL-LEVEL, followed by four occurrences of EMP-SKILL-ID. In the file, each record is constructed as: EMP_SKILL_LEVEL, EMP_SKILL_LEVEL, EMP_SKILL_LEVEL, EMP_SKILL_LEVEL, EMP_SKILL_ID, EMP_SKILL_ID, EMP_SKILL_ID, EMP_SKILL_ID.

Example 3-3 provides an example of a varying array defined on elementary fields.

01 EMPLOYEE-RECORD.
   05 EMP-ID PIC 9(6).
   05 EMP-REGION PIC 9.
   05 EMP-DEPT PIC 999.
   05 EMP-HIRE-DATE.
      10 EMP-HIRE-DATE-MM PIC 99.
      10 EMP-HIRE-DATE-DD PIC 99.
      10 EMP-HIRE-DATE-YYYY PIC 9999.
   05 EMP-SALARY PIC 9(9).
   05 EMP-NAME PIC X(15).
   05 EMP-SKILL-COUNT PIC 99.
   05 EMP-SKILL-LEVEL PIC 99 OCCURS 1 TO 4 TIMES.
      DEPENDING ON EMP-SKILL-COUNT.
   05 EMP-SKILL-ID PIC 9(4) OCCURS 1 TO 4 TIMES.
      DEPENDING ON EMP-SKILL-COUNT.

Two independent arrays are defined, one on EMP-SKILL_LEVEL and one on EMP-SKILL_ID. In this example, the value in EMP-SKILL-COUNT determines the number of occurrences in both arrays. In the file, a record with EMP_SKILL_COUNT equal to one is constructed as: EMP_SKILL_LEVEL, EMP_SKILL_ID. If the value of EMP-SKILL-COUNT is two, then two occurrences of EMP-SKILL-LEVEL are followed by two occurrences of EMP-SKILL-ID. In the file, a record with EMP_SKILL_COUNT equal to two is constructed as: EMP_SKILL_LEVEL, EMP_SKILL_LEVEL, EMP_SKILL_ID, EMP_SKILL_ID.

Example 3-4 provides an example of an array defined on a group field.

01 EMPLOYEE-RECORD.
   05 EMP-ID PIC 9(6).
   05 EMP-REGION PIC 9.
   05 EMP-DEPT PIC 999.
   05 EMP-HIRE-DATE.
      10 EMP-HIRE-DATE-MM PIC 99.
      10 EMP-HIRE-DATE-DD PIC 99.
      10 EMP-HIRE-DATE-YYYY PIC 9999.
   05 EMP-SALARY PIC 9(9).
   05 EMP-NAME PIC X(15).
   05 EMP-SKILLS OCCURS 4 TIMES.
      10 EMP-SKILL-LEVEL PIC 99.
      10 EMP-SKILL-ID PIC 9(4).

In this example an array is defined with four elements. Each element contains one occurrence of each field: EMP_SKILL_LEVEL and EMP_SKILL_ID. In the file, each record is constructed as: EMP_SKILL_LEVEL, EMP_SKILL_ID, EMP_SKILL_LEVEL, EMP_SKILL_ID, EMP_SKILL_LEVEL, EMP_SKILL_ID, EMP_SKILL_LEVEL, EMP_SKILL_ID.

Example 3-5 provides an example of a varying array defined on a group field.

01 EMPLOYEE-RECORD.
   05 EMP-ID PIC 9(6).
   05 EMP-REGION PIC 9.
   05 EMP-DEPT PIC 999.
   05 EMP-HIRE-DATE.
      10 EMP-HIRE-DATE-MM PIC 99.
      10 EMP-HIRE-DATE-DD PIC 99.
      10 EMP-HIRE-DATE-YYYY PIC 9999.
   05 EMP-SALARY PIC 9(9).
   05 EMP-NAME PIC X(15).
   05 EMP-SKILL-COUNT PIC 99.
   05 EMP-SKILLS OCCURS 4 TIMES DEPENDING ON EMP-SKILL-COUNT.
     10 EMP-SKILL-LEVEL PIC 99.
     10 EMP-SKILL-ID PIC 9(4).

In this example, one array is defined with up to four elements. The value in EMP-SKILL-COUNT defines the number of occurrences of the array. Therefore, if the value of EMP-SKILL-COUNT is set as one, then there is one occurrence of EMP_SKILLS. In the file, a record with EMP_SKILL_COUNT equal to one is constructed as: EMP_SKILL_LEVEL, EMP_SKILL_ID.

If the value of EMP-SKILL-COUNT is two, then there are two occurrences of EMP-SKILLS. In the file, a record with EMP_SKILL_COUNT equal to two is constructed as: EMP_SKILL_LEVEL, EMP_SKILL_ID, EMP_SKILL_LEVEL, EMP_SKILL_ID.

Defining Multiple Records

COBOL generated files can contain multiple record types. Whenever there is multiple level 01 item in a file definition, each level 01 provides a separate definition of the data record area. Only one record is kept in the data record area at a time, so only one level 01 definition is used at a time. For example, a file may contain two types of records, department records and employee records. Level 01 items are defined for both department and employee records. The hierarchy for each record immediately follows the level 01 item for that record and provides the definitions for the entire record. Each record contains a field that identifies the record type. This record type is at the same position for all record definitions.

In the following example, the record type is in the first position:

01   DEPARTMENT-RECORD. 
      05 DEPT-RECORD-TYPE PIC X. 
      05 DEPT-ID PIC 999. 
      05 DEPT-NAME PIC X(30). 
      05 DEPT-DESCRIPTION PIC X(160). 
01   EMPLOYEE-RECORD.
      05 EMP-RECORD-TYPE PIC X. 
      05 EMP-ID PIC 9(6). 
      05 EMP-NAME PIC X(30). 
      05 EMP-REGION PIC 9. 
      05 EMP-DEPT PIC 999.  

Defining Group Fields

As seen in the section on Data Hierarchy, fields can be organized in groups. These groups actually provide an additional definition of the fields and are used to access the data.

Redefining Fields and Groups

COBOL provides the ability to redefine a field or a group. Redefinition does not define data at a new location, but instead provides an additional definition of data characters that have been previously defined.

01  EMPLOYEE-RECORD. 
   05 EMP-ID PIC 9(6). 
   05 EMP-ID-R REDEFINES EMP-ID. 
      10 EMP-ID-GROUP PIC 99. 
      10 EMP-ID-NUM PIC 9999. 
   05 EMP-REGION PIC 9. 
   05 EMP-DEPT PIC 999.

In the above example, the EMP-ID field is defined as a 6 digit numeric field. A redefinition is provided dividing the field into two fields: EMP-ID-GROUP is defined as the first two digits of the EMP-ID field. EMP-ID-NUM is defined as the last four digits of the EMP-ID field. EMP-ID-NUM and EMP-ID-GROUP both begin at position 1 in the record.

01 EMPLOYEE-RECORD.
05 EMP-ID PIC 9(6).
05 EMP-ID-R REDEFINES EMP-ID.
10 EMP-ID-GROUP PIC 99.
10 EMP-ID-NUM PIC 9999.
05 EMP-REGION PIC 9.
05 EMP-DEPT PIC 999.
05 EMP-HIRE-DATE.
10 EMP-HIRE-DATE-MM PIC 99.
10 EMP-HIRE-DATE-DD PIC 99.
10 EMP-HIRE-DATE-YYYY PIC 9999.
05 EMP-SALARY PIC 9(9).
05 EMP-NAME PIC X(15).
05 EMP-SKILLS OCCURS 4 TIMES.
10 EMP-SKILL-LEVEL PIC 99.
10 EMP-SKILL-ID PIC 9(4).
05 EMP-SKILLS-R REDEFINES EMP-SKILLS.
10 EMP-SKILL-LEVEL1 PIC 99.
10 EMP-SKILL-ID1 PIC 9(4).
10 EMP-SKILL-LEVEL2 PIC 99.
10 EMP-SKILL-ID2 PIC 9(4).
10 EMP-SKILL-LEVEL3 PIC 99.
10 EMP-SKILL-ID3 PIC 9(4).

In the above example, the EMP-SKILL array has been redefined so that each element is expanded providing a field for each element. As in the previous example, the definitions for EMP-SKILLS-R is defining the same data area as EMP-SKILLS. The first occurrence of EMP-SKILL-LEVEL is at the same position in the record as EMP-SKILL-LEVEL1.

COBOL File Formats

COBOL programs can create files of different organization. These include the following:

• Line Sequential: Line sequential files are generally known as text files because the primary use of this file type is for display data. The records in these files can only be accessed in the order they were written. Line sequential files contain variable length records. A record delimiter separates each record in the file. The record delimiter that is used is operating system dependent and is inserted after the last character in each record.

• Record Sequential: Record sequential files are also accessed in the order they were written. This file organization is more flexible than line sequential. Records can be of fixed or variable length. The record sequential organization is used for sequential files that contain binary or packed data, or any data that may have other non-printable characters. In fixed length files, every record that is written to the file is the same length. If necessary, the record is padded with blanks to ensure uniform length. With variable length records, each record is written based on the actual size of the record. A Record Descriptor Word (RDW) is inserted at the beginning of each record. The RDW contains the actual length of the record. It is not considered part of the record and is not included in the data definition. In general, variable length records are used when there are many small records and few large records. Variable length records must be converted before they can be imported.

• Relative Files: Relative files can be accessed randomly in the order they are written. Records can be declared as variable, but they are written as fixed. The random access is not by key, but is instead by relative record number. Relative files must be converted to sequential before they can be imported.

• Indexed Files: Indexed files can be accessed by key field(s) in the order they are written. Records in indexed files can be fixed or variable. Indexed files consist of two physical files, one containing the data and the other containing the index. Indexed files are converted to sequential before they are imported.

The file format used for data storage is determined by the COBOL application. This information is not defined in the copybook.

Import Cobol Session Options

You can use this dialog box to specify a template file and other properties. Based on the copybook you are importing, determine if the default physical file properties are acceptable. Or else, select a template file from which the physical characteristics are imported. You can edit some physical properties of the file after the import as well.

To specify a template file, select a file from the Copy Flat File Properties From list.

If you retain the Default Properties option from the Copy Flat File Properties From list, the file is defined as fixed format (not delimited) with the following properties:

• Skip 0 records

• Use first row as column name set to false

• Record delimiter set to \n

• One physical record for each logical record

If you import a multi-record copybook, then it is set to multi record.

If you select a file from the Copy Flat File Properties From list, then the physical characteristics of this file are used for the newly imported copybook.

Do Not Import Groups: To suppress the definition of group items. This ensures that only the subordinate items get imported. For example, if you have the following fields:

05  EMP-HIRE-DATE.
    10 EMP-HIRE-MONTH PIC 99.
    10 EMP-HIRE-DAY PIC 99.
    10 EMP-HIRE-YEAR PIC 9999.

When you select the Do Not Import Groups option, only three fields are created, EMP-HIRE-MONTH, EMP-HIRE-DAY, and EMP-HIRE-YEAR. When the option is not selected, then four fields, EMP-HIRE-DATE, EMP-HIRE-MONTH, EMP-HIRE-DAY, EMP-HIRE-YEAR are created at the time of import.

Do Not Import Redefines: To avoid import of redundant redefined fields. For example, if you have the following copybook definition:

05 HIRE_DATE.
10 HIRE_MONTH PIC 99.
10 HIRE_DAY PIC 99.
10 HIRE_YEAR PIC 9999.
05 HIRE_DATE_ALPHA REDEFINES HIRE_DATE.
10 HIRE_MONTH PIC XX.
10 HIRE_DAY PIC XX.
10 HIRE_YEAR PIC XXXX.

To import all the fields, clear the Do Not Import Redefinitions option. When the option is selected, only the first four fields, HIRE_DATE, HIRE_MONTH, HIRE_DAY, HIRE_YEAR are imported.

At the time of importing the metadata, it is recommended that you inspect the copybook for unnecessary metadata including grouped fields and redefined fields. You can also edit the file later to remove unnecessary definitions.

Importing The COBOL Copybook

Import the copybook as described in "Importing a Copybook".

To set the file properties of the imported copybook, right-click the file on the Projects Navigator and click Open. On the Edit Flat File dialog box, define the file properties as given in "Defining the File Properties".

Defining the File Properties

Use the Name, General, and Structure tabs to specify the file properties.

General Tab

Use the General tab to set the following file properties.

• Record Delimiter: The record delimiter for this file must be set to the binary value X'0D0A'.

• Logical Record Definition: In this example, there is one logical record per physical record.

• Field Format: Each field in this record is at a constant position. Therefore, the Fixed Length Fields option is selected by default.

• Record Type: This copybook contains a single type of record. Therefore, the Single Record option is selected by default.

The field properties are as shown in Figure 3-5.

Figure 3-5 The Field Properties of the Imported Cobol File

Description of "Figure 3-5 The Field Properties of the Imported Cobol File"

Structure Tab

Use the Structure tab to review the field properties and make changes if required.

The structure of the imported file is as shown in Figure 3-6.

Figure 3-6 Structure of the Imported Cobol File

Description of "Figure 3-6 Structure of the Imported Cobol File"

Creating a Mapping to Load Data

Create a mapping with the imported COBOL file as the source. Insert an unbound table operator in the mapping and map the desired fields from the COBOL file to the table operator.

Figure 3-7 Mapping a Flat File to a Table

Description of "Figure 3-7 Mapping a Flat File to a Table"

Configuring The Mapping

Right-click the mapping and click Configure to open the Configuration Properties dialog box. Select SQL*LOADER as the language for code generation. Tune any of the other SQL*Loader settings.

Figure 3-8 Configuration Properties Dialog Box

Description of "Figure 3-8 Configuration Properties Dialog Box"

Specifying the Data File

Click Sql Loader Data Files and then click the ellipsis as shown in Figure 3-9 to open the SQL Loader Data Files dialog box.

Figure 3-9 Configuration Properties for the Mapping

Description of "Figure 3-9 Configuration Properties for the Mapping"

On the SQL Loader Data Files dialog box, right-click SQL Loader Data Files and select Create. A new data file node is added under Sql Loader Data Files.

Provide the data file name and select the data file location from where data is to be loaded. Specify a bad file to store those records that are not loaded into the target table due to error in the data. Specify a discard file to store those records that are not loaded due to SQL*Loader loading checks.

Figure 3-10 SQL Loader Data Files Dialog Box

Description of "Figure 3-10 SQL Loader Data Files Dialog Box"

Executing the Mapping

After you have defined the configuration properties for the mapping, you can deploy the table and the mapping and then start the mapping to load the COBOL data into the target table.

To deploy the mapping, right-click the mapping and select Deploy. After deploying the mapping, start the mapping. Right-click the mapping and select Start. When you run the mapping, the data is read from the data file you provided while configuring the mapping, and loaded into the target table.

To view the data in the target table, right-click the table, and select Data. Figure 3-11 shows the data in the table.

Figure 3-11 Data in the Target Table

Description of "Figure 3-11 Data in the Target Table"

Name Page

Use the Name page to define a name and an optional description for the external table. Enter the name in the Name field. In the physical naming mode, you must enter a name between 1 and 200 valid characters. Spaces are not allowed in physical mode. In the logical mode, you can enter a unique name up to 4000 characters in length. The external table name must be unique within the module. Spaces are allowed in the logical naming mode.

Use the Description field to enter an optional description for the external table.

File Selection Page

The wizard displays the File Selection page. The wizard lists all the flat files available in the workspace. Select a file on which to base the external table. To search through long lists of files, type the first few letters of the file name and click Go.

If you select a file that contains multiple record types, you must also select the record type name at the bottom of the File Selection page. An external table can represent only one record type.

You have the option of not specifying the file at this stage. If you do not specify a file in the wizard, you can later specify information such as record type, access parameters, and data files on the external table properties sheet.

Locations Page

You can select a location from the list of flat file locations. Alternatively, you can leave the location unspecified. If you do not specify a location in the wizard, you can later specify a location on the external table properties sheet.

Name Tab

Use the Name tab to rename the external table. The same rules for renaming tables apply to external tables. For more information, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Columns Tab

Use the Columns tab to add or edit columns. The same rules for adding columns to tables apply to external tables. For more information, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

File Tab

Use the File tab to view the name of the flat file that provides the metadata for the external table. If the source flat file has multiple record types, the File tab also displays the record name associated with the external table. You can update this relationship or change it to a different file and record by reconciling the external table. For more information, see "Synchronizing an External Table Definition with a Record in a File".

The File tab displays under the following conditions:

• You used the New External Table Wizard to create the external table and you specified a file name.

• You did not specify a file name in the New External Table Wizard, but you reconciled the external table definition with a file and record.

Locations Tab

Use the Location tab to view or change the flat file location. The Location list displays the available locations. Select a location from this list.

Data Rules Tab

Use the Data Rules tab to define data rules for the external table. For more information about using data rules, see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Access Parameters Tab

Access parameters define how to read from the flat file when a file has not been specified for the external table. In some cases, the External Table editor displays the Access Parameters tab instead of the File tab.

The tab for the access parameters displays under the following conditions:

• You imported an external table from another workspace. In this case, you can view and edit the access parameters.

• You created an external table in an Oracle Database and imported its definition. In this case, you can view and edit the access parameters.

• You use the Create External Table Wizard to create an external table and do not specify a reference file. The access parameters are empty. Before generating the external table, you must reconcile the external table definition with a flat file record or manually enter your own access specifications.

The access parameters describe how fields in the source data file are represented in the external table as columns. For example, if the data file contained a field emp_id with a data type of INTEGER(2), the access parameters could indicate that the field be converted to a character string column in the external table.

Although you can make changes to the access parameters that affect how the external table is generated and deployed, it is not recommended. Oracle Warehouse Builder does not validate the changes. For more information about external tables and the access parameters, see Oracle Database Utilities.

Access Specification

Under Access Specification, you can indicate the following file names and locations that Oracle Warehouse Builder uses to load the external table through SQL*Loader.

• Bad File: If you specify a name and location for a bad file, Oracle Database is directed to write to that file all records that were not loaded due to errors. For example, records written to the bad file include those not loaded due to a data type error in converting a field into a column in the external table. If you specify a bad file that exists, the existing file is overwritten.

• Discard File: If you specify a name and location for a discard file, Oracle Database is directed to write to that file all records that were not loaded based on a SQL*Loader load condition placed on the file. If you specify a discard file that exists, the existing file is overwritten.

• Log File: If you specify a name and location for a log file, then Oracle Database is directed to log messages related to the external table to that file. If you specify a log file that exists, new messages are appended.

For each of these files, you can either specify a file name and location, select Do not use, or select Use default location.

Reject

Under Reject, you can indicate how many rejected rows to allow. By default, the number of rejected rows allowed is unlimited. If you set Rejects are unlimited to false, enter a number in Number of rejects allowed.

Parallel

Parallel: Enables parallel processing. If you are using a single system, set the value to NONPARALLEL to improve performance. If you are using multiple systems, accept the default PARALLEL.The access driver attempts to divide data files into chunks that can be processed separately. The following file, record, and data characteristics make it impossible for a file to be processed in parallel:

• Sequential data sources (such as a tape drive or pipe).

• Data in any multibyte character set whose character boundaries cannot be determined starting at an arbitrary byte in the middle of a string. This restriction does not apply to any data file with a fixed number of bytes for each record.

• Records with the VAR format

Data Characteristics

If you imported the external table into the workspace or created the external table without specifying a source file, do not configure these properties. Data characteristics properties are overruled by settings on the Access Parameters tab in the External Table Properties window.

Under Data Characteristics you can set the following properties:

• Endian: The default for the Endian property is Platform. This indicates that it is assumed that the endian of the flat file matches the endian of the platform on which it resides. If the file resides on a Windows platform, the data is handled as little-endian data. If the file resides on Sun Solaris or IBM MVS, the data is handled as big-endian. If you know the endian value for the flat file, you can select big or little-endian. If the file is UTF16 and contains a mark at the beginning of the file indicating the endian, then that endian is used.

• String Sizes in: This property indicates how data with multibyte character sets, such as UTF16, is handled. By default, the lengths for character strings in the data file are assumed to be in bytes. You can change the selection to indicate that strings sizes are specified in characters.

Field Editing

If you imported the external table into the workspace or created the external table without specifying a source file, do not configure these properties. Field editing properties are overruled by settings on the Access Parameters tab in the External Table Properties window.

Under Field Editing, you can indicate the type of whitespace trimming to be performed on character fields in the data file. The default setting is to perform no trim. All other trim options can reduce performance. You can also set the trim option to trim blanks to the left, right, or both sides of a character field.

Another option is to set the trim to perform according to SQL*Loader trim function. If you select SQL*Loader trim, fixed-length files are right trimmed and delimited files specified to have enclosures are left trimmed only when a field is missing an enclosure.

You can indicate how to handle missing fields in a record. If you set the option Trim Missing Values Null to true, fields with missing values are set to NULL. If you set the property to false, fields with missing values are rejected and written into the specified bad file.

Identification

See Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for details.

Data Files

If a file is associated with an external table, and it has a file name specified, that name is used. The user can configure if a different file must be specified or if multiple files must be specified.

To add a data file:

1. Right-click the Data Files node and select Create.

   Enter a name for the data file such as DATAFILE1. Your entry displays as a new node in the right panel of the Configuration Properties dialog box.

2. Enter the following values for each data file you define:

   Data File Location: Location for the flat file.

   Data File Name: The name of the flat file including its extension. For example, enter myflatfile.dat.