Creating Advanced Database Source Activity

Owned by Gunjan Tulwani (Unlicensed)
Last updated: Aug 21, 2020 by Akash Kumar Jaiswal (Unlicensed)Version comment
2 min read

Advanced Database Source activity fetches data from multiple tables of a database. 

Prerequisites

Ensure that you have created Database Info activity and Advanced Database Schema.

To create an Advanced Database Source activity: 

  1. Go to Develop > Services > Source > Adv. Database. 

     

  2. Click Create New.



  3. On the New Adv. Database Source window, type the name and description of the new Advanced Database Source activity in the Name and Description.

  4. Select the database info activity and advanced database schema activity from the Database Info and the Schema Name.

    You can edit the query only after saving the Advanced Database Source activity by clicking Edit Queries, default disabled.

    You can edit the query only for the where clause and you can't change the table name or column name. To change the table name or columns, go to Advanced Database Schema and edit the schema.

    If you specify where clause in the Advanced Database Source as well as in Advanced Database Schema then where clause of Advanced Database Schema is used during execution. 

  5. Expand Advanced Properties to set the advanced properties related to the activity.



  6. In the Query Definition, database query is displayed in the XML form. The database query is displayed only if you have edited the query. If you edit the query displayed in the Query Definition then database query defined using Edit Query is overwritten.
  7. Use Optimize Algorithm: This algorithm sorts the parent table with the Primary Key and all subsequent child tables with the joining key of their parent table. This algorithm applies only, if all the child tables of the parent table, at any level, are related to the parent table by the same key. Selecting this check box makes data fetching from the source database table faster. Select this check box if the database source is MS SQL.

  8. Select With (NoLock) Option checkbox to fetch the records from the source database without any lock constraint. Select this check box if the database source is MS SQL.

    For information on Advanced Properties, refer to Changing Advanced Properties.

  9. If required you can override the default fetch size value in Fetch Size field by setting the size you need. If you leave this field blank, Adeptia Connect will set its value to default.

    Fetch size refers to the volume of records being fetched from the database. Higher fetch size retrieves higher volume of records at a time, improving the performance by reducing the number of hits to the database. You can set your own fetch size to override the default size and improve the performance. If you leave it blank or enter 0 (Zero), it considers the default fetch size defined in the database.
  10. Click Save.