UNIX Scripting (KSH) and Oracle

[pareshan]

Hi Guys,
I haven't worked on oracle much but I have a situation where I have to do df in all the servers and insert that information into oracle table. I have already created table which have 7 columns, I can insert manually but I dont know how to insert that using Korn shell.

SERVER_ID NOT NULL NUMBER(6)
FS_LOCAL_NAME NOT NULL VARCHAR2(200)
LOCAL_MOUNT NOT NULL CHAR(1)
TOTAL_SPACE NOT NULL NUMBER(10)
USED_SPACE NUMBER(10)
SPACE_AVAILABLE NUMBER(10)
PERCENTAGE_USED NUMBER(3,2)

This is just a example I have done manually and its inserting data

#!/bin/ksh
sqlplus pareshan/ac6e94aac5b1b979902c0b0b1f42621761c@let01a<insert into unix_servers values (1,'superior','Y',89885485,858757,4657575,2);
EXIT
EOF
But the thing I need is I have to run df command and the outuput I have to insert into respective column in the oracle table.

Plz Help,
Thanks In Advance

[Hein van den Heuvel]

There are a lot of ways to do this, with varying degrees of efficiency and complexity.

The simplest, and most appealing approach for SA's is probably by parsing df output and generating insert statements.

Here is an example of just that, using PERL as a driver. This works on my laptop in c cygwin session against a local Oracle XE database:

open SQL, "| sqlplus hein/password";
foreach (qx(df)) {
my ( $fs, $total, $user, $avail, $pct, $mnt ) = split;
next if /^File/ ; # header line
$pct =~ s/%//; # don't need no percent sign.
print SQL q(insert into unix_servers values ) .
qq( \( 1, '$fs', 'Y', $total, $user, $avail, $pct\);\n);
}
print SQL qq(commit;\n);
close SQL;

Input:

$ df
Filesystem 1K-blocks Used Available Use% Mounted on
C:\cygwin\bin 58605088 49106180 9498908 84% /usr/bin
C:\cygwin\lib 58605088 49106180 9498908 84% /usr/lib
C:\cygwin 58605088 49106180 9498908 84% /
c: 58605088 49106180 9498908 84% /cygdrive/c

Output:

SQL> select * from unix_servers;

SERVER_ID FS_LOCAL_NAME L TOTAL_SPACE USED_SPACE SPACE_AVAILABLE PERCENTAGE_USED
---------- -------------------- - ----------- ---------- --------------- ---------------
1 C:\cygwin\bin Y 58605088 49106180 9498908 84
1 C:\cygwin\lib Y 58605088 49106180 9498908 84
1 C:\cygwin Y 58605088 49106180 9498908 84
1 c: Y 58605088 49106180 9498908 84

This is just a sample. A lot of details need to be addressed of course. You probably want a DATE column there. And you probably do not want echoes. And I change the percentage column to a simple number(5).

For Oracle it would be nicer to use BIND VARIABLES, but hey, for once or twice a day the above will work fine this will work fine.

If I had to do this for real I might also look at 'EXTERNAL TABLES' to solve this.
This is where you define a table on a flat file, with separators and line terminators of your choice. Place it from teh servers, and uses with with basic oracle commands.

hth,
Hein van den Heuvel
HvdH Performance Consulting

[pareshan]

Thanks alot, I know this works but I am trying to do that in Hp-Unix using Korn Sheel not perl.

I am thinking about using sql loader but I am having problem. Any idea?
If i use sql loader in the control file can i use the table which is already created ? if can then how coz I have error here

this is controlfile
control_file

LOAD DATA
INFILE '/home/gc1488/FST/input'
INTO TABLE unix_servers
FIELDS TERMINATED BY ','
(SERVER_ID,
SERVER_NAME,
MARKET,
SERVER_TYPE,
PROD_IND,
OWNER)

already have input file in that location. One thing the table im going to use should already exist or doesnt matter because I have to insert into the table which is already there. so I am using unix_servers which is already there but I have an error here.

SQL*Loader-601: For INSERT option, table must be empty. Error on table UNIX_SERVERS

Is that mean I cannot use the existing table? because the table is empty and still it says table must be empty.
thank you very much

[pareshan]

Note: sorry I used different table in 2nd one but table structure really doesnt matter i guess. main thing is it should work

[Hein van den Heuvel]

Just specify APPEND in the control file.
From the (9.2) Oracle Utilities Ref Man:

SQL*Loader Control File Reference 5-33

APPEND If data already exists in the table, SQL*Loader appends the new rows to it.
data does not already exist, the new rows are simply loaded. You must have
SELECT privilege to use the APPEND option. Case Study 3: Loading a Delimited,
Free-Format File on page 10-11 provides an example.


On the perl versus Ksh.... IMHO... if you do not know how to map that perl script onto KSH actions, then you have no business using KSH as an implementation language. Just an opinion.

Cheers!
Hein.

[pareshan]

Thank you very much, I really appreciate and you are right I can do using that and I have done also but here is the twist.

my bdf command gives me a bunch of output I think if I print it here it will be a long list but I am sure you are aware of that. It includes local file sytem + all the mounted file systems and of course I cant put that in one row of a table so I need to calcuate that using script and find out ( total, used, free and percentage used) and only that information I can enter in the table.
I tried to write korn shell script for that but Its not that easy but I am still trying.

Anybody have some script already or have some idea about it.

I hope I made it clear this time. if any queries please let me know. I will appreciate your help
thaks alot

[Hein van den Heuvel]

In my opinion, and it is just an opinion, the parsing of BDF output and doing some basic calculation is much easier on in AWK or PERL than shell code.
In my earlier perl example it would just be a few lines early in the df data loop, making it accumulate variables as it recognizes them and calling for 'next' until all values for a sql insert are available.

While a full BDF output may be too much and show details you do not care to share, some reader migt be able to help you better if you provided say 2 or 3 example sections and the 2 or 3 rows of data from there that you woud want to push into Oracle. Yeah I know, it sounds like we should be able to figure out what you want, but why not make it as easy as possible for folks who are willing to help.

If you chose to show an example, then please provide an attachment with a verbatim .TXT file in addition to a simple cut & paste to make sure we see spaces and line wraps as they really are.

cheers,
Hein.

