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.