Solved

How Can We Migrate/Copy Table Structures From DB2400 to SQL Server 2008?

Posted on 2010-09-21
7
546 Views
Last Modified: 2012-05-10
We are in the process of testing a migration of data from DB2400 to SQL Server 2008 database server. We are utilizing a tool called DBMoto to do the replication. We also use the software JD Edwards Enterpriseone. and in that software it comes with included batch programs that you can run that will perform a copy of data from DB2 to SQL.

But we have encountered an issue.  The batch program we run in JD Edwards (R98403)  to replicate the table structures from DB2 to SQL Server is unable to replicate a table’s primary key in SQL Server.  This is due to the differences between the databases were DB2 allows columns will null values to be a part of the primary key whilst SQL Server does not allow columns with null values to be a part of the primary key.  

Without primary keys on the tables in SQL Server, DBMoto tool cannot replicate the data.  

Does anyone have any suggestions on how to programmatically copy table structures from DB2 to SQL Server were the primary key is properly translated, please let me know.

Thanks!
0
Comment
Question by:matrix0511
7 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 33726066

Fill in the primary keys. They should really never be null anyway.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33726183
can you try to alter the columns on the sql server side to be not null?
0
 

Author Comment

by:matrix0511
ID: 33726307
daveslash, what do you mean "fill in the primary keys"? How do we do that when it's a tool or batch program that we run that does this copy for us? What needs to be change?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:matrix0511
ID: 33726426
What's strange guys is that looking at just one of the tables it's trying to copy: F0101, you can see the column for NULLs in the table and it's ALL set to "N" not to allow any nulls. so that should be enough for the copy to work you would think right? It's clearly showing that it does not allow NULLs
 so what the heck?? haha

We encountered this problem on all the tables when we ran the batch program to copy the data.  The  log file generated the following error message for each table (using table F0101 as an example):

 [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot define PRIMARY KEY constraint on nullable column in table 'F0101'. - SQLSTATE: 37000
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 33728227
Show is the ddl for this table from SQL server and from db2. Sound like the tab may not be getting created correctly in SQL server.
0
 
LVL 27

Accepted Solution

by:
tliotta earned 500 total points
ID: 33730003
If the column is in fact not nullable in DB2, and if the copy fails due to a nullable primary key, then the tool/method being used for the copy is failing somehow. Maybe the driver can't extract the attribute. Maybe an inappropriate driver is being used. Maybe DB2 isn't exposing the attribute correctly. Maybe... who knows?

As Gary asks, show us the table definitions. It might also help to describe the environment a little more -- version of DB2 might help. (Version of the AS/400 OS will tell us about DB2.)

Tom
0
 

Author Comment

by:matrix0511
ID: 33806731
Thanks for all your help guys, but I think we foudn a way to move on using the DBMoto tool.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now