Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

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

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
matrix0511
Asked:
matrix0511
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:

Fill in the primary keys. They should really never be null anyway.
0
 
momi_sabagCommented:
can you try to alter the columns on the sql server side to be not null?
0
 
matrix0511Author Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
matrix0511Author Commented:
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
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
 
tliottaCommented:
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
 
matrix0511Author Commented:
Thanks for all your help guys, but I think we foudn a way to move on using the DBMoto tool.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now