RE: Toying about with SQL, and just wondering -- RPG400-L

Hi Cor

SQL processing uses an optimizer to determine the best way toretrieve the data (called the access method), based on timeestimates. This depends on a number of things, including whetherthere are indexes that can support the process, the number of recordslikely to be read, etc. Several access methods can be used, from atable scan to an index-only scan (very fast) to building a new indexto making a temporary copy.

The result set does not necessarily "exist" anywhere. And there areways to influence what access method will be selected by theoptimizer, including attributes of the select statement andparameters on the CRTxxx command. In your case, you want to encouragereturning the first set of records as quickly as possible, whichusually involves an index when relatively few rows are involved. Whenthere are a million records, you would still probably want to put anOPTIMIZE FOR clause on your SELECT statement.


I recommend several things as you explore these things:

1. Run your program after executing STRDBG - you will have messagesin the job log that explain what the optimizer did, withrecommendations to improve performance2. Go to www.iseries.ibm.com/db2 and rummage around - especiallyunder the Support tab there - you will also see a link there to the manuals

3. Use the block fetch as suggested by the other reply you received

4. Read the performance manual for SQL - "Database performance andquery optimization" athttp://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/rzajq.pdf- this describes access methods, performance suggestions, and how toanalyze performance.

Welcome to the black art of SQL - it is ideal for subfile processing,in my view, especially if you match your subfile page spec with thenumber of occurs in a multiple occurrence data structure in a block fetch.


Good luck
Vern

At 04:49 AM 11/8/2006, you wrote:

Thanks for the head-up, I appreciate it. However, that doesn't answer my
question (which is probably worded iffy):

In my view of the SQL at this moment the cursor is nothing but a method
of 'browsing' through the resultset. In order to browse the resultset
must be populated first. So when I ask for 1 or a number of rows from
the resultset I ask from a resultset which has been filled with
1.000.000 rows when opening my cursor, resulting in a number of seconds
(minutes, hours, depending on the speed of the machine) of waiting time
before the data is actually returned. My question did not concern the
method of getting data from the resultset, but concerns the filling of
the resultset. Basically I want to 'suspend' the filling of the
resultset until the data is actually needed for a limitted number of
rows (again to limit the waiting time for the user).

The question is the result of the yearlong working with 'semi'static
subfiles where the data is read into the subfile records on a page-down
(but only one page ahead).

Cheers,

Cor

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of McCully,
> Keith (RBS Insurance)
> Sent: woensdag 8 november 2006 11:33
> To: 'RPG programming on the AS400 / iSeries'
> Subject: RE: Toying about with SQL, and just wondering
>
> If you're ok with SQL cursors then you can use the FETCH
> statement to retrieve rows from a table.
>
> You can then add the multiple-row-fetch clause to control the
> size of the result set:
>
> FOR n ROWS INTO MyStructure
>
> Where: n is the integer number of rows that you want to load,
>
> MyStructure is (in RPG) a multi-occur data structure host
> variable formatted in accordance with the data retrieved from
> the table. The number of occurrences should be equal or
> greater than the number of rows retrieved.
>
> Note that if you want to capture the actual number of rows
> actually loaded (could be less than your chosen value for
> n)use the SQLERR3 (ROW_COUNT) from the included SQLCA Data Structure.
>
> Cheers,
>
> Keith
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> Sent: 08 November 2006 06:27
> To: RPG programming on the AS400 / iSeries
> Subject: Toying about with SQL, and just wondering
>
> *** WARNING : This message originates from the Internet ***
>
> Goodday fellow-listers,
>
> I am toying about with SQL, just because I am not too
> familiar with SQL as a database I/O method and I want to gain
> knowledge on this area.
> During this I was playing about with the various parts that
> can be filled by file I/O: header part, main screen and
> subfile. Now I think I have the idea about preparing
> statements and executing them, and even the part in which you
> 'connect' them to a cursor. But then I wondered:
> say that a file would produce a large result set (for the sake of
> argument: 1.000.000 rows), and I wanted to fill a subfile
> with just 10 rows at a time; do I have to wait until all
> 1.000.000 records have been placed in the result set before I
> can begin building the subfile? Or is it possible to fill the
> resultset with, say, 100 records at a time and continue this
> 'at will'?
>
>
> Kind regards,
>
> Cor Takken
>
>
>
> This e-mail and any attachment is for authorised use by the intended
> recipient(s) only. It may contain proprietary material,
> confidential information and/or be subject to legal
> privilege. It should not be copied, disclosed to, retained or
> used by, any other party. If you are not an intended
> recipient then please promptly delete this e-mail and any
> attachment and all copies and inform the sender. Thank you.
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L)
> mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
>
> The Royal Bank of Scotland plc, Registered in Scotland No.
> 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB
>
> Authorised and regulated by the Financial Services Authority.
>
> This e-mail message is confidential and for use by the
> addressee only. If the message is received by anyone other
> than the addressee, please return the message to the sender
> by replying to it and then delete the message from your
> computer. Internet e-mails are not necessarily secure. The
> Royal Bank of Scotland plc does not accept responsibility for
> changes made to this message after it was sent.
>
> Whilst all reasonable care has been taken to avoid the
> transmission of viruses, it is the responsibility of the
> recipient to ensure that the onward transmission, opening or
> use of this message and any attachments will not adversely
> affect its systems or data. No responsibility is accepted by
> The Royal Bank of Scotland plc in this regard and the
> recipient should carry out such virus and other checks as it
> considers appropriate.
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L)
> mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.