Need count when doing OPEN

[nxm150]

I was wondering if there was a field that contains the number of rows that will be in my FETCH when I do an OPEN. I know SQLERRD(3) contains the count but only for a UPDATE, DELETE, or INSERT. But not for a FETCH. Any help would be appreciated.

 

[MarcLodge]

Hi nxm150,
there's not a variable that I know of, although I'll continue to look. You can of course perform a SELECT COUNT(*) beforehand (as I'm sure you're aware), but it would be nice if the count was held somewhere. It should be really, as the results are stored in an intermediate results tables, but I cannot find any reference to be able to access that information.
Marc

 

[nxm150]

Yes, I do realize about SELECT COUNT(*) but I was trying to avoid doing that if I could grab the value from a OPEN statement. Thanks for any help you can provide!

 

[nxm150]

I talked to about 10 people at my work and no one could come up with an answer.

 

[MarcLodge]

What platform and language are you using? There's only one other possible way I can think of, although it's even more messy than SELECT COUNT(*) in my opinion. You could DECLARE GLOBAL TEMPORARY TABLE and then INSERT the rows using your SELECT from your OPEN CURSOR. In theory this should give you the number of rows in SQLERRD(3), although you've then got to open and fetch the temporary table so we've not really gained anything. I'm not sure what you'd get in SLQERRD(3) if you DECLARE GLOBAL TEMPORARY TABLE AS SELECT blah blah blah. I've not had the chance to try any of these out as I'm at home now and away from DB2. If you want a bit more info on the GLOBAL TEMPORARY TABLE, try this link:

http://www-3.ibm.com/cgi-bin/db2www...ort?last_page=list.d2w&fn=db2v7s0decgtemp.htm

I'm more than a bit dubious about it all to be honest, but it's conceivable I guess.

I'd be intested to know if you turn anything up.

Marc

 

[nxm150]

Marc, I am using COBOL/DB2 on the mainframe. What you say sounds like it will work, from what I have read. I just coded a SELECT COUNT(*). I am coding varying list dynamic SQL and did not want to add more code to my program. I used fixed list dynamic SQL for the SELECT COUNT. Thanks for your input!

 

[gregsimpson]

nxm150,

I think the solution you have accepted from Marc is by far your best bet. I believe the reason that DB2 doesn't give you the value require on opening the CURSOR is because it doesn't necessarily know how many rows. Whenever you open a CURSOR, DB2 may or may not materialise a result set. Almost certainly it will if the result set needs sorting. However with parameters such as OPTIMIZE for 1 ROW or FETCH FIRST XX ROWS ONLY, the optimiser is more likely to choose an access path where it gets the next record as you do the next fetch rather than materialising the full result set up front. Clearly this being the case DB2 is unable to return you the value with out effectively performing internally the suggestion Marc has given you. It would be inefficient to always do this, as not every application using a Cursor requires this information.