Solved

ReIndexing in As400

Posted on 2007-11-27
7
1,818 Views
Last Modified: 2011-10-03
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
Comment
Question by:Camillia
7 Comments
 
LVL 18

Accepted Solution

by:
daveslash earned 250 total points
ID: 20362539

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
 
LVL 7

Author Comment

by:Camillia
ID: 20362562
>> 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
 
LVL 18

Expert Comment

by:daveslash
ID: 20362570

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 7

Author Comment

by:Camillia
ID: 20362624
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
 
LVL 7

Author Comment

by:Camillia
ID: 20362630
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
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 200 total points
ID: 20379360
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
 

Assisted Solution

by:prmurphy63026
prmurphy63026 earned 50 total points
ID: 20383791
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question