0

Given a db2 proc call's output:

$ db2 "call SOME_PROC(103,5,0,'','',0,0)"

  Return Status = 0

I wish to just get the value and when I 'chain-em-up' it does not work as I think it should, so given:

$ db2 "call SOME_PROC(103,5,0,'','',0,0)" | sed -rn 's/ *Return Status *= *([0-9]+)/\1/p'
0

I try to chain 'em up:

$ var=$(db2 "call SOME_PROC(103,5,0,'','',0,0)" | sed -rn 's/ *Return Status *= *([0-9]+)/\1/p')
$ echo $var

You get nothin !

But if you redirect to tmp file:

$ db2 "call SOME_PROC(103,5,0,'','',0,0)" > /tmp/fff
$ var=$(cat /tmp/fff | sed -rn 's/ *Return Status *= *([0-9]+)/\1/p')
$ echo $var
0

You do get it ...

Similarly if you put in var:

$ var=$(db2 "call DB2INST1.USP_SPOTLIGHT_GET_DATA(103,5,0,'','',0,0)")
$ var=$(echo $var | sed -rn 's/ *Return Status *= *([0-9]+)/\1/p')
$ echo $var
0

You also get it ...

Is there a way to get value as my first attempt? And also I wonder why does it not work? What am I missing?

I also tried the below and it also givs nothing!

cat <(db2 -x "call DB2INST1.USP_SPOTLIGHT_GET_DATA(103,5,0,'','',0,0)" | sed -rn 's/ *Return Status *= *([0-9]+)/\1/p')
lzc
  • 919
  • 7
  • 16
  • 1
    If you want to do something complicated with db2, you are probably better off using a scripting language which has a db2 interface. Maybe http://stackoverflow.com/questions/6044326/how-to-connect-python-to-db2 will be helpful. (I've never used it, but I found it with an SO search.) – rici Apr 06 '16 at 16:29
  • 1
    I think the problem is that `$(db2 "call whatever..." | sed .. )` is launched in a new shell, where the database connection does not exist. You probably need to do something like `$(db2 connect ...>/dev/null; db2 call...)` – mustaccio Apr 06 '16 at 16:54
  • @mustaccio: To me if `cmd | cmd` gives output then `var=$(cmd | cmd)` should set that output to `$var`. Your explanation, I think, does not apply to my question – lzc Apr 06 '16 at 17:02
  • I don't know what is going on here, but it seems possible that the `sed` command is throwing away useful information. If you run `var=$(db2 "call SOME_PROC(103,5,0,'','',0,0)" | cat) ; declare -p var`, what output do you get? – pjh Apr 06 '16 at 17:48
  • 1
    I guess I didn't explain myself properly. I'll try again. `db2 call...` requires an established database connection, which is local to the shell instance. Once you stick it into `$(db2 call...)` you spawn a new shell, where the connection does not exist. You can see this yourself: create a shell function `function mybashpid { echo $BASHPID; }`, then run it as `mybashpid` and as `echo $(mybashpid)`, you will see that the latter runs in a new shell. If you do `echo $(db2 call ... 2>&1)` you will see that it prints an error instead of your procedure output. – mustaccio Apr 06 '16 at 18:34
  • Yep @mustaccio, now I understand it, I also add for extra measure `echo $BASH_SUBSHELL` – lzc Apr 06 '16 at 20:27

2 Answers2

2

The db2 command-line interface requires that the db2 command be issued as a direct child of the parent of the command which initiated the connection. In other words, the db2 call and db2 connect commands need to be initiated from the same shell process.

That does not interact well with many shell features:

  • pipelines: cmd1 | cmd2 runs both commands in subshells (different processes).

  • command substitution: $(cmd) runs the command in a subshell.

  • process substitution (bash): <(cmd) runs the command in a subshell.

However, if the shell is bash, the situation is not quite that restricted, so under some circumstances the above constructions will still work with db2. In pipelines and command substitution, bash will optimize away the subshell if the command to be run in the subshell is simple enough. (Roughly speaking, it must be a simple command without redirects.)

So, for example, if some bash process P executes

cmd1 | cmd2

then both commands have P as their parent, because they are both simple commands. Similarly with

a=$(cmd)

However, if a pipelined command or a substituted command is not simple, then a subshell is required. For example, even though { ...} does not require a subshell, the syntax is not a simple command. So in

{ cmd1; } | cmd2

the first command is a child of a subshell, while the second command is a child of the main shell.

In particular, in

a=$(cmd1 | cmd2)

bash will not optimize away the command-substitution subshell, because cmd1 | cmd2 is not a simple command. So it will create a subshell to execute the pipeline; that subshell will apply the optimization, so there will not be additional subshells to execute the simple commands cmd1 and cmd2.

In short, you can pipeline the output of db2 call or you can capture the output in a variable, but you cannot capture the output of a pipeline in a variable.

Other shells are more (or less) capable of subshell optimizations. With zsh, for example, you can use process substitution to avoid the subshell:

# This will work with zsh but not with bash
var=$(db2 "call ..." > >(sed -rn ...))

The syntax cmd1 > >(cmd2) is very similar to the pipeline cmd1 | cmd2, but it differs in that is syntactically a simple command. For zsh, that is sufficient to allow the elimination of the subshell (but not for bash, which won't optimize away a subshell if the command involves a redirection).

rici
  • 234,347
  • 28
  • 237
  • 341
  • Possibly, like how one segment of a pipeline runs in the current shell too. – Etan Reisner Apr 06 '16 at 16:40
  • @Etan: A more precise explanation of the bash optimization. Unfortunately, the `cmd1 > >(cmd2)` trick doesn't work because the redirect is enough to avoid the optimization. – rici Apr 06 '16 at 19:01
0

As @rici so briliantly explained it all, I just wanna show it live:

With cmd | cmd you get:

$ db2 "call SOME_PROC(103,5,0,'','',0,0)" | cat

  Return Status = 0

But with {cmd ;} | cmd you get:

$ { db2 "call SOME_PROC(103,5,0,'','',0,0)" ;} | cat
SQL1024N  A database connection does not exist.  SQLSTATE=08003
lzc
  • 919
  • 7
  • 16