Page Comparison

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 activity  service to connect to the database.
It

Steps to create Database (Deprecated) Layout activityLayout:

1. Click Configure > LAYOUTS > Database (Deprecated).

2. Click Create Database (Deprecated) Database Layout.
   
   
   
   

3. Enter the name and description for Context Upload in the in Database Layout service in the Name and Description fields.

4. Select the database info activity service from the Select Database Info drop-down list or click the Create New button to create a new database info activityservice. 

   Info
   Click here to learn how to create Database Info activityservice.

   
   

5. To define schema layout definition, select one of the following options: 
   • Import XSD File
   • Table Name
6. To select the XSD file, which contains schema layout information, select Import XSD File radio button and then click Choose File button.
7. To define schema using a database table, select the Table Name radio button and click the Browse Tables button.
8. 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.

9. 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.
   
   Image Added
   
   

10. 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.
    
    Image Added
    
    

    Info
    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.

    
    

11. 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 plaintextplain text.
    
    Image Added
    
    

12. Click the 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.

    Info
    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.

    
    

13. 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.

    Info

    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.

    
    

14. Click OK to return to the Database Schema screen. This action will generate an editable select query in the SQL Query field.

    Info

    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. |

    
    

15. 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 layout service as target database.

16. Click Advanced Properties to view the advanced properties of the Database Schemalayout.
    
    Image Added
    
    

17. To enable the query batch update, check the Query Batch Update checkbox and enter the batch size in the Query Batch Size field. 

    Info

    Enabling the Query Batch Update checkbox leads to database updation in batches as per the positive value set in the Query Update Batch Size field. 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.

    
    

18. Enter the number of statements that you want to commit to the database in a single instance, in the Commit Count textbox text box.

    Info
    When you set the commit count value to a positive integer value then the schema layout starts and commits a batch of transaction as per the number in the Commit Count textbox text box. This helps in reducing the network traffic and in turn increases network performance. This is called as batch transaction.

    
    

19. Check the Use NoLock Option checkbox to enable the no lock option. When you enable this option, the database schema layout will be able to read the data from the database table even if the database table is locked.
20. 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

    
    

21. 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

    Info
    title
    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.

    
    

22. On the Database Schema screen, click the Save buttonSelect the Project from Project drop-down list.

23. Click Save.