The Advanced Database Target activity provides the ability to specify multiple tables of a database. These are the target tables for which you can fetch the data from the source location by creating a source activity. On execution of the process flow, the activity maps the schema of the fetched data to the schema of the target data. Moreover, depending on how you want to process the error records, the activity will move the data records to these target tables.

Prerequisites

Database Info activity and Advanced Database Schema must be created before creating Advanced Database Target activity.
This section describes how to create an Advance Database Source activity using the following details as an example: 

Steps to create an Advanced Database Target Activity 

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

2. Go to Services > Target and then click Adv.Database. The Manage Advanced Database Target screen is displayed (see Figure 281).

   

   Figure 281: Manage Advanced Database Target

3. Click the Create New link. The Create Advanced Database Target screen is displayed.
4. Enter the name of the new Advanced Database Target in the textbox Name. Then, enter the description for the Advanced Database Target in the textbox Description.

5. Select the database info activity and database schema activity from the dropdown lists Database Info and Schema Name respectively (see Figure 282).

   

   Figure 282: Create Advanced Database Target

6. Database Operation specifies how data records are inserted into database tables. Select one of the following database operations:
    
   • Insert
   • Update
   • Insert/Update
   • Update/Insert

    

   Database Operation	Effect on Database
   Insert	This option is selected when you want to insert records into the database tables. If records already exist in the database table, new records are added in the database table along with existing records.
   Update	This option is selected when you want to update the existing record. To use updated option a column of database must be marked as Primary Key. When Update option is selected, database target first checks which column of the database table is marked as Primary Key.
   Insert/Update	This option is selected when the database target first tries to insert the data into database table. If insert operation fails, database target tries to update the data.
   Update/Insert	This option is selected when the database target first tries to update the database table. If update operation fails, database target tries to insert the data.

    

    

   If the specified database operation fails on any source data, the activity creates its error records. While creating a process using an Advance Database Source, you can specify what to do with error records.

   • Error records can be saved into repository file
   • Error records can be ignored
   • It can be further processed (e.g. can be sent to File Target)
   • Process Flow can be aborted, if there is any error record
     
     
     In the Process Designer, right click on the Advance Database Source activity and select the View properties. Select the value of the "Error Record" properties.
     
     To learn, how to use Process Designer, refer to the section Using Process Designer.
     To learn how to create Database Info activity, refer to the Creating Database Info section in the Administrator Guide. To learn how to create the Advance Database Schema activity, refer to the Creating Advance Database Schema Activity section.

     

   When an excel file is used as a database target, only Insert operation is supported. The Update, Insert/Update and Update/Insert, operations are not supported.
   When an excel file is used as a database target, the error records are not generated in case of data type mismatch.

     

   To learn about Advanced Properties refer to Changing Advanced Properties section.

7. Click the Save button.

Understanding Record Count in Process Flow Logs

The Process Flow Log displays the number of records processed by an Advanced Database Target activity during the execution of a process flow. This record count differs based on its type. The various types of record counts are explained below.

Transaction Commit Count
This record count type includes the total number of records in the input at root level, which the Advanced Database Target activity can successfully process and commit. It includes inserted, deleted, and updated records.

Insert Query: Submitted/Successful
This record count type includes the Insert Query, which are submitted or are successful. Submitted is the total number of Insert Query submitted/executed by the Advanced Database Target activity.
Successful is the total number of records, which are inserted and committed in the table for the Insert Query and which are not rolled back.

Delete Query: Submitted /Successful

This record count type includes the Delete Query, which are submitted or successful.
Submitted is the total number of Delete Query submitted/executed by the Advanced Database Target activity.
Successful is the total number of records, which are deleted and committed from the table for the Delete Query and which are not rolled back.

Update Query: Submitted /Successful

This record count type includes the Update Query, which are submitted or successful.

Submitted is the total number of Update Query submitted/executed by the Advanced Database Target activity.
Successful is the total number of records, which are updated and committed in the table for the insert query and which are not rolled back.

Rollback Transaction Count
This record count type includes the total number of records in the input at root level that the Advance Database Target activity has rolled back.

Rollback Insert Count
This record count type includes the total number of records, which were to be inserted but rolled back due to any error.

Rollback Delete Count
This record count type includes the total number of records, which were to be deleted but rolled back due to any error.

Rollback Update Count
This record count type includes the total number of records, which were to be updated but rolled back due to any error.
To understand the different counts, let us assume the following example:

Suppose there are two records R1 and R2 at root level of input. R1 has a child R1C1 and further R1C1 has a child R1C1C1. Similarly, R2 has a child R2C1 and further R2C1 has a child R2C1C1.
Their number of records is displayed in the brackets. For these records, insert queries are issued by the Advanced Database Target activity. Records from R1, its child (R1C1) and sub-child (R1C1C1) are successfully inserted in the target table. At the same time, while inserting the records from R2C1C1 the Advance Database Target activity encountered an error in 8 records. Now, the record of R2 and its child (R2C1) corresponding to erroneous records will be rolled back. Thus, the number of records rolled back from R2C1C1, R2C1, and R2 will be 8, 4, and 2 respectively.
Thus, in this scenario, the values of the record count types will differ as displayed below.
Table 1: Record Count Type Values

All the other counts will be zero.