Friday, March 30, 2007

Display CPU, UPA, PCI or SBus Speeds

You can use the .speed command to display the speeds for the CPU, UPA, PCI or SBus. This command is an OpenBoot command. Here's an example of its use.

Note the "." preceding speed
ok .speed

Thursday, March 29, 2007

Setup an Internet Relay Chat (IRC) Server

Here is the official organizational site of the IRCD-Hybrid Project. You'll be able to find everything you'll need to get started in hosting your own Internet Relay Chat or IRC server as well as support. I've been using it for years without a problem.

IRCD-Hybrid Project

Restrict FTP User Access to Host

If you want to disallow/deny certain login(s) to your workstation via File Transfer Protocol (FTP), you will need to create a file called /etc/ftpusers because it is not a default file. Add the login name or login names to the file. For security concerns, it is recommended that the root user be added to this file. Below is an illustration of adding login names.

# vi /etc/ftpusers
root
anonymous
esofthub
user1
user2
user3

User4
:wq! (saves and quits vi)

Wednesday, March 28, 2007

Automate FTP Connection to Remote Host

The $HOME/.netrc file allows a local user to FTP to a remote destination in an automated manner. The presence of a .netrc file is usually due to a particular activity that requires frequent FTP transfers to a (presumably) trusted host. To setup the remote connection, here’s an example of its syntax.

#vi $HOME/.netrc
machine myfake_IP login myfake_login password myfake_password
:wq! (saves and quits vi)

The machine can be an IP or URL.

Note: Your IT or security department may prohibit or restrict the use of a .netrc file.

Tuesday, March 27, 2007

Determine POP3 or Sendmail Availability

After an upgrade, I had a problem pulling mail from the mail server. Performed a telnet action to confirm the pop3 configuration. The mail server didn't connect. Usually the POP server and mail service are on the same box.

This should connect to the mail server
#telnet server 110
Trying server_IP...
telnet: Unable to connect to remote host: Connection refused

Added the following line:
#vi /etc/inetd/inetd.conf
pop3 stream tcp6 nowait root /usr/local/sbin/popper
:wq! (saves and quits vi)

#ps -ef | grep inetd
#kill -HUP inetd_PID
#telnet server 110

You can also check if the Sendmail and ircd services are available.
#telnet server 25
#telnet server 6667

Monday, March 26, 2007

Defined Shell Determines FTP user Access

I had a chat with a colleague today regarding FTP access. For some reason, it made me think of the /etc/shells file. This file allows an FTP user access to the system if his/her shell is defined in it. An example is shown below.

By the way, this is a user created file.
#cat > /etc/shells
/sbin/sh
/bin/sh
/bin/csh
/bin/ksh
/bin/bash
/bin/tcsh
control ^d

Saturday, March 24, 2007

Solaris Operating System - Freeware

I found another freeware link for Sun software. It's mostly OS related. You will probably have to register with Sun to complete the downloads.

Here's what is available.
-- Solaris 10 11/06 OS Companion Software DVD downloads
-- Solaris 9 Companion Software CD downloads
-- Solaris 8 Companion Software CD downloads

Solaris Operating System - Freeware

Friday, March 23, 2007

Delete Group Account with groupdel via Command Line

I'm going to delete the group account, unixgrp, using the groupdel command. Here's an example of its use.

#groupdel unixgrp

Or use GUI

#admintool &

Modify a Group Account via groupmod via Command Line

I going to modify the unixgrp account ID from 1000 to 1001. This is a simple task.

#groupmod -g 1001 unixgrp

Alternate method

#admintool &

Add a Group Account with groupadd via Command Line

Adding groups to Solaris is easy, too. Here's an example.

#groupadd -g 1000 unixgrp

By way of GUI, use admintool.

#admintool &

Delete a User Account with userdel via Command Line

I'm going to delete the sysadmin1 account and its home directory using the userdel command. Here's an illustration of its use.

#userdel -r sysadmin1

You can use admintool to delete the account, too.

#admintool &

Modify a User Account with usermod via Command Line

I'm building on the previous post. Now I'm going to modify the user name, esofthub, to something else. Here's an example.

#usermod -d /export/home/sysadmin1 -m sysadmin1 esofthub

