The Database layout Activity defines the procedure to insert, update, or delete data to and from a database table. It uses the predefined Database Info activity to connect to the database. With this, you can parse data from multiple tables within a particular database. You can insert or update data into multiple tables of a single database.
...
Go to Account and click Manage Objects.
- Click LAYOUTS > Database.
- Click CREATE Database Layout.
On Create Layout window, enter the name and description of the new Database Source in the Name and Description textboxes respectively.
Select the database info activity from the Select Database Info drop-down list or click the Create New button to create a new database info activity.
Click here to learn how to create Database Info activity.
- To define schema definition, select one of the following options:
- Import XSD File
- Use Database Table
- To select the XSD file, which contains the schema information, select the Import XSD File radio button and then click the Browse button.
- To define a schema using database table, select the Use Database Table radio button and then click the Browse Tables button. The Select Schema screen is displayed with the list of RDBMS Schema in case of SQL Server and DB2 Database Info.
This action will show you the Select Schema screen that has the list of RDBMS Schemas, in case of SQL Server and DBO Database Info .
...
Go to Services > Schema and then click Database. The action will show you the Manage Database Schema screen (see Figure 239).
Figure 239: Manage Database Schema
...
Info
...
Figure 240: Create Database Schema
To learn how to create Database Info activity, refer to the section Creating Database Info in the Developer Guide.
...
- Use XSD File
- Table Name
...
This action will show you the Select Schema screen that has the list of RDBMS Schemas, in case of SQL Server and DBO Database Info (see Figure 241).
Figure 241: Select Schema
...
Figure 242: Select Tables
If you want to go back from the Select Table screen to the Select Schema screen then, click the Close button.
If the schema and its tables are created on DB2, then you need to remember that the schema name in DB2 is case-sensitive. Else, it will display the same table more than once.
...
Select the table that you want and click the Get Columns button, this action will show you the Select Table Column(s) screen. This screen has a list of columns and their data types, along with the data format. The data format can be encrypted or plaintext (see Figure 243).
Figure 243: Select Table Column(s)
...
Click the Select All link to get the select query for all the columns or click on individual column names to get the select query of only those columns in the text field. You can also specify the where clause in the select query.
The column name should not include any special character (except $ and SID#) or reserved SQL keyword (for example, Identity) as then the schema will generate an error when you will use it at the source or target end database.
...
Select the mode of data in the column, whether Encrypted or Plain Text from the dropdown list. If you use this schema at the source end and you are getting encrypted data then, select Encrypted from the dropdown list. Similarly, if you use this schema at the target end and you want to send encrypted data then, select Encrypted from the dropdown list.
- If you select Encrypted in table column, you must select Encryption/Decryption secret key from the Advanced Properties.
- If you select Encrypted then the encrypted data length can be more than the source data length. In this case, you must define the target table column length more than the corresponding source table column length.
- If you select Encrypted in table column, you must set the Data Action property in Process Designer, while creating the process flow. To know more about Process Designer, refer to the Working with Process Flow section.
Click OK to return to the Database Schema screen. This action will generate an editable select query in the SQL Query field.
...
When you click the Save button, the schema will validate this query before saving it.
In case you use some SQL function or perform some calculation over the Column Name(s), use the alias name for that Column Name(s).
...
...
.
...
You can achieve performance optimization by using batch update. The advantage of batch update is to reduce the network calls to the database by not executing every single SQL query when they are raised. By using this feature, you can send multiple queries to the database in a single instant and thereby reducing the number of JDBC calls and improving performance.
This feature is available for the Insert and Update operation only.
...
Enter the number of statements that you want to commit to the database in a single instance, in the Commit Count textbox.
When you set the commit count value to a positive integer value then the schema starts and commits a batch of transaction as per the number in the Commit Count textbox. This helps in reducing the network traffic and in turn increases network performance. This is called as batch transaction.
...
Number Type: updated to null value
Date Type: updated to null value
String type: updated to empty value
To handle Null or Empty elements, check Handle Null/Empty Element(s) true. Check this option false to skip this handling. By default the option is checked true.
Note
Note that only one of the options between Handle Null/Empty Element(s) and Update Empty Tag should be checked true. Checking both of these true will abort the process flow.
...
On the Database Schema screen, click the Save button.
To learn more about the Advanced Properties refer to the Changing Advanced Properties section.
Special Usage Scenario
Delete Target Records
In a Database schema, you can set the Action attribute to delete the records from the target database table, if they are matching with the records of the source database table. If you define a primary key in the source and target schemas then the Data Mapper will match the source and target records on the basis of the primary key. If you do not define a primary key then the Data Mapper will match all of the records. You can enable this attribute in Data Mapper while mapping source and target schemas.
Steps to set action attribute to delete matching records
Load the source and target schemas in the Data Mapper. In target schema, there will be an Action attribute.
Create a constant 'delete' and map it to the Action attribute of the target schema.
You need to use the keyword "delete" if your target database is ORACLE, IBM DB2, MS SQL Server or HSQLDB. You can also use the "cancel" keyword for MS SQL Server DBMS to achieve the same functionality. However please note that, you cannot use the "cancel" keyword for other RDBMS except for Ms SQL Server.
Save the mapping activity.
To learn how to use Data Mapper, refer to the Using Data Mapper section.
...