Unconfigured Ad Widget

Unconfigured Ad Widget Module


Announcement Module
No announcement yet.

Is it possible to dynamically find last QHST table via SQL?

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Is it possible to dynamically find last QHST table via SQL?

    I have a handful of SQL queries that I have been running nearly every work day to monitor a few projects on hand. Each result then gets put into various spreadsheet files etc. It's interesting enough that I haven't minded doing it, but I admit it's more timeconsuming than it needs to be. I have GoAnywhere Director and decided I should probably just set up a job to run the queries for me and email me the results to save some time.

    The only one I haven't figure out how to handle is my query that parses the latest QHST file for only certain entries. The query itself works just fine, but I only see about 70-80 results per file and when I see that a new QHST file is created, I update the query manually and rerun it.
    If I program a scheduled job to run the checks, I don't know how to handle this aspect.

    Is there a way to run query against a library to get table names?

    It seemed like there should be a way, but I haven't found it. (I've seen some solutions that are for other systems or require API usage, but I'm looking to just run simple queries. Having to write a full blown program for this, just is too much side effort for everything else I'm juggling.)

    Thank you!

  • #2
    Howdy EB! Try this, and see if this is what you're looking for:

    SELECT * FROM qsys2/systables


    • #3

      I just gave that a shot. (from a session connected through SQuirreL SQL Client v3.4.0)
      I get the following:
      Error: [SQL5016] Qualified object name SYSTABLES not valid.
      SQLState:  42833
      ErrorCode: -5016
      Just for random kicks, I also tried it against QSYS instead of QSYS2, but no luck either.

      The iSeries is running v5r3, if that makes a difference. I'll take a look further into this, but I'm still open to suggestions.

      Thank you!


      • #4
        I didn't realize it at the time, but SYSTABLES is a view/logical. It's built over QADBXREF in QSYS. Try QSYS/QADBXREF, and see if that works.

        Also, when I use Squirrel, I can't use a slash for the library/file separator. I would key it like this: select * from qsys2.systables

        I hope one of those tables is there for you.
        Last edited by bbardini; 01-09-2013, 05:33 PM.


        • #5
          Oh ok. That was my brain failing then! I've been using SQuirreL for a while, but recently been using STRSQL again and guess I just mixed up which formats I should have been using, since I completely blanked on the syntax.

          I ran select * from qsys2.systables this morning and was able to get results just fine!
          select * from qsys2.systables where LEFT(TABLE_NAME,4)= 'QHST' gave me all the QHST tables and now I should be able to tweak that further to just get the records from the last file that match my other parsing query. Wheee!

          Thank you very much!


          • #6
            You are very welcome.