Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
15 replies Latest Post - ‏2013-02-07T21:40:11Z by chunqiu
SystemAdmin
SystemAdmin
1632 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T18:15:13Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

      ‏2013-02-06T18:53:23Z  in response to 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
        ACCEPTED ANSWER

        Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

        ‏2013-02-06T19:05:43Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

          ‏2013-02-06T19:22:01Z  in response to 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
            ACCEPTED ANSWER

            Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

            ‏2013-02-06T19:29:44Z  in response to SystemAdmin
            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
              ACCEPTED ANSWER

              Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

              ‏2013-02-06T20:34:53Z  in response to SystemAdmin
              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
                ACCEPTED ANSWER

                Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

                ‏2013-02-06T21:08:07Z  in response to chunqiu
                Chunqiu,
                I have attached the 'About IBM Data Studio' dialog box.

                Bob
                • SystemAdmin
                  SystemAdmin
                  1632 Posts
                  ACCEPTED ANSWER

                  Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

                  ‏2013-02-06T23:00:28Z  in response to SystemAdmin
                  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
                    ACCEPTED ANSWER

                    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

                    ‏2013-02-07T00:17:37Z  in response to SystemAdmin
                    Bob,
                    Thanks for the update. I could not view the About DS jpg, it is corrupted.
                    • chunqiu
                      chunqiu
                      15 Posts
                      ACCEPTED ANSWER

                      Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

                      ‏2013-02-07T00:35:44Z  in response to chunqiu
                      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
                        ACCEPTED ANSWER

                        Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

                        ‏2013-02-07T16:10:57Z  in response to 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
                          ACCEPTED ANSWER

                          Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

                          ‏2013-02-07T18:14:29Z  in response to SystemAdmin
                          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
                            ACCEPTED ANSWER

                            Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

                            ‏2013-02-07T21:21:39Z  in response to chunqiu
                            Chunqiu,
                            I have opened SR 73051,550,000. I hope this meets your request.

                            Bob
                            • chunqiu
                              chunqiu
                              15 Posts
                              ACCEPTED ANSWER

                              Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

                              ‏2013-02-07T21:40:11Z  in response to SystemAdmin
                              Bob,
                              Thanks! We will go from there.

                              Chunqiu
  • chunqiu
    chunqiu
    15 Posts
    ACCEPTED ANSWER

    Re: Call Stored SYSPROC.ADMIN_COMMAND_DB2 from Data Studio 3.2

    ‏2013-02-06T18:55:18Z  in response to SystemAdmin
    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