Page Comparison

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.
This feature is available in:

...

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

   

   Figure 211: Manage Advance Database Schema

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 textboxes respectively.Select the database info activity from the Database Info dropdown list (see Figure 212).
   

   Figure 212: Create Advance Database Schema
   
   

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

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

   

   Figure 213: Select Schema

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 (see Figure 214).
   
   Figure 214: Select Tables
   
   

   Info

   A Close button appears on the Select Table screen, in case of SQL Server or DBO Database Info. Clicking this button takes the user to the Select Schema screen. 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.   If you want to know more about the various key words and search parameters then click on the Help link on the webpage (see Figure 213).

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

   

   Figure 215: Select Columns and Primary Key

10. Anchor
    _Hlt145319779 _Hlt145319779

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

    Anchor
    _Hlt130966485 _Hlt130966485

    
    

    
    Figure 216: Generate Query

     

    Info
    There should not be any special character (except $ and SID#) in column name.

     

     

    Info

    The Adeptia Suite validates the query once you click the Save button. In case some SQL function is used or some calculation is done over the Column Name(s), use the alias name for that Column Name(s). For example, for a 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.

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

    Info
    At times, you may not use the Primary Key in the query, but append it internally in the query. You need to ensure that the Primary Key name should be the same at both the places. Else, the schema will fetch the data twice for the Primary Key column and result in errors.

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

    

    Figure 217: Create Child Table

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 (refer to Figure 214).

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

    Info
    A Close button appears on the Select Table screen, in case of SQL Server or DBO Database Info. Clicking this button takes the user to the Select Schema screen.

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

    

    Figure 218: Select Column, Primary and Related Keys

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.

    Info
    A column name should not include any standard SQL keyword (for example, Identity), as it generates an error, when you use the Advance Database schema as a source or target database.

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

    

    Figure 219: Add Related Key

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.

    Info
    At the Root level, you should not use the same table more than once.

22. Click Advanced Properties. The advanced properties of Advanced Database Schema are displayed (see Figure 220). 

    

    Figure 220: Advanced Properties

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

    Info
    Always uncheck the Filter Unmapped Elements checkbox in the data mapper while using the Auto increment Key Support feature.

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.

    Info

    Enabling the Query Batch Update and setting the Query Update Batch Size to a positive integer value causes updates to be sent to the database in batches of the specified size. For example, setting the Query Update Batch Size to 10 will group 10 separate statements and submit them as a single batch. Setting the Query Update Batch Size to 0 will cause the Database Target to disable batch execution and send updates to the database for every execution of statement. It is primarily used for performance optimization. The advantage of batch update is to reduce the network calls to database rather than executing single SQL statement. You can send multiple queries to the database at a time using batch update feature and this reduces the number of JDBC calls and improves the performance of the Adeptia Suite, network traffic, and also of your Process Flow.    Query Batch Update is supported for Insert and Update operation only.

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

    Info

    By default in JDBC, transaction starts and commits after each statement's execution on a connection. That is the behaviour when commit count is set to 1. Obviously, this mechanism gives good facility for users if they want to execute a single statement. But it gives poor performance when the schema executes multiple statements on a connection. This is because by default, the schema commits after each statement that in turn reduces the performance by issuing unnecessary commits. The remedy is to set commit count size to a value greater than 1 and it will then cause the Database Target to issue commit instruction to the database after a set of statements execute. This is usually called as batch transaction.

    Info
    To learn about other Advanced Properties refer to the Changing Advanced Properties section.

28. Select the project under which you want to save this Advance Database Schema activity from the Project drop-down list.
29. Select the owner for this activity from the Owner drop-down list.
30. Click the Save button.

...

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

...