[Bill Hassell]

> my bdf command gives me a bunch of output I think if I print it here it will be a long list but I am sure you are aware of that. It includes local file sytem + all the mounted file systems and of course I cant put that in one row of a table so I need to calcuate that using script and find out ( total, used, free and percentage used) and only that information I can enter in the table.

Well, the first thing to do is to drop the mounted filesystems. Use: bdf -l

However, if you need properly formatted bdf listing, use the attached bdfmegs script. It will never split a line and has a script-friendly -q to drop the header. You can also run it with -V VGname to select only filesystems from a specific volume group. It is very useful to find almost full filesystems -- use -P with a percentage like 95 or 98 to see only those filesystems. And the units of measure are megabytes, not Kbytes. You can also specify -g to see filesystems in gigabytes.

[pareshan]

Hi Hien and everyone I have attached my bdf Output, as you can see there its not in good format, some have more space some have less and even some are in different lines I tried alot to make it right but im not able to. I just want to separate the values by single space so that I can populate that to database table easily,

Another thing I found one old thread which is about more or less same and so many replied and sent the code which is attached but I am not able to view that attach file so anyone who have that code could you help me plz. this is the thread im talking about.

http://forums13.itrc.hp.com/service/forums/questionanswer.do?admit=109447627+1234794464067+28353475&threadId=732203

Steven E, protter, Sanjay and Sridhar Bhaskarla, If anyone of you are reading this please can you help me coz you guys replied me in that thread and gave solutions which I think will help me also.


And Bill Hassell, I tried to use the code you have given but I dont know why its not working and its too long so I am not able to understand properly also, do you have any short version plz.

Note: my problem is just i want to format the bdf output with the values separated by space and put that into text file so that i can fed that into oracle table. I use perl also to format but its not working for bdf properly and same script works for every other file which have random spaces or format.

plz help guys
thanks in advance

[Hein van den Heuvel]

That's the standard df line-wrap problem which has been solved many times with many tools.

Here is something I had floating around for that purpose:


#!/usr/bin/perl
use strict;
use warnings;
my $header = <>; # Eat header line
while (<>) {
if (!/% /) { # glue on next line, if no percentage on this line
chomp; # clean of the new-line
$_ .= <>; # add wrapped part
}
next if /^\w+:/; #skip NFS mount with ":" in first word
chomp;
s/% //; # strip percentage sign
s/\s+/ /g; # replace all tabs and spaces with just 1 space
my ($fs, $kbytes, $used, $avail, $pct_used, $mount) = split; # now have each column in a variable
# do anything you like with the columns (like preparing an oracle insert)
print $_, "\n" ; # just to prove new glued line
}

Hope that helps some,
Hein.

[Hein van den Heuvel]

>> s/% //; # strip percentage sign

There should be a space in the replace part:

s/% / /; # strip percentage sign

Hein.

[pareshan]

Hein, In that script from where I am supposed to give bdf command or output?

I tried to give from command line from file, nothign really working, could you tell me how to do that plz,

[Hein van den Heuvel]

Assume you pasted that script into a file called: bdf_format
The intend was to activate it with: bdf | bdf_format

You could also use : ./bdf_format

Now if you want to issue the BDF command in the script, which IMHO is a fine idea, then it needs a few modifications. For example as posted below my signature. That script is run 'stand-alone'. No pipe to feed.

Hein.


#!/usr/bin/perl
use strict;
use warnings;
my ($filesystem) ;

foreach (qx(bdf)) { # executing command here!

next if /^Filesystem/; # skip header line
if (!/% /) { # remember for next line if no percentage on this line
chomp; # clean of the new-line
$filesystem = $_; # remember
next;
}
$_ = $filesystem . $_ if /^\s/; # add filesystem for wrapped lines
next if /^\w+:/; #skip NFS mount with ":" in first word
chomp;
s/% / /; # strip percentage sign
s/\s+/ /g; # replace all tabs and spaces with just 1 space
my ($fs, $kbytes, $used, $avail, $pct_used, $mount) = split; # now have each column in a variable
# do anything you like with the columns (like preparing an oracle insert)
print $_, "\n" ; # just to prove new glued line
}

[Bill Hassell]

The file attached above (bdfmegs) is a complete script. You save the attachment as a file on your PC, then transfer it to your HP-UX system using ftp with the ASCII option. Once it is stored on your HP-UX system, set the file executable (chmod 755 bdfmegs) and you simply run it from your script.

However, if you want verything to be in your script, here are the lines you would add to obtain each of the values with no extra line feeds:

bdf | while read FS SZ USED AV PCT MNT
do
[ -z "$SZ" ] && read SZ USED AV PCT MNT
#
# all the values are now present:
# $FS $SZ $USED $AV $PCT $MNT
# call sqlplus to add these values to your database
done

You may want to take a class in shell scripting to help with these tasks.

[pareshan]

Bill, thanks alot and I know I need to brush on my korn shell because I have just been working on this field recently and Im trying hard. Thanks for the suggestion though and all the helps I really appreciate that.

Here I am not looking for much Just I wanted to remove those spaces which I have put in my text file. or make into one line if the output is in two lines.

for example
/dev/vg3psw/lvol9 5120000 1286387 3594077 26% /usr/local/g1_3.2_SE
/dev/vghome_old/lvol1
23552000 10375127 12353732 46% /usr/local/g1/dev/vgapp02/lvol1 153616384 100802161 49543296 67% /tuxappl
/dev/vg00/lvol6 1540096 44424 1488456 3% /tmp
/dev/vgapp-io/lvol1
199155712 153617264 45361864 77% /tlgvar
/dev/vgapp/lvol3 2097152 24344 2056680 1% /opcode
/dev/vgapp/lvol9 25624576 23454180 2138564 92% /mps
/dev/vghome_old/lvol5
5144576 185360 4649319 4% /lsms_tool
/dev/vgapp-io/lvol2
2097152 5208 2075800 0% /logs
/dev/vgdbcommon/lvol1
18432000 1517936 16786512 8% /logs/ORACLE
/dev/vghome/lvol1 62898176 54860264 8037912 87% /home
/dev/vgapp/lvol8 524288 290587 219125 57% /emc
/dev/vgd2bl12d_2/lvol3
20512768 6129 19224982 0% /csmtier1/logs
/dev/vgapp/lvol10 1048576 6334 977155 1% /csmscripts
/dev/vghome_old/lvol6
51707904 3864 51300112 0% /ora_D2BL12B_4

