Topic
  • 3 replies
  • Latest Post - ‏2013-06-05T15:28:03Z by David.Zhu
LukeNumrych
LukeNumrych
73 Posts

Pinned topic DSWC handling of commented out lines in a script...

‏2013-05-23T17:34:47Z |

I just ran into this situation...  

Here is a snippet of my User-Defined alert:
 

create or replace variable ts_diff decimal(20,6);
--select current schema as schema, current user as user from sysibm.sysdummy1;
--select substr(varschema,1,20) as varschema, substr(varname,1,20) from syscat.variables where lower(varname) = 'ts_diff';
set (ts_diff) = (
        select 
--              current_timestamp
--              , coalesce(max(end_time),timestamp('1900-01-01-00.00.00'))
                timestampdiff(8,char(current_timestamp - coalesce(max(end_time),timestamp('1900-01-01-00.00.00')))) 
 

When I tried executing it, I got the following error:

Failed Statement: --select current schema as schema, current user as user from sysibm.sysdummy1
 Error message: [jcc][t4][10234][10927][3.63.138] SQL passed with no tokens. ERRORCODE=-4462, SQLSTATE=null
 Error code: -4462
 SQL state: null

 

I fixed it by removing the terminator from the commented out lines, so the alert is working fine right now; however, it would be nice if someone could explain to me why is DSWC complaining about the commented out lines...

  • AndreS
    AndreS
    2 Posts

    Re: DSWC handling of commented out lines in a script...

    ‏2013-05-27T03:55:35Z  

    I have written a number of user defined alerts in DSWC 3+ but I have never attempted a multi-command SQL script as an alert. My scripts have always been a single SELECT that returns the exit code and a description and these can be fairly complex and DWSC will have no issue running it. I do not have my DWSC open in front of me but I am almost 100% sure I have used comments without the ";" in my alerts.

    All I can ask, is to check if you can run your code from the SQL Editor in the DS Admin Client. If it runs there then it should run in DWSC. Other than that it may be a DWSC "feature" that it cannot correctly parse comment lines that contains the statement delimiter.

    My 2c.

  • LukeNumrych
    LukeNumrych
    73 Posts

    Re: DSWC handling of commented out lines in a script...

    ‏2013-05-28T14:03:46Z  
    • AndreS
    • ‏2013-05-27T03:55:35Z

    I have written a number of user defined alerts in DSWC 3+ but I have never attempted a multi-command SQL script as an alert. My scripts have always been a single SELECT that returns the exit code and a description and these can be fairly complex and DWSC will have no issue running it. I do not have my DWSC open in front of me but I am almost 100% sure I have used comments without the ";" in my alerts.

    All I can ask, is to check if you can run your code from the SQL Editor in the DS Admin Client. If it runs there then it should run in DWSC. Other than that it may be a DWSC "feature" that it cannot correctly parse comment lines that contains the statement delimiter.

    My 2c.

    The script runs just fine in DS.  It also runs fine in DSWC, as soon as I remove the terminator from commented out lines...  I do think this is a "feature". Someone should alert the developers to its existence ;).

  • David.Zhu
    David.Zhu
    67 Posts

    Re: DSWC handling of commented out lines in a script...

    ‏2013-06-05T15:28:03Z  

    The script runs just fine in DS.  It also runs fine in DSWC, as soon as I remove the terminator from commented out lines...  I do think this is a "feature". Someone should alert the developers to its existence ;).

    Hi,AndreS,

    I agree with Luke that this shouldn't be "feature", could you check the location where split the whole script to statement according to the terminator whether it send the splited statement to DB2 but don't check whether this statement is just comments?

    According to Luke's descriptions. If the statement include valid SQL plus comments, DB2 can execute it correctly. but if you just send the comments to the DB2, then it will report the error message as Luke paste above.

    Thanks.