IBM DB2

Overview

IBM DB2 step is used to access IbmDb and make basic queries on the database (inserting, selecting, updating and deleting)

Configuration

• User Name - user name to access the Oracle Db, we recommend consuming it as workspace variable e.g. {{$env.DB_USER}})
• Password - password for accessing the database, we recommend consuming it as workspace variable e.g. {{$env.DB_PASSWORD}})
• Connection string - the connection string to connect to database (the recommended way of usage would be to specify a global connection string and access it here through e.g. {{$env.DB_CONNECTION}})

Actions

Actions allows creating SQL queries that need to be run when the step is executed.

• SQL - SQL statement to execute on the database (e.g. SELECT id FROM users WHERE email = :email)
• Binds - Array of variables that need to be inserted into query (e.g. [ { "ParamType": "ARRAY", "DataType": 1, "Data": [1, 2, 3 ,4, 5] }])
• Options - Object of query options
• Target path - doc node on which to place the query results

note

See this documentation to know more about binding params, Ibm DB2 documentation

Code page & locale

It is possible to configure the DB2 code page and locale (system-wide only) by configuring flow runner environment variables LANG and DB2CODEPAGE. For code page and locale options, refer to db2 documentation.

Examples

Create table

Create table Flow Example

{
  "id": "6c725e42-088a-4d90-9a73-7c45d2d06dac",
  "name": "ibmDbDemoCreateTableFlow",
  "flow": {
    "name": "ibmDbDemoCreateTableFlow",
    "description": "",
    "steps": [
      {
        "stepType": "ibm-db2",
        "color": "rgb(95, 206, 165)",
        "displayName": "IbmDB",
        "isSelected": true,
        "config": {
          "actions": [
            {
              "sql": "CREATE TABLE person(user_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,name VARCHAR(150) NOT NULL,age INT NOT NULL);",
              "binds": [],
              "options": {},
              "targetPath": "result.y"
            }
          ],
          "connectionString": "DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP",
          "user": "db2inst1",
          "password": "password"
        },
        "textColor": "red"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2022-03-15T10:35:04.991Z",
  "tags": ["general"],
  "inputSchemaLabel": "",
  "version": 1
}

Result

{
  "doc": {
    "result": {
      "y": []
    }
  },
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "ibm-db2",
        "executionTime": 126
      }
    ],
    "executionTimeOfFlow": 128,
    "timeMetric": "ms"
  }
}

Insert

IbmDb Insert Flow Example

{
  "id": "10cd3795-a3c0-41de-82a7-ebb0abcf7f27",
  "name": "ibmDbInsertDemo",
  "flow": {
    "name": "ibmDbInsertDemo",
    "description": "",
    "steps": [
      {
        "stepType": "ibm-db2",
        "color": "rgb(95, 206, 165)",
        "displayName": "IbmDB2",
        "isSelected": true,
        "config": {
          "actions": [
            {
              "sql": "insert into person values (?, ?, ?)",
              "binds": [
                {
                  "ParamType": "ARRAY",
                  "DataType": 1,
                  "Data": [1, 2, 3, 4, 5]
                },
                {
                  "ParamType": "ARRAY",
                  "DataType": 1,
                  "Data": [
                    "Person 1",
                    "Person 2",
                    "Person 3",
                    "Person 4",
                    "Person 5"
                  ],
                  "Length": 8
                },
                {
                  "ParamType": "ARRAY",
                  "DataType": 1,
                  "Data": [14, 25, 36, 47, 58]
                }
              ],
              "options": {
                "ArraySize": 5
              },
              "targetPath": "result.y"
            }
          ],
          "connectionString": "DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP",
          "user": "db2inst1",
          "password": "password"
        },
        "textColor": "red"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2022-03-24T13:38:55.036Z",
  "tags": ["general"],
  "inputSchemaLabel": "",
  "version": 1
}

Flow Result

{
  "doc": {
    "result": {
      "y": []
    }
  },
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "ibm-db2",
        "executionTime": 16
      }
    ],
    "executionTimeOfFlow": 18,
    "timeMetric": "ms"
  }
}

Select

Select flow exaple

{
  "id": "e69eac4f-ea45-447f-a706-11b797b74646",
  "name": "ibmDemoSelectFlow",
  "flow": {
    "name": "ibmDemoSelectFlow",
    "description": "",
    "steps": [
      {
        "stepType": "ibm-db2",
        "color": "rgb(95, 206, 165)",
        "displayName": "IbmDB2",
        "isSelected": true,
        "config": {
          "actions": [
            {
              "sql": "select * from person",
              "binds": [],
              "options": {},
              "targetPath": "result.rows"
            }
          ],
          "connectionString": "DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP",
          "user": "db2inst1",
          "password": "password"
        },
        "textColor": "black"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2022-03-25T06:23:42.664Z",
  "tags": ["general"],
  "inputSchemaLabel": "",
  "version": 1
}

Result

{
  "doc": {
    "result": {
      "rows": [
        {
          "USER_ID": 1,
          "NAME": "Person 1",
          "AGE": 14
        },
        {
          "USER_ID": 2,
          "NAME": "Person 2",
          "AGE": 25
        },
        {
          "USER_ID": 3,
          "NAME": "Person 3",
          "AGE": 36
        },
        {
          "USER_ID": 4,
          "NAME": "Person 4",
          "AGE": 47
        },
        {
          "USER_ID": 5,
          "NAME": "Person 5",
          "AGE": 58
        }
      ]
    }
  },
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "ibm-db2",
        "executionTime": 80
      }
    ],
    "executionTimeOfFlow": 83,
    "timeMetric": "ms"
  }
}

Update

IbmDb update example

{
  "id": "62429c55-aa15-4fe6-ae80-f46ca4a90b2e",
  "name": "ibmDemoUpdateFlow",
  "flow": {
    "name": "ibmDemoUpdateFlow",
    "description": "",
    "steps": [
      {
        "stepType": "ibm-db2",
        "color": "rgb(95, 206, 165)",
        "displayName": "IbmDB2",
        "isSelected": true,
        "config": {
          "actions": [
            {
              "sql": "update person set name='updated name' where USER_ID=1",
              "binds": [],
              "options": {},
              "targetPath": "result.result"
            }
          ],
          "connectionString": "DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP",
          "user": "db2inst1",
          "password": "password"
        },
        "textColor": "black"
      }
    ]
  },
  "inputSchema": {},
  "createDate": "2022-03-25T06:49:19.047Z",
  "tags": ["general"],
  "inputSchemaLabel": "",
  "version": 1
}

Result

{
  "doc": {
    "result": {
      "result": []
    }
  },
  "errors": [],
  "performance": {
    "steps": [
      {
        "step": "ibm-db2",
        "executionTime": 10
      }
    ],
    "executionTimeOfFlow": 14,
    "timeMetric": "ms"
  }
}