Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL iQuery Documentation - UDF and UDTF
has no ratings.
Published: 17 Jan 2020
Revised: 19 Jan 2020 - 274 days ago
Last viewed on: 19 Oct 2020 (157 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.

Supplied SQL User Defined Functions and Table Functions Published by: Bob Cozzi on 17 Jan 2020 view comments

List File Dependencies (Based-On Files)

The iQuery.depFile() UDTF returns one row for each file dependant on the file name specified. The results are similar to the QDBLDBR API or the DSPDBR CL command.

Parameters

iQuery.depfile( library, file, member, rcdfmt )

The library is the name of the library that contains the file(s) whose dependent files are to be returned. In addition to a specific library name, the special values *ALL, *ALLUSR, *USRLIBL, *LIBL, and *CURLIB may be specified.

The optional file name is the name of the file whose dependent files are to be returned. A generic or full name may be specified. The default is *ALL.

The optional member name specifies the name of the file's member to be used as the dependent files to be returned.  The default is *ALL but *FIRST and *LAST may also be specified.

The optional rcdfmt (record format) name specifies the name of the record format that is used by the dependent files returned.

Usage:

If only the LIBRARY or LIBRARY and FILE parameters are specified, then a file dependency list is created. If only the FILE parameter is not specified, then the *ALL is used.

If the MEMBER parameter is specified, then a member dependency list is created.

If the RCDFMT parameter is specfied, then a record format sharing dependency list is create.

ResultSet Columns

Column Type When Returned Description
LIBRARY varchar(10) F, M, R The library name of the file whose dependent information is returned
FILE varchar(10) F, M, R The name of the file whose dependent information is returned.
MBR varchar(10) M The member name of the file whose dependent information is returned.
RCDFMT varchar(10) R The record format name of the file whose dependent information is returned.
DEPLIBRARY varchar(10) F, M R The library name of the dependent file.
DEPFILE varchar(10) F, M R The name of the dependent file.
DEPMBR varchar(10) M The member name of the dependent file.
DEPTYPE varchar(16) F, M The type of dependency: CONSTRAINT, DATA, ACCPTH, ACCPTH OWNER, SQL VIEW
CSTLIBRARY varchar(10) F, M The library name containing the Constraint, if any.
CONSTRAINT varchar(258) F, M The constraint (if any).

When Returned Legend

  • F=File dependencies are requested
  • M=Member dependencies are requested
  • R=Record Format dependencies are requested

Example 1

The following creates a list of dependencies for the file named CUSTMAST in library PRODDATA.

select * from table(iQuery.depFile(library => 'PRODDATA', File => 'CUSTMAST')) dbr;

Example 2

The following creates a list of dependencies for the Member CHICAGO in the file named CUSTSALES in the library PRODDATA.

select * from table(iQuery.depFile(library => 'PRODDATA', File => 'CUSTSALES', member=>'CHICAGO')) dbr;

 

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

COMMENTS