The Database Layout service defines the procedure to insert, update, or delete data to and from a database table. It uses the predefined Database Info service to connect to the database.
Prerequisites
You must create a Database Info before you create Database Layout.
Steps to create Database Layout:
Click Configure > LAYOUTS > Database.
Click Create Database Layout.
- Enter the name and description for Database Layout service in the Name and Description fields.
Select the database info service from the Select Database Info drop-down list or click the Create New button to create a new database info service.
Click here to learn how to create Database Info service.- To define layout definition, select one of the following options:
- Import XSD File
- Table Name
- To select the XSD file, which contains layout information, select Import XSD File radio button and then click Choose File button.
- To define schema using a database table, select the Table Name radio button and click the Browse Tables button.
- To define a schema using database table, select the Table Name 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.
On the Select Schema screen, select the required RDBMS schema and click the Get Tables button. This action will show you the Select Table screen with a list of database 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 plain text.
Click 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).
For example, for query like:
SELECT SID,Name+Dept,Salary FROM dbo.Employee
You can use:
SELECT SID, (Name+Dept) as AliasName,Salary FROM dbo.Employee
Reason:
While getting the result set, you have to assign the output of
(Name+Dept) to some new field. |- Enter the name of the primary key in the Primary Key textbox. Primary Key is the name of the field on the basis of which the schema updates the target database table. Primary Key is only used, when the database schema is used with database target. You can use Primary Key only when you are using database layout service as target database.
Click Advanced Properties to view the advanced properties of the Database layout.
- Define the Character-set encoding for your Database layout in the Character Set Encoding field.
To enable the query batch update, check the Query Batch Update checkbox and enter the batch size in the Query Batch Size field.
Enabling the Query Batch Update checkbox leads to database updation in batches as per the positive value set in the Query Batch Size field.
For example, setting a value '10' in the Query Batch Size field will group 10 separate statements and submit them as a single batch. However setting the value to '0' will disable the batch execution and execution of every statement will occur simultaneously.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.Select Use Commit Count check box to enable the Commit Count field.
Enter the number of statements that you want to commit to the database in a single instance, in the Commit Count field.
When you set the commit count value to a positive integer value then the layout starts and commits a batch of transaction as per the number in the Commit Count text box. This helps in reducing the network traffic and in turn increases network performance. This is called as batch transaction.
- Check the Use NoLock Option checkbox to enable the no lock option. When you enable this option, the database layout will be able to read the data from the database table even if the database table is locked.
- When you check the Update Empty Tag option then, an empty tag, such as <id/>, in the input XML file to the target database is handled as given below:
Number Type: updated to null value
Date Type: updated to null value
String type: updated to empty value Select the check box Handle Null/Empty Element(s) to handle Null or Empty elements.
Select Filter Invalid XML Characters check Box to filter out invalid XML characters (if any) in the input data
In case, the input data contains some characters that are invalid in XML, then this may result in the mapping getting aborted.If the layout is used at the target end and you want to convert the data from Plain Text to encrypted mode, select the secret key activity from the Encryption Secret Key field. The selected secret key activity is used to encrypt the data.
If the layout is used at the source end and you want to convert the data from Encrypted Mode to Plain Text, select the secret key activity from the Decryption Secret Key field. The selected secret activity is used to decrypt the data.
Select the Project from the Project drop-down list.
Click Save.