Variables and QMQRY

[ukpi1b]

Recently there were many interesting and useful publications related to embedded and dynamic SQL, host variables in RPG/RPGLE etc. Less popular are QM Queries publications. Once in a while you will find questions and answers related to variable usage in QM Queries. I guess not many of us use power of QM Queries. Following example explains how to build and execute QM Query on a fly. Suppose you have to identify all physical files in a library where two fields match selection criteria. Basically you are looking for a report where files, file fields and possible combination of field values are listed. It might sound a little complicated but really it is not. Two CL programs and 3 queries will do it. First program (IS120CL scans system tables and creates xxLIB/XBSM, calls IS121CL to build and executes query to get report. Example uses 2 fields and one library as input parameters. Field one. Field name contains ’MCU’- passed to program as parameter. Length is 12, field text contains ‘UNIT’. Field value is ‘ MI’. Field two. Field text contains ‘Company’ – passed to program as parameter. Length is 5. Field value is ‘00322’. Step1. Program IS120CL. Identify all files in given library where both fields could be found and create XBSM file to store result. Input parameters: &LIB – library (PRDDTA) &PF1- first field name contains ‘MCU’, &PF2 - second field text (contains ’Company’) I use SYSCOLUMNS and SYSTABLES provided by the system. Check column names in both tables: you may use column name as is or alternative names. Some columns in these tables are null capable, some are variable length columns, and some columns have binary data. Notice that not all files are listed in these tables: files created via CPYF or CRTDUPOBJ, outfiles for queries are not listed there. You may run DSPOBJD xxLIB/*ALL and DSPFFD xxLIB/*ALL to outfiles and use them instead. SFDTQC QMQRY uses all three variables and creates CSM file in QTEMP. Query SFDTQCX formats CSM data and created BCSM file in QTEMP. Save BCSM data in your library. Because both selected fields are character, DEC and; DEC2 columns are irrelevant to this particular case. Save query result in XBSM file. Step2 program IS121CL. Read XBSM file, build QMQRY and execute query for each record to extract data from respective file. Save query result in QTEMP/SFDTL. Note that result file field names and length are variables depending on data in XBSM file. Make report from this SFDTL file, assign meaningful column names as you like. Programs provide accurate result, but they are not perfect. You may add screen to pass parameters as well as selection criteria, add error handling and multiple members file processing code, probably use dynamic SQL especially if you don’t like use CLP. These queries and programs could be easily modified for any number of fields and selections within IBM restrictions. Next time I will post example with 3 variables (character and numeric).

isscc1.doc