/dev/vg27/lvol3 204865536 197661672 7147648 97% /ora_D2BL42A_3
/dev/vg27/lvol2 307232768 26120 304806672 0% /ora_D2BL42B_2
/dev/vg27/lvol1 307232768 287320072 19757192 94% /ora_D2BL43A_2
/dev/vg27/lvol4 20578304 9825080 10669224 48% /ora_fbf_1



I am not really looking for those big scripts because I cant handle those kind of scripts right now. Just like in that example above if the output is in two lines then i want to shift that in one line rest removing spaces and all i can do it using perl. Just you can see some of them are in one line and some of then are in two lines just i need help with those whose result came in two lines. something like if the value is in two lines then shift it to one line and im not able to implement it thats my problem. can be in ksh or perl i dont care

thanks alot
help plz

[James R. Ferguson]

HI:

Bill gave you the solution to your last question if you will only _try_ it and _read_ his post!

You would do well to at least try and examine Bill's script. You will learn at great deal about good shell scripting and good shell scripting techniques from it.

...JRF...

[pareshan]

I got this error while running bills script


File-System Mbytes Used Avail %Used Mounted on
format_bdf[391]: Setup: not found
/dev/vg00/lvol4 1024 306 711 30% /
format_bdf[391]: Setup: not found
/dev/vg00/lvol1 990 63 828 7% /stand
format_bdf[391]: Setup: not found
/dev/vg00/lvol8 4096 1432 2644 35% /var
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol12 3072 1192 1761 40% /var/mqm
format_bdf[391]: Setup: not found
/dev/vg00/lvol10 4096 3661 431 89% /var/adm/sw
format_bdf[391]: Setup: not found
/dev/vg00/lvol9 4096 16 4047 0% /var/adm/crash
format_bdf[391]: Setup: not found
/dev/vg00/lvol7 5120 1827 3266 36% /usr
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol1 4096 3792 300 93% /usr/local
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol7 9.9g 8115 1856 81% /usr/local/vertex
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol2 12.1g 7891 4513 64% /usr/local/oracle
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol9 5000 1256 3509 26% /usr/local/g1_3.2_SE
format_bdf[391]: Setup: not found
/dev/vghome_old/lvol1 22.5g 9.9g 11.8g 46% /usr/local/g1
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol3 2048 133 1899 7% /usr/local/dazel
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol8 1200 295 897 25% /usr/local/ccmi
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol10 4000 3627 349 91% /usr/local/TWS
format_bdf[391]: Setup: not found
/dev/vgapp/lvol5 2048 15 2023 1% /tuxhome
format_bdf[391]: Setup: not found
/dev/vgapp02/lvol1 146.5g 96.1g 47.2g 67% /tuxappl
format_bdf[391]: Setup: not found
/dev/vg00/lvol6 1504 43 1453 3% /tmp
format_bdf[391]: Setup: not found
/dev/vgapp-io/lvol1 189.9g 146.5g 43.3g 77% /tlgvar
format_bdf[391]: Setup: not found
/dev/vgapp/lvol4 20.0g 2 19.8g 0% /tlg
format_bdf[391]: Setup: not found
/dev/vgapp/lvol2 62.5g 1111 60.9g 2% /reports
format_bdf[391]: Setup: not found
/dev/vgmafg/lvol1 50.0g 6 49.6g 0% /pvdev
format_bdf[391]: Setup: not found
/dev/vgapp/lvol6 200 10 188 5% /p/sbms
format_bdf[391]: Setup: not found
/dev/vgapp/lvol7 97.8g 46.2g 51.2g 47% /p/sbms/mps
format_bdf[391]: Setup: not found
/dev/vgdbcommon/lvol2 160.0g 111.2g 48.4g 70% /oraexp
format_bdf[391]: Setup: not found
/dev/vg00/lvol5 4096 3555 535 87% /opt
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol11 3072 514 2397 18% /opt/mqm
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol5 1024 379 639 37% /opt/iona
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol13 1024 1 958 0% /opt/app/d1mqmm1
format_bdf[391]: Setup: not found
/dev/vg3psw/lvol6 2048 678 1360 33% /opt/app/bmc
format_bdf[391]: Setup: not found
/dev/vgapp/lvol3 2048 23 2008 1% /opcode
format_bdf[391]: Setup: not found
/dev/vgapp/lvol9 24.4g 22.4g 2088 92% /mps
format_bdf[391]: Setup: not found
/dev/vghome_old/lvol5 5024 181 4540 4% /lsms_tool
format_bdf[391]: Setup: not found
/dev/vgapp-io/lvol2 2048 5 2027 0% /logs
format_bdf[391]: Setup: not found
/dev/vgdbcommon/lvol1 17.6g 1474 16.0g 8% /logs/ORACLE
format_bdf[391]: Setup: not found
/dev/vghome/lvol1 60.0g 51.8g 8393 86% /home
format_bdf[391]: Setup: not found
/dev/vgapp/lvol8 512 283 213 57% /emc
format_bdf[391]: Setup: not found
/dev/vgd2bl12d_2/lvol3 19.6g 5 18.3g 0% /csmtier1/logs
format_bdf[391]: Setup: not found
/dev/vgapp/lvol10 1024 6 954 1% /csmscripts
format_bdf[391]: Setup: not found
/dev/vghome_old/lvol6 49.3g 3 48.9g 0% /ora_D2BL12B_4
format_bdf[391]: Setup: not found
/dev/vgd2bl12b_2/lvol1 50.0g 4058 45.6g 8% /ora_D2BL12B_3
format_bdf[391]: Setup: not found
/dev/vgd2bl12d_2/lvol2 32.6g 27.7g 4994 85% /ora_D2BL42D_2
format_bdf[391]: Setup: not found
/dev/vghome_old/lvol7 60.0g 58.6g 1393 98% /ora_D2BL42C_5
format_bdf[391]: Setup: not found
/dev/vghome_old/lvol4 29.3g 3 29.1g 0% /ora_D2BL12A_3
format_bdf[391]: Setup: not found
/dev/vghome_old/lvol3 34.5g 9 32.3g 0% /ora_D2BL12B_2
format_bdf[391]: Setup: not found
/dev/vghome_old/lvol2 19.5g 4002 15.5g 20% /ora_D2BL42C_4
format_bdf[391]: Setup: not found
/dev/vgd2bl42a_2/lvol2 25.0g 24.5g 510 98% /ora_D2BL42C_3
format_bdf[391]: Setup: not found
/dev/vgd2bl42e/lvol1 50.0g 22.4g 27.4g 45% /ora_D2BL42E
format_bdf[391]: Setup: not found
/dev/vgd2bl42d/lvol1 20.0g 10.0g 9.9g 50% /ora_D2BL42D
format_bdf[391]: Setup: not found
/dev/vgd2bl42c_2/lvol1 40.0g 38.1g 1909 95% /ora_D2BL42C_2
format_bdf[391]: Setup: not found
/dev/vgd2bl42c/lvol1 20.0g 17.9g 2122 90% /ora_D2BL42C
format_bdf[391]: Setup: not found
/dev/vgd2bl42b/lvol1 25.0g 4018 20.9g 16% /ora_D2BL42B
format_bdf[391]: Setup: not found
/dev/vgd2bl42a_2/lvol1 15.0g 2 14.9g 0% /ora_D2BL42A_2
format_bdf[391]: Setup: not found
/dev/vgd2bl42a/lvol1 20.0g 18.4g 1629 92% /ora_D2BL42A
format_bdf[391]: Setup: not found
/dev/vgd2bl42e/lvol2 20.0g 2 19.8g 0% /ora_D2BL42E_2
format_bdf[391]: Setup: not found
/dev/vgd2bl43c/lvol1 15.0g 12.1g 2933 81% /ora_D2BL43C
format_bdf[391]: Setup: not found
/dev/vgd2bl43b/lvol2 14.0g 13.7g 330 98% /ora_D2BL43B_2
format_bdf[391]: Setup: not found
/dev/vgd2bl43b/lvol1 45.0g 44.5g 444 99% /ora_D2BL43B
format_bdf[391]: Setup: not found
/dev/vgd2bl43a/lvol1 12.0g 11.1g 914 93% /ora_D2BL43A
format_bdf[391]: Setup: not found
/dev/vgd2bl12d_2/lvol1 29.3g 16 29.1g 0% /ora_D2BL12D_2
format_bdf[391]: Setup: not found
/dev/vgd2bl12d/lvol1 20.0g 15.9g 4133 80% /ora_D2BL12D
format_bdf[391]: Setup: not found
/dev/vgapp/lvol11 48.9g 3 48.5g 0% /ora_D2BL12C_4
format_bdf[391]: Setup: not found
/dev/vgd2bl12c_2/lvol2 40.0g 3 39.7g 0% /ora_D2BL12C_3
format_bdf[391]: Setup: not found
/dev/vgd2bl12c_2/lvol1 40.0g 3 39.7g 0% /ora_D2BL12C_2
format_bdf[391]: Setup: not found
/dev/vgd2bl12c/lvol1 125.0g 119.1g 5923 95% /ora_D2BL12C
format_bdf[391]: Setup: not found
/dev/vgd2bl12b/lvol1 139.9g 101.4g 38.3g 73% /ora_D2BL12B
format_bdf[391]: Setup: not found
/dev/vgd2bl12a_2/lvol2 50.0g 4102 45.6g 8% /ora_D2BL12A_4
format_bdf[391]: Setup: not found
/dev/vgd2bl12a_2/lvol1 50.0g 3 49.6g 0% /ora_D2BL12A_2
format_bdf[391]: Setup: not found
/dev/vgd2bl12a/lvol1 75.0g 73.2g 1757 98% /ora_D2BL12A
format_bdf[391]: Setup: not found
/dev/vg27/lvol3 195.4g 188.5g 6980 97% /ora_D2BL42A_3
format_bdf[391]: Setup: not found
/dev/vg27/lvol2 293.0g 25 290.7g 0% /ora_D2BL42B_2
format_bdf[391]: Setup: not found
/dev/vg27/lvol1 293.0g 274.0g 18.8g 94% /ora_D2BL43A_2
format_bdf[391]: Setup: not found
dhtqa2:/nvqa_98 80.0g 66.7g 13.2g 84% /nvqa_98
format_bdf[391]: Setup: not found
/dev/vg27/lvol4 19.6g 9594 10.2g 48% /ora_fbf_1
format_bdf[391]: Setup: not found
dhdtlgcc:/nvdev5 48.9g 41.7g 6891 86% /nvdev_98a
format_bdf[391]: Setup: not found
dhdtlgcc:/nvdev5 48.9g 41.7g 6891 86% /nvdev_97a
format_bdf[391]: Setup: not found
dhdtlgcc:/scdev2 2048 466 1482 24% /scdev_30
format_bdf[391]: Setup: not found
dhdtlgcc:/scdev5 2048 466 1482 24% /scdev_30a
format_bdf[391]: Setup: not found
dhdtlgcc:/scdev5 2048 466 1482 24% /scdev_29
format_bdf[391]: Setup: not found
dhdtlgcc:/fbfdev2 1024 325 656 33% /fbfdev_98
format_bdf[391]: Setup: not found
dhdtlgcc:/fbfdev5 1024 330 649 34% /fbfdev_98a
format_bdf[391]: Setup: not found
dhdtlgcc:/fbfdev5 1024 330 649 34% /fbfdev_97a
format_bdf[391]: Setup: not found
dhdtlgcc:/fbfdev1 1024 313 667 32% /fbfdev_97
format_bdf[391]: Setup: not found
dhdtlgcc:/scdev4 2048 305 1634 16% /scdev_28
format_bdf[391]: Setup: not found
dhdtlgcc:/nvdev4 65.0g 37.7g 25.6g 60% /nvdev_96
format_bdf[391]: Setup: not found
dhdtlgcc:/nvdev5 48.9g 41.7g 6891 86% /nvdev_96a
format_bdf[391]: Setup: not found
dhdtlgcc:/nvdev3 73.9g 42.3g 29.6g 59% /nvdev_95
format_bdf[391]: Setup: not found
dhdtlgcc:/qproj4 78.9g 63.6g 15.2g 81% /qproj4
format_bdf[391]: Setup: not found
dhdtlgcc:/qproj3 78.1g 56.2g 20.6g 73% /qproj3
format_bdf[391]: Setup: not found
dhdtlgcc:/qproj2 78.1g 64.4g 12.9g 83% /qproj2
format_bdf[391]: Setup: not found
dhdtlgcc:/qproj1 78.9g 48.3g 28.8g 63% /qproj1
format_bdf[391]: Setup: not found
dhtqa2:/qa_data 15.0g 6997 7854 47% /qa_data
format_bdf[391]: Setup: not found
dhtqa2:/fbfqa_98 20.0g 1383 18.5g 7% /fbfqa_98
format_bdf[391]: Setup: not found
dhtqa2:/scqa_30 3072 1524 1451 51% /scqa_30
format_bdf[391]: Setup: not found
dhtqa2:/scqa_26 2048 618 1339 32% /scqa_26
format_bdf[391]: Setup: not found
dhtqa2:/nvqa_97 79.1g 64.2g 14.0g 82% /nvqa_97
format_bdf[391]: Setup: not found
dhtqa2:/nvqa_96 98.0g 73.3g 23.1g 76% /nvqa_96
format_bdf[391]: Setup: not found
dhtqa2:/nvqa_93 48.3g 37.6g 10.1g 79% /nvqa_93
format_bdf[391]: Setup: not found
dhdtlgcc:/fbfdev4 1024 304 675 31% /fbfdev_96
format_bdf[391]: Setup: not found
dhdtlgcc:/fbfdev2 1024 325 656 33% /fbfdev_94
format_bdf[391]: Setup: not found
dhdtlgcc:/fbfdev1 1024 313 667 32% /fbfdev1
format_bdf[391]: Setup: not found
dhdtlgcc:/scdev5 2048 466 1482 24% /scdev1
format_bdf[391]: Setup: not found
dhtqa2:/fbfqa_97 29.5g 1326 26.4g 5% /fbfqa_97
format_bdf[391]: Setup: not found
dhtqa2:/scqa_29 25.0g 950 23.9g 4% /scqa_29
format_bdf[391]: Setup: not found
wishbone:/opcode 18.0g 1347 16.6g 7% /opcode_qa
format_bdf[391]: Setup: not found
dhttrn1:/ECA 2048 258 1784 13% /ECA
format_bdf[391]: Setup: not found
dhdtlgcc:/home/cc 39.1g 35.7g 3245 92% /home/cc
format_bdf[391]: Setup: not found
dhdtlgcc:/nvdev1 47.5g 42.0g 5243 89% /nvdev_97
format_bdf[391]: Setup: not found
dhdtlgcc:/nvdev2 48.9g 40.7g 7812 84% /nvdev_98
format_bdf[391]: Setup: not found
dhdtlgcc:/fbfdev5 1024 330 649 34% /fbfdev_71a
format_bdf[391]: Setup: not found
dhdtlgcc:/fbfdev3 1024 331 648 34% /fbfdev_71
format_bdf[391]: Setup: not found
dhdtlgcc:/scdev5 2048 466 1482 24% /scdev_31a
format_bdf[391]: Setup: not found
dhdtlgcc:/scdev3 2048 431 1515 22% /scdev_31
format_bdf[391]: Setup: not found
dhdtlgcc:/nvdev5 48.9g 41.7g 6891 86% /nvdev_71a
format_bdf[391]: Setup: not found
dhdtlgcc:/nvdev3 73.9g 42.3g 29.6g 59% /nvdev_71
format_bdf[391]: Setup: not found
dhdtlgcc:/fbfdev3 1024 331 648 34% /fbfdev_95
format_bdf[391]: Setup: not found
dhdtlgcc:/scdev3 2048 431 1515 22% /scdev_27
format_bdf[391]: Setup: not found
dhqtlgmm:/tlg 310.0g 248.1g 58.1g 81% /ndev_988
format_bdf[391]: Setup: not found
dhtqa2:/nvqa_71 79.1g 64.2g 14.0g 82% /nvqa_71
format_bdf[391]: Setup: not found
dhtqa2:/scqa_31 25.0g 950 23.9g 4% /scqa_31
format_bdf[391]: Setup: not found
dhtqa2:/fbfqa_71 29.5g 1326 26.4g 5% /fbfqa_71
format_bdf[391]: Setup: not found
dhtqa1:/qadmin 1024 58 958 6% /qadmin