I've changed the directory and login of esofthub to sysadmin1.

If you don't like this method, use admintool.

#admintool &

Add a User with useradd via Command Line

This post is for the admins who have an affinity for command line. I'm going to add a user via command line. This is a fairly easy process once you get the syntax down. Here's a simple example.

#useradd -u 2000 -g 1000 -d /export/home/esofthub -m -s /bin/csh -c "SysAd Account" esofthub

I've added a user with a user ID of 2000, group ID of 1000, a home directory called esofthub, set shell to c shell, added a comment, and user is called esofthub.

If you don't like the command tool method, use the GUI utility, admintool.

#admintool &

Wednesday, March 21, 2007

Download Free Solaris Freeware (Software Packages)

Here's a site that is celebrating 14 years as the #1 site for free software packages for Solaris. Currently offering more than 30 GB of packages for Solaris 2.5-10 and used by over 340,000 sites around the world.

In the past, I've downloaded software packages such as apache, perl, qpopper, tk, tcl, xv and etc from this site. No problems.

Download Free Solaris Freeware

Remove Software Package from Operating System

The pkgrm utility will remove files and directories associated with a software package. It will also warn about package dependencies---be mindful of those messages. Here's an example.

#pkgrm SUNWns

Check if Software Package was Installed Correctly

The pkgchk command checks whether a software package was installed correctly or not. If the pkgchk returns no message, the package was successfully installed. Here's an example.

#pkgchk SUNWns

Add a Software Package with pkgadd Utility

Here's how you add packages to the Solaris operating system. As you can see, it's fairly self-explanatory. Here are a few examples.

From CDROM
#cd /cdrom/cdrom0/s0/Solaris_X/Product
#pkgadd -d . SUNWpackage

Another example is a package that was copied to the /tmp directory
#pkgadd -d /tmp SUNWpackage

Install Netscape 7.0. Download the zip file to /tmp. Ensure required patches are installed.
#cd /tmp
#unzip NSinstaller.zip
#cd NSinstaller
#./NSinstaller
Go to installation directory
#cd /opt/SUNWns
# ./netscape &
or
#cd /tmp
#unzip downloadfile.zip
#cd unzipped_directory
#pkgadd -d . SUNWnsxp SUNWnsm SUNWnsb SUNWnspsm
Go to installation directory
#cd/opt/SUNWns
#./netscape &

Display Information about Software Packages

Here's the command to display information about the packages installed on a workstation or server.

#pkginfo | pg
or
#pkginfo | more

How many software packages are installed on a workstation or server?
#pkginfo | wc -l

Get specific information about a software package
#pkginfo -l SUNWns

List the packages on a CDROM
#pkginfo -d /cdrom/cdrom0/Solaris_X/Product | more

Tuesday, March 20, 2007

Create SQL Insert Script with Inline Korn Script

We had to insert 400+ rows by 7 columns of data into an Oracle database table. Here is the inline script I used to create the SQL script file, which will be used to perform the inserts. By the way, we were having problems using the imp/exp utilities, so we opted for this method.

Change shell to Korn
# ksh
while read -r a b c d e f g
do
printf "%s %s %s %s %s %s %s\n" "insert into db_table values('$a'"",""$b"",""$c"",""$d"",""$e"",""$f"",""$g);"
done<mylist>>insert_file.sql

# more insert_file.sql
insert into db_table values('Phoenix', 95, 89, 89, 99, 89, 93);
insert into db_table values('Tucson', 87, 88, 85, 90, 88, 91);
insert into db_table values('Las Vegas', 83, 85, 85, 84, 85, 89);
insert into db_table values('Reno', 53, null, 55, 54, 55, 59);
...
...

Here's the script version

# vi db_parser.ksh
#!/bin/ksh
while read -r a b c d e f g
do
printf "%s %s %s %s %s %s %s\n" "insert into db_table values('$a'"",""$b"",""$c"",""$d"",""$e"",""$f"",""$g);"
done<$1>>$2.sql
:wq! (saves and quits vi)

Turn on the read and execute bits for all and then run the script.
# chmod 555 db_parser.ksh
# ./db_parser.ksh mylist insert_file

