2

I have an application which uses Sybase ASE 15.7 for the underlying database. In older days it was recommended to split the tables and located them in two different databases let's say db1 and db2. I know that there are no naming conflicts which means that I could migrate either objects from db1 to db2 or vice versa.

What would be the best option to migrate the data. I have SQL scripts to create all objects I need in the remeining database. Is there a better option than using this:

1> INSERT INTO db2..tblA
2> SELECT * FROM db1..tblA
3> GO

Some of the tables are quite huge. So I need to take care that the transaction log is not filled up.

BCP might also be an option like that:

bcp db1..tblA out tblA.save -U... -P....
bcp db2..tblA in tblA.save -U... -P....

Is there a tool available that could connect to both databases and could handle something like this?

Maybe someone has an idea. Thanks in advance.

Best regards

Jens

Jens Heinitz
  • 21
  • 2

1 Answers1

1

To prevent the log filling up you could perform an unlogged operation. If that is not possible you could set the DB options to truncate log checkpoint during the copy procedure but this 2nd method might not be enough to ensure the log is not exhausted. If an unlogged operation is used then after completion a full DB dump should be done to create a backup of the new baseline. Unlogged operations might be dangerous if done outside of an offline maintenance window.

Truncate on Checkpoint (do the following and then perform a checkpoint)

To switch the Truncate mode on/off use

use master;

sp_dboption , 'trunc log on chkpt', [false|true];

To allow BCP/select INTO on a DB (do the following and then perform a checkpoint)

use master;

sp_dboption , 'select into/bulkcopy/pllsort ', [false|true];

To perform a checkpoint

To checkpoint in DB ‘dbname’ use.

use 'dbname';

checkpoint;

BCP Option

If you choose to use BCP then make sure you use fast BCP. Fast BCP is unlogged.

The rules to ensure Fast BCP is used are specified at http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1570100/doc/html/san1367605064460.html.

You can do it in two phases an ‘extract’ (out) run and then a ‘load’ in run. The commands would look something like this.

SYBASE/$SYBASE_OCS/bin/bcp ${DB1NAME}..${TABNAME} out $DUMPDIR/$DB1NAME/${TABNAME}.TXT -c -U$SQLUSER -P$USERPASS -S$SERVER1NAME

SYBASE/$SYBASE_OCS/bin/bcp ${DB1NAME}..${TABNAME} in $DUMPDIR/$DB1NAME/${TABNAME}.TXT -c -U$SQLUSER -P$USERPASS -S$SERVER1NAME

The select into / bulk copy DB option needs to be set in the DB for this to work.

You will need to dump the DB after this operation.

Unlogged Operation - Select Into

As you have access to both databases from within the same server you should have a look at using select into.

‘select into’ is an unlogged operation.

The target table for the select into statement cannot exist so what you will need to do is move the original target table using sp_rename and then run the select into using the two source tables in your query.

The select into / bulk copy DB option needs to be set for the DB for this to work.

You will need to dump the DB after this operation.

This might be slow due to the Union statement. Also union is not a good option is the rows are not unique.

Indexes

In General if there is a large index on the target table (especially a clustered one) it would probably be more efficient to drop it during the copy and recreate it afterwards.

london-deveoper
  • 533
  • 3
  • 8