• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1932
  • Last Modified:

ReIndexing in As400

is there a concept in AS400 for reindexing tables?

For example, in sql server, I run DBCC Reindex('tablename').

Also, is there such thing as table fragmentation? in sql server, I run DBCC ShowContig ('tablename').

We have a client that says their AS400 system is slow. Not sure where to start. But in SQL Server, i usually start with those commands. I know part of the problem is probably our app but need to see if their db needs optimization.

My manager said there's something like rearranging physical files or something. He said for example, if 10 rows are deleted, AS400 will take up space and for example it shows more space taken. But he could be just making this up :)
0
Camillia
Asked:
Camillia
3 Solutions
 
Dave FordSoftware Developer / Database AdministratorCommented:

Greetings Farzadw,

In general, the AS/400 re-indexes everything "on-the-fly", and there's usually no need to do anything.  All index statistics are generated automatically with no user intervention.

That being said, there is a SLIGHT performance advantage when you re-organize the table to compress out deleted records and re-order the table in priary-key order.

RGZPFM FILE(mylib/myfile)  KEYFILE(*FILE)

Again, this advantage is very small, but it certainly can't hurt.

Just be sure that you can get an exclusive lock on the table while re-organizing. Also, this can take some time ... especially if it's a badly un-normalized large table with many logical-files (indexes and views).

HTH,
DaveSlash
0
 
CamilliaAuthor Commented:
>> badly un-normalized large table with many logical-files

Database is un-normalized. My manager and ex-developers have treated it like flat text files.
One table alone has 16 logical files. They've taken this and created 16 indexes in SQL server's database. That's one reason our sql server had bad performance.



Let me read about that command. But there are no Primary Keys in any of the tables. There are unique indexes. Also, no Foreign Keys. Thanks.

Kamila.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

16 logicals?  That's really not so bad. (it's probably more than I would create, but I've seen tables with over 100 logicals ... ugh!)

So, if the files have no primary key, then the performance improvement will be even smaller, but (again) it certainly can't hurt.

HTH,
DaveSlash
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
CamilliaAuthor Commented:
Logicals like views in sql server are used to select from a table/file to narrow down the resultset..right?

for example, select * from table where something = whatever.

What's the point of creating Logicals if they all do the same thing. 16 logicals..all doing : select * from table.  Vendor has done the same in sql server. 16 views all have : select * from table.

Just doesnt make sense and we're seeing the result of system being slow and clients complaining.
0
 
CamilliaAuthor Commented:
or maybe as400's logicals are created correctly and the vendor has messed up the sql server.

How can I look at a logical's code?
0
 
tliottaCommented:
Kamila:

_Some_ care should be taken when discussing LFs (Logical Files) in comparison to SQL VIEWs. The two are not precisely equivalent and _can_ be very different.

A LF _can_ implement selection criteria similar to a WHERE clause, but that's unusual. Most commonly, a LF would be used to implement something similar to an ORDER BY clause or a SQL INDEX.

Also, a LF is often used to implement a limited selection of columns for a SELECT rather than a limited number of rows. This aspect of a SQL VIEW is far more likely to be seen than limiting rows in the result set.

Regardless, a question that needs to be answered is whether or not you're creating LFs for use by SQL or by native (non-SQL) I/O.

If these are to be used by SQL, then you probably should be creating SQL VIEWs and INDEXes instead of "LFs". Both VIEWs and INDEXes are implemented by DB2(400) as forms of LF objects, but the details of the objects are different depending on whether they are created by SQL DDL or native DDS. The details can make significant performance differences.

Tom
0
 
prmurphy63026Commented:
From the perspective of the AS/400, you should provide the SQL to the AS/400 DBA so it can be analyzed to determine the access path of the query and determine if an adjustment is required, i.e. if an index needs to be created.  This tool is readily available with the AS/400 client interface for Windows.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now