[pareshan]

Hein,

when I run I got this output but I got one error also.



/dev/vg00/lvol4 1048576 314096 728792 30 /
/dev/vg00/lvol1 1014648 65224 847952 7 /stand
/dev/vg00/lvol8 4194304 1463624 2710720 35 /var
/dev/vg3psw/lvol12 3145728 1221234 1804232 40 /var/mqm
/dev/vg00/lvol10 4194304 3749008 441888 89 /var/adm/sw
/dev/vg00/lvol9 4194304 16744 4144936 0 /var/adm/crash
/dev/vg00/lvol7 5242880 1871472 3345104 36 /usr
/dev/vg3psw/lvol1 4194304 3883768 308128 93 /usr/local
/dev/vg3psw/lvol7 10338304 8812630 1430332 86 /usr/local/vertex
/dev/vg3psw/lvol2 12738560 8080712 4622152 64 /usr/local/oracle
/dev/vg3psw/lvol9 5120000 1286387 3594077 26 /usr/local/g1_3.2_SE
Can't call method "line" on an undefined value at ./hein.pl line 10.



and also when I tried to print the value of those variables
$fs, $kbytes, $used, $avail, $pct_used, $mount
I dint get anything the output dint change than the above one.

Looks like the variable doesnt hold anything.

Any suggestion?

