Creating a Database Schema instructs the Integration Engine how to format data it reads from or writes to a database.
- Select File | New | Database Schema from the main menu bar.
Note: If you select the package in which the Database Schema should reside before creating it, some parameters are automatically populated in the New Database Schema window.
- Provide the Source folder, Package, and Name.
- Click Finish. The Database Schema editor appears.
Defining a Database Schema populates it with the tables and columns specified in a Data Source.
You must also have a Data Source created and defined. If you have not already created this object, see Creating and Defining Data Sources.
- In the Database Schema editor, under the Schema section, click the Populate the schema using a Data Source button to produce the Select Data Source window.
- Select a Data Source. Click OK. The tables and columns defined in the Data Source appear in the Schema section.
Note: A Data Source must exist in the current Project, or in a Package that has been made available to the current Project, to appear in the selector window.
- Please note the following on the default value:
- It applies to target data only.
- It applies only when the data being passed is null (OR)
- only when a new record is generated for the field containing the null.You have an option to set a default value for null data in your target data (data with no value). For example, you may wish that “NULL” always appears in target data in case of no data.
To set a default value on a particular field, select the field and type a value in the Default Value On Target field on the Properties view.
Note: In order to use this feature, you must also configure the Null Source Action drop-down found on your Ruleset editor, under the Runtime view.
This only applies to older, Version 1 Rulesets. New engine functionality (Version 2) no longer requires this additional configuration to the Ruleset editor.
- Click Save to complete this task.
Note: Although you can edit a Schema to remove tables and columns or change a table's or column's properties, Cleo does not recommend doing so. It is best to keep the Schema matching the Data Source. If you make the Schema different from the Data Source and ever need to repopulate the Schema from the Data Source (due to added fields, etc.), you will lose changes made in the Schema.
If you need different tables or columns than those specified in the current Data Source, the best practice is to create another Data Source. If you decide to edit a table or column in a Schema, you must also edit any Database Monitor that is referencing the Schema. This information can be found in the Data Binding section of the Database adapter.
Cleo recommends using the Database Refresh Wizard to streamline any changes to Data Sources and their affected Schemas and Adapters.
Next: To use the Schema, select it when creating a Ruleset.