When and How to Use DDF2BTR.EXE Utility
Release Date: 03/03/2016
Version: 8.0 & up
A - Actually, you can find the DDF2BTR.EXE utility in the <ElliottRoot>\Bin, <ElliottRoot>\Bin85 and <ElliottRoot>Bin86 folders. The purpose of DDF2BTR.EXE is to convert the existing Elliott *.BTR files to make them consistent with the DDF index structure. Use DDF2BTR from the folder that match your current Elliott version. This utility is to support any application that needs to access Elliott data through the PSQL relational engine. This includes, but is not limited to, Crystal Reports, Microsoft Excel Query, Starship, Web Services, and Elliott 8.6 Report Desk. DDF2BTR.EXE replaces the old Macola utility SQL2BTR. The inconsistency can happen when the BTR file was created and the DDF definition for the table was not yet defined or installed.
How Do I Know My Database Is Not Relational Compatible?
Example 1 - Check POWHHSTI.BTR (PO Warehouse Receiving History in Elliott 8.2 format)
For example, a user reported that he had trouble using simple a SQL query against the POWHHSTI table as follows:
The following query works:
select * from "POWHHSTI";
The following query DOES NOT work even though the user is sure there are warehouse receiving records
select * from "POWHHSTI"
where powhhsti_post_date = 20160303;
This kind of problem is typically caused by a “segmentation” key issue. To be more precise, when POWHHSTI.BTR was first created, the corresponding DDF definition was not there yet. As a result, the POWHHSTI.BTR index was created based on the COBOL internal format. Now the DDF is defined for POWHHSTI.BTR, but unlike the COBOL index format, it is segmented. When you use a query on the POWHHSTI table, it is expected that the index will be segmented. As a result, you get a confused result.
Use BUTIL to Check Table Index Structure
To verify if that is the case, you can use the following BUTIL command on your <ElliottRoot>\DATA\POWHSTI.BTR file.
BUTIL –STAT POWHSTI.BTR
If you see the index structure where the data types are all "strings" and each key has only one segment (as in the following example)...
Key Position Type Null Values* ACS
Segment Length Flags Unique Values
0 1 1 33 String -- 95326 --
1 1 6 28 String MD -- 95326 --
2 1 20 14 String MD -- 65676 --
...then it is likely there is an index structure issue. The proper segmented key structure that matches the DDF of this table should look like the following (Elliott 8.2 format):
Key Position Type Null Values* ACS
Segment Length Flags Unique Values
0 1 1 5 String -- 95326 --
0 2 6 8 Nsa -- 95326 --
0 3 14 6 Nsa -- 95326 --
0 4 20 8 String -- 95326 --
0 5 28 3 Nsa -- 95326 --
0 6 31 3 Nsa -- 95326 --
1 1 6 8 Nsa MD -- 95326 --
1 2 14 6 Nsa MD -- 95326 --
1 3 20 8 String MD -- 95326 --
1 4 28 3 Nsa MD -- 95326 --
1 5 31 3 Nsa MD -- 95326 --
2 1 20 8 String MD -- 65676 --
2 2 28 3 Nsa MD -- 65676 --
2 3 31 3 Nsa MD -- 65676 --
Example 2 - AROPNFIL.BTR (AR Open Item File in Elliott 8.2 format)
Example of Using DDF2BTR to Convert with Elliott 8.0 - 8.2
To address this problem, you need to rebuild the index of POWHHSTI.BTR to match with the DDF format. Fortunately, we provided a very good utility to do this. Go to your <ElliottRoot>\Bin folder, and you'll find a DDF2BTR.EXE program. Use the following command in the command prompt from the BIN folder:
M:\Elliott7\Bin> DDF2BTR ..\DATA\POWHHSTI.BTR
Then you will receive the following message:
Make sure there are no other users using the POWHHSTI table. Click “Yes” to proceed with the conversion. You will receive the following progress window:
When the conversion is done, you will receive the following status displayed in Notepad:
Syntax of DDF2BTR
You may use a wildcard with DDF2BTR. For example, you may use the following:
M:\Elliott7\Bin> DDF2BTR ..\DATA\*.BTR
This will convert all tables in the DATA folder with a physical key structure that mismatches DDF.
By default, DDF2BTR will not rebuild a table whose physical key structure matches the DDF. However, if you want to force the rebuild of a table, regardless of whether the index structure mismatches or not, then you can use the following:
M:\Elliott7\Bin> DDF2BTR /F ..\DATA\*.BTR
The /F option will force processing even if the DDFs and file indexes match.
If you wish to find out more details on the parameters to use with DDF2BTR, you may use the /? parameter as follows:
M:\Elliott7\Bin> DDF2BTR /?
Then you will see the following example show up to explain how to use the DDF2BTR utility:
Keep in mind the DDF2BTR.EXE utility in <ElliottRoot>\Bin folder is for Elliott V8.0 - V8.2 database format where all document numbers are in numeric format.
The following query works:
select * from "POWHHSTI";
The following query DOES NOT work even though the user is sure there are warehouse receiving records
select * from "POWHHSTI"
where powhhsti_post_date = 20160303;
This kind of problem is typically caused by a “segmentation” key issue. To be more precise, when POWHHSTI.BTR was first created, the corresponding DDF definition was not there yet. As a result, the POWHHSTI.BTR index was created based on the COBOL internal format. Now the DDF is defined for POWHHSTI.BTR, but unlike the COBOL index format, it is segmented. When you use a query on the POWHHSTI table, it is expected that the index will be segmented. As a result, you get a confused result.
Use BUTIL to Check Table Index Structure
To verify if that is the case, you can use the following BUTIL command on your <ElliottRoot>\DATA\POWHSTI.BTR file.
BUTIL –STAT POWHSTI.BTR
If you see the index structure where the data types are all "strings" and each key has only one segment (as in the following example)...
Key Position Type Null Values* ACS
Segment Length Flags Unique Values
0 1 1 33 String -- 95326 --
1 1 6 28 String MD -- 95326 --
2 1 20 14 String MD -- 65676 --
...then it is likely there is an index structure issue. The proper segmented key structure that matches the DDF of this table should look like the following (Elliott 8.2 format):
Key Position Type Null Values* ACS
Segment Length Flags Unique Values
0 1 1 5 String -- 95326 --
0 2 6 8 Nsa -- 95326 --
0 3 14 6 Nsa -- 95326 --
0 4 20 8 String -- 95326 --
0 5 28 3 Nsa -- 95326 --
0 6 31 3 Nsa -- 95326 --
1 1 6 8 Nsa MD -- 95326 --
1 2 14 6 Nsa MD -- 95326 --
1 3 20 8 String MD -- 95326 --
1 4 28 3 Nsa MD -- 95326 --
1 5 31 3 Nsa MD -- 95326 --
2 1 20 8 String MD -- 65676 --
2 2 28 3 Nsa MD -- 65676 --
2 3 31 3 Nsa MD -- 65676 --
Example 2 - AROPNFIL.BTR (AR Open Item File in Elliott 8.2 format)
If you use "BUTIL -STAT AROPNFIL.BTR," you should see the following index structure:
Key Position Type Null Values* ACS
Segment Length Flags Unique Values
0 1 1 6 String -- 52844 --
0 2 7 8 Nsa -- 52844 --
0 3 15 6 Nsa -- 52844 --
0 4 21 1 String -- 52844 --
0 5 22 6 Nsa -- 52844 --
1 1 28 6 String MD -- 26821 --
1 2 34 6 Nsa MD -- 26821 --
Segment Length Flags Unique Values
0 1 1 6 String -- 52844 --
0 2 7 8 Nsa -- 52844 --
0 3 15 6 Nsa -- 52844 --
0 4 21 1 String -- 52844 --
0 5 22 6 Nsa -- 52844 --
1 1 28 6 String MD -- 26821 --
1 2 34 6 Nsa MD -- 26821 --
If not, then your database index is likely not PSQL Relaional Engine compatible.
Example 3 - CPINVHDR.BTR (Invoice Header File in Elliott 8.2 format)
If you use "BUTIL -STAT CPINVHDR.BTR", you should see the following index structure:
Key Position Type Null Values* ACS
Segment Length Flags Unique Values
0 1 1 6 Nsa -- 24261 --
1 1 40 6 String MD -- 24261 --
1 2 46 6 Nsa MD -- 24261 --
2 1 892 6 Nsa MD -- 24200 --
3 1 898 8 Nsa MD -- 6071 --
Segment Length Flags Unique Values
0 1 1 6 Nsa -- 24261 --
1 1 40 6 String MD -- 24261 --
1 2 46 6 Nsa MD -- 24261 --
2 1 892 6 Nsa MD -- 24200 --
3 1 898 8 Nsa MD -- 6071 --
If not, then your database index is likely not PSQL Relational Engine compatbile.
Example 4 - CPINVHDR.BTR (Invoice Header File in Elliott 8.5 format)
If your database is in Elliott 8.5 format, then using "BUTIL -STAT CPINVHDR.BTR" should return the following index structure:
Key Position Type Null Values* ACS
Segment Length Flags Unique Values
0 1 1 6 String -- 46149 --
1 1 40 6 String MD -- 46149 --
1 2 46 6 String MD -- 46149 --
2 1 892 6 String MD -- 46107 --
3 1 898 8 Nsa MD -- 4938 --
Segment Length Flags Unique Values
0 1 1 6 String -- 46149 --
1 1 40 6 String MD -- 46149 --
1 2 46 6 String MD -- 46149 --
2 1 892 6 String MD -- 46107 --
3 1 898 8 Nsa MD -- 4938 --
The main difference between Elliott V8.2 and V8.5 is that all document numbers (mostly 6 digits) are in numeric (Nsa) format with V8.2, and in a string format with V8.5.
Example of Using DDF2BTR to Convert with Elliott 8.0 - 8.2
To address this problem, you need to rebuild the index of POWHHSTI.BTR to match with the DDF format. Fortunately, we provided a very good utility to do this. Go to your <ElliottRoot>\Bin folder, and you'll find a DDF2BTR.EXE program. Use the following command in the command prompt from the BIN folder:
M:\Elliott7\Bin> DDF2BTR ..\DATA\POWHHSTI.BTR
Then you will receive the following message:
Make sure there are no other users using the POWHHSTI table. Click “Yes” to proceed with the conversion. You will receive the following progress window:
When the conversion is done, you will receive the following status displayed in Notepad:
Syntax of DDF2BTR
You may use a wildcard with DDF2BTR. For example, you may use the following:
M:\Elliott7\Bin> DDF2BTR ..\DATA\*.BTR
This will convert all tables in the DATA folder with a physical key structure that mismatches DDF.
By default, DDF2BTR will not rebuild a table whose physical key structure matches the DDF. However, if you want to force the rebuild of a table, regardless of whether the index structure mismatches or not, then you can use the following:
M:\Elliott7\Bin> DDF2BTR /F ..\DATA\*.BTR
The /F option will force processing even if the DDFs and file indexes match.
If you wish to find out more details on the parameters to use with DDF2BTR, you may use the /? parameter as follows:
M:\Elliott7\Bin> DDF2BTR /?
Then you will see the following example show up to explain how to use the DDF2BTR utility:
Keep in mind the DDF2BTR.EXE utility in <ElliottRoot>\Bin folder is for Elliott V8.0 - V8.2 database format where all document numbers are in numeric format.
On the other hand, the DDF2BTR.EXE utility in <ElliottRoot>\Bin85 folder is for Elliott V8.5 database format where all document numbers are in string format. Use the appropriate utility based on your situation. Refer to the document Elliott V8.5 Release Notes for more on this subject.
Converting Elliott V8.2 Data to V8.5 Format
Converting Elliott V8.2 Data to V8.5 Format
In Elliott V8.2, all document numbers are assumed to be numeric. While in Elliott V8.5, the system assumes all document numbers to be strings so you can store alphabetic values in document numbers. When you are ready to store alphabetic values in document numbers, you must first convert (re-index) your database from the V8.2 format to V8.5. You can do so by logging on to the PSQL server as an administrator, bringing up the command prompt, going to the folder <ElliottRoot>\Bin85, and typing the following command:
DDF2BTR <ElliottRoot>\DATA\*.BTR
Substitute<ElliottRoot>with the root directory where your Elliott is installed. For example, use “M:\Elliott7.” The DATA is the corresponding DATA folder. It can be DATA, DATA_02…etc., which corresponds to each company. If you have a custom modification with your database, you will specify the path of your custom DDF with the DDF2BTR command. Use DDF2BTR /? to find out the proper command to do so.
When you perform this conversion on the PSQL server, you can estimate about 120 MB per minute. Do not perform this conversion on the client side since the performance will be significantly slower and less reliable. The actual time may depend on many factors. Only convert your Elliott data when no one is using Elliott, including external applications that access Elliott data like Report Writer or web services.
To be safe, you can optionally copy your production company’s data to a new company and perform your database conversion in the new company first, then continue your test with external applications that point to this new company. If the external application tests are successful, then convert your production company data. If you have multiple companies, you may perform this conversion one at a time. You don’t need to convert all of them at the same time. You still can convert the V8.5 database back to the V8.2 format as long as there’s no alphabetic value stored in any document number fields.
Substitute<ElliottRoot>with the root directory where your Elliott is installed. For example, use “M:\Elliott7.” The DATA is the corresponding DATA folder. It can be DATA, DATA_02…etc., which corresponds to each company. If you have a custom modification with your database, you will specify the path of your custom DDF with the DDF2BTR command. Use DDF2BTR /? to find out the proper command to do so.
When you perform this conversion on the PSQL server, you can estimate about 120 MB per minute. Do not perform this conversion on the client side since the performance will be significantly slower and less reliable. The actual time may depend on many factors. Only convert your Elliott data when no one is using Elliott, including external applications that access Elliott data like Report Writer or web services.
To be safe, you can optionally copy your production company’s data to a new company and perform your database conversion in the new company first, then continue your test with external applications that point to this new company. If the external application tests are successful, then convert your production company data. If you have multiple companies, you may perform this conversion one at a time. You don’t need to convert all of them at the same time. You still can convert the V8.5 database back to the V8.2 format as long as there’s no alphabetic value stored in any document number fields.
What Are *.BTO and *.BTN Files?
Elliott PSQL databases are stored in *.BTR files in the DATA folder. If, after using the DDF2BTR.EXE utility, you see the data directory folder has *.BTO or *.BTN files, these are temporary files and we will explain what they are in this section:
The DDF2BTR.EXE utility starts by reading one *.BTR file and writing to a *.BTO file as a temporary storage space. Once that file is successfully converted to a *.BTO file, it will delete the *.BTR file and rename the *.BTO file to a *.BTR file. However, we noticed sometimes the utility was able to delete the *.BTR file, but was not able to rename the *.BTO file to a *.BTR file. We believe the failure of renaming a *.BTO to a *.BTR file has to do with timing, where the DDF2BTR.EXE has already sent the request to close the *.BTO file to the PSQL engine. But for whatever reason (e.g., Lazy Write), PSQL does not close that file immediately. We found this happens mostly if you perform DDF2BTR conversions on the client side (workstation side). We have not observed this behavior if you use the DDF2BTR utility on the server side. For that reason, plus the performance consideration, we recommend that you use DDF2BTR only on the PSQL server.
To avoid the potential issues with renaming *.BTO to *.BTR files, starting with Elliott V8.5, upon the successful conversion of a *.BTR file, the system will first attempt to rename the *.BTO file to a *.BTN file. If the rename is successful, then it will delete the *.BTR file, and rename the *.BTN to *.BTR file.
In the conversion log file of DDF2BTR.EXE, if you see the entries showing that the system has difficulty renaming *.BTO or *.BTN files, you could manually rename them to the *.BTR file if necessary.
EMK