# more insert_file.sql
insert into db_table values('Phoenix', 95, 89, 89, 99, 89, 93);
insert into db_table values('Tucson', 87, 88, 85, 90, 88, 91);
insert into db_table values('Las Vegas', 83, 85, 85, 84, 85, 89);
insert into db_table values('Reno', 53, null, 55, 54, 55, 59);
...
...

# su - oracle
# sqlplus
**you will be prompt for login/password information**
SQL>@insert_file.sql;
--The "@" symbol executes the contents in the insert_file.sql file
--If you have errors/modifications, simply open the insert_file.sql in vi/nedit/textedit/emacs and make the necessary changes
--Then rerun the file using the "@" symbol

Monday, March 19, 2007

Determine Inode Count for Filesystem

It was determined that the inode count on one of the RAID's filesystems was inadequate to meet the demands of a new project. There was ample space available but not enough inodes. We had to recreate the filesystem with the newfs utility to accommodate more inodes.

Determine inode count on all UFS filesystems
#/usr/bin/df -F ufs -o i

Extra: List inode example
#ls -i /

Sunday, March 18, 2007

Display Workstation or Server System Information via GUI

wsinfo is a handy gui utility. It is used to display system information such as workstation name, workstation type (e.g. Sun Fire v490), host ID, Internet address, network domain, physical memory (RAM), virtual memory (Swap), virtual memory in use, operating system version, and windows system version. It's located in the /usr/openwin/bin directory. I use it to check for virtual memory in use or to see how much RAM is configured.

#/usr/openwin/bin/wsinfo &

Saturday, March 17, 2007

How to Perform an Interactive Restore with ufsrestore

I had to retrieve a configuration file from an archive of the /var/local directory. I assumed records were archived with the “-n” option, i.e. /dev/rmt/0hn. This example is a bit oversimplied, but you should get the gist. Here are the steps for an interactive restore. But first, I will need to move to the appropriate record on the tape.

#cd /var/tmp
#mt –f /dev/rmt/0 rewind
#mt -f /dev/rmt/0 fsf 3 (4th record)
#ufsrestore ivf /dev/rmt/0
ufsrestore>ls
local
ufsrestore>cd local
ufsrestore>ls
ircd
ufsrestore>cd ircd
ufsrestore>ls
conf
ufsrestore>cd conf
ufsrestore>ls
ircd.conf
ufsrestore>add ircd.conf
ufsrestore>ls
*ircd.conf (* asterisk marks for extraction)
ufsrestore>extract
specify next volume #: 1
set owner/mode for '.'? [yn] n
ufsrestore>quit

#mt -f /dev/rmt/0 offline (rewinds and ejects tape)
#cp -p /var/tmp/ircd.conf /var/local/ircd/conf/ircd.conf
#chown irc:irc /var/local/ircd/conf/ircd.conf
#rm /var/tmp/ircd.conf

Friday, March 16, 2007

Find a Record on a Data Tape

To find a record written to tape, you will want to use the mt utility. In this example, I'm looking for the 5th record on the tape, and I'm assuming the tape was rewound. Here's the procedure.

# mt -f /dev/rmt/0n fsf 4

Client Workstations Can't Print to the Print Server

Several years ago, I was having problems printing jobs from a workstation that was not configured as a print server. As it turned out, the port listening device, in.lpd, was commented out in the /etc/inet/inetd.conf file. I uncommented the entry and then rebooted. Instead of rebooting, I could have done a hangup of inetd's PID.

On the print server workstation, uncomment the port listening device (in.lpd)
# vi /etc/inet/inetd.conf
#
# Print Protocol Adaptor - BSD listener
#
#printer stream tcp nowait root /usr/lib/print/in.lpd in.lpd
printer stream tcp nowait root /usr/lib/print/in.lpd in.lpd
:wq! (saves and quits vi)

Instead of rebooting
# ps -ef | grep inetd
# kill -HUP inetd_PID
or
# pkill -HUP inetd

Client Workstations Access to Print Server

Here's how the client workstations access the locally configured print server. Again, the admintool utility was used to perform this task. The print server was configured via parallel port.

#admintool &
Select Browse -> Printers
Select Edit -> Add -> Access to Printer
Enter Printer Name
Enter Print Server's name
Enter Printer Description
Select Default Printer
Press OK
Select File -> Exit

