IBM DB2

Learn how to connect Engini to IBM DB2.

Using Engini’s IBM DB2 activities, you can: create, get and update records to manage and define databases.

Getting Started with IBM DB2 #

Prerequisites #

• A IBM DB2 account

Add a connection to IBM DB2 in Engini #

1. Enter your Engini account at https://app.engini.io.
2. Navigate to Connections page by clicking on the Connections on the left sidebar or by clicking here.
3. Click on the “Add connection” option located at the topbar.

4. Choose IBM DB2 option from the available applications

5. Enter the following details in the “Add Connection” form and press Save:

• Connection name
  Enter a unique and descriptive name for this connection. This name will help you identify and manage the connection in your Engini account. “IBM DB2” by default.
• Connection Method

–Direct Access:

Server address
Specify the address or hostname of your IBM DB2 server. This is the location where your database is hosted.

• Host name
  Enter your host name.
  • Database name
    Provide the name of the specific database within the IBM DB2 server that you want to connect to.
  • Username
    Enter the username associated with your IBM DB2 This username should have the necessary permissions to access and interact with the database.
  • Password
    Enter the corresponding password for the provided username. Make sure the password is accurate to establish a secure connection.
• Communication Channel
  Choose the appropriate connection type based on your setup:
  • Cloud- If you are connecting to a database hosted in a cloud environment, select “Cloud”.
  • OPA- If your database is on-premises and you are using an On-Premises Agent (OPA) for the connection, select “OPA”.
    In this case, an additional field will appear:
    On-Prem Agent- Choose the specific On-Premises Agent that you want to use for this connection if you have multiple agents configured.

– DSN – For ODBC connections, you must have a data source name (DSN): Creating data source names (DSN) – IBM Documentation

• DSN name
  Provide the name of the DSN
• Username
  Enter the username associated with your IBM DB2 This username should have the necessary permissions to access and interact with the database.
• Password
  Enter the corresponding password for the provided username. Make sure the password is accurate to establish a secure connection.
• Communication Channel
  Choose the appropriate connection type based on your setup:
  • Cloud- If you are connecting to a database hosted in a cloud environment, select “Cloud”.
  • OPA- If your database is on-premises and you are using an On-Premises Agent (OPA) for the connection, select “OPA”.
    In this case, an additional field will appear:
    On-Prem Agent- Choose the specific On-Premises Agent that you want to use for this connection if you have multiple agents configured.

6. After clicking “save”, a window will prompt you to select Database Objects from available Tables/Views/Stored Procedures.

Actions #

Create Record #

1. Table
   1. Click on the empty field and the tooltip will pop up showing all the tables you can use.
   2. All available tables will be accessible and you can select the specific table for the new record you create.
2. Add field
   
   By clicking the “Add field” button, you can choose how to fill in the various fields in the record you are creating. You can add one field or more to the activity definitions.
   1. Choose from the drop down the field you want to fill.
   2. Click on the empty field to the right of the drop down to be shown the tooltip with all the options you can use to fill the field.
   3. You can populate the field in one of the following options:
      • Static value – number / string. when using strings, you need to surround them with single quotes (‘).
      • Property value from a previous activity (Using the tooltip that opens when clicking on the field).
      • Expression – using functions and/or Previous activity properties and/or static values (Using the tooltip that opens when clicking on the field).
   4. Repeat steps 1-3 for all the fields you want to populate.

Get Records #

Get Records from a specified table/view.

1. Table/View
   1. Click on the empty field and the tooltip will pop up showing all the Tables/Views you can use.
   2. All available tables/views will be accessible and you can select the specific tables/views of the records you want to get.
2. Top
   You can set the number of rows you want to retrieve from the table or view. This parameter limits the result set to the specified number of records, with the default being 100.
3. Offset
   If you want to skip a certain number of rows before retrieving data, you can set the offset value. The default is 0, meaning no rows are skipped.
4. Add Sorting
   You can specify sorting criteria for the retrieved data. Sorting helps you order the records based on specific columns, such as ascending or descending order.
