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.
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 where OUTPUT_QUEUE_LIBRARY_NAME in (&LIBLCSV);
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.