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

    tags:
      - general
    steps:
      - stepType: ibm-db2
        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
        name: IbmDB
        description: ""
        condition: ""
    sampleData: {}

Result

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

Insert

IbmDb Insert Flow Example

    tags:
      - general
    steps:
      - stepType: ibm-db2
        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
        name: IbmDB2
        description: ""
        condition: ""
    sampleData: {}

Flow Result

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

Select

Select flow exaple

    tags:
      - general
    steps:
      - stepType: ibm-db2
        config:
          actions:
            - sql: select * from person
              binds: []
              options: {}
              targetPath: result.rows
          connectionString: DATABASE=testdb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP
          user: db2inst1
          password: password
        name: IbmDB2
        description: ""
        condition: ""
    sampleData: {}

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

    tags:
      - general
    steps:
      - stepType: ibm-db2
        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
        name: IbmDB2
        description: ""
        condition: ""
    sampleData: {}

Result

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