5. Add Filter
   By clicking the “Add filter” button, You can specify which records will be returned from the get results. You can add one filter or more to the activity definitions.
   1. Choose from the drop down the field you want to filter by.
   2. Select the condition you want the record to meet for the selected field.
   3. Click on the field to show the tooltip with all the options you can choose.
   4. You can populate the field in one of the following options:
      • Static value – number / string. when using strings, you need to surround them with single quotes (‘).
      • Property value from a previous activity (Using the tooltip that opens when clicking on the field).
      • Expression – using functions and/or Previous activity properties and/or static values (Using the tooltip that opens when clicking on the field).

Update Record #

1. Table
   1. Click on the empty field and the tooltip will pop up showing all the tables you can use.
   2. All available tables will be accessible and you can select the specific table of the record you want to update.
2. Add Field
   By clicking the “Add field” button, you can choose how to fill in the various fields in the record you are creating. You can add one field or more to the activity definitions.
   1. Choose from the drop down the field you want to fill.
   2. Click on the empty field to the right of the drop down to be shown the tooltip with all the options you can use to fill the field.
   3. You can populate the field in one of the following options:
      • Static value – number / string. when using strings, you need to surround them with single quotes (‘).
      • Property value from a previous activity (Using the tooltip that opens when clicking on the field).
      • Expression – using functions and/or Previous activity properties and/or static values (Using the tooltip that opens when clicking on the field).
   4. Repeat steps 1-3 for all the fields you want to populate.

Update Record #

Table

1. Click on the empty field and the tooltip will pop up showing all the tables you can use.
2. All available tables will be accessible and you can select the specific table of the records you want to update.

1. Add Field
   By clicking the “Add field” button, you can choose how to fill in the various fields in the record you are creating. You can add one field or more to the activity definitions.
   1. Choose from the drop down the field you want to fill.
   2. Click on the empty field to the right of the drop down to be shown the tooltip with all the options you can use to fill the field.
   3. You can populate the field in one of the following options:
      • Static value – number / string. when using strings, you need to surround them with single quotes (‘).
      • Property value from a previous activity (Using the tooltip that opens when clicking on the field).
      • Expression – using functions and/or Previous activity properties and/or static values (Using the tooltip that opens when clicking on the field).
2. Add Filter
   By clicking the “Add filter” button, You can specify which records will be returned from the get results. You can add one filter or more to the activity definitions.
   1. Choose from the drop down the field you want to filter by.
   2. Select the condition you want the record to meet for the selected field.
   3. Click on the field to show the tooltip with all the options you can choose.
   4. You can populate the field in one of the following options:
      • Static value – number / string. when using strings, you need to surround them with single quotes (‘).
      • Property value from a previous activity (Using the tooltip that opens when clicking on the field).
      • Expression – using functions and/or Previous activity properties and/or static values (Using the tooltip that opens when clicking on the field).

Delete Records #

1. Table
   1. Click on the empty field and the tooltip will pop up showing all the tables you can use.
   2. All available tables will be accessible and you can select the specific table of the record(s) you want to delete.
2. Add Filter
   By clicking the “Add filter” button, You can specify which records will be returned from the get results. You can add one filter or more to the activity definitions.
   1. Choose from the drop down the field you want to filter by.
   2. Select the condition you want the record to meet for the selected field.
   3. Click on the field to show the tooltip with all the options you can choose.
   4. You can populate the field in one of the following options:
      • Property value from a previous activity (Using the tooltip that opens when clicking on the field).
      • Expression – using functions and/or Previous activity properties and/or static values (Using the tooltip that opens when clicking on the field).
      • Static value – number / string. when using strings, you need to surround them with single quotes (‘).

Create Batch of Records #

This activity iterates over a selected data list and for each record in the data list create records for a designated table corresponding to the specified data list.

1. Data List
   Click on the empty field next to the data list label and the tooltip will popup showing only previous activities than contains data lists from which you can choose. Choose a data list to iterate on.
2. Table
   1. Click on the empty field and the tooltip will pop up showing all the tables you can use.
   2. All available tables will be accessible and you can select the specific table for the new records you create.