[Hein van den Heuvel]

I think you just had a copy & paste problem causing a line wrap for the word 'line' around line 10.

If you run the scrip on the system that produced the sample output, then the error happened on the first time it encountered a wrapped bdf line ( for: /dev/vghome_old/lvol1 ).

Without the comments, which potentially cause line wraps on pasting the text, the code to handle that looks like:

if (!/% /) {
chomp;
$filesystem = $_;
next;
}

Please verify to make sure that was the problem and/or use the attached txt file version.
That version also prints just 2 columns, to prove that that works also.
Those variables, like $fs, are ONLY valid within the loop, and change for most every iteration of the loop. The 'my ($fs...' construct makes them loop-local variables.

Hein.

[pareshan]

Hein, Its getting better, that one is solved but I have this error now

/dev/vg00/lvol4 1048576 314096 728792 30 /
/dev/vg00/lvol1 1014648 65224 847952 7 /stand
/dev/vg00/lvol8 4194304 1469008 2705192 35 /var
/dev/vg3psw/lvol12 3145728 1221238 1804228 40 /var/mqm
/dev/vg00/lvol10 4194304 3749008 441888 89 /var/adm/sw
/dev/vg00/lvol9 4194304 16744 4144936 0 /var/adm/crash
/dev/vg00/lvol7 5242880 1871472 3345104 36 /usr
/dev/vg3psw/lvol1 4194304 3883768 308128 93 /usr/local
/dev/vg3psw/lvol7 10338304 8812630 1430332 86 /usr/local/vertex
/dev/vg3psw/lvol2 12738560 8080712 4622152 64 /usr/local/oracle
/dev/vg3psw/lvol9 5120000 1286387 3594077 26 /usr/local/g1_3.2_SE
print() on unopened filehandle 23552000 10375127 12353732 46% /usr/local/g1
/dev/vghome_old/lvol1 23552000 10375127 12353732 46 /usr/local/g1
/dev/vg3psw/lvol3 2097152 137104 1944800 7 /usr/local/dazel
/dev/vg3psw/lvol8 1228800 302520 919104 25 /usr/local/ccmi
/dev/vg3psw/lvol10 4096000 3714072 358119 91 /usr/local/TWS
/dev/vgapp/lvol5 2097152 16192 2072336 1 /tuxhome
/dev/vgapp02/lvol1 153616384 100872998 49476759 67 /tuxappl
/dev/vg00/lvol6 1540096 34920 1497888 2 /tmp
print() on unopened filehandle 199155712 153637688 45341544 77% /tlgvar
/dev/vgapp-io/lvol1 199155712 153637688 45341544 77 /tlgvar
/dev/vgapp/lvol4 20971520 2912 20804800 0 /tlg
/dev/vgapp/lvol2 65536000 1138696 63894272 2 /reports
/dev/vgmafg/lvol1 52396032 6968 51982472 0 /pvdev
/dev/vgapp/lvol6 204800 10712 192632 5 /p/sbms
/dev/vgapp/lvol7 102498304 48414312 53672696 47 /p/sbms/mps
print() on unopened filehandle 167755776 116583368 50781904 70% /oraexp
/dev/vgdbcommon/lvol2 167755776 116583368 50781904 70 /oraexp
/dev/vg00/lvol5 4194304 3641336 548688 87 /opt
/dev/vg3psw/lvol11 3145728 527068 2455009 18 /opt/mqm
/dev/vg3psw/lvol5 1048576 388952 654520 37 /opt/iona
/dev/vg3psw/lvol13 1048576 1357 981775 0 /opt/app/d1mqmm1
/dev/vg3psw/lvol6 2097152 735096 1353320 35 /opt/app/bmc
/dev/vgapp/lvol3 2097152 24344 2056680 1 /opcode
/dev/vgapp/lvol9 25624576 23453584 2139148 92 /mps
print() on unopened filehandle 5144576 185360 4649319 4% /lsms_tool
/dev/vghome_old/lvol5 5144576 185360 4649319 4 /lsms_tool
print() on unopened filehandle 2097152 5216 2075792 0% /logs
/dev/vgapp-io/lvol2 2097152 5216 2075792 0 /logs
print() on unopened filehandle 18432000 1527000 16777512 8% /logs/ORACLE
/dev/vgdbcommon/lvol1 18432000 1527000 16777512 8 /logs/ORACLE
/dev/vghome/lvol1 62898176 54017728 8880448 86 /home
/dev/vgapp/lvol8 524288 290587 219125 57 /emc
print() on unopened filehandle 20512768 6129 19224982 0% /csmtier1/logs
/dev/vgd2bl12d_2/lvol3 20512768 6129 19224982 0 /csmtier1/logs
/dev/vgapp/lvol10 1048576 6334 977155 1 /csmscripts
print() on unopened filehandle 51707904 3864 51300112 0% /ora_D2BL12B_4
/dev/vghome_old/lvol6 51707904 3864 51300112 0 /ora_D2BL12B_4
print() on unopened filehandle 52396032 4155472 47863744 8% /ora_D2BL12B_3
/dev/vgd2bl12b_2/lvol1 52396032 4155472 47863744 8 /ora_D2BL12B_3
print() on unopened filehandle 34209792 29055568 5114024 85% /ora_D2BL42D_2
/dev/vgd2bl12d_2/lvol2 34209792 29055568 5114024 85 /ora_D2BL42D_2
print() on unopened filehandle 62898176 61460312 1426640 98% /ora_D2BL42C_5
/dev/vghome_old/lvol7 62898176 61460312 1426640 98 /ora_D2BL42C_5
print() on unopened filehandle 30720000 3216 30476816 0% /ora_D2BL12A_3
/dev/vghome_old/lvol4 30720000 3216 30476816 0 /ora_D2BL12A_3
print() on unopened filehandle 36126720 9960 33859469 0% /ora_D2BL12B_2
/dev/vghome_old/lvol3 36126720 9960 33859469 0 /ora_D2BL12B_2
print() on unopened filehandle 20480000 4098928 16253104 20% /ora_D2BL42C_4
/dev/vghome_old/lvol2 20480000 4098928 16253104 20 /ora_D2BL42C_4
print() on unopened filehandle 26214400 25687784 522512 98% /ora_D2BL42C_3
/dev/vgd2bl42a_2/lvol2 26214400 25687784 522512 98 /ora_D2BL42C_3
print() on unopened filehandle 52412416 23481928 28704536 45% /ora_D2BL42E
/dev/vgd2bl42e/lvol1 52412416 23481928 28704536 45 /ora_D2BL42E
print() on unopened filehandle 20955136 10502664 10370880 50% /ora_D2BL42D
/dev/vgd2bl42d/lvol1 20955136 10502664 10370880 50 /ora_D2BL42D
print() on unopened filehandle 41926656 39955584 1955680 95% /ora_D2BL42C_2
/dev/vgd2bl42c_2/lvol1 41926656 39955584 1955680 95 /ora_D2BL42C_2
print() on unopened filehandle 20955136 18765072 2172960 90% /ora_D2BL42C
/dev/vgd2bl42c/lvol1 20955136 18765072 2172960 90 /ora_D2BL42C
print() on unopened filehandle 26198016 4115056 21910448 16% /ora_D2BL42B
/dev/vgd2bl42b/lvol1 26198016 4115056 21910448 16 /ora_D2BL42B
print() on unopened filehandle 15728640 2784 15603008 0% /ora_D2BL42A_2
/dev/vgd2bl42a_2/lvol1 15728640 2784 15603008 0 /ora_D2BL42A_2
print() on unopened filehandle 20955136 19273216 1668784 92% /ora_D2BL42A
/dev/vgd2bl42a/lvol1 20955136 19273216 1668784 92 /ora_D2BL42A
print() on unopened filehandle 20955136 2920 20788536 0% /ora_D2BL42E_2
/dev/vgd2bl42e/lvol2 20955136 2920 20788536 0 /ora_D2BL42E_2
print() on unopened filehandle 15712256 12684776 3003896 81% /ora_D2BL43C
/dev/vgd2bl43c/lvol1 15712256 12684776 3003896 81 /ora_D2BL43C
print() on unopened filehandle 14680064 14338784 338624 98% /ora_D2BL43B_2
/dev/vgd2bl43b/lvol2 14680064 14338784 338624 98 /ora_D2BL43B_2
print() on unopened filehandle 47169536 46710616 455400 99% /ora_D2BL43B
/dev/vgd2bl43b/lvol1 47169536 46710616 455400 99 /ora_D2BL43B
print() on unopened filehandle 12566528 11622368 936848 93% /ora_D2BL43A
/dev/vgd2bl43a/lvol1 12566528 11622368 936848 93 /ora_D2BL43A
print() on unopened filehandle 30736384 17104 30479296 0% /ora_D2BL12D_2
/dev/vgd2bl12d_2/lvol1 30736384 17104 30479296 0 /ora_D2BL12D_2
print() on unopened filehandle 20963328 16697736 4232272 80% /ora_D2BL12D
/dev/vgd2bl12d/lvol1 20963328 16697736 4232272 80 /ora_D2BL12D
/dev/vgapp/lvol11 51298304 3864 50893712 0 /ora_D2BL12C_4
print() on unopened filehandle 41910272 3560 41579328 0% /ora_D2BL12C_3
/dev/vgd2bl12c_2/lvol2 41910272 3560 41579328 0 /ora_D2BL12C_3
print() on unopened filehandle 41910272 3560 41579328 0% /ora_D2BL12C_2
/dev/vgd2bl12c_2/lvol1 41910272 3560 41579328 0 /ora_D2BL12C_2
print() on unopened filehandle 131047424 124934576 6065160 95% /ora_D2BL12C
/dev/vgd2bl12c/lvol1 131047424 124934576 6065160 95 /ora_D2BL12C
print() on unopened filehandle 146743296 106296952 40130408 73% /ora_D2BL12B
/dev/vgd2bl12b/lvol1 146743296 106296952 40130408 73 /ora_D2BL12B
print() on unopened filehandle 52396032 4201448 47818072 8% /ora_D2BL12A_4
/dev/vgd2bl12a_2/lvol2 52396032 4201448 47818072 8 /ora_D2BL12A_4
print() on unopened filehandle 52396032 3880 51982848 0% /ora_D2BL12A_2
/dev/vgd2bl12a_2/lvol1 52396032 3880 51982848 0 /ora_D2BL12A_2
print() on unopened filehandle 78618624 76804888 1799640 98% /ora_D2BL12A
/dev/vgd2bl12a/lvol1 78618624 76804888 1799640 98 /ora_D2BL12A
/dev/vg27/lvol3 204865536 197661672 7147648 97 /ora_D2BL42A_3
/dev/vg27/lvol2 307232768 26120 304806672 0 /ora_D2BL42B_2
/dev/vg27/lvol1 307232768 287320072 19757192 94 /ora_D2BL43A_2
dhtqa2:/nvqa_98 83886080 69989880 13789704 84 /nvqa_98
/dev/vg27/lvol4 20578304 9825080 10669224 48 /ora_fbf_1
dhdtlgcc:/nvdev5 51249152 43721568 7057120 86 /nvdev_98a
dhdtlgcc:/nvdev5 51249152 43721568 7057120 86 /nvdev_97a
dhdtlgcc:/scdev2 2097152 477720 1518240 24 /scdev_30
dhdtlgcc:/scdev5 2097152 478104 1517880 24 /scdev_30a
dhdtlgcc:/scdev5 2097152 478104 1517880 24 /scdev_29
dhdtlgcc:/fbfdev2 1048576 332856 671776 33 /fbfdev_98
dhdtlgcc:/fbfdev5 1048576 338832 665408 34 /fbfdev_98a
dhdtlgcc:/fbfdev5 1048576 338832 665408 34 /fbfdev_97a
dhdtlgcc:/fbfdev1 1048576 320664 683960 32 /fbfdev_97
dhdtlgcc:/scdev4 2097152 312832 1673720 16 /scdev_28
dhdtlgcc:/nvdev4 68157440 39546928 26825408 60 /nvdev_96
dhdtlgcc:/nvdev5 51249152 43721568 7057120 86 /nvdev_96a
dhdtlgcc:/nvdev3 77463552 44335616 31057448 59 /nvdev_95
dhdtlgcc:/qproj4 82706432 66679968 15915696 81 /qproj4
dhdtlgcc:/qproj3 81920000 58942336 21561512 73 /qproj3
dhdtlgcc:/qproj2 81920000 67528304 13497008 83 /qproj2
dhdtlgcc:/qproj1 82706432 50596336 30184632 63 /qproj1
dhtqa2:/qa_data 15728640 7165664 8043296 47 /qa_data
dhtqa2:/fbfqa_98 20971520 1416464 19403080 7 /fbfqa_98
dhtqa2:/scqa_30 3145728 1561032 1486552 51 /scqa_30
dhtqa2:/scqa_26 2097152 633680 1372048 32 /scqa_26
dhtqa2:/nvqa_97 82968576 67447328 14551200 82 /nvqa_97
dhtqa2:/nvqa_96 102760448 76883520 24263072 76 /nvqa_96
dhtqa2:/nvqa_93 50634752 39384216 10547440 79 /nvqa_93
dhdtlgcc:/fbfdev4 1048576 311528 691768 31 /fbfdev_96
dhdtlgcc:/fbfdev2 1048576 332856 671776 33 /fbfdev_94
dhdtlgcc:/fbfdev1 1048576 320664 683960 32 /fbfdev1
dhdtlgcc:/scdev5 2097152 478104 1517880 24 /scdev1
dhtqa2:/fbfqa_97 30932992 1358472 27726128 5 /fbfqa_97
dhtqa2:/scqa_29 26214400 972984 25045088 4 /scqa_29
wishbone:/opcode 18874368 1379576 17365680 7 /opcode_qa
dhttrn1:/ECA 2097152 265072 1827744 13 /ECA
dhdtlgcc:/home/cc 40960000 37415648 3322880 92 /home/cc
dhdtlgcc:/nvdev1 49807360 44079800 5369648 89 /nvdev_97
dhdtlgcc:/nvdev2 51249152 42715696 8000136 84 /nvdev_98
dhdtlgcc:/fbfdev5 1048576 338832 665408 34 /fbfdev_71a
dhdtlgcc:/fbfdev3 1048576 339872 664432 34 /fbfdev_71
dhdtlgcc:/scdev5 2097152 478104 1517880 24 /scdev_31a
dhdtlgcc:/scdev3 2097152 441920 1551800 22 /scdev_31
dhdtlgcc:/nvdev5 51249152 43721568 7057120 86 /nvdev_71a
dhdtlgcc:/nvdev3 77463552 44335616 31057448 59 /nvdev_71
dhdtlgcc:/fbfdev3 1048576 339872 664432 34 /fbfdev_95
dhdtlgcc:/scdev3 2097152 441920 1551800 22 /scdev_27
dhqtlgmm:/tlg 325058560 260225144 60795032 81 /ndev_988
dhtqa2:/nvqa_71 82968576 67447328 14551200 82 /nvqa_71
dhtqa2:/scqa_31 26214400 972984 25045088 4 /scqa_31
dhtqa2:/fbfqa_71 30932992 1358472 27726128 5 /fbfqa_71
dhtqa1:/qadmin 1048576 59880 981384 6 /qadmin

