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 ' identityoverrid e' ELSE ''
END)
|| (CASE WHEN COUNT(Text) 0 THEN ' generatedoverri de' 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 (identityoverri de, generatedoverri de) for
the MODIFIED BY clause automatically.
B.
7  12020 
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 ' identityoverrid e' ELSE ''
END)
|| (CASE WHEN COUNT(Text) 0 THEN ' generatedoverri de' 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 (identityoverri de, generatedoverri de) for
the MODIFIED BY clause automatically.
B.
may be something like
db2 -xt +p << EOF
SELECT
'MODIFIED BY'
|| (CASE WHEN MAX(Identity) = 'Y' THEN ' identityoverrid e' ELSE ''
END)
|| (CASE WHEN COUNT(Text) 0 THEN ' generatedoverri de' ELSE ''
END)
FROM
SysCat.Columns
WHERE
TabSchema = '$Schema'
AND TabName = '$Table'
AND Generated = 'A'
GROUP BY
Tabname;
EOF | read Overrides
echo ${Overrides}
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 ' identityoverrid e' ELSE ''
END)
|| (CASE WHEN COUNT(Text) 0 THEN ' generatedoverri de' 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 (identityoverri de, generatedoverri de) for
the MODIFIED BY clause automatically.
B.
may be something like
db2 -xt +p << EOF
SELECT
'MODIFIED BY'
|| (CASE WHEN MAX(Identity) = 'Y' THEN ' identityoverrid e' ELSE ''
END)
|| (CASE WHEN COUNT(Text) 0 THEN ' generatedoverri de' 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.
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 ' identityoverrid e' ELSE ''
END)
|| (CASE WHEN COUNT(Text) 0 THEN ' generatedoverri de' 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 (identityoverri de, generatedoverri de) 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_U PSEC
output=`db2 "SELECT \
'MODIFIED BY' \
|| (CASE WHEN MAX(Identity) = 'Y' THEN ' identityoverrid e' ELSE '' \
END) \
|| (CASE WHEN COUNT(Text) 0 THEN ' generatedoverri de' ELSE '' \
END) \
FROM \
SysCat.Columns \
WHERE \
TabSchema = '$Schema' \
AND TabName = '$Table' \
AND Generated = 'D' \
GROUP BY \
Tabname"`
status=$?
echo "Staus=$sta tus, output=$output"
[db2inst1@wb-01 ~]$ ./aa.sh
Staus=0, output=
1
----------------------------------------------
MODIFIED BY identityoverrid e
1 record(s) selected.
HTH
/Lennart
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 ' identityoverrid e' ELSE ''
END)
|| (CASE WHEN COUNT(Text) 0 THEN ' generatedoverri de' 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 (identityoverri de, generatedoverri de) 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_U PSEC
output=`db2 "SELECT \
'MODIFIED BY' \
|| (CASE WHEN MAX(Identity) = 'Y' THEN ' identityoverrid e' ELSE '' \
END) \
|| (CASE WHEN COUNT(Text) 0 THEN ' generatedoverri de' ELSE '' \
END) \
FROM \
SysCat.Columns \
WHERE \
TabSchema = '$Schema' \
AND TabName = '$Table' \
AND Generated = 'D' \
GROUP BY \
Tabname"`
status=$?
echo "Staus=$sta tus, output=$output"
[db2inst1@wb-01 ~]$ ./aa.sh
Staus=0, output=
1
----------------------------------------------
MODIFIED BY identityoverrid e
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.
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
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.
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=$($DB 2 -xt +p \
"SELECT \
'MODIFIED BY' \
|| (CASE WHEN MAX(Identity) = 'Y' THEN ' identityoverrid e' ELSE ''
END) \
|| (CASE WHEN COUNT(Text) 0 THEN ' generatedoverri de' 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: biner |
last post by:
Hello,
I have file called PARAMETRES that is used in bourne shell script to
define variable. In order to do so I put a ". PARAMETRES" line and the
script has acces to all the variable defined in the PARAMETRES file.
Now, I would like to be able to get the same thing in python. I
googled and played with os.system to try to come up with something but
no success so far. I also tryed exec but it doesn't work becaus a lot
of string...
|
by: Glen |
last post by:
Hi,
I just have a quick question
Im trying to initialize a string (or char) variable with the information
created by the output of a shell (or DOS) command..
Example:
I want to execute a command such as
system("cat /test.txt | grep something ") or whatever DOS or linux command
and assign the output of that command to the variable str;
|
by: dixie |
last post by:
I have been running some code from a form's on open event to run regedit if
a registry key does not exist in the registry. It has been working fine,
but I have had the path to the registry fix file hard coded into the VBA
like this.
RunReg = Shell("regedit.exe /s F:\MyFolder\SQLfix.reg", 0)
Now I wish to make this more generic and allow it to run from any path.
I have tried to use CurrentProjectPath like this.
|
by: Christophe HELFER |
last post by:
Hi,
I would like to do this in VB Languague.
I have an executable file in DOS mode. This executable returns informations
and display them to the DOS window. Unfortunately, I have to process theses
informations.
The way to do that, is to redirect standard input and output, insn't it ?
This is possible in C++ language, but I cannot make any line in this
language.
|
by: Clodoaldo Pinto Neto |
last post by:
Output from the shell:
$ set | grep IFS
IFS=$' \t\n'
Output from subprocess.Popen():
"IFS=' \t\n"
Both outputs for comparison:
| | |
by: Rafael Fernandez |
last post by:
Hi folks,
I have the following sql/pl:
CREATE PROCEDURE DBSTG.TEST
(OUT PRESULT INTEGER
)
SPECIFIC DBSTG.TEST
DYNAMIC RESULT SETS 1
LANGUAGE SQL
|
by: Anthony Irwin |
last post by:
Hi All,
I would like to run the command below and have each line from the
output stored as an element in a list.
find /some/path/ -maxdepth 1 -type f -size +100000k -exec ls -1 '{}' \
The reason for this is so I can then work on each file in the
following manner
|
by: eeriehunk |
last post by:
Hi,
I wrote a shell script where I connect to an oracle db(the connection works because I able to see the DBMS output) and I execute a procedure, but I am not able to get the return value from that procedure into one of the shell variables (i understand that procedure do not return values but i am using IN OUT and i want this out value to go into a variable declared in the shell). I made sure the procedure works in sqlplus and the OUT value...
|
by: jtertin |
last post by:
My first inclination would be that this is not possible, but wanted to ask anyway (in case the Shell() call is bi-directional or can return output):
If I use VBA to execute a call to the command shell via a Shell() call, is there any way I can grab the output of that call (to store in a string variable and, thus, a database entry)?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
