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.

• Add a BDomain

• [Add CFI BDomain Settings](#cfi-add-bdomain-settings

• Add a CFI Loader

• Add BDomain specific metadata to a Loader

4.1. Add a CFI Loader Column

For each column of the source file a specification for the target TSA-table must be added using API_CFIADM.ADD_LOADER_BD_COLUMN. Mind that not only the name and data type are important parameteres but also the position of the column in the file.

For VARCHAR columns the size must be provided. The rest of the parameters can be set to NULL for INT and VARCHAR columns.

The variable p_col_scale can be used to define the number of decimal positions that should be used for columns of the type DECIMAL. For this column type the column size also has to be set and the format of the column has to be defined (see Exasol Documentation). The rest of the variables can be set to null for this column type as well.

For columns of the type DATE only the column format has to be defined additionally to the variables having to be provided for all other column types as well.

The examples below show a definition for each column type.

Example - column type = ‘int’

EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD_COLUMN(
   'PERSON'     -- p_ldr_name
   ,'TESTING'   -- p_bdomain
   ,'id'        -- p_col_name
   ,'int'       -- p_col_data_type
   ,null        -- p_col_size
   ,null        -- p_col_scale
   ,null        -- p_col_format
   ,1           -- p_col_pos
   ,null        -- p_col_fbv_size
   ,null        -- p_col_fbv_start
   ,null        -- p_col_fbv_align
   ,null        -- p_col_fbv_padding
);

Example - column type = ‘varchar’

EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD_COLUMN(
   'PERSON'         -- p_ldr_name
   ,'TESTING'       -- p_bdomain
   ,'FirstName'     -- p_col_name
   ,'varchar'       -- p_col_data_type
   ,100             -- p_col_size
   ,null            -- p_col_scale
   ,null            -- p_col_format
   ,2               -- p_col_pos
   ,null            -- p_col_fbv_size
   ,null            -- p_col_fbv_start
   ,null            -- p_col_fbv_align
   ,null            -- p_col_fbv_padding
);

EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD_COLUMN(
   'PERSON'         -- p_ldr_name
   ,'TESTING'       -- p_bdomain
   ,'LastName'      -- p_col_name
   ,'varchar'       -- p_col_data_type
   ,100             -- p_col_size
   ,null            -- p_col_scale
   ,null            -- p_col_format
   ,3               -- p_col_pos
   ,null            -- p_col_fbv_size
   ,null            -- p_col_fbv_start
   ,null            -- p_col_fbv_align
   ,null            -- p_col_fbv_padding
);

Example - column type = ‘decimal’

EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD_COLUMN(
   'PERSON'          -- p_ldr_name
   ,'TESTING'        -- p_bdomain
   ,'Salary'         -- p_col_name
   ,'decimal'        -- p_col_data_type
   ,8                -- p_col_size
   ,2                -- p_col_scale
   ,'999G999D99'     -- p_col_format
   ,4                -- p_col_pos
   ,null             -- p_col_fbv_size
   ,null             -- p_col_fbv_start
   ,null             -- p_col_fbv_align
   ,null             -- p_col_fbv_padding
);

Example - column type = ‘date’

EXECUTE SCRIPT API_CFIADM.ADD_LOADER_BD_COLUMN(
   'PERSON'          -- p_ldr_name
   ,'TESTING'        -- p_bdomain
   ,'Birthday'       -- p_col_name
   ,'date'           -- p_col_data_type
   ,null             -- p_col_size
   ,null             -- p_col_scale
   ,null             -- p_col_format
   ,5                -- p_col_pos
   ,null             -- p_col_fbv_size
   ,null             -- p_col_fbv_start
   ,null             -- p_col_fbv_align
   ,null             -- p_col_fbv_padding
);

Issuing the commands for the different column types above results in the following entries in the table CFI_LOADER_BD_COLUMNS.

LDR_NAME	BDOMAIN	COL_NAME	COL_DATA	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	Birthday	date	(null)	(null)	(null)	5

4.2. Modify a CFI Loader Column

Modify a loader column using API_CFIADM.MODIFY_LOADER_BD_COLUMN

In the example below the column containing the Birthday is being modified. A speciic date format is added.

Example

EXECUTE SCRIPT API_CFIADM.MODIFY_LOADER_BD_COLUMN(
   'PERSON'          -- p_ldr_name
   ,'TESTING'        -- p_bdomain
   ,'Birthday'       -- p_col_name
   ,'date'           -- p_col_data_type
   ,null             -- p_col_size
   ,null             -- p_col_scale
   ,'YYYY-MM-DD'     -- p_col_format
   ,5                -- p_col_pos
   ,null             -- p_col_fbv_size
   ,null             -- p_col_fbv_start
   ,null             -- p_col_fbv_align
   ,null             -- p_col_fbv_padding
);

The command above results in a change in the entry of the column ‘Birthday’ in the table CFI_LOADER_BD_COLUMNS, as can be seen below.

LDR_NAME	BDOMAIN	COL_NAME	COL_DATA	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	Birthday	date	(null)	(null)	YYYY-MM-DD	5

4.3. Remove a CFI Loader Column

To remove a CFI loader column use API_CFIADM.REMOVE_LOADER_BD_COLUMN.

Example

EXECUTE SCRIPT API_CFIADM.REMOVE_LOADER_BD_COLUMN (
   'PERSON'           -- p_ldr_name
   ,'TESTING'         -- p_bdomain
   ,'id'              -- p_col_name
);

If the command above is issued for each column name, no entries for the loader ‘PERSON’ with the bdomain ‘TESTING’ should remain in the table CFI_LOADER_BD_COLUMNS.