I have written a shell script to calculate dbsize :-
db2 "call get_dbsize_info(?,?,?,-1)" | sed -n '8p' | awk -F : '{print $2}'
dbsize=`db2 "call get_dbsize_info(?,?,?,-1)" | sed -n '8p' | awk -F : '{print $2}'`
echo $dbsize
when I execute it the syntax works but it's not able to assign to a variable.
Can you please help me identify the mistake I am doing here . Why it's not working here when I try to assign the value to a variable.
Thank you
Not the slightest idea. Can you echo the result immediately < echo $(db2 ...) >?
Added one more line with echo :-
db2 "call get_dbsize_info(?,?,?,-1)" | sed -n '8p' | awk -F : '{print $2}'
echo $(db2 "call get_dbsize_info(?,?,?,-1)" | sed -n '8p' | awk -F : '{print $2}')
dbsize=`db2 "call get_dbsize_info(?,?,?,-1)" | sed -n '8p' | awk -F : '{print $2}'`
echo $dbsize
Output:-
And if you drop the first line?
Only the first line gives me the output and rest 2 doesn't.
---------- Post updated at 05:35 PM ---------- Previous update was at 05:09 PM ----------
looks to be an issue with sed -n
Script :-
echo $(db2 "call get_dbsize_info(?,?,?,-1)" )
echo $(db2 "call get_dbsize_info(?,?,?,-1)" | sed -n '8p')
+ db2 call get_dbsize_info(?,?,?,-1)
+ echo Value of output parameters -------------------------- Parameter Name : SNAPSHOTTIMESTAMP Parameter Value : 2013-11-03-17.31.13.182521 Parameter Name : DATABASESIZE Parameter Value : 21874778112 Parameter Name : DATABASECAPACITY Parameter Value : 115676934144 Return Status = 0
Value of output parameters -------------------------- Parameter Name : SNAPSHOTTIMESTAMP Parameter Value : 2013-11-03-17.31.13.182521 Parameter Name : DATABASESIZE Parameter Value : 21874778112 Parameter Name : DATABASECAPACITY Parameter Value : 115676934144 Return Status = 0
+ db2 call get_dbsize_info(?,?,?,-1)
+ sed -n 8p
+ echo
OK. Let us take a step backwards here.
Using echo with no quotes on it arguments is hiding any newlines, tabs, and any sequences of two or more spaces that may have been produced by the output of the db2 command. Then, since you converted all of the output from db2 into a single line with the echo, sed -n 8p will obviously not find a number to print on the eighth line of its single line of input.
But, even before we get to that problem, you also show that the output from db2 has spaces surrounding the colons in its output. So, even if the eighth line of output was something like:
Parameter Value : 21874778112
The output when printing the 2nd field of that colon separated value line would have a leading space (which you did not show us in any message in this thread until now). That leading space will keep the assignment to dbsize from working the way you want it to work.
Making several wild assumptions, the following change shown in red might work:
dbsize=`db2 "call get_dbsize_info(?,?,?,-1)" | sed -n '8p' | awk -F ': *' '{print $2}'`
or, preferably:
dbsize="$(db2 "call get_dbsize_info(?,?,?,-1)" | sed -n 8p | awk -F ': *' '{print $2}')"
or:
dbsize="$(db2 "call get_dbsize_info(?,?,?,-1)" | sed -n '8s/.*: *//p')"
If these don't work, please show us the exact output from the command:
db2 "call get_dbsize_info(?,?,?,-1)"
so we can actually see what is on the eighth line of its output.
Had no luck with the above syntax so Here's the db2 command output :-
db2 "call get_dbsize_info(?,?,?,-1)"
Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2013-11-03-19.00.50.590366
Parameter Name : DATABASESIZE
Parameter Value : 21874778112
Parameter Name : DATABASECAPACITY
Parameter Value : 115676934144
Return Status = 0
Is it possible that when get_dbsize_info is called twice in quick succession that the 2nd call doesn't output the same data?
You could try putting sleep 5 between the lines to give db2 a chance to "cleanup" from the previous call.
So, adding line numbers to your output:
1 db2 "call get_dbsize_info(?,?,?,-1)"
2
3 Value of output parameters
4 --------------------------
5 Parameter Name : SNAPSHOTTIMESTAMP
6 Parameter Value : 2013-11-03-19.00.50.590366
7
8 Parameter Name : DATABASESIZE
9 Parameter Value : 21874778112
10
11 Parameter Name : DATABASECAPACITY
12 Parameter Value : 115676934144
13
14 Return Status = 0
So, there is no way to get 21874778112 out of line 8 like you showed in your 1st message in this thread since you show here that it appears on line 9.
Did you just use the wrong line number?
Is the output from your db2 command always in this format, or does it vary depending on something you haven't shown us?
Would looking for the last string on the line after the line ends with "DATABASESIZE" be more reliable?
I don't think the db2 line is part of the output, but I agree with matching on the DATABASESIZE string for a more robust solution.
Perhaps:
db2 "call get_dbsize_info(?,?,?,-1)" | awk -F": " '/DATABASESIZE/ {print $3}' RS=
sleep 5
dbsize=`db2 "call get_dbsize_info(?,?,?,-1)" | awk -F": " '/DATABASESIZE/ {print $3}' RS=`
echo $dbsize
After checking found that combining database commands with unix commands it's not working .
db2 "call get_dbsize_info(?,?,?,-1)" | awk -F": " '/DATABASESIZE/ {print $3}' RS=
db2 "call get_dbsize_info(?,?,?,-1)" | sed -n 8p
dbinfo=`db2 "call get_dbsize_info(?,?,?,-1) `
echo $dbinfo
dbsize=`db2 "call get_dbsize_info(?,?,?,-1)" | sed -n 1p`
echo $dbsize
Output :-
+ awk -F: /DATABASESIZE/ {print $3} RS=
+ db2 call get_dbsize_info(?,?,?,-1)
21920915456
+ db2 call get_dbsize_info(?,?,?,-1)
+ sed -n 8p
Parameter Value : 21920915456
+ + db2 call get_dbsize_info(?,?,?,-1)
dbinfo=
Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2013-11-04-14.10.19.348220
Parameter Name : DATABASESIZE
Parameter Value : 21920915456
Parameter Name : DATABASECAPACITY
Parameter Value : 115676815360
Return Status = 0
+ echo Value of output parameters -------------------------- Parameter Name : SNAPSHOTTIMESTAMP Parameter Value : 2013-11-04-14.10.19.348220 Parameter Name : DATABASESIZE Parameter Value : 21920915456 Parameter Name : DATABASECAPACITY Parameter Value : 115676815360 Return Status = 0
Value of output parameters -------------------------- Parameter Name : SNAPSHOTTIMESTAMP Parameter Value : 2013-11-04-14.10.19.348220 Parameter Name : DATABASESIZE Parameter Value : 21920915456 Parameter Name : DATABASECAPACITY Parameter Value : 115676815360 Return Status = 0
+ + db2 call get_dbsize_info(?,?,?,-1)
+ sed -n 1p
dbsize=SQL1024N A database connection does not exist. SQLSTATE=08003
+ echo SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
Is there an alternate way for it ?
dbsize=`echo "$dbinfo" | sed -n 8p`
As Don already said, without quotes the echo will strip all the whitespace - so you just need to quote it.
EDIT: Also, while I have no experience of DB2, if the missing double-quote on
dbinfo=`db2 "call get_dbsize_info(?,?,?,-1) `
isn't just a paste typo or giving you a syntax error, is it leaving the DB2 call open?