Topic
  • 2 replies
  • Latest Post - ‏2014-05-14T00:46:39Z by HLHuang
EdsonYai
EdsonYai
1 Post

Pinned topic Web Console 4.1- apparently wrong return code from SQL Script for user-defined alerts

‏2014-04-16T20:57:33Z |

While creating a new alert type in Web Console generated from a SQL only script, the result is always "Execution Status code: 2" even though the script returns only other very different values: 0, -1 and -2.

The script was adapted from the Information Center (http://www-01.ibm.com/support/knowledgecenter/SS62YD_4.1.0/com.ibm.datatools.db.web.health.install.doc/topics/customalerts_sample_scripts.html)  and follows below:

SELECT CASE level
  WHEN 'W' THEN -1
  WHEN 'E' THEN -2
  WHEN 'C' THEN -2
  WHEN 'S' THEN -2
  ELSE 0
END AS RETURNVALUE
FROM TABLE (PD_GET_DIAG_HIST('MAIN','ALL', '', CURRENT TIMESTAMP - 1 DAYS, CURRENT TIMESTAMP)) AS T;
 

I verified that the script was created in the Alert Types page as a "SQL only script".

Is really the script execution engine returning the correct return code? If so, why it does not return the codes it should as the Information Center says, and what can be done so the script returns only the desired values?

Thank you in advance.

  • LukeNumrych
    LukeNumrych
    57 Posts

    Re: Web Console 4.1- apparently wrong return code from SQL Script for user-defined alerts

    ‏2014-04-22T14:21:52Z  

    <ObiWan>This is not the return value you are looking for</ObiWan>

    If you look at your user-defined alert execution log, you will see something like this (or close to it, the below is from DSWC 3.2)

    ID: 1392409551527
    Name: MyAlert
    Commands Executed:
    
    
    
    SELECT CASE level 
    WHEN 'W' THEN -1 
    WHEN 'E' THEN -2 
    WHEN 'C' THEN -2 
    WHEN 'S' THEN -2 
    ELSE 0
    END AS RETURNVALUE
    FROM TABLE (PD_GET_DIAG_HIST('MAIN','ALL', '', CURRENT TIMESTAMP - 1 DAYS, CURRENT TIMESTAMP)) AS T;
    
    Start Time: 2014-04-21 14:30:00
    End Time: 2014-04-21 14:30:00
    Output from executing:
    
    If your alert were returning a varchar column 
    
    alert_details 
    this is where the contents would appear
    
    Result: The job executed successfully. Execution Status code: 2

     

    If you look closely at the text in the Result section, you'll see it states "the job executed successfully."  This means that your alert, which is just a scheduled job that returns specially formatted results, has executed successfully - i.e. there were no connectivity, syntax, or other errors executing your job.  That does not mean that your user-defined alert did not return a returnvalue equal to an alert or warning condition.  It just means that DSWC was able to successfully execute your user-defined alert.

    If the execution of your user-defined alert itself causes an error, the Execution Status code will be different.  Try substituting your correctly executing SQL with something that has no chance of being executed without an error, like for example:

    values (1/0);

    ID: 1398175731223
    Name: test
    Commands Executed:
    values (1/0);
    
    Start Time: 2014-04-22 09:09:16
    End Time: 2014-04-22 09:09:17
    Error message:
    Commands executed: values (1/0); Error message: null Error code: -801 SQL state: 22012
    
    Errors from executing:
    Failed Statement: values (1/0) Error message: null Error code: -801 SQL state: 22012
    
    Result: The job execution failed. Execution Status code: 3

    Do not ask me what the different Execution Status codes are, however.  I have not been able to find that information in the documentation yet.  I think they are:

    1. Error connecting to the database.
    2. Execution successful.
    3. Error executing the user-defined alert job.

    But that is just conjecture, so do not quote me on it.

    Updated on 2014-04-22T14:22:28Z at 2014-04-22T14:22:28Z by LukeNumrych
  • HLHuang
    HLHuang
    32 Posts

    Re: Web Console 4.1- apparently wrong return code from SQL Script for user-defined alerts

    ‏2014-05-14T00:46:39Z  

    <ObiWan>This is not the return value you are looking for</ObiWan>

    If you look at your user-defined alert execution log, you will see something like this (or close to it, the below is from DSWC 3.2)

    ID: 1392409551527
    Name: MyAlert
    Commands Executed:
    
    
    
    SELECT CASE level 
    WHEN 'W' THEN -1 
    WHEN 'E' THEN -2 
    WHEN 'C' THEN -2 
    WHEN 'S' THEN -2 
    ELSE 0
    END AS RETURNVALUE
    FROM TABLE (PD_GET_DIAG_HIST('MAIN','ALL', '', CURRENT TIMESTAMP - 1 DAYS, CURRENT TIMESTAMP)) AS T;
    
    Start Time: 2014-04-21 14:30:00
    End Time: 2014-04-21 14:30:00
    Output from executing:
    
    If your alert were returning a varchar column 
    
    alert_details 
    this is where the contents would appear
    
    Result: The job executed successfully. Execution Status code: 2

     

    If you look closely at the text in the Result section, you'll see it states "the job executed successfully."  This means that your alert, which is just a scheduled job that returns specially formatted results, has executed successfully - i.e. there were no connectivity, syntax, or other errors executing your job.  That does not mean that your user-defined alert did not return a returnvalue equal to an alert or warning condition.  It just means that DSWC was able to successfully execute your user-defined alert.

    If the execution of your user-defined alert itself causes an error, the Execution Status code will be different.  Try substituting your correctly executing SQL with something that has no chance of being executed without an error, like for example:

    values (1/0);

    ID: 1398175731223
    Name: test
    Commands Executed:
    values (1/0);
    
    Start Time: 2014-04-22 09:09:16
    End Time: 2014-04-22 09:09:17
    Error message:
    Commands executed: values (1/0); Error message: null Error code: -801 SQL state: 22012
    
    Errors from executing:
    Failed Statement: values (1/0) Error message: null Error code: -801 SQL state: 22012
    
    Result: The job execution failed. Execution Status code: 3

    Do not ask me what the different Execution Status codes are, however.  I have not been able to find that information in the documentation yet.  I think they are:

    1. Error connecting to the database.
    2. Execution successful.
    3. Error executing the user-defined alert job.

    But that is just conjecture, so do not quote me on it.

    The return value from the Select statement determine what kind of alert that this user defined alert end up with.

    If it's -1, then a warning alert will be generated, if it's -2,  then critical alert will be generated, 0 means no alert.

    That's why this is called user defined alert. It's not the return value that determines the job status whether

    failed or successful.  You could also set this alert type to have alert notification (say if critical alert happens

    then send an email to you).  Some user wrote an executable script job of the user defined alert type  to have it

    run daily at certain to check if the disk space is lower than xx GByes, then it will generate an critical alert.

    And they associate this critical alert to the alert notification to send an email to them so that they

    could clean up the hard drive.  This is an example of how user defined alert is used.  :)