TV 3.7.0
Before a column can be added to a CFI bdomain loader, a bdomain with corresponding bdomain settings and a loader have to exist. Furthermore, a bdomain loader has to be added as well. This prerequisites can be created by following the first chapter of each of the following HowTo Guides:
¶ 1. Add a CFI Column Loader
A column loader has to be added for each column of the source file to define how the TSA table, the data should be loaded into, should look like. This step will create all necessary metadata for the next step. For each column the name of the loader and bdomain as well as the name and data type of each column have to be provided to the script API_CFIADM.ADD_LOADER_BD_COLUMN. The only other mandatory variable for all column types is the position of the column. For 'varchar' columns the only other variable that has to be provided is the size of the column. The rest of the variables 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. For defining a decimal colum format the number 9 is used to represent the number places while a D represents the decimal separator and a G the thousand separator. 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
);
¶ 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
,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 = '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
,4 -- 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 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 | Salary | decimal | 8 | 2 | 999G999D99 | 3 |
| PERSON | TESTING | FirstName | varchar | 100 | (null) | (null) | 2 |
| PERSON | TESTING | Birthday | date | (null) | (null) | (null) | 4 |
¶ 2. Modify a CFI Column Loader
If one of the column parameters of a column loader should be modified the script API_CFIADM.MODIFY_LOADER_BD_COLUMN can be used. 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
,4 -- 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 CFIADM.CFI_LOADER_BD_COLUMNS, as can be seen below.
| 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 | Salary | decimal | 8 | 2 | 999G999D99 | 3 |
| PERSON | TESTING | FirstName | varchar | 100 | (null) | (null) | 2 |
| PERSON | TESTING | Birthday | date | (null) | (null) | YYYY-MM-DD | 4 |
¶ 3. Remove a CFI Column Loader
To remove a CFI column loader, one only has to provide the name of the loader, the name of the bdomain and the name of the column specification that should be removed to the script API_CFIADM.REMOVE_LOADER_BD_COLUMN. Therefore, one has to issue the following command for each column that should be removed. The example below shows how the column id can be removed.
¶ 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 CFIADM.CFI_LOADER_BD_COLUMNS