Setup a Local Printer Configuration

Per the customer's request, a local print server was setup on a workstation. The printer was connected to workstation via the parallel port. The admintool utility was used to perform the setup. Here are the steps.

#admintool &
Select Browse -> Printers
Select Edit -> Add -> Local Printer
Enter Printer Name
Enter Printer Server
Enter Printer Description
Parallel Port -- usually /dev/ecpp0
Enter Printer Type -- usually Postscript
Enter File Contents -- usually Postscript
Select Default Printer
Press OK
Select File -> Exit

Thursday, March 15, 2007

Display Space Used by Users on Filesystems

A workstation's filesystem was filling up yesterday. We used the quot command to determine the space hogs.

Shows users on all mounted file systems. The first column is space used in kilobytes, the second column is the number of files and the third column is the users.
#quot -af

Shows users on a specific filesystem
#quot -f /dev/dsk/c1t1d0s5

Wednesday, March 14, 2007

Remove a Patch from a Solaris Operating System

Here's the command to remove a patch from the Solaris operating system. In the last post, I installed the Sun XVR-100 graphics accelerator patch. For illustration purposes, I'm going to remove it.

#patchrm 114537-40

Check for 114537-40 and it should not be detected
#showrev -p | grep 114537-40
or
#patchadd -p | grep 114537-40

Install a Solaris Patch Cluster

After the download, I'm now ready to install the patch cluster. I copied the cluster zip file into the /var/tmp directory.

#cd /var/tmp
#unzip J2SE_Solaris_8_Recommended.zip
#cd J2SE_Solaris_8_Recommended
#./install_cluster

Once the patch cluster is installed, you should perform a reboot.
#reboot

You can also install patches singly. This is an example of installing the Sun XVR-100 graphics accelerator patch.

#
cd /var/tmp
#unzip 114537-40.zip
#patchadd 114537-40

Download Sun Solaris Cluster Patches

Here is a convenient link to download the Recommended Sun Solaris cluster patches via Sun’s SunSolve website. There is also a patch finder on the web page and all you have to do is enter the patch ID or patch ID with revision. I find the patch finder is a very handy tool and use it frequently. The other day I used it to quickly find a patch for an LTO3 drive.

By the way, I had to register to download the cluster patch. Here is the link to the download site.

Download Sun Patches

Check Solaris Patch Levels

I had to upgrade a workstation today and needed to check the patch levels. Here's the commands.

# showrev -p
or
# patchadd -p

Recommend you use showrev -p since it's a binary program. The patchadd utility is a script; therefore, it takes awhile to run.

Recover Filesystem's Primary Superblock with FSCK

While running fsck utility, we encountered a problem with the primary superblock. We were then prompted to recover from an alternative superblock. Remember to specify the rdsk and know that block 32 is always an alternative superblock.

# fsck - o b=32 /dev/rdsk/c0t0d0s6

Determine which superblocks are available
newfs -Nv /dev/rdsk/c0t0d0s6

Monday, March 12, 2007

Stop and Start the Volume Manager Daemon

If you deal with UNIX enough, you will have to stop and start the volume manager daemon on more than one occasion.

#/etc/init.d/volmgt stop; /etc/init.d/volmgt start
or
#/etc/rc2.d/S92volmgt stop; /etc/rc2.d/S92volmgt start
or
#vold

Allow Clients to Access Applications on the Server

For many applications, a single application on the server is maintained and then network file system (NFS) to the workstations. This allows users virtual access to the application. The centralize location also helps reduce maintenance actions. One example is the Zircon client application. Below is an example.

Of course, any application configured in this manner should be licensed for whatever number of instances you might invoke.

Add the following lines
#vi /etc/dfs/dfstab
share -F nfs -o rw=client1:client2:client3:client4:client5,root=client1:client2:client3:client4:client5 -d "Zircon Client" /export/opt/zircon
:wq! (saves and quits vi)

It is assumed the host information for server and clients is included in files, nis, nis+ or whatever naming service you are using.

Next we need to share it out
#shareall

#vi /etc/auto_direct
/opt/zircon bg,soft server:/export/opt/zircon
:wq! (saves and quits vi)

#vi /etc/auto_master
/- auto_direct
:wq! (saves and quits vi)

