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

Designing a Lottery System

I was asked this question in an interview: You need to design a database to hold customers and their lottery tickets. A lottery ticket has a sequence of 6 numbers e.g. 04 12 18 28 35 41. Once you have designed your tables, write a query which will print out a report of all customers whose tickets match three or more numbers of the winning number. Assume every customer has only one lottery ticket.

The simplest way is to have two tables:

• Customer: with an id, name etc
• Ticket: with an id, customer id and number (which will hold one of the numbers only, so you will get six records per ticket)

Given a winning number, the query to find all customers and how many numbers they matched would be:

SELECT c.name, COUNT(t.num) AS matches
FROM customer c, ticket t
WHERE c.id = t.customer_id
AND t.num IN
(
'05', /*winning number*/
'12',
'19',
'28',
'35',
'42'
)
GROUP BY customer
HAVING COUNT(t.num) >= 3
ORDER BY matches DESC

Escaping a Character in Oracle

Consider the following table

select * from my_table

NAME
----
FAHD
FAHD_SHARIFF
SHARIFF_FAHD
FAHDSHARIFF

If you want to select only those names containing an underscore, the following query will NOT work:


select * from my_table where name like '%_%'

NAME
----
FAHD
FAHD_SHARIFF
SHARIFF_FAHD
FAHDSHARIFF

All rows are returned even though rows 1 and 4 do not contain an underscore! This is because an underscore is a special character - it is a single character wildcard.

You need to escape the underscore so that Oracle treats it as a literal:


select * from my_table where name like '%\_%' escape '\'

NAME
----
FAHD_SHARIFF
SHARIFF_FAHD

Oracle 10g's New Row Timestamps

One of the systems I work on, maintains a cache of data which is reloaded from the database every time an update is made. Using a cache, rather than hitting the database each time, has some significant performance advantages. However, one of the downsides is the time taken to load the cache. Due to the large amount of data involved, sometimes reloading the cache takes up to 15 minutes!

The main reason for this is that because we don't know which rows of data have changed, we end up loading the entire cache each time. This means that we will reload 50,000 rows of data even if only 1 row has changed!

One solution would be to add a "last_updated" column to each of our tables and use that in our cache-refresh query. A better way, however, would be to use an exciting new feature that comes in Oracle 10g...

In Oracle 10g, a new pseudocolumn called ORA_ROWSCN is available on every row which "returns the conservative upper bound system change number (SCN) of the most recent change to the row". This is useful for determining approximately when a row was last updated. Also, the SCN_TO_TIMESTAMP function can be used to convert an SCN to a timestamp!

So coming back to the cache problem, we can determine which rows were modified simply by writing a query which gets all rows having an SCN_TO_TIMESTAMP(ORA_ROWSCN) greater than the time that the cache was last reloaded!

Sadly, we're still on Oracle 9i so will have to wait to use this awesome new feature:(

Example

SELECT ORA_ROWSCN, last_name FROM employees
WHERE first_name = 'FAHD';

UPDATE employees SET salary = salary*10
WHERE first_name = 'FAHD';

SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), last_name FROM employees
WHERE first_name = 'FAHD';

Reference

Oracle®: ORA_ROWSCN Pseudocolumn