Virtual Table and Indexes

[CGibson]

Hello,

I'm using the app builder in a windows environment and the data is stored on a Linux machine and accessed via ODBC.

1. How do I verify what indexes are created on a table?
- Using the ODBC console tab doesn't seem to help since it appears to just be creating a temporary table and LIST STAT doesn't show indexes (a .DBX file may or may not exist).

2. How do I create an index for a table?
- I don't know if INDEX on fieldname tag tagname is working (see point number 1).

3. How do I set and use an index when virtual tables are used?

Cory

[yvonne.milne]

Hi Cory,

If you are doing it all remotely from the ODBC Console in the App Builder, with the DSN open, you can:

1. Use the SQL CREATE INDEX command to create indexes.

2. Query the SYSINDEXINFO system table to determine which indexes exist, their keys etc. Just use a WHERE clause (the structure is listed on that wiki page) to restrict to the table and/or key you are interested in.

Regards,

Yvonne

[HankFay]

Wow, that's a great tip! (that Create Index works from the ODBC console). Fantastic feature!

I notice that at least some commands (e.g. list tables) work directory from the odbc console. Based on the time delay, it appears that the command is wrapped in a sqlexec call, and the return is marshalled back to the console screen.

I'm thinking this will be a neat way to make experimental (in a test setting) or crises (n an emergency) data schema changes on an ad hoc basis.

thanks,

Hank

[CGibson]

Hi Yvonne,

In the ODBC tab, I tried the following:

use <tablename>
SELECT * FROM sysindexinfo

It seems like it completed although nothing was returned.

If I changed the select statement to lower case, I get:
** Error 104 **
ALIAS name 'XXXXXXX' already in use

Cory

[yvonne.milne]

Hi Cory,

Try in a new ODBC connection, without opening <tablename>, but specifying it in the SELECT statement:

select * from sysindexinfo where table_name = "<tablename>"

Regards,

Yvonne

[CGibson]

Hi Yvonne,

There is now some progress, although not much.

I tried it with several tables.
They all returned blank grids with the sysindexinfo column headers.

Cory

[yvonne.milne]

Hi Cory,

(I'm assuming here that this is your test system that you have said in the past is for your exclusive use, so will not affect other users on the Linux system.)

Try using the SQL create index to create an index, then see if that is then included in the results when you query the sysindexinfo system table.

Regards,

Yvonne

[CGibson]

Hi Yvonne,

Yes this test data and there are no other users accessing this data.

Using SQL create index didn't provide any feedback although using my mapped windows drive, the modified date for the dbx file did change.
Retrying the sysindexinfo query still returns a blank grid.

Cory

[yvonne.milne]

Hi Cory,

Does your ODBC DSN reference a database or a directory?

Regards,

Yvonne

[CGibson]

Hi Yvonne,
It references a directory.

Cory