DB (Database) Definitions

DB Definitions describe the structure of your database to the Statelake system, This allows Statelake to understand your particular database structure, so that data can be read from the database and written to successfully. This gives you the flexibility to interact with any database structure as you are in complete control over the structure specified in the definition.

A DB Definition is an in-memory representation of the data you are going to read from or write to the database.

When reading data, the definition is opened against the specified database, and the data is read from the database into memory. The data is retained in memory until the definition is closed. This is a DB Definition for a Source dataset (table) within the database.

When writing data, the definition is opened against the database but no data is read - just the underlying structure, providing an in-memory representation of what the data structure is. You then write data into the definition using a Map, which converts the data to appropriate Insert/Update statements. Even after writing the data to the database, the data is retained in memory until the definition is closed. If the act of writing to the database has caused auto-generated fields to create values, these values can be returned to the in-memory dataset. This is a DB Definition for a Destination dataset (table) within the database.

While it could be assumed that the information required for a Source DB Definition or a Destination DB Definition would be the same, there are some areas where Source and Destination requirements differ, such as the way that some fields are handled. And there are some aspects within the definition that apply to either the Source or the Destination - and not both.

So it is most important that you understand the requirements of each definition, and set them up accordingly.

General Screen

The details required on this screen must be fully completed prior to advancing to the design phase.

Buttons

Button Name	Description
Save	Click to save any changes you have made. All changes will be lost if you do not click the Save button.
Cancel	Click to cancel any changes you have made
Delete	Click to delete this module. You will be asked to confirm your choice.
Design	This is a tab button. Click this button to open the designer. You should populate the fields of this main screen prior to designing the DB Definition.

The terms Exporting and Importing on this General Screen, may be confusing.

Exporting - in this case, means updating the Source dataset.

Importing - means writing into a Destination dataset.

Because the requirements for Source DB Definitions can be different from the requirements for Destination DB Definitions, these parts of the General Screen are identified separately.

Source DB Definition

The highlighted fields only apply when the DB Definition is used as the Source of a Map.

Fields

Field Name	Description
DB Definition Name	A friendly name for the DB Definition.
DB Connections	Select the DB Connection to be used on this DB Definition. This is a design-time setting, and can be overridden with run-time setting on the Action.
Transaction Identification None (for exporting)	The DB Connection must have been specified, else an error will be generated. This only applies when the DB Definition is used as the Source definition of a Map (exporting data). Click to appropriate radio button to select the method to use for flagging transactions as processed. Only one option can be selected. The default setting is None.
Use XFLO_ID field	Only select after the Design phase has been completed, and after a dataview has been created. Click to select.
Use Custom ID field	Only select after the Design phase has been completed, and after a dataview has been created. Click to identify the Custom ID Field to use, and specify the value that will indicate that processing has been completed. These are free text fields, and no internal checking will be performed to validate the field name. The value is dependent on the field type - such as zero (0) or one (1) for a boolean type; N or Y for a string type; one (1) or minus one (-1) for an integer type etc.
From “Last” ID processed	Only select after the Design phase has been completed, and after a dataview has been created. Click to select.
Exclude Audited Transactions	Click to select.
Duplicate Record Handling Update Record (for importing)	Leave as defaulted with the radio button for Update Record.
Disable automatic trim string	Disable the Automatic Trimming of blank spaces from the start and end of strings. By default this is activated.

Destination DB Definition

The highlighted fields only apply when the DB Definition is used as the Destination of a Map.

Fields

Field Name	Description
DB Definition Name	A friendly name for the DB Definition.
DB Connections	Select the DB Connection to be used on this DB Definition. This is a design-time setting, and can be overridden with run-time setting on the Action.
Transaction Identification None (for exporting)	Leave as defaulted with the radio button for None.
Duplicate Record Handling Update Record (for importing)	This only applies when the DB Definition is used as the Destination of a Map (importing data). Click the appropriate radio button to select the method to handle duplicate records when writing data to the Destination. Only one option can be selected. The default setting is Update Record.
Update Record (Cascading)	Click to select.
Ignore Record	Click to select. Ignore the new record. No action will be taken if the record is deemed to already exist.
Error Record	Click to select. An error message will be generated and no further action taken on the existing record.
Allow “dirty reads” for checking	Tick to activate.
Use In-Memory Check	Tick to activate.
Disable automatic trim string	Disable the Automatic Trimming of blank spaces from the start and end of strings. By default this is activated.
Save Null Fields as Blank Strings	Enables generated SQL statements to use Blank Strings for any fields with null values.
Save Blank String Fields as Nulls	Treats fields with blank string values as Nulls. This works with null values in SQL Statements.
Include null values in SQL statement (ie force nulls)	Enables generated SQL statements to include Null Values for any fields with null values.
Disable SQL Transactions