What are the thoughts on building an index over a substring of a column?
Example: create index testLib.veh_test_substr_index for system name tstvehsubi on prodLib.vehFile ( substr(lotNbr, 1, 2) as lane );
...just another index?

What is your problem?

You can create derived indexes, they can also be used, but make sure you use the same syntax, i.e. SUBSTR(Fld, 1, 2) and not LEFT(Fld, 2).

The easiest way to avoid such problems is to create an additional view based on your table with an additional column that substrings the original field (with the same syntax as in the index).

When using this view and selecting the additional column the syntax is in either way identical.

According to my experience the query optimizer will rather use an index when there is a view with an appropriate column (this column is used in the query), than hardcoding it in exactly the same way. 

What are the thoughts on building an index over a substring of a column?
Example: create index testLib.veh_test_substr_index for system name tstvehsubi on prodLib.vehFile ( substr(lotNbr, 1, 2) as lane );
...just another index?

Birgitta,

Thanks for the feedback. No problem to answer your question. I was making sure there are no issues. I just thought I would check with the experts. Your advice is spot-on. A view is going to be created to coincide with the index.

Thanks again

What is your problem?

You can create derived indexes, they can also be used, but make sure you use the same syntax, i.e. SUBSTR(Fld, 1, 2) and not LEFT(Fld, 2).

The easiest way to avoid such problems is to create an additional view based on your table with an additional column that substrings the original field (with the same syntax as in the index).

When using this view and selecting the additional column the syntax is in either way identical.

According to my experience the query optimizer will rather use an index when there is a view with an appropriate column (this column is used in the query), than hardcoding it in exactly the same way. 

What are the thoughts on building an index over a substring of a column?
Example: create index testLib.veh_test_substr_index for system name tstvehsubi on prodLib.vehFile ( substr(lotNbr, 1, 2) as lane );
...just another index?

Dear Patrick

I attach herewith 2 article explaining the use of derived index published when it was first available in IBM i 6.1. 

Birgitta,

Thanks for the feedback. No problem to answer your question. I was making sure there are no issues. I just thought I would check with the experts. Your advice is spot-on. A view is going to be created to coincide with the index.

Thanks again

What is your problem?

You can create derived indexes, they can also be used, but make sure you use the same syntax, i.e. SUBSTR(Fld, 1, 2) and not LEFT(Fld, 2).

The easiest way to avoid such problems is to create an additional view based on your table with an additional column that substrings the original field (with the same syntax as in the index).

When using this view and selecting the additional column the syntax is in either way identical.

According to my experience the query optimizer will rather use an index when there is a view with an appropriate column (this column is used in the query), than hardcoding it in exactly the same way. 

What are the thoughts on building an index over a substring of a column?
Example: create index testLib.veh_test_substr_index for system name tstvehsubi on prodLib.vehFile ( substr(lotNbr, 1, 2) as lane );
...just another index?