How do the records retrieve from data base when using sql statements? - IBM AS400

This is a discussion on How do the records retrieve from data base when using sql statements? - IBM AS400 ; Hello group, When I use a simple "READ FILE", the records are retrieved, according to the file index. If for example the key of the file(physical/logical) is "item code", the records are retrieved according to the item code value. However, ...

+ Reply to Thread
Results 1 to 15 of 15

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

  1. How do the records retrieve from data base when using sql statements?

    Hello group,

    When I use a simple "READ FILE", the records are retrieved,
    according to the file index. If for example the key of the
    file(physical/logical) is "item code", the records are retrieved
    according to the item code value.

    However, when I use sql statements in order to retrieve records
    from the same physical/logical file, they are not allways retrieved
    according to the "item code" order.

    Is there a way to "say" in the sql statements, to retrieve the
    records according
    to the physical/logical key fields order?
    I try to avoid using "order by" in sql statements in order not to
    hurt the query performances when reading large files

    Thank you in advance,
    Amos

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

    Use the "order by" clause. You really have no choice if you want records in
    a specific order.

    If you don't use it you might be getting the records in the order you want
    today but they may not be in the same order next week. If, for example, the
    file size changes significantly the query optimizer may decide to get the
    data another way.

    In most cases, "order by" it won't hurt performance if you have the correct
    logicals built. And I'd recommend using the SQL against the physical file,
    unless you have select/omit criteria in a logical that you want SQL to
    honor.

    Sam


    "AMOS" wrote in message
    news:f72ec7d6-585d-45c9-96da-78c3d76e6964@59g2000hsb.googlegroups.com...
    > Hello group,
    >
    > When I use a simple "READ FILE", the records are retrieved,
    > according to the file index. If for example the key of the
    > file(physical/logical) is "item code", the records are retrieved
    > according to the item code value.
    >
    > However, when I use sql statements in order to retrieve records
    > from the same physical/logical file, they are not allways retrieved
    > according to the "item code" order.
    >
    > Is there a way to "say" in the sql statements, to retrieve the
    > records according
    > to the physical/logical key fields order?
    > I try to avoid using "order by" in sql statements in order not to
    > hurt the query performances when reading large files
    >
    > Thank you in advance,
    > Amos




  3. Re: How do the records retrieve from data base when using sqlstatements?

    On Jun 19, 4:08*am, "Saml" wrote:
    > Use the "order by" clause. *You really have no choice if you want records in
    > a specific order.
    >
    > If you don't use it you might be getting the records in the order you want
    > today but they may not be in the same order next week. *If, for example, the
    > file size changes significantly the query optimizer may decide to get the
    > data another way.
    >
    > In most cases, "order by" it won't hurt performance if you have the correct
    > logicals built. *And I'd recommend using the SQL against the physical file,
    > unless you have select/omit criteria in a logical that you want SQL to
    > honor.
    >
    > Sam
    >
    > "AMOS" wrote in message
    >
    > news:f72ec7d6-585d-45c9-96da-78c3d76e6964@59g2000hsb.googlegroups.com...
    >
    >
    >
    > > Hello group,

    >
    > > * *When I use a simple "READ FILE", the records are retrieved,
    > > according to the file index. If for example the key of the
    > > file(physical/logical) is "item code", the records are retrieved
    > > according to the item code value.

    >
    > > * However, when I use sql statements in order to retrieve records
    > > from the same physical/logical file, they are not allways retrieved
    > > according to the "item code" order.

    >
    > > * Is there a way to "say" in the sql statements, to retrieve the
    > > records according
    > > to the physical/logical key fields order?
    > > I try to avoid *using "order by" in sql statements in order not to
    > > hurt the query performances when reading large files

    >
    > > Thank you in advance,
    > > Amos- Hide quoted text -

    >
    > - Show quoted text -


    Hello Sam,

    Thank you for your guidance.

    One thing I would like to understand: Does using sql stattemnts
    on the physical file only(not join), ensures retrieving rows according
    to the keys order? When I use the "read" instruction in the RPG
    program, the records are retrieved exactly according to the key
    sequence.

    Now if I've to use the "order by" clause on the key fields, what
    happens if the file contains a lot of records. Does the query
    optimizer decide to use the file key in order to retrieve the records?

    my query is simple:
    select field1, field2,..
    from physicalFile
    orfer by keyField

    Thank you in advance,
    Amos

  4. Re: How do the records retrieve from data base when using sqlstatements?

    On Jun 19, 4:08*am, "Saml" wrote:
    > Use the "order by" clause. *You really have no choice if you want records in
    > a specific order.
    >
    > If you don't use it you might be getting the records in the order you want
    > today but they may not be in the same order next week. *If, for example, the
    > file size changes significantly the query optimizer may decide to get the
    > data another way.
    >
    > In most cases, "order by" it won't hurt performance if you have the correct
    > logicals built. *And I'd recommend using the SQL against the physical file,
    > unless you have select/omit criteria in a logical that you want SQL to
    > honor.
    >
    > Sam
    >
    > "AMOS" wrote in message
    >
    > news:f72ec7d6-585d-45c9-96da-78c3d76e6964@59g2000hsb.googlegroups.com...
    >
    >
    >
    > > Hello group,

    >
    > > * *When I use a simple "READ FILE", the records are retrieved,
    > > according to the file index. If for example the key of the
    > > file(physical/logical) is "item code", the records are retrieved
    > > according to the item code value.

    >
    > > * However, when I use sql statements in order to retrieve records
    > > from the same physical/logical file, they are not allways retrieved
    > > according to the "item code" order.

    >
    > > * Is there a way to "say" in the sql statements, to retrieve the
    > > records according
    > > to the physical/logical key fields order?
    > > I try to avoid *using "order by" in sql statements in order not to
    > > hurt the query performances when reading large files

    >
    > > Thank you in advance,
    > > Amos- Hide quoted text -

    >
    > - Show quoted text -


    On Jun 19, 4:08 am, "Saml" wrote:
    > Use the "order by" clause. You really have no choice if you want recordsin
    > a specific order.
    >
    > If you don't use it you might be getting the records in the order you want
    > today but they may not be in the same order next week. If, for example, the
    > file size changes significantly the query optimizer may decide to get the
    > data another way.
    >
    > In most cases, "order by" it won't hurt performance if you have the correct
    > logicals built. And I'd recommend using the SQL against the physical file,
    > unless you have select/omit criteria in a logical that you want SQL to
    > honor.
    >
    > Sam
    >
    > "AMOS" wrote in message
    >
    > news:f72ec7d6-585d-45c9-96da-78c3d76e6964@59g2000hsb.googlegroups.com...
    >
    >
    >
    > > Hello group,

    >
    > > When I use a simple "READ FILE", the records are retrieved,
    > > according to the file index. If for example the key of the
    > > file(physical/logical) is "item code", the records are retrieved
    > > according to the item code value.

    >
    > > However, when I use sql statements in order to retrieve records
    > > from the same physical/logical file, they are not allways retrieved
    > > according to the "item code" order.

    >
    > > Is there a way to "say" in the sql statements, to retrieve the
    > > records according
    > > to the physical/logical key fields order?
    > > I try to avoid using "order by" in sql statements in order not to
    > > hurt the query performances when reading large files

    >
    > > Thank you in advance,
    > > Amos- Hide quoted text -

    >
    > - Show quoted text -


    Hello Sam,

    Thank you for your guidance.

    One thing I would like to understand: Does using sql stattemnts
    on the physical file only(not join), ensures retrieving rows according
    to the keys order? When I use the "read" instruction in the RPG
    program, the records are retrieved exactly according to the key
    sequence.

    Now if I've to use the "order by" clause on the key fields, what
    happens if the file contains a lot of records. Does the query
    optimizer decide to use the file key in order to retrieve the records?

    my query is simple:
    select field1, field2,..
    from physicalFile
    orfer by keyField

    Thank you in advance,
    Amos

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

    AMOS wrote:
    >
    > One thing I would like to understand: Does using sql statements on
    > the physical file only(not join), ensures retrieving rows according
    > to the keys order? When I use the "read" instruction in the RPG
    > program, the records are retrieved exactly according to the key
    > sequence.
    >
    > Now if I've to use the "order by" clause on the key fields, what
    > happens if the file contains a lot of records. Does the query
    > optimizer decide to use the file key in order to retrieve the
    > records?
    >
    > my query is simple:
    > select field1, field2,..
    > from physicalFile
    > order by keyField


    Order is /undefined/ in SQL access, except where explicitly requested
    by an ORDER BY. This is true irrespective of involvement of join.

    SQL is one particular language used to implement RDBMS access. SQL
    generates set-based results. The RPG READ performs what is often called
    a RLA [Row\Record Level Access] or ISAM [Indexed Sequential Access
    Method] to retrieve a row to the program; even if underlying support can
    effectively retrieve a set. By mathematical definition a /set/ has no
    implied order, and SQL honors that definition. The manner in which to
    effect a desired collation of data in a result set, is to request an
    ORDER BY. A good implementation of SQL would not limit the access
    method to use an existing index, opting to use /sort/ algorithms where
    an exact index either does not exist or where an index which limits row
    selection is a better choice for choosing which rows to include in the
    set; in the given example, there is no WHERE clause to limit row selection.

    That SQL SELECT with only an ORDER BY would not necessarily _use_ the
    /file key/ [of any file, logical or physical] to retrieve the records.
    In fact for such a request with many rows and no selection, the data is
    most likely to be _sorted_ into an intermediate table [if the temporary
    results are enabled]. This is because index access is /random I\O/
    which may result in significant paging, whereas the /sequential I\O/ for
    a request to retrieve all of the data in the file can be expected to be
    extremely efficient due to minimal paging requirements. The collation
    for character data is defined by the /Sort Sequence/ in effect for the
    SQL SELECT request.

    Regards, Chuck

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

    The query optimizer is a complicated beast. It uses many things, including
    how many records it thinks might be returned and how many records there are
    in the file, to decide how to get at the data. Even if you are running the
    SQL over a physical file that is keyed by your selection criteria, there is
    no guarantee the optimizer will use the index.

    Unless you specify an order by clause, there is no guarantee that you will
    get the data in key order.

    If you *don't* specify an order by, and if you are likely to return less
    that 20% of the records in the physical file, the optimizer may choose to
    use an index that matches, or closely matches, the selection criteria. If
    it is more than 20%, it may decide that reading through the entire physical
    file is more efficient than accessing each record via the index (or logical
    file.) There is a break over point where you will end up doing more
    physical IO to read the indexes and find the record to return than if you
    ignore the indexes and blast through the physical file. As I recollect, it
    is around 20% but I don't think it is a hard and fast number for every
    situation.

    If you do the same select *and* specify an order by that matches an index,
    and if you are returning less that 20% of the records, the optimizer may, is
    probably likely to, use the index, but it is not guaranteed to. If you will
    be returning more than 20%, it may decide that running thought the entire
    physical file, selecting the records, then sorting then before returning
    them to you is more efficient that using the index.

    So, if you want a guaranteed sequence, always specify an order by.

    The optimizer is smart, and keeps getting smarter. Generally you can trust
    it, thought that is not to say that you don't need indexes or logical files.
    If you have *no* key on the physical and *no* logical files and *no* SQL
    indexes, SQL will still return the correct data is the order by sequence,
    but as file size grows so may the length of time to return the data. If
    your SQL is to return data and the order bys are is in the same sequence as
    existing logicals, then chances are that you won't have any performance
    issues.

    Chuck Pence has said essentially the same thing from the set theory that
    drives SQL.

    Sam

    "AMOS" wrote in message
    news:f8eb39e9-1d9d-4057-ac62-bffc7e58f6db@m45g2000hsb.googlegroups.com...
    On Jun 19, 4:08 am, "Saml" wrote:
    > Use the "order by" clause. You really have no choice if you want records
    > in
    > a specific order.


    Hello Sam,

    Thank you for your guidance.

    One thing I would like to understand: Does using sql stattemnts
    on the physical file only(not join), ensures retrieving rows according
    to the keys order? When I use the "read" instruction in the RPG
    program, the records are retrieved exactly according to the key
    sequence.

    Now if I've to use the "order by" clause on the key fields, what
    happens if the file contains a lot of records. Does the query
    optimizer decide to use the file key in order to retrieve the records?

    my query is simple:
    select field1, field2,..
    from physicalFile
    orfer by keyField

    Thank you in advance,
    Amos



  7. Re: How do the records retrieve from data base when using sqlstatements?

    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/OS
    http://www-03.ibm.com/servers/enable...ndxng_abs.html

    Birgitta

  8. Re: How do the records retrieve from data base when using sqlstatements?

    On 23 יו*י, 06:54, "Hau...@sss-software.de"
    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

  9. Re: How do the records retrieve from data base when using sqlstatements?

    On 23 יו*י, 06:54, "Hau...@sss-software.de"
    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

  10. Re: How do the records retrieve from data base when using sqlstatements?

    On Jun 25, 11:26*am, AMOS wrote:
    > On 23 יו*י, 06:54, "Hau...@sss-software.de"
    > 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


    The data in physical files is almost certainly not in keyed sequence.
    There is a way to re-sequence the data using rgzpfm but that would be
    over the top for every insert/update. You can also add a key to the
    physical file (keyed physical) but that really only counts as an
    additional index. As others have said if you re-sequence the data then
    a table scan may well get the data back in sequence but if SQL decides
    to use an index, maybe because of a selection criteria, then your data
    may well be in the sequence given by that index.

    Jonathan

  11. 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" wrote in message
    news:cdd5d2d8-c52a-4097-ad5e-de2bc73623e4@27g2000hsf.googlegroups.com...
    On 23 ????, 06:54, "Hau...@sss-software.de"
    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



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

    AMOS wrote:
    > <>
    > 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.


    Other databases typically offer /clustered indexes/ because they do
    not have direct storage management access, since the RDBMS is not part
    of the operating system. Thus for performance reasons, they incur the
    cost at insert\update time to manage the physical ordering in contiguous
    storage; i.e. /for performance/, with the hope that retrieval time will
    be reduced, due to the physical ordering. The DB2 for i5/OS is tightly
    integrated with the LIC storage management, plus its single level store
    and automatic striping of data, limit the cases where the benefits of
    physical ordering will assuredly outweigh the costs. As such there was
    little justification to provide clustered indexes; too little to be
    gained since few SQL statements would be able to take advantage. With
    Encode Vector Indexes [EVI] there were even fewer reasons for clustered
    index since those indexes can reflect the physical storage of the data
    to the optimizer, to enable even quicker retrieval.

    As Jonathon notes, there is the option to perform the request to
    RGZPFM KEYFILE(Keyed_FileName) to effect essentially contiguous physical
    storage of the rows, but that ordering is only in effect until the next
    key-update or insert. The benefits from that type of reorganize action
    are most visible for keyed row level access when large amounts of data
    will be read, even if changes\additions have transpired; for the same
    reason as clustered indexes. Similar benefit could be seen for the
    simple but not often used, select all or first using physical keyed
    order, but again, at a large cost to update and insert.

    Regards, Chuck

  13. Re: How do the records retrieve from data base when using sqlstatements?

    Why don't you just build a logical that's keyed by the field you
    want? It's a one-time build that will then alway be available. It
    will automatically get used if it matches the ORDER BY clause,
    regardless of which file you specify in the SELECT.

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

    azj@sbcglobal.net wrote:
    > Why don't you just build a logical that's keyed by the field you
    > want? It's a one-time build that will then alway be available. It
    > will automatically get used if it matches the ORDER BY clause,
    > regardless of which file you specify in the SELECT.


    Keyed logical files or SQL INDEX objects are more likely to be used
    for selection than for ORDER BY. It is false to assume or imply that a
    keyed logical file matching the ORDER BY will /automatically get used/
    [to implement a query] to retrieve the rows. The optimizer [both CQE
    and SQE] is not so limited in what it can do, in choosing how rows will
    be retrieved. Because an index is random I/O, its cost for implementing
    collation can become extremely high as the number of rows [that will be
    returned] grows; thus for the query given by the OP where _all rows_ are
    selected, such an index is not likely, in general, to be very beneficial
    except in a mostly memory constrained environment where the query will
    perform poorly for that & other reasons [even if only as side effects of
    the memory constraints]. As the OP infers, a /clustered index/ could be
    beneficial for that specific type of query, but as I noted since their
    benefit is so limited in scope to those types of queries, and costly for
    insert & update activity, that type of index is not supported by the DB2
    for i5/OS.

    Regards, Chuck

  15. Re: How do the records retrieve from data base when using sqlstatements?

    One of the best ways to gain information as to SQL performance, I have
    found is to use OPS NAV. Go into the system, then DATABASE, then see
    the options to test SQL commands, see performance monitors, and even
    get advice on what access paths need to be built to improve
    performance on frequently. It can generate SQL code to drop and (re)
    create a table. There are many other useful functions here, so
    explore this area thoroughly if you want a window on the SQL world of
    your AS400 database.

+ Reply to Thread