Cannot add index

Hi all, we have a strange situation when creating a functional index. Following table is base for this index: CREATE TABLE crm_travels ( customer_id INTEGER NOT NULL, item_id INTEGER NOT NULL, supplier_id VARCHAR(32), booking_id VARCHAR(20), no_of_persons INTEGER, date_of_booking DATE, booking_user INTEGER, start_travel DATE, end_travel DATE, status VARCHAR(2), total_price MONEY, booking_orgunit NVARCHAR(64), order_id INTEGER, order_no INTEGER, mediator_id VARCHAR(32), mediator_affiliate VARCHAR(10), destination_code NVARCHAR(20), traveltype_key VARCHAR(2), transport_key VARCHAR(2), persons_key VARCHAR(2), destination_key VARCHAR(2), duration_key VARCHAR(2), PRIMARY KEY(booking_orgunit,customer_id,item_id) constraint pk_crm_travels ) ALTER TABLE crm_travels ADD CONSTRAINT ( FOREIGN KEY(customer_id) REFERENCES customer(customer_id) CONSTRAINT fk_crm_travels_customer ) Function to index is: CREATE FUNCTION get_cust_first_travel_booking_date(p_customerId integer) RETURNS Date with (not variant); DEFINE first_booking Date; select min(date_of_booking) into first_booking from crm_travels where customer_id = p_customerId; IF first_booking is null THEN LET first_booking = MDY(12, 31, 1799); END IF; RETURN first_booking; We want to add a functional index: CREATE INDEX cftBookingDateIndex ON crm_travels ( get_cust_first_travel_booking_date(customer_id)) Error message is: 212: Cannot add index 106: ISAM error: non-exclusive access I have tried this on my personal instance, freshly started, no sessions active, executed in dbaccess. IDS Version 11.50.UC5DE (Developer Edition, but also does not work in Production 11.10FC3, same error) Linux systems, Ubuntu on mine, Production with Debian 64bit Any idea what we do wrong ? Marcus Haarmann

