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!
matrix0511Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
tliottaConnect With a Mentor Commented:
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
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
 
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
 
matrix0511Author Commented:
Thanks for all your help guys, but I think we foudn a way to move on using the DBMoto tool.
0
All Courses

From novice to tech pro — start learning today.