count of records in SQL recordset.

count of records in SQL recordset.

Post by Tony West » Fri, 11 Jul 2003 22:55:10



Hi There,

We use a Java swing application on the pc client, talking to an
ile-rpg program on our iseries over a permanent socket. Using this
approach, we can emulate the function of a 5250 green screen page by
page subfile; The user see's what appears to be a huge 100'000 record
table on the screen, however the only data that is transmitted across
the network is the actual 50 or so records that the table is
positioned to. The table positioning is controlled by a scrollbar.

To implement this, we have two types of message that handled by the
server:

BuildList - The server is instructed to build a recordset, and return
the count of records within this set. This count is used to correctly
configure the scrollbars maximum value.

GetlistBlock - the server is instructed to move the cursor to the
appropriate position within the recordset, and send a specific count
of records from that position back to the client.

When we code the BuildList handler, we have to.

A) Select count(*) from {whatever}                   (to get the
count)

B) Declare cursor c1 from {whatever} // open c1.        (to build the
recordset)

The problem is, the server has to calculate the records that match the
subset (A), and then has to do the same work, and take the same time
in building the cursor (B). In other words, the server has to do the
same database interrogation twice. Now, when the subset is a complex
select statement, that might take say 2 seconds to process the SQL, it
takes 4 seconds to return from our Buildlist function (far too long).
Also, we cann't guarantee that the count = qty of records in recordset
(records may have been added or deleted in the 2 seconds it took to
build the cound).

Does anyone know of anyway we can omit the (A) step. Is there any way
of getting the count of records directly from our declared cursor
rather from a seperate sql statement.

(ps, this is from within SQLRPGLE, I am aware that by using JDBC we
could get a count, but as we are not using any database logic at the
client side, we can not use this method.)

Thanks.

Tony.

 
 
 

count of records in SQL recordset.

Post by Kent Milliga » Sat, 12 Jul 2003 00:04:02


Quote:> Does anyone know of anyway we can omit the (A) step. Is there any way
> of getting the count of records directly from our declared cursor
> rather from a seperate sql statement.

No, the database doesn't know how many records will be returned until it hits
end of file.  

--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries

Quote:>>> www.iseries.ibm.com/db2

(opinions stated are not necessarily those of my employer)

 
 
 

count of records in SQL recordset.

Post by Thomas Ki » Sat, 12 Jul 2003 03:13:23


This may or may not help...

I have a somewhat analagous situation in an Oracle database where I
need a row count prior to returning results.

In one situation, I use an Oracle global temporary table to hold the
selected rows, then return the count from that table with no where
clause.  When the second query comes in, a cursor to the global
temporary table is returned, which does not require the original query
be re-fired.

An AS/400 version might be to build a table in QTEMP.

However, I have no idea what kind of overhead this will create on the
AS/400.


> Hi There,

> We use a Java swing application on the pc client, talking to an
> ile-rpg program on our iseries over a permanent socket. Using this
> approach, we can emulate the function of a 5250 green screen page by
> page subfile; The user see's what appears to be a huge 100'000 record
> table on the screen, however the only data that is transmitted across
> the network is the actual 50 or so records that the table is
> positioned to. The table positioning is controlled by a scrollbar.

> To implement this, we have two types of message that handled by the
> server:

> BuildList - The server is instructed to build a recordset, and return
> the count of records within this set. This count is used to correctly
> configure the scrollbars maximum value.

> GetlistBlock - the server is instructed to move the cursor to the
> appropriate position within the recordset, and send a specific count
> of records from that position back to the client.

> When we code the BuildList handler, we have to.

> A) Select count(*) from {whatever}                   (to get the
> count)

> B) Declare cursor c1 from {whatever} // open c1.        (to build the
> recordset)

> The problem is, the server has to calculate the records that match the
> subset (A), and then has to do the same work, and take the same time
> in building the cursor (B). In other words, the server has to do the
> same database interrogation twice. Now, when the subset is a complex
> select statement, that might take say 2 seconds to process the SQL, it
> takes 4 seconds to return from our Buildlist function (far too long).
> Also, we cann't guarantee that the count = qty of records in recordset
> (records may have been added or deleted in the 2 seconds it took to
> build the cound).

> Does anyone know of anyway we can omit the (A) step. Is there any way
> of getting the count of records directly from our declared cursor
> rather from a seperate sql statement.

> (ps, this is from within SQLRPGLE, I am aware that by using JDBC we
> could get a count, but as we are not using any database logic at the
> client side, we can not use this method.)

> Thanks.

> Tony.

 
 
 

count of records in SQL recordset.

Post by Tony West » Sat, 12 Jul 2003 16:31:08



> > Does anyone know of anyway we can omit the (A) step. Is there any way
> > of getting the count of records directly from our declared cursor
> > rather from a seperate sql statement.

> No, the database doesn't know how many records will be returned until it hits
> end of file.

I don't buy this!. If I declare a dynamic scroll cursor, I can jump to
the final record in the recordset very quickly by using:

fetch relative (maxrecnbr) into {whatrever}

or

fetch last into {whatever)

These sql directives execute almost instantly, there is no way that
the system is reading through the entire recordset looking for eof.
Also, with JDBC, it is possible to do ResultSet.last() followed by
ResultSet.getRow() to quickly retrieve the final row number. Is there
the equivalent to getRow() in SQLRPGLE??

thanks.

 
 
 

count of records in SQL recordset.

Post by Tony West » Sat, 12 Jul 2003 21:25:36


Well , I found this article here:
http://www.db2mag.com/db_area/archives/2002/q2/favero.shtml

if you scroll half way down, after the subheader 'BE VIGILANT', there
is described a neat feature about getting the number of rows from an
answer set. However, as far as I can see, this tip doesn't work on an
iSeries, is the DB2 implementation on the iSeries different to that on
the 390??....

Thanks.

 
 
 

count of records in SQL recordset.

Post by Drew Dekreo » Sun, 13 Jul 2003 02:55:57


It might: I know sqlerrd(3) definately returns the number of rows
updated/deleted. I've never tried (1) or (2)

> Well , I found this article here:
> http://www.db2mag.com/db_area/archives/2002/q2/favero.shtml

> if you scroll half way down, after the subheader 'BE VIGILANT', there
> is described a neat feature about getting the number of rows from an
> answer set. However, as far as I can see, this tip doesn't work on an
> iSeries, is the DB2 implementation on the iSeries different to that on
> the 390??....

> Thanks.

 
 
 

count of records in SQL recordset.

Post by Kent Milliga » Sun, 13 Jul 2003 06:40:32


Positioning to a particular row in a result set is different than fetching the
entire result set.   And "Quickly" would be query dependent.  

Yes DB2 on zSeries & DB2 on iSeries are two different engines.

--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries

Quote:>>> www.iseries.ibm.com/db2

(opinions stated are not necessarily those of my employer)