Creating Database Schema Activity

Owned by Gunjan Tulwani (Unlicensed)
Last updated: Jul 18, 2018 by Nitin Beri (Unlicensed)Version comment
7 min read

The Database Schema 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. It is recommended that you use Advanced Database Schema Activity. 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. Click here to know how to create a new Advanced Database Schema Activity.

Prerequisites

  • You must create Database Info activity before you create Database Schema activity.


Steps to create Database Schema

  1. On the Adeptia Suite homepage, click the Develop tab.

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

  3. Click the Create New link. This action will show you the Create Database Schema screen.
  4. Enter the name and description for the new database schema in the Name and Description textboxes respectively.

  5. 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 (see Figure 240).

    Figure 240: Create Database Schema

     

    To learn how to create Database Info activity, refer to the section Creating Database Info in the Developer Guide.

  6. To define schema definition, select one of the following options:
     
    • Use XSD File
    • Table Name
  7. To select the XSD file, which contains schema information, click the Choose File button.
  8. To define schema using a database table, select the Table Name radio button and click the Browse Tables button.

  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 (see Figure 241).

    Figure 241: Select Schema

  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 (see Figure 242).


    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.

  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 plaintext (see Figure 243).


    Figure 243: Select Table Column(s)

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

  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.

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

    • 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 as target database.

  16. Click Advanced Properties to view the advanced properties of the Database Schema.

    Advanced Properties of Database Schema

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

    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.

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

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

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

  2. Save the mapping activity.

     

    To learn how to use Data Mapper, refer to the Using Data Mapper section.