Solved

ReIndexing in As400

Posted on 2007-11-27
7
1,839 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find Value column 2 does not work 1 204
Problem to script 14 195
Date and Time Conversion from Numeric Fields 21 281
Importing Android SQlite database data to db2 4 92
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 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