Inserting multiple rows with DB2Command.ExecuteNonQuery

Question

I'm attempting to insert multiple rows into a DB2 database using C# code like this:

string query = 
"INSERT INTO TESTDB2.RG_Table (V,E,L,N,Q,B,S,P) values" +
"('abc', 'def', '2009-03-27 12:01:19', 'ghi', 'jkl', NULL, NULL, NULL)," +
"('abc', 'def', '2009-03-27 12:01:19', 'ghi', 'jkl', NULL, NULL, NULL)";

DB2Command cmd = new DB2Command(query, this.connection, this.transaction);

cmd.ExecuteNonQuery();

If I stop building the query string after the first set of values, it executes without an error. Attempting to load multiple values using this method results in the following error:

Upload error : ERROR [42601] [IBM][DB2] SQL0104N  
An unexpected token "," was found following "".  
Expected tokens may include:  "<END-OF-STATEMENT>".  SQLSTATE=42601

The SQL syntax matches that which I have read elsewhere and IBM's documentation gives this example:

cmd = conn.CreateCommand();
cmd.Transaction = trans;

cmd.CommandText =
"INSERT INTO company_a VALUES(5275, 'Sanders', 20, 'Mgr', 15, 18357.50), " +
"(5265, 'Pernal', 20, 'Sales', NULL, 18171.25), " +
"(5791, 'O''Brien', 38, 'Sales', 9, 18006.00)";

cmd.ExecuteNonQuery();

Can anyone explain what could account for this?

I can compose multiple Insert statements and execute them with one command as suggested by a now deleted answer - thank you - but I gain no performance improvement. Am I wrong to assume that inserting one hundred rows in one Insert command will take less time than executing one hundred Insert commands? — , Apr 17 '09 at 19:30
If the bottleneck is the time spent connecting to the DB and/or going through network/firewall then you will get a performance inprovement. But if the DB/2 database is the bottleneck the there is no performance inprovement. — Sigersted, Nov 19 '09 at 02:06
@Sigersted – Compound inserts are absolutely a way to improve performance. A single atomic statement will always be faster than multiple independent statements. — Ian Bjorhovde, Sep 13 '11 at 17:06

score 2 · Answer 1 · edited May 23 '17 at 11:54

I realize this is a really old question, but so far none of the answers have gotten to the real root of the problem. Since he can't use multiple VALUES clauses, I'm going to guess he's on z/OS, which doesn't support that. See my other answer here for more information.

There is an alternative, though, since he appears to be using .NET, which is the "Chaining" ability of the driver. This allows you to batch up multiple inserts/updates/deletes, and when you "end" your chain, the driver sends all of the statements at once. Here's some example code:

<!-- language: lang-cs --> //Code parser seems to be going crazy here...
public void InsertToDatabase(IEnumerable<Row> rows)
{
    using (var conn = new DB2Connection())
    using (var trans = conn.BeginTransaction())
    using (var cmd = conn.CreateCommand())
    {
        cmd.Transaction = trans;
        cmd.CommandText =
            "INSERT INTO company_a VALUES " +
            "(@field1,@field2,@field3,@field4,@field5,@field6)";

        conn.BeginChain();
        foreach (var row in rows)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@field1", row.Field1);
            cmd.Parameters.Add("@field2", row.Field2);
            cmd.Parameters.Add("@field3", row.Field3);
            cmd.Parameters.Add("@field4", row.Field4);
            cmd.Parameters.Add("@field5", row.Field5);
            cmd.Parameters.Add("@field6", row.Field6);
            cmd.ExecuteNonQuery();
        }
        conn.EndChain();
        trans.Commit();
    }
}

If DB2 throws any exceptions, you will only get it when you run EndChain, and they will all come at once.

score 0 · Answer 2 · answered Jul 20 '09 at 19:37

0

your syntax looks broadly correct.

does it work from the the cli?
have you taken a look at JDBC batched update. I think that you will find it nearly as fast., and a lot more readable and supportable.

answered Jul 20 '09 at 19:37

phatmanace

4,671
3
24
29

score 0 · Answer 3 · edited Apr 04 '13 at 14:25

I'm pretty sure that inserting mutliple rows doesn't work if you specify the rowlist; in this case

(V,E,L,N,Q,B,S,P)

To insert multiple rows you have to give the values in native order of the table's columns.

Try modifying your SQL to :

string query = "INSERT INTO TESTDB2.RG_Table values" +   
"('abc', 'def', '2009-03-27 12:01:19', 'ghi', 'jkl', NULL, NULL, NULL)," +
"('abc', 'def', '2009-03-27 12:01:19', 'ghi', 'jkl', NULL, NULL, NULL)";

score 0 · Answer 4 · edited Jul 18 '13 at 19:14

0

Did you try the insert thru command line?. Syntax seems to be correct as per this link.

edited Jul 18 '13 at 19:14

jh314

27,144
16
62
82

answered Apr 20 '09 at 15:02

kishore

407
3
10

The first line of the page to which the link point says, "You use an INSERT statement to specify criteria for inserting a SINGLE row of data into an existing database table." I understand that to mean that the statement can be used for only one row. That contradicts other documentation but matches my experience. Can you tell me what you mean by inserting through command line? Do you mean running a command line client application? I'm not an administer on the server. – Apr 20 '09 at 16:49

Inserting multiple rows with DB2Command.ExecuteNonQuery

4 Answers4