Topic
  • 26 replies
  • Latest Post - ‏2013-04-19T16:17:36Z by Sriram_Europa
jcvoog
jcvoog
7 Posts

Pinned topic Web Console 3.1.1 and DB2 CLP Scripts

‏2012-05-11T14:03:12Z |
Hello,

I'm trying to use the Web Console to schedule DB2 maintenance scripts (BACKUP DATABASE) over a DB2 Express-C 10.1 installation. The IBM Secure Shell Server is running on the server.

When I launch the script, it fails silently (appears with status "Succeeded" in the history but the job reports a DB21061E error (command line environment not initialized) and the backup is not present). Shell/executable and pure SQL scripts seems to run fine.

What should I do to solve this problem (what am I missing) ?

Environment : DB2 10.1 Express-C x64, Web Console 3.1.1 x64, Windows 7 x64, French.

Thanks for your help, regards,

Jean-Christophe Voogden
Updated on 2013-04-05T16:14:14Z at 2013-04-05T16:14:14Z by Sriram_Europa
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-11T16:45:20Z  
    Hi,
    Is the database that you running the DB2 CLP job against on Windows or on Unix?

    Dimple
  • jcvoog
    jcvoog
    7 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-11T17:07:14Z  
    Hi,
    Is the database that you running the DB2 CLP job against on Windows or on Unix?

    Dimple
    Hello,

    Database server and Web console are on the same system (Windows 7 x64). All services are started on LocalSystem account.
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-11T17:38:15Z  
    • jcvoog
    • ‏2012-05-11T17:07:14Z
    Hello,

    Database server and Web console are on the same system (Windows 7 x64). All services are started on LocalSystem account.
    On Windows, for the DB2 CLP Script job type, we issue the "db2cmd /i /c /w". So looks like there is trouble with db2cmd command. Can you try to issue "db2cmd" using the Executable/Shell Script job type and see if that works against the same database.
    Thanks.

    Dimple
  • jcvoog
    jcvoog
    7 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-14T07:58:53Z  
    On Windows, for the DB2 CLP Script job type, we issue the "db2cmd /i /c /w". So looks like there is trouble with db2cmd command. Can you try to issue "db2cmd" using the Executable/Shell Script job type and see if that works against the same database.
    Thanks.

    Dimple
    Hello,

    When launching a shell script with the command "db2cmd /w /i /c db2 backup database perso to d:\", the backup is performed successfully.

    It looks like my database connection is not identified as Windows ? Tried with a connection to the repository database (meta_db...), and with a connection to the database to be backup-ed itself, both on the same system, both failing with DB21061E.

    And I don't understand the need to create a DATABASE connection to execute a CLP script... By definition all what is needed is an INSTANCE connection, but data studio doesn't seem to know about that.

    Regards,

    Jean-Christophe
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-14T16:50:56Z  
    • jcvoog
    • ‏2012-05-14T07:58:53Z
    Hello,

    When launching a shell script with the command "db2cmd /w /i /c db2 backup database perso to d:\", the backup is performed successfully.

    It looks like my database connection is not identified as Windows ? Tried with a connection to the repository database (meta_db...), and with a connection to the database to be backup-ed itself, both on the same system, both failing with DB21061E.

    And I don't understand the need to create a DATABASE connection to execute a CLP script... By definition all what is needed is an INSTANCE connection, but data studio doesn't seem to know about that.

    Regards,

    Jean-Christophe
    Hi,
    How did you connect to the repos DB and database to be backed up? I assume you have added the database to be backed up on the "Databases" page. So can you try to click the "Test Connection" button after selecting this database from the grid and see if that is successful. Similarly for the repository database, pls. go to the "Configuration Repository" page, click the "Select Repository Database" buton and click on "Test Connection" and see if you get a successful connection message.
    By identifiying the DATABASE in the DB2 CLP Script, you don't need to specify the database in the script and you can specify databases in different instances.
    Thanks.
    Dimple
  • Sriram_Europa
    Sriram_Europa
    68 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-14T17:38:39Z  
    Hi,
    How did you connect to the repos DB and database to be backed up? I assume you have added the database to be backed up on the "Databases" page. So can you try to click the "Test Connection" button after selecting this database from the grid and see if that is successful. Similarly for the repository database, pls. go to the "Configuration Repository" page, click the "Select Repository Database" buton and click on "Test Connection" and see if you get a successful connection message.
    By identifiying the DATABASE in the DB2 CLP Script, you don't need to specify the database in the script and you can specify databases in different instances.
    Thanks.
    Dimple
    In Job manager, when you pick a database to execute a db2 clp script against - it just uses the credentials associated with that database to ssh into that system.

    It then uses db2/db2cmd to execute the script that you have provided - essentially against that DB2 instance. However - you would still need to explicitly identify the database to CONNECT to in your db2 clp script.

    Here is an example that I just tried out:

    create a db2 clp job and entered this in my script.

    connect to TESTDB;
    list applications;

    schedule for execution or just choose "run job" & pick that database.

    Can you try something similar and let us know if it works ?

    (Shell script job type too is similar - the credentials associated with the database in DSWC is used by the job manager to ssh to that system. The key difference vs. DB2 CLP job type is that db2 or db2cmd is not invoked)
  • jcvoog
    jcvoog
    7 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-15T09:22:51Z  
    Hi,
    How did you connect to the repos DB and database to be backed up? I assume you have added the database to be backed up on the "Databases" page. So can you try to click the "Test Connection" button after selecting this database from the grid and see if that is successful. Similarly for the repository database, pls. go to the "Configuration Repository" page, click the "Select Repository Database" buton and click on "Test Connection" and see if you get a successful connection message.
    By identifiying the DATABASE in the DB2 CLP Script, you don't need to specify the database in the script and you can specify databases in different instances.
    Thanks.
    Dimple
    Hello,

    I'm aware of the expected result (practicing DB2 since 2.1 - even used 1.2 on OS/2), I know about the difference between cmd.exe and db2cmd.exe (DB2 CLP) ;) ). The database connexion has been declared in the "Databases" (if my translations are correct since I'm using a French version and do not see English labels - "Bases de données").

    • The database connection works fine, test is successful.
    • An SQL job executed against the database connection works fine (I performed inserts in a log table to confirm proper execution).
    • A Shell / Executable job running db2cmd (command "db2cmd /i /w /c db2 backup...") works fine (backup performed).
    • A DB2 CLP job fails with the "DB21061E L'environnement nécessaire à l'exécution de l'interpréteur de commandes n'a pas été initialisé." error in the log (but appears successful in the history). No backup performed.

    The script (copy/pasted from DataStudio but I could have written it myself) :

    CONNECT TO PERSO;
    QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
    CONNECT RESET;
    DEACTIVATE DATABASE PERSO;
    BACKUP DATABASE PERSO TO d:\ EXCLUDE LOGS WITHOUT PROMPTING;
    CONNECT TO PERSO;
    UNQUIESCE DATABASE;
    CONNECT RESET;

    • PERSO is the database used when launching (interactively) the script. Tried all combinations of launch parameters (single or multiple database, default or specific creditentials). Did not try to schedule the execution at a later time.

    I presumed that the CYGWIN OpenSSH installation instruction where obsolete, given that DB2 10.1 installed an SSH server by itself. Should I try to un-install the "IBM Secure Shell Server for Windows" and install CYGWIN instead ?

    Thanks for the help,

    Jean-Christophe

    PS :

    Just for information, the job report in history seems to contain only a job completion status, is it possible to get a full report with console output ?
  • jcvoog
    jcvoog
    7 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-15T09:58:27Z  
    • jcvoog
    • ‏2012-05-15T09:22:51Z
    Hello,

    I'm aware of the expected result (practicing DB2 since 2.1 - even used 1.2 on OS/2), I know about the difference between cmd.exe and db2cmd.exe (DB2 CLP) ;) ). The database connexion has been declared in the "Databases" (if my translations are correct since I'm using a French version and do not see English labels - "Bases de données").

    • The database connection works fine, test is successful.
    • An SQL job executed against the database connection works fine (I performed inserts in a log table to confirm proper execution).
    • A Shell / Executable job running db2cmd (command "db2cmd /i /w /c db2 backup...") works fine (backup performed).
    • A DB2 CLP job fails with the "DB21061E L'environnement nécessaire à l'exécution de l'interpréteur de commandes n'a pas été initialisé." error in the log (but appears successful in the history). No backup performed.

    The script (copy/pasted from DataStudio but I could have written it myself) :

    CONNECT TO PERSO;
    QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
    CONNECT RESET;
    DEACTIVATE DATABASE PERSO;
    BACKUP DATABASE PERSO TO d:\ EXCLUDE LOGS WITHOUT PROMPTING;
    CONNECT TO PERSO;
    UNQUIESCE DATABASE;
    CONNECT RESET;

    • PERSO is the database used when launching (interactively) the script. Tried all combinations of launch parameters (single or multiple database, default or specific creditentials). Did not try to schedule the execution at a later time.

    I presumed that the CYGWIN OpenSSH installation instruction where obsolete, given that DB2 10.1 installed an SSH server by itself. Should I try to un-install the "IBM Secure Shell Server for Windows" and install CYGWIN instead ?

    Thanks for the help,

    Jean-Christophe

    PS :

    Just for information, the job report in history seems to contain only a job completion status, is it possible to get a full report with console output ?
    Just a precision for my last question : it only applies to SQL Script output.
  • jcvoog
    jcvoog
    7 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-15T12:12:31Z  
    • jcvoog
    • ‏2012-05-15T09:58:27Z
    Just a precision for my last question : it only applies to SQL Script output.
    Another precision : if I change the credentials of the "IBM Secure Shell Server for Windows" from LocalSystem to an administrator account (in fact the same account used for database connection), the job starts and seems to hang forever (no activity, the backup is not performed) until the SSH service is stopped (then the job fails).
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-15T18:27:23Z  
    • jcvoog
    • ‏2012-05-15T12:12:31Z
    Another precision : if I change the credentials of the "IBM Secure Shell Server for Windows" from LocalSystem to an administrator account (in fact the same account used for database connection), the job starts and seems to hang forever (no activity, the backup is not performed) until the SSH service is stopped (then the job fails).
    Hi,
    Thanks for trying out different options.
    1. Regarding your question on the output of SQL Only Script job type, we do not store and display the result sets since they could be large. But we will look into an enhancement in a future release where - storing maybe say the first 100 result sets
    2. Regarding the DB2 CLP Script issue with IBM Secure Shell, you can try with the CYGWIN SSH but also open a PMR so that we can investigate DB2 CLP Script job type with IBM Secure Shell.
  • jcvoog
    jcvoog
    7 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-05-16T10:32:40Z  
    Hi,
    Thanks for trying out different options.
    1. Regarding your question on the output of SQL Only Script job type, we do not store and display the result sets since they could be large. But we will look into an enhancement in a future release where - storing maybe say the first 100 result sets
    2. Regarding the DB2 CLP Script issue with IBM Secure Shell, you can try with the CYGWIN SSH but also open a PMR so that we can investigate DB2 CLP Script job type with IBM Secure Shell.
    Hello,

    I made progress on this problem. Using the CYGWIN OpenSSH, the DB2 CLP scripts works fine.

    The service configuration leads me to a problem, which may or may not be the root cause. Initially, I couldn't connect to the Cygwin SSH Server (access denied). Our company administrators have the habit to create two copies of our user, one on the domain and one local to the machine (don't know exactly why, may be useful for laptop users...).

    On my system they created the local profile with a different case than the domain profile (JCV instead of jcv). Renaming the local user with the correct case solved my authentication problem, and now the backup works with the Cygwin version of SSH. But the job is marked as failed (status code 3) !!

    This seems to be due to the presence of cygwin warnings in the job log :

    "tty" option detected in CYGWIN environment variable.
    CYGWIN=tty is no longer supported. Please remove it from your
    CYGWIN environment variable and use a terminal emulator like mintty,
    xterm, or rxvt.
    cygwin warning:
    MS-DOS style path detected: c:/db2cmdRunner.bat
    Preferred POSIX equivalent is: /cygdrive/c/db2cmdRunner.bat
    CYGWIN environment variable option "nodosfilewarning" turns off this warning.
    Consult the user's guide for more details about POSIX paths:
    http://cygwin.com/cygwin-ug-net/using.html#using-pathnames

    Solved those problems by modifying the CYGWIN variable to remove the "tty" option and adding the "nodosfilewarning". Maybe the DataStudio installation instructions should be updated...

    I tried to restart the IBM SSH afterwards, but now the execution of the script fails with a new error :

    For input string: "ParamŠtre non valide - /dbb48db40536096fa570f6203c855b2b.level"

    Maybe the Cygwin installation corrupted some IBM settings. I give up for now...

    Regards,

    Jean-Christophe
    PS: this leaves me with a mitigated conclusion about DB2 10. I think DataStudio is far too complicated to setup and use for our average customer, compared to the "good old" control center, which at least was working "out of the box" and was far more intuitive... And the lack of a graphical tool for client setup is a problem too (not everybody is at ease with the command line processor and the catalog node / database commands).
  • Mark.BB
    Mark.BB
    9 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-06-05T11:08:50Z  
    In Job manager, when you pick a database to execute a db2 clp script against - it just uses the credentials associated with that database to ssh into that system.

    It then uses db2/db2cmd to execute the script that you have provided - essentially against that DB2 instance. However - you would still need to explicitly identify the database to CONNECT to in your db2 clp script.

    Here is an example that I just tried out:

    create a db2 clp job and entered this in my script.

    connect to TESTDB;
    list applications;

    schedule for execution or just choose "run job" & pick that database.

    Can you try something similar and let us know if it works ?

    (Shell script job type too is similar - the credentials associated with the database in DSWC is used by the job manager to ssh to that system. The key difference vs. DB2 CLP job type is that db2 or db2cmd is not invoked)
    Hello

    DB2 9.7 and 10.1 x64, Web Console 3.1.1 x64, Windows 7 x64

    I installed and configured Cygwin an described here:
    http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.base.install.doc/topics/t_dsa_configurelcygwin.html
    The difference during my installation was that I had to install the sshd service under some cyg_server account since I was told that it's unable to use the Local System account for that.
    SSH server works well.
    When I try to use DB2 CLP script with a single command, for example:
    backup db sample ...;
    I see this output:
    --
    connect to sample

    Database Connection Information
    Database server = DB2/NT64 9.7.5
    SQL authorization ID = CYG_SERV...
    Local database alias = SAMPLE

    backup db sample ...

    SQL1092N "CYG_SERVER" does not have the authority to perform the requested
    command or operation.
    --
    So it seems that Web Console ssh'es with CYG_SERVER account (which the OS service sshd service runs under) even I use 'Single database' and explicitly specify a different userid.

    When I use 'Shell script' I don't see such strange effect - the WC ssh'es with userid provided by me and not from this CYG_SERVER userid.

    The question is: Is this some feature of DB2 CLP scripts?

    Sincerely,
    Mark B.
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-06-05T17:42:47Z  
    • Mark.BB
    • ‏2012-06-05T11:08:50Z
    Hello

    DB2 9.7 and 10.1 x64, Web Console 3.1.1 x64, Windows 7 x64

    I installed and configured Cygwin an described here:
    http://publib.boulder.ibm.com/infocenter/dstudio/v3r1/topic/com.ibm.datatools.base.install.doc/topics/t_dsa_configurelcygwin.html
    The difference during my installation was that I had to install the sshd service under some cyg_server account since I was told that it's unable to use the Local System account for that.
    SSH server works well.
    When I try to use DB2 CLP script with a single command, for example:
    backup db sample ...;
    I see this output:
    --
    connect to sample

    Database Connection Information
    Database server = DB2/NT64 9.7.5
    SQL authorization ID = CYG_SERV...
    Local database alias = SAMPLE

    backup db sample ...

    SQL1092N "CYG_SERVER" does not have the authority to perform the requested
    command or operation.
    --
    So it seems that Web Console ssh'es with CYG_SERVER account (which the OS service sshd service runs under) even I use 'Single database' and explicitly specify a different userid.

    When I use 'Shell script' I don't see such strange effect - the WC ssh'es with userid provided by me and not from this CYG_SERVER userid.

    The question is: Is this some feature of DB2 CLP scripts?

    Sincerely,
    Mark B.
    Hi Mark,
    Even for DB2 CLP Script, if you select the Single Database option and specify a userid/pwd , we use that to connect via SSH. The only difference between the Executable/Shell Script job type and DB2 CLP Script is that we explicitly do a db2 connect to the database in the DB2 CLP Script type bu tin case of the Shell/Executable type, we just run the script as is. Could you try to specify a bogus userid/pwd instead and then try the Test Connection button on the dialog where you specify the credentials and see if the Test Connection fails. And then run the job using the bogus credentials and if it fails it will tell us that it did use those bogus credentials ( and not the credentials from the database connection) to connect via SSH.
    Also attaching a screenshot of the dialog where you specify the credentials and select the single database. Thanks.

    Dimple
  • Mark.BB
    Mark.BB
    9 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-06-06T08:47:13Z  
    Hi Mark,
    Even for DB2 CLP Script, if you select the Single Database option and specify a userid/pwd , we use that to connect via SSH. The only difference between the Executable/Shell Script job type and DB2 CLP Script is that we explicitly do a db2 connect to the database in the DB2 CLP Script type bu tin case of the Shell/Executable type, we just run the script as is. Could you try to specify a bogus userid/pwd instead and then try the Test Connection button on the dialog where you specify the credentials and see if the Test Connection fails. And then run the job using the bogus credentials and if it fails it will tell us that it did use those bogus credentials ( and not the credentials from the database connection) to connect via SSH.
    Also attaching a screenshot of the dialog where you specify the credentials and select the single database. Thanks.

    Dimple
    Hi Dimple,

    When I try to use a bogus userid/pwd I get an error (I use the script with single 'values current user;' command for simplicity):
    ***
    ID: 1338886530340
    Name: test DB2CLP
    Commands Executed:

    values current user;

    Start Time: 2012-06-06 11:23:17
    End Time: 2012-06-06 11:23:18
    Error message:

    Auth cancel

    Result: The job execution failed. Execution Status code: 3
    ***

    And in the Windows Event Viewer I see a messages from sshd like:
    sshd: PID 7856: Failed password for db2inst1 from 127.0.0.1 port 59406 ssh2
    But when I provide a valid userid/password (see an attached screenshot) I get this:
    ***
    ID: 1338886530340
    Name: test DB2CLP
    Commands Executed:

    values current user;

    Start Time: 2012-06-06 12:30:59
    End Time: 2012-06-06 12:31:08
    Output from executing:

    connect to sample

    Database Connection Information

    Database server = DB2/NT64 9.7.5
    SQL authorization ID = CYG_SERV...
    Local database alias = SAMPLE

    values current user

    1
    CYG_SERVER

    1 record(s) selected.
    terminate

    DB20000I The TERMINATE command completed successfully.

    Result: The job executed successfully. Execution Status code: 2
    ***

    And in the Windows Event Viewer I see a message from sshd like:
    sshd: PID 12180: Accepted password for db2inst1 from 127.0.0.1 port 59451 ssh2
    BTW:
    When I place my commands into some file (user.sql) on the server I do see the correct output:

    ***
    mark_b@BARIN_M ~
    $ expect -c 'spawn ssh localhost -l db2inst1 db2cmd /i /w /c db2 -tvf c:/cygwin/home/db2inst1/db2test/user.sql -z c:/cygwin/home/db2inst1/db2test/user.log ; expect password ; send "***\n" ; interact'
    spawn ssh localhost -l db2inst1 db2cmd /i /w /c db2 -tvf c:/cygwin/home/db2inst1/db2test/user.sql -z c:/cygwin/home/db2inst1/db2test/user.log
    db2inst1@localhost's password:

    connect to sample

    Database Connection Information

    Database server = DB2/NT64 9.7.5
    SQL authorization ID = DB2INST1
    Local database alias = SAMPLE
    values current user

    1
    DB2INST1

    1 record(s) selected.
    connect reset
    DB20000I The SQL command completed successfully.
    ***
  • Mark.BB
    Mark.BB
    9 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-06-09T14:07:56Z  
    Hi Mark,
    Even for DB2 CLP Script, if you select the Single Database option and specify a userid/pwd , we use that to connect via SSH. The only difference between the Executable/Shell Script job type and DB2 CLP Script is that we explicitly do a db2 connect to the database in the DB2 CLP Script type bu tin case of the Shell/Executable type, we just run the script as is. Could you try to specify a bogus userid/pwd instead and then try the Test Connection button on the dialog where you specify the credentials and see if the Test Connection fails. And then run the job using the bogus credentials and if it fails it will tell us that it did use those bogus credentials ( and not the credentials from the database connection) to connect via SSH.
    Also attaching a screenshot of the dialog where you specify the credentials and select the single database. Thanks.

    Dimple
    Seems that I have the same problem with shell script.
    I placed 2 commands in a file:

    $ cat c:/cygwin/home/mark_b/db2test/test_sh2.sh

    log=d:/tmp/username.txt
    id > $log
    cmd /c echo %username% >> $log

    When I run it from cygwin:

    mark_b@BARIN_M ~
    $ c:/cygwin/home/mark_b/db2test/test_sh2.sh

    I get "correct" output:

    $ cat d:/tmp/username.txt
    uid=1000(mark_b) gid=513(None) группы=513(None),545(Users),1009(DB2ADMNS)
    mark_b

    The same is for expect utility:

    mark_b@BARIN_M ~
    $ expect -c 'spawn ssh db2inst1@localhost c:/cygwin/home/mark_b/db2test/test_sh2.sh ; expect password ; send "***\n" ; interact'
    spawn ssh db2inst1@localhost c:/cygwin/home/mark_b/db2test/test_sh2.sh
    db2inst1@localhost's password:

    $ cat d:/tmp/username.txt
    uid=1003(db2inst1) gid=513(None) groups=513(None),0(root),544(Administrators),545(Users),1009(DB2ADMNS),1006(db2iadm1)
    db2inst1

    But when I run this script from the Job Manager (job consists of one command calling this test_sh2.sh script) using db2inst1 user I get this:

    $ cat d:/tmp/username.txt
    uid=1003(*db2inst1*) gid=513(None) groups=513(None),0(root),544(Administrators),545(Users),1009(DB2ADMNS),1006(db2iadm1)
    cyg_server

    I see the same when I use
    db2 -tf some_file.sql
    in a shell job - it connects to the database with cyg_server user.

    So it seems that when we call any "native" Windows command like cmd or db2 in a shell script from the Job Manager - it runs under cyg_server user.
    Can somebody produce a similar test inform on results?

    Thanks in advance,
    Mark B.
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-06-09T23:41:45Z  
    • Mark.BB
    • ‏2012-06-09T14:07:56Z
    Seems that I have the same problem with shell script.
    I placed 2 commands in a file:

    $ cat c:/cygwin/home/mark_b/db2test/test_sh2.sh

    log=d:/tmp/username.txt
    id > $log
    cmd /c echo %username% >> $log

    When I run it from cygwin:

    mark_b@BARIN_M ~
    $ c:/cygwin/home/mark_b/db2test/test_sh2.sh

    I get "correct" output:

    $ cat d:/tmp/username.txt
    uid=1000(mark_b) gid=513(None) группы=513(None),545(Users),1009(DB2ADMNS)
    mark_b

    The same is for expect utility:

    mark_b@BARIN_M ~
    $ expect -c 'spawn ssh db2inst1@localhost c:/cygwin/home/mark_b/db2test/test_sh2.sh ; expect password ; send "***\n" ; interact'
    spawn ssh db2inst1@localhost c:/cygwin/home/mark_b/db2test/test_sh2.sh
    db2inst1@localhost's password:

    $ cat d:/tmp/username.txt
    uid=1003(db2inst1) gid=513(None) groups=513(None),0(root),544(Administrators),545(Users),1009(DB2ADMNS),1006(db2iadm1)
    db2inst1

    But when I run this script from the Job Manager (job consists of one command calling this test_sh2.sh script) using db2inst1 user I get this:

    $ cat d:/tmp/username.txt
    uid=1003(*db2inst1*) gid=513(None) groups=513(None),0(root),544(Administrators),545(Users),1009(DB2ADMNS),1006(db2iadm1)
    cyg_server

    I see the same when I use
    db2 -tf some_file.sql
    in a shell job - it connects to the database with cyg_server user.

    So it seems that when we call any "native" Windows command like cmd or db2 in a shell script from the Job Manager - it runs under cyg_server user.
    Can somebody produce a similar test inform on results?

    Thanks in advance,
    Mark B.
    Hi Mark,
    Could you pls. let me know which version of Cygwin you downloaded from http://cygwin.com. I will download it and try it out and then will let you know what I find. Thanks.

    Dimple
  • Mark.BB
    Mark.BB
    9 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2012-06-10T09:08:45Z  
    Hi Mark,
    Could you pls. let me know which version of Cygwin you downloaded from http://cygwin.com. I will download it and try it out and then will let you know what I find. Thanks.

    Dimple
    Hi Dimple,

    this is the latest version 1.7.15.

    $ uname -a
    CYGWIN_NT-6.1-WOW64 BARIN_M 1.7.15(0.260/5/3) 2012-05-09 10:25 i686 Cygwin

    Thanks in advance,
    Mark B.
  • Mark.BB
    Mark.BB
    9 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2013-04-02T12:17:57Z  
    • Mark.BB
    • ‏2012-06-10T09:08:45Z
    Hi Dimple,

    this is the latest version 1.7.15.

    $ uname -a
    CYGWIN_NT-6.1-WOW64 BARIN_M 1.7.15(0.260/5/3) 2012-05-09 10:25 i686 Cygwin

    Thanks in advance,
    Mark B.
    Hi All,

    I've just installed DSWC 3.2 and the latest cygwin:

    $ uname -a
    CYGWIN_NT-6.1-WOW64 pdc 1.7.17(0.262/5/3) 2012-10-19 14:39 i686 Cygwin

    But results are the same:

    for this OS script:
    ---
    cmd /c echo %username%
    id
    ---

    which was being run from the db2tools user I have this output:

    ---
    cyg_server
    uid=1111(db2tools) gid=513(Domain Users) groups=513(Domain Users),549(Server Operators),545(Users),1107(DB2USERS),1112(DB2CTRL)
    ---

    cyg_server - the windows account for the sshd service.
    This means that even if I able to adjust a DB2 environment all "db2 ..." commands will be ran from the cyg_server user.

    Does anybody see such behavior on Windows & Cygwin openSSH?

    Thanks in advance,
    Mark B.
  • Mark.BB
    Mark.BB
    9 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2013-04-02T13:15:29Z  
    • Mark.BB
    • ‏2012-06-10T09:08:45Z
    Hi Dimple,

    this is the latest version 1.7.15.

    $ uname -a
    CYGWIN_NT-6.1-WOW64 BARIN_M 1.7.15(0.260/5/3) 2012-05-09 10:25 i686 Cygwin

    Thanks in advance,
    Mark B.
    Running CLP script returns an error now:

    Execution log:
    ---
    connect to sample;
    values user;
    connect reset;

    Start Time: 2013-04-02 17:11:06
    End Time: 2013-04-02 17:11:09
    Error message:

    null

    Errors from executing:

    Failed to Write File: /c//ddb6d5df3446beff4b46783e086d7cf6db2cmdRunner.bat
    ---

    with error in the
    "C:\Program Files\IBM\DSWC3.2\logs\JobManagerLogger.0"
    file:
    ---
    JobManagerLogger (14294) Apr 2, 2013 5:11:09 PM ERROR Exception in run() of DB2CLPRunner
    java.lang.NullPointerException
    at com.ibm.datatools.jobmanager.execution.DB2CLPRunner.executeCommands(DB2CLPRunner.java:178)
    at com.ibm.datatools.jobmanager.execution.DB2CLPRunner.run(DB2CLPRunner.java:82)
    at com.ibm.datatools.sqwruntime.taskmgr.core.impl.TaskRunner.run(TaskRunner.java:54)
    at java.lang.Thread.run(Thread.java:779)
    ---

    Where does it want to write this file and what for?
    Is this configurable?

    Regards,
    Mark B.
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2013-04-02T18:24:52Z  
    • Mark.BB
    • ‏2013-04-02T13:15:29Z
    Running CLP script returns an error now:

    Execution log:
    ---
    connect to sample;
    values user;
    connect reset;

    Start Time: 2013-04-02 17:11:06
    End Time: 2013-04-02 17:11:09
    Error message:

    null

    Errors from executing:

    Failed to Write File: /c//ddb6d5df3446beff4b46783e086d7cf6db2cmdRunner.bat
    ---

    with error in the
    "C:\Program Files\IBM\DSWC3.2\logs\JobManagerLogger.0"
    file:
    ---
    JobManagerLogger (14294) Apr 2, 2013 5:11:09 PM ERROR Exception in run() of DB2CLPRunner
    java.lang.NullPointerException
    at com.ibm.datatools.jobmanager.execution.DB2CLPRunner.executeCommands(DB2CLPRunner.java:178)
    at com.ibm.datatools.jobmanager.execution.DB2CLPRunner.run(DB2CLPRunner.java:82)
    at com.ibm.datatools.sqwruntime.taskmgr.core.impl.TaskRunner.run(TaskRunner.java:54)
    at java.lang.Thread.run(Thread.java:779)
    ---

    Where does it want to write this file and what for?
    Is this configurable?

    Regards,
    Mark B.
    Hi,
    We have fixed some issues related to running Job Manager using CYGWIN in Web Console 3.2. It would be helpful if you could upgrade to Web Console 3.2 and try and let us know. Thank you.

    Regards,
    Dimple
  • Mark.BB
    Mark.BB
    9 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2013-04-03T06:05:18Z  
    Hi,
    We have fixed some issues related to running Job Manager using CYGWIN in Web Console 3.2. It would be helpful if you could upgrade to Web Console 3.2 and try and let us know. Thank you.

    Regards,
    Dimple
    Hi,

    I'm already at DSWC 3.2

    Thanks,
    Mark B.
  • SystemAdmin
    SystemAdmin
    1632 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2013-04-04T17:35:12Z  
    • Mark.BB
    • ‏2013-04-03T06:05:18Z
    Hi,

    I'm already at DSWC 3.2

    Thanks,
    Mark B.
    Hi,
    If you are already at 3.2, then I suggest you open a PMR with the IBM support team and provide us with the logs with tracing on. You can turn on tracing by: opening up the Services page under Product Setup and then select JobManagerLogger from grid and then click on the "Configure" button. Select "Trace" from the Log level drop down and then rerun the DB2 CLP job. After the job has executed, pls. run the collector.bat which will generate a zip file and provide that zip file to the IBM support team. Thanks.

    Dimple
  • Sriram_Europa
    Sriram_Europa
    68 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2013-04-05T16:14:14Z  
    Hi,
    If you are already at 3.2, then I suggest you open a PMR with the IBM support team and provide us with the logs with tracing on. You can turn on tracing by: opening up the Services page under Product Setup and then select JobManagerLogger from grid and then click on the "Configure" button. Select "Trace" from the Log level drop down and then rerun the DB2 CLP job. After the job has executed, pls. run the collector.bat which will generate a zip file and provide that zip file to the IBM support team. Thanks.

    Dimple
    From this -

    Failed to Write File: /c//ddb6d5df3446beff4b46783e086d7cf6db2cmdRunner.bat

    I suspect that the cygwin sshD server did not provide enough information to identify a temporary directory on that windows host that the user's credentials you have chosen would have write permissions to. A temp directory is needed to place batch scripts in prior to execution and to capture output as well as the 'errorlevel' in after the scripts execution

    I suspect that recent builds of cygwin sshD removes some key environment variables such as "USERPROFILE" during an SSH session.

    Here is something you can try:
    echo %USERPROFILE% in a dos prompt or echo $USERPROFILE from a cygwin shell after logging in as that very same user that you provided to Job Manager/DSWC.

    • and then try something like this:
    ssh userID@hostipaddress env - and see if that output returns the USERPROFILE variable's value

    Now - if it is indeed the case that USERPROFILE (and other variables such as TEMP, TMP etc..) get removed from the cygwin sshD sessions -then DSWC would need an alternate mechanism to identify the temporary directory on the remote host (which would mean at least a patch that you can work with IBM support with).

    By the way - I have noticed that the IBM SSH Server that is part of the DB2 Linux, Unix & Windows distributions (v10 & later) does not have these problems with DSWC v3.2. This is usually a better alternative to cygwin-sshd as its more stable & does not change as drastically as cygwin sshd seems to. Would you be able to adopt the IBM SSH server ? (Note there are still some restrictions with the IBM SSH server usage with Data Studio Eclipse)
  • Mark.BB
    Mark.BB
    9 Posts

    Re: Web Console 3.1.1 and DB2 CLP Scripts

    ‏2013-04-18T13:39:52Z  
    From this -

    Failed to Write File: /c//ddb6d5df3446beff4b46783e086d7cf6db2cmdRunner.bat

    I suspect that the cygwin sshD server did not provide enough information to identify a temporary directory on that windows host that the user's credentials you have chosen would have write permissions to. A temp directory is needed to place batch scripts in prior to execution and to capture output as well as the 'errorlevel' in after the scripts execution

    I suspect that recent builds of cygwin sshD removes some key environment variables such as "USERPROFILE" during an SSH session.

    Here is something you can try:
    echo %USERPROFILE% in a dos prompt or echo $USERPROFILE from a cygwin shell after logging in as that very same user that you provided to Job Manager/DSWC.

    • and then try something like this:
    ssh userID@hostipaddress env - and see if that output returns the USERPROFILE variable's value

    Now - if it is indeed the case that USERPROFILE (and other variables such as TEMP, TMP etc..) get removed from the cygwin sshD sessions -then DSWC would need an alternate mechanism to identify the temporary directory on the remote host (which would mean at least a patch that you can work with IBM support with).

    By the way - I have noticed that the IBM SSH Server that is part of the DB2 Linux, Unix & Windows distributions (v10 & later) does not have these problems with DSWC v3.2. This is usually a better alternative to cygwin-sshd as its more stable & does not change as drastically as cygwin sshd seems to. Would you be able to adopt the IBM SSH server ? (Note there are still some restrictions with the IBM SSH server usage with Data Studio Eclipse)

    Hello.

    From putty:

    ---

    cyg_server@pdc ~
    $ env | grep -E "USERP|TEMP|TMP"
    TEMP=/tmp
    TMP=/tmp

    cyg_server@pdc ~
    $ echo test > /tmp/test.txt | cat /cygdrive/c/cygwin/tmp/test.txt
    test
    ---

    From cmd:

    ---

    C:\Windows\system32>set | grep -E "USERP|TEMP|TMP"
    TEMP=C:\Users\CYG_SE~1\AppData\Local\Temp
    TMP=C:\Users\CYG_SE~1\AppData\Local\Temp
    USERPROFILE=C:\Users\cyg_server
    ---

    So there is no USERPROFILE variable in the ssh session for cyg_server indeed.

    And TEMPS directories are different in the cmd and ssh sessions.

    It's better to open a PMR for this issue I think.

     

    As for 'IBM SSH Server': I won't use it until this restrictions is still there:

    Restriction: IBM Secure Shell Server for Windows, which is installed with DB2 V10.1 Server products, is not supported and cannot be used to run SQL statements, utilities, and commands, or to browse and access files on remote servers.

    http://pic.dhe.ibm.com/infocenter/dstudio/v3r2/topic/com.ibm.datatools.base.install.doc/topics/c_dsa_remote.html

    I won't install this 'IBM Secure Shell' just for DSWC and CYGWIN for Data Studio.

    Thanks,

    Mark B.