Friday, 27 January 2012

Sybase and SQL Server Image Data Move

SQL Developer can move data online and offline.
Online is really only for small amounts of data (<100mb).
For larger sets of data with greater performance and much better logging, the Offline Data Move feature should be used.



SQL Developer will create two sets of scripts.
One to dump out the data using Sybase/SQL Servers BCP tool to DAT files on the disk.
The second set of scripts to read the DAT files and load the data into the Oracle tables using Oracle SQL*Loader.
As the scripts are just plain text files they should be inspected and can be modified to suit your needs.

Because SQL Developer knows the names of all the tables and each columns name/datatype it can do a good job of defining these scripts, so very little has to be modified.

Currently there is an issue moving large Images from Sybase/SQL Server to Oracle using the offline data move scripts SQL Developer generate. But with a little change you can get them to move correctly.

For this little test , the first thing is to


Insert an Image into Sybase using BCP.
  • Create a table with one column in Sybase of type IMAGE
CREATE TABLE loadimage (col1 IMAGE)
go
  • Make sure the Sybase database can INSERT from BCP (note this database is called loadimage)
use master
go
sp_dboption "loadimage", "select into", true
go
use loadimage
go
checkpoint
go
  • The bcp.fmt needs to have the correct size of the imagefile specified, in this case 1551333.
10.0
1
1 SYBIMAGE 0 1551333"" 1 col1
  • From the command line load an image into the Sybase table using Sybase BCP
bcp loadimage..loadimage in image3.jpg -Usa -P -S -f bcp.fmt -T2000000

This should load up the image3.jpg into the Sybase table.
It can be viewed from SQL Developer.
Just browse to the Sybase table, click on the data tab. Double click the (BLOB) item and choose "View As Image".

Now we need to move it to Oracle.
Migrate the Sybase database to Oracle, but choose Offline Data Move in step 8 of the migration wizard.

The following changes need to be performed against the scripts SQL Developer generates for you.

The unload_script.bat needs to be changed as BCP by default will truncate to 32K.
mkdir data
mkdir log
bcp "[loadimage].[dbo].[loadimage]" out "data\dbo_loadimage.loadimage.dat" -T 1552503 -c -t "" -r "" -U%2 -P%3 -S%1


The -T 1552503 denotes the largest Image in that column.
This can be found by querying the Sybase table.
select max(datalength()) from

Now the DAT file will include all the IMAGE data in HEX format, and it will not be truncated.
Next we have to load it into Oracle and those scripts need a little change as well.
SQL Developer creates scripts which
1) Create a new CLOB column to hold the HEX string
2) Process the CLOB HEX string into a BLOB using a procedure
3) Remove the CLOB column
This is necessary as you cannot load a large HEX string directly into a BLOB column in Oracle.
This action is performed for you, but this is a default limit to the size of the HEX string SQL*Loader will read, and this needs to be changed.

Open the control/dbo_loadimage.loadimage.ctl file
load data
infile 'data/dbo_loadimage.loadimage.dat'
"str ''"
into table dbo_loadimage.loadimage
fields terminated by ''
trailing nullcols
(
SQLDEVELOPER_CLOB_1 CHAR(20000000)
)

Change the size of the CHAR column. This is the temporary column the scripts will create for you to load in the HEX String.

Once this is done you can run the script to load the image data into the Oracle table.
oracle_ctl.bat system oracle@11GR1

Notice that the rows are loaded fairly fast into the CLOB columns as HEX strings. But it does take some time to process these HEX strings into BLOBs.

Once the load is finished the Images can be inspected in SQL Developer