Accessing versioned geodata in ArcSDE with SQL

This is excerpted from the following forum post:

“We’re running SDE and have several featuresets with versioning turned on…. We have numerous non-GIS applications that use SQL queries to access information from the spatial data. However, we don’t get all the features that I’m expecting to be returned. How do I access the features that have been added/deleted (and are, therefore, “hidden” from a straight SQL query)?”

As long as it is just the attributes that you are after, you can set up a multi-versioned view using sdetable.exe.

sdetable.exe -o create_mv_view -T mvv_wMeter -t wMeter

Then on your db connection, you execute the set_current_version stored proc to set the version, then issue your select statement on the multi-versioned view:

exec sde.set_current_version 'SDE.SOMEOTHERVERSION'
GO
Select COUNT(*) from mvv_wMeter ;

i don’t have it my example, but sdetable.exe needs db connection info arguments: -s,-i,-u,-p,-D in some combination depending on your config (I usually just set the SDEDATABASE,SDEINSTANCE,SDESERVER env vars in a batch file–sdetable use the env vars if they exist). – Jay Cummins Aug 6 ’10 at 19:39

I think this ESRI support page may be relevant.

I believe it is worth posting the warnings on that page here:

Never use database management system (DBMS) tools to update any row ID (object ID) field maintained by ArcSDE in the database. These object ID fields are allocated and managed by the geodatabase and, therefore, should not be altered using SQL.

Never edit the DEFAULT version of the geodatabase using SQL. Starting an edit session on a version obtains an exclusive lock on the state that the version references. If you lock the DEFAULT version, you prevent ArcGIS users from connecting to the geodatabase.

In the 9.3 help page they also warned against editing non-simple feature class attributes (Geometric Networks, Topologies, etc.) via SQL.

ITIS Organism Flat table from MySQL

