Is there a way to retrieve the data displayed on the 'Display License Users' report using SQL? I'd like to automate this report so I do not have to do the WRKLICINF command each time I want an updated listing.
oh you could try this to get rows from the spool file... SELECT * FROM QSYS2.SPLF_INFO WHERE SPLF_NAME LIKE '%Display License Users%' ORDER BY CREATED_TIMESTAMP DESC FETCH FIRST 50 ROWS ONLY;
you could try some SQL queries like SELECT USER_PROFILE_NAME FROM QSYS2.USER_INFO ORDER BY USER_PROFILE_NAME; SELECT DISTINCT USER_NAME FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) AS AJ ORDER BY USER_NAME; to see what field names are available and then customize a query to get just what you need. If you can't output to a file you could probably just screen grab, depending on how many records you end up with. My systems usually only have like 50 profiles max.
When I do a WRKLICINF PRDID(5R33M7X) from the command line then place a '9' in the Opt field next to the 5R33M7X Product a spool file titled 'Display License Users' is created. I would like to run an SQL statement that would provide me that same list.
"That SQL statement does not list the licensed users. It just gives the user counts. I'm looking for a way to query a list of the licensed user profiles." Ok the "IBM side" is not by profile, you could have 100 profiles on the iSeries and only a 50 user license, First come first served. Are you looking for current active users?
The WRKLICINF and DSPLICINF commands on our system do not allow the OUTFILE parameter. Can you provide the information related to the API? We are currently at V7R5M0.
That SQL statement does not list the licensed users. It just gives the user counts. I'm looking for a way to query a list of the licensed user profiles.
Short answer: Yes — the usual approach is to have the display command write its output to an outfile and then query that outfile with SQL. On IBM i you can run the Display/Work with License command with OUTPUT(*OUTFILE) (or run DSPLICINF which supports OUTFILE) to create a physical file you can query. Then use SQL (or a CL program that runs SQL) to automate.
Below are concrete steps and examples.
Create the outfile from the license display Try one of these ( Read more...your release/commands may vary; check which one you have): DSPLICINF OUTPUT(*OUTFILE) OUTFILE(MYLIB/LICOUT) WRKLICINF OUTPUT(*OUTFILE) OUTFILE(MYLIB/LICOUT) Example interactive CL command: DSPLICINF OUTPUT(*OUTFILE) OUTFILE(MYLIB/LICOUT) If the command accepts an OUTFILE parameter it will create the physical file MYLIB/LICOUT containing the report rows. If your system uses a different display command name (or parameter names differ by OS TRL), run the command with ? or check the help (F4) to see the OUTPUT/OUTFILE parameters. Inspect the outfile structure (column names/types) Use SQL against QSYS2.SYSCOLUMNS or DSPFFD: sql
SELECT COLUMN_NAME, DATA_TYPE, LENGTH FROM QSYS2.SYSCOLUMNS WHERE TABLE_SCHEMA = 'MYLIB' AND TABLE_NAME = 'LICOUT' ORDER BY ORDINAL_POSITION; Or from 5250: DSPFFD FILE(MYLIB/LICOUT) Query the outfile with SQL Basic SELECT:
sql
SELECT * FROM MYLIB.LICOUT; Example: count licensed users per product (replace column names with actual column names from your LICOUT):
sql
SELECT PRODUCT_NAME, COUNT(USER_ID) AS NUM_LICENSED_USERS FROM MYLIB.LICOUT GROUP BY PRODUCT_NAME ORDER BY PRODUCT_NAME; Example: list each user with product and license type (adjust column names):
sql
SELECT USER_ID, PRODUCT_NAME, LICENSE_TYPE, LICENSE_SERIAL FROM MYLIB.LICOUT ORDER BY USER_ID; Automate the process Put the DSPLICINF (or WRKLICINF) OUTFILE command into a CL program (or call it from a script) that overwrites a fixed file (e.g., MYLIB/LICOUT), then run SQL (RUNSQLSTM, STRSQL, or embedded SQL in a program) to extract or transform data and write to a reporting table or CSV. Example simple CL program outline: PGM /* produce the outfile */ DSPLICINF OUTPUT(*OUTFILE) OUTFILE(MYLIB/LICOUT) /* optionally call an SQL stored procedure or QSH script to export/query */ ENDPGM Use the IBM i job scheduler to run that CL program on a schedule (or submit it via SBMJOB to run ad hoc). Notes and permissions You need authority to run DSPLICINF/WRKLICINF and to create files in the target library. Column names and available fields in the OUTFILE vary by OS release. Inspect the outfile to confirm exact names. If your release does not support OUTFILE for that command, there are APIs that return license information (but the API names differ by release) — in that case I can help map the API to an SQL accessible file or write a small program to call the API and dump to a physical file. Show less...
