Topic
  • 15 replies
  • Latest Post - ‏2013-02-07T21:40:11Z by chunqiu
SystemAdmin
SystemAdmin
1632 Posts

Pinned topic Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

‏2013-02-05T22:23:13Z |
Hello,
I believe that I am performing something simple in DataStudio 3.2, but something is eluding me.

I am issuing:
CALL SYSPROC.ADMIN_COMMAND_DB2('-DISPLAY DATABASE(*) SPACENAM(*) RESTRICT LIMIT(*)',50,'TS',NULL,?,?,?,?,?,?,?,?)
but I receive
ERRORCODE=-4463, SQLSTATE=42601

When I call this SP, in a Data Perspective, I am prompted for values for the eight question marks. But, according to DB2 InfoCenter,
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.admin%2Fsrc%2Ftpc%2Fdb2z_sp_admincommanddb2.htm
these are output values. I have cataloged my database, which is a connection to DB2 z/OS V9.1.

I tested this command in DB2 CLP, and it ran successfully.

I have been searching for this specific scenario in Data Studio documentation, but I have come up with very little.

Is it possible to call ADMIN_COMMAND_DB2 in DataStudio?

Thanks in advance.
Bob
Updated on 2013-02-07T21:40:11Z at 2013-02-07T21:40:11Z by chunqiu
  • chunqiu
    chunqiu
    15 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T18:15:13Z  
    Bob,
    I tested your scenario on a z/os V9 database connection via DS32, it did not pop up the dialog page "Specify Host Variable Valus" and the command ran successfully.

    But when I tested the same scenario on a LUW 10.1 connection via DS32, it does pop up the dialog page to ask for providing host variable values. I am investigating this issue right now.

    Can you confirm the issue you saw is for LUW or z/os database connection?

    Thanks!

    Chunqiu
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T18:53:23Z  
    • chunqiu
    • ‏2013-02-06T18:15:13Z
    Bob,
    I tested your scenario on a z/os V9 database connection via DS32, it did not pop up the dialog page "Specify Host Variable Valus" and the command ran successfully.

    But when I tested the same scenario on a LUW 10.1 connection via DS32, it does pop up the dialog page to ask for providing host variable values. I am investigating this issue right now.

    Can you confirm the issue you saw is for LUW or z/os database connection?

    Thanks!

    Chunqiu
    Hi Chunqiu,
    thank you for responding and investigating.

    I have verified that my connection is to DB2 z/OS V9.1.

    I ran a query, that you'll see in my screenshot, and the results showed DB2 objects that I expect to see on my DB2 z/OS. I am attaching a screenshot of the DS32 admin window. When I run the stored procedure call, I am prompted with the dialog box to provide the input variables, but I could not attach a screenshot of that, since I am limited to 1 file that I may attach. I hope it helps.

    Bob
  • chunqiu
    chunqiu
    15 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T18:55:18Z  
    Bob,
    On top of my previous reply, the reason it did not work properly for LUW database connection is because LUW does not have procedure SYSPROC.ADMIN_COMMAND_DB2 (...) defined. DB2 for z/os has such procedure defined. Based on my test result, your test scenario can run successfully on z/os database connection with data studio 32. Can you double check to see if you still have problems when invoking the procedure on z/os database connection with DS32?

    Thanks!

    Chunqiu
  • chunqiu
    chunqiu
    15 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T19:05:43Z  
    Hi Chunqiu,
    thank you for responding and investigating.

    I have verified that my connection is to DB2 z/OS V9.1.

    I ran a query, that you'll see in my screenshot, and the results showed DB2 objects that I expect to see on my DB2 z/OS. I am attaching a screenshot of the DS32 admin window. When I run the stored procedure call, I am prompted with the dialog box to provide the input variables, but I could not attach a screenshot of that, since I am limited to 1 file that I may attach. I hope it helps.

    Bob
    Bob,
    Can you run below query to see if your database system has such procedure defined?

    select schema, name from sysibm.sysroutines
    where schema='SYSPROC' AND name='ADMIN_COMMAND_DB2';
    Here is my query result:

    Query execution time => 308 ms


    SCHEMA NAME

    -----------------
    SYSPROC ADMIN_COMMAND_DB2

    Thanks!

    Chunqiu
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T19:22:01Z  
    • chunqiu
    • ‏2013-02-06T19:05:43Z
    Bob,
    Can you run below query to see if your database system has such procedure defined?

    select schema, name from sysibm.sysroutines
    where schema='SYSPROC' AND name='ADMIN_COMMAND_DB2';
    Here is my query result:

    Query execution time => 308 ms


    SCHEMA NAME

    -----------------
    SYSPROC ADMIN_COMMAND_DB2

    Thanks!

    Chunqiu
    Hi Chunqiu,
    I browsed for this stored procedure in DS and generated the DDL for it:
    --<ScriptOptions statementTerminator=";"/>

    CREATE PROCEDURE "SYSPROC"."ADMIN_COMMAND_DB2" (DB2_CMD VARCHAR(32704) CCSID EBCDIC FOR SBCS DATA,
    LEN_CMD INTEGER,
    PARSE_TYPE VARCHAR(3) CCSID EBCDIC FOR SBCS DATA,
    DB2_MEMBER VARCHAR(8) CCSID EBCDIC FOR SBCS DATA,
    OUT CMD_EXEC INTEGER,
    OUT IFCA_RET INTEGER,
    OUT IFCA_RES INTEGER,
    OUT XS_BYTES INTEGER,
    OUT IFCA_GRES INTEGER,
    OUT GXS_BYTES INTEGER,
    OUT RETURN_CODE INTEGER,
    OUT MSG VARCHAR(1331) CCSID EBCDIC FOR SBCS DATA)
    DYNAMIC RESULT SETS 2
    EXTERNAL NAME "DSNADMCD"
    LANGUAGE C
    MODIFIES SQL DATA
    PARAMETER STYLE GENERAL CALL WITH NULLS
    FENCED
    WLM ENVIRONMENT DB2PSGUS
    ASUTIME NO LIMIT
    COLLID DSNADM
    RUN OPTIONS 'TRAP(OFF),STACK(,,ANY,)'
    STAY RESIDENT NO
    COMMIT ON RETURN NO
    PROGRAM TYPE MAIN
    SECURITY DB2;

    Furthermore, I created a new connection profile to DB2P, DB2 z/OS V9.1, then I attempted to call ADMIN_COMMAND_DB2, in both a DATA perspective and a DATABASE ADMINISTRATION perspcetive. In both cases, I received the input window for SP input variables.

    I am (almost :-) ) that I have not altered any defaults within DS, but could the problem be related to a default that is incorrectly set?

    Bob
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T19:29:44Z  
    Hi Chunqiu,
    I browsed for this stored procedure in DS and generated the DDL for it:
    --<ScriptOptions statementTerminator=";"/>

    CREATE PROCEDURE "SYSPROC"."ADMIN_COMMAND_DB2" (DB2_CMD VARCHAR(32704) CCSID EBCDIC FOR SBCS DATA,
    LEN_CMD INTEGER,
    PARSE_TYPE VARCHAR(3) CCSID EBCDIC FOR SBCS DATA,
    DB2_MEMBER VARCHAR(8) CCSID EBCDIC FOR SBCS DATA,
    OUT CMD_EXEC INTEGER,
    OUT IFCA_RET INTEGER,
    OUT IFCA_RES INTEGER,
    OUT XS_BYTES INTEGER,
    OUT IFCA_GRES INTEGER,
    OUT GXS_BYTES INTEGER,
    OUT RETURN_CODE INTEGER,
    OUT MSG VARCHAR(1331) CCSID EBCDIC FOR SBCS DATA)
    DYNAMIC RESULT SETS 2
    EXTERNAL NAME "DSNADMCD"
    LANGUAGE C
    MODIFIES SQL DATA
    PARAMETER STYLE GENERAL CALL WITH NULLS
    FENCED
    WLM ENVIRONMENT DB2PSGUS
    ASUTIME NO LIMIT
    COLLID DSNADM
    RUN OPTIONS 'TRAP(OFF),STACK(,,ANY,)'
    STAY RESIDENT NO
    COMMIT ON RETURN NO
    PROGRAM TYPE MAIN
    SECURITY DB2;

    Furthermore, I created a new connection profile to DB2P, DB2 z/OS V9.1, then I attempted to call ADMIN_COMMAND_DB2, in both a DATA perspective and a DATABASE ADMINISTRATION perspcetive. In both cases, I received the input window for SP input variables.

    I am (almost :-) ) that I have not altered any defaults within DS, but could the problem be related to a default that is incorrectly set?

    Bob
    I forgot to say that I did verify the execution of this stored procedure, using the command syntax exactly as show above, using DB2 CLP. I can provide a screenshot of that if you'd like.

    Thanks!
    Bob
  • chunqiu
    chunqiu
    15 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T20:34:53Z  
    I forgot to say that I did verify the execution of this stored procedure, using the command syntax exactly as show above, using DB2 CLP. I can provide a screenshot of that if you'd like.

    Thanks!
    Bob
    Bob,
    Though it is most likely the case you are using DS32 GA version, I still want to double check and confirm with you: are you using the DS32 GA version? The fix for such problem was put in in the later cycle of DS32 development phase. I am wondering maybe the DS32 you are using does not have the fix somehow ...

    Thanks!

    Chunqiu
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T21:08:07Z  
    • chunqiu
    • ‏2013-02-06T20:34:53Z
    Bob,
    Though it is most likely the case you are using DS32 GA version, I still want to double check and confirm with you: are you using the DS32 GA version? The fix for such problem was put in in the later cycle of DS32 development phase. I am wondering maybe the DS32 you are using does not have the fix somehow ...

    Thanks!

    Chunqiu
    Chunqiu,
    I have attached the 'About IBM Data Studio' dialog box.

    Bob
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T23:00:28Z  
    Chunqiu,
    I have attached the 'About IBM Data Studio' dialog box.

    Bob
    Hi Chunqiu,
    Update: If I right-click on the ADMIN_COMMAND_DB2 stored procedure (In the Data Source Explorer window), and then choose 'Run...', and fill in the appropriate input variables, I get successful execution.

    It seems that I cannot execute this SP with a CALL from an SQL window.

    Thanks.
    Bob
  • chunqiu
    chunqiu
    15 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-07T00:17:37Z  
    Hi Chunqiu,
    Update: If I right-click on the ADMIN_COMMAND_DB2 stored procedure (In the Data Source Explorer window), and then choose 'Run...', and fill in the appropriate input variables, I get successful execution.

    It seems that I cannot execute this SP with a CALL from an SQL window.

    Thanks.
    Bob
    Bob,
    Thanks for the update. I could not view the About DS jpg, it is corrupted.
  • chunqiu
    chunqiu
    15 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-07T00:35:44Z  
    • chunqiu
    • ‏2013-02-07T00:17:37Z
    Bob,
    Thanks for the update. I could not view the About DS jpg, it is corrupted.
    Bob,
    As I could not recreate the problem from my end, I cannot trace the code to find out what is the root cause for your problem. If possible, I can provide you with a diagnosing patch that will log the error exception at the code place in doubt. And you need to apply the patch, try the same use case and see if receiving error exception in the error log view. That will help me narrow down the root cause. Please let me know if you are able to apply the patch.

    Thanks!

    Chunqiu
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-07T16:10:57Z  
    • chunqiu
    • ‏2013-02-07T00:35:44Z
    Bob,
    As I could not recreate the problem from my end, I cannot trace the code to find out what is the root cause for your problem. If possible, I can provide you with a diagnosing patch that will log the error exception at the code place in doubt. And you need to apply the patch, try the same use case and see if receiving error exception in the error log view. That will help me narrow down the root cause. Please let me know if you are able to apply the patch.

    Thanks!

    Chunqiu
    Hi Chunqiu,
    my apologies for the corrupted file. This is the version of DS:
    IBM® Data Studio
    Version 3.2.0.0
    I don't know how to determine if this is not the GA edition, other thank checking the 'Help > About IBM Data Studio' dialog.

    I am more than willing to install a patch for DS32 to assist in resolving this.

    Thanks!
    Bob
  • chunqiu
    chunqiu
    15 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-07T18:14:29Z  
    Hi Chunqiu,
    my apologies for the corrupted file. This is the version of DS:
    IBM® Data Studio
    Version 3.2.0.0
    I don't know how to determine if this is not the GA edition, other thank checking the 'Help > About IBM Data Studio' dialog.

    I am more than willing to install a patch for DS32 to assist in resolving this.

    Thanks!
    Bob
    Bob,
    Thank you for all your support! In order to receive the patch and patch install instruction, you may need to work with Data studio customer support team to open PMR for your problem. Once PMR is opened, I will work with customer support team to provide the diagnosing patch and we can go from there.

    Thanks!

    Chunqiu
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-07T21:21:39Z  
    • chunqiu
    • ‏2013-02-07T18:14:29Z
    Bob,
    Thank you for all your support! In order to receive the patch and patch install instruction, you may need to work with Data studio customer support team to open PMR for your problem. Once PMR is opened, I will work with customer support team to provide the diagnosing patch and we can go from there.

    Thanks!

    Chunqiu
    Chunqiu,
    I have opened SR 73051,550,000. I hope this meets your request.

    Bob
  • chunqiu
    chunqiu
    15 Posts

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-07T21:40:11Z  
    Chunqiu,
    I have opened SR 73051,550,000. I hope this meets your request.

    Bob
    Bob,
    Thanks! We will go from there.

    Chunqiu