Hi,
One way to help find the place in your code that encounters the problem (I won't say 'causes' because with an SQL system error SQL0901 means that you are likely looking at SQL itself breaking the lock enforcement rules, and not your code) without code changes is to turn debug on for the jobs in question. Not to run in a debugger (use STRDBG with Program as *NONE against the jobs before they start to execute from the queue) but to get the full SQL 'verbose' mode into the joblog.
This will give you everything that SQL does in your job log - fetches, inserts, file opens, cursor declares and opens etc. are all recorded as SQL messages along with any other messages logged by the job. Using your knowledge of your code while reading the 'verbose' SQL in the joblog, you can often figure out at least what table is involved at the time of failure and what else is happening in your code at that moment in time. Forewarned - the joblogs become huge and you should set them to spool off rather than *WRAP or you could easily miss the required information.
I mention it as you may need to do something like this to help IBM identify the required steps to reproduce the issue if it is not already documented in an APAR.
You can also get some logging information if you install the PTF to deliver a system wide logger - available only on some recent releases.
http://www-912.ibm.com/a_dir/as4ptf.nsf/c2fd98f5d2eccb83862574ce00520341/f3efc44a3afe9ca3862575fb0068f502?OpenDocument
Take care,
Genyphyr Novak
System i Technical Consultant
+33 450 53 93 70
SQL0901: "SQL system error" and MCH2601: "Lock enforcement rules broken when trying to access object &1"
Tue Mar 23 21:29:37 2010
From:
"Dan" <dan27649@xxxxxxxxx>
To:
"" <RPG400-L@xxxxxxxxxxxx>, "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>(Cross-posted to Midrange and
RPG400 lists.)
We have a real mind-blower on our hands. We have
an application which
submits several jobs to the job queue, which
allows up to three jobs to run
simultaneously. The jobs run a
program (the same program using different
data) which uses SQL
*extensively*. SQL INSERTS are performed on tables
that exist in
libraries that are unique to each job. DECLARE CURSORs are
defined
with "FOR READ ONLY". No explicit locking (i.e., ALCOBJ) is used.
If the stars align correctly, we occasionally get an SQL0901, which is a
"SQL system error". The second-level text isn't much help except to suggest
that the job log should be looked at. (At the end of this post, I've pasted
the relevant part of the job log with "white space" removed to reduce line
wrapping.) I am usually able to replicate the error, and it seems to occur
when a particular segment of the program is being executed simultaneously by
two jobs. But nailing it down seems to be near impossible, given the lack
of information in the error and the job log. At this point, I am about to
add code to test for SQL0901 after every executable SQL statement, and
perform a dump with an identifying label. The MCH2601 message appears in
the job log just prior to the SQL0901, but I've not been able to find any
information on this message relevant to it being issued as a result of an
SQL
error.
I searched the IBM Series i Support site for "MCH2601
SQL0901" (without the
double quotes) and got one hit. Unfortunately, it requires PartnerWorld
access, and it may be a few days before I
can get the required company token
to be able to add myself to the
company profile. (If anyone reading this
has PartnerWorld access and can send me the content from the result link, I
would be greatly
appreciative.)
TIA,
Dan
MSGID TYPE SEV
DATE TIME FROM PGM LIBRARY
INST TO PGM
LIBRARY
MCH2601 Escape 40 03/23/10 11:40:11.412336 #cfochkr
000B10 QQQVAP QSYS
To module . . . . . . . . . : QQQVAP
To procedure . . . . . . . :
RESOLVE_SYSTEM_POINTERS
Statement . . . . . . . . . : 4204
Message . . . . : Lock enforcement
rules broken when trying to access
object
&1.
MCH2601 Escape 40 03/23/10 11:40:11.414120 #cfochkr
000B10
QQQVALID QSYS
To module . . . . . . . . . : QQQVALID
To procedure . . . . . . . :
RESOLVE_SYSTEM_POINTERS
Statement . . . . . . . . . : 6700
Message . . . . : Lock enforcement
rules broken when trying to access
object
&1.
CPF4204 Escape 50 03/23/10 11:40:11.414408 QQQQUERY QSYS
*STMT QQQQUERY QSYS
From module . . . . . . . . : QQQQUERY
From procedure . . . . . . : QQQQUERY
Statement . . . . . . . . . : 27564
To module . . . . . . . . . : QQQQUERY
To
procedure . . . . . . . : QQQQUERY
Statement . . . . . . . . . : 27564
Message . . . . : Internal failure occurred in query
processor.
Cause . . . . . : A system failure has occurred in the query
processor
program. The query definition template number is 0. Recovery . . .
: See
the low-level messages. Correct any errors and try your
request
again. If
the problem continues, report the problem (ANZPRB
command).
SQL0901 Diagnostic 50 03/23/10
11:40:11.414624 QSQRUN3 QSYS
*STMT QSQRUN3 QSYS
From module . . . . . . . . : QSQINS
From procedure . . . . . . : CLEANUP
Statement . . . . . . . . . : 24225
To
module . . . . . . . . . : QSQINS
To procedure . . . . . . .
: CLEANUP
Statement . . . . . . . . . : 24225
Message . . . . : SQL system error.
Cause . . . . . : An SQL system
error has occurred. The current
SQL
statement cannot be
completed successfully. The error will not
prevent
other
SQL statements from being processed. Previous messages may
indicate
that there is a problem with the SQL statement and SQL did not
correctly
diagnose the error. The previous message identifier was CPF4204.
Internal
error type 3107 has occurred. If precompiling, processing will not
continue
beyond this statement. Recovery . . . : See the previous
messages
to
determine if there is a problem with the SQL
statement. To view
the
messages, use the DSPJOBLOG command
if running interactively, or the
WRKJOB
command to view the
output of a precompile. An application
program
receiving
this return code may attempt further SQL statements.
Correct any
errors and try the request again.
midrange.com
