Copy table schema from DB2 to SQL Server

Question

I'm looking at creating staging tables in SQL Server for one of our SSIS packages to reduce the number of calls to DB2 since calls to DB2 may experience timeouts when DB2 recycles inactive connections. Is there an automated method for copying table schema from DB2 to SQL Server? There would need to be a 1 to 1 mapping of data types between DB2 and SQL Server for this to work. If there isn't a tool that exists, I may write one myself since some of our DB2 tables have 20+ columns and it would be a pain to manually recreate in SQL Server.

Answer 1

I have a partially working script you're welcome to use. We don't care about primary keys and such from DB2 into our SQL Server side of things. Our only concern is to get the data over. Plus, the data I've had to deal with was only string or date based so where I build the data_type might be incorrect for the decimal.

The core concept is that I inspect the sysibm.syscolumns to derive a list of all the tables and columns and then try to provide a translation between the DB2 data types and SQL Server.

Anyways, give it a shot. Feel free to edit or make a comment about what's broken and I'll see if I can fix it.

This is built using a mix of the SQL Server 2012 CONCAT function and the classic string concatenation operator +. It also assumes a Linked server exists for the OPENQUERY to work.

WITH SRC AS
(
SELECT
    OQ.NAME AS column_name
,   OQ.TBNAME AS table_name
--,   RTRIM(OQ.COLTYPE) AS data_type
,   CASE RTRIM(OQ.COLTYPE)
        WHEN 'INTEGER' THEN 'int'
        WHEN 'SMALLINT' THEN 'smallint'
        WHEN 'FLOAT' THEN 'float'
        WHEN 'CHAR' THEN CONCAT('char', '(', OQ.LENGTH, ')')
        WHEN 'VARCHAR' THEN CONCAT('varchar', '(', OQ.LENGTH, ')')
        WHEN 'LONGVAR' THEN CONCAT('varchar', '(', OQ.LENGTH, ')')
        WHEN 'DECIMAL' THEN CONCAT('decimal', '(', OQ.SCALE, ')')
        WHEN 'DATE' THEN 'date'
        WHEN 'TIME' THEN 'time'
        WHEN 'TIMESTMP' THEN ''
        WHEN 'TIMESTZ' THEN ''
        WHEN 'BLOB' THEN ''
        WHEN 'CLOB' THEN ''
        WHEN 'DBCLOB' THEN ''
        WHEN 'ROWID' THEN ''
        WHEN 'DISTINCT' THEN ''
        WHEN 'XML' THEN ''
        WHEN 'BIGINT' THEN ''
        WHEN 'BINARY' THEN ''
        WHEN 'VARBIN' THEN ''
        WHEN 'DECFLOAT' THEN ''
        ELSE ''
    END AS data_type
,   OQ.LENGTH
,   OQ.SCALE
,   CONCAT(CASE OQ.NULLS WHEN 'Y' THEN 'NOT' ELSE '' END, ' NULL') AS allows_nulls
,   OQ.UPDATES AS updateable
FROM
    OPENQUERY(LINKED, 'SELECT * FROM abcde01.sysibm.syscolumns T WHERE T.TBCREATOR = ''ABCD''  ' ) AS OQ
)
, S2 AS
(
SELECT
    CONCAT(QUOTENAME(S.column_name), ' ', S.data_type, ' ', S.allows_nulls) AS ColumnDeclaration
,   S.table_name
FROM
    SRC AS S
)
, MakeItPretty AS
(
    SELECT DISTINCT
        QUOTENAME(S.TABLE_NAME) AS TABLE_NAME
    ,   STUFF
        (
            (
            SELECT  ',' + ColumnDeclaration 
            FROM S2 AS SI
            WHERE 
                SI.TABLE_NAME = S.TABLE_NAME
            FOR XML PATH('')),1,1,''
        ) AS column_list
    FROM
        S2 AS S
)
SELECT
    CONCAT('CREATE TABLE ', MP.TABLE_NAME, char(13), MP.column_list) AS TableScript
FROM
    MakeItPretty AS MP;