Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
QADBXFIL vs SYSTABLES
has no ratings.
Published: 29 Jun 2015
Revised: 09 Jul 2015 - 750 days ago
Last viewed on: 28 Jul 2017 (1728 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

COMMENTS

(Sign in to Post a Comment)
Posted by: avrSteve
Premium member *
Comment on: QADBXFIL vs SYSTABLES
Posted: 2 years 19 days 21 hours 13 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.

 

SELECT * FROM QSYS2.Views
  WHERE TABLE_SCHEMA = yourLibrary AND
        VIEW_DEFINITION LIKE '%yourUDF%';