/
Creating Advanced Database Schema Activity

Creating Advanced Database Schema Activity

Owned by Gunjan Tulwani (Unlicensed)
Last updated: May 16, 2018 by Ankit Gandhi (Unlicensed)Version comment

You need to create an advanced database schema activity if you want to parse data from multiple tables within a particular database. You can also use this schema if you wish to insert or update data into multiple tables on a single database. The Advance Database schema uses the predefined Database Info to connect to the database. 


Prerequisites

  • You should create Database Info activity before creating Advanced Database Schema activity.


Steps to create the Advance Database Schema

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

  2. Go to Services >Schema and then click Adv. Database. The Manage Adv. Database Schema screen is displayed.

     

  3. Click the Create New button. The Create Advance Database Schema screen is displayed.

  4. Enter the name and description for the new Advance Database Schema in the Name and Description text boxes respectively.Select the database info activity from the Database Info dropdown listFigure 212).

  5. To define a schema definition, select one of the following options:
     

    • Use XSD File

    • Use Database Table

  6. To select the XSD file, which contains the schema information, select the Use XSD File radio button and then click the Browse button.

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

     

  8. Select the RDBMS schema that you need and click the Get Tables button. If you need to filter your search by some key word or a range of characters then enter the text in the text box as in Figure 213 and then click the Get Tables button. The Select Table screen is displayed with the list of tables.

  9. Select the table that you need and click the Get Columns button. The following screen is displayed with the list of columns and their data types.

     

  10. In the Select Columns grid, select the required column(s) and click the Generate Query button to generate the database query. The generated query is displayed in the Query list box. You can also specify the Where clause within the generated Select query.


     

  11. To designate a column as primary key, select the column(s) that you need and then click the Add Primary Key button. The selected column is displayed in the Primary Key textbox

  12. Click OK to return to the Create Advance Database Schema screen. You can see the selected query and primary key in their respective fields.

  13. To add a child table to the selected table, click the Add Child Table button in the Create Advance Database Schema screen.

     

  14. Click the Browse Tables button in the child table. The Adeptia Suite displays the Select Schema screen with a list of RDBMS Schema in case of SQL Server and DB2 Database Info (refer to Figure 213). If HSQLDB Database Info is selected, then the Adeptia Suite will display the Select Table screen.

  15. On the Select Schema screen, select the required RDBMS schema and click the Get Tables button. The Adeptia Suite will then show you the Select Table screen with the list of tables (refer to Figure 214).

  16. Select the table which you want to display and then click the Get Columns button. The following screen is displayed.

     

  17. In Select Columns grid, select the required column(s) and click the Generate Query button to generate the database query. The generated query is displayed in the Query field. You can also specify the Where clause within the generated Select query.

  18. To designate a column as a Related Key, select the column(s) that you want to relate and click the Add Related Key button. The selected column is displayed in the Related Keys list box.

     

  19. Select the column(s) that you want to change the Primary Key and click the Add Primary Key button. The selected column is displayed in the Primary Keys list box.

  20. Click OK to return to the Advance Database Schema screen.

  21. To add another independent database table, click the Add Root Table button and repeat the steps from 8 to 13. Alternately, you can create a root table from the Create Advance Database Schema screen by clicking the Add Root Table button.

  22. Click Advanced Properties. The advanced properties of Advanced Database Schema are displayed. 


  23. If required, change the default Character Set Encoding according to the encoding of your data file. This value is used to parse the data.

  24. Enable the Auto increment Key Support checkbox if required. This feature is used when the parent table's AutoIncrement column is referenced in the child table. Enabling this checkbox automatically populates the value in the child table's foreign key column with the Auto increment column's value from the parent table.

  25. There are some Unicode Characters, which are reserved characters in XML. You can filter these characters by selecting the Filter Invalid XML Characters option.

  26. To enable the query batch update, select the Query Batch Update checkbox and enter the batch size in the Query Batch Size textbox.

  27. Enter the number of statements that you want to commit to the database at a time, in the Commit Count textbox.

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


  29. Select the project under which you want to save this Advance Database Schema activity from the Project drop-down list.

  30. Select the owner for this activity from the Owner drop-down list.

  31. Click the Save button.

Special Usage Scenario

Delete Records from target table

In Advance Database schema, you can set action attribute to delete the records from the target database table, if they are matching with the records of source database table. Source and target records are matched based on primary key. This attribute is enabled in Data Mapper, while mapping source and target schemas.
 

Steps to set action attribute to delete matching records

Load required source and target schema in Data Mapper. In target schema, there will be an Action attribute.

  1. Create a constant 'delete' and map it to Action attribute of the target schema.

  2. Save the Mapping activity.

    Enable RollBackOn Error Attribute

    In Advance Database schema, you can enable the RollBackOnError attribute. In case your schema encounters any error in the target database then you will need to enable RollBackOnError attribute to roll back data updation or insertion. When you map source and target schemas only then the Data Mapper enables this feature.

    Steps to Enable RollBackOnError attribute

  1. Load the source and target schema in the Data Mapper. In the target schema, there will be a RollBackOnError attribute.

  2. Create a constant 'true and map it to RollBackOnError attribute of the target schema.

  3. Save the Mapping activity.

Suppose there is an hierarchy as:
R1

- R1C1

 - R1C1C1

If you apply the RollbackOnError attribute on R1C1, then in case of an error condition in R1C1 or R1C1C1, rollback will be up to R1C1 only. The RollbackOnError attribute will not affect R1.

According to this implementation, the Adeptia Suite will display the error count in the Process Flow log.
To learn how to use Data Mapper, refer to the Using Data Mapper section.