I had no problems defining all of this on my 11.70 server on Ubuntu 9 except for the foreign key since I don't have your customer table. Here's the output from myschema afterwards (function text edited in from a separate run): CREATE TABLE crm_travels ( customer_id INTEGER NOT NULL, item_id INTEGER NOT NULL, supplier_id VARCHAR(32,0), booking_id VARCHAR(20,0), no_of_persons INTEGER, date_of_booking DATE, booking_user INTEGER, start_travel DATE, end_travel DATE, status VARCHAR(2,0), total_price MONEY(16,2), booking_orgunit NVARCHAR(64,0) NOT NULL, order_id INTEGER, order_no INTEGER, mediator_id VARCHAR(32,0), mediator_affiliate VARCHAR(10,0), destination_code NVARCHAR(20,0), traveltype_key VARCHAR(2,0), transport_key VARCHAR(2,0), persons_key VARCHAR(2,0), destination_key VARCHAR(2,0), duration_key VARCHAR(2,0) ) IN datadbs EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW; { Please review extent sizing and adjust to allow for growth. } REVOKE ALL ON crm_travels FROM public;-- Index < 208_25> is a constraint index. Creating normal index: P208_25. CREATE UNIQUE INDEX P208_25 ON crm_travels ( booking_orgunit ASC, customer_id ASC, item_id ASC ) USING btree IN datadbs; -- Check index location. Constraint indexes are created in ROOTDBS! CREATE FUNCTION get_cust_first_travel_booking_date (p_customerId integer) RETURNS Date with (not variant); DEFINE first_booking Date; select min(date_of_booking) into first_booking from crm_travels where customer_id = p_customerId; IF first_booking is null THEN LET first_booking = MDY(12, 31, 1799); END IF; RETURN first_booking; end function; CREATE INDEX cftbookingdateindex ON crm_travels ( get_cust_first_travel_booking_date( customer_id ) ASC ) USING btree IN datadbs; GRANT SELECT, UPDATE, INSERT, DELETE, INDEX ON crm_travels TO "public"; ALTER TABLE crm_travels ADD CONSTRAINT PRIMARY KEY ( booking_orgunit, customer_id, item_id ) CONSTRAINT pk_crm_travels; Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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 Wed, Dec 8, 2010 at 11:11 AM, Marcus Haarmann <marcus.haarmann@midoco.de>wrote: > Hi all, > > we have a strange situation when creating a functional index. > > Following table is base for this index: > CREATE TABLE crm_travels (> > customer_id INTEGER NOT NULL, > > item_id INTEGER NOT NULL, > > supplier_id VARCHAR(32), > > booking_id VARCHAR(20), > > no_of_persons INTEGER, > > date_of_booking DATE, > > booking_user INTEGER, > > start_travel DATE, > > end_travel DATE, > > status VARCHAR(2), > > total_price MONEY, > > booking_orgunit NVARCHAR(64), > > order_id INTEGER, > > order_no INTEGER, > > mediator_id VARCHAR(32), > > mediator_affiliate VARCHAR(10), > > destination_code NVARCHAR(20), > > traveltype_key VARCHAR(2), > > transport_key VARCHAR(2), > > persons_key VARCHAR(2), > > destination_key VARCHAR(2), > > duration_key VARCHAR(2), > > PRIMARY KEY(booking_orgunit,customer_id,item_id) constraint > pk_crm_travels > ) > ALTER TABLE crm_travels> > ADD CONSTRAINT ( FOREIGN KEY(customer_id) > REFERENCES customer(customer_id) CONSTRAINT fk_crm_travels_customer ) > > Function to index is: > CREATE FUNCTION get_cust_first_travel_booking_date(p_customerId integer)> > RETURNS Date with (not variant); > > DEFINE first_booking Date; > > select min(date_of_booking) into first_booking from crm_travels > where customer_id = p_customerId; > > IF first_booking is null THEN > > LET first_booking = MDY(12, 31, 1799); > > END IF; > > RETURN first_booking; > > We want to add a functional index: > CREATE INDEX cftBookingDateIndex ON crm_travels ( > get_cust_first_travel_booking_date(customer_id))> > Error message is: > 212: Cannot add index > 106: ISAM error: non-exclusive access> > I have tried this on my personal instance, freshly started, no sessions > active, executed in dbaccess. > IDS Version 11.50.UC5DE (Developer Edition, but also does not work in > Production 11.10FC3, same error) > Linux systems, Ubuntu on mine, Production with Debian 64bit > > Any idea what we do wrong ? > > Marcus Haarmann > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3054ace3b562600496e8c4da

