Hi,

As a Newbie coming from an Oracle world..How would I check whether
certain tables which are being frequently accessed have updated
statistics? How would I check for the last time they were reorganized,
or if they need a reorganization?



Thanks

I have good news for you, there's no need to do any of that.
Statistics are maintained automatically by the system.
Reorgs are not needed if files are created to reuse deleted records -
REUSEDLT(*YES).  Only exception to that is if you delete ton of records
an they're never repopulated with new records.  That is the only case
where I'd recomment RGZPFM command.

Actually there are a lot of tasks that you used to perform on Oracle
that are done for you by the OS.

Hi Elvis,

Thanks for the information.

In Oracle, in order to check whether or not indexes needed to
be reorganized, there are commands to check for the levels of
the index, percentage of leaf nodes that are deleted, etc..

In AS400, how do you check whether or not an index is a good
candidate for a rebuild, or is it also done automatically?


Thanks



Elvis wrote:
> I have good news for you, there's no need to do any of that.
> Statistics are maintained automatically by the system.
> Reorgs are not needed if files are created to reuse deleted records -
> REUSEDLT(*YES).  Only exception to that is if you delete ton of
records
> an they're never repopulated with new records.  That is the only case
> where I'd recomment RGZPFM command.
>
> Actually there are a lot of tasks that you used to perform on Oracle
> that are done for you by the OS.

basis_consult...@hotmail.com wrote:
> Hi Elvis,
>
> Thanks for the information.
>
> In Oracle, in order to check whether or not indexes needed to
> be reorganized, there are commands to check for the levels of
> the index, percentage of leaf nodes that are deleted, etc..
>
> In AS400, how do you check whether or not an index is a good
> candidate for a rebuild, or is it also done automatically?

Usually done automatically; see 'Access path maintenance' below:

DSPFD Command Input
  File  . . . . . . . . . . . . . . . . . . . : FILE       WHP700L1
    Library . . . . . . . . . . . . . . . . . :            TVSDF00
  Type of information . . . . . . . . . . . . : TYPE       *ACCPTH
  File attributes . . . . . . . . . . . . . . : FILEATR    *ALL
  System  . . . . . . . . . . . . . . . . . . : SYSTEM     *LCL
File Description Header
  File  . . . . . . . . . . . . . . . . . . . : FILE       WHP700L1
  Library . . . . . . . . . . . . . . . . . . :            TVSDF00
  Type of file  . . . . . . . . . . . . . . . :            Logical
  File type . . . . . . . . . . . . . . . . . : FILETYPE   *DATA
  Auxiliary storage pool ID . . . . . . . . . :            00001
Access Path Description
  Access path maintenance . . . . . . . . . . : MAINT      *IMMED
  Unique key values required  . . . . . . . . : UNIQUE     Yes
  Select/omit specified . . . . . . . . . . . :            No
  Access path journaled . . . . . . . . . . . :            No
  Access path . . . . . . . . . . . . . . . . :            Keyed

basis_consultant@hotmail.com wrote:
> In AS400, how do you check whether or not an index is a good
> candidate for a rebuild, or is it also done automatically?
> 

If the index MAINT parameter is set to *IMMED, then this is handled for 
you automatically as well.

Maybe you already know this, but:
If you type 'CHGPF <library>/<filename>' (or CHGLF if it is a logical) 
then hit 'F4', it'll prompt you for all the parameters, and then you put 
the cursor on any field and hit 'F1' to get the online help descriptions 
of the parm and what it does. 'F11' toggles between parm name and values.

"Elvis" <budimlic@hotmail.com> wrote in message 
news:1109368027.639824.157760@f14g2000cwb.googlegroups.com...
>I have good news for you, there's no need to do any of that.
> Statistics are maintained automatically by the system.
> Reorgs are not needed if files are created to reuse deleted records -
> REUSEDLT(*YES).  Only exception to that is if you delete ton of records
> an they're never repopulated with new records.  That is the only case
> where I'd recomment RGZPFM command.
>
> Actually there are a lot of tasks that you used to perform on Oracle
> that are done for you by the OS.
>

As much as I like the /400 this may be a bit of an over statement.

The  REUSEDLT(*YES) parameter governs recovery of space from deleted 
records, it does not re-order records by key.

The /400 is very smart and efficient and the key structure of the access 
path is almost never a problem.  However, if you are working with a large 
file - 10's of millions of records or more - and if it needs to be processed 
many times in index sequential order, you may want to RGZPFM to put the 
records in a physical order that corresponds with their key sequence.  This 
allows faster access of contiguous blocks of records with related keys.

Mike Sicilian 

Mike, you are absolutely correct.  For your scenario reorganizing
physical to match the index would help sequential performance (i.e.
batch processes and like).

I still wouldn't recommend it due to the fact that reorgs on large
files take a long time, making file and its indexes inaccessible during
that time (unless on V5R3).
As you point out, 400 indexes are very efficent even on files with over
a billion rows.

BTW, has anyone used new reorg-while-active feature on V5R3?
I love the theory behind it, but am curious what the practical
experiences are.

I did some experimentation with it.  The file must be journaled so you need 
to consider that it can write a lot of journal records.  I got reassigned to 
another project, so I never did finish up--the file in question is just 
getting bigger...

Sam

"Elvis" <budimlic@hotmail.com> wrote in message 
news:1109627406.688242.105970@o13g2000cwo.googlegroups.com...

>
> BTW, has anyone used new reorg-while-active feature on V5R3?
> I love the theory behind it, but am curious what the practical
> experiences are.
>