Application group wants batch job to terminate if it is unable to establish SQL session to external database.

Job log indicates MSGID QSQLMSG/SQ30080

"Communication error occurred during distributed database processing.

This appears in the joblog as a level 30 diagnostic message.

When this message is encountered we want the job to exceed end severity and terminate.

I have tried raising the severity code on the SQL message identifier and while it exceeds end severity for the job description attached to the active job the job does not end. i suspect this is happening because this the SQL message is received as a diagnostic not escape message.

How can I get the job to abend when this (or ther SQL specific message identifiers) are received in the active joblog?

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your response...

Discuss This Question: 4 &nbspReplies

There was an error processing your information. Please try again later.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

What is the structure of the job? That is, how is it opening the connection? Is this an RPG (COBOL? C?) program doing a CONNECT? Or are there other SQL or database elements involved?
Is this the only error message? If the message is monitored or handled by the programming, then it won't matter what severity you assign. The message will be 'handled' so that the "end job" severity won't be signaled.
In that case, you'll need to see what the programming is doing at the point where the error occurs. You'll also need to be sure if other messages are logged just before this one. A previous message should describe the reason that this message was sent. The reason should help you locate and understand what needs to be changed or fixed.
Tom

The job is managed by Robot Scheduler, a controlling CLP calls and RPGLE program which initiates the SQL session. In our case it is to an Oracle database via the Oracle transparent gateway running on the iSeries.
I've stripped all MONMSG statements out for test purposes and changed severity on the message identifier in QSQLMSG message file. Job log shows the modified message identifier severity level yet the job does not abend.
Is it only escape message identifiers (excluding diag messages) that are considered for a job to exceed end severity?
If this isn't enough info I will post the code and the joblog for review.
Thanks !

...a controlling CLP calls and RPGLE program which initiates the SQL session...
The error is quite possibly being handled in the RPGLE program.
I’ve stripped all MONMSG statements out for test purposes...
...therefore the CL and its MONMSGs might never see that there was a problem.
...and changed severity on the message identifier in QSQLMSG message file. Job log shows the modified message identifier severity level yet the job does not abend.
Now for the "bad" news -- a message severity level has practically nothing to do with job termination. The severity level of a message is for your programming to make decisions about. The system work management doesn't care about it.
Is it only escape message identifiers (excluding diag messages) that are considered for a job to exceed end severity?
For this discussion, the answer should probably be "Yes."
That is, the message type is what's important here. An *ESCAPE message that isn't 'handled' will eventually percolate up through every program in the current stack. Once it reaches the default handlers for the job, the job will terminate. The 'severity' assigned to the message description won't matter. An *ESCAPE message signals an exception that must be handled.
A *DIAG (Diagnostic) message can have any severity. As long as no exception is signalled, a *DIAG won't make any difference.
A SQL error is being signalled when the RPGLE program executes some SQL statement. The error might be ignored or it might cause the RPGLE to come to an end. Of course, if it's ignored, there will likely be subsequent errors that might also be ignored. Whatever is happening, the RPGLE program is reaching a logic end point without sending any kind of "abnormal termination" signal out to the calling CL. The CL then continues as if nothing is wrong because it has no other knowledge.
The RPGLE will (should) have SQL WHENEVER statements or it will have IF-statements to test the SQLSTATE and/or SQLCODE values that are returned from SQL when SQL statements are executed. The RPGLE should interpret any code to choose whether to continue and how to continue. Errors arising out of a CONNECT might normally be re-sent out of any RPGLE programming as *ESCAPE messages to signal the calling program that something went very wrong.
Tom

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

Share this item with your network:

To follow this tag...

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy