index and upper() usage

[jayvaughn]

we have a PF and a LF that utilizes ALTSEQ(QSYSTRNTBL) for case insensitive searches.

What is the least invasive path to change the PF to a table, and utilize an index that uses upper() and has the most minimal impact to the application layer where the current LF is currently used?

I'm thinking...

1.) convert PF to table (as-is)
2.) convert the LF to the index but create new columns ( upper(column) as newcolumn) and change the applications to look at this newcolumn.

Is there a less invasive path?

[Rocky]

If using standard RPG file I/O that will work - keep in mind that an index doesn't work the same way in SQL - for that you'd need a view rather than an Index.

The flip side however is that in SQL all you need to do is set your SRTSEQ to *LANGIDSHR and you have the same result without adding any new columns.

[jayvaughn]

If using standard RPG file I/O that will work - keep in mind that an index doesn't work the same way in SQL - for that you'd need a view rather than an Index.

The flip side however is that in SQL all you need to do is set your SRTSEQ to *LANGIDSHR and you have the same result without adding any new columns.

Thx rocky. Yes this is to appease most of the current code base which is all native rpg rla. Can you expand a bit more on the srtseq to *langidshr? In what case would this be needed?

[B.Hauser]

Even though an SQL Index cannot be specified within an SQL-Statement, it can be used in composition with native I/O like any keyed logical file.
For more Information just have a look at the following article:

SQL indexes and native I/O ? no contradiction
Profit from the use of SQL indexes when native I/O is used

https://www.ibm.com/developerworks/ibmi/library/i-sql-indexs-and-native-io/index.html

There is no need to convert the DDS described physical file into an SQL table (even though it is recommended) before using SQL Features.
Converting a DDS described physical into an SQL Table is no problem either:
1. Generate the SQL Code with Reverse Engineering
Wizard in Client Access Database or better ACS - Access Client Solution - Schemas). Position on the appropriate database object right click and select GENERATE SQL.
Make sure you set the CREATE OR REPLACE option.
An SQL Script will be generated.
If the physical file includes something that cannot be converted into SQL (for example Date Formats) it is commented.
Alternativel the SQL Script can be generated by calling the Stored Procedure GENERATE_SQL
2. If everything is ok with the SQL Script, just run it (Note: CREATE OR REPLACE TABLE must be used)
Per default the data witn the table will be preserved, no need to save the data and copy them back
The liked objects, such as logical files, Indexes, views, triggers, constraints are preserved
There is not even a need to recompile the RPG programs.

Birgitta

[jayvaughn]

Even though an SQL Index cannot be specified within an SQL-Statement, it can be used in composition with native I/O like any keyed logical file.
For more Information just have a look at the following article:

SQL indexes and native I/O ? no contradiction
Profit from the use of SQL indexes when native I/O is used

https://www.ibm.com/developerworks/ibmi/library/i-sql-indexs-and-native-io/index.html

There is no need to convert the DDS described physical file into an SQL table (even though it is recommended) before using SQL Features.
Converting a DDS described physical into an SQL Table is no problem either:
1. Generate the SQL Code with Reverse Engineering
Wizard in Client Access Database or better ACS - Access Client Solution - Schemas). Position on the appropriate database object right click and select GENERATE SQL.
Make sure you set the CREATE OR REPLACE option.
An SQL Script will be generated.
If the physical file includes something that cannot be converted into SQL (for example Date Formats) it is commented.
Alternativel the SQL Script can be generated by calling the Stored Procedure GENERATE_SQL
2. If everything is ok with the SQL Script, just run it (Note: CREATE OR REPLACE TABLE must be used)
Per default the data witn the table will be preserved, no need to save the data and copy them back
The liked objects, such as logical files, Indexes, views, triggers, constraints are preserved
There is not even a need to recompile the RPG programs.

Birgitta

understood Birgitta - that covers converting a pf to a table, which I am well acquainted with - my obstacle is described in my original post.

[Rocky]

Thx rocky. Yes this is to appease most of the current code base which is all native rpg rla. Can you expand a bit more on the srtseq to *langidshr? In what case would this be needed?

The *LANGIDSHR is for SQL - doesn't do anything for native I/O - to accomplish what you want you'd have to CREATE INDEX index_name ON table_name (UPPER(field_name));

[Rocky]

You wouldn't have to change the PF to SQL - just add the index,

[Rocky]

And obviously you're programs will have to use upper case for the values in SETLL, READE, etc.

[jayvaughn]

You wouldn't have to change the PF to SQL - just add the index,

You can add an SQL index to a native PF? didn't know that - will have to test it.

After some fiddling, i've arrived at....

1.) convert pf to table (as-is)
2.) convert LF to index (as-is) - however, set option to SRTSEQ = *LANGIDSHR
3.) no application changes are needed.

[Rocky]

Yes - you can create SQL Views and Indexes over PF - and technically the other way around - you can create DDS LF over SQL Tables - though I'm not sure why you'd ever do that.

The SRTSEQ is for an session environment as far as I know - I don't think there's any way to set that for the index itself - would be great...

If you use SQL RPG you can specify that attribute with the SET OPTION - it has to be physically the first SQL statement in your source - for example I use:


EXEC SQL
SET OPTION NAMING = *SYS,
CLOSQLCSR = *ENDMOD,
COMMIT = *NONE,
SRTSEQ = *lANGIDSHR,
DATFMT = *ISO,
COMPILEOPT = 'DBGVIEW(*LIST)';

The SRTSEQ=*LANGIDSHR tells the SQL environment that any tests are case insensitive - that is lower case and upper case have equal "weight" - so if you have:

EXEC SQL SELECT * FROM filea WHERE fieldname = 'abc";

It will find the row if the value of field name is "ABC", "abc", "AbC", etc.It doesn't care what case you use.

In using SQL you don't have to do anything to your tables - no need to convert the PF, no need to create the index (except for perhaps performance reasons)

If you want to retain using native I/O in RPG there's no need to convert the PF (can if you want to - just not necessary) - you'll have to create an index using the UPPER function for the key value... and your RPG program will have to convert the value to upper case before using it in SET## or READ? statements.

I do not know a way to make it case insensitive when using native RPG I/O - be cool to find out how if there is....

[jayvaughn]

Srtseq can be set at index level, pgm level or env level. I'm setting it at index level so that's really all I'll need to do.