Manage IBM DB2 resources with Azure Logic Apps

• 08/23/2018
• 12 minutes to read
• • 
  • 
  • 
  • 
  • 

With Azure Logic Apps and the IBM DB2 connector, you can create automated tasks and workflows based on the resources stored in your DB2 database. Your workflows can connect to the resources in your database, read and list your database tables, add rows, change rows, delete rows, and more. You can include actions in your logic apps that get responses from your database and make the output available for other actions.

This article shows how you can create a logic app that performs various database operations. If you're new to logic apps, review What is Azure Logic Apps?.

Supported platforms and versions

The DB2 connector includes a Microsoft client that communicates with remote DB2 servers across a TCP/IP network. You can use this connector for accessing cloud databases such as IBM Bluemix dashDB or IBM DB2 for Windows running in Azure virtualization. You can also access on-premises DB2 databases after you install and set up the on-premises data gateway.

The IBM DB2 connector supports these IBM DB2 platforms and versions along with IBM DB2 compatible products, such as IBM Bluemix dashDB, that support Distributed Relational Database Architecture (DRDA) SQL Access Manager (SQLAM) versions 10 and 11:

Supported database operations

The IBM DB2 connector supports these database operations, which map to the corresponding actions in the connector:

Prerequisites

• An Azure subscription. If you don't have an Azure subscription, sign up for a free Azure account.

• An IBM DB2 database, either cloud-based or on-premises

• Basic knowledge about how to create logic apps

• The logic app where you want to access your DB2 database. This connector provides only actions, so to start your logic app, select a separate trigger, for example, the Recurrence trigger. The examples in this article use the Recurrence trigger.

Add DB2 action - Get tables

1. In the Azure portal, open your logic app in the Logic App Designer, if not already open.

2. Under the trigger, choose New step.

3. In the search box, enter "db2" as your filter. For this example, under the actions list, select this action: Get tables (Preview)

   

   You're now prompted to provide connection details for your DB2 database.

4. Follow the steps for creating connections for cloud databases or on-premises databases.

Connect to cloud DB2

To set up your connection, provide these connection details when prompted, choose Create, and then save your logic app:

For example:

Connect to on-premises DB2

Before creating your connection, you must already have your on-premises data gateway installed. Otherwise, you can't finish setting up your connection. If you have your gateway installation, continue with providing these connection details, and then choose Create.

For example:

View output tables

To run your logic app manually, on the designer toolbar, choose Run. After your logic app finishes running, you can view the output from the run.

1. On your logic app menu, select Overview.

2. Under Summary, in the Runs history section, select the most recent run, which is the first item in the list.

   

3. Under Logic app run, you can now review the status, inputs, and outputs for each step in your logic app. Expand the Get tables action.

   

4. To view the inputs, choose Show raw inputs.

5. To view the outputs, choose Show raw outputs.

   The outputs include a list of tables.

   

Get row

To fetch one record in a DB2 database table, use the Get row action in your logic app. This action runs a DB2 SELECT WHERE statement, for example, SELECT FROM AREA WHERE AREAID = '99999'.

1. If you've never used DB2 actions before in your logic app, review the steps in the Add DB2 action - Get tables section, but add the Get row action instead, and then return here to continue.

   After you add the Get row action, here is how your example logic app appears:

   

2. Specify values for all the required properties (*). After you select a table, the action shows the relevant properties that are specific to records in that table.

   Property	Required	Description
   Table name	Yes	The table that has the record you want, such as "AREA" in this example
   Area ID	Yes	The ID for the record you want, such as "99999" in this example

   

3. When you're done, on the designer toolbar, choose Save.

View output row

To run your logic app manually, on the designer toolbar, choose Run. After your logic app finishes running, you can view the output from the run.

1. On your logic app menu, select Overview.

2. Under Summary, in the Runs history section, select the most recent run, which is the first item in the list.

3. Under Logic app run, you can now review the status, inputs, and outputs for each step in your logic app. Expand the Get row action.

4. To view the inputs, choose Show raw inputs.

5. To view the outputs, choose Show raw outputs.

   The outputs include your specified row.

   

Get rows

To fetch all records in a DB2 database table, use the Get rows action in your logic app. This action runs a DB2 SELECT statement, for example, SELECT * FROM AREA.

1. If you've never used DB2 actions before in your logic app, review the steps in the Add DB2 action - Get tables section, but add the Get rows action instead, and then return here to continue.

   After you add the Get rows action, here is how your example logic app appears:

   

2. Open the Table name list, and then select the table you want, which is "AREA" in this example:

   

3. To specify a filter or query for results, choose Show advanced options.

4. When you're done, on the designer toolbar, choose Save.

View output rows

To run your logic app manually, on the designer toolbar, choose Run. After your logic app finishes running, you can view the output from the run.

1. On your logic app menu, select Overview.

2. Under Summary, in the Runs history section, select the most recent run, which is the first item in the list.

