Hi,

I've come across some reading and was wondering if anyone here can confirm/verify this....

In DB2 8.1 AIX if you create an index on a column defined as VARCHAR with NOT PADDED option, the index will actually be created with variable column lengths - not to their maximum size (will will happen if you create it with PADDED option).

The point to this is then the index can be used to obtain index only access.

Can anyone verify this? Has anyone tried the NOT PADDED option yet? Any comments on performance?

Linda

I know that this was an issue with DB2 z/OS and OS/390 Version 6. This was "fixed" in version 7. I was not aware of anything like this in DB2 for Linux, UNIX, and Windows (but that may just be my ignorance). Do you have a reference for the use of the NOT PADDED option on DB2 for AIX?

Sure...

From CS Mullins:
http://www.dbazine.com/mullins_db2indexing.shtml

Section:
Indexing Variable Columns

For the record, i cannot find a PADDED/NOT PADDED option in the CREATE INDEX command.

That article refers to DB2 for z/OS and OS/390 (mainframe).

at the risk of sounding blonde... where can you see this?

I didn't think the article was specific to any platform, hence the creation of the thread.

Unfortunately, the article does not mention that it only applies to DB2 for OS/390 and z/OS, but it does. Here is a few things I noticed:

1. The author is Craig Mullins with BMC. I happen to know about Craig and that he is primarily (if not totally) a mainframe guy, where BMC is very strong in 3rd party DB2 tools.

2. If you look at the link to his book at www.amazon.com, and "Look Inside the Book" you will see that it refers to OS/390 and z/OS and not DB2 for LUW.

3. The syntax for the CREATE INDEX that he uses includes the CLOSE NO clause, which only applies to mainframe DB2, which uses VSAM files for all tablespaces. At one time there was a fairly low limit as to how many VSAM files could be open at one time on OS/390.

4. He says that if you do not explicitly declare a clustering index, DB2 will choose the oldest index as the clustering index. This is not true for DB2 for LUW tables, which don't have a clustering index if one is not explicitly created (rows would be added at the end of the table in that case).

5. He talks about indexing with DB2 partitioning, but much of what he says only applies to DB2 for OS/390 and z/OS, which only has range partitioning. DB2 for LUW only has hash partitioning and there are significant differences.

6. As I already stated, the index only issue about padding of VARCHAR columns in an index does not apply to DB2 for LUW.

If you need any further proof, I will need an address to send my invoice.