Announcement

Announcement Module
Collapse
No announcement yet.

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

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

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

    Hello,
    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!
    EB

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

    SELECT * FROM qsys2/systables

    Comment


    • #3
      Howdy!

      I just gave that a shot. (from a session connected through SQuirreL SQL Client v3.4.0)
      I get the following:
      Code:
      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!
      EB

      Comment


      • #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.

        Comment


        • #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!

          Comment


          • #6
            You are very welcome.

            Comment

            Working...
            X