Internal error in routine sqoptim
After migrating from Informix 11.10 to 12.10fc6, a SELECT DISTINCT query failed with error -768 (internal error in sqoptim: no valid query plan generated). The issue was related to multi-index scan optimization. Suggested workarounds included setting the undocumented MULTI_INDEX_SCAN configuration parameter to 0, or using the +AVOID_MULTI_INDEX query directive. Opening a PMR with IBM was recommended.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, We get this error after do informix migration from 11.10 to 12.10fc6. program stop at line 2057 sql statement error number -768 internal error in routine sqoptim: no valid query plan generated. When i check line 2057. "select distinct prm_basic from prm_sch_hdr" It means we cannot use "select distinct", how to fix this issue. When i check with informix error "IT06168". problem because using multi-index scan but this error already fix In IBM Informix Server 12.10.xC5. IT should work because now we used 12.10.fc6. Please advice what we need to do to fix this problem ? Thank You
Hi, you can try set configuration parameter MULTI_INDEX_SCAN to 0 in Informix configuration file to turn off system wide. Petr Dne 7.10.2016 v 10:43 MOHD FADZIL JUSOH napsal(a): > Hi, > > We get this error after do informix migration from 11.10 to 12.10fc6. > > program stop at line 2057 > sql statement error number -768 > internal error in routine sqoptim: no valid query plan generated. > > When i check line 2057. > > "select distinct prm_basic from prm_sch_hdr" > > It means we cannot use "select distinct", how to fix this issue. > > When i check with informix error "IT06168". problem because using multi-index > scan but this error already fix In IBM Informix Server 12.10.xC5. IT should > work because now we used 12.10.fc6. > > Please advice what we need to do to fix this problem ? > > Thank You > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Hi, I cannot find MULTI_INDEX_SCAN on configuration file. It's new parameter need to add into our config file? Thank You
It's**undocumented config. parammeter. petr Dne 7.10.2016 v 11:12 MOHD FADZIL JUSOH napsal(a): > Hi, > > I cannot find MULTI_INDEX_SCAN on configuration file. It's new parameter need > to add into our config file? > > Thank You > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
you can try use the +AVOID_MULTI_INDEX directive at query level Petr Dne 7.10.2016 v 11:12 MOHD FADZIL JUSOH napsal(a): > Hi, > > I cannot find MULTI_INDEX_SCAN on configuration file. It's new parameter need > to add into our config file? > > Thank You > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Open a PMR with IBM? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com 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 Fri, Oct 7, 2016 at 4:43 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > We get this error after do informix migration from 11.10 to 12.10fc6. > > program stop at line 2057 > sql statement error number -768 > internal error in routine sqoptim: no valid query plan generated. > > When i check line 2057. > > "select distinct prm_basic from prm_sch_hdr" > > It means we cannot use "select distinct", how to fix this issue. > > When i check with informix error "IT06168". problem because using > multi-index > scan but this error already fix In IBM Informix Server 12.10.xC5. IT should > work because now we used 12.10.fc6. > > Please advice what we need to do to fix this problem ? > > Thank You > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c0c2934ba92a5053e43e300