Topic
  • 9 replies
  • Latest Post - ‏2012-08-10T14:42:53Z by datta
stoned99
stoned99
26 Posts

Pinned topic Stored procedure doesn't return any visible ouput

‏2012-03-21T16:29:11Z |
In our move from Control Center to Data Studio we we're looking for a way to display threads, utilities, etc. A quick google search turned up this stored procedure,

call sysproc.admin_command_db2('-dis thread(*)',15,'THD',null,?,?,?,?,?,?,?,?);

however when I run this in Data Studio I don't see any output (it works in control center and Toad). A while back I had a similar problem where I didn't see the output parameters of executed stored procedures, which I sidestepped by running my SP's using Visual Studio.

Based upon this failure I think Data Studio is just not handling Stored Procedure CALL executions correctly.

Is there a fix, configuration change for this. I'm running 3.1.

Thanks

Mark.
Updated on 2012-08-10T14:42:53Z at 2012-08-10T14:42:53Z by datta
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Stored procedure doesn't return any visible ouput

    ‏2012-03-21T18:35:05Z  
    Did you run this statement in a SQL script editor launched from Data Source Explorer? Was you prompted the specify value dialog? What values did you enter?
  • stoned99
    stoned99
    26 Posts

    Re: Stored procedure doesn't return any visible ouput

    ‏2012-03-21T18:45:56Z  
    Did you run this statement in a SQL script editor launched from Data Source Explorer? Was you prompted the specify value dialog? What values did you enter?
    Here's a screen shot of the test.
  • stoned99
    stoned99
    26 Posts

    Re: Stored procedure doesn't return any visible ouput

    ‏2012-03-21T18:53:54Z  
    • stoned99
    • ‏2012-03-21T18:45:56Z
    Here's a screen shot of the test.
    Hmm, I just discovered something, in my Control Center testing I discovered an issue related to a invalid plan in our production environment (Message following).

    In my test environment, the same request results in information being returned. I had not tested against my test environment using Data Studio until now. What I discovered is in my test environment the command completes as expected.

    So the problem seems to be that the error returned in Control Center is NOT returned in Data Studio. In fact data Studio flags the CALL statement as executing successfully.
    connect to DB2PLOC user Mark using

    Database Connection Information

    Database server = DB2 z/OS 9.1.5
    SQL authorization ID = MARK
    Local database alias = DB2PLOC
    A JDBC connection to the target has succeeded.

    Commands Entered
    call sysproc.admin_command_db2('-dis thread(*)',15,'THD',null,?,?,?,?,?,?,?,?);

    call sysproc.admin_command_db2('-dis thread(*)',15,'THD',null,?,?,?,?,?,?,?,?)
    CMD_EXEC: 1
    IFCA_RET: 0
    IFCA_RES: 0
    XS_BYTES: 0
    IFCA_GRES: 0
    GXS_BYTES: 0
    RETURN_CODE: 12
    MSG: DSNA618I DSNADMCD SQL ERROR DURING SQL STATEMENT DELETE, TABLE=SYSIBM.DB2_CMD_OUTPUT…DSNT408I SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME DB2PLOC.DSNADM.DSNADMCD.0E5F9D9F01D4F140 NOT FOUND IN PLAN…DISTSERV. REASON 02…DSNT418I SQLSTATE = 51002 SQLSTATE RETURN CODE…DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING ERROR…DSNT416I SQLERRD = -251 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION…DSNT416I SQLERRD = X'FFFFFF05' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC…INFORMATION…

    "ADMIN_COMMAND_DB2" RETURN_STATUS: 0
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Stored procedure doesn't return any visible ouput

    ‏2012-03-21T22:12:55Z  
    • stoned99
    • ‏2012-03-21T18:53:54Z
    Hmm, I just discovered something, in my Control Center testing I discovered an issue related to a invalid plan in our production environment (Message following).

    In my test environment, the same request results in information being returned. I had not tested against my test environment using Data Studio until now. What I discovered is in my test environment the command completes as expected.

    So the problem seems to be that the error returned in Control Center is NOT returned in Data Studio. In fact data Studio flags the CALL statement as executing successfully.
    connect to DB2PLOC user Mark using

    Database Connection Information

    Database server = DB2 z/OS 9.1.5
    SQL authorization ID = MARK
    Local database alias = DB2PLOC
    A JDBC connection to the target has succeeded.

    Commands Entered
    call sysproc.admin_command_db2('-dis thread(*)',15,'THD',null,?,?,?,?,?,?,?,?);

    call sysproc.admin_command_db2('-dis thread(*)',15,'THD',null,?,?,?,?,?,?,?,?)
    CMD_EXEC: 1
    IFCA_RET: 0
    IFCA_RES: 0
    XS_BYTES: 0
    IFCA_GRES: 0
    GXS_BYTES: 0
    RETURN_CODE: 12
    MSG: DSNA618I DSNADMCD SQL ERROR DURING SQL STATEMENT DELETE, TABLE=SYSIBM.DB2_CMD_OUTPUT…DSNT408I SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME DB2PLOC.DSNADM.DSNADMCD.0E5F9D9F01D4F140 NOT FOUND IN PLAN…DISTSERV. REASON 02…DSNT418I SQLSTATE = 51002 SQLSTATE RETURN CODE…DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING ERROR…DSNT416I SQLERRD = -251 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION…DSNT416I SQLERRD = X'FFFFFF05' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC…INFORMATION…

    "ADMIN_COMMAND_DB2" RETURN_STATUS: 0
    If you run the call stored procedure statement within SQL Script editor, currently DS does not support showing result set. We have a feature request.

    At meanwhile, Data Studio provides end-to-end GUI support for stored procedures, including create, edit, deploy, run and debug. For your case, you can expand the Database node in Data Source Explorer to locate the stored procedure under Schema> (schema name) >Stored Procedures folder. Next you can run the stored procedure by right clicking it and selecting Run. It will give you result set in SQL Result View.
  • stoned99
    stoned99
    26 Posts

    Re: Stored procedure doesn't return any visible ouput

    ‏2012-03-21T22:50:14Z  
    If you run the call stored procedure statement within SQL Script editor, currently DS does not support showing result set. We have a feature request.

    At meanwhile, Data Studio provides end-to-end GUI support for stored procedures, including create, edit, deploy, run and debug. For your case, you can expand the Database node in Data Source Explorer to locate the stored procedure under Schema> (schema name) >Stored Procedures folder. Next you can run the stored procedure by right clicking it and selecting Run. It will give you result set in SQL Result View.
    Although what you suggest as a workaround is possible, it's horrendously slower to run SP's that way as it requires finding the sp, and then entering all the parameters. Cut / Paste execution, or saved SQL is so much faster.

    And let me be clear, this was available and still is available in Control Center, and Toad.

    When can we expect this to be included in DS ?

    Hopefully soon as Control Center does not work with DB2 10, and as you can see I was already trying to find a workaround to Control Centers nice, Display Thread, Utility, etc functionality by using Stored Procedures instead to run the DB2 command.

    Thanks

    Mark.
  • vikrantislav
    vikrantislav
    40 Posts

    Re: Stored procedure doesn't return any visible ouput

    ‏2012-03-23T15:20:20Z  
    If you run the call stored procedure statement within SQL Script editor, currently DS does not support showing result set. We have a feature request.

    At meanwhile, Data Studio provides end-to-end GUI support for stored procedures, including create, edit, deploy, run and debug. For your case, you can expand the Database node in Data Source Explorer to locate the stored procedure under Schema> (schema name) >Stored Procedures folder. Next you can run the stored procedure by right clicking it and selecting Run. It will give you result set in SQL Result View.
    I agree with the original poster, a feature for showing output values of stored proc parameters is much needed in Data Studio. Thanks!
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Stored procedure doesn't return any visible ouput

    ‏2012-03-23T16:41:39Z  
    • stoned99
    • ‏2012-03-21T22:50:14Z
    Although what you suggest as a workaround is possible, it's horrendously slower to run SP's that way as it requires finding the sp, and then entering all the parameters. Cut / Paste execution, or saved SQL is so much faster.

    And let me be clear, this was available and still is available in Control Center, and Toad.

    When can we expect this to be included in DS ?

    Hopefully soon as Control Center does not work with DB2 10, and as you can see I was already trying to find a workaround to Control Centers nice, Display Thread, Utility, etc functionality by using Stored Procedures instead to run the DB2 command.

    Thanks

    Mark.
    Thanks for your comments. As I mentioned in previous post, feature request has been filed. It has high priority, targeting next release.
  • hylianux
    hylianux
    1 Post

    Re: Stored procedure doesn't return any visible ouput

    ‏2012-08-10T14:34:33Z  
    I'm just now stumbling upon this post... It would seem there has been an update since (DS is now on 3.1.1). Has this feature been implemented yet? I ask because I still don't see it...
  • datta
    datta
    2 Posts

    Re: Stored procedure doesn't return any visible ouput

    ‏2012-08-10T14:42:53Z  
    • hylianux
    • ‏2012-08-10T14:34:33Z
    I'm just now stumbling upon this post... It would seem there has been an update since (DS is now on 3.1.1). Has this feature been implemented yet? I ask because I still don't see it...
    I don't think so. I am using Data Studio Version 3.1.1.0, administration client.
    I can see the data if the SP has a result set. But for parameters, it is still blank.