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

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

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

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

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

    ‏2012-11-29T17:39:49Z  
    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

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

    ‏2012-11-29T18:01:46Z  
    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
    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

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

    ‏2012-11-29T18:56:20Z  
    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
    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

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

    ‏2012-11-29T19:55:31Z  
    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
    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

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

    ‏2012-11-29T22:21:18Z  
    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
    Hi Mike,

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

    Thanks,
    ~Loic
  • loicjulien
    loicjulien
    96 Posts

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

    ‏2012-11-30T23:58:21Z  
    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
    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