Determining if You Have Access Paths at *MAX4GB

To reduce file contention, since V4R1M0 it has been IBM's recommendation to change all files from Maximum Access Path Size from *Max4GB to *Max1TB . By making this change, it allows the system to lock only rows and not the entire file when key range estimates are performed.  In addtion *MAX1GB access paths are more efficient and almost always takes less space than *MAX4GBones.

Arrival sequence physical files should be changed as well. An access path will not be rebuilt but this change will allow any constraint built over the physical file will be *MAX1TB and not *MAX4GB.

Owning files of access paths need to be changed in order to change the access path size. Changing one of the files that shares the access path will not change the access path.

Note: Do not change the access path size on any system files (they usually begin with Q). In addition, if the system has any files created by a third party, the third party should be contacted before changing the access path size.

The following are examples of how you can determine which files on your system are currently at *MAX4GB:

1.	Use the Qsys2.syspartitionindexes to find existing keyed Logical Files and Keyed SQL Indexes that are at *MAX4GB: You can use the SQL: select SYSTEM_INDEX_NAME, SYSTEM_INDEX_SCHEMA, INDEX_SIZE, ACCPTH_TYPE from qsys2.syspartitionindexes where ACCPTH_TYPE = '4 GB'
2.	Find existing keyed Logical Files and keyed SQL Indexes that are at *MAX4GB with the DSPFD command. a. Use the DSPFD command for Logical Files and Indexes: DSPFD FILE(*ALL/*ALL) TYPE(*ATR) OUTPUT(*OUTFILE) FILEATR(*LF) OUTFILE(library/lfoutfile) b. Query the output file to find out what files must be changed to *MAX1TB. This query will return records that have an access path size of *MAX4GB. Using IBM SQL/400: You can adapt this query and select other fields. You can see an explanation of the fields by issuing the DSPFFD command on the output file that was created from the DSPFD command.      select LGFILE, LGLIB, LGFTYP, LGACCP, LGSQLT, LGAPSZ      from library/lfoutfile      where LGAPSZ = '0'            (0 = *MAX4GB)                    AND LGACCP = 'K' (K = keyed access path) Using Query for i (Query/400): This is the Select Records portion you would use for a Query for i query, using the output you created with the DSPFD command listed above:      Select record tests      AND/OR   Field             Test     Value (Field, Numbers, or 'Characters')               LGAPSZ            EQ       '0'      AND      LGACCP            EQ       'K'
3.	Find existing keyed Physical Files and keyed SQL Tables that are at *MAX4GB: a. Use the DSPFD command for Physical Files and Tables: DSPFD FILE(*ALL/*ALL) TYPE(*ATR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(library/pfoutfile) b. Query the output file to find out what files must be changed to *MAX1TB. This query will return records that have an access path size of *MAX4GB. Using SQL: You can adapt this query and select other fields. You can see an explanation of the fields by issuing the DSPFFD command on the output that was created from the DSPFD command.      select PHFILE, PHLIB, PHFTYP, PHACCP, PHSQLT, PHAPSZ      from library/pfoutfile      where PHAPSZ = '0'        (0 = *MAX4GB)        AND PHACCP = 'K'       (K = keyed access path)  Using Query for i: This is the Select Records portion you would use for a Query for i query, using the output you created with the DSPFD command listed above:      Select record tests      AND/OR   Field             Test     Value (Field, Numbers, or 'Characters')               PHAPSZ            EQ       '0'      AND      PHACCP            EQ       'K' The fields LGAPSZ and PHAPSZ shows the access path size where 0=*MAX4GB and 1= *MAX1TB. The queries will return all files that are keyed that have an access path size of *MAX4GB.