The create-index command

-a or (A)	Adjourn the re-building of the index data until a later time using 'rebuild-index'
-c	Make the index case insensitive
-d	Debug of the index code allowed
-lcode	Index Lookup code such as '-lD2' for date conversion of input data during index query
-m or (M)	Disable multi-value index key i.e. use entire attribute for index key
-n or (N)	Any indexes that produce null strings are ignored
-o	Overwrite any existing definition
-s	Write out a pseudo source files
-v	Verbose display, if you rebuild the index, one period character is displayed for each 1000 records rebuilt.
-x or (X)	Exclude records currently in the file when building index data
(S)	Silent mode
(Vnn)	Limit the multi-values to the first nn values in each referenced attribute

The first syntax is the most complex but gives the most flexibility. For example to create an index based on attribute 3 in ascending numeric order you could do this

The full format of the index-definition command argument is given in the section Full Index Creation Definition. The above example shows how the index key will be created by passing each multi-value through the MCN conversion (i.e. leave only numeric characters) and will then be stored in ascending numeric sequence. When the application performs a query against the index key, say with key-select, then the data on the command line to key-select will be passed through the input conversion "D2" as a lookup code before being tested against the index data.

The second syntax will just use an existing DICTionary definition and will create an index name the same as the DICT name. Consider the following DICTionary item :

Now let us assume you use the create-index command in it's second syntax type where a DICT name is specified.

This will create an index whose name is also INDEX1 and the index definition is based on the DICT item INDEX1. In this case it will perform a "MCN" conversion on all multi-values before creating index keys and will store the resulting index keys in right justified (numeric) ascending order. When the data is later queried with key-select, the conversion D2 will be applied to the lookup data before being tested against the index data. Be aware that only A (or S) type dictionary items are supported with the second syntax type.

There are some restrictions to what you can have in attributes 7 and 8 of the DICT item, and the restriction is that the conversion must be one or more (delimited by a value mark) conversion that can be passed through the normal OCONV() or ICONV() function in jBC code. This means things like 'F' correlatives will not work. In these cases you will need to use the first syntax type to create the index definition.

Attribute in DICT item	Usage in the create-index command
2	What attribute number to extract from the record
7	What look-up code to apply to the input data when the index is queried with say the query-index or key-select command.
8	What conversion(s) to apply to the input data when building the index keys
9	If a value R this tells us if a numeric (right) sort is to be performed, otherwise a left sort will be performed.

jBASE allows indexes created using the second syntax to be used with some jQL commands like SELECT or SORT. An index which is not created via a dictionary item must query the index with KEY-SELECT or QUERY-INDEX.

In the examples in the above two syntaxes, they actually are the same thing. The first syntax uses the more complex but more flexible mechanism, the second example uses the simpler but less flexible mechanism.

By default jBASE will create an index key for each multi-value in the attribute. When more than one attribute is specified in the index definition, this causes problems and so the index definition will be rejected. To remedy this the -m or (M) option is required so that the attribute is treated as a single entity instead of a multiple entity. The section on multi-values within an index definition explains this more.

Option -a or (A) means to adjourn the building of the index data from the data file. With this option the create-index will complete very quickly although the index data is not in sync. You will need to later execute a rebuild-index command to make the data in sync.

Option -c means the indexes created will be done in a case insensitive fashion. For example "Fred" and "FRED" will be the same index. This is used automatically in the key-select or query-index command. However if a jQL command such as SORT or SELECT wants to use the index, then the command must be done in such a way that the jQL command would also be case insensitive (for example, attribute 7 of the DICT item is MCU and the selection criteria is all upper case).

Option -d means the pseudo-code created to build the index key can be debugged. This assumes that the debugger is enabled for the rest of the jBC code anyway.

Option -l is the lookup code. It is used with key-select and query-index. What happens is that the selection criteria will be converted using an ICONV call before being used. For example if you create a right justified (numeric) index on say attribute 6 of all items, this could be a date field in internal format. If you want to look at a range of dates then instead of doing this :

where 10638 is a date in internal format, then by using the option "-lD" we will perform an ICONV on the selection criteria of format "D" thus translating the date in external format to internal format, and so your command line would be :

This also applies to selection criteria passed with a jQL command such as LIST or SELECT.

Option -m or (M) means to disable the multi-value part of the index definition. By default when an index definition is created, it means all multi-values in the defined attributes will create an index key. With complex index definitions this is often disallowed. The section on multi-values within a jBASE index definition gives more details.

Option -n or (N) shows that any index keys that are created as zero length strings will not be entered into the index. This is useful for suppressing unwanted index keys. It is especially useful when used in conjunction with the CALL statement in the index definition so that if the subroutine decides the index is not interested in being stored, it creates a null index key.

Option -o will overwrite any existing index definition. Without this, if you attempt to re-define an index definition you will get an error. If the -o option is used to re-define an existing index definition, then all the index data for the definition previously associated with the index will be deleted.

Option -s causes some pseudo source code to be created. This is used with option -d so that you can debug complex index definitions.

Option (S) is the silent option and will cause the create-index command to function silently (unless there is an error of course).

Option -v is the verbose mode and will display a period character for every 1000 records in the file that are rebuilt.

Option (Vnn) will limit the index to the first nn multi-values in an attribute. For example, the option (V2) means then when creating the index data from the original record, only the first 2 values within the attribute will be used.

Option -x or (X) shows that when the index data is created, we create an empty set of index data. In other words we create the index definition, mark the index as being in sync, but don't add any records to the index data. The net result is that you get an index that will contain only newly created records -- very nice when you are dealing with huge files and you only want to process what has changed since the index was created.

Example: Create an index based on attribute 1 , concatenated with the record key. Note we need the -m or (M) option to prevent us getting a multi-value error.

Example: Create an index on the attribute given in the DICTionary definition NAME and then further sorted by the fifth value inside attribute number 3 , but in descending right justified (numeric) order. Note we need the -m or (M) option to prevent us getting a multi-value error.

Example: Create an index on attribute 4 which is normally an internal date. You want to be able to specify dates in external format when doing selections against it. Additionally if the field is a null string, then don�t store any index information.

http://807199.827977/r5/knowledgebase/manuals/3.0/SecondaryIndexes/html/create-index.htm last modified on 06/19/09 05:29 AM

create-index: Create a New Index Definition.