VALUES queries result in SQL1224 error

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Attempt to execute a query with the VALUES statement
2. Watch it die!

What is the expected output? What do you see instead?
The expected output is the result of the VALUES statement

What version of the product are you using? On what operating system?
ibm_db version 1.01 under Python 2.6 on 64-bit Linux 2.6.31 with DB2 
Enterprise Edition 9.7 FP1

Please provide any additional information below.
Here's a simple script to reproduce the error:

#!/usr/bin/env python
import ibm_db_dbi
conn = ibm_db_dbi.connect('SAMPLE')
cur = conn.cursor()
cur.execute('VALUES CURRENT DATE')
# ^-- Dies here for me
cur.fetchall()

I've tested a fairly wide variety of queries involving VALUES (including 
wrapped in a SELECT, wrapped in a CTE, joined with other tables, and 
querying SYSIBM.SYSDUMMY1 which is actually a VALUES statement wrapped in a 
view), and they all fail.

Original issue reported on code.google.com by wavefor...@gmail.com on 31 Mar 2010 at 9:26

[GoogleCodeExporter]

Thanks dave for reporting this issue, this one is reproducible for me. 
And i am currently analyzing this issue. 

Original comment by rahul.pr...@in.ibm.com on 1 Apr 2010 at 12:33

• Changed state: Accepted

[GoogleCodeExporter]

It's just occurred to me that Andrey's recently reported issue on the forum 
(http://groups.google.com/group/ibm_db/browse_thread/thread/c8085b44e3a9ec7f?hl=
en) 
is probably the same as this (it's the same SQLSTATE and he's querying 
SYSIBM.SYSDUMMY1 which, as I observed above, is actually a VALUES query wrapped 
in a 
view). If that's the case then I suspect that it's *not* a CLI error specific 
to 
Express-C (as postulated in this post: 
http://groups.google.com/group/ibm_db/msg/2e17c8d8a12903b8?hl=en) partly 
because I'm 
using Enterprise instead of Express-C, but also because all the queries I've 
tried 
work fine from the db2 command line (which I believe uses CLI) but not from 
ibm_db. 
My hunch is that it's a bug in ibm_db, but I haven't got any time to go digging 
at 
the moment unfortunately.

Original comment by wavefor...@gmail.com on 1 Apr 2010 at 6:18

[GoogleCodeExporter]

You didn't get error through db2 command line because from there you haven't set
cursor attribute to static. 
The same cli script(which is on
http://groups.google.com/group/ibm_db/msg/2e17c8d8a12903b8?hl=en) is able to
regenerate this problem on Enterprise version also. It works fine with other 
version
of db2(v97 fp0).
you can rise a ticket for this issue on db2. A customer generated ticket have 
higher
priority than internal ticket. 

Original comment by rahul.pr...@in.ibm.com on 6 Apr 2010 at 8:31

[GoogleCodeExporter]

Summary in addition to above - 
  When Rahul tested earlier and said only reproducible on Express-C and not ESE
version, he had tested with ESE v97 fp0 (passes fine) and Express-C v97 fp1 
(crash).
He hadn't explicitly tested on ESE v97 fp1. When u reported this, it was tested 
on
ESE v97 fp1 (hang). Thus the issue is a v97 fp1 issue.

Original comment by mario.br...@gmail.com on 7 Apr 2010 at 6:04

[GoogleCodeExporter]

can you please give me the db2level which you are using.

Original comment by rahul.pr...@in.ibm.com on 7 Apr 2010 at 10:31

[GoogleCodeExporter]

Output from db2level is as follows:

DB21085I  Instance "db2inst1" uses "64" bits and DB2 code release "SQL09071" 
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23034", and Fix Pack 
"1".
Product is installed at "/opt/ibm/db2/V9.7".

And thanks for the info on the static cursor being part of the cause - that 
definitely explains why I couldn't reproduce it with the other interfaces.

Original comment by wavefor...@gmail.com on 7 Apr 2010 at 12:12

[GoogleCodeExporter]

I contacted with the db2 server team. They already fixed it in DB2 v97 FP2. 
So, with official release of DB2 v97 fp2 you will get it fixed. 

If you have any problem with this then let me know.

And for more information about fp2 release, please contact IBM technical 
support.

Original comment by rahul.pr...@in.ibm.com on 20 Apr 2010 at 8:24

[GoogleCodeExporter]

Original comment by rahul.pr...@in.ibm.com on 9 May 2010 at 1:56

• Changed state: Done