Advanced Database Layout
The Database layout Activity defines the procedure to insert, update, or delete data to and from a database table. It uses predefined Database Info activity to connect to 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.
Prerequisites
- You must create Database Info activity before you create Database layout activity.
Steps to create Database layout
Click Configure > 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.
- To define schema definition, select one of the following options:
- Import XSD File
- Use Database Table
- To select XSD file, which contains the schema information, select Import XSD File radio button and then click Choose File 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.
- 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 plaintext.
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).
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 schema activity as target database.
Click Advanced Properties to view the advanced properties of the Database Schema.
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 Update Batch Sizefield.
For example, setting a value '10' in the Query Update 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.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.
- Check the Use NoLock Option checkbox to enable the no lock option. When you enable this option, the database schema 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 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.