TCL

create-index command

The create-index BASIC program creates a new B-tree index in the specified file using the a (algebraic) processing code expression provided and ensures the a0 index case-sensitivity matches the item-ID case sensitivity.

Note:

D3 does not support indexes on sub-values.
For the a0 index only, the c option must be used if the file was created with the s option. If the file was not created with the s option, the c option cannot be used when creating the a0 index for that file.

Syntax

 
 create-index file.reference a.code{(options)}
 create-index file.reference attribute.defining.item{(options)}
 create-index file.reference* {(options)}

Parameter(s)


`a.code`	Specifies the a processing code to be used in forming the keys to the index. The processing code must include an attribute number immediately after the a and cannot contain any named attribute references. The first attribute number in the processing code is the master attribute. This attribute determines the number of values that an index key generates. Note: The processing code does not require an attribute number immediately after the a on D3 Windows FSI. However, these indexes can only be used in BASIC. They will not be used by the update processor or Access. The first attribute referenced in the a.code parameter determines the number of multivalues contained in the index. Regarding the use of translates when creating indexes For Hot Backup (Windows and UNIX): All indexes using translate correlatives should contain a fully-pathed file reference. This is necessary because the Hot Backup slave processes operate out of the DM or FSIDM accounts. For example: create-index file1 a1(tmainaccount,file2,;x;;1)) For Windows: Great caution should be taken when using translates during index creation since index corruption can occur. To avoid such issues, always use the full path to the file when employing translates. This practice ensures that the backup process can reliably locate and access the necessary files. For example: create-index file1 a1(tfsidm,tfile2,;x;;1)) For UNIX: Use the s (subindex) processing code to employ translates. Just as with Windows, always provide the full path for file references to maintain index integrity during the backup process.
`specifier`	*	Designates all indexes. This can be used as an alternate to a series of create-index commands by placing the i processing codes directly into the correlative position of file-defining item (D-pointer), and then using the asterisk with the create-index command.
`options`	c	Specifies the keys are case sensitive. Default is case insensitive. If the `*` specifier is used, the index(es) will remain case insensitive. As of D3 v9.0, you can create an index with a casing different than what is established by the TCL case command (defaults to off), but AQL will not use it unless the TCL case command setting matches the index casing. The BASIC root and key statements will use the specified index that matches the TCL case command setting, unless the BASIC casing statement is used to override it.
	f	For Windows FSI only: Creates floating-point indexes (where the keys are floating-point numbers, such as `'1.234E123'. 2`). Index keys might be normalized (plus signs and non-significant digits are removed, and decimal points are moved). For example: `'+00123e12'` is entered in the index as `'123e12'`. Tip: If the keys do not have exponents (for example: '`123.456`'), use numeric indexes instead.
	l	Locks the file on which it is currently working to facilitate parallel access by other users. The l option forces a create-index to lock the entire file during index creation.
	n	Creates numeric indexes. Numeric indexes are sorted according to their numerical value. For example: "10.5" is greater than "9.34" and "9 June 1980" is less than "1 Aug 2010" When searching for the first entry in the index, use the smallest expected negative number as the first search key. The empty string "" is considered to be 0. For example: s=-9999 i='' key('n',r,s,i) then... finds -9000, but does not find -10000. Inserting non-numeric keys in the index produces unpredictable results. Because the locale is used to determine the thousands and decimal separators, it is strongly recommended to store numerics in scaled internal format. That is, with no decimal or thousands separators {-} `n` {`n`...}. This ensures correct behavior when accessing the index from locations with different locales. Note: For numeric indexes to work properly in the vme, numeric data must be stored in scaled internal format. That is, with no decimal or thousands separators {-} `n` {n...}. This ensures correct behavior when accessing numeric indexes.
	o	Re-creates the index, overriding the previous version.
	s	Suppresses the display of the running count of items.

Warning:

You cannot create an index with a translate to the same file.
An index created with a multivalued a processing code is not supported, whether it is created with an a code or an attribute-defining item.
When you create indexes using attribute-defining items containing translate correlatives (A8), the translate correlative must contain the attribute reference, not infer the attribute reference from A2.
Example: a1(tcustomers;x;;3)
For FSI Hot Backup only: When you place the operation into the queue, it will add the o option, regardless of what has been added on the primary server.
Creating indexes on attributes that contain over 32767 values is not supported and may result in errors.

VME indexes

The number following i indicates the root FID of the index in the VME. This should never be manually altered.

VME index keys have a maximum length of 249.

FSI indexes

The number following i indicates the number of the index in the FSI. This should never be manually altered.

FSI index keys have no maximum length.

Example(s)

Example 1

Creates an index on the contents of attribute 1.

 create-index entity a1

Example 2

Creates an index on the results returned from attribute 3 of invoices, translated to customer.file, and returning attribute 1 from the item, if found.

 create-index invoices a3(taccount,customer.file,;x;;1)

Example 3

Creates an index on attribute 1 concatenated with attribute 3.

 create-index sales a1:3

Example 4

Illustrates how a series of indexes can be built:

Index references are placed in the correlative attribute (8) of the (entity) files D-pointer. For example:
```
 correlative   Ia1
 Ia23
```
The command is issued.
```
 create-index entity *
```
Afterward, the correlative attribute contains the root FID for each of the indexes.

Referenced by

8 topics mention create-index command in its description.

list-file-access command Cursor movement commands Features not supported for D3 Windows i (index) processing code nxtkey statement prvkey statement System privileges Using the Update processor as a data processor