I need to make a table to look up common (vernacular) names for some organisms. So I imported the USGS ITIS database from text files into MySQL and created this little view. Hopefully this helps someone else.

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_flat_table` AS
SELECT
  `taxonomic_units`.`tsn`         AS `tsn`,
  `taxonomic_units`.`unit_ind1`   AS `unit_ind1`,
  `taxonomic_units`.`unit_name1`  AS `unit_name1`,
  `taxonomic_units`.`unit_ind2`   AS `unit_ind2`,
  `taxonomic_units`.`unit_name2`  AS `unit_name2`,
  `taxonomic_units`.`unit_ind3`   AS `unit_ind3`,
  `taxonomic_units`.`unit_name3`  AS `unit_name3`,
  `taxonomic_units`.`unit_ind4`   AS `unit_ind4`,
  `taxonomic_units`.`parent_tsn`  AS `parent_tsn`,
  `taxonomic_units`.`update_date` AS `update_date`,
  `taxon_unit_types`.`rank_name`  AS `rank_name`,
  `vernaculars`.`vernacular_name` AS `vernacular_name`,
  `longnames`.`completename`      AS `completename`
FROM (((`taxon_unit_types`
     JOIN `taxonomic_units`
       ON (((`taxon_unit_types`.`rank_id` = `taxonomic_units`.`rank_id`)
            AND (`taxon_unit_types`.`kingdom_id` = `taxonomic_units`.`kingdom_id`))))
    LEFT JOIN `vernaculars`
      ON ((`vernaculars`.`tsn` = `taxonomic_units`.`tsn`)))
   LEFT JOIN `longnames`
     ON ((`longnames`.`tsn` = `taxonomic_units`.`tsn`)))

Loading tab-delimited data to local MySQL

I’m looking at some data from the Avian Knowledge Network and Microsoft Access just isn’t up to dealing with the volume of records. So I switched over to MySQL.

I’m using the Positive Observation Essentials format as queried from their database. Here’s an example of the data:

I did use a sed command (created and verified in Excel) on the original files to fix the spaces in the column names (if you are in the business of making data for people, never, ever, ever, ever create large tabular files with spaces or special characters in the column names. Avoid dashes as well, and preferably use all caps). Note that the commands below will REPLACE your files. So be sure to back up your originals in case anything goes wrong.

find . -name ‘*AKN*’ -exec sed -i ‘s/Record\ Number/Record_Number/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Global\ Unique\ Identifier/Global_Unique_Identifier/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Collection\ Code/Collection_Code/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Scientific\ Name/Scientific_Name/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Latitude/Latitude/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Longitude/Longitude/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Country/Country/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/State\ Province/State_Province/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Observation\ Count/Observation_Count/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Obs\ Count\ At\ Least/Obs_Count_At_Least/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Obs\ Count\ At\ Most/Obs_Count_At_Most/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Observation\ Date/Observation_Date/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Duration\ In\ Hours/Duration_In_Hours/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Time\ Observations\ Started/Time_Observations_Started/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Time\ Observations\ Ended/Time_Observations_Ended/g’ {} \;
find . -name ‘*AKN*’ -exec sed -i ‘s/Sampling\ Event\ Identifier/Sampling_Event_Identifier/g’ {} \;

Then I create a basic staging table (commands also from Excel). I don’t know why I used InnoDB, when MyIsam would have been faster. But, notice the absence of indexes for faster loading.

CREATE TABLE `nv_akn` (
  `Record_Number` VARCHAR(30) DEFAULT NULL,
  `Global_Unique_Identifier` VARCHAR(30) DEFAULT NULL,
  `Collection_Code` VARCHAR(30) DEFAULT NULL,
  `Scientific_Name` VARCHAR(150) DEFAULT NULL,
  `Latitude` VARCHAR(30) DEFAULT NULL,
  `Longitude` VARCHAR(30) DEFAULT NULL,
  `Country` VARCHAR(50) DEFAULT NULL,
  `State_Province` VARCHAR(50) DEFAULT NULL,
  `Observation_Count` VARCHAR(30) DEFAULT NULL,
  `Obs_Count_At_Least` VARCHAR(30) DEFAULT NULL,
  `Obs_Count_At_Most` VARCHAR(30) DEFAULT NULL,
  `Observation_Date` VARCHAR(30) DEFAULT NULL,
  `Duration_In_Hours` VARCHAR(30) DEFAULT NULL,
  `Time_Observations_Started` VARCHAR(30) DEFAULT NULL,
  `Time_Observations_Ended` VARCHAR(30) DEFAULT NULL,
  `Sampling_Event_Identifier` VARCHAR(50) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=latin1

I’m just doing this locally, so XAMPP is my friend. So from the xampp /mysql/bin directory, I ran mysql.exe. I chose my database and ran the following on the text files produced by the above sed cleaners.

mysql> LOAD DATA LOCAL INFILE ‘c:/xampp/mysql/bin/Nevada_Pos_obs_Essent_15-MAR-2011.txt’ INTO TABLE nv_akn
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’ IGNORE 1 LINES;

I used LOCAL since the database is on my workstation. Note the full path to the windows file, with forward slashes. Fields are tab-delimited, lines seem to just use carriage returns (or at least it doesn’t look like I need another line ender) and I’m ignoring the column header row.

Remove header from a text file

I’m processing some Darwin-schema biodiversity text files in preparation for GISing them and I need to trim the headers from the tops of the files before importing them to the database. Of course, *NIX command line utilities have been my friend here. The following will trim the first line off the file. Just up the number to trim more lines off. I’m using Cygwin on Windows to run these.

sed '1d' file.txt # trims the line then replaces file.txt, so be careful.

awk 'FNR>1{print}' file.txt > newfile.txt # I ended up using this to be safe. It was easier to test.

tail -n+2 /path/to/file > /path/to/output # can be tested with echo -e "foo\nbar\nbaz" | tail -n+2

Also in my case, I wanted column headers left in file, and thankfully the headers were pretty much the same across all the files. So I grep searched in the directory with all the files for the name of the first uniquely worded column and asked grep to also return the line numbers of where it found that word. Then I use that number (minus one) to put into my header deleting command.

> grep -n 'Institution' *
HerpnetUtahBottomHalfProviders_January25_2010.txt:35:Institution	Collection	Catalog number text ...
HerpnetUtahTopHalfProviders_January25_2010.txt:27:Institution	Collection	Catalog number text...

Then, for example, I would use

 awk 'FNR>34{print}' HerpnetUtahBottomHalfProviders_January25_2010.txt > HerpnetUtahBottomHalfProviders_January25_2010_trimmed.txt

I love using Excel to craft a slew of commands then copy/paste them into the shell to batch process tens of files in my case.

Another automated FTP bash script

from http://www.stratigery.com/scripting.ftp.html

Another solution from the site above for performing FTP PUTs on a schedule.

The Problem

The problem I always encountered in scripting ftp transfers involved getting a password to the ftp server. Typical ftp client programs under Unix, Linux, Solaris and NetBSD all read the ftp password from /dev/tty.

Example Working Script

#!/bin/sh
HOST='ftp.users.qwest.net'
USER='yourid'
PASSWD='yourpw'
FILE='file.txt'

ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
put $FILE
quit
END_SCRIPT
exit 0

The Tricks

Getting the password to the ftp server without having the ftp client program read the password from /dev/tty requires two tricks:

  1. Using the -n option on the ftp client program to prevent the ftp client from trying to log in immediately. That way, the ftp client does not ask for a user ID and password. No use of /dev/tty.
  2. Use the ftp client program command quote to send user ID and password to the ftp server.

Control of ftp by a shell script

One obvious improvement would have the ftp client program controlled by the shell script. I don’t think that would comprise an impossible task, but I also don’t think that it would have much value. Scripting ftp transfer using expect might cause you less pain, except that it is sensitive to the environment that it is run in. Changes to default program inputs and outputs can break it.


Alternative #1

I saw a second way of doing this in a usenet article:

#!/bin/sh
USER=userid
PASSWD=userpw
ftp -n f2dev <<SCRIPT
user $USER $PASSWD
binary
get some.file
quit
SCRIPT

It still uses the “-n” trick, but it sends user ID and password in the same “user” command.


Alternative #2

Use a .netrc file

Linux, Unix and BSD users have the alternative of using a .netrc file. The ftp man page documents the format of .netrc. To accomplish the task of using ftp in a shell script you would have to fill out a .netrc file something like this:


machine something.else.com
login myid
password mypassword

ftp demands that .netrc not have group or world read or write permissions:


$ ls -l .netrc
-rw-------    1 bediger  users          51 Dec 16 13:30 .netrc

Using a .netrc file has a few problems that may or may not prevent you from using it.

  • A shell scripkt that does FTP using .netrc is no longer self-contained. You have to keep track of two files, which means that bugs can be less than obvious.
  • ftp reads it’s user ID’s .netrc. If you develop your script under a given user ID, then put it in production under a second user ID, you have to coordinate .netrc file contents between those two user IDs.

Alternative #3

Apparently, the Ckermit program from Columbia University understands FTP. You could use Ckermit to script FTP transfers. This looks to have advantages and disadvantages. On the “pro” side, it appears that Ckermit can exit on various errors, like unknown user IDs, or bad passwords. On the “con” side, you have to have Ckermit. I don’t recall that it had a too onerous install, but it doesn’t come with many Linux distros these days, and it probably doesn’t come with any vendor Unix.

See also http://www.linuxforums.org/forum/programming-scripting/106665-automatic-ftp-upload-via-script.html

#!/bin/bash
# puts the file bookmarks.html
cd $HOME/.mozilla/firefox/
cd ./*.default/
UDIRX=`pwd`
HOST='www.linuxforums.org'
USER='core-wizard'
PASSWD='leethaxor'
BASENAME=$(basename $UDIRX)
echo '$UDIRX is' $UDIRX
echo '$BASENAME is' $BASENAME

ftp -n -v $HOST << EOT
ascii
user $USER $PASSWD
prompt
mkdir .mozilla
cd .mozilla
mkdir firefox
cd firefox
mkdir $BASENAME
cd $BASENAME
put bookmarks.html
bye
EOT
sleep 12

Scheduled file copies or directory sync from local directory to FTP server

If you have a remote FTP server that you need to put files into, and you don’t want to deal with SCP/SFTP passkeys, lftp (http://lftp.yar.ru/) on the local client machine might be for you. It comes with most linux distros (I found it using yum simply as lftp) and one of its most useful traits is to be able to mirror the remote FTP directory to your local one, and vice versa (through –reverse mirror). Here’s some examples:

# verbosely mirror files from FTP server to local dir. -d to show FTP responses
lftp -d -u ftpusername,password -e “mirror –only-newer –verbose /home/ftpusername/tmp /home/localusername/tmp” ftphost.com&

# more quietly mirror files *to* FTP server from local dir.
lftp -u ftpusername,password -e “mirror –reverse –only-newer /home/localusername/tmp tmp” ftphost.com&

Notice the ampersand, which sends the command to the background so you don’t have to keep the terminal window open.

Here are some more links:

http://www.softpanorama.org/Net/Application_layer/Ftp/lftp.shtml
http://www.linux.com/archive/articles/122169
http://how-to.wikia.com/wiki/How_to_use_lftp_as_a_sftp_client
http://www.gsp.com/cgi-bin/man.cgi?section=1&topic=lftp

Using screen in Linux to save your bacon

Tonight I’m being plagued by dropped ssh connections. 2 minutes of work, them bam everything goes away. I’ve seen screen used in the past, but never had to use its power until tonight. Ask any seasoned sysadmin and they’ll tell you that screen has saved their bacon many times, and it did for me tonight. I’m definitely adding it to my toolbelt. Hopefully it stays around a little longer.

From http://www.rackaid.com/resources/linux-screen-tutorial-and-how-to/

Linux Screen Tutorial and How To

Posted by Jeff H. 01/01/2008

Using Linux Screen for Session Management

Lost your shell connection? Need multiple shell sessions?

You are logged into your remote server via SSH and happily plucking along at your keyboard and then it happens. Suddenly, the characters stop moving and then you get the dreaded “Connection Closed” message. You have just lost your session. You were halfway through some task and now you have to start over. Ugh. Well you can prevent this from happening by using screen. The Linux screen tool can not only save you from disconnection disasters, but it also can increase your productivity by using multiple windows within one SSH session.

Linux Screen for Session Management!

Screenshot of Linux Screen Terminal

Linux Screen showing the GNU page for Linux Screen Itself

Linux Screen Can Save you from that Lost Connection

What is Screen for Linux?

As the man page states, “Screen is a full-screen window manager that multiplexes a physical terminal between several processes (typically interactive shells).” This can be a life saver when working on your dedicated server. Screen has a several great features for helping you administer your server more productively and safely. I am going to discuss the three features (multiple windows, logging, sessions) that I use the most but be sure to see the man page for full details.

Installing Screen on Linux

Chances are that you already have screen on your system. On most Red Hat distributions you can find it in /usr/bin/screen. To see if screen is in your path, you can use the which command:

[admin@ensim admin]$ which screen

If you do not have screen, then you can install it easily from an RPM or the package file for your system. On Cobalt Raq servers, you can safely use the RedHat RPMS appropriate for your system.
Screen RPMs: rpmfind
Screen Web site: GNU Screen

As you probably already have screen or can use an RPM, I am not going to cover the building of screen from source. Lets get on to how to use screen.

Using Screen

Screen is started from the command line just like any other command:

[admin@gigan admin]$ screen

You may or may not get a text message about screen. If you do not, then you probably think nothing has happened, but it has. You are now inside of a window within screen. This functions just like a normal shell except for a few special characters. Screen uses the command “Ctrl-A” as a signal to send commands to screen instead of the shell. To get help, just use “Ctrl-A” then “?”. You should now have the screen help page.

Screen key bindings, page 1 of 2.

               Command key:  ^A   Literal ^A:  a
break      ^B b       lockscreen ^X x       reset      Z
clear      C          log        H          screen     ^C c
colon      :          login      L          select     " '
copy       ^[ [       meta       a          silence    _
detach     ^D d       monitor    M          split      S
digraph    ^V         next       ^@ ^N sp n suspend    ^Z z
displays   *          number     N          time       ^T t
fit        F          only       Q          title      A
flow       ^F f       other      ^A         vbell      ^G
focus      ^I         pow_break  B          version    v
help       ?          pow_detach D          width      W
history            prev       ^P p ^?    windows    ^W w
info       i          readbuf    <          wrap       ^R r
kill       K          redisplay  ^L l       writebuf   >
lastmsg    ^M m       remove     X          xoff       ^S s
license    ,          removebuf  =          xon        ^Q q
                 [Press Space for next page; Return to end.]

Key bindings are the commands the screen accepts after you hit “Ctrl-A”. You can reconfigure these keys to your liking using a .screenrc file, but I just use the defaults.

Multiple Windows

Screen, like many windows managers, can support multiple windows. This is very useful for doing many tasks at the same time without opening new sessions. As a systems manager, I often have four or five SSH sessions going at the same time. In each of the shell, I may be running two or three tasks. Without screen, that would require 15 SSH sessions, logins, windows, etc. With screen, each system gets its own single session and I use screen to manage different tasks on that system.

To open a new window, you just use “Ctrl-A” “c”. This will create a new window for you with your default prompt. For example, I can be running top and then open a new window to do other things. Top stays running! It is still there. To try this for yourself, start up screen and then run top. (Note: I have truncated some screens to save space.)

Start top

  Mem:   506028K av,  500596K used,    5432K free,
    0K shrd,   11752K buff
    Swap: 1020116K av,   53320K used,  966796K free
              393660K cached
     PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %ME
     6538 root      25   0  1892 1892   596 R    49.1  0.3
     6614 root      16   0  1544 1544   668 S    28.3  0.3
     7198 admin     15   0  1108 1104   828 R     5.6  0.2

Now open a new window with “Ctrl-A” “c”

[admin@ensim admin]$

To get back to top, use “Ctrl-A “n”

   Mem:   506028K av,  500588K used,    5440K free,
    0K shrd,   11960K buff
    Swap: 1020116K av,   53320K used,  966796K free
              392220K cached
     PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %ME
     6538 root      25   0  1892 1892   596 R    48.3  0.3
     6614 root      15   0  1544 1544   668 S    30.7  0.3

You can create several windows and toggle through them with “Ctrl-A” “n” for the next window or “Ctrl-A” “p” for the previous window. Each process will keep running while your work elsewhere.

Leaving Screen

There are two ways to get out of screen. The first is just like logging out of a shell. You kill the window with “Ctrl-A” “K” or “exit” will work on some systems. This will kill the current windows. If you have other windows, you will drop into one of those. If this is the last window, then you will exit screen.

The second way to leave screen is to detach from a windows. This method leaves the process running and simple closes the window. If you have really long processes, you need to close your SSH program, you can detach from the window using “Ctrl-A” “d”. This will drop you into your shell. All screen windows are still there and you can re-attach to them later.

Attaching to Sessions

So you are using screen now and compiling that program. It is taking forever and suddenly your connection drops. Don’t worry screen will keep the compilation going. Login to your system and use the screen listing tool to see what sessions are running:

[root@gigan root]# screen -ls
There are screens on:
        31619.ttyp2.gigan       (Detached)
        4731.ttyp2.gigan        (Detached)
2 Sockets in /tmp/screens/S-root.

Here you see I have two different screen sessions. To re-attach to a session, use the re-attach command:

[root@gigan root]#screen -r 31619.ttyp2.gigan

Just use screen with the -r flag and the session name. You are now re-attached to the screen. A nice thing about this, is you can re-attach from anywhere. If you are at work or a clients office, you can use screen to start a job and then logout. When you get back to your office or home, you can login and get back to work.

Screen Logging

As a consultant, I find it important to keep track of what I do to someone’s server. Fortunately, screen makes this easy. Using “Ctrl-A” “H”, creates a running log of the session. Screen will keep appending data to the file through multiple sessions. Using the log function is very useful for capturing what you have done, especially if you are making a lot of changes. If something goes awry, you can look back through your logs.

Linux Screen Tips

Just wanted to mention to other cool tricks you can do with screen. Screen can monitor a window for activity or lack thereof. This is great if you are downloading large files, compiling, or watching for output. If you are downloading something or compiling, you can watch for silence. To start the monitor, go to the screen you want to monitor and use “Ctrl-A” “M” to look for activity or “Ctrl-A” “_” to monitor for silence. Then open or switch to a new window. When the monitor detects activity or silence, you will get an alert at the bottom with the window number. To quickly go to that window, use “Ctrl-A” “ (thats a quote mark, ctrl-a then a “). After you do this, just type in the number of the window and enter. To stop monitoring, go to that window and undo the monitor with the same command. For example, to stop monitoring for activity you would use “Ctrl-A” “M” again.

Reference

Screen was covered recently in Linux Magazine by Adam Lazur (Jan 2003, Issue 105). Much of his information was adapted for this rackTIP. Other information was collected from the man pages.

SYSSTAT: SAR/IOSTAT

The pidstat command is used to monitor processes and threads currently being managed by the Linux kernel. It can also monitor the children of those processes and threads.

With its -d option, pidstat can report I/O statistics, providing that you have a recent Linux kernel (2.6.20+) with the option CONFIG_TASK_IO_ACCOUNTING compiled in. So imagine that your system is undergoing heavy I/O and you want to know which tasks are generating them. You could then enter the following command:

$ pidstat -d 2
Linux 2.6.20 (localhost)    09/26/2007
10:13:31 AM       PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
10:13:31 AM     15625      1.98  16164.36      0.00  dd
10:13:33 AM       PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
10:13:33 AM     15625      4.00  20556.00      0.00  dd
10:13:35 AM       PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
10:13:35 AM     15625      0.00  10642.00      0.00  dd

When no PID’s are explicitly selected on the command line (as in the case above), the pidstat command examines all the tasks managed by the system but displays only those whose statistics are varying during the interval of time.

The sar utility (System Activity Reporter) is a system activity reporter that is quite popular with HP/UX and Solaris, and sar is also available for AIX. Just like top, sar gives detailed information about Oracle tasks from the UNIX level. You will be able to see the overall consumption of CPU, disk, memory, and Journal File System (JFS) buffer usage. There are three major flags that you can use with sar:

  • sar –u Shows CPU activity
  • sar –w Shows swapping activity
  • sar –b Shows buffer activity

NOTE: Each flavor of UNIX has a different implementation of sar. For example, some of the key flags used in the Sun version of sar are not available on HP/UX. The examples in this book show the HP/UX version of sar.

The output from sar reports usually shows a time-based snapshot of activity. This is true for all reports that you’ll see in this section. When you issue the sar command, you pass two numeric arguments. The first represents the time interval between samples, and the second represents the number of samples to take. For example:

L 6-4

sar –u 10 5

The sar command in this example is requesting five samples taken at 10-second intervals.

sar –w: The Memory Switching and Swapping Activity Report

The sar –w command is especially useful if you suspect that your database server
is experiencing a memory shortage. The following example shows the swapping activity report that you get from sar:

L 6-6

>sar -w 5 5

HP-UX corp-hp1 B.11.00 U 9000/800    12/25/01

07:19:33 swpin/s bswin/s swpot/s bswot/s pswch/s
07:19:38    0.00     0.0    0.00     0.0     261
07:19:43    0.00     0.0    0.00     0.0     231
07:19:48    0.00     0.0    0.00     0.0     326
07:19:53    0.00     0.0    0.00     0.0     403
07:19:58    0.00     0.0    0.00     0.0     264

Average     0.00     0.0    0.00     0.0     297

The column descriptions are as follows:

  • swpin/s Number of process swap-ins per second.
  • swpot/s Number of process swap-outs per second.
  • bswin/s Number of 512-byte swap-ins per second.
  • bswot/s Number of 512-byte swap-outs per second.
  • pswch/s Number of process context switches per second.

With sar you can watch realtime the network usage:

# sar -n DEV 1 0
Linux 2.6.22-15-generic (xXxXx)  07/09/2008
11:26:36 AM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s
11:26:37 AM        lo      0.00      0.00      0.00      0.00      0.00      0.00      0.00
11:26:37 AM      eth0      5.05      0.00      0.86      0.00      0.00      0.00      0.00
11:26:37 AM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s
11:26:38 AM        lo      0.00      0.00      0.00      0.00      0.00      0.00      0.00
11:26:38 AM      eth0      4.00      0.00      0.45      0.00      0.00      0.00      0.00

iostat
Display a single history since boot report for all CPU and
Devices.

iostat -d 2
Display a continuous device report at two second intervals.

iostat -d 2 6
Display six reports at two second intervals for all devices.

iostat -x hda hdb 2 6
Display six reports of extended statistics at two second inter-
vals for devices hda and hdb.

iostat -p sda 2 6
Display six reports at two second intervals for device sda and
all its partitions (sda1, etc.)

Display Disk IO Statistics using sar command

# sar –d

Linux 2.6.9-42.ELsmp (dev-db)        01/01/2009
12:00:01 AM    DEV              tps    rd_sec/s  wr_sec/s
12:05:01 AM    dev2-0           1.65      1.28     45.43
12:10:01 AM    dev8-1          4.08      8.11     21.81

Skipped..

Average:       dev2-0           4.66    120.77     69.45
Average:       dev8-1          1.89      3.17      8.02

Display networking Statistics using sar command

# sar -n DEV | more

Linux 2.6.9-42.ELsmp (dev-db)        01/01/2009
12:00:01 AM     IFACE   rxpck/s   txpck/s   rxbyt/s   txbyt/s   rxcmp/s   txcmp/
s  rxmcst/s
12:05:01 AM        lo      0.17      0.16     25.31     23.33      0.00      0.0
0      0.00
12:10:01 AM      eth0     52.92     53.64  10169.74  12178.57      0.00      0.0
0      0.00

# sar -n SOCK |more

Linux 2.6.9-42.ELsmp (dev-db)        01/01/2009
12:00:01 AM    totsck    tcpsck    udpsck    rawsck   ip-frag
12:05:01 AM        50        13         3         0         0
12:10:01 AM        50        13         4         0         0
12:15:01 AM        53        13         5         0         0