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

    Unanswered: db2 and piping the output - strange behaviour

    Hello there,
    I observed IMHO a strange behavior:
    Let's say there is these db2-commands (running on AIX):

    db2 get instance # to make sure I'm on the right instance
    db2 connect to db1
    db2 -x "select c1, c2 from t1" | grep *do something here*
    (or another command after the pipe)

    Then the correct result is printed on the screen.

    However when I assign the result to a variable, e.g.
    result=$(db2 -x "select c1, c2 from t1" | grep *do something here*)
    I get different results.
    It turns out the instance must have been changed in the background because the results are from table t1 in another instance.

    This must have something to do with the environment variables but I still don't quite get it.

    Why do I get the correct result when it's not assigned to a variable?
    result=$(...) minus the pipe works fine, too.
    This could lead to "funny" results...

    Thanks for any suggestions.

  2. #2
    Join Date
    Jun 2011
    Posts
    9
    you have to do something like this
    result=`db2 -x "select c1, c2 from t1" | grep *do something here*`
    echo $result

  3. #3
    Join Date
    Sep 2008
    Posts
    9
    Thanks will give it a try.
    I always thought x=$(...) and x=`...`is the same?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    from another instance? that is a bit worrying

    Code:
    db2set -all
    Do you have DB2DBDFT set, and is this the database from which you are getting the results?

    Check if your shell is impacted by

    IBM IV20893: KSH93 FORKS EXTRA PROCESS WHEN COMMAND HAPPENS INSIDE A FUNCTION - United States
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Sep 2008
    Posts
    9
    Thanks for all your suggestions!
    Argh, I posted this when I was at home and got it wrong.
    The instance is not changed, both DBs are on the same instance.
    However, DB2DBDFT is set and contains the DB that is mysteriously used in the query with pipe and the shell-variable. So I'd assume it might have something to do with it. I wasn't aware of DB2DBDFT yet and will investigate.
    Is it possible to use something like "set -x" to see what's happening in the background?

    `...` instead of $(...) doesn't help...

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by sk-online View Post
    Thanks for all your suggestions!
    Argh, I posted this when I was at home and got it wrong.
    The instance is not changed, both DBs are on the same instance.
    However, DB2DBDFT is set and contains the DB that is mysteriously used in the query with pipe and the shell-variable. So I'd assume it might have something to do with it. I wasn't aware of DB2DBDFT yet and will investigate.
    Is it possible to use something like "set -x" to see what's happening in the background?

    `...` instead of $(...) doesn't help...
    I don't think so.... Have you checked the link with the bug??? Based on your explanation, it is quiet possible that you are hitting the ksh93 bug.

    If you set DB2DBDFT to a null value(ie , no default database), then you will get "Connection does not exist". As you have DB2DBDFT set, when the shell sees the connection does not exist, it creates a connection to the default database.

    I don't think set -x will help - as the connection happens internally within the db2 client code.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Sep 2008
    Posts
    9
    Thanks for your relpy!
    It seems we are running AIX 6.1.
    Per default, we use ksh, but I switched to bash and ran into the same issue (with $(...) and `...`).
    I'll ask the DBA to clear the DB2DBDFT variable on our test machine!

Posting Permissions

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