iQuery

www.cozTools.com

www.MidrangeNews.com

Contact Us

COZTOOLS

SQL iQuery for IBM i

Member List Table Function (MBRLIST  UDtF)

SQL iQuery includes several helpful functions and procedures. For a list of those functions and stored procedures, visit this UDF page.

Download a free trial of SQL iQuery

MBRLIST( file, library )

The Cozzi SQL iQuery MBRLIST() UDTF allows users to query a source physical file's member list using the same column (field) names as produced by DSPFD *MBRLIST OUTPUT(*OUTFILE). Column names such as MLNAME (member name), MLFILE (source file name), and MLLIB (file's library) along with all the others columns are included.

Using any User Defined Table Function (UDTF) requires that it be enclosed in the SQL TABLE( ) function and a correlation name must be specified. Failure to follow these two requirements will result in an empty set or a warning message being issued.

Example Use:

MBRLIST Diagram

In the above example, a member list for source file QRPGLESRC in COZTOOLS library is produced. All members are listed and all columns are included.

Let's look at a more specific example. Often I'm called in to work on a new client's system and locating source members can sometimes be a challenge. Rather than roll my own, I now use the MBRLIST UDTF and ask SQL to search for the members in question. For example, suppose I want to list all source members whose names contain the word 'DATE' anywhere in the name, or anywhere in that member's text description; and what if I want only RPG source members. But there are RPG, RPGLE, SQLRPG and SQLRPGLE source members.

Using MBRLIST and SQL, it is almost too easy to perform this task. Here's the SQL SELECT statement that does this:

   SELECT MLFILE, MLLIB, MLNAME, MLSEU, MLMTXT 
   FROM TABLE( MBRLIST( 'QRPG*', '*ALLUSR') ) ML 
   WHERE MLNAME LIKE '%DATE%' or MLMTXT LIKE '%DATE%' and MLSEU LIKE '%RPG%'
   ORDER BY MLLIB, MLFILE, MLNAME

This query will produce a listing of member names containing the word 'DATE' within their name or within their member text description. The resultset is ordered by the source library, file and member name. The source file, library, member, type and text are included in the output. I helped out the processing time by including only QRPGxxxx source files, but the MLSEU like '%RPG%' selects only RPG source members.

You can run this SQL statement within the Interactive SQL client that IBM has been shipping with the OS for decades. However that environment is being sunset and users should move to some other tool, like SQL iQuery's RUNIQRY CL command. To run the above SELECT statement, type in RUNIQRY then press F4 to prompt, and type in the statement exactly as it appears above. The Command Prompter will double-up the quotes that appear around the source file and library names.

Here's what that looks like:

Our SQL iQuery product ships with several more UDFs and procedures. We try to keep an up-to-date list of them at this link.

Saving Frequently Run Queries in Source Members

You can run any SQL statement from within a source file member using SQL iQuery's RUNIQRY command. To illustrate, let's save the above SELECT statement as a source member named ML in source file QSQLSRC in library PRODLIB.

   SELECT MLFILE, MLLIB, MLNAME, MLSEU, MLMTXT 
   FROM  TABLE( MBRLIST( 'QRPG*', '*ALLUSR') ) ML 
   WHERE MLNAME LIKE '%DATE%' or MLMTXT LIKE '%DATE%' and MLSEU LIKE '%RPG%'
   ORDER BY MLLIB, MLFILE, MLNAME

To run the source member, simply adjust your RUNIQRY command as follow:

RUNIQRY SRCMBR(ML) SRCFILE(PRODLIB/QSQLSRC)

The results are the same as entering the SQL statement on the SQL parameter.

Suppose you want to be able to specify the Source File and Member name at runtime or within a CL program; perhaps as a parameter passed to that CL program. To do that, include our substitution variable identifiers. You have two choices. Using ordinals (sequential  numbers) such as &1, &2, &3, etc. or using variable names, such as &FILE, &LIB, &TYPE.

To simplify our example, I'm going to allow the source file and library name to be passed in via RUNIQRY's SETVAR parameter. I'll use &SRCF and &LIB as the variable names. Here is the completed source member with the revised SELECT statement:

   SELECT MLFILE, MLLIB, MLNAME, MLSEU, MLMTXT 
   FROM  TABLE( MBRLIST( '&SRCF', '&LIB') ) ML 
   ORDER BY MLLIB, MLFILE, MLNAME

In this example, I replaced 'QRPG*' with '&SRCF' and '*ALLUSR' with '*&LIB'. The RUNIQRY command will insert the source file and library names from the SETVAR parameter. Again to simplify things, I removed the WHERE clause although you could have provided SETVAR parameters for it as well.

To run the above source member:

RUNIQRY SRCMBR(ML) SRCFILE(PRODLIB/QSQLSRC)  SETVAR((SRCF 'QRPG*') (LIB 'COZTOOLS'))

Substitution variables are specified in the source member with a prefix. We use the same prefix as the legacy Query/400 (the & ampersand). The VARPREFIX parameter of RUNIQRY defaults to VARPREFIX(*AMP) and automatically prefixes any SETVAR variable names with an ampersand if they do not already contain one. The source itself must include the full substitution variable symbol including its prefix.

 

Check out SQL iQuery for yourself. Download our free trial today.

Copyright © 2014 Cozzi Productions, Inc. All Rights Reserved.