In Mapping, it is possible to define stages, which are on the fly temporary objects that can be used to perform transformations, or anything you want between your integrations.
When using stages, the datatypes of the fields are by default automatically calculated based on the source and target.
This is sufficient and works in most of the cases, but sometimes the chosen datatype might not reflect data functionally, or is not optimized for it.
This article demonstrates how to define manually the datatype to be used on the stage fields.
- Stambia DI Designer S19.0.0 or higher
Defining datatypes on a Mapping stage is quite easy.
Let's take the example of this Mappings that loads a file into a stage table before integrating it in a target table.
The source 'CUS_FULL_IDENTIFIER' field represents a large string field that can contain more than 5000 characters.
The default calculated datatype in stage would be a VARCHAR datatype as the source field is a string, and this could work; but for this example, we want a CLOB datatype, that reflects more our target table.
For this, we simply click on the stage field, click on 'Enable DataType' in the Properties, and then specify that we want it to be a CLOB:
The datatype is specified in the 'Structure' tab, that opens automatically:
As we want it to be used directly in the load step, when creating the load table before loading source file data into the stage, the execution of the stage field must be set to 'source':
Using a datatype on a stage offers the possibility to perform source expressions in stages when the source supports transformations!
For the moment, the datatypes defined on stages are supported only on Load Templates, when the stage field is defined with source execution location, as shown in the example.
We're actually working on updating the staging templates to offer the possibility to do it also on the staging area execution location.