Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    9

    Unanswered: db2cmd in a remote session

    Hello there,

    I have a Unix shell-script that uses ssh to connect with a windows machine / cygwin and runs a script there.
    At some point, I have to write some data in a DB2-DB and it goes like this:

    Code:
    ssh user@host "
    ...
    ...
    db2cmd -i -w <<EOF 1>>$logpath/log.log 2>>$logpath/error.log
    db2 CONNECT TO database USER testuser USING topsecret 
    db2 Insert into schema.tab values (1)
    EOF
    ...
    ...
    
    "
    This doesn't work :-(
    The "output" of the db2cmd seems to get lost in the layers of connections (ssh, db2cmd-"window"). The error.log is empty and log.log just contains the Windows-path (E:\sshprofiles\user) and vi says "Incomplete last line" so there isn't much to read from it.
    (I am not so sure about the -i -w part but only these parameters seem to work in the last example)

    I tried an interactive ssh-session (without the "..."):

    Code:
    ssh user@host 
    db2cmd -i -w <<EOF 1>>$logpath/log.log 2>>$logpath/error.log
    db2 CONNECT TO database USER testuser USING topsecret 
    db2 Insert into schema.tab values (1)
    EOF
    This doesn't seem to do anything neither.

    However, when I open an interactive ssh-session and run db2cmd in the active session (without the <<EOF), the insert is successful:
    Code:
    ssh user@host
    ...
    db2cmd -i -w # this opens the DB2-Command window and now I'm in E:\sshprofiles\user. There I can run the SQL commands:
    db2 connect to database USER testuser USING topsecret # connection to the DB was successful
    db2 insert into schema.tab values (1) # value is written in the table
    exit # back to the cygwin shell
    exit # back to where I called the outer script


    Now this is where I am really losing it:
    I tried it with another machine (ssh user@host2) and ran into the same problems.
    However, when I use a simple procedure to do the insert, it's working in batch-mode (with the EOF) on "host2" but not so on "host"
    Code:
    ssh user@host2 "
    ...
    db2cmd -i -w
    db2 connect to database USER testuser USING topsecret
    db2 call schema.proc_ins(1234)
    EOF
    "
    This works on host2, but not on with the other machine.

    Probably something is up with the ODBC set-up on "host". I'd really appreciate any suggestions what I could do about this!


    This is the procedure I used:
    Code:
    CREATE PROCEDURE schema.proc_ins (
      Arg_SQL_Name	int
       )
    LANGUAGE SQL
    BEGIN
       DECLARE EXIT_MESSAGE CHAR(70);
       DECLARE STATUS INTEGER DEFAULT 0;
    	IF	Arg_SQL_Name IS NULL 
       	THEN	SET EXIT_MESSAGE='missing value';
    		SET STATUS=-1;
    	ELSE	
    		INSERT INTO schema.tab(c1)
    		VALUES(Arg_SQL_Name)
    		;
    	   END IF;
       IF STATUS = -1 THEN
          SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT=EXIT_MESSAGE;
       END IF;
    END

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Verify shell startup files are identical betwen working and failing hosts,
    Try capturing stdout differently,
    And also check exit-code from db2 clp:


    ...
    ...
    db2cmd -i -w <<EOF
    db2 -o $logpath\log.log CONNECT TO database USER testuser USING topsecret
    if errorlevel 1 (@echo Failed to connect && @goto :EOF)
    db2 -o $logpath\log.log "Insert into schema.tab values (1)"
    if errorlevel 1 (@echo Failed to insert && @goto :EOF)
    EOF
    ...
    ...

  3. #3
    Join Date
    Sep 2008
    Posts
    9
    Thanks for your reply, db2mor!

    Unfortunately, I still couldn't get it to work.

    I could narrow it down to this example:
    When I open an interactive db2 environment, the insert is completed successfully:
    Code:
    db2cmd -i -w
    db2 "CONNECT TO DB USER user USING topsecret"
    db2 "insert into test.t_test values 123"
    exit
    However, when I try to run this in batch mode, the insert does not do anything:

    Code:
    db2cmd -i -w <<EOF 1>log.log 2>error.log
    db2 "CONNECT TO DB USER user USING topsecret"
    echo %errorlevel%
    db2 "insert into test.t_test values 999"
    echo %errorlevel%
    EOF
    The logfile does not show anything:
    When I open it with vi, it just contains the current path:
    ^LE:\sshprofiles\user>
    "log.log" [Incomplete last line] 1 line, 23 characters

    I'd really appreciate any suggestions!

  4. #4
    Join Date
    Sep 2008
    Posts
    9
    Now when I try this from the cygwin shell:
    Code:
    cmd /c "db2cmd -c -i -w db2 connect to mydb user me using topsecret"
    I can successfully connect to the DB.

    Then I tried
    Code:
    cmd /c "db2cmd -c -i -w db2 connect to mydb user me using topsecret && db2 insert into test.t_test values 123"
    It connects again, but then I get DB21061E Command Line Environment is not initialized

    So I tried
    Code:
    cmd /c "db2cmd -c -i -w db2clpsetcp db2 connect to mydb user me using topsecret && db2 insert into test.t_test values 123"
    or
    Code:
    cmd /c "db2cmd -c -i -w db2clpsetcp && db2 connect to mydb user me using topsecret && db2 insert into test.t_test values 123"
    But now not even the connect seems to go through. With the INSERT I immediately get the DB21061E. Without the INSERT, I'm back at the prompt instantly as if it didn't even try to connect...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •