SQL connection failure error handling

25 pts.
Tags:
Batch jobs
END SEVERITY
Joblogs
SQL connectivity
SQL Server
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?

 

                                                         

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • Midranger
    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 !
    25 pointsBadges:
    report
  • Denny Cherry
    What database software are you using, and what version?
    66,185 pointsBadges:
    report
  • TomLiotta
    ...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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

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

REGISTER or login:

Forgot Password?
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

Thanks! We'll email you when relevant content is added and updated.

Following