Index for performance question

[Former Member]

I have a few applications that I am trying to tweak to gain better performance. I would like to try a few indexes based upon my analysis and also data coming from the System i SQL cache and index advisor. My question is this: Does the SAP system look at indexes differently based upon whether they were created within the SAP system (SE11) versus just creating them at the OS level. I want to try some indexes but I don't want to define them to SAP until I see that they are doing some good. I would just like to add them at the OS level, run the transaction and see if it helped. If it does help, then I can define them to SAP.

Best regards,

Russ Cook

[Former Member · Accepted solution]

Hello Ross,

Yes, you can manually add the index on DB2/400 level. And the query optimizer of DB2/400 (not SAP component) will take advantage of it if proper.

I would pick a low-activity time to create the index.

Remember to remove it before recreating/transporting it to SAP. DB2/400 doesn't create same index structure as I remember.

Best regards,

Victor

[Former Member · Accepted solution]

Please help educate me a bit on why would you want to define the index(es) from SAP level? As far as I know, SAP has no way of telling DB2 for i5/OS to use any particualr indexes for any of its operations. As Mr. Lin pointed out, DB2 decides by itself which indexes to use. Of all my local SAP on i5/OS customers who run V5R4 or V6R1, I always use the new System-wide Index Advisor and Index Condensor tools in iSeries Navigator to identify the most wanted but not-yet-exist indexes and create them from iSeries Navigator. My customers told me they noticed the overall system performance became somewhat more consistent after I spent some days creating some 50-60 most wanted indexes for frequently used SAP tables. But I could not find information why we would want to define the indexes from SAP level for DB2.

Thanks.

Satid S.

IBM Thailand

[Former Member · Accepted solution]

Hello Satid,

Yes that is a question I have myself. Does it make any difference whether I defined the index to SAP or not? In the past, I thought so since I would define an index at the OS level and not see the expected results. I would then remove that index and go into SAP and create the index and I did see improvement. However I may have just been working with a flawed process and perhaps when I created the index on the OS level, it had the wrong authorities. Other than being able to see the indexes defned on a table within SAP, I am not sure there is any advantage to adding them in SAP. Also in some cases the index advisor may request the index to be an encoded vector index. I am not sure how you would even specify this in SAP.

Best regards,

Russ

[Former Member · Accepted solution]

Hi Satid & Russ,

so, it is 100% the same for the performance, if you create the indexes with OpsNav STRSQL or SE11 via SAP - I do this myself hard on the DB always as well.

But:

If you do this with Non-SAP-Tools, you have to care for the survival of the indexes with SPs and upgrades and perhaps even homo-copies ..

=> I do this for fast testing hard on the DB as well. If it then turns out to be good, I create it via SE11 and delete my one and otherwise, I just delete my one, because a non-used index, is a very expensive index ...

Regards

Volker Gueldenpfennig, consolut international ag

http://www.consolut.net - http://www.4soi.de - http://www.easymarketplace.de

[dorothea_stein · Accepted solution]

Hi Russ,

if you wanted to, SAP allows you to create EVIs through SE11 as well. That's how it works:

-- Define a new index in SE11 and choose "For selected database systems".

-- Click on the arrow button and pick "DB4" from the selection list.

-- Save.

-- Go to SE14 for that table, push "Edit".

-- Choose "Storage Parameters" and go to edit mode

-- Push "For new creation" and choose "Current database parameters"

-- Now you can specify that your secondary index should be created as an EVI by setting

"ENCODED VECTOR INDEX" to "X".

-- Specify a number for "DISTIINCT VALUES". (The system will not create the index with exactly that number though, but with what it believes is appropriate).

-- "Activate and adjust database"

Best regards,

Dorothea

[Former Member · Accepted solution]

Hello Dorothea,

Thank you for that information. I was not aware of that capability.

Best regards,

Russ

[Former Member · Accepted solution]

Mr. Cook

.... and perhaps when I created the index on the OS level, it had the wrong authorities.

Per your remark about the wrong authorities of indexes in DB2 for i, I can assure you that this should not be an issue because the DB2 for i indexes are used by the query engine which is a kernel-level process, the engine can access any indexes it wants regardless of the assigned authorities of the indexes.

Satid S.

IBM Thailand

Edited by: Satid Singkorapoom on Jun 3, 2009 9:12 AM