problem creating idnex on spatial table with scri

IBM Informix Dynamic Server Version 11.50.UC9 OS Name Linux OS Release 2.6.18-371.1.2.el5 OS Node Name wldbt635 OS Version #1 SMP Mon Oct 7 16:34:35 EDT 2013 OS Machine x86_64 ArcSDE Version 10.0 a believe it or not story when i create a index on a spatial table using a script,run from command line , with following command echo "create index shape_ix1 on parcels(shape st_geometry_ops) using rtree" | dbaccess $DATABASE; echo "update statistics for table parcels" | dbaccess $DATABASE the index is created apparently ok but in fact is not. No error messages are received. When search is done via arccatalogue for maps index is not used, If I open dbaccess in interactive mode that is dbaccess - Query-language -new create index shape_ix1 on parcels(shape st_geometry_ops) using rtree the index is created ok doing search with arccatalogue now uses index and performs ok Has anyone else met this starnge behaviour? We have had this same probem on 5 spatial databases

You ran dbaccess in menu mode. In that mode dbaccess issues a BEGIN WORK immediate upon connecting to the database and if you don't COMMIT WORK within your session whatever you did gets rolled back. Options: - Run dbaccess in command line mode by adding a dash (-) at the end of the command line: echo "create index shape_ix1 on parcels(shape st_geometry_ops) using rtree" | dbaccess $DATABASE - ; - Include a COMMIT WORK in the echo: echo "create index shape_ix1 on parcels(shape st_geometry_ops) using rtree; commit work;" | dbaccess $DATABASE The same goes for the update statistics command. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 20, 2014 at 3:32 PM, KARL OLIVER <karl.oliver@maf.govt.nz>wrote: > IBM Informix Dynamic Server Version 11.50.UC9 > > OS Name Linux > OS Release 2.6.18-371.1.2.el5 > OS Node Name wldbt635 > OS Version #1 SMP Mon Oct 7 16:34:35 EDT 2013 > OS Machine x86_64 > > ArcSDE Version 10.0 > a believe it or not story > when i create a index on a spatial table using a script,run from command > line > , with following command > > echo "create index shape_ix1 on parcels(shape st_geometry_ops) using > rtree" | > dbaccess $DATABASE; > > echo "update statistics for table parcels" | dbaccess $DATABASE > > the index is created apparently ok but in fact is not. No error messages > are > received. > When search is done via arccatalogue for maps index is not used, > > If I open dbaccess in interactive mode > > that is > dbaccess - Query-language -new > > create index shape_ix1 on parcels(shape st_geometry_ops) using rtree> > the index is created ok > doing search with arccatalogue > now uses index and performs ok > > Has anyone else met this starnge behaviour? > We have had this same probem on 5 spatial databases > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c36c78268e5504f2dd7209

I dropped the index - arccatalogue created one . then recreated. the index is created I checked with dbschema. The problem is it is not used. I have used this method for years.

this works fine echo "create database karlstest with log" | dbaccess sysmaster db is created so is there something strange happening when creating index this way- passing command to dbaccess

i have logged a call with IBM support about this, AT this stage they can see no reason why the 2 methods i tried do not work. They are contibuing to look at it , THe problem only happens when creating spatial indexes