Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
www.cozTools.com/qryfile
Chicagoland
SQL iQuery Documentation - UDF and UDTF
has no ratings.
Published: 20 Jun 2019
Revised: 19 Jul 2019 - 1741 days ago
Last viewed on: 22 Apr 2024 (798 views) 
  1. SQL iQuery - RUNiQRY CL Command
  2. CSV() - Read and Parse Comma Separated Values File
  3. CSV_VAL() Extract CSV Column Value
  4. CSV_XXXX() Extract CSV Column Value as xxxx
  5. CSV_COUNT - Retrieve Field Count
  6. ObjExist - Check Object Exits
  7. DTAARA - Data Area
  8. encode_URL Encode URL for the Web
  9. encode_TAG Encode HTML TAG Content
  10. FLDLIST - List the Fields of a Table
  11. FROMHEX - Fold 2-hex Chars into 1 Char
  12. GET CPUCNT, CURLIB, ENV, SRLNBR, SYSNAME
  13. MD5_HASH - UDF - Return MD5 Hash
  14. ifsStat - Get IFS File Attributes
  15. ifsExists - Check if IFS File Exists
  16. ifsFile - Query Contents of IFS Text File
  17. ifsDIR - List IFS Directory Entires
  18. DEPFILE - List Dependent Database Relations
  19. JOB - Get Job Name Component
  20. Job_ATTR - Job Attributes
  21. JOB_DATE Retrieve the Job Date
  22. JOBLOG - Write Message to Joblog
  23. Library List UDF and Procedures
  24. MBRLIST - Member List
  25. MCHINFO Machine Type and Model Number and OS version
  26. OSVER and OSVRM Get IBM i Version/Release
  27. PrintPDF - Print a PDF to a PDF Compatible Printer
  28. CPYTOPDF - Copy SPOOLED File to PDF file
  29. OBJ_LIST List Object in a Library
  30. LIB_LIST List Descriptions of Libraries on the Library List
  31. OBJ_EXPORTS List Exported Items from an Object
  32. OBJ_STRUCT Retrieve Objects Components
  33. RTVCMDD Retrieve Command Definition
  34. RTVJOBA - Retrieve Job Attributes
  35. RTVJOBD - Retrieve Job Description
  36. RTVLASTSPLF - Retrieve ID of Last SPOOLED File for this Job
  37. RTVxxx - Retrieve Various Objects Description Table Functions
  38. WATCHLIST - List Current Watches
  39. Polymorphic Qualified Name Syntax

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.

iQuery.lib_list - UDTF Published by: Bob Cozzi on 20 Jun 2019 view comments

List Descriptions of the Libraries on the Library List

The iQuery.lib_list() UDTF returns one row for each library on the library list. Unlike the LIBRARY_LIST_INFO view in the QSYS2 library, this UDTF returns object-related information along with the library list library names.

Parameters

iQuery.lib_List(  )

This UDTF has no parameters.

Result Set Columns

Column Type Description
LLSEQ smallint The sequence the library appears on the library. UDTF do not return ordered lists in the order specified. Therefore to return the library list in the proper sequence, specify ORDER BY LLSEQ on the SELECT statement.
OBJNAME varchar(10) The name of the library.
LLTYPE varchar(15) The library list type where the libary occurs. USER, SYSTEM, PRODUCT or CURRENT
OBJATTR varchar(10) The Object Attribute. For Libraries, this column is either: PROD or TEST
OBJTEXT varchar(50) The text description of the library.
OBJOWNER varchar(10) The library owner.
OBJCREATOR varchar(10) The user profile of the user who created the library.
CRTDATE DATE The Library Creation Date.
CRTTIME TIME The Library Creation Time
CRTSYSNAME varchar(10) The system on which the library was created.
DAMAGED varchar(10) Damed Object Attribute: *DAMAGED, *PARTIAL or blank.
ASP smallint The ASP where the library exists.
DOMAIN varchar(10) The domain where the library exists (system or user domain)
CRTVRM varchar(9) The version of IBM i on which the object was created. The format is VxRyMz
CHGDATE DATE The Last Changed Date of the object.
CHGTIME TIME The Last Changed Time of the object.
LASTSAVEDDATE DATE Last Date when a save of the object occurred.
LASTSAVEDTIME TIME Last Time a save of the object occurred.
LASTRESTOREDDATE DATE The date when the object was last restored.
LASTRESTOREDTIME TIME Last Time the object was restored.
LASTUSEDDATE DATE The date when the object was last used.
LASTUSEDTIME TIME The time on the last used date when the object was used (often not specified)
DAYSUSED INT The number of days the object has been used.

Example

The following creates a list of all objects in library COZTOOLS and includes the creation date and last used date.

select * from table(iQuery.lib_list()) ll ORDER BY llSeq;

An excerpt from the resultSet generated by the above statement follows:

LLSEQ  OBJNAME     LLTYPE           OBJATTR     OBJTEXT                                             OBJOWNER    OBJCREATOR 
    1  QSYS        SYSTEM           PROD        System Library                                      QSYS        *IBM       
    2  QSYS2       SYSTEM           PROD        System Library for CPI's                            QSYS        *IBM       
    3  QHLPSYS     SYSTEM           PROD                                                            QSYS        QLPINSTALL 
    4  QUSRSYS     SYSTEM           PROD        System Library for Users                            QSYS        *IBM       
    5  IQUERY      PRODUCT          TEST                                                            QPGMR       COZZI      
    6  COZTOOLS    CURRENT          TEST        Cozzi APPTOOLS - Developer Tools                    QPGMR       COZZI      
    7  COZTOOLS    USER             TEST        Cozzi APPTOOLS - Developer Tools                    QPGMR       COZZI      
    8  COZDEV      USER             TEST        COZTOOLS Development Source Library                 COZZI       COZZI      
    9  QGPL        USER             PROD        General Purpose Library                             QSYS        *IBM       
   10  QTEMP       USER             TEST                                                            QSYS        QSYS       
   11  SENDMAIL    USER             TEST        Example of using Java Sendmail from RPG IV          QPGMR       COZZI

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

COMMENTS