and the script I have used is almost the same you attached except I tried to print all the variables because thats what I have to insert inthe tables.

[Hein van den Heuvel · Accepted solution]

You are missing a closing semi-colon on the line that you added: print $_

And you added an 'r' to 'use' in 'use strict'

My version also printed the whole line, after cleaning up some some stuf tht at first glance seemed not to be needed: whitespace, percent, no nfs

fwiw... when you have a script like this looking at all the data already, then why not make it do all the work (SQLplus or SQLload or whatever)

If all you want to do is glue the split up line together then use something like:

bdf | awk 'NF < 2 { getline rest; $0 = $0 rest } NR > 1 {print}'

Cheers,
Hein.

[pareshan]

Thank You very much Hein,
I really appreciate your help, it helpmed me alot.

It finally worked and yeah of course I have to put that in oracle table which looks like this

Name Null? Type
----------------------------------------- -------- ----------------------------
SERVER_ID NOT NULL NUMBER(6)
FS_LOCAL_NAME NOT NULL VARCHAR2(200)
LOCAL_MOUNT NOT NULL CHAR(1)
TOTAL_SPACE NOT NULL NUMBER(10)
USED_SPACE NUMBER(10)
SPACE_AVAILABLE NUMBER(10)
PERCENTAGE_USED NUMBER(3,2)

