Data Access Worldwide Knowledge Base
Article Text
QUESTION:
I have a DataFlex table (TABLE_A) that uses RECNUM as its identifier. Other DataFlex tables relate to this one using the Recnum value -- i.e. other tables have a field in their structure to store the Recnum from TABLE_A.
When I run the conversion wizard using Connectivity Kit 4.1 and Database Builder 11.1, TABLE_A gets its RECNUM reassigned, breaking the relationship to other tables.
How can I preserve the RECNUM value when converting tables?
ANSWER:
When converting to an SQL backend, RECNUM tables will have a RECNUM column created. That column is created as the identity column with starting value set to 1 with increments of 1.
In order to preserve the value stored in RECNUM, you can do the following:
MICROSOFT SQL SERVER
Conversion Using Dump & Load Option
1. Select Database | SQL Server | Dump & Load for SQL Server and:
o Select "Identity Column" for RID
o Leave the two checkboxes checked
That means that you will generate the dump file (your data from your DataFlex table will be dumped into a .ASC file; the description of your table structure will be dumped into a .FMT file) and the script (the script will be the dfmssql.bat file).
2. After that process is over, start the conversion wizard selecting Database | SQL Server | Convert to MS SQL Server and make sure you convert the table "definition only"
o Select "Convert Definition only" checkbox on the "Define Conversion Options" panel
3. Using the MS SQL Server Management Studio, set identity to "No" for Recnum in the new empty table created; save and close your table
4. Then start a CMD window, change directory to your Data directory (this is where the dump files and the script are placed by default)
5. Type: dfmssql
and press Enter - all data should be loaded into your SQL table using the Recnum values that were in the DataFlex table
6. Using the MS SQL Server Management Studio, set identity back to "Yes" for Recnum
Conversion Using the Convert to MS SQL Server Option
1. Start the conversion wizard selecting Database | SQL Server | Convert to MS SQL Server and make sure you convert the table "definition only"
o Select "Convert Definition only" checkbox on the "Define Conversion Options" panel
2. Using the MS SQL Server Management Studio, set identity to "No" for Recnum in the new empty table created; save and close your table
3. Delete the CCH file for that table
4. Change that table's INT file to contain
Generate_Record_ID_Method NONE
5. Copy your data using Database Builder (Maintenance | Copy Records option)
o Select the DataFlex file as the source and the INT file as the destination files
6. After the copy is done, using the MS SQL Server Management Studio, set identity back to "Yes" for Recnum
7. Delete the CCH file for that table
8. Change that table's INT file to contain
Generate_Record_ID_Method IDENTITY_COLUMN
IBM DB2
Conversion Using Dump & Load Option
1. Select Database | DB2 | Dump & Load for DB2 and:
o Select "Identity Column" for RID
o Leave the two checkboxes checked
That means that you will generate the dump file (your data from your DataFlex table will be dumped into a .DEL file) and the script (the script will be the dfdb2.bat file).
2. After that process is over, start the conversion wizard selecting Database | DB2 | Convert to DB2 and make sure you convert the table "definition only"
o Select "Convert Definition only" checkbox on the "Define Conversion Options" panel
3. Using the Control Center from DB2 9.1, remove the identity from Recnum
o Start Control Center
o Select your database and click on Tables
o Right-click on the table name
o Select Alter
o Select RECNUM and press Change
o Select the Value generation tabpage
o Select None
o Press OK
4. Then start a DB2 command window, change directory to your Data directory (this is where the dump files and the script are placed by default)
5. Type: db2 -f dfdb2.bat
and press Enter - all data should be loaded into your DB2 table using the Recnum values that were in the DataFlex table
6. Using the Control Center from DB2 9.1, set Recnum back to be the identity column
Conversion Using the Convert to DB2 Option
1. Start the conversion wizard selecting Database | DB2 | Convert to DB2 and make sure you convert the table "definition only"
o Select "Convert Definition only" checkbox on the "Define Conversion Options" panel
2. Using the Control Center from DB2 9.1, remove the identity from Recnum as described above
3. Delete the CCH file for that table
4. Change that table's INT file to contain
Generate_Record_ID_Method NONE
5. Copy your data using Database Builder (Maintenance | Copy Records option)
o Select the DataFlex file as the source and the INT file as the destination files
6. After the copy is done, using the Control Center, set Recnum back to be the identity column
7. Delete the CCH file for that table
8. Change that table's INT file to contain
Generate_Record_ID_Method IDENTITY_COLUMN
Note1:
If you have many records to convert, you should use the Dump & Load option.
Note2:
Relationships based on RECNUM are not recommended - RECNUM is system-assigned, so the developer has no control over it or its value.
Contributed By:
Marcia Booth
Company: Data Access Worldwide
Web Site: http://www.dataaccess.com
Email this Article
Email this Article to a Colleague
Send Feedback on this Article to Data Access Worldwide
Copyright ©2010 Data Access Corporation. All rights reserved.
| The information provided in the Data Access Technical Knowledge Base is provided "as is" without warranty of any kind. Data Access Corporation disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall Data Access Corporation or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if Data Access Corporation or its suppliers have been advised of the possibility of such damages. Some states do not allow the exclusion or limitation of liability for consequential or incidental damages so the foregoing limitation may not apply. |