3. Add Field
   By clicking the “Add field” button, you can choose how to fill in the various fields in the record you are creating. You can add one field or more to the activity definitions.
   1. Choose from the drop down the field you want to fill.
   2. Click on the empty field to the right of the drop down to be shown the tooltip with all the options you can use to fill the field.
   3. You can populate the field in one of the following options:
      • Static value – number / string. when using strings, you need to surround them with single quotes (‘).
      • Property value from a previous activity (Using the tooltip that opens when clicking on the field).
      • Expression – using functions and/or Previous activity properties and/or static values (Using the tooltip that opens when clicking on the field).
   4. Repeat steps 1-3 for all the fields you want to populate.

Get Records Batch #

This activity iterates over a selected data list and for each record in the data list retrieves records from a designated table corresponding to the specified data list.

1. Data List
   Click on the empty field next to the data list label and the tooltip will popup showing only previous activities than contains data lists from which you can choose. Choose a data list to iterate on.
2. Table
   • Click on the empty field and the tooltip will pop up showing all the tables you can use.
   • All available tables will be accessible and you can select the specific table of the record(s) you want to get.
3. Top
   You can set the number of rows you want to retrieve from the table or view. This parameter limits the result set to the specified number of records, with the default being 100.
4. Add Sorting
   You can specify sorting criteria for the retrieved data. Sorting helps you order the records based on specific columns, such as ascending or descending order.
5. Add Filter
   By clicking the “Add filter” button, You can specify which records will be returned from the get results. You can add one filter or more to the activity definitions.
   • Choose from the drop down the field you want to filter by.
   • Select the condition you want the record to meet for the selected field.
   • Click on the field to show the tooltip with all the options you can choose.
   • You can populate the field in one of the following options:
     • Static value – number / string. when using strings, you need to surround them with single quotes (‘).
     • Property value from a previous activity (Using the tooltip that opens when clicking on the field).
     • Expression – using functions and/or Previous activity properties and/or static values (Using the tooltip that opens when clicking on the field).

Update Batch of Records #

• This activity iterates over a selected data list and for each record in the data list updates records from a designated table corresponding to the specified data list.

1. Data List
   Click on the empty field next to the data list label and the tooltip will popup showing only previous activities than contains data lists from which you can choose. Choose a data list to iterate on.
2. Table
   • Click on the empty field and the tooltip will pop up showing all the tables you can use.
   • All available tables will be accessible and you can select the specific table of the record(s) you want to update.
3. Add Field
   By clicking the “Add field” button, you can choose how to fill in the various fields in the record you are creating. You can add one field or more to the activity definitions.

• Choose from the drop down the field you want to fill.
• Click on the empty field to the right of the drop down to be shown the tooltip with all the options you can use to fill the field.
• You can populate the field in one of the following options:
  • Static value – number / string. when using strings, you need to surround them with single quotes (‘).
  • Property value from a previous activity (Using the tooltip that opens when clicking on the field).
  • Expression – using functions and/or Previous activity properties and/or static values (Using the tooltip that opens when clicking on the field).
• Repeat steps 1-3 for all the fields you want to populate.

Execute Customized SQL #

The “Execute Customized SQL” activity is an activity that allows you to interact with a IBM DB2 system by running custom SQL queries or commands within your workflow.

In the SQL text field, you can write and enter your own SQL queries, which are structured commands that define what you want to do with the data in the IBM DB2 system. These queries can include operations like data retrieval, modification, or database management.

Execute Procedure #

The “Execute Procedure” activity is an activity that allows you to interact with a IBM DB2 system by running stored procedures within your workflow.

1. Procedure Name
   You need to specify the name of a stored procedure that exists within your IBM DB2 database. A stored procedure is a pre-defined, reusable set of IBM commands that are stored in the database and can be executed on demand.
2. Add Field
   By clicking the “Add field” button, you can add fields or parameters to provide input to the procedure you. You can add one field or more to the activity definitions.
   1. Choose from the drop down the field you want to fill.
   2. Click on the empty field to the right of the drop down to be shown the tooltip with all the options you can use to fill the field.
   3. You can populate the field in one of the following options:
      • Static value – number / string. when using strings, you need to surround them with single quotes (‘).
      • Property value from a previous activity (Using the tooltip that opens when clicking on the field).
      • Expression – using functions and/or Previous activity properties and/or static values (Using the tooltip that opens when clicking on the field).