Kbase P108497: Will a binary dump dump all records if the index used is built on a field with a null or unknown pos
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
05/12/2007 |
|
Status: Unverified
GOAL:
Will a binary dump dump all records if the index used is built on a field with a null or unknown possible value?
FIX:
If an index contains an unknown (?) or null value, Progress sorts that value higher than any other value. There are internal algorithms that ensure that even when a field doesn't contain a value, all records can be found. That is when a field doesn't contain a value, index entry contain a "unknown value" which is a special combination of bits. Null or unknown value is part of the index key. Index manager is capable to find the record id associated with a certain index entry regardless it contain unknown value or not. Null and unknown are taken to mean the same thing. There is no need for anything special. Null values for the key fields will still get index entries. They sort higher than all other index values and even for unique indexes, multiple null values are allowed (the notion being that at the time the record is created, not all key fields have to be filled in and actual values can be filled in later). To dump all the records, all that is needed is a bracket on the whole index. It includes the null values.
The following was tested with 91E02 on HP-UX 11.11 64-bit.
1. pro sports2000 -p disp-address.p
/* disp-address.p */
for each customer:
disp custnum name address.
end.
This demonstrates that there are some records - e.g.
1025 Athlete's Track
1030 Soccer Universe
- that are in the database without values for their address fields. Currently, the address isn't part of any index for sports2000.
2. Dump customer table from sports2000 via Data Dictionary, which reports that 1117 records were dumped.
3. Create new index on cust table of sports2000, Address_Index, make it Primary and delete other indexes on that table.
4. Dump .df for customer table. df shows only index -
ADD INDEX "Address_Index" ON "Customer"
AREA "Cust_Index"
PRIMARY
INDEX-FIELD "Address" ASCENDING ABBREVIATED
5. Create customer database using customer.st -
- prostrct create customer
- procopy $DLC/empty customer
# customer.st
#
b ./customer.b1
#
d "Schema Area":6,32 ./customer.d1
#
d "Cust_Data":7,32 ./customer_7.d1 f 320
d "Cust_Data":7,32 ./customer_7.d2
#
d "Cust_Index":8,32 ./customer_8.d1 f 320
d "Cust_Index":8,32 ./customer_8.d2
6. Load customer.df and customer.d via Data Dictionary; 1117 records are loaded.
7. pro customer -p disp-address.p now shows the following ordering for its first page -
1025 Athlete's Track
1030 Soccer Universe
1149 Hidden Valley Sports
1346 Twin Creeks Sports
1347 Metropolitan Sports
1660 Kitty Hawk Sports Inc
1728 Sherm's Sporting Goods
1878 All American Sporting Goods
1894 Outdoor Outlet
1971 Baker Sporting
2009 Darter Baits
2096 Atkilak's Hunting & Fishing
189 Big 5 Sporting Goods 1 BELLIS FAIR PKY Suite 202
--------------------------------------------------
So a Data Dict dump and load is ok. Now for the binary dump and load -
8. proutil customer -C dump "customer" $PWD
PROGRESS Version 9.1D09 as of Sat May 8 14:01:15 EDT 2004
Using index 8 for dump of table customer. (6101)
Dumped 1117 record(s). (6127)
Binary Dump complete. (6254)
Since no index is specified, the dump will use the primary, though in this database only one is defined.
9. prodel customer
10. Recreate customer and load df via Data Dictionary.
11. proutil customer -C load customer.bd build indexes
Loaded 1117 records.. (6244)
Using the "build indexes" modifier should improve the performance on the load (I've left out the associated -TB, -TM, -SS options here, but advise using these for larger databases).
12. pro customer -p disp-address.p confirms the order as in (7) above.
The records without addresses seem to be ordered sequentially according to the custnum's, so clearly there is some kind of ordering algorithm in use in the background. However, what this does illustrate is that no records are missed out and dumps are complete even in the case where there are null values for the field(s) used in the index.
.