The summary information contains all the columns I am interested in, therefore, most of the time I will be calling this function with the third parameter being '*NO'. The exceptions are the information for the output queue and library, but you already know those to be able to call this function. The same columns are available in the detailed information of the Table function as are in the View. Detailed ( *YES) or summary ( *NO) information.This table function has three parameters: That is also its advantage, if you only needed the information from one output queue it is faster to use this than it is to use the View. The Table function is more restrictive than the View, as you can only run it for one output queue. Using the columns SPOOLNAME, JOB_NAME, and FILENUM I can use the Delete Spool File command, DLTSPLF, to delete the spool files. Now I can review the contents of my table/file and decided which spool files can be deleted. The SELECT is similar to the one above, the only difference is that I only want the top 200 worst offenders. Line 4: I have used the CREATE TABLE to create an output table/file that contains the results of my SQL statement. USER_NAME,STATUS,PAGES,JOB_NAME,FILENUM + (SELECT SIZE,SPOOLNAME,OUTQ,OUTQLIB,CREATED,+ To do that I could create a simple CL program like this:Ġ4 RUNSQL SQL('CREATE TABLE MYLIB/SPLFILES AS + It would be better for the results to be in a file that I can then work through the list to eliminate the offenders. The output I would get would look like this:ĪLLANSK READY 425 310442/ALLANSK/CREATE_INV 1 SELECT SIZE,SPOOLNAME,OUTQ,OUTQLIB,CREATED,USER_NAME,STATUS,įROM QSYS2/OUTPUT_QUEUE_ENTRIES ORDER BY SIZE DESC To find the worst offenders I would want to sort the results of my SQL statement by size, in a descending order: Qualified job name that produced the spool file When the spool file was created (timestamp) The columns I care about when trying to establish the worst spool file offenders are: Column As there are so many I am not going to list them all here, but I will refer you to IBM's page in the Knowledge Center here. There are columns for things I don't think I would want to inquire about. When I looked through the available columns in this View I was tempted to call this post " Everything you wanted to know about Spool file, but never dared to ask". What is the difference between a View and a Table function? A Table function requires parameters passed to it, a View does not. If not, *JOBCTL authority and the output queue has OPRCTL(*YES)īoth the View and the Table function reside in the library QSYS2 and are called:.If not, read authority to the output queue.To be able get information back from the View and the Table function you will need the following authority: With the new View and Table function I can get my hands on this information in a couple of the minutes at the most. I always wanted a quick and easy way to identify the following information about spool files:
The task of managing spool files is a thankless task, but without it I am always surprised how much disk space is lost to spool files after a few months. Retention of spool files has always been a contentious issue in all the IBM i shops I have worked. When I heard what was coming in IBM i 7.2 TR3 and 7.1 TR11 I was excited to learn that two of the new introductions would be a SQL View and SQL Table Function to list spool files in output queues.
0 kommentar(er)
