I have added the complete job log listing from where the problem starts. As recommended by Chuck:
All the "to" vars (procedure, module etc.) apparently point to stuff in the OS itself.
The system is on V6R1M0. As for PTF:s (since I do not manage PTF:s) the question is which product to look for.
A full version of the log messages follow. Interestingly there is no telling what reason code "6" is - despite the logging level is *seclvl.
The sequence below then repeats over and over again for over 90 thousand pages.
Anyhow this is a full version of where the messages start:
---------------------
CPF8361 Escape 40 11-12-30 04:11:26,524933 QTNCMTSP QSYS *STMT QRWSARDB QSYS
From module . . . . . . . . : QTNCRDSP
From procedure . . . . . . : SNDMSG
Statement . . . . . . . . . : 17178
To module . . . . . . . . . : QRWSARDB
To procedure . . . . . . . : REGLCLRSC
Statement . . . . . . . . . : 9895
Message . . . . : Cannot place resource under commitment control. Reason
code 6.
Cause . . . . . : An attempt was made to place a resource under commitment
control for commitment definition *DFTACTGRP that was not valid. Reason code
is 6 -- One of the following limits was exceeded: system storage, user
profile storage limit for user profile QDBSHR, no more than 2,097,143 unique
commitment definition/journal combinations on the system, or the maximum
resources for the commitment definition exceeded. Recovery . . . :
Depending on which limit was exceeded, do one of the following: (1) Free
some storage STG(*FREE) parameter on the SAVOBJ command. (2) Assign storage
to user profile QDBSHR using the MAXSTG parameter on the CHGUSRPRF command.
(3) Have fewer than 2,097,143 unique commitment definitions/journal
combinations on the system. (4) Remove some resources from commitment
control. Technical description . . . . . . . . : The commitment
5761SS1 V6R1M0 080215 Job Log ED9EM05A 11-12-30 04:11:32 Pag
Job name . . . . . . . . . . : M01_NDC User . . . . . . : REXNDC Number . . . . . . . . . . . : 85545
Job description . . . . . . : REXBATCH Library . . . . . : REXNDCSHR Section number . . . . . . . : 0007
MSGID TYPE SEV DATE TIME FROM PGM LIBRARY INST TO PGM LIBRARY
definition identifier is X'5CC4C6E3C1C3E3C7D9D7'. The activation group
number is X'00000002'. The lock space id is UDB_01000000001A5E1D. The lock
space associated space id is 1. The XID is
X'00000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000'.
SQL0752 Diagnostic 30 11-12-30 04:11:26,525147 QSQCONN QSYS *STMT QSQCONN QSYS
From module . . . . . . . . : QSQCONN
From procedure . . . . . . : CLEANUP
Statement . . . . . . . . . : 21002
To module . . . . . . . . . : QSQCONN
To procedure . . . . . . . : CLEANUP
Statement . . . . . . . . . : 21002
Message . . . . : Connection cannot be changed. Reason code is 0.
Cause . . . . . : Connection cannot be made because the application process
is not in a connectable state. The reason code is 0. Reason codes and their
meanings are: 1 -- SQL is not in a connectable state. SQL enters the
connectable state after a COMMIT or ROLLBACK. SQL leaves the connectable
state when any SQL statement except a COMMIT, ROLLBACK, or CONNECT statement
is run. 2 -- There are pending changes or open files under commitment
control at the current server and the relational database (RDB) specified on
the CONNECT request is not the current server. 3 -- A create SQL package
request is being processed when not on a commit boundary. 4 -- The
connection is locked by another invocation of Interactive SQL, or there is a
level mismatch between the Interactive SQL product and DB2 for i5/OS. 5 --
The connection cannot be changed due to restrictions with remote connections
and the job level commitment definition. 6 -- The connection cannot be
changed to a remote system due to a SET TRANSACTION statement. 7 -- The
connection cannot be changed using *RUW connection management because a
previous connection is protected. 8 -- CONNECT RESET cannot start a local
connection to RDB ED9EM05A because the RDB for the auxiliary storage pool
(ASP) group of the thread is ED9EM05A. 9 -- There is a mismatch between the
active connection and the current ASP group of the thread. Recovery . . . :
Do one of the following based on the reason code: 1 -- Issue a COMMIT or
ROLLBACK statement to enter the connectable state. 2 -- Close all files open
under commitment control and issue a COMMIT or ROLLBACK statement. 3 --
Issue a COMMIT or ROLLBACK statement. 4 -- Exit Interactive SQL and try the
request again. If Interactive SQL is active, the current server can only be
changed using Interactive SQL. 5 -- All activation groups associated with
the job level commitment definition must be local; or only one remote
connection and no local connections can be associated with the job level
commitment definition. 6 -- Issue a COMMIT or ROLLBACK statement. 7 --
Release the protected conversation followed by a COMMIT. 8 -- Change the ASP
group of the thread to the ASP group for relational database ED9EM05A. 9 --
Either change the ASP group of the thread to the ASP group for relational
database ED9EM05A or use a CONNECT or SET CONNECTION statement to change the
active connection.
SQL0900 Diagnostic 30 11-12-30 04:11:26,525180 QSQROUTS QSYS *STMT QSQROUTS QSYS
From module . . . . . . . . : QSQCLNUP
From procedure . . . . . . : SQROUTE_CLEANUP
Statement . . . . . . . . . : 3945
5761SS1 V6R1M0 080215 Job Log ED9EM05A 11-12-30 04:11:32
Job name . . . . . . . . . . : M01_NDC User . . . . . . : REXNDC Number . . . . . . . . . . . : 85
Job description . . . . . . : REXBATCH Library . . . . . : REXNDCSHR Section number . . . . . . . : 00
MSGID TYPE SEV DATE TIME FROM PGM LIBRARY INST TO PGM LIBRARY
To module . . . . . . . . . : QSQCLNUP
To procedure . . . . . . . : SQROUTE_CLEANUP
Statement . . . . . . . . . : 3945
Message . . . . : Application process not in a connected state.
Cause . . . . . : One of the following occurred: -- The current connection
was disconnected using the DISCONNECT statement. -- The current connection
was released and a commit occurred. -- A previous error has left the
application process in an unconnected state. Use the Display Job Log
(DSPJOBLOG) command to see previous errors. Recovery . . . : Issue
CONNECT statement with the TO or RESET clause or the SET CONNECTION
statement to enter the connected state.
SQL0900 Diagnostic 30 11-12-30 04:11:26,525203 QSQROUTS QSYS *STMT QSQROUTS QSYS
From module . . . . . . . . : QSQCLNUP
From procedure . . . . . . : SQROUTE_CLEANUP
Statement . . . . . . . . . : 3945
To module . . . . . . . . . : QSQCLNUP
To procedure . . . . . . . : SQROUTE_CLEANUP
Statement . . . . . . . . . : 3945
Message . . . . : Application process not in a connected state.
Cause . . . . . : One of the following occurred: -- The current connection
was disconnected using the DISCONNECT statement. -- The current connection
was released and a commit occurred. -- A previous error has left the
application process in an unconnected state. Use the Display Job Log
(DSPJOBLOG) command to see previous errors. Recovery . . . : Issue
CONNECT statement with the TO or RESET clause or the SET CONNECTION
statement to enter the connected state.
---------------------------------------------------
Med vänlig hälsning / Best regards
Åke H Olsson
Box 433 SE 551 16 Jönköping Sweden visit: Brunnsgatan 11
phone: +46 (0)36 342976 mobile: +46 (0)705 482976 fax: +46 (0)36 34 29 29
ake.olsson@xxxxxx www.pdb.se
message: 3
date: Thu, 29 Dec 2011 11:04:27 -0800
from: CRPence <CRPbottle@xxxxxxxxx>
subject: Re: SQL-Commitment control issue generating enormout amount
of jog logs
On 29-Dec-2011 01:37 , ?ke Olsson wrote:
We have an overnight job where we from time to time see an enormous
number of job logs. Last night over 91 thousand pages.
Most of this is repeats of the same information and it all happens
in the flow between calls to two programs:
The starting point for the issues is a call to non-sql program.
We get a CPF8361 "Cannot place resource under commitment control.
Reason code 6" Followed by SQL0752 "Connection cannot be changed"
SQL0900 "Application process not in a connected state."
And this continues for another 90785 pages of joblog - same three
messages in that sequence.
The time used by this is just under 3 minutes.
I have checked all SQL programs in the flow to see if there could be
any SQL statements with problems and found none. We do not user
journaling or commitment control at all.
Any ideas on where to look?
The best place to start would be with the messages, but viewed for
the full details available from a LOG(4 0 *SECLVL) spooled joblog. That
will show all of the replacement text for each message as well as
exactly which program(s) receive the errors [i.e. the "to" program] such
as the program that apparently is wanting to place some resource under
CmtCtl for which that request is being denied, and the program(s) that
identified both the resource as being ineligible for the request [i.e.
the "from" program] and the connection status difficulties.
Which release of the LIC and OS [plus optionally PTF cumulative and
database group levels] is installed where the problem is being
encountered might be relevant to assist in excluding unrelated defect
searches. The first listed error has a partial [per lack of the full
second level spooled joblog message details] symptom string which could
be used in a defect search [though alone, produces results spanning
several releases] as:
msgCPF8361 RC6
Regards, Chuck
midrange.com
