Use Pagination in an Integration

When you must fetch a large number of results, you can use the Netezza Adapter’s pagination feature and receive sorted results/records in chunks. This use case describes how to use pagination to receive sorted results/records from the Netezza database to insert into an application (for example, SAP ASE) or files.

In this use case, the Netezza database is the source and the SAP ASE database is the target application. Similarly, you can receive sorted results/records from the Netezza database to insert into other applications or files using the Netezza Adapter.

To perform this operation, you create the Netezza Adapter andSAP ASE (Sybase) Adapter connections in Oracle Integration.

This use case uses the following features or operations:

• Offset and Limit Fields: These fields are displayed in the mapper. You can specify the number of results per page by providing the offset and limit values according to your requirement. Enter a starting value in the Offset field and an end value in the Limit field to receive sorted results.
• Run a SQL Statement (Netezza Adapter): Retrieves the total number of records.
• Select (Netezza Adapter): Enables you to enter the required query and select the pagination option.
• Insert or Update (SAP ASE (Sybase) Adapter): Inserts or updates the records into the SAP ASE database.

1. Create a schedule integration.
2. Assign the following two variables to your integration. Specify the values to the variables as follows:

   offset = “0”
   limit= “schedule_limit”
   

3. Drag a Netezza Adapter into the integration canvas.
4. Configure the Netezza Adapter as follows:
   1. On the Basic info page, provide an endpoint name, and select Run a SQL Statement.
   2. On the Run a SQL Statement page, enter a SQL query, and click Validate SQL Query.
   3. On the Summary page, review your selections.
5. Assign the following variable to your integration. Specify the value to the variable as follows:

   count="COUNT"

6. Drag a while action below the assign action.
7. Set the condition for the specified variables as follows:

   offset < count

8. Drag a Netezza Adapter inside the while action.
9. Configure the Netezza Adapter as follows:
   1. On the Basic info page, provide an endpoint name, and choose Select from the Perform an Operation On a Table list.
   2. On the Operation on Table page, select the parent database table, and click Edit under Review and edit SQL Query.
   3. In the SQL Query field, enter the required query. For this example:

      SELECT emp_id, employee_name, "location", designation 
      FROM public.employee rows limit #limit offset #offset

   4. Select the Pagination check box.
   5. On the Summary page, review your selections.
10. In the mapper, map offset to offset and limit to limit.
    
    

    

    
    
11. Drag an SAP ASE (Sybase) Adapter inside the while action.
12. Configure the SAP ASE (Sybase) Adapter as follows:
    1. On the Basic info page, provide an endpoint name, and choose Insert from the Perform an Operation On a Table list.
    2. On the Operation on Table page, select the parent database table, and click Edit under Review and filter columns from selected database tables.
    3. Select the required columns.
    4. On the Summary page, review your selections.
13. Drag an assign action inside the while action to update the specified variables as follows:

    offset = offset+limit

14. When complete, save and activate the integration.
    The completed integration looks as follows.