Networking

Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.

Applications

Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.

Data

Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.

Monitoring

Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » Commands & Shells, MySQL

Database operations with SQL and Korn shell

Submitted by on November 20, 2005 – 11:46 am 3 Comments

The following Korn shell function will read the a directory containing CSV data files named server_sysstat.20051119.csv with format:

server_2,7,1461,2,2195979,90,00:00:17,42
server_2,6,1467,2,2200018,85,00:02:18,42
server_2,6,1462,2,2198306,89,00:04:20,42

The function will read each file line by line and do either an insert to or an update of the database table. It is important to remember that, if your source data contains single quotes (apostrophes), you will need to preceed all single quotes with the backward slash (use three slashes “\” with KSH).

For example:

country=$(echo "$LINE" | awk -F'@' '{print $2}' | sed "s+'+\'+g")

And here’s the script:

SQL_Diskmon_Sysstat_Func() {

	ls "$INPUTDIR3" | sort | fgrep "_sysstat." | while read REPORT
	do
		year=$(echo "$REPORT" | awk -F'.' '{print $2}' | cut -c 1-4)
		month=$(echo "$REPORT" | awk -F'.' '{print $2}' | cut -c 5-6)
		day=$(echo "$REPORT" | awk -F'.' '{print $2}' | cut -c 7-8)
		DATE=$(echo "${year}-${month}-${day}")

		cat "${INPUTDIR3}/${REPORT}" | while read LINE
		do
			STORAGE_UNIT=$(echo "$LINE" | awk -F',' '{print $1}')
			VAR2=$(echo "$LINE" | awk -F',' '{print $2}')
			VAR3=$(echo "$LINE" | awk -F',' '{print $3}')
			VAR4=$(echo "$LINE" | awk -F',' '{print $4}')
			VAR5=$(echo "$LINE" | awk -F',' '{print $5}')
			VAR6=$(echo "$LINE" | awk -F',' '{print $6}')
			TIME=$(echo "$LINE" | awk -F',' '{print $7}')
			DATETIME=$(echo "$DATE $TIME")
			VAR8=$(echo "$LINE" | awk -F',' '{print $8}')

			echo "$STORAGE_UNIT,$DATETIME,$VAR2,$VAR3,$VAR4,$VAR5,$VAR6,$VAR8" >> "$TMPF3"
			echo "$STORAGE_UNIT,$DATETIME,$VAR2,$VAR3,$VAR4,$VAR5,$VAR6,$VAR8"
		done
	done

	if [ -f "$TMPF3" ]
	then
		cat "$TMPF3" | while read LINE
		do
			HOST_NAME=$(echo "$LINE" | awk -F',' '{print $1}')
			DATETIME=$(echo "$LINE" | awk -F',' '{print $2}')
			VAR3=$(echo "$LINE" | awk -F',' '{print $3}')
			VAR4=$(echo "$LINE" | awk -F',' '{print $4}')
			VAR5=$(echo "$LINE" | awk -F',' '{print $5}')
			VAR6=$(echo "$LINE" | awk -F',' '{print $6}')
			VAR7=$(echo "$LINE" | awk -F',' '{print $7}')
			VAR8=$(echo "$LINE" | awk -F',' '{print $8}')

			$MYSQL -u$DBUSER -p$DBPASS $DBNAME << EOF | wc -l | awk '{print $1}' | read COUNT
			SELECT * FROM sandisk_performance WHERE storage_unit='$STORAGE_UNIT' AND line_datetime='$DATETIME' 
			AND threads_running='$VAR3' AND threads_blocked='$VAR4' AND var5='$VAR5' AND free_memory='$VAR6' 
			AND cpu_idle='$VAR7' AND cpu_threashold='$VAR8';
EOF

			if [ $COUNT -eq 0 ]
			then
			echo "Inserting report for : ${STORAGE_UNIT}($DATETIME)"
				$MYSQL -u$DBUSER -p$DBPASS $DBNAME << EOF
				INSERT INTO

				sandisk_performance(storage_unit,line_datetime,threads_running,threads_blocked,var5,
				free_memory,cpu_idle,cpu_threashold)
				values('$STORAGE_UNIT','$DATETIME','$VAR3','$VAR4','$VAR5','$VAR6',
				'$VAR7','$VAR8');
EOF
			else
				echo "Updating report for : ${STORAGE_UNIT}($DATETIME)"
				$MYSQL -u$DBUSER -p$DBPASS $DBNAME << EOF
				UPDATE sandisk_performance SET
				storage_unit='$STORAGE_UNIT',
				line_datetime='$DATETIME',
				threads_running='$VAR3',
				threads_blocked='$VAR4',
				var5='$VAR5',
				free_memory='$VAR6',
				cpu_idle='$VAR7',
				cpu_threashold='$VAR8'

				WHERE storage_unit='$STORAGE_UNIT' AND line_datetime='$DATETIME' 
				AND threads_running='$VAR3' AND threads_blocked='$VAR4' AND var5='$VAR5' AND free_memory='$VAR6' 
				AND cpu_idle='$VAR7' AND cpu_threashold='$VAR8';
EOF
			fi
		done
	fi
}

Print Friendly, PDF & Email

3 Comments »

  • Gamer959 says:

    How or where i can write a C programme in Linux Operating System. Also the way how can i run this programme.
    2) There is two commands one for compiling and other for the running the programme in the Linux Waht are they!!!!
    Please help i have the assignments on it…………..

  • clntvrrt says:

    For instance. i have a database table. this table has 31 rows in it. the first is owner which determines who “owns” the list. the rest are called “slot” followed by a number in order. “slot1” “slot2” all the the way to slot30.

    what im trying to do is add to this table a value in the next empty slot.

    i’m unsure how to achieve that however.

  • mike s says:

    i’m using the viscual basic 2008 …..and i can’t figure out how to transfer information from my textbox to a access database.table.

Leave a Reply

%d bloggers like this: