Solved

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

Posted on 2010-09-21
7
549 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 44
Sql Server group by 10 29
Sql Query 6 67
access query to sql server 3 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

810 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