#automount -v

Launch Zircon application from the workstation via command line or menu. Of course, you will need to configure your menu options.

Allow Clients Access to Home Directories on Server

Home directories are usually maintained in a central location. In most cases, that would be the server. The clients workstations must have access to the home directories on the server. Here is a quick and dirty way of doing it.

Add the following lines to the dfstab file
# vi /etc/dfs/dfstab
share -F nfs -o rw=client1:client2:client3:client4:client5,root=client1:client2:client3:client4:client5 -d "The home directories on the server" /export/home
:wq! (saves and quits vi)

It is assumed the host information for server and clients is included in files, nis, nis+ or whatever naming service you are using.

Next we need to share it out
# shareall

# vi /etc/auto_home
* server:/export/home/&
+auto_home
:wq! (saves and quits vi)

# vi /etc/auto_master
/home auto_home -nosuid,nobrowse
:wq! (saves and quits vi)

#automount -v

Login from the workstation

Saturday, March 10, 2007

Characters not Recommended in Filename Convention

A filename should not contain any of the characters shown below. Why? They have special meaning to the shell. Technically speaking, you can use them in the composition of a filename, but you might have a difficult time accessing or deleting them in the future.

Characters not recommended in the composition of a filename.
, ! @ # $ % ^ & * ( ) - + [ ] { } < > ? ; ' " | \ / ~ ` : "space"

Recommended characters
_ 0-9 a-z A-Z .

Acceptable filenames
ABC.txt
_this_is_good
amc.txt
123.txt

Friday, March 09, 2007

Filename Completion Wild Card Characters

Here are a few common uses for completing filenames with wild cards. I will demonstrate the "*", "?", and "[]" wild card options.

The asterisk, "*", represents any sequence of characters.

Prints all files that end with "2007.txt" eg. file12007.txt, file22007.txt, etc.
#lp *2007.txt
Prints all files that end with "txt" in the current directory
#lp *txt

Removes everything in the current directory (BE CAREFUL!)
#rm *
#rm -i * (interactive)

List all files that have "Feb" in its filename
#ls -l *Feb*

The question mark, "?", represents one character.
Print all documents in the current directory that contains report.1(any single character).txt eg. report.2a.txt, report.20.txt, report.21.txt, etc.
#lp report.2?.txt
#cp -p report1?.txt archive

The brackets, "[]", represent one character within a range.
Print all documents in the current directory that contains report.1[any character in a range].txt eg. report.20.txt, report.21.txt, report.29.txt.

#lp report.2[0-9].txt
#cp -p report1[0-9].txt archive
#rm report1[0-9].txt

Thursday, March 08, 2007

Determine the Time to Execute a Process

Sometimes you may want to determine how long it takes to execute a UNIX utility or script via the command line, especially during testing. Here's a simple way of doing that. Use the time command.

#time -p rcp -p serverbox:/home/esofthub/100MB.dat /directory_on_local

#timex ls

Determine the Command Type or Description

The type utility will tell you what type of command you are executing. It will tell you if the command is a function, shell built-in, hashed command, alias, or keyword. It will also display the pathnames for some commands.

Here's an example.

#type ls

Wednesday, March 07, 2007

Add a Patch to the Solaris Operating System

We were having display problems with a newly installed application. It was later determined that five required Open GL patches were missing. We used the patchadd command to install them.

Search for patch IDs
#showrev -p | grep patch_number

Used an inline script to install the patches
#sh
#cd /to_location_of_patches
#for i in patch1 patch2 patch3 patch4 patch5
do
patchadd $i
done

Tuesday, March 06, 2007

Create a Shell Function via the Command Line

On occasion we create shell functions on the command line to facilitate system administration tasks. Their clear advantage is speed because they are stored in a computer's main memory. These functions can also be setup in a script file or in a .profile file.

Here's an example.
#currentusers
{
date
echo Users currently on the system
who
}

Displays the the users who are currently logged on
#currentusers

To unset the function
#unset currentusers

And here's one more example.
#pingtest
{
date
for i in 1 2 3 4 5
do
ping wkstn$i
done
echo pingtest done
}

Shows workstation pings
#pingtest

To unset the function
#unset pingtest

Problem Changing the root Password

It was that time again. We had to change the root password. I changed it on the first workstation and it failed. Why? The /etc/nsswitch.conf file was initially setup for NIS+ and then files. I had to swap them.

Here's the error message: "passwd: Unsupported switch entry for "passwrd:", Use "-r repository". Unexpected failure. Password file/table unchanged.

Here's how it was setup.

passwd: nisplus files
group: nisplus files

Change to the following:
# vi /etc/nsswitch.conf
passwd: files nisplus
group: files nisplus
...
:wq! (saves and quits vi)

Then I proceeded with the root password change
# passwd root

...or as Cass pointed out

For Files
# passwd -r files root
# passwd -r file username

For NIS+
# passwd -r nisplus username

Sunday, March 04, 2007

Perform Recursive Pattern Searches on Files

As a UNIX systems administrator, I am frequently asked how to find a particular pattern or string in a file in which the location of the file is unknown. This is a nifty recursive way of doing that. I routinely use it on the command line.

# /usr/bin/find /etc –type f | xargs grep –i PATTERN

These types of searches can take awhile depending on which directories you are searching.

If you have an alternate method, please share it.

Here's a refined method given by ux-admin
ux-admin said...

Tip: to speed the search up, use "-depth", and a case-sensitive `grep`, like so:

find /a/path/ -depth -type f -print | xargs grep the-string-you-are-looking-for

Compare Files and Directories

Here are two commands used for file and directory comparisons. Diff is for files and dircmp is for directories. Personally, I routinely use the diff command to compare files. Here are examples of their use.

Compare two files
# diff file1 file2

Show file differences a screenful at a time if output is greater than a screen
#diff files1 files2 | pg

Compare two directories
#dircmp dir1 dir2

Output to Screen and Append to a File

There are a few occasions when you might want to display the output of a command and then capture its output to a file at the same time. Here is an example below.

File capture_file is created and then appended to by the tee command.
# date > capture_file; ls -l /etc | tee -a capture_file

Here is another example of output going to the screen and then appended to a file.

# echo " " >> capture_file
# echo " " >> capture_file
# cat myfile1 | tee -a capture_file
# cat myfile2 | tee -a capture_file

Schedule a Job At a Specified Time

We were required to dump of our production database prior to an upgrade. Since it was only going to be a one time affair, we didn't want to bother setting up a cronjob. So we decided to use the AT command to schedule the task. We used the "-m" option to send us mail to confirm its completion.

Note: Your login must be in the /etc/cron.d/at.allow file.

#cd /opt/db/scripts
#at -m 4am < dumpDB.sh
job 48736701.a at Sat Mar 3 12:34:00 2007

Displays a list of jobs in the queue
#at -l
user = esofthub 48736700.a Sat Mar 3 12:34:00 2007
user = esofthub 48736701.a Sat Mar 3 12:34:00 2007

Removes job from the queue
#at -r 48736700.a

MISC:
#at 6am
lp -d not_default_printer customer_taskfile
control ^d
job 48736702.a at Sat Mar 3 12:36:00 2007

#at 6am Mon
lp -d not_default_printer customer_taskfile
control ^d
job 48736703.a at Sat Mar 3 12:37:00 2007

#at -m 0700 Tue +2 weeks < print_accesslog_stats.sh
job 48736704.a at Sat Mar 3 12:38:00 2007


Friday, March 02, 2007

Delete a Row From a Database Table

Deleting rows from a database table is fairly simple, too. Here is the syntax to delete row(s) containing the CUST_ID attribute 10001. I wouldn't have added this post but I noticed the SQL action was being routinely searched on via a meta data program I'm using.

Here's the SQL syntax.

ORACLE
SQL> delete from CUSTOMER where CUST_ID = 10001;

SYBASE
1> delete from CUSTOMER where CUST_ID = 10001
2> go

The ";" is the execute command for Oracle and "go" is the execute command for Sybase.

Update Value in Database Table Oracle or Sybase

Here is the syntax to update a database table. Again, this is a fairly straightforward task. In this example, we are changing two values where CUST_ID = 10001.

ORACLE
SQL> update CUSTOMER set CUST_SSN_ID = '111-11-1234', CUST_TITLE = 'Gen Mgr' where CUST_ID = 10001;

SYBASE
1> update CUSTOMER set CUST_SSN_ID = '111-11-1234', CUST_TITLE = 'Gen Mgr' where CUST_ID = 10001
2> go

The ";" is the execute command for Oracle and "go" is the execute command for Sybase.

Update (No pun intended): I had a few folks ask me about MySQL syntax.

UPDATE people SET CUST_SSN_ID = "111-11-1234", CUST_TITLE= "Gen Mgr" WHERE CUST_ID = 10001

Create Database Table in Oracle or Sybase

Here's the syntax to create a database table with datatypes defined. It's a fairly straightforward task.

ORACLE
SQL> CREATE TABLE CUSTOMER (
CUST_ID NUMBER(6) PRIMARY KEY,
CUST_TITLE VARCHAR2(10) NOT NULL,
CUST_HOME_AREA_CODE NUMBER(3) NOT NULL,
CUST_HPHONE VARCHAR2(8) NOT NULL,
CUST_WORK_AREA_CODE NUMBER(3) NOT NULL,
CUST_WPHONE VARCHAR2(8) NOT NULL,
CUST_STREET VARCHAR2(30) NOT NULL,
DATE_ACCT_OPENED DATE NOT NULL,
CUST_SSN_ID VARCHAR2(11) NOT NULL,
CUST_ZIP_ID NUMBER(5) NOT NULL,
FOREIGN KEY (CUST_SSN_ID) REFERENCES CUSTOMER ON DELETE CASCADE,
FOREIGN KEY (CUST_ZIP_ID) REFERENCES ZIPCODE);

SYBASE
1> CREATE TABLE CUSTOMER (
CUST_ID NUMERIC (6,0) PRIMARY KEY,
CUST_TITLE VARCHAR (10) NOT NULL,
CUST_HOME_AREA_CODE NUMERIC (3,0) NOT NULL,
CUST_HPHONE VARCHAR (8) NOT NULL,
CUST_WORK_AREA_CODE NUMERIC (3,0) NOT NULL,
CUST_WPHONE VARCHAR (8) NOT NULL,
CUST_STREET VARCHAR (30) NOT NULL,
DATE_ACCT_OPENED DATE NOT NULL,
CUST_SSN_ID VARCHAR (11) NOT NULL,
CUST_ZIP_ID NUMERIC (5,0) NOT NULL,
FOREIGN KEY (CUST_SSN_ID) REFERENCES CUSTOMER ON DELETE CASCADE,
FOREIGN KEY (CUST_ZIP_ID) REFERENCES ZIPCODE)
2> go

The ";" is the execute command for Oracle and "go" is the execute command for Sybase.

Insert a Row into a Database Table

Inserting a row into a database table is a fairly easy task as long as the the parent keys are populated. In this example, we assume the parent keys have been populated. Here's an illustration.

ORACLE
SQL> insert into CUSTOMER VALUES (10001,'GM',628,'111-0000',628,'111-1111','123 Main St.','3-12-1999','111-11-1111',89897);

SYBASE
1> insert into CUSTOMER VALUES (10001,'GM',628,'111-0000',628,'111-1111','123 Main St.','3-12-1999','111-11-1111',89897)
2>go

If the parent key(s) is/are not populated, you will get an "integrity constraint violated - parent key not found" error.

The ";" is the execute command for Oracle and "go" is the execute command for Sybase.

Thursday, March 01, 2007

Protect the Literal Meaning of Special Characters

If you want to preserve the literal meaning of a special character, you will need to use an escape character. Here's an example of its use.

#echo "For My SysAd Blog, the post value of \$number is $number."
For My SysAd Blog, the post value of \$number is 126.

#echo "For \"My SysAd Blog,\" the post value of \$number is $number."
For "My SysAd Blog," the post value of \$number is 126.

Protect Strings Within Double Quotes

If you want quotes around a string, use single quotes to preserve the double quotes. Here's an example of its use.

Here's the syntax and its output
#echo 'Your single quotes "PRESERVED" the double quotes.'
Your single quotes "PRESERVED" the double quotes.

#echo 'Your single quotes "HAVE PRESERVED" the double quotes.'
Your single quotes "HAVE PRESERVED" the double quotes.