Solved

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

Posted on 2010-09-21
7
575 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 18

Expert Comment

by:Dave Ford
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 35

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

617 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