Int8 -> serial8, slow alter
User asked why converting INT8 to SERIAL8 columns couldn't use in-place alter algorithm, unlike DEC to SERIAL8 conversions. Art Kagel reported this was fixed in Informix 11.70.FC7W1 release. The fix also addressed constraints being dropped during serial-to-serial8 conversions. User requested confirmation that INT8-to-SERIAL8 conversion would now support in-place alter, and Kagel indicated it should be supported.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Does anyone know why converting an INT8 column to a SERIAL8 column cannot use the in-place alter algorithm? I thought the only difference between these fields is that with a SERIAL8 Informix keeps a record of the current ID number in sysmaster:sysptnhdr. Strangely a conversion from a DEC(p1,s1) column to a SERIAL8 or BIGSERIAL column can use the in-place alter algorithm. Ben.
Good question. I will try to bring it up here at the IIUG Conference and see if there's an answer that makes sense. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 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 Mon, Apr 22, 2013 at 8:54 AM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > Hi, > > Does anyone know why converting an INT8 column to a SERIAL8 column cannot > use > the in-place alter algorithm? > > I thought the only difference between these fields is that with a SERIAL8 > Informix keeps a record of the current ID number in sysmaster:sysptnhdr. > > Strangely a conversion from a DEC(p1,s1) column to a SERIAL8 or BIGSERIAL > column can use the in-place alter algorithm. > > Ben. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b342ba4f0e6de04daf351bf
My sources tell me that Benjamin already received this info, but for the community: Apparently this is already fixed in the 11.70.FC7W1 release that was released on Friday. It should be downloadable now (if not then in a day or three). Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 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 Mon, Apr 22, 2013 at 8:54 AM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > Hi, > > Does anyone know why converting an INT8 column to a SERIAL8 column cannot > use > the in-place alter algorithm? > > I thought the only difference between these fields is that with a SERIAL8 > Informix keeps a record of the current ID number in sysmaster:sysptnhdr. > > Strangely a conversion from a DEC(p1,s1) column to a SERIAL8 or BIGSERIAL > column can use the in-place alter algorithm. > > Ben. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b342ba47356b904daf39469
Hi Art, Thank you for the answer. I was not involved in this change request directly and only know so much. I was aware that something was been done to make serial to serial8 column conversions use the in-place alter algorithm, but not this particular combination. It does appear that only certain combinations are supported and there isn't an obvious logic to it. The other issue with a serial to serial8 conversion is that constraints relating to the altered column are dropped in the process, which I believe the fix also addresses. Serial and serial8 are fundamentally different data types where int8 and serial8 are the same and today is the first time I have attempted this conversion, hence the separate question. Could you confirm that this combination will also be a supported in-place alter? Ben.
Hello. You may be hitting some constraint issue, according to the manual (sorry, it´s 11.50 version, but applies in 11.70+) http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids _sqr_141.htm Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: benjamin.thompson@bskyb.com > Subject: Int8 -> serial8, slow alter [30119] > Date: Mon, 22 Apr 2013 08:54:36 -0400 > > Hi, > > Does anyone know why converting an INT8 column to a SERIAL8 column cannot use > the in-place alter algorithm? > > I thought the only difference between these fields is that with a SERIAL8 > Informix keeps a record of the current ID number in sysmaster:sysptnhdr. > > Strangely a conversion from a DEC(p1,s1) column to a SERIAL8 or BIGSERIAL > column can use the in-place alter algorithm. > > Ben. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
As far as I know yes. But I will try to double check. Art On Apr 22, 2013 9:52 AM, "BENJAMIN THOMPSON" <benjamin.thompson@bskyb.com> wrote: > Hi Art, > > Thank you for the answer. I was not involved in this change request > directly > and only know so much. > > I was aware that something was been done to make serial to serial8 column > conversions use the in-place alter algorithm, but not this particular > combination. It does appear that only certain combinations are supported > and > there isn't an obvious logic to it. > > The other issue with a serial to serial8 conversion is that constraints > relating to the altered column are dropped in the process, which I believe > the > fix also addresses. > > Serial and serial8 are fundamentally different data types where int8 and > serial8 are the same and today is the first time I have attempted this > conversion, hence the separate question. Could you confirm that this > combination will also be a supported in-place alter? > > Ben. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0115fe5caf5aab04daf6b79f