Replicating NTEXT and IMAGE data to DB2

One issue that came to us recently was on how to replicate SQL data containing NTEXT and IMAGE columns to DB2. Replicating most other data is straightforward, but NTEXT and IMAGE is a bit of a problem. Currently our data provider does not support LOBs (Large objects) such as CLOB or BLOB (character/binary large objects). So the only way to move the data is by using either VARCHAR or VARCHAR FOR BIT DATA. However, there are a few limitations if using VARCHAR with DB2.

1. DB2 does not allow you to have multiple VARCHAR(MAX) columns in the same table.
   CREATE TABLE  Categories (description1  VARCHAR(32703),
   Picture1  VARCHAR(32703) FOR BIT DATA)
   will return error " SQL statement too long or Complex".
2. NTEXT, TEXT, and IMAGE  data types in SQL can hold much larger values than VARCHAR or VARCHAR() for BIT DATA in DB2. So there is a VERY good chance of truncation if the SQL value happens to go beyond 32703. Especially for image data. Also VARCHAR limits on DB2 depend on page size of DB2. It can range from 4045 to 32703 for page sizes ranging from 4KB to 32KB. The maximum VARCHAR value you can have is 32703.
3. AS400 object names are case sensitive.

MSDN documents the following about DB2 Subscribers with SQL Server (at https://msdn.microsoft.com/en-us/library/ms152492.aspx):

• DB2 can support VARCHAR columns as large as 32 kilobytes (KB); therefore it is possible that some SQL Server large object columns can be appropriately mapped to DB2 VARCHAR columns. However, the OLE DB provider that replication uses for DB2 does not support mapping SQL Server large objects to DB2 large objects. For this reason, SQL Server text, varchar(max) , ntext, and nvarchar(max) columns are mapped to VARCHAR(0) in the generated create scripts. The length value of 0 must be changed to an appropriate value prior to applying the script to the Subscriber. If the data type length is not changed, DB2 will raise error 604 when the table create is attempted at the DB2 Subscriber (error 604 indicates that the precision or length attribute of a data type is not valid).

In order to change the data type of VARCHAR(0) to something meaningful, you need to change the publication to use a custom creation script. This is discussed in the same link as above (see item #4 on the page), and the purpose of this blog entry is to show how to use sp_changearticle and a custom creation script to replicate NTEXT and IMAGE data from SQL Server to DB2.

For my test purposes, I used the Northwind database, specifically the Categories table, which contains 4 columns (int, nvarchar, ntext, image) with 8 rows of data.

 CREATE TABLE [dbo].[Categories](
    [CategoryID] [int] NOT NULL,
    [CategoryName] [nvarchar](15) NOT NULL,
    [Description] [ntext] NULL,
    [Picture] [image] NULL)

First, to ensure there would be no truncation, I checked the length of the columns as follows:

 use Northwind
select DATALENGTH([CategoryID]) as CategoryID_Length,
    DATALENGTH([CategoryName]) as CategoryName_Length,
    DATALENGTH([Description]) as Description_Length,
    DATALENGTH ([Picture]) as Picture_Length
from dbo.Categories

This returned the following to me:

Based on the above, the Maximum length of the Destination column is 116, while the Picture column is 10,746 bytes in length, so moving this data to DB2 should be easy. Since the IMAGE data type is binary, you would want to use VARCHAR() FOR BIT DATA in DB2.

Now that we know the lengths, let’s ensure that we can create the table properly.

For my purposes, I am using DB2/400 v5r4, but this should be similar for DB2 on other platforms. Also, I will be creating the table in my own personal schema (CHARLIEE2). Using iSeries Navigator, I ran the following script:

 /* Creating table CHARLIEE2.CATEGORIES */
CREATE TABLE CHARLIEE2.CATEGORIES (
      CATEGORYID INTEGER NOT NULL
    , CATEGORYNAME VARCHAR(15) NOT NULL
    , DESCRIPTION VARCHAR(116) DEFAULT NULL
    , PICTURE VARCHAR(10746) FOR BIT DATA DEFAULT NULL )   ;
CREATE UNIQUE INDEX PK_CATEGORIES ON CHARLIEE2.CATEGORIES(CATEGORYID);

This returned successful. It is important to test this. Also, do NOT delete the table you just created, as it will break things later on for this example as the table needs to be created 1^st for the creation script used by the publication to work properly.

Next, create the snapshot publication using the “New Publication Wizard”. But, there are a few things you need to do while creating the publication:

After selecting the Categories table, click “Article Properties” then “Set Property of Highlighted Table Article.” This dialog should appear:

Change this to appear as such:

The AS400 is case sensitive, so we want to ensure everything is correct, and we don’t need the Destination Object owner here.

Do not set the snapshot agent to run immediately. We will be running some scripts later that will invalidate the snapshot.

I named my publication “CATEGORIES”. You will need to know the name of your publication later for the scripts we will run.

After creating the publication, right click on it and select properties. Under Subscription Options, change “Allow non SQL Server Subscribers” to True.

Now that the publication is created, we need to create the subscription. Run the “New Subscription Wizard”. For my connection string, I used the following (created by the Data Access Tool):

Provider=DB2OLEDB;User ID=<userid>;Password=<password>;Initial Catalog=S105FRDM;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=S105FRDM;Network Port=446;Package Collection=CHARLIEE2;Default Schema=CHARLIEE2;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=CHARLIEE2;DBMS Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Connection Pooling=False;Derive Parameters=False;

I also set the synchronization schedule to “Run on demand only” for testing.

After the subscription is created, we need to create our custom creation script. Begin by creating a new SQL Script file on your system. For my test, I created c:\temp\replciation\CUSTOMREPL.SQL.

In this file, I added the following:

 bypass_translation

DROP TABLE CHARLIEE2.CATEGORIES
GO

CREATE TABLE  CHARLIEE2.CATEGORIES (
CATEGORYID   INT NOT NULL,
CATEGORYNAME VARCHAR(15) NOT NULL,
DESCRIPTION  VARCHAR(116) DEFAULT NULL,
PICTURE VARCHAR(10746) FOR BIT DATA DEFAULT NULL )
GO

CREATE UNIQUE INDEX PK_CATEGORIES ON CHARLIEE2.CATEGORIES(CATEGORYID)
GO

Notice that the Create Table is the same as I used when creating the file with iSeries.

Now we need to tell the publication to use this file, and make a couple of other changes. To do this, run the following scripts:

 -- set the creation_script property of the Article to point to custom Create Script
sp_changearticle @publication = CATEGORIES,
                @article = Categories,
                @property = 'creation_script',
                @value = "C:\temp\Replication\CUSTOMREPL.sql",
                @force_invalidate_snapshot = 1,
                @force_reinit_subscription = 1

-- then change the Schema option.
DECLARE @option AS int;
SET @option = (SELECT CAST(0x00 AS int));
EXEC     sp_changearticle @publication = CATEGORIES,
            @article = Categories,
            @property = N'schema_option', 
            @value = @option,
                @force_invalidate_snapshot = 1,
                @force_reinit_subscription = 1

-- make sure to set the pre creation command to nothing 
exec sp_changearticle 
    @publication = CATEGORIES,
    @article = Categories,
    @property = pre_creation_cmd,
    @value = 'none',
    @force_invalidate_snapshot = 1,
    @force_reinit_subscription = 1

At this point you are done. You should be able to successfully replicate the data from SQL Server to DB2 without any problems.