Hi, Working from the error codes given and the example described where you just brought up your personnel instance. I would ask this question: Did you turn off scheduler ? the SCHAPI ? If auto update stats is running against the given table you will not be able to get an exclusive lock. Try running the following in sysadmin a few minutes prior to running the index create: DATABASE sysadmin; EXECUTE FUNCTION task("scheduler stop"); CLOSE DATABASE; George. From: "Marcus Haarmann" <marcus.haarmann@midoco.de> To: ids@iiug.org Date: 12/08/2010 10:12 AM Subject: Cannot add index [22158] Sent by: ids-bounces@iiug.org Hi all, we have a strange situation when creating a functional index. Following table is base for this index: CREATE TABLE crm_travels ( customer_id INTEGER NOT NULL, item_id INTEGER NOT NULL, supplier_id VARCHAR(32), booking_id VARCHAR(20), no_of_persons INTEGER, date_of_booking DATE, booking_user INTEGER, start_travel DATE, end_travel DATE, status VARCHAR(2), total_price MONEY, booking_orgunit NVARCHAR(64), order_id INTEGER, order_no INTEGER, mediator_id VARCHAR(32), mediator_affiliate VARCHAR(10), destination_code NVARCHAR(20), traveltype_key VARCHAR(2), transport_key VARCHAR(2), persons_key VARCHAR(2), destination_key VARCHAR(2), duration_key VARCHAR(2), PRIMARY KEY(booking_orgunit,customer_id,item_id) constraint pk_crm_travels ) ALTER TABLE crm_travels ADD CONSTRAINT ( FOREIGN KEY(customer_id) REFERENCES customer(customer_id) CONSTRAINT fk_crm_travels_customer ) Function to index is: CREATE FUNCTION get_cust_first_travel_booking_date(p_customerId integer) RETURNS Date with (not variant); DEFINE first_booking Date; select min(date_of_booking) into first_booking from crm_travels where customer_id = p_customerId; IF first_booking is null THEN LET first_booking = MDY(12, 31, 1799); END IF; RETURN first_booking; We want to add a functional index: CREATE INDEX cftBookingDateIndex ON crm_travels ( get_cust_first_travel_booking_date(customer_id)) Error message is: 212: Cannot add index 106: ISAM error: non-exclusive access I have tried this on my personal instance, freshly started, no sessions active, executed in dbaccess. IDS Version 11.50.UC5DE (Developer Edition, but also does not work in Production 11.10FC3, same error) Linux systems, Ubuntu on mine, Production with Debian 64bit Any idea what we do wrong ? Marcus Haarmann ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, we have now been able to create the index. We had not to use the crm_travels table but the connected customer table as basis. CREATE INDEX cftBookingDateIndex ON customer ( get_cust_first_travel_booking_date(customer_id)) Creating the index the way Art described was possible when there is no data in the tables, but lead to an error when filled. Dont know why, but we have only a couple of such indexes in the system and never had these problems before. Thanks all for your help. Marcus -----Original Message----- From: George_Palmer@aotx.uscourts.gov [mailto:George_Palmer@aotx.uscourts.gov] Sent: Wednesday, December 08, 2010 5:53 PM To: ids@iiug.org Subject: Re: Cannot add index [22162] Hi, Working from the error codes given and the example described where you just brought up your personnel instance. I would ask this question: Did you turn off scheduler ? the SCHAPI ? If auto update stats is running against the given table you will not be able to get an exclusive lock. Try running the following in sysadmin a few minutes prior to running the index create: DATABASE sysadmin; EXECUTE FUNCTION task("scheduler stop"); CLOSE DATABASE; George. From: "Marcus Haarmann" <marcus.haarmann@midoco.de> To: ids@iiug.org Date: 12/08/2010 10:12 AM Subject: Cannot add index [22158] Sent by: ids-bounces@iiug.org Hi all, we have a strange situation when creating a functional index. Following table is base for this index: CREATE TABLE crm_travels ( customer_id INTEGER NOT NULL, item_id INTEGER NOT NULL, supplier_id VARCHAR(32), booking_id VARCHAR(20), no_of_persons INTEGER, date_of_booking DATE, booking_user INTEGER, start_travel DATE, end_travel DATE, status VARCHAR(2), total_price MONEY, booking_orgunit NVARCHAR(64), order_id INTEGER, order_no INTEGER, mediator_id VARCHAR(32), mediator_affiliate VARCHAR(10), destination_code NVARCHAR(20), traveltype_key VARCHAR(2), transport_key VARCHAR(2), persons_key VARCHAR(2), destination_key VARCHAR(2), duration_key VARCHAR(2), PRIMARY KEY(booking_orgunit,customer_id,item_id) constraint pk_crm_travels ) ALTER TABLE crm_travels ADD CONSTRAINT ( FOREIGN KEY(customer_id) REFERENCES customer(customer_id) CONSTRAINT fk_crm_travels_customer ) Function to index is: CREATE FUNCTION get_cust_first_travel_booking_date(p_customerId integer) RETURNS Date with (not variant); DEFINE first_booking Date; select min(date_of_booking) into first_booking from crm_travels where customer_id = p_customerId; IF first_booking is null THEN LET first_booking = MDY(12, 31, 1799); END IF; RETURN first_booking; We want to add a functional index: CREATE INDEX cftBookingDateIndex ON crm_travels ( get_cust_first_travel_booking_date(customer_id)) Error message is: 212: Cannot add index 106: ISAM error: non-exclusive access I have tried this on my personal instance, freshly started, no sessions active, executed in dbaccess. IDS Version 11.50.UC5DE (Developer Edition, but also does not work in Production 11.10FC3, same error) Linux systems, Ubuntu on mine, Production with Debian 64bit Any idea what we do wrong ? Marcus Haarmann ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.