Information on QSQCMPGM

Content

Programs have a 16 Meg size limit for the Program Associated Space (PAS).
This space is used to store SQL information and access plans of the SQL statements in the program.
Below are some common questions and answers to those questions.

Q1 What actually happens in recovering the space as it relates to ‘old Access Plans’?
A1 QSQCMPGM removes fragmented storage and abandoned access plans, potentially giving you the internal space needed to continue using the program.

Besides surfacing in the DB2 Health Center, the Program_Used_sized column in the QSYS2.SYSPROGRAMSTAT catalog also returns this information.

Q2. What, if any, drawbacks are there in using QSQCMPGM?
A2 You need to get an exclusive lock on the program. The processing is very fast, so usually this has little to no impact.

Q3. What are the potential issues we need to consider if a program grows in size relatively quickly?
A3. You would need to study "why" its happening to answer this question.
Frequent rebuilds of access plans is something to avoid, as its slower than reusing plans.

Q4. Is the size of an object a factor in its execution?  As in, will it load appreciably faster if the SQL space is significantly smaller?
A4. Every static SQL statement in a program or service program has information related to the statement stored in the SQL PAS.

Q5. When should the compressing of the SQL space be considered in terms of size (e.g. > 10MB)?
A5. This will be different for each user and possibly each program.
There is no IBM recommendation on when to run the QSQCMPGM.
A program could be created to automate the process of detecting the space size and use of QSQCMPGM if the size is over a threshold set by the program.

Q6. Would it be worthwhile to run QSQCMPGM against all of our programs to reduce the space used?    
A6. IBM intended QSQCMPGM to be used against the subset of programs that are approaching the limit, not against many or every program.

Q7. Does each module within a *PGM or *SRVPGM have it's own PAS?    If so, are they all compressed with a QSQCMPGM?
A7.  Yes and yes

Q8: How do I call this program?
A8: CALL PGM(QSYS/QSQCMPGM) PARM('-YOURLIB-' '-YOUROBJECT-' '*SRVPGM')
   First parameter is library, second parameter is the object to compress, third parameter is object type - *PGM, *SRVPGM
   You do need to use upper case

Q9. I'm getting a locking issue when I try to do a QSQCMPGM.   But WRKOBJLCK doesn't show any locks.
A9. The locks that are preventing the QSQCMPGM do not necessarily appear via WRKOBJLCK or WRKJOB option 12.
However, we will attempt to document the jobs that are preventing the QSQCMPGM via the "detailed messages" (Toggle F10 from QCMD)  
Note that these "detailed messages" will also document the before and after sizes