Midrange News for the IBM i Community

Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
has no ratings.
Published: 29 Jun 2015
Revised: 09 Jul 2015 - 3241 days ago
Last viewed on: 22 May 2024 (4288 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

QADBXFIL vs SYSTABLES Published by: Bob Cozzi on 29 Jun 2015 view comments(1)

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.


Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: avrSteve
Premium member *
Posted: 8 years 10 months 14 days 10 hours 10 minutes ago

The view defitiniton column is helpful if you have UDF's embedded in views & you need to change parameters to the UDF. Doing a sql LIKE against the column you describe saves a lot of pain. I needed to change a UDf & had trouble finding the views that use the UDF and after snooping around came up woth the stmnt below.