Midrange News for the IBM i Community

Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
SQL iQuery Documentation - UDF and UDTF
has no ratings.
Published: 18 Jun 2019
Revised: 20 Jun 2019 - 487 days ago
Last viewed on: 19 Oct 2020 (199 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
  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.MBRLIST() - UDTF Published by: Bob Cozzi on 18 Jun 2019 view comments

Retrieve a File's Member List

The iQuery.mbrList() UDTF returns one row for for each member in the specified database file table name. The resultSet is purposely formatted to be similar to the DSPFD *MBRLIST OUTPUT(*OUTFILE) CL command results--but is more flexible and much faster.

UDF Return Type Description
mbrList TABLE Returns a resultSet that contains the list of member names and properties from the specified database table.


table( iQuery.mbrList( 'library', 'physical file', 'member name', 'text description' )) ml

The library name is the name of the library that contains the physical file named on the 2nd parameter.

The physical file is the name of the table whose member list is to be generated.

The optional member name identifies the members to be listed. Specify a generic name. If unspecified *ALL is the default. Use a generic member name, such as 'OE05*' or 'PO100%'.

The optional text description parameter may be specified to reduce the resultSet based on the text description. Use a generic text such as 'SALES%' or 'SALES*', or even '*ORD*'.

Result Set Columns

Please note, the resultSet is purposely based on the output from the DSPFD *MBRLIST CL command. So the sequence of columns and column names are based on that CL command's results. However iQuery.mbrlist() does not use DSPFD to generate its resultSet.

Column Type Description
MLRCEN char(1) The century digit (as text) of when the member list is produced. 
MLRDAT char(6) The date when the member list is produced.
MLRTIM char(6) The time when the member list is produced.
MLFILE char(10) The file name of the file whose member list is produced.
MLLIB char(10) The library name of the file returned in the MLFILE column.
MLFATR char(10) The file attribute of the file returned in the MLFILE column.
MLSYSN char(8) The system name where the file exists.
MLASP dec(3,0) The ASP that contains the file.
MLNOMB dec(5,0) The number of members in the file specified in the MLFILE column.
MLNAME char(10) The member name.
MLNRCD dec(10,0) The number of records in the member.
MLNDTR dec(10,0) The number of deleted records in the member.
MLSIZE dec(15,0) The data space size, in bytes.
MLRCDL dec(5,0) The record length.
MLSEU char(10) The legacy SEU attribute column. See MLSEU2 for current value.
MLCCEN char(1) The member creation century digit as text.
MLCDAT char(6) The member creation date
MLCTIM char(6) The member creation time
MLCHGC char(1) The member last changed century digit as text.
MLCHGD char(6) The member last changed date
MLCHGT char(6) The member last changed time
MLMTXT char(50) Member text description
MLUCEN char(1) The member last used century digit as text.
MLUDAT char(6) The member last used date
MLUTIM char(6) The member last used time
MLUCNT dec(5,0) Days used count
MLTCEN char(1) Century when Last Used Date was Reset
MLTDAT char(6) Date when last Used Date was Reset
MLLFILE varchar(128) Long file/table name
MLLLIB varchar(128) Long library/schema name
MLLMBR varchar(128) Long member name
CREATEDTS timeStamp TimeStamp when the member was added/created in the file.
LASTCHGDTS timeStamp TimeStamp when the member was last changed.
LASTUSEDDATE Date Date when the member was last used.


select * from table( iQuery.mbrList( 'IQUERY','QIQMACRO', NULL, '*JOB*')) ml;

The output from the above UDTF would list all the members in file QIQMACRO in the IQUERY library that contain the word "JOB" somewhere in their text description.

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