Use sqsh, not isql!

Sqsh is a sql shell and a far superior alternative to the isql program supplied by Sybase. It's main advantage is that it allows you to combine sql and unix shell commands! Here are a few reason why I love it:

1. Pipe data to other programs
You can create a pipeline to pass SQL results to an external (or unix) program like less, grep, head etc. Here are a few examples:

# pipe to less to browse data
1> select * from data; | less

# a more complex pipeline which gzips data containing a specific word
2> select * from data; | grep -i foo | gzip -c > /tmp/foo.gz

# this example shows the use of command substitution
3> sp_who; | grep `hostname`

2. Redirect output to file
Just like in a standard unix shell, you can redirect output of a sql command to file:

# write the output to file
1> sp_helptext my_proc; > /tmp/my_proc.txt

3. Functions and aliases
You can define aliases and functions in your ~/.sqshrc file for code that you run frequently. Some of mine are shown below. (Visit my GitHub dotfiles repository to see my full .sqshrc.)

\alias h='\history'

# shortcut for select * from
\func -x sf
    \if [ $# -eq 0 ]
        \echo 'usage: sf "[table [where ...]]"'
        \return 1
    \fi
    select * from $*; | less -F
\done

# count rows in a table
\func -x count
    \if [ $# -eq 0 ]
        \echo 'usage: count "[table [where ...]]"'
        \return 1
    \fi
    select count(*) from $*;
\done

You can invoke them like this:

# select * from data table
1> sf "data where date='20140306'"

# count the rows in the employee table
2> count employee

# list aliases
3> \alias

4. History and reverse search

You can rerun a previous command by using the \history command or by invoking reverse search with Ctrl+r:

1> \history
(1) sp_who
(2) select count(*) from data
(3) select top 10 * from data

# invoke the second command from history
2> !2

# invoke the previous command
3> !!

# reverse search
4> <Ctrl+r>
(reverse-i-search)`sp': sp_who
4> sp_who

5. Customisable prompt
The default prompt is ${lineno}> , but it can be customised to include your username and database, and it even supports colours. It would be nice if there was a way to change the colour based on which database you were connected to (for example, red for a production database), but I haven't been able to figure out if this is possible yet. Here is my prompt, set in my ~/.sqshrc:

\set prompt_color='{1;33}' # yellow
\set text_color='{0;37}'   # white
\set prompt='${prompt_color}[$histnum][$username@$DSQUERY.$database] $lineno >$text_color '

6. Different result display styles
sqsh supports a number of different output styles which you can easily switch to. The ones I frequently use are csv, html and vert (vertical). Here is an example:

1> select * from employee; -m csv
123,"Joe","Bloggs"

2>select * from employee; -m vert
id:        123
firstName: Joe
lastName:  Bloggs

7. For-loops
A for-loop allows you to iterate over a range of values and execute some code. For example, if you want to delete data, in batches, over a range of dates, you can use a for-loop like this:

\for i in 1 2 3 4 5
    \loop -e "delete from data where date = '2014020$i';"
    \echo "Deleted 2014020$i"
\done

8. Backgrounding long-running commands
If you have a long-running command, you can run it in the background by putting an & at the end of the command. You can then continue running other commands, whilst this one runs in the background. You will see a message when the background command completes and you can use \show to see the results. Here is an example:

# run a command in the background
1> select * from data; &
Job #1 running [6266]

Job #1 complete (output pending)

# show the results of the backgrounded command
3> \show 1

Further information:
You can download sqsh here and then read the man page for more information.
You can take a look at my .sqshrc in my GitHub dotfiles repository.

Calling getdate() using Hibernate

This post shows you how to use Hibernate to call Sybase's getdate() function in order to get the current date and time on your database server.

First, you need to create an entity to represent the date object. Hibernate will then map the result of getdate() to this entity.

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/**
 * Represents a date entity.
 * Used by hibernate to map the getdate() sybase function onto.
 */
@Entity
public class DBDateTime {

    @Id
    @Temporal(TemporalType.TIMESTAMP)
    private Date date;

    /**
     * @return the date
     */
    public Date getDate() {
        return date;
    }
}

Usage:
The code snippet below shows how you would call getdate() on your Sybase database and get a Date returned:

Query query = entityManager.createNativeQuery("SELECT getdate() as date", 
                                              DBDateTime.class);
DBDateTime dateEntity = (DBDateTime) query.getSingleResult();
Date now = dateEntity.getDate();

Sybase: How to BCP data in and out of databases

To quickly copy data from a table in one database to another database, for example, from production to a development environment, use the Sybase bcp utility as follows:

Step 1: bcp out to a file
First run bcp to copy data out of your database table and into a flat file. Just hit [Return] when prompted for lengths of columns, but remember to save the table format information to a file. An example is shown below:

$ bcp  Customers out /tmp/bcp.out -S server1 -t, -U username -P password
Enter the file storage type of field firstName [char]:
Enter prefix-length of field firstName [0]:
Enter length of field firstName [32]:
Enter field terminator [,]:

Enter the file storage type of field lastName [char]:
Enter prefix-length of field lastName [0]:
Enter length of field lastName [10]:
Enter field terminator [,]:

Enter the file storage type of field accessTime [smalldatetime]:
Enter prefix-length of field accessTime [0]:
Enter field terminator [,]:

Do you want to save this format information in a file? [Y/n] Y

Host filename [bcp.fmt]: /tmp/bcp.fmt

Starting copy...

14 rows copied.
Clock Time (ms.): total = 1  Avg = 0 (14000.00 rows per sec.)

Step 2: bcp in to the target database
Next run bcp to copy data from the flat file to your target database using the format file you saved in Step 1.

$ bcp  Customers in /tmp/bcp.out -S server2 -f /tmp/bcp.fmt -U username -P password
Starting copy...

14 rows copied.
Clock Time (ms.): total = 9  Avg = 0 (1555.56 rows per sec.)

Find Blocking Processes Using sp_who [Sybase]

If a user has executed an insert/update on a table, but not committed the transaction, other users will find that their queries on the same table hang. This is because the table has been locked and the first user's process is blocking everyone else's.

The following scenario illustrates this. User Bob updates a table but does not commit it. User Alice then tries to query the table, but here command hangs:

bob$ isql -S myServer -D myDatabase -U myUser -P myPass
1> begin transaction
2> update MY_TABLE set COL='C' where COL='A'
3> go
(1 row affected)

alice$ isql -S myServer -D myDatabase -U myUser -P myPass
1> select * from MY_TABLE where COL = 'A'
2> go -- Hangs

In this example, Alice's query to the table hangs because Bob has not committed his transaction. You can use the sp_who command in order to see which commands are blocked and who they are being blocked by.

alice$ isql -S myServer -D myDatabase -U myUser -P myPass
1> sp_who
2> go
fid spid status     loginame  origname  hostname                  blk_spid
        dbname      tempdbname cmd               block_xloid
--- ---- ---------- --------- --------- ------------------------- --------
         ----------- ---------- ----------------- -----------
  0   51 recv sleep myUser myUser MACHINE21302                     0
        myDatabase  tempdb     AWAITING COMMAND            0
  0  343 lock sleep myUser myUser MACHINE21501                    51
        myDatabase  tempdb     SELECT                      0

The output shows that spid 343 from MACHINE21501 is being blocked by spid 51 from MACHINE21302. You can use the command sp_lock 51 in order to find more information about the locking process.

You can either kill the blocking process (if you have DBA rights) using kill 51 or use the psloggedon utility to find out which user is logged onto MACHINE21302, so that you can tell them to commit their open transaction.