Handling Flat File and COBOL Copybook Sources in Mappings

This tutorial shows you how to use flat files and COBOL Copybook sources in a mapping. In this tutorial, you also learn how to use a flat file as a target.

Approximately 1 hour

Topics

.This tutorial covers the following topics:

Importing the COBOL Copybook Source file

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

Overview

Extract, transform, load (ETL) involves the movement and transformation of data from your sources to your targets. Warehouse Builder mappings can be designed to specify which source data objects provide data to which target data objects.

In this tutorial, you practice mapping a multi-record flat file to two staging tables.

The focus of this tutorial is to illustrate how you can use flat files as a source or a target in your mappings. The sampling of the flat files are already done for you. Moreover, you will learn how you can use the COBOL copybook sources.

Back to Topic List

Before starting this tutorial, you should:

1.

Have completed the tutorial, Setting Up the Oracle Warehouse Builder 11g Release 2 Tutorial Environment

The Setting Up the Oracle Warehouse Builder 11g Release 2 Environment tutorial provides the setup files to configure and setup an environment for this tutorial.

2.

Have downloaded the labs.zip and saved it at a location on your Linux file system. Extract the .zip file to create a labs_files folder.

This folder contains the flat files and COBOL Copybook files required in this tutorial.

Back to Topic List

Using a Flat File Source in a Mapping

In this topic you map a source multi-record flat file to two staging tables. You add a new attribute to a target table that is loaded by one of the attributes of the data generator operator.

Examine the File Definition
Use a Multi-record Flat File
Configure Properties and Generate Code

Back to Topic List

To examine the file definition, perform the following steps:

1.

To log into the Warehouse Builder Design Center, double-click the OWB Design Center desktop launcher.

In the Design Center logon dialog box, enter etl_owner/etl_owner as the username/password.

If this is the first time you are logging in, enter the Connection details as localhost for host field, 1521 for port, and the service name. Click OK.

Expand ETL_Project > Files. Expand SRC_FLAT_FILES module. Right-click FILE_GEOGRAPHY_MULTI_CSV and select Open. The Flat File Editor opens.

 

2.

Click Record to examine the record structure of the file. Note that this is a multi-record file with two types of record: REGION_REC and COUNTRY_REC.

 

3.

To view the structure of each record type, click Structure. The REGION_REC record has four fields as shown in the screenshot below. From the Record Name drop down list, select COUNTRY_REC to view its fields. Click OK to exit the editor.

 

Back to Topic

To use a multi-record flat file as a source in a mapping, perform the following steps:

1.

Expand Databases > Oracle > TRGT_STAGING. Right-click Mappings and select New Mapping. The Create Mapping window opens. Enter Map_Stg_File_Geography as name of the mapping. Click OK.

 

2.

The Mapping Editor canvas opens.

3.

Expand Files node. Drag FILE_GEOGRAPHY_MULTI_CSV to the Mapping Editor canvas. Click OK.

The selected flat file is added on the canvas. In the following steps, you map the multi-record flat file to two staging tables: Stg_Countries_Rec and Stg_Regions_Rec.

 

4.

From the Component Palette, drag a table operator on the canvas. The Add Table Operator window opens. Select the Create unbound operator with no attributes option. Name the table Stg_Countries_Rec. Click OK.

Note: This table is unbound (no corresponding repository table) and it is without attributes.

 

5.

Select the following source attributes from the flat file’s Country_Rec group and drag them into the INOUTGRP1 of the Stg_Countries_Rec mapping table.

Note: Click the icon on operator's title bar to maximize the operator. Hold [SHIFT] and click on the attributes to select them.

Subregion_Number
Country_Number
Country_Code2
Country_Code3
Country_Name
Currency_Code
Currency_Name

 

6.

As mentioned the Stg_Countries_Rec table does not exist yet. To create the table, right-click the operator and select Create and Bind.

The Create and Bind window opens. Make sure the table is created in /MY_WORKSPACE2/ETL_PROJECT/TRGT_STAGING/Tables. Click OK.

 

7.

Add a new attribute, LOAD_DATE in the Stg_Countries_Rec table. Double-click Stg_Countries_Rec.

In the table operator editor, click Input/Output. At the bottom, in the Attribute field, enter LOAD_DATE and set the Data type drop down list to DATE. Click OK.

 

8.

You added the Load_Date attribute to the mapping table after you performed "Create and Bind" to create the table definition.

Therefore, this new attribute does not exist in the table definition yet. To verify this, in the Projects Navigator panel, in TRGT_STAGING, expand Tables. Double-click STG_COUNTRIES_REC to open it in a new panel. Click the Columns tab.

Note that LOAD_DATE is not added yet. To get the new attribute added in the table definition, you should synchronize the table operator in the mapping with the table definition in the TRGT_STAGING module.