3. Under Logic app run, you can now review the status, inputs, and outputs for each step in your logic app. Expand the Get rows action.

4. To view the inputs, choose Show raw inputs.

5. To view the outputs, choose Show raw outputs.

   The outputs include all the records in your specified table.

   

Insert row

To add a single record to a DB2 database table, use the Insert row action in your logic app. This action runs a DB2 INSERT statement, for example, INSERT INTO AREA (AREAID, AREADESC, REGIONID) VALUES ('99999', 'Area 99999', 102).

1. If you've never used DB2 actions before in your logic app, review the steps in the Add DB2 action - Get tables section, but add the Insert row action instead, and then return here to continue.

   After you add the Insert row action, here is how your example logic app appears:

   

2. Specify values for all the required properties (*). After you select a table, the action shows the relevant properties that are specific to records in that table.

   For this example, here are the properties:

   Property	Required	Description
   Table name	Yes	The table where to add the record, such as "AREA"
   Area ID	Yes	The ID for the area to add, such as "99999"
   Area description	Yes	The description for the area to add, such as "Area 99999"
   Region ID	Yes	The ID for the region to add, such as "102"

   For example:

   

3. When you're done, on the designer toolbar, choose Save.

View insert row outputs

To run your logic app manually, on the designer toolbar, choose Run. After your logic app finishes running, you can view the output from the run.

1. On your logic app menu, select Overview.

2. Under Summary, in the Runs history section, select the most recent run, which is the first item in the list.

3. Under Logic app run, you can now review the status, inputs, and outputs for each step in your logic app. Expand the Insert row action.

4. To view the inputs, choose Show raw inputs.

5. To view the outputs, choose Show raw outputs.

   The outputs include the record you added to your specified table.

   

Update row

To update a single record in a DB2 database table, use the Update row action in your logic app. This action runs a DB2 UPDATE statement, for example, UPDATE AREA SET AREAID = '99999', AREADESC = 'Updated 99999', REGIONID = 102).

1. If you've never used DB2 actions before in your logic app, review the steps in the Add DB2 action - Get tables section, but add the Update row action instead, and then return here to continue.

   After you add the Update row action, here is how your example logic app appears:

   

2. Specify values for all the required properties (*). After you select a table, the action shows the relevant properties that are specific to records in that table.

   For this example, here are the properties:

   Property	Required	Description
   Table name	Yes	The table where to update the record, such as "AREA"
   Row ID	Yes	The ID for the record to update, such as "99999"
   Area ID	Yes	The new area ID, such as "99999"
   Area description	Yes	The new area description, such as "Updated 99999"
   Region ID	Yes	The new region ID, such as "102"

   For example:

   

3. When you're done, on the designer toolbar, choose Save.

View update row outputs

To run your logic app manually, on the designer toolbar, choose Run. After your logic app finishes running, you can view the output from the run.

1. On your logic app menu, select Overview.

2. Under Summary, in the Runs history section, select the most recent run, which is the first item in the list.

3. Under Logic app run, you can now review the status, inputs, and outputs for each step in your logic app. Expand the Update row action.

4. To view the inputs, choose Show raw inputs.

5. To view the outputs, choose Show raw outputs.

   The outputs include the record you updated in your specified table.

   

Delete row

To delete a single record from a DB2 database table, use the Delete row action in your logic app. This action runs a DB2 DELETE statement, for example, DELETE FROM AREA WHERE AREAID = '99999'.

1. If you've never used DB2 actions before in your logic app, review the steps in the Add DB2 action - Get tables section, but add the Delete row action instead, and then return here to continue.

   After you add the Delete row action, here is how your example logic app appears:

   

2. Specify values for all the required properties (*). After you select a table, the action shows the relevant properties that are specific to records in that table.

   For this example, here are the properties:

   Property	Required	Description
   Table name	Yes	The table where to delete the record, such as "AREA"
   Row ID	Yes	The ID for the record to delete, such as "99999"

   For example:

   

3. When you're done, on the designer toolbar, choose Save.

View delete row outputs

To run your logic app manually, on the designer toolbar, choose Run. After your logic app finishes running, you can view the output from the run.

1. On your logic app menu, select Overview.

2. Under Summary, in the Runs history section, select the most recent run, which is the first item in the list.

3. Under Logic app run, you can now review the status, inputs, and outputs for each step in your logic app. Expand the Delete row action.

4. To view the inputs, choose Show raw inputs.

5. To view the outputs, choose Show raw outputs.

   The outputs no longer include the record you deleted from your specified table.

   

Connector reference

For technical details, such as triggers, actions, and limits, as described by the connector's OpenAPI (formerly Swagger) file, see the connector's reference page.

Get support

• For questions, visit the Azure Logic Apps forum.
• To submit or vote on feature ideas, visit the Logic Apps user feedback site.

Next steps

• Learn about other Logic Apps connectors