I had recently switched to using SYSTABLES in QSYS2 exclusively for checking for a file existance (using SQL) and/or translating a LONG FILE NAME to its SYSTEM NAME. SYSTABLES is great for that purpose.

However it was not long before I found out that SYSTABLES does NOT include all files on the system. It includes RELATIONAL DATABASE FILES. That is, file created with SQL DDL or IBM i DDS. Therefore files created without DDS (such as Legacy file or file created with CRTPF and a RCDLEN but no DDS) are NOT including in SYSTABLES. So I was getting a false response (file name not found) but the file actually existed.

So I've started using QADBXFIL in QSYS which has been there a while and includes the LONG FILE NAME to SYSTEM NAME conversion I needed. It turns out it includes all objects of type *FILE regardless of how they are created. So I've switched to it.

Initially, I started using the QDBRTVSN (Retrieve Short Name) API, which as the name implies, returns the "short" name or System Name for objects with long names. And issue I had with it, is that it doesn't work with DDM files nor with Remote Database connections. So I had to go with a table-based approach.

Therefore starting today, I now use QADBXFIL in QSYS (which is a Logical View) to perform these tasks.

One other thing I found; if the file is an SQL VIEW, there is a field in this file that contains the actual SQL VIEW statement used to create the View (the first 5000 characters of it anyway). So that may be helpful for something.