Insight 204
Register Now for 2005! By Marie Kelly Director of Marketing
Registration is now OPEN for Insight 204!
CCA is now accepting registrations for the next Insight 204 Symposium, which takes place June 5-8, 2005 in Boston at the Hyatt Harborside Hotel. With a major new version of Model 204 being released in the spring, this is an event you definitely do not want to miss. Visit the Insight 204 Web site at http://www.cca-int.com/resources/usergroups/insight/main_2005.html and reserve your spot today!
As always, registration for CCA's Insight 204 Symposium is FREE! The Web site contains preliminary information, which will be updated as more details are confirmed. So, bookmark the Web site today and spread the word to all Model 204 users at your site.
If you have any questions that need to be answered immediately, please complete the Feedback Form on the Web site. We look forward to seeing you in Boston in June!
Model 204
USE OF AND ACCESS TO PRODUCTS AND FEATURES ARE IN ACCORDANCE WITH THE TERMS AND CONDITIONS OF THE USER’S SOFTWARE LICENSE. THE PRESENTATION OF MATERIAL HEREIN DOES NOT, IN ANY MANNER, MODIFY SUCH TERMS AND CONDITIONS.
Looking for Performance Improvements in Table B By James Damon
File performance is affected by the physical layout of Table B. The number of records per page (the BRECPPG parameter setting), the amount of freespace per page, the number of unused record numbers or record slots per page all play a role in how a file performs when accessed by User Language programs. The TABLEB command lets a File Manager or DBA look into the file and get a sense of how well data is compacted in Table B and how much I/O is required to process a given number of records. It can also provide insight into how data has changed over time.
Table B page layout The following schematic represents the layout of a Table B page. It assumes BRECPPG=4.
Figure 1. Table B schematic
A Table B page consists of a variable number of 2-byte offset pointers (which are not preallocated) at the top of the page, followed by contiguous free space, followed by records starting at offset X’1800’ minus 1, followed by a 40-byte page trailer that is reserved for overhead. The page grows from the top down and from the bottom up. The page is full when:
The 2-byte numbers at the top of the page are offsets to other locations on the page. In Figure 1, the first offset (X’000A’) is the pointer to the free space pointer. The next four offsets point to records on the page. The offset X'1800' points to relative record 0 on this page.
Relative record numbers A relative record number is the number given a record on a particular Table B page. On each page, the relative record numbers start with 0 and go to a maximum of BRECPPG minus 1.
Absolute record numbers An absolute record number is derived from a Table B page number and BRECPPG. In Figure 1 with BRECPPG=4, if you were looking at Table B page 100, the absolute record number of records on that page would start at 400 (BRECPPG*100) and continue through a maximum, 403. Although some records may not exist on Table B page 100, all the record numbers are assigned or accounted for so that the absolute record numbers on Table B page 101 begin with 404.
The last offset, X'12B0', points to the first byte of free space on the page, and thus is called the pointer to free space. Together, the pointer to the free space pointer and the pointer to free space delimit the record pointer slots. Although there can be no more than BRECPPG record-pointer slots, there may be fewer, so the two delimiters are required to identify the record pointer slots.
Displaying Table B pages The TABLEB LIST command provides a quick picture of each Table B page. It scans the 2-byte pointers at the top of each page to determine:
This listing will continue through all the pages of Table B unless you terminate processing with C(cancel), K(kill), or PA1.
Evaluating TABLEB LIST output The following is an example of the output from the TABLEB LIST command. The file, MYFILE, has parameter settings of BRECPPG=25 and BRESERVE=250, These settings will prove to be optimal, as we will see later on.
Figure 2. Sample output from TABLEB LIST for pages 0 through 15
VIEW CURFILECURFILE MYFILE CURRENT FILETABLEB LIST PAGE NO. FREE SPACE FREE SLOTS 0 227 0 1 216 0 2 85 0 3 220 0 4 405 0 5 296 0 6 291 0 7 276 0 8 228 0 9 229 0 10 236 0 11 340 0 12 316 0 13 212 0 14 293 0 15 179 0
We will also keep our eye on the VIEW only statistics, BHIGHPG and EXTNADD, to help us evaluate performance, which for Figure 1 are:
You can view a sample of pages anywhere in the file using slightly different TABLEB command syntax. In Figure 3, we are looking at the final 14 pages in Table B for MYFILE, using the TABLEB PAGES command:
Figure 3. Sample output from TABLEB PAGES 40459 TO 40472
TABLEB PAGES 40459 TO 40472 PAGE NO. FREE SPACE FREE SLOTS 40459 240 0 40460 5857 24 40461 6086 25 40462 6086 25 40463 6086 25 40464 6086 25 40465 6086 25 40466 6086 25 40467 6086 25 40468 6086 25 40469 6086 25 40470 6086 25 40471 4116 15 40472 1411 0 5177 AVG. FREE SPACE PER PAGE 20 AVG. FREE SLOTS PER PAGE 14 NUMBER OF PAGES PROCESSED 25 BRECPPG - TABLE B RECORDS PER PAGE250 BRESERVE - TABLE B RESERVED SPACE PER PAGE
Using the results from the TABLEB command Comparing Figures 2 and 3, we can see immediately that data stored at the beginning of the file has affected Table B in a different way than data stored at the end. There is less free space per page in pages 0-15 and there are fewer free record slots in those pages than at the end of the file. This is a reuse record number file, so new records may be stored on any Table B page that has free record numbers and free space. The last pages appear to have had records stored then deleted, because the free space is nearly equal to the available space (6144 bytes) on a Table B page. Also, the free slots are equal to BRECPPG, meaning that no records are currently stored on those pages.
There are two general points to bear in mind when analyzing TABLEB LIST output:
Analyzing a TABLEB RECLEN command Average record length is computed when the TABLEB RECLEN command is issued. This command scans every page in Table B from 0 to BHIGHPG and provides results similar to Figure 4. Under the assumption that each physical I/O to Table B takes roughly 2-5 ms, this will require somewhere around 80 to 200 seconds of elapsed time to run.
Figure 4. Calculating the average record length in Table B
TABLEB RECLEN 182 AVG. FREE SPACE PER PAGE 0 AVG. FREE SLOTS PER PAGE 40473 NUMBER OF PAGES PROCESSED 25 BRECPPG - TABLE B RECORDS PER PAGE 250 BRESERVE - TABLE B RESERVED SPACE PER PAGE 241 AVG. RECORD LENGTHE
The results indicate that BRECPPG and BRESERVE are set to optimal values. The average record length of 241 bytes indicates that BRECPPG=25 is just about ideal: 6144 / 241 = 25.5. BRESERVE is typically set to average record length and the setting of 250 is also pretty close to optimum. There are very few extension records—EXTNADDD=407, out of a total record count of 999,847. There is very little free space per page and most pages have 25 records stored with no wasted record numbers, as shown by:
AVG. FREE SLOTS PER PAGE = 0
Assessing the TABLEB command output These results can also indicate a file that needs reorganization. If any of the following are true, you may need to reorganize the file with new values for BRECPPG and BRESERVE:
In Summary The TABLEB command and its variations can be extremely useful in providing a quick picture of Table B. It can assist you in decisions regarding file reorganization and file parameter settings. It also provides insight into how the file is being used and how recently stored records are affecting page utilization, compared to records stored when the file was first created. The TABLEB command can be very handy to a File Manager or DBA whose responsibility involves managing files, monitoring and maintaining high performance, and should be added to your repertoire.
System 1032
Back to Basics, Part 4 – Enabling Indexing By Tym Stegner
In the previous articles of this Back to Basics series, we described the components and options that define attributes for storing correctly in System 1032 datasets. In this article, we examine the class of attribute options that enable indexing. The attribute definition option that supports indexing is KEYED.
Attributes are indexed primarily for use in a FIND command query. A FIND command query performed against one or more attributes defined with the KEYED option enables the fast selection of records without System 1032 reading the underlying data records, because key information is stored separately in optimized structures known as key tables. (Note: The SEARCH command query is used against attributes that are not defined with the KEYED option.
All data types support indexing. The KEYED option has additional options based on data type.
Keying Text Data Types Text and Text Varying attributes are by far the most commonly indexed attributes, closely followed by Date/Time attributes. You can index fixed-length text attributes from 1 to 80 characters.
KEYED [[IGNORE_CASE | USE_CASE][MULTINATIONAL | NO_MULTINATIONAL]] | [[n:n]]
For text attributes, three sets of optional parameters apply:
Keying Compound Data Types
KEYED [FOR_ALL | BY_ELEMENT]
For arrayed attributes, the FOR_ALL and BY_ELEMENT options specify the dimensional granularity by which an array is keyed. The FOR_ALL option lets you query to match any array element that qualifies. Whereas, the KEYED BY_ELEMENT option lets you query to match a specific array element. The FOR_ALL and BY_ELEMENT options are mutually exclusive.
Note: For grouped attributes, you cannot key the group itself. If you want to make a query based on the entire group value, you must define a composite attribute that contains all the data from the individual group elements. Use of Record Descriptors and trigger procedures can automate populating and updating composite attribute values, which will be discussed in a later article.
Keying Binary Varying Data Type
KKEYED [[n:m]]
You can key a Binary Varying attribute to index up to 40 consecutive bytes of the first 80 characters of the value.
Keying Other Data Types The remaining data types--Integer, Real, Decimal, Date/Time, Logical, Special--support indexing as well. Take care in query commands to specify values using the proper precision to locate the desired results.
Performance Considerations The query results from keyed attributes are returned much more easily and quickly than attributes without keys. However, records with many keyed attributes are updated more slowly than those with fewer keyed attributes, due to the additional data structures that must be updated for the record.
Arrayed attributes defined with the BY_ELEMENT option are updated more efficiently than an array having the FOR_ALL option. However, the BY_ELEMENT option requires additional data storage for the attribute. In Summary
In Summary You can not only store and represent the data you enter in System 1032 with the ATTRIBUTE command options, but you can also specify the data that you want retrieved quickly. The KEYED option of the ATTRIBUTE command enables System 1032 to index attribute values for faster query results. Not all attributes in a record should be keyed, but you might consider keying the values you query most frequently.
Copyright © 2008 Computer Corporation of America. All right reserved. Published in the United States of America.
