Nested subselect in INSERT with static SQL?

[stevexff]

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:

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.

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

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

Steve

[small]"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:erlDesignPatterns)[/small]

 

[stevexff]

Aaaargh!

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



Steve

[small]"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:erlDesignPatterns)[/small]

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[stevexff]

Frederico

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

Steve

[small]"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:erlDesignPatterns)[/small]

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[stevexff]

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

Steve

[small]"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:erlDesignPatterns)[/small]