470,641 Members | 1,575 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,641 developers. It's quick & easy.

Getting db2 output into a shell variable

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.

Nov 29 '06 #1
7 11483


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.

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.

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}

Nov 29 '06 #2

db2admin wrote:
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.

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.


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}
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.

Nov 29 '06 #3

Brian Tkatch wrote:
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.
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

Nov 29 '06 #4

Lennart wrote:
Brian Tkatch wrote:
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.

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
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.

Nov 30 '06 #5

Brian Tkatch wrote:
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
Strange, do you get a connection at all? What does the connect say if
you remove "/dev/null ..."?
/Lennart

Nov 30 '06 #6

Lennart wrote:
Brian Tkatch wrote:
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

Strange, do you get a connection at all? What does the connect say if
you remove "/dev/null ..."?
/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.

Nov 30 '06 #7
Brian Tkatch wrote:
Lennart wrote:
Brian Tkatch wrote:
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
>
Strange, do you get a connection at all? What does the connect say if
you remove "/dev/null ..."?
/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.
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.

Nov 30 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by biner | last post: by
5 posts views Thread by Christophe HELFER | last post: by
9 posts views Thread by Clodoaldo Pinto Neto | last post: by
1 post views Thread by Rafael Fernandez | last post: by
1 post views Thread by Anthony Irwin | last post: by
3 posts views Thread by eeriehunk | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.