IIB - passing more than one row (e.g. using a multi-dimensional array) to the database using the PASSTHRU function

Currently, when we want to INSERT several rows on a table using parameter markers in a single (batch) operation with IIB PASSTHRU function:
[PASSTHRU function]
https://www.ibm.com/support/knowledgecenter/en/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ak05890_.htm

we have to use the following syntax (in this example, to insert 5 rows):

INSERT INTO [schema].[table] (C1,C2,C3,C4) VALUES (?,?,?,?),(?,?,?,?),(?,?,?,?),(?,?,?,?),(?,?,?,?)

Although this works, we think that the best way to insert multiple rows at a time when using parameter markers should be to use this syntax (irrespective of the number of rows to insert):

INSERT INTO [schema].[table] (C1,C2,C3,C4) VALUES (?,?,?,?)

and then provide the exact values to be used for the multiple rows to be inserted using a bidimensional array (one dimension for each row and the other one for the column values) or similar structure.
Using this SQL syntax will reduce the number of different statements that need to be prepared and stored in the database and the integration node irrespective of how many rows you want to insert in a single execution.

This can be seen in the following article (by one of the best IBM Db2 experts) in the case of Db2 (see the section: "Insert multiple rows at a time"):
[Tips for getting the best INSERT performance]
http://www.idug.org/p/bl/et/blogaid=602

If you're like most people and use JDBC or ODBC, then it's a little different. The SQL only specifies one row, but you use ODBC / JDBC constructs to tell the driver how many rows' worth of data you want to push down in a single execution.


/* Multiple rows at a time in ODBC */
PreparedStatement SQLCHAR *stmt = (SQLCHAR *) "INSERT INTO t(c1, c2) VALUES(?, ?)";
SQLSMALLINT parameter1[] = { 121, 131, 141 };
char parameter2[][20] = { "Toronto", "Vancouver", "Ottawa" };
int row_array_size = 3;
:
cliRC = SQLPrepare(hstmt, stmt, SQL_NTS);
:
cliRC = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER) row_array_size, 0);
:
cliRC = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SHORT, SQL_SMALLINT, 0, 0, parameter1, 0, NULL);
cliRC = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 20, 0, parameter2, 20, NULL);
cliRC = SQLExecute(hstmt);

/* Multiple rows at a time in JDBC */
PreparedStatement prepStmt = con.prepareStatement( "INSERT INTO t VALUES (?,?)");
for ... object : objects ... {
prepStmt.setInt (1,object.parameter1());
prepStmt.setString(2,object.parameter2();
prepStmt.addBatch ();
}
int [] numUpdates=prepStmt.executeBatch(); /* Generally you would do this periodically in the loop too */


However, this is not possible when using IIB PASSTHRU function, as it was explained by L3 on the associated PMR.
Note: we've seen this limitation when dealing with SQL Server, but we think the same applies to other database managers.