Hi all,

This is driving me crazy trying to figure out. I have a GDB writer that builds an attribute index after write.

• CREATE INDEX <name_INDEX> ON <table_name> (<attribute_name>)

Unbeknownst to me, each time I updated the data, an additional index was being created instead of overwritten which led to a bloated GDB and blown out write times. Note: The GDB table handling is set to truncate before write.

I first went to using the below statement, but I was unsuccessful. 

• ALTER INDEX <name_INDEX> ON <table_name> REBUILD

The next attempts were focussed on dropping the index before write and creating it anew afterwards.

My problem is that no matter which format I use, FME errors with ‘An invalid SQL statement was used’. I believe the issue could be with the INDEX parameter because it isn’t highlighted by the syntax highlighter and leaving it out provides a useful error:  'An expected Field was not found or could not be retrieved properly.

• ALTER TABLE <table_name> DROP <name_INDEX>.

Here are the statement formats I’ve tried:

• DROP INDEX <name_INDEX> ON <table_name>
• DROP INDEX <name_INDEX> ON <table_name>(<attribute_name>)
• DROP INDEX <name_INDEX> ON <table_name>.<attribute_name>
• DROP INDEX <table_name>.<name_INDEX>
• DROP INDEX <table_name>(<name_INDEX)
• ALTER <table_name> DROP INDEX <name_INDEX>
  • including the above variations of ( ) and dot notation on the table name
• I’ve also inserted IF EXISTS before and after the DROP INDEX command to no effect.

Does anyone know how I can achieve this? Any help is much appreciated.