Getting db2 output into a shell variable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian Tkatch - 29 Nov 2006 15:06 GMT

This is a shell question (bash), but i'm more familiar with this group,
so i hope noone minds.

I have a bash shell script which needs to query the database and return
information into a variable. This can be done with a subshell, but that
slows everything down because it is within a loop (and would be done
mutiple times) and it would need a new login each time. As such, i got
it to work via a temp file something like:

db2 -xt +p << EOF > temp
SELECT
'MODIFIED BY'

|| (CASE WHEN MAX(Identity) = 'Y' THEN ' identityoverride' ELSE ''

END)

|| (CASE WHEN COUNT(Text) > 0 THEN ' generatedoverride' ELSE ''

END)
FROM
SysCat.Columns
WHERE
TabSchema = '$Schema'
AND TabName = '$Table'
AND Generated = 'A'
GROUP BY
Tabname;
EOF

read Overrides < temp

Is there an easier way to just put the data into a variable?

FWIW, the script is to run LOAD on the development database and that
loop provides any overrides (identityoverride, generatedoverride) for
the MODIFIED BY clause automatically.

B.

Reply to this Message

db2admin - 29 Nov 2006 15:39 GMT

On Nov 29, 10:06 am, "Brian Tkatch" <Maxwell_Sm...@ThePentagon.com>
wrote:

> This is a shell question (bash), but i'm more familiar with this group,
> so i hope noone minds.
[quoted text clipped - 31 lines]
>
> B.

may be something like

db2 -xt +p << EOF
SELECT
'MODIFIED BY'
|| (CASE WHEN MAX(Identity) = 'Y' THEN ' identityoverride' ELSE ''
END)
|| (CASE WHEN COUNT(Text) > 0 THEN ' generatedoverride' ELSE ''
END)
FROM
SysCat.Columns
WHERE
TabSchema = '$Schema'
AND TabName = '$Table'
AND Generated = 'A'
GROUP BY
Tabname;
EOF | read Overrides
echo ${Overrides}

Reply to this Message

Brian Tkatch - 29 Nov 2006 16:51 GMT

> On Nov 29, 10:06 am, "Brian Tkatch" <Maxwell_Sm...@ThePentagon.com>
> wrote:
[quoted text clipped - 53 lines]
> EOF | read Overrides
> echo ${Overrides}

That does not work because the EOF must be alone. Moving it up top: db2
-xt +p << EOF | read Overrides

does not work (it just produces blank lines)

B.

Reply to this Message

Lennart - 29 Nov 2006 20:19 GMT

> This is a shell question (bash), but i'm more familiar with this group,
> so i hope noone minds.
[quoted text clipped - 31 lines]
>
> B.

This should work (I changed the predicate Generated = 'A' since I dont
have such a table):

[db2inst1@wb-01 ~]$ cat aa.sh
#!/bin/sh

db2 connect to base2k > /dev/null 2>&1

Schema=NYA
Table=DIPLOMA_UPSEC

output=`db2 "SELECT \
'MODIFIED BY' \

|| (CASE WHEN MAX(Identity) = 'Y' THEN ' identityoverride' ELSE '' \

END) \

|| (CASE WHEN COUNT(Text) > 0 THEN ' generatedoverride' ELSE '' \

END) \
FROM \
SysCat.Columns \
WHERE \
TabSchema = '$Schema' \
AND TabName = '$Table' \
AND Generated = 'D' \
GROUP BY \
Tabname"`
status=$?

echo "Staus=$status, output=$output"

[db2inst1@wb-01 ~]$ ./aa.sh
Staus=0, output=
1
----------------------------------------------
MODIFIED BY identityoverride

1 record(s) selected.

HTH
/Lennart

Reply to this Message

Brian Tkatch - 30 Nov 2006 15:43 GMT

> > This is a shell question (bash), but i'm more familiar with this group,
> > so i hope noone minds.
[quoted text clipped - 71 lines]
> HTH
> /Lennart

I just tried your script (changing only the schema and table name). It
does not work for me, because it starts a subshell which does not use
the current connection.

bash-2.05$ ./load
Staus=4, output=SQL1024N A database connection does not exist.
SQLSTATE=08003

B.

Reply to this Message

Lennart - 30 Nov 2006 16:00 GMT

> I just tried your script (changing only the schema and table name). It
> does not work for me, because it starts a subshell which does not use
[quoted text clipped - 3 lines]
> Staus=4, output=SQL1024N A database connection does not exist.
> SQLSTATE=08003

Strange, do you get a connection at all? What does the connect say if
you remove "> /dev/null ..."?

/Lennart

Reply to this Message

Brian Tkatch - 30 Nov 2006 16:39 GMT

> > I just tried your script (changing only the schema and table name). It
> > does not work for me, because it starts a subshell which does not use
[quoted text clipped - 8 lines]
>
> /Lennart

> Strange, do you get a connection at all?

I do get a connection, because i tried the subshell originally, and
only statements outside the subshell worked.

> What does the connect say if you remove "> /dev/null ..."?

First it said: SQL1013N The database found. SQLSTATE=42705

Heh, silly me. :)

Then it gave me:

bash-2.05$ ./load

Database Connection Information

Database server = DB2/SUN 8.1.6
SQL authorization ID = <userid>
Local database alias = <database>

Staus=1, output=
1
----------------------------------------------

0 record(s) selected.
bash-2.05$

Of course that's because i forgot to change the D back to an A.

So indeed, it does work. I'm trying to make sense of this now.

B.

Reply to this Message

Brian Tkatch - 30 Nov 2006 18:05 GMT

> > > I just tried your script (changing only the schema and table name). It
> > > does not work for me, because it starts a subshell which does not use
[quoted text clipped - 42 lines]
>
> B.

OK, got it working. Apparently my subshell issue came from not
double-quoting the entire statement, which made the inner parentheses
(CASE, MAX, COUNT) cause issues with other subshells.

Anyway, this works:

Overrides=$($DB2 -xt +p                                \
"SELECT                                         \
    'MODIFIED BY'                                    \
    || (CASE WHEN MAX(Identity) = 'Y' THEN ' identityoverride' ELSE ''
END)    \
    || (CASE WHEN COUNT(Text) > 0 THEN ' generatedoverride' ELSE ''
END)    \
FROM                                            \
    SysCat.Columns                                    \
WHERE                                            \
    TabSchema = '$Schema'                                \
AND    TabName    = '$Table'                                \
AND    Generated = 'A'                                    \
GROUP BY                                        \
    Tabname;")

Thanx for the help. One less file to deal with. :)

B.

Reply to this Message