Topic
7 replies Latest Post - ‏2012-11-30T23:58:21Z by loicjulien
SystemAdmin
SystemAdmin
1632 Posts
ACCEPTED ANSWER

Pinned topic looking for example of running db2 system command in SQL editor

‏2012-11-29T15:17:05Z |
Hi,
Anybody has experience with running db2 SYSTEM command in the DS client SQL editor?

Below is what's mentioned in the Data Studio 3.1.1 info center:

DB2 command support
You can use DB2® commands, both CLP and system, in the scripts that you edit with the SQL and XQuery editor. The SQL and XQuery editor verifies the syntax of the DB2 commands that you type.

any example will be appreciated.

Thank you.
Updated on 2012-11-30T23:58:21Z at 2012-11-30T23:58:21Z by loicjulien
  • stoned99
    stoned99
    26 Posts
    ACCEPTED ANSWER

    Re: looking for example of running db2 system command in SQL editor

    ‏2012-11-29T15:36:24Z  in response to SystemAdmin
    call sysproc.admin_command_db2('-dis thread(*)',15,'THD',null,?,?,?,?,?,?,?,?);
  • loicjulien
    loicjulien
    96 Posts
    ACCEPTED ANSWER

    Re: looking for example of running db2 system command in SQL editor

    ‏2012-11-29T17:39:49Z  in response to SystemAdmin
    Hi Jie Wu,

    You will need to use the CLP run method and add a Bang (!) before your command. I am adding a screenshot to illustrate this.

    Hope this helps,
    ~Loic
    • SystemAdmin
      SystemAdmin
      1632 Posts
      ACCEPTED ANSWER

      Re: looking for example of running db2 system command in SQL editor

      ‏2012-11-29T18:01:46Z  in response to loicjulien
      I tried running these commands with the Command Line Processor but it seems the session is not maintained. How to I run a series of CLP commands with Data Studio?

      !db2 connect to tpcdb;

      !db2 get db cfg;
      Results

      !db2 connect to tpcdb
      Database Connection Information
      Database server = DB2/LINUXX8664 9.7.7
      SQL authorization ID = DB2INST1
      Local database alias = TPCDB

      Query execution time => 4 s: 500 ms
      !db2 get db cfg

      SQL1024N A database connection does not exist. SQLSTATE=08003

      Query execution time => 1 s: 640 ms
      • loicjulien
        loicjulien
        96 Posts
        ACCEPTED ANSWER

        Re: looking for example of running db2 system command in SQL editor

        ‏2012-11-29T18:56:20Z  in response to SystemAdmin
        Hi,

        I could not reproduce this in my environment as the connection remains open; however, I am running the upcoming version. I do know that we have fixed a defect where you had to make sure the password had to be saved (Save password checkbox checked) for us to correctly reestablish the connection on execution for commands.

        I am running on Windows and locally, so, the execution component leverages DAS. Did you execute remotely with SSH?

        If the "Save password" does not help addressing this, let me know and I'll ask more questions on your environment and get some additional help to reproduce and provide you with more info.

        Adding the screenshot.

        Thanks,
        ~Loic
        • SystemAdmin
          SystemAdmin
          1632 Posts
          ACCEPTED ANSWER

          Re: looking for example of running db2 system command in SQL editor

          ‏2012-11-29T19:55:31Z  in response to loicjulien
          I'm running Data Studio 3.1.1 shell sharing with IDA on Windows.

          When I posted before I was trying to send CLP commands to DB2 9.7 FP 7 running on Linux.

          I tried the same commands again on my local Windows system with DB2 10.1 and it worked.
          So I guess there is an issue with CLP commands on remote machines...

          Thanks,
          Mike
          • loicjulien
            loicjulien
            96 Posts
            ACCEPTED ANSWER

            Re: looking for example of running db2 system command in SQL editor

            ‏2012-11-29T22:21:18Z  in response to SystemAdmin
            Hi Mike,

            Thanks for the information. I'll follow up and update you.

            Thanks,
            ~Loic
      • loicjulien
        loicjulien
        96 Posts
        ACCEPTED ANSWER

        Re: looking for example of running db2 system command in SQL editor

        ‏2012-11-30T23:58:21Z  in response to SystemAdmin
        Hi Mike,

        We leverage different protocols local/remote execution (on Windows only - Same on Linux), so this may explain the behavior observed for OS Commands. Overall though, doing !db2 will run those commands as OS commands, and this is not needed in your case since you are running CLP commands. You should modify your script so that you remove !db2, this will allow to keep the connection opened.

        connect to tpcdb;
        get db cfg;

        Make sure to select the CLP run method, as provided in the earlier screenshot. With this runner you are already in the DB2 clp mode, so you do not need to specify DB2 again.

        Hope this helps,
        ~Loic