Create Index (31)

The Create Index operation (B_BUILD_INDEX) adds a key to an existing file.


	Op Code	Pos Block	Data Buf	Data Buf Len	Key Buffer	Key Number
Sent
Returned

Prerequisites

•

The file must be open.

•

The number of existing key segments in the file must be less than or equal to maximum number of key segments allowed minus the number of key segments to be added.

•

The maximum number of key segments allowed depends on the file’s page size. The following table shows these values:


Page Size (bytes)	Maximum Key Segments byFile Version
Page Size (bytes)	8.x and prior	9.0	9.5
512	8	8	rounded up2
1,024	23	23	97
1,536	24	24	rounded up2
2,048	54	54	97
2,560	54	54	rounded up2
3,072	54	54	rounded up2
3,584	54	54	rounded up2
4,096	119	119	204 (or 119)3
8,192	n/a1	119	420 (or 119)3
16,384	n/a1	n/a1	420 (or 119)3
1”n/a” stands for “not applicable” 2”rounded up” means that the page size is rounded up to the next size supported by the file version. For example, 512 is rounded up to 1,024, 2,560 is rounded up to 4,096, and so forth. 3The maximum number of index segments that can be used with the relational interface is 119. For the transactional interface, the maximum number is 204 for a page size of 4,096, and 420 for page sizes 8,192 and 16,384.

•

Ensure that the key flags, position, and length of the new key are appropriate for the file to which you are adding the key.

•

No transactions can be active.

Procedure

Set the Operation Code to 31.

Pass the Position Block for the file to which to add the key.

For each segment in the key, store a 16-byte key specification block in the Data Buffer. Use the same structure as defined in Table 8. Store the information for the key position and the key length as integers. If you are rebuilding the system-defined log key (also called system data), the Data Buffer must be at least 16 bytes long and initialized to zeroes.

To define an ACS for the new key, perform one of the following steps:

•

To use the default ACS, which is the first ACS already defined in the file, specify the Use Default ACS attribute in the Key Flags word.

•

To define a new ACS, specify the Use Numbered ACS attribute in the Key Flags word and set the ACS Number field to zero (0). In addition, store the 265-byte ACS after the last key specification block in the Data Buffer.

•

To specify an existing ACS by name, specify the Use Named ACS attribute in the Key Flags word and set the ACS Number field to zero (0). In addition, store the name of the ACS at the beginning of the 265-byte block after the last key specification block in the Data Buffer. (The remainder of the ACS block after the name is ignored.) The name must be in one of the following formats:


ACS Type	Length (in Bytes)	Description
User-defined ACS	1	Signature 0xAC
“	8	ACS table name
ISR	1	Signature 0xAE
“	16	ISR table name

Set the Data Buffer Length parameter to the number of bytes in the Data Buffer. For a new key with no ACS (or one that uses the default ACS), use the following formula to determine the correct Data Buffer Length:

16 * (# of segments)

If the new key specifies an ACS other than the default, use the following formula to determine the correct Data Buffer Length:

16 * (# of segments) + 265

To assign a specific Key Number to the key being created, add the desired key number to 0x80, and place the sum in the Key Number parameter. If you are rebuilding the system-defined log key (also called system data), specify 0xFD (that is, key number 125 plus 128).

Note: Key numbers must be unique to the file. They must also be valid. (The value of each key number must be less than the maximum number of key segments allowed for the file’s page size.)

Details

The transactional interface allows you to assign specific key numbers when creating a key. This capability complements the ability to delete a key and not have the transactional interface renumber all keys that have a key number greater than that of the deleted key. If an application drops an index and instructs the transactional interface not to renumber higher-numbered keys, and a user then clones the affected file without assigning specific key numbers, the cloned file has different key numbers than the original.

If you define an ACS in the Data Buffer, the transactional interface first checks for an existing ACS (using the name you specified) before adding it to the file. If the transactional interface finds an existing ACS with the name you specified, the transactional interface does not duplicate the ACS definition in the file, but does associate the ACS with the new key.

If you specify the Use Named ACS attribute in the Key Flags word, the transactional interface uses the ACS name supplied in the Data Buffer to locate an ACS of the same name within the file, then assigns that ACS to the new key.

If a file is opened by more than one transactional interface and a client initiates a Create Index process, remote clients can perform Get and Step operations on the same file while the transactional interface creates the key.

If the key being created is not an AUTOINCREMENT key, the Get and Step operations of remote clients can have lock biases, and when the Create Index process is completed, you can update and delete the locked records without issuing additional read operations. This is possible because the transactional interface does not have to change the images of the records in order to create the key.

However, if the key being created is an AUTOINCREMENT key, the transactional interface has to both build the index and change every record with a zero value in the appropriate field. Remote clients that perform Get or Step operations without a lock bias before or during the key creation can receive Status Code 80 when they execute an update or delete operation after the successful completion of the key creation.

Also, the transactional interface returns Status Code 84 if a client attempts to create an AUTOINCREMENT key while another client has locked a record. Similarly, the transactional interface returns Status Code 85 if a client attempts to execute a Get or Step operation with a lock bias during index creation for an AUTOINCREMENT key by another client.

Result

The transactional interface immediately adds the new key to the file. The time required for this operation depends on the total number of records to be indexed, the size of the file, and the length of the new index.

If the Create Index operation is successful, the number of the new key is either the number you specified or one of the following:

•

For files that have no gaps between key numbers, the key number is one higher than the previous highest key number.

•

For files that have gaps between key numbers, the key number is the lowest missing key number.

You can use the new key to access your data as soon as the operation completes.

If the Create Index operation is unsuccessful, the transactional interface drops whatever portion of the new index it has already built. The file pages allocated to the new index prior to the error are placed on a list of free space for the file and reused when you insert records or create another key.

If the operation fails during the creation of an AUTOINCREMENT key, any values that have already been altered remain altered. The transactional interface can return the following status codes:


22	The data buffer length is too short.
27	The key position is invalid.
41	The transactional interface does not allow the attempted operation.
45	The specified key flags are invalid.
49	The extended data type is invalid.
56	An index is incomplete.
84	The record or page is locked.
85	The file is locked.
104	The transactional interface does not recognize the locale.
134	The transactional interface cannot read the International Sorting Rule.
135	The specified International Sort Rule table is corrupt or otherwise invalid.
136	The transactional interface cannot find the specified Alternate Collating Sequence in the file.

If processing is interrupted during the creation of a key, you can access the data in the file through the file’s other keys. However, the transactional interface returns a nonzero status code if you try to access data by the incomplete index. To correct this problem, drop the incomplete index with a Drop Index operation (32) and reissue the Create Index operation.

Positioning

The Create Index operation has no effect on any file currency information.