Close the STG_COUNTRIES_REC table panel.

 

9.

In the mapping canvas, right-click Stg_Countries_Rec and select Synchronize.

Now you select the direction of synchronization. You should synchronize the table operator, STG_COUNTRIES_REC with the table, /ETL_PROJECT/TRGT_STAGING/STG_COUNTRIES_REC.

Therefore, select the Outbound option. Click OK.

 

10.

In the Projects Navigator panel, in the TRGT_STAGING > Tables node, double-click STG_COUNTRIES_REC to open it in a panel. Click the Columns tab.

Note that LOAD_DATE is now added.

 

11.

Switch back to the mapping canvas. As mentioned in Step 4, from the Component Palette, add a second table operator, Stg_Regions_Rec.

Note: This table is unbound (no corresponding repository table) and it is without any attributes.

Select the following source attributes from the Region_Rec group and drag it into the INOUTGRP1 of the Stg_Regions_Rec table operator:

Region_Name
Subregion_Name
Subregion_Number


 

12.

This time you can avoid the task of synchronizing by adding the LOAD_DATE attribute before selecting "Create and Bind".

Add a new attribute, LOAD_DATE in the Stg_Regions_Rec table. In the mapping canvas, double-click Stg_Regions_Rec. In the table operator editor, click Input/Output.

In the Attribute field, enter LOAD_DATE and set the Data type drop down list to DATE. Click OK.

 

13.

As mentioned the Stg_Regions_Rec table does not exist yet.

To create the table, right-click Stg_Regions_Rec operator. From the menu, select Create and Bind.

The Create and Bind window opens. Make sure the table is created in /MY_WORKSPACE2/ETL_PROJECT/TRGT_STAGING/Tables. Click OK.

 

Back to Topic

To add the data generator for loading the Load_ date column, perform the following steps:

1.

From the Component Palette, drag a Data Generator operator to the canvas.

Note: The Data Generator operator provides information such as record number, system date, and sequence values.

 

2.

Map the Data Generator’s SYSDATE1 attribute to the Load_Date attribute of the Stg_Countries_Rec mapping table.

Also map SYSDATE1 to the Load_Date attribute of the Stg_Regions_Rec table.

Click Save All () to save your mapping. Click Yes in the Save Confirmation dialog box.

 

Back to Topic

To configure properties and generate code, perform the following steps:

1.

Examine some properties related to a flat file source in a mapping. You examine the properties of the selected object/operator in the Property Inspector panel.

Note: If you don't see the property inspector panel, in the View menu, select Property Inspector to get it.

You can specify a physical file name for the source flat file. The physical file name is automatically populated with the name of the file you sampled. However, if it is created in any other way, you can specify it explicitly.

Click on FILE_GEOGRAPHY_MULTI_CSV to highlight it in the mapping.

In the property inspector panel, note that because this file was sampled, the default physical file name property is by default set to the name of the flat file that was sampled.

Note, the source data file location is also set here.

 

2.

Before you deploy the mapping, you can validate it and also generate the code.

To validate the mapping, click Validate () on the toolbar.

Note: You get a warning that should be ignored if you want to use the default source filename and location.

To view the code, click on Generate () on the toolbar. The Generation Results panel gets added at the bottom along the Results-Log panel.

Note: You will not actually deploy the mappings in this tutorial. This tutorial focuses only on the design metadata level and does not have the back-end setup to support actual deployment of database objects and execution of mappings.

Close the MAP_STG_FILE_GEOGRAPHY panel.

Back to Topic

Back to Topic List

Using a Flat File as a Target

You can use a Flat File operator as a target in a mapping to load data into a flat file. A mapping with a flat file target generates a PL/SQL package that loads data into a flat file instead of loading data into rows in a table. You can use one of the following methods to create a Flat File target operator:

Import an existing flat file definition into the repository and use this flat file as a target in a mapping
Define a flat file using the Create Flat File Wizard and use this as a target in the mapping
Create a new flat file using the Flat File operator in the mapping

 

Back to Topic List

1.

Expand TRGT_STAGING. Right-click Mappings and select New Mapping. Enter MAP_FILE_TGT as the name of the mapping. Click OK.

 

2.

Suppose you want to load the data in the Stg_Countries_Rec table into a new flat file.

In the Projects Navigator panel, expand TRGT_STAGING > Tables. Drag Stg_Countries_Rec table to the mapping canvas.


3.

From the Component Palette, drag a Flat File operator.

In the Add Flat File Operator dialog box, select Create unbound operator with no attributes. In the name field, enter File_Countries. Click OK.

 

4.

Suppose you want to load only the COUNTRY_NAME, CURRENCY_NAME, and the LOAD_DATE values in the target flat file.

