Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ReIndexing in As400

Posted on 2007-11-27
7
Medium Priority
?
1,912 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:
Dave Ford earned 1000 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:Dave Ford
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 800 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 200 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

916 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