INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Nested subselect in INSERT with static SQL?

Nested subselect in INSERT with static SQL?

(OP)
I've got a table which has columns customer_id, A, B, seq.

Customers can have multiples of A and B, and when this happens the seq gets incremented:

CODE

customer_id A B seq
1           1 1 1
1           1 1 2
1           2 1 1
1           2 2 1
To make this simple, I want to write an INSERT that will add a row with the next available sequence number.

CODE

insert into table (customer_id, A, B, seq)
   values(1, 1, 1,
      (select coalesce(max(seq), 0) + 1
         from table
         where customer_id = 1
         and A = 1
         and B = 1)
   )
This works fine in SPUFI, and does exactly what I want - if there is no row already there, the null gets coalesced to zero, and one is added giving a seq of 1. If the max() returns a value, it gets incremented and the insert proceeds with the next available seq.

But as soon as I try to run this through the COBOL precompiler using host variables, it barfs at the syntax of the nested subselect. I've tried all sorts of combinations including INSERT with a fullselect using host variables and a nested subselect for the seq but nothing works.

I'm getting to the point where I think it will be easier to just do a separate SELECT to get the value into a host variable, but that seems like a cop-out sad

There doesn't seem to be anything in the manual that says you can't do this - does anyone have any ideas please?

Steve

"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)

RE: Nested subselect in INSERT with static SQL?

(OP)
Aaaargh!

The source control system is still using the DB2 v7 precompiler. When I compile using the v8 version, it's fine...

banghead

Steve

"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)

RE: Nested subselect in INSERT with static SQL?

glad you found the problem.

Just as a hint for you and others that may see this, if something does not work with the pre-compiler, try it with dynamic SQL. although DB2 normally works fine with that (if you are on the correct pre compiler version), oracle for example is known for having problems with some SQL's on precompilers.
Dynamic SQL works around this restriction as the SQL is then processed at runtime by the database processor

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Nested subselect in INSERT with static SQL?

(OP)
Frederico

Thanks, but at the transaction rates I need to support, dynamic SQL isn't really an option for me.

Steve

"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)

RE: Nested subselect in INSERT with static SQL?

Steve,

Dynamic SQL is not as bad as it seems these days.

But it is its use that can make the difference.

This is generally speaking, for benefit of others.

Lets look at the usage of Dynamic SQL within a batch process.
First we need to to make clear that although it is defined as Dynamic SQL, it would be better if we just called it Dynamically Prepared SQL. This is because the SQL it self is not dynamic, e.g. does not change from one run to the other, but rather it is prepared once the first time it needs to be executed, and from that point on it behaves as if it was static SQL

Lets suppose a batch application that processes a input file with 10.000.000 rows.

logical processing would be

Using static SQL
while not eof
do
  read file
  move record variables to DB2 host variables
  execute SQL insert (SQL 1)
end


Using dynamicaly prepared SQL

if SQL not prepared
   prepare sql (SQL 3)
   set SQL prepared to true
end
while not eof
do
  read file
  move record variables to DB2 host variables
  execute SQL insert (SQL 2)
end


on the above we have 3 EXEC SQL END-EXEC statements
1- exec sql INSERT INTO TABLE X VALUES (:1,:2,:3) end-exec <--- this is the standard static SQL

2- exec sql prepare inserttablex from :sqlstring end-exec <--- this is dynamicaly prepared SQL

3- exec sql execute inserttablex using (:1,:2:3) end-exec <--- this is the execution of the prepared statement

where :1,:2,:3 are the host variables containing the values to insert, and :sqlstring is the SQL to prepare
where sqlstring = "insert into table x values (?,?,?)"

Now by looking at the above process, the dynamic SQL is prepared only once, and then executed the same number of times as the static one is. The time spent to prepare the SQL is minimal, and the impact on the runtime of the job will only depend on the number of rows to insert. if called to insert a single row, then yes the impact is significant over the overall run time, but if called to insert a significant number of rows, then the impact is null.

If the above is called from a online program, and the SQL program is not kept in memory, then yes the impact is big. but if it the calls are done through a online program that is always in memory, then the SQL will be prepared only once so again minimal impact.
 

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Nested subselect in INSERT with static SQL?

(OP)
Indeed. But I need to run between 500-1500 discrete transactions per second, so I can't afford the overhead of the prepares.

Steve

"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close