Awk appears to disconnect my DB2 session when piping

Question

Hello.

I'm attempting to run the following command in Korn Shell (ksh):

set -A INDEXES `db2 "describe indexes for table ${TABSCHEMA}.${TABNAME} show detail" | awk '{print $1"."$2}'`

What I'm attempting to achieve is place a list of the indexes over a particular table into an array which I can later iterate through.

The problem is, when I run the above command the contents of the array starts with the error message of 'SQL1024N' (which is telling me that the database connection does not exist).

However, if I remove the 'awk' at the end of the statement as so:

set -A INDEXES `db2 "describe indexes for table ${TABSCHEMA}.${TABNAME} show detail"`

it works just fine (well, to the extent its returning data. Obviously without the awk I'm not capturing the correct data).

Does anyone know why the awk is having this affect?

I appreciate there is more than one way to get this data, but it baffles me as to why this is happening.

Thanks in advance.

Greg.

Answer 1

I doubt it's awk per se. Maybe db2 is particular about stdout being connected to a tty or console? Or at least doesn't like when it's connected to a pipe.

Answer 2

Hi,

This is an unusual one, and as Pez says it is probably a db2 quirk.

I have seen similar issues when, e.g. using time or timex prior to a db2 command string, where db2 does not have a defined database to connect to.

There is an environment variable DB2DBDFT which sets a default database for implicit connections. I am sorry to say that I'm not sure if this variable is available to non-DBA users (presuming you're an app developer). It is worth investigating if setting this variable via:

db2set DB2DBDFT=${your_db_name}

And retrying your query.

If you have multiple databases your environment could connect to you can unset the DB2DBDFT variable once you have completed your work.

Answer 3

Try the db2 command with the -x switch:

db2 **-x** "describe indexes for table ${TABSCHEMA}.${TABNAME} show detail"....

I've had instances where this has cured my inability to pipe output to awk.

Answer 4

In this case, when the DB2 CLP says that it's not connected to the database, it's because the shell has opened up a sub-process that requires its own dedicated db2bp backend process, which cannot access the connection opened by the original shell process. It's not that something is becoming disconnected, it's that a newly created shell process (and its accompanying db2bp process) are being created but aren't being told to connect to a database. One way to remedy this is to explicitly connect (or re-connect) to the database when you know you're in one of those situations.

set -A INDEXES `db2 connect to watevrDB >/dev/null;db2 -x describe indexes for table ${TABSCHEMA}.${TABNAME} show detail | awk {'print $1"."$2'}`

I realize that this question is more about scripting and awk with DB2 than about the system catalog, or else I would have recommended some straightforward catalog queries to produce the same result.