in place alter table and varchars

Hello, we are using IDS 9.30.FC7 on HP-UX and want to alter a table column from varchar(9) to varchar(20) with the following SQL: alter table b50 modifiy (b50bestellnr varchar(20) ) ; The database seems to use the slow alter algorithm. Is that the correct behaviour or a bug in that version? The performance guide doesn't mention varchar-columns for in-place-alter-operations, but I thought there should be no problem to do an in-place-alter when increasing varchar lengths. The table is quite large. Therefore the slow alter produces a long transaction error. Any alternatives to unloading and reloading the table? Regards, Andreas Kutsche

--0__=09BBE508DFE391658f9e8a93df938690918c09BBE508DFE39165 Content-type: multipart/alternative; Boundary="1__=09BBE508DFE391658f9e8a93df938690918c09BBE508DFE39165" --1__=09BBE508DFE391658f9e8a93df938690918c09BBE508DFE39165 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable Depends on what other columns are in the table. If the table contains UDRs, then any alter will be a slow alter. = "Andreas.KUT...." = <andreas.kutsche@ = spar.at> = To Sent by: ids@iiug.org = forum.subscriber@ = cc iiug.org = Subj= ect in place alter table and varchar= s 02/01/2005 11:56 [4131] = AM = = = = = = Hello, we are using IDS 9.30.FC7 on HP-UX and want to alter a table column from varchar(9) to varchar(20) with the following SQL: alter table b50 modifiy (b50bestellnr varchar(20) ) ; The database seems to use the slow alter algorithm. Is that the correct behaviour or a bug in that version? The performance guide doesn't mention varchar-columns for in-place-alter-operations, but I thought there should be no problem to do an in-place-alter when increasing varchar lengths. The table is quite large. Therefore the slow alter produces a long transaction error. Any alternatives to unloading and reloading the table? Regards, Andreas Kutsche = --1__=09BBE508DFE391658f9e8a93df938690918c09BBE508DFE39165 Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> <p>Depends on what other columns are in the table. If the table contai= ns UDRs, then any alter will be a slow alter.<br> <img src=3D"cid:10__=3D09BBE508DFE391658f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for &quot;Andreas.KUT..= ..&quot; &lt;andreas.kutsche@spar.at&gt;">&quot;Andreas.KUT....&quot; &= lt;andreas.kutsche@spar.at&gt;<br> <br> <br> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D09BBE50= 8DFE391658f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul><b><font size=3D"2">&quot;Andreas.KUT....&quot; &lt;andreas.kutsche= @spar.at&gt;</font></b><font size=3D"2"> </font><br> <font size=3D"2">Sent by: forum.subscriber@iiug.org</font> <p><font size=3D"2">02/01/2005 11:56 AM</font></ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBE508DFE391658f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">To</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBE508DFE391658f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">ids@iiug.org</font></td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBE508DFE391658f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">cc</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBE508DFE391658f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> </td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D09BBE508DFE391658f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">Subject</font></div></td><td widt= h=3D"100%"><img src=3D"cid:30__=3D09BBE508DFE391658f9e8a93df938@us.ibm.= com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">in place alter table and varchars [4131]</font></td></= tr> </table> <table border=3D"0" cellspacing=3D"0" cellpadding=3D"0"> <tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D09BBE508DFE3= 91658f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt= =3D""></td><td width=3D"336"><img src=3D"cid:30__=3D09BBE508DFE391658f9= e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><= /td></tr> </table> </td></tr> </table> <br> <tt>Hello,<br> <br> we are using IDS 9.30.FC7 on HP-UX and want to <br> alter a table column from &nbsp;varchar(9) to varchar(20) <br> with the following SQL: <br> <br> alter table b50 modifiy (b50bestellnr varchar(20) ) ;<br><br> The database seems to use the slow alter algorithm. <br> Is that the correct behaviour or a bug in that version?<br> The performance guide doesn't mention &nbsp;varchar-columns<br> for in-place-alter-operations, but I thought there should<br> be no problem to do an in-place-alter when increasing<br> varchar lengths.<br> <br> The table is quite large. Therefore the slow alter <br> produces a long transaction error.<br> Any alternatives to unloading and reloading the table?<br> <br> Regards,<br> Andreas Kutsche<br> <br> <br> </tt><br> </body></html>= --1__=09BBE508DFE391658f9e8a93df938690918c09BBE508DFE39165-- --0__=09BBE508DFE391658f9e8a93df938690918c09BBE508DFE39165 Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=09BBE508DFE391658f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=09BBE508DFE391658f9e8a93df938690918c09BBE508DFE39165 Content-type: image/gif; name="pic28976.gif" Content-Disposition: inline; filename="pic28976.gif" Content-ID: <20__=09BBE508DFE391658f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhWABDALP/AAAAAK04Qf79/o+Gm7WuwlNObwoJFCsoSMDAwGFsmIuezf///wAAAAAAAAAA AAAAACH5BAEAAAgALAAAAABYAEMAQAT/EMlJq704682770RiFMRinqggEUNSHIchG0BCfHhOjAuh EDeUqTASLCbBhQrhG7xis2j0lssNDopE4jfIJhDaggI8YB1sZeZgLVA9YVCpnGagVjV171aRVrYR RghXcAGFhoUETwYxcXNyADJ3GlcSKGAwLwllVC1vjIUHBWsFilKQdI8GA5IcpApeJQt8L09lmgkH LZikoU5wjqcyAMMFrJIDPAKvCFletKSev1HBw8KrxtjZ2tvc3d5VyKtCKW3jfz4uMKmq3xu4N0nK BVoJQmx2LGVOmrqNjjJf2hHAQo/eDwJGTKh

Andreas Kutsche <andreas.kutsche@spar.at> wrote on 02/01/2005 09:56:19 AM: > we are using IDS 9.30.FC7 on HP-UX and want to > alter a table column from varchar(9) to varchar(20) > with the following SQL: > > alter table b50 modifiy (b50bestellnr varchar(20) ) ;> > The database seems to use the slow alter algorithm. > Is that the correct behaviour or a bug in that version? > The performance guide doesn't mention varchar-columns > for in-place-alter-operations, but I thought there should > be no problem to do an in-place-alter when increasing > varchar lengths. > > The table is quite large. Therefore the slow alter > produces a long transaction error. > Any alternatives to unloading and reloading the table? AFAIK, in currently available versions of IDS, any alter on a table with VARCHAR data in any of the columns is a slow alter. Alternatives? Increase the size of your logical logs? -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Information Management Division 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!"