Operating System - HP-UX
1748225 Members
4511 Online
108759 Solutions
New Discussion юеВ

Re: UNIX Scripting (KSH) and Oracle

 
SOLVED
Go to solution
pareshan
Regular Advisor

UNIX Scripting (KSH) and Oracle

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
25 REPLIES 25
Hein van den Heuvel
Honored Contributor

Re: UNIX Scripting (KSH) and Oracle


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
Regular Advisor

Re: UNIX Scripting (KSH) and Oracle

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
Regular Advisor

Re: UNIX Scripting (KSH) and Oracle

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
Honored Contributor

Re: UNIX Scripting (KSH) and Oracle


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
Regular Advisor

Re: UNIX Scripting (KSH) and Oracle

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
Honored Contributor

Re: UNIX Scripting (KSH) and Oracle

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
Honored Contributor

Re: UNIX Scripting (KSH) and Oracle

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


Bill Hassell, sysadmin
pareshan
Regular Advisor

Re: UNIX Scripting (KSH) and Oracle

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
Honored Contributor

Re: UNIX Scripting (KSH) and Oracle

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.