Map the three columns from the Stg_Countries_Rec table into the INOUTGRP1 of the File_Countries operator.

 

5.

The file, File_Countries, is not created yet. To create the file, right-click FILE_COUNTRIES and select Create and Bind.

In the Create and Bind dialog box, select the flat file module, SRC_FLAT_FILES module, as the location where you want to create the file. Click OK.

 

6.

In the Projects Navigator panel, expand FILES > SRC_FLAT_FILES. Double-click File_Countries.

In the Edit Flat File window, click Structure to view the file structure. Click OK to close the window.

Click Save All to save your work. Click Yes in the Save Confirmation dialog box.

 

7.

You need to specify the target data file location and target data file name.

In the Projects Navigator panel, right-click MAP_FILE_TGT and select Configure.

In the Configuration panel, expand Flat File Operators. Expand FILE_COUNTRIES > Access Specification.

Make sure that the Target Data File Location is set to the flat file location, SRC_FLAT_FILES_LOCATION.

Set the Target Data File name to File_Countries.csv (including the file extension). Click the ellipses besides the Target Data File Name field. Enter File_Countries.csv in the dialog box and click OK.

Click Save All to save your work. Click Yes in the Save Confirmation dialog box.

 

 

Back to Topic List

Importing the COBOL Copybook Source file

OWB 11g R2 extends the support for COBOL data structures by using copybooks for importing metadata.

When you have a COBOL copybook available, you can use it to easily define files using the new import COBOL wizard. The wizard allows you to create a flat file and capture the record and field metadata contained in the COBOL copybook. Session options are provided to allow you to override default file properties, limit the extent of the import and control error processing. After you have created the file, make sure you review the properties to ensure the definition is correct and complete. The file can be used as any other file.

Back to Topic List

To import a source COBOL Copybook, perform the following steps:

1.

Create a new Flat file module in ETL_PROJECT.

Right-click Files and select New Flat File Module. The Create Module wizard opens. Click Next on the Welcome Page. Enter cbl_files in the Name field. Click Next.

 

2.

In the Connection Information page, create a new location, cbl_files_location, pointing to the directory that has the copybook.

Click Edit. In the Edit File System Location dialog box, enter cbl_files_location as the name. Click Browse to select the folder that has the two copybook files.

In the Choose Directory dialog box, browse to the location you extracted the labs.zip in. Select the .../lab_files folder. Click Select. Click OK.

Click OK in the Edit File System Location dialog box. Click Finish. Click Save All to save your work. Click Yes in the Save Confirmation dialog box.


3.

In the Projects Navigator panel, right-click CBL_FILES and select Import > Cobol. The COBOL Import window opens.

 

4.

In the COBOL Import window, click Add Copybook.

In the Open dialog box, select fsr_ebcdic_us.cpy file. Click Open.

 

5.

Note the row that is added in the Import Copybook table. Note that the File column identifies the name of the file that is created. You can also add a meaningful description.

To view the copybook, click View Copybook. The COBOL data structure details are shown. Close the window.

Note: Notice the copybook identifies a record named EMPLOYEE-RECORD. It also has 8 elementary items specified at the 05 level, followed by an array named EMP-SKILL. The COBOL importer will create a record named EMPLOYEE-RECORD. It will create a field for each of the first 8 items.

The last 05 level item is group for the array. The size of the field created for this item will include the size of all of the elements in the array. It will also generate fields for each occurrence of the items in the array, therefore it will generate 8 fields, 4 for EMP-SKILL-LEVEL and 4 for EMP-SKILL-ID.

 

6.

Click Session Options to examine some of the advanced import options.

In the Import COBOL Session Options dialog box, examine the various advanced import options. Click OK.

 

7.

Click Import. The COBOL - Log panel shows the import details. In the COBOL -Log panel, select FSR_EBCDIC_US and click on the horizontal arrow, highlighted in the screenshot below.

 

8.

In the Edit Flat File window, enter FSR_DEP_EBCDIC.dat as the Default Physical Filename.

Also, you can change the character set to WE8EBCDIC500.


Note:The COBOL copybook does not contain the physical characteristics of the file, therefore you should review the file properties and set them appropriately. For example, you may need to set a different character set or record delimiter.

Click Structure to view the imported fields and their data types.

Note: The EMP_SKILL field was generated for the array with a length of 6. The fields generated following the EMP_SKILL field define the elements of the array.

Click OK. From File menu, select Exit to exit the Design Center. Click Yes in the Exit Confirmation dialog box.

Importing a COBOL copybook creates a flat file. It can be used as reference for an external table and it can mapped as a source or target file in a mapping.

Now that you have the file and the definition you can either use external tables or SQL Loader to work on the data.

 

Back to Topic List

Summary

In this lesson, you learned how to:

Back to Topic List

Place the cursor over this icon to hide all screenshots.