It depends. In huge files you could do a Vector Encoded index (using CREATE INDEX) to avoid that first runtime access path creation.

But in general, no, there is no real need to pre-build access paths, particularly since the system now shares them across job boundaries, and can keep them until the next IPL if necessary.

I am thinking as a typical developer here, as an example: if I can do an sql select distinct instead of a chain to get a single record out of a file then why would there be a need for a key in a file anymore?

You wouldn't; but you would use the FETCH FIRST ROW ONLY clause instead of DISTINCT. With DISTINCT, you end up with worse overall performance, and less accurate results.

I am not sure if tables built with DDS use SQE or CQE. Obviously you want them to SQE. And so does it not make sense to use SQL to build the new tables(files)? The tool within inav works pretty slick and if you build it and say crap I forget a field in the middle, no real problem, simply, "generate sql", delete off the table, take the generated sql, adjust what you want, and run it in inav for example. the reason I say this about missing a field in the middle is of course for it to "match" for a legacy program.

As far as "indexes", then if you run inav index advisor it will show you the various usages and you can see where having an index could help. Of course as I understand it, you still use the main table/file, but the SQE engine is smart enough to know that index exists and uses it.

Yes, DDS is something that can and should be retired when it comes to database creation. Use SQL to create files instead of DDS and you have your first step. However, while it is highly recommended to move to SQL over DDS, it is in no way required.

A good choice to start, is to use the RTVSQLSRC command (currently available in COZTOOLS as a free runtime command). It builds SQL statements that mimic your original DDS statements and can be used to "re create" the file just like your DDS can.

And you should read into my statements that using SQL to create a table means better performance for accessing your data using SQL, but you can still get to it using RPG IV I/O opcodes.

Yes, you still need keys. The SQL query optimizer examines all the access paths to determine the fastest way to run the query. Without keys, the system has to do a full table scan to run  your query. If the file is small, not a big deal. But "large" files, do you really want to do a table scan on a million row table for an interactive job? No.

Chris Ringer

Ringer has it. If your access is all SQL, then, strictly speaking, you don't need any keyed access paths; SQL will build an index on the fly if the optimizer doesn't find a suitable existing index. However, you're going to want to build them for performance, just as you always have.

The equivalent to a keyed LF is a CREATE INDEX. The Encoded Vector Index (EVI) that Bob mentioned can give better performance in some cases, but is not always appropriate.

We certainly noticed a marked improvement adding an index to a file with 5mil records. Index was over a field that did not appear on any of the (DDS) LF's that were already on the file. But index building is definitely an interesting need, I tend towards the "we don't need it unless performance is poor" camp.

Here are the patterns you will see if you DON'T key your tables.

1. It tests and performs great in a test environment.

    You install it to production and the performance instantly takes a nose dive. Why?

    You tested with only a few rows in the tables in development and

    production has huge files. So you build an index to fix that.

2. It tests and performs great in a test environment.

    You install it to production and the performance is fine for a year or 2 or 3 and

    then performance takes a nose dive. Why? The file size grew.

    So you build an index to fix that.

So you might as well cut to the chase and build an index up front and be done with it so the users won't get the perception that the AS400 is an old slow machine.

Chris Ringer

The pre-built index practice can be helpful, but today it is rarely helpful over a dynamic build at runtime that will occur and then stick around for everyone. So yes, the first person might see a sluggish performance/response time while the initial index is being built, but after that is is the same as if you had built it previously. I think this is what Neil is saying.

There is the 50 million record scenario, where pre-built INDEX's are useful and practical. However, depending on your system performance, that figure is sliding. Some P05 systems with 8 GB of RAM or less need more pre-built INDEX's because that don't have enough resources to build them quickly for humans to enjoy the dynamic process. 

For me, the cool IDENTITY column feature, which I use in nearly every new file I create, is the PRIMARY key, and is how I tend to access files--so except for dynamic Searches/Lookup routines, performance is always very good with SQL, for me.

Bob,

Technically, I know what you are saying. But if even one end user creates an IT trouble ticket because of slow performance (index not found, system building temporary access path), I have to deal with it later. I can't explain to them how the system is creating a temporary index over a million row table. Their eyes will glaze over, they will say their little $500 PC is fast so why is the big AS400 slow?. I'll have to explain that once daily to a different user. It's easier to just create the index(es) and be done with it. Set it and forget it. Then the users will say my apps are always fast (which makes my boss happy too) instead of sometimes slow.

Chris Ringer

The normal DB2 way of doing this is to CREATE TABLE using the PRIMARY KEY clause to specify which columns (if any) should be unique in the table.  Alternatively you can use the CREATE UNIQUE INDEX command that will create a similar constraint on the table.  In either case, DB2 will not allow duplicates in the table.  It is good practice to do one or the other whenever there are columns that should remain unique, just as with DDS DB design.  You can also use the FOREIGN KEY clause to enforce relationships between the new table and other tables.  In addition, you can use CREATE INDEX (non-unique) to help with performance.  iSeries Navigator has some good tools to help you identify when an index would help with performance and what it should look like.

the nice thing about index advisor is that it makes recommendations of indexes to you and it also shows you the usage. For example, you have a table that has 752,129 uses of order#/item# combination, and you have 3 uses of ship by date. Which index should you consider building? (o.k. yes you could build every possible one, but at some point performance will nosedive with too many indexes (or can)). There is a happy medium I believe.

chris

Chris and CL nailed it imho. You create the indexes you know you have to make sooner or later anyway and then you'll keep a keen eye on what Navigator advises you might be nice to have. This last process is to be repeated on a regular basis as tables have a tendency to grow...

Regards and seasonal greetings!

Paulster

I'm telling ya, do a Ron Popeil. Set it and forget it.

The question is why wouldn't you want to have an index for every possible way? Well at least in many other database engines, you pass a point where you add more overhead to have those indexes than not to have. It has been a while since I read a book on ms sql (and I know, mickeymouse sql (microsoft) is not as good as db2), but it makes sense. And think about when you have to bring the machine up after a hard shut off and it has to build all those indexes, some on paths seldom taken. I remember hearing about S/38's that could take 24 hours or more to build the indexes after a crash...

chris

If you listen to Mike Caine and Paul Tuohy lecturing, you might as well create an index for each conceivable access - at no cost. Sound almost too good to be true - maybe it is...

Meanwhile, as you, I stick to the ones you can anticipate and create others on the fly as required.

Creating every conceivable index would be silly.  That's what data warehouses are for.  Indexes contain data.  They contain the pointers of the rows in the table in the pre-sorted order you want.  When rows are added, deleted, updated, all the indexes must be maintained by the system as overhead.  You can even watch the size of an index grow along with the size of the table. 

We have tables that have hundreds of millions of rows in them.  Issue a CREATE INDEX command against one of those puppies and it takes a long time to create.  Add too many indexes and table performance overall starts to suffer.  Don't add the right ones and performance sucks even worse.  We only add them when absolutely needed.  And we only keep them if the Query Optimizer actually uses them and performance is measurably improved.

> Creating every conceivable index would be silly.

Surely no one here is saying to do this. Create the likely needed indexes and periodically check the index advisor in iSeries Nav. So for a customer master file: by customer number, by name/customer number, and by country/state/customer number. But by date entered or date maintained? Not needed.

Chris Ringer