ibm-midrange

AS400 - DB2 list all the dataq

Quite new to this AS400. I've tried this code and it works to list all the files:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE 
FROM QSYS2.SYSTABLES 
WHERE TABLE_SCHEMA = 'QGPL' 
ORDER BY TABLE_NAME

How do I list all the dataq inside the QGPL?

Solution

  • What version of IBM i are your running? IBM has added "SQL Services" around many OS APIs .. including QSYS2.DATA_QUEUE_ENTRIES()

    SELECT * FROM TABLE(QSYS2.DATA_QUEUE_ENTRIES(
                                             DATA_QUEUE => 'DQ1', 
                                             DATA_QUEUE_LIBRARY => 'TESTLIB'))
  ORDER BY ORDINAL_POSITION

    Should be available starting at 7.4 w/SF99704 Level 10 or higher

    If on out of date version, you could always built your own UDTF that calls the system API.

    EDIT

    On second look, it appears you are just wanting a list of data queues...
    Try OBJECT_STATISTICS table function

    SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('QGPL','DTAQ','*ALLSIMPLE')) X