but I have to insert them using Korn shell not the PERL so I am wondering how can i do that as my script in in Perl now. Also you can see there is one extra field SERVER_ID rest is exact same as we have from bdf command.

[pareshan]

Any Idea about how to inset into the table with the above structure because
Note: table got one extra column SERVER_ID that we dont have in bdf output

thanks

[Hein van den Heuvel]

pareshan,

Please check back to the very first reply. It injects a server_id column for sql (a fixed value of 1)
Combine the (working) outline there with what we since learned

You may want to pick up the server_id as a command line argument or an environment variable or a command.

pick one from:
- my $server_id = qx(hostname);
- my $server_id = shift;
- my $server_id = $ENV{SERVER_ID};

Now in the (perl) script use the variable.
Pick one from:
- print qq($server_id $fs $kbytes $used $avail $pct_used $mount\n);
- print $server_id, ' ', $_;

Good luck!
Hein.

[pareshan]

Thank You Very much guys, Specilly Hein,

That really helped me, as my requirement kept on changing as the number of table kept on increasing so I have done little bit different later on instead of preparing insert statement in perl itself I prepared the text file with all the columns i needed to insert into table and used sqlloader to load everything completely. Everything else is working great. I just have one small query before closing this thread..

I have inserted data into my oracle table which have 9 columns basically my data is output of bdf command with some extra values..

this is my table structure

SERVER_ID NOT NULL NUMBER(6)
FS_LOCAL_NAME NOT NULL VARCHAR2(200)
LOCAL_MOUNT NOT NULL CHAR(1)
TOTAL_SPACE NOT NULL NUMBER(10)
USED_SPACE NUMBER(10)
SPACE_AVAILABLE NUMBER(10)
PERCENTAGE_USED NUMBER(6,2)
FS_NAME NOT NULL VARCHAR2(200)
MOUNTED_SERVER VARCHAR2(20)


I have made the composite primary key on server_id, fs_local_name, fs_name. Here, what I am trying to do is everytime I run the script i want to store my bdf output values in using some sort of Timestamp so that its easy to separate the results.

Anybody can help me how can i do that. How can i define the timestamp column in a table so that each time i run the script it will give me timestamp value. Also it will be great if i can make primary key on timestamp and server_id instead of the other one i used.

Thanks in advance
I will really appreciate any help