Get Indexes only shows SQL indexes. Needs LF's as well.

[duieolson]

Liam, When right clicking on a table in the Schema Browser, and I choose the Get Index option on the drop down menu, the Results only show SQL indexes only. All Logical File indexes are skipped. I am suggesting show LF's with the sql indexes for a complete list of indexes.

To Reproduce Open ACS Schemas to a schema... Tables view and right click on a table that has LF's and SQL indexes. You will find a complete list.

Now Get Indexes in DB2 for IBM i extension. You will be missing LF's.

Environment

• OS: Windows
• Extension Version [DBV2 for IBMi v0.4.1]

VS Code: Version: 1.82.2 (system setup) Commit: abd2f3db4bdb28f9e95536dfa84d8479f1eb312d Date: 2023-09-14T05:55:25.390Z Electron: 25.8.1 ElectronBuildId: 23779380 Chromium: 114.0.5735.289 Node.js: 18.15.0 V8: 11.4.183.29-electron.0 OS: Windows_NT x64 10.0.19045

[worksofliam]

--
-- "Tables"
--
select TABLE_SCHEMA, TABLE_NAME, NATIVE_TYPE,
       case
         when sql_object_type = 'TABLE' then 'SQL TABLE'
         when FILE_TYPE = 'DATA' then 'NATIVE FILE'
         else 'SOURCE PHYSICAL FILE'
       end as file_type_name, IASP_NUMBER, TEXT_DESCRIPTION, FILE_TYPE, SQL_OBJECT_TYPE,
       LAST_ALTERED_TIMESTAMP, SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, FILE_LEVEL_ID, LEVEL_CHECK,
       FILE_OWNER, CREATE_PUBLIC_AUTHORITY, NUMBER_MEMBERS, MAXIMUM_MEMBERS, MAXIMUM_RECORD_LENGTH,
       NUMBER_KEY_FIELDS, MAXIMUM_KEY_LENGTH, TRIGGER_COUNT, CONSTRAINT_COUNT, NUMBER_BASED_ON_FILES,
       BASED_ON_FILES, -- BASED_ON_FILES column available at 7.5 or later
       ALLOW_READ, ALLOW_WRITE, ALLOW_UPDATE, ALLOW_DELETE,
       MAXIMUM_FILE_WAIT_TIME, MAXIMUM_RECORD_WAIT_TIME, FORCE_WRITE_RATIO, SELECT_OMIT,
       PROGRAM_DESCRIBED, DISTRIBUTED, FILE_VRM, EARLIEST_POSSIBLE_RELEASE, ALLOW_NULL_KEYS,
       ALLOW_NULL_DATA, PRIMARY_KEY, UNIQUE_CONSTRAINT, "VOLATILE", KEEP_IN_MEMORY,
       MEDIA_PREFERENCE, SOURCE_FILE_LIBRARY, SOURCE_FILE, SOURCE_FILE_MEMBER, ACCESS_PATH_KEYED,
       ACCESS_PATH_TYPE, ACCESS_PATH_MAINTENANCE, ACCESS_PATH_SIZE, LOGICAL_PAGE_SIZE,
       FORCE_KEYED_ACCESS_PATH, ACCESS_PATH_JOURNALED, ACCESS_PATH_RECOVERY, SRTSEQ_IND,
       SORT_SEQUENCE_LIBRARY, SORT_SEQUENCE, LANGUAGE_IDENTIFIER, ROUNDING_MODE, DECFLOAT_WARNINGS,
       NUMBER_RECORD_FORMATS, FORMAT_LEVEL_ID, FORMAT_NAME, RECORD_LENGTH, NUMBER_FIELDS,
       COMMON_CCSID, CONTAINS_EXPLICIT_CCSID, CONTAINS_MULTIPLE_CCSIDS, CONTAINS_UNICODE,
       CONTAINS_VARYING_LENGTH, CONTAINS_DATETIME, CONTAINS_GRAPHIC, CONTAINS_LOB, CONTAINS_ROWID,
       CONTAINS_UDT, CONTAINS_DATALINK, CONTAINS_DATALINK_FILE_LINK_CONTROL, CONTAINS_NULL,
       CONTAINS_DEFAULT, CONTAINS_IDENTITY, CONTAINS_ROW_CHANGE_TIMESTAMP,
       CONTAINS_USER_DEFINED_FUNCTION, ALLOCATE_STORAGE, CONTIGUOUS_STORAGE,
       MAXIMUM_DELETED_PERCENTAGE, INITIAL_RECORDS, INCREMENT_RECORDS, MAXIMUM_INCREMENTS,
       REUSE_DELETED_RECORDS, MATERIALIZED_QUERY_TABLE, PARTITIONED_TABLE,
       ROW_AND_COLUMN_ACCESS_CONTROL, TOTAL_SELECT_OMIT, FMTSLR_LIBRARY, FMTSLR_PROGRAM,
       IS_JOIN_LOGICAL, DYNAMIC_SELECTION, WITH_CHECK_OPTION, PHYSICAL_LOB, PHYSICAL_DATALINK,
       INDEX_COLUMN_IS_EXPRESSION, INDEX_EXPRESSION_HAS_UDF, INDEX_HAS_SEARCH_CONDITION,
       INDEX_SEARCH_CONDITION_HAS_UDF
  from QSYS2.SYSFiles
  where TABLE_SCHEMA = 'QGPL' and
        NATIVE_TYPE = 'PHYSICAL';
        
--
-- "Views"
--
select TABLE_SCHEMA, TABLE_NAME, NATIVE_TYPE,
       case
         when sql_object_type = 'TABLE' then 'SQL TABLE'
         when FILE_TYPE = 'DATA' then 'NATIVE FILE'
         else 'SOURCE PHYSICAL FILE'
       end as file_type_name, IASP_NUMBER, TEXT_DESCRIPTION, FILE_TYPE, SQL_OBJECT_TYPE,
       LAST_ALTERED_TIMESTAMP, SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, FILE_LEVEL_ID, LEVEL_CHECK,
       FILE_OWNER, CREATE_PUBLIC_AUTHORITY, NUMBER_MEMBERS, MAXIMUM_MEMBERS, MAXIMUM_RECORD_LENGTH,
       NUMBER_KEY_FIELDS, MAXIMUM_KEY_LENGTH, TRIGGER_COUNT, CONSTRAINT_COUNT, NUMBER_BASED_ON_FILES,
       BASED_ON_FILES, -- BASED_ON_FILES column available at 7.5 or later
       ALLOW_READ, ALLOW_WRITE, ALLOW_UPDATE, ALLOW_DELETE,
       MAXIMUM_FILE_WAIT_TIME, MAXIMUM_RECORD_WAIT_TIME, FORCE_WRITE_RATIO, SELECT_OMIT,
       PROGRAM_DESCRIBED, DISTRIBUTED, FILE_VRM, EARLIEST_POSSIBLE_RELEASE, ALLOW_NULL_KEYS,
       ALLOW_NULL_DATA, PRIMARY_KEY, UNIQUE_CONSTRAINT, "VOLATILE", KEEP_IN_MEMORY,
       MEDIA_PREFERENCE, SOURCE_FILE_LIBRARY, SOURCE_FILE, SOURCE_FILE_MEMBER, ACCESS_PATH_KEYED,
       ACCESS_PATH_TYPE, ACCESS_PATH_MAINTENANCE, ACCESS_PATH_SIZE, LOGICAL_PAGE_SIZE,
       FORCE_KEYED_ACCESS_PATH, ACCESS_PATH_JOURNALED, ACCESS_PATH_RECOVERY, SRTSEQ_IND,
       SORT_SEQUENCE_LIBRARY, SORT_SEQUENCE, LANGUAGE_IDENTIFIER, ROUNDING_MODE, DECFLOAT_WARNINGS,
       NUMBER_RECORD_FORMATS, FORMAT_LEVEL_ID, FORMAT_NAME, RECORD_LENGTH, NUMBER_FIELDS,
       COMMON_CCSID, CONTAINS_EXPLICIT_CCSID, CONTAINS_MULTIPLE_CCSIDS, CONTAINS_UNICODE,
       CONTAINS_VARYING_LENGTH, CONTAINS_DATETIME, CONTAINS_GRAPHIC, CONTAINS_LOB, CONTAINS_ROWID,
       CONTAINS_UDT, CONTAINS_DATALINK, CONTAINS_DATALINK_FILE_LINK_CONTROL, CONTAINS_NULL,
       CONTAINS_DEFAULT, CONTAINS_IDENTITY, CONTAINS_ROW_CHANGE_TIMESTAMP,
       CONTAINS_USER_DEFINED_FUNCTION, ALLOCATE_STORAGE, CONTIGUOUS_STORAGE,
       MAXIMUM_DELETED_PERCENTAGE, INITIAL_RECORDS, INCREMENT_RECORDS, MAXIMUM_INCREMENTS,
       REUSE_DELETED_RECORDS, MATERIALIZED_QUERY_TABLE, PARTITIONED_TABLE,
       ROW_AND_COLUMN_ACCESS_CONTROL, TOTAL_SELECT_OMIT, FMTSLR_LIBRARY, FMTSLR_PROGRAM,
       IS_JOIN_LOGICAL, DYNAMIC_SELECTION, WITH_CHECK_OPTION, PHYSICAL_LOB, PHYSICAL_DATALINK,
       INDEX_COLUMN_IS_EXPRESSION, INDEX_EXPRESSION_HAS_UDF, INDEX_HAS_SEARCH_CONDITION,
       INDEX_SEARCH_CONDITION_HAS_UDF
  from QSYS2.SYSFiles
  where TABLE_SCHEMA = 'QGPL' and native_type = 'LOGICAL' and access_path_keyed = 'NO' ;
  
       
--
-- "Indexes"
--
select TABLE_SCHEMA, TABLE_NAME, NATIVE_TYPE,
       case
         when sql_object_type = 'TABLE' then 'SQL TABLE'
         when FILE_TYPE = 'DATA' then 'NATIVE FILE'
         else 'SOURCE PHYSICAL FILE'
       end as file_type_name, IASP_NUMBER, TEXT_DESCRIPTION, FILE_TYPE, SQL_OBJECT_TYPE,
       LAST_ALTERED_TIMESTAMP, SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME, FILE_LEVEL_ID, LEVEL_CHECK,
       FILE_OWNER, CREATE_PUBLIC_AUTHORITY, NUMBER_MEMBERS, MAXIMUM_MEMBERS, MAXIMUM_RECORD_LENGTH,
       NUMBER_KEY_FIELDS, MAXIMUM_KEY_LENGTH, TRIGGER_COUNT, CONSTRAINT_COUNT, NUMBER_BASED_ON_FILES,
       BASED_ON_FILES, -- BASED_ON_FILES column available at 7.5 or later
       ALLOW_READ, ALLOW_WRITE, ALLOW_UPDATE, ALLOW_DELETE,
       MAXIMUM_FILE_WAIT_TIME, MAXIMUM_RECORD_WAIT_TIME, FORCE_WRITE_RATIO, SELECT_OMIT,
       PROGRAM_DESCRIBED, DISTRIBUTED, FILE_VRM, EARLIEST_POSSIBLE_RELEASE, ALLOW_NULL_KEYS,
       ALLOW_NULL_DATA, PRIMARY_KEY, UNIQUE_CONSTRAINT, "VOLATILE", KEEP_IN_MEMORY,
       MEDIA_PREFERENCE, SOURCE_FILE_LIBRARY, SOURCE_FILE, SOURCE_FILE_MEMBER, ACCESS_PATH_KEYED,
       ACCESS_PATH_TYPE, ACCESS_PATH_MAINTENANCE, ACCESS_PATH_SIZE, LOGICAL_PAGE_SIZE,
       FORCE_KEYED_ACCESS_PATH, ACCESS_PATH_JOURNALED, ACCESS_PATH_RECOVERY, SRTSEQ_IND,
       SORT_SEQUENCE_LIBRARY, SORT_SEQUENCE, LANGUAGE_IDENTIFIER, ROUNDING_MODE, DECFLOAT_WARNINGS,
       NUMBER_RECORD_FORMATS, FORMAT_LEVEL_ID, FORMAT_NAME, RECORD_LENGTH, NUMBER_FIELDS,
       COMMON_CCSID, CONTAINS_EXPLICIT_CCSID, CONTAINS_MULTIPLE_CCSIDS, CONTAINS_UNICODE,
       CONTAINS_VARYING_LENGTH, CONTAINS_DATETIME, CONTAINS_GRAPHIC, CONTAINS_LOB, CONTAINS_ROWID,
       CONTAINS_UDT, CONTAINS_DATALINK, CONTAINS_DATALINK_FILE_LINK_CONTROL, CONTAINS_NULL,
       CONTAINS_DEFAULT, CONTAINS_IDENTITY, CONTAINS_ROW_CHANGE_TIMESTAMP,
       CONTAINS_USER_DEFINED_FUNCTION, ALLOCATE_STORAGE, CONTIGUOUS_STORAGE,
       MAXIMUM_DELETED_PERCENTAGE, INITIAL_RECORDS, INCREMENT_RECORDS, MAXIMUM_INCREMENTS,
       REUSE_DELETED_RECORDS, MATERIALIZED_QUERY_TABLE, PARTITIONED_TABLE,
       ROW_AND_COLUMN_ACCESS_CONTROL, TOTAL_SELECT_OMIT, FMTSLR_LIBRARY, FMTSLR_PROGRAM,
       IS_JOIN_LOGICAL, DYNAMIC_SELECTION, WITH_CHECK_OPTION, PHYSICAL_LOB, PHYSICAL_DATALINK,
       INDEX_COLUMN_IS_EXPRESSION, INDEX_EXPRESSION_HAS_UDF, INDEX_HAS_SEARCH_CONDITION,
       INDEX_SEARCH_CONDITION_HAS_UDF
  from QSYS2.SYSFiles
  where TABLE_SCHEMA = 'QGPL' and native_type = 'LOGICAL' and access_path_keyed = 'YES' ;

[worksofliam]

https://www.ibm.com/docs/en/i/7.3?topic=views-sysfiles

[davecharron]

https://www.ibm.com/docs/en/i/7.5?topic=views-syspartitionindexes

Use this view when you want to see index information for indexes built on a specified table or set of tables. The information is similar to that returned by the Work with Indexes found in IBM i Access Client Solutions (ACS) Schemas.

[duieolson]

Excellent point Dave. From a developer standpoint. I am looking for all LFs and indexes so I can see what indexes are availble on a table, to better structure my embedded sql, or, to tell if I am going to need a new index as part of my pgm development. Dave's referenced SYSPARTITIONINDEXES shows us both as well as the key fields in order on one row along wtih many other very useful columns of info. Wonderful!