Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Execute multiple SQL statements

Posted on 2004-10-29
Medium Priority
Last Modified: 2012-05-05
In my C++ app, I use ODBC to access one of three different DBMS systems (MsSQL 2K, Sybase, and IBM DB2 v8.1).  I have a handy function I wrote that executes a SQL statement:

    SqlPrintfExec( "UPDATE Idxs SET nNextIdx=(nNextIdx+1)" );

I have another that makes a query and obtains a single data value:

    nInqIdx= SqlPrintfGetInt( "SELECT nNextIdx FROM Idxs" );

As you can see, I am trying to update a single field in a single record so that I can generate sequential unique index numbers that are used elsewhere.  

Using MsSQL, I can combine the two statements by separating them with a semicolon:

    sSql= "UPDATE Idxs SET nNextIdx=(nNextIdx+1); SELECT nNextdx FROM Idxs";
    nNextIdx= SqlPrintfGetInt( sSql );

I can't figure out how to do this with IBM DB2.   I'm needing to do it in two steps:

    SqlPrintfExec( "UPDATE Idxs SET nNextIdx=(nNextIdx+1)" );
    nInqIdx= SqlPrintfGetInt( "SELECT nNextIdx FROM Idxs" );

That means that is is *possible* that a secondary thread or process could mess me up... a different UPDATE could be executed before the SELECT.

Do you have any ideas on how to do this (an UPDATE immediatly followed by a SELECT) with IBM DB2 in a single statement?

With MsSQL, I "batch execute" large blocks of SQL (eg, 32K of INSERT statements) using the same technique (semicolons separate the statements).  This is VERY efficient -- it make my code run about 100 times faster.  But I can't make IBM DB2 do the same thing ... I must make separate calls to execute each INSERT.

Your expert help -- exspecially tried and tested solutions as opposeed to random untested suggeestions -- will be much apperciated.  Thanks!

-- Dan
Question by:DanRollins
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
LVL 13

Expert Comment

ID: 12448556
No matter what DB you are using, the DB engine can only execute one statement (operation) at a time, so even if in mySQL you can combine the statement in one string, the engine executes them sequentially and not concurrently.
I think you are forgetting that the DB2 concurrency model can prevent secondary threads from updating the same record, for example if you use select some column from some table for update of a column then you effectively lock that record and prevent other users from updating it. In addition, update statements impose U locks on the rows, and U locks are not compatible with other U locks from other users.

So, change you select statement (if your IBM UDB is not running in RS isolation level and is at least v7.2) to
select.....from table..... FOR UPDATE OF .... WITH RS, the result is the row or rows are now locked and no other user can update the row(s).
You can easily see this at the command line by opening two sessions, select in one session and try and update in the other.

Expert Comment

ID: 12464314
Another suggestion is to have DB2 automatically generate the unique number for you. This ensures the number is always unique, and can be safely retrieved after the insert without worrying about concurrency. This takes the headache out of sequencing statements and worrying about the isolation level.
Of course, this only works for inserts. I'm not sure why you are doing this as part of an update - are you creating the record first without a sequence number, then setting it afterwards?

If you need something more complex, or require a specific format for your unique identifier, then ghp7000's method is the way to go.
LVL 49

Author Comment

ID: 12466263
How does the FOR UPDATE OF clause work?  That is, how can it tell that the update is complete and that the record should be unlocked?  For instance, what if I never update the specified table?

>> ... have DB2 automatically generate the unique number for you.

The database should probably have been designed with an automatically-generated (auto-increment) index value for the table, but when the project started, concurrency was not an issue -- we assumed that only one process would be adding new records, so we just used MAX(nIdx)+1 in the INSERT statement.

Then we went with this strategy of using a separate table to track the current highest index -- and that works perfectly for the other two DBMSes becasue I get back the new value with the SELECT which is executed atomically with the UPDATE.

What I need is a way to get back the new (next) index value that is certain to be unique.

But that is only part of the the larger problem... I am stuck with this "one-SQL-statement-per-call" limitation when using IBM DB2 and it is cramping my style :-)  I'm thinking there must be a simple way to concatenate SQL statements, but I can't seem to make it happen.

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

LVL 13

Accepted Solution

ghp7000 earned 1600 total points
ID: 12467450
the update statement is complete with you issue a commit or rollback statement, this will generate sqlcode 0 (zero). When your application receives sqlcode 0, you know the update is complete, that is committedor rolled back)

if you want to use auto increment, create a sequence (db2 create sequence statement) and reference the next highest sequence value with values(nextval)

lol you can try until you are blue in the face, but db2 wont accept a concatated sql statement
LVL 49

Author Comment

ID: 12467607
I also thought of creating a simple Stored Procedure, but when I try, it seems to want to run a C++ or Java compiler.  

Looking into it, it appears that DB2 does not support the idea of a stored procedure consisting of just a couple of straight SQL statements.  Is that right or is the documentation just confiusing?  It is hard for me to understand because I was able to write TRIGGER procedures of just SQL statements with no problem...

Assisted Solution

pluim earned 400 total points
ID: 12467646
Locking the index should work, e.g.

.. process resultset, get value of next ID
.. nobody else can access the record right now
UPDATE Idxs SET nNextIdx=(nNextIdx+1)

If you never commit or rollback the transaction, the table remains locked until a specified interval. DB2 has a global timeout setting after which locks are released. That's the last recourse of course, since the application is stuck in the mean time.

If you cannot have DB2 generate the ID for you, there are ways to mitigate the deadlock problem. For example, you can have the application reserve identifiers in blocks of 25, 100, 1000 etc. Using the SQL above, get the next available ID, then update with a value of (nextID + 25). You now have 25 identifiers to use before having to lock the table again. Of course, you may end up with "gaps" because not all reserved identifiers are used.

LVL 49

Author Comment

ID: 12468855
That looks feasible...
In my situation, autocommit will be off only long enough to do the select after the update.

    SqlPrintfExec( "SetAutoCommit(off)" );
    SqlPrintfExec( "UPDATE Idxs SET nNextIdx=(nNextIdx+1)" );
    nInqIdx= SqlPrintfGetInt( "SELECT nNextIdx FROM Idxs" );
    SqlPrintfExec( "commit" );

Alas, SETAUTOCOMMIT does not seem to be a recognized SQL statement.
LVL 13

Expert Comment

ID: 12469707
to turn autocommit on or off:

from db2 command line processor:
db2 list command options
(this tells you the current settings)
if c=-c then
dbset db2options=+c
autocommit is now off for the databases running under the same instance

to create stored procedures, you need to have c++ compiler installed
then you can write simple stored procedures using simple SQL, see the create procedure statement for help
LVL 49

Author Comment

ID: 12510516
Well, I didn't find what I wanted (the FOR UPDATE OF clause usage still eludes me), but I guess I could use the CREATE SEQUENCE.


-- Dan

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question