Re: How do the records retrieve from data base when using sql statements?

Just use an order by clause. As Chuck said, SQL does not specify which
order records will be returned in unless you use an order by clause.

Even if you have a cluster index for Sybase are you sure that records are
guaranteed to be returned in the correct order in you don't specify an order
by clause?

You can do a RGZPFM command on an AS/400 table and specifically tell it to
put the physical records in the same sequence as a specified key. This is
good until the next record is inserted, but combined with an order by clause
it will probably improve performance even if some records are out of
sequence. And if you have the machine time, you can re-do the RGZPFM on a
regular basis to keep your data tuned.

Bear in mind that organzing it physically one way may impact performance for
other queries. And the usual caveat: It depends on how many records in the
file and how many you are retrieving.

(I admit to mild curiosity about why you don't seem to want to specify an
order by. Or maybe you have a serious performance problem that you are
trying to solve. If so, I'm mildy curious about what it is.)

Sam

"AMOS" <am12348@xxxxxxxxx> wrote in message
news:cdd5d2d8-c52a-4097-ad5e-de2bc73623e4@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On 23 ????, 06:54, "Hau...@xxxxxxxxxxxxxxx" <Hau...@xxxxxxxxxxxxxxx>
wrote:
In either way avoid specifying any DDS described logical file in an
SQL statement.

For more information about indexing read the following White Paper:
Indexing and statistics strategies for DB2 for
i5/OShttp://www-03.ibm.com/servers/enable/site/education/abstracts/indxng_...

Birgitta

Hello Brigitta,Sam and Chuck,

Thank you for your useful explanations

Upon reading your responses, the data in physical files are not
necessarily located
according to the keys values sequence. Therefore the optimizer does
not use them necessarily in order to retrieve the records.

I'm working with Sybase too. I know that you can define cluster
indexes on tables.
This means that the keys are located continuously and every "insert"
operation reorganize them again.

My question is, if there is a possibility to define keys for physical
files in AS400 in such way.

Thank you,
Amos


.

Relevant Pages

  • Re: Can I create an index on a view?
    ... Using AQUA DATA STUDIO with a connection to our iSeries, ... view using SQL and I am trying to create an index on this view using ... No. although you can specify a view in the from clause of a select ...
    (comp.sys.ibm.as400.misc)
  • Re: SQL Anfrage zu Es existiert genau ein ....
    ... Wer hat dir denn diesen Unsinn erzählt? ... Specify a grouped table derived by the application of the <group by ... clause> to the result of the previously specified clause. ... The following restrictions apply for Intermediate SQL: ...
    (microsoft.public.de.sqlserver)
  • Re: Registry Key Replication Kills Performance
    ... You should only specify ... > those keys which are used by your service (service documentation or vendor ... > Microsoft Cluster Server ... >> lag when you click on a menu item before that item actually displays. ...
    (microsoft.public.windows.server.clustering)
  • Re: BLOCK CONTAINS
    ... program that does not have the BLOCK CONTAINS clause. ... specify the BLOCK CONTAINS clause, because it has no meaning to VSAM). ... "It is not mandatory to code DCB parameters in JCL while creating new QSAM ... There are three source for DCB information, Program, JCL, and File Label, ...
    (comp.lang.cobol)
  • Re: deleting rows based on other tables
    ... I guest you just totally misunderstood a very simple question. ... Not knew to Database or Structured Query Language. ... We do everything in SQL with keys, which you did not show us. ... > 1) Does not allow the DELETE FROM clause to be abbreviated to DELETE. ...
    (microsoft.public.sqlserver.programming)