This is the second part of a two part story. In the first part I described how I had downloaded a list of libraries from an IFS file to a DDL table just using SQL. In this part of the story I want to check any of job descriptions on this IBM i partitions contain one of these libraries.
In the "real world" there were 58 libraries. In this example I am only using five. To check what these libraries are called I can using the following SQL statement over the file I built in part one:
SELECT * FROM OLDLIBS |
The libraries I will be using are:
LIBRARY ------- LIB1 LIB2 LIB3 LIB4 LIB5 |
There is a SQL view, JOB_DESCRIPTION_INFO, that allows me to search for information about all the job descriptions on the partition, including its library list.
Alas, the library list is contained in a single column, which is 2,750 bytes. If I was searching for a single library I could use the following, which contains a wildcard in the Where clause:
SELECT JOB_DESCRIPTION_LIBRARY,JOB_DESCRIPTION,LIBRARY_LIST FROM QSYS2.JOB_DESCRIPTION_INFO WHERE LIBRARY_LIST LIKE '%QGPL%' |
I do not want to write and run separate SQL statements for each of the libraries in the table OLDLIBS, as in the "real world" it contained 58 libraries. I just want one statement and one set of results.
The Where clause would need to contain a subselect fetching rows from OLDLIBS. But how to code a wildcard with values from columns from the results of the subselect?
Using Google I found some things similar in different flavors of SQL, which I was able to adapt for Db2 for i. When I was done I had the following:
01 SELECT JOB_DESCRIPTION_LIBRARY AS "Lib", 02 JOB_DESCRIPTION AS "Jobd", 03 LIBRARY_LIST AS "Libl" 04 FROM QSYS2.JOB_DESCRIPTION_INFO 05 WHERE EXISTS (SELECT 1 FROM OLDLIBS B 06 WHERE LIBRARY_LIST LIKE '%' || B.LIBRARY || '%') |
Lines 1 – 3: The columns I want to return in the results. I have given them short column headings too.
Lines 5 and 6: The Where clause contains EXIST, this means that the subselect must return a true value, the number 1, for the row to be included in the results. All I need to do is to concatenate the percentage symbol ( % ) with the column name to get:
%LIB1% |
And yes I always use the double pipe symbols ( || ) to indicate concatenation rather than CONCAT.
The results look like:
Lib Jobd Libl ----- ------ ---------------------------- QGPL JOHNB QTEMP LIB5 QGPL LIB2 CASSIE QTEMP LIB2 LIB3... LIB1 TOMTE QTEMP LIB1 LIB2... |
Using these results I know which job descriptions I need to change or delete before I delete the libraries.
This article was written for IBM i 7.4, and should work for some earlier releases too.
Very clever! Well done!
ReplyDeleteNice to read more on QSYS2.JOB_DESCRIPTION_INFO!
ReplyDeleteTo avoid false positives (%LIB1% matches LIB10, ZLIB1...) here are my 2 cents:
1. Stay with the SUBSELECT/LIKE Theme:
`...where LIBRARY_LIST like '%' || rpad(trim(b.library),11, ' ') || '%'...`
>> The list is an array of 11 character entries.
>> Each entry contains a ten character
>> name followed by one blank.
2. Playing with DB2 features
SELECT JOB_DESCRIPTION_LIBRARY AS "Lib",
JOB_DESCRIPTION AS "Jobd",
LIBRARY_LIST AS "Libl"
FROM QSYS2.JOB_DESCRIPTION_INFO AS I
WHERE EXISTS (SELECT 1
FROM OLDLIBS B
-- revisited 'filter'
CROSS JOIN LATERAL (
SELECT ELEMENT
FROM TABLE(SYSTOOLS.SPLIT(TRIM(I.LIBRARY_LIST),' ')) S
WHERE S.ELEMENT = B.LIBRARY
) JLIB
)
I was going to be Mr. SmartyPants and describe how LOCATE was so much faster than LIKE. When I did the SQL Analyzer though, the difference was minuscule with LIKE winning 33.966 to 34.816. What I do prefer about LOCATE is getting rid of the ampersands. Now, in the past, I did test a batch job and for that condition with those files, LOCATE was much faster than LIKE so you would do well to consider both depending on the application.
ReplyDeleteSELECT JOB_DESCRIPTION_LIBRARY,JOB_DESCRIPTION,LIBRARY_LIST
FROM QSYS2.JOB_DESCRIPTION_INFO
WHERE LOCATE('QGPL',LIBRARY_LIST) <> 0
I love your tip!
ReplyDelete