Solved

ReIndexing in As400

Posted on 2007-11-27
7
1,776 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
Comment Utility

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
Comment Utility
>> 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
Comment Utility

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 7

Author Comment

by:Camillia
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
how to get the substring vaule in db2 11 295
How to access tables of DB2 in Excel 8 232
DB2 Integer to Decimal 1 118
DB2 return first match 3 90
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…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now