SQL iQuery Script Built-in Function



Convert Array Values to a String with optional separator.

The ARR_STRING built-in functions converts a Session Variable with multiple values into one long value. An optional separator may be specified.


The first format of ARRAY_STRING() built-in function returns the values assigned to the Session Variable as a delimited string. The delimiter and optional wrapper may be specified to control the characters used for those purposes.
The second form of ARRAY_STRING() built-in function returns the values assigned to the Session Variable by assigning each value a fixed length in the result. The user may specify the length each value occupies in the resulting string.


  1. A session variable with 1 or more values.
  2. The separator between each returned value when more than one value is returned or the fixed-length of each value returned.
  3. A wrapper (up to 2 characters in length) that is used to surround each resulting value (but not the separator). This parameter is only valid if parameter 2 is specified but not as a length argument.
SELECT NAME  into &libl                            
    FROM qsys2.library_list_info       
    WHERE TYPE in ('CURRENT','USER')            
    order by colno;                                
eval &liblCSV = array_stg(&libl,',','\'');         
select * from qsys2.output_queue_info              

What"s going on here: The IBM i Db2 Service named LIBRARY_LIST_INFO is run with each library being inserted as a value into the &LIBL variable. I include on the User-portion of the library list along with *CURLIB. Effectively we've omitted the System library list library names along with any Product Library name. Next we use ARRAY_STG to convert the values in &LIBL into a Comma-Separated List, such as: 'QGPL','DEVTEST','TESTDATA',QTEMP','COZTOOLS'

You will note that the 3rd parameter contains a single quote and is "escaped". The back slash is how you escape special characters in SQL iQuery Script, except on SQL statements which use the SQL-notation.
That new variable is inserted into the SQL SELECT statement's WHERE clause. The list of Output Queue names is restricting it to just the entries in the &LIBLCSV variable, which is the user portion of the library list. On my system, this brought up the Output Queues in QGPL and DEVTEST, but avoided those in QUSRSYS which is traditionally where most of the *OUTQ objects are created.