4. Manage CFI Loader Columns

Before adding column definitions to a CFI loader, make sure that the loader exists and all BDomain metadata have been set:

4.1. Add a CFI Loader Column

For each column of the source file, a column definition for the target TSA table must be added using API_CFIADM.ADD_LOADER_BD_COLUMN. Besides name and data type, the position of the column in the file (p_col_pos) is essential.

The parameters depend on the data type:

DATA TYPE

REQUIRED PARAMETERS

INT

none besides name, data type, and position

VARCHAR

p_col_size (number of characters)

DECIMAL

p_col_size (precision), p_col_scale (decimal places), and p_col_format (numeric format model)

DATE

p_col_format (date format model)

The FBV parameters (p_col_fbv_size, p_col_fbv_start, p_col_fbv_align, p_col_fbv_padding) are only used for fixed-width files and are NULL for CSV files.

The following examples define the five columns of the sample CSV file.

Example: INT Column

 1EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD_COLUMN(
 2    'PERSON'    -- p_ldr_name
 3   ,'TESTING'   -- p_bdomain
 4   ,'ID'        -- p_col_name
 5   ,'int'       -- p_col_data_type
 6   ,null        -- p_col_size
 7   ,null        -- p_col_scale
 8   ,null        -- p_col_format
 9   ,1           -- p_col_pos
10   ,null        -- p_col_fbv_size
11   ,null        -- p_col_fbv_start
12   ,null        -- p_col_fbv_align
13   ,null        -- p_col_fbv_padding
14);

Example: VARCHAR Columns

 1EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD_COLUMN(
 2    'PERSON'        -- p_ldr_name
 3   ,'TESTING'       -- p_bdomain
 4   ,'FirstName'     -- p_col_name
 5   ,'varchar'       -- p_col_data_type
 6   ,100             -- p_col_size
 7   ,null            -- p_col_scale
 8   ,null            -- p_col_format
 9   ,2               -- p_col_pos
10   ,null            -- p_col_fbv_size
11   ,null            -- p_col_fbv_start
12   ,null            -- p_col_fbv_align
13   ,null            -- p_col_fbv_padding
14);
15
16EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD_COLUMN(
17    'PERSON'        -- p_ldr_name
18   ,'TESTING'       -- p_bdomain
19   ,'LastName'      -- p_col_name
20   ,'varchar'       -- p_col_data_type
21   ,100             -- p_col_size
22   ,null            -- p_col_scale
23   ,null            -- p_col_format
24   ,3               -- p_col_pos
25   ,null            -- p_col_fbv_size
26   ,null            -- p_col_fbv_start
27   ,null            -- p_col_fbv_align
28   ,null            -- p_col_fbv_padding
29);

Example: DECIMAL Column

 1EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD_COLUMN(
 2    'PERSON'         -- p_ldr_name
 3   ,'TESTING'        -- p_bdomain
 4   ,'Salary'         -- p_col_name
 5   ,'decimal'        -- p_col_data_type
 6   ,8                -- p_col_size
 7   ,2                -- p_col_scale
 8   ,'999G999D99'     -- p_col_format
 9   ,4                -- p_col_pos
10   ,null             -- p_col_fbv_size
11   ,null             -- p_col_fbv_start
12   ,null             -- p_col_fbv_align
13   ,null             -- p_col_fbv_padding
14);

The format characters G (group separator) and D (decimal separator) are interpreted according to p_numeric_characters of the BDomain loader (‘,.’ in this example).

Example: DATE Column

 1EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD_COLUMN(
 2    'PERSON'         -- p_ldr_name
 3   ,'TESTING'        -- p_bdomain
 4   ,'BirthDate'      -- p_col_name
 5   ,'date'           -- p_col_data_type
 6   ,null             -- p_col_size
 7   ,null             -- p_col_scale
 8   ,'YYYYMMDD'       -- p_col_format
 9   ,5                -- p_col_pos
10   ,null             -- p_col_fbv_size
11   ,null             -- p_col_fbv_start
12   ,null             -- p_col_fbv_align
13   ,null             -- p_col_fbv_padding
14);

These commands create the following entries in CFIADM.CFI_LOADER_BD_COLUMNS:

LDR_NAME

BDOMAIN

COL_NAME

COL_DATA_TYPE

COL_SIZE

COL_SCALE

COL_FORMAT

COL_POS

PERSON

TESTING

ID

int

(null)

(null)

(null)

1

PERSON

TESTING

FirstName

varchar

100

(null)

(null)

2

PERSON

TESTING

LastName

varchar

100

(null)

(null)

3

PERSON

TESTING

Salary

decimal

8

2

999G999D99

4

PERSON

TESTING

BirthDate

date

(null)

(null)

YYYYMMDD

5

4.2. Modify a CFI Loader Column

Modify a loader column using API_CFIADM.MODIFY_LOADER_BD_COLUMN. NULL leaves a parameter unchanged. In the following example the column LastName is enlarged to 200 characters.

Example: Enlarge a VARCHAR Column

 1EXECUTE SCRIPT API_CFIADM.MODIFY_LOADER_BD_COLUMN(
 2    'PERSON'         -- p_ldr_name
 3   ,'TESTING'        -- p_bdomain
 4   ,'LastName'       -- p_col_name
 5   ,null             -- p_col_data_type
 6   ,200              -- p_col_size
 7   ,null             -- p_col_scale
 8   ,null             -- p_col_format
 9   ,null             -- p_col_pos
10   ,null             -- p_col_fbv_size
11   ,null             -- p_col_fbv_start
12   ,null             -- p_col_fbv_align
13   ,null             -- p_col_fbv_padding
14);

The entry of column LastName in CFIADM.CFI_LOADER_BD_COLUMNS has changed:

LDR_NAME

BDOMAIN

COL_NAME

COL_DATA_TYPE

COL_SIZE

COL_SCALE

COL_FORMAT

COL_POS

PERSON

TESTING

ID

int

(null)

(null)

(null)

1

PERSON

TESTING

FirstName

varchar

100

(null)

(null)

2

PERSON

TESTING

LastName

varchar

200

(null)

(null)

3

PERSON

TESTING

Salary

decimal

8

2

999G999D99

4

PERSON

TESTING

BirthDate

date

(null)

(null)

YYYYMMDD

5

Important

If the TSA table already exists, apply column changes to it with API_CFIADM.REFRESH_LOADER. With p_force false the script only shows the DDL statements; with true it executes them.

4.3. Remove a CFI Loader Column

To remove a CFI loader column, use API_CFIADM.REMOVE_LOADER_BD_COLUMN.

Example: Remove the Column ‘ID’

1EXECUTE SCRIPT API_CFIADM.REMOVE_LOADER_BD_COLUMN(
2    'PERSON'    -- p_ldr_name
3   ,'TESTING'   -- p_bdomain
4   ,'ID'        -- p_col_name
5);

The entry of column ID is removed from CFIADM.CFI_LOADER_BD_COLUMNS.