Trying to run an automated cron job to a bulk insert

[sagarcasm]

Hi team,

Could you help me to understand what is the best way to do to bulk insert on the db2 database. We are using the below piece of code for inserts, but it takes horrendous amount of time even to load 500 records which is not efficient in our case.

async reConnectIfClose() {
    if (!this.db2conn.connected) {
      this.db2conn = await ibmdb.openSync(DB2CONNECTSTRING);
    }
  }

  async executeNonQuery(sql, data) {
    let result;
    let resultdata;
    let stmt;
    try {
      await this.reConnectIfClose();
      stmt = this.db2conn.prepareSync(sql);
      result = await stmt.executeSync(data);
      resultdata = result.fetchAllSync({ fetchMode: 3 }); // Fetch data in Array mode.
      return resultdata;
    } catch (error) {
      log.error('executeNonQuery:', error);
      await this.db2conn.rollbackTransactionSync().catch((rberror) => log.error(rberror));
      throw error;
    } finally {
      await result.closeSync();
      await stmt.closeSync();
    }
  }

Could you us suggest a vey efficient way of doing a batch inserts ?

[bimalkjha]

@sagarcasm You can prepare the insert statement once. Then do bind and execute for every row being inserted as being done by for loop in test file https://github.com/ibmdb/node-ibm_db/blob/master/test/bench-prepare-bind-execute-closeSync.js Alternatively, you can create an array of column data of size 20 or 50 and do an array insert as shown in test file https://github.com/ibmdb/node-ibm_db/blob/master/test/test-array-insert.js . In your shared program, you are doing prepare, execute and fetch for every row. Which is inefficient for insert. If you want to use your current program then preparesync should be called only once and just run executesync with data for every row. then close the result and stmt. no need to call fetch. Thanks.

[bimalkjha]

@sagarcasm Hope you are able to make progress. Let us know otherwise. Thanks.

[markddrake]

The code example in this issue shows an array insert https://github.com/ibmdb/node-ibm_db/issues/883. The technique shown works perfectly for all data types except the data types shown, eg the combination of BINARY, VARBINARY, CHAR FOR BIT and BLOB data types and a Javascript Buffer.

[bimalkjha]

ibm_db@3.1.0 has fixed this issue. Thanks.