Re: Change Column data type on a BIG table

From: vtnguyen <vtnguyen@home.com> > >The table has about 50 million records. The data type of one of the >columns currently is DECIMAL(13,2). We want to change it to >DECIMAL(9,2). > >What we did was: > Alter table TabA modify colA(9,2)> >And it took forever (the server is running in no-log mode). > >Is there any other better/faster way? What version? Recent versions support in-place alter.... >Another question about SQL in Informix >In Sybase I can do this: > select 'abc..... ' >With Oracle I can do similar as: > select 'abc......' from dual >Can/how I do it on Informix? Huh? Can you have a SELECT statement in Informix? Of course. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Thanks for the reply, 1. The Informix version is Online 7.23. 2. My question about the select statement might not very clear. Basically, I would like to print messages for each SQL statements in the scripts. For examples: PRINT A MESSAGE SAYING "Creating table A...." Create table A (id int);PRINT A MESSAGE SAYING "Creating index AIDX..." Create index aidx on A(id);... ... The question is how do I display the messages to the console. In Sybase I can do like: select "Creating table A...." Create table A (id int)select "Creating index AIDX..." Create index aidx on A(id) Thanks again for all your help Tung Nguyen Obnoxio The Clown wrote: > From: vtnguyen <vtnguyen@home.com> > > > >The table has about 50 million records. The data type of one of the > >columns currently is DECIMAL(13,2). We want to change it to > >DECIMAL(9,2). > > > >What we did was: > > Alter table TabA modify colA(9,2)> > > >And it took forever (the server is running in no-log mode). > > > >Is there any other better/faster way? > > What version? Recent versions support in-place alter.... > > >Another question about SQL in Informix > >In Sybase I can do this: > > select 'abc..... ' > >With Oracle I can do similar as: > > select 'abc......' from dual > >Can/how I do it on Informix? > > Huh? Can you have a SELECT statement in Informix? Of course. > ________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Re 2) the select stuff. well, you could do a select unique 'creating table A now:' , CURRENT YEAR TO SECOND from systables; that would show up on your stdout AS creating table a NOW: 23 19:41:36 vtnguyen wrote: > Thanks for the reply, > > 1. The Informix version is Online 7.23. > 2. My question about the select statement might not very clear. Basically, > I would like to print messages for each SQL statements in the scripts. For > examples: > > PRINT A MESSAGE SAYING "Creating table A...." > Create table A (id int);> PRINT A MESSAGE SAYING "Creating index AIDX..." > Create index aidx on A(id);> ... > ... > The question is how do I display the messages to the console. > > In Sybase I can do like: > > select "Creating table A...." > Create table A (id int)> select "Creating index AIDX..." > Create index aidx on A(id)> > Thanks again for all your help > > Tung Nguyen > > Obnoxio The Clown wrote: > > > From: vtnguyen <vtnguyen@home.com> > > > > > >The table has about 50 million records. The data type of one of the > > >columns currently is DECIMAL(13,2). We want to change it to > > >DECIMAL(9,2). > > > > > >What we did was: > > > Alter table TabA modify colA(9,2)> > > > > >And it took forever (the server is running in no-log mode). > > > > > >Is there any other better/faster way? > > > > What version? Recent versions support in-place alter.... > > > > >Another question about SQL in Informix > > >In Sybase I can do this: > > > select 'abc..... ' > > >With Oracle I can do similar as: > > > select 'abc......' from dual > > >Can/how I do it on Informix? > > > > Huh? Can you have a SELECT statement in Informix? Of course. > > ________________________________________________________________________ > > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Obnoxio The Clown wrote: > From: vtnguyen <vtnguyen@home.com> > >[...]Another question about SQL in Informix > >In Sybase I can do this: > > select 'abc..... ' > >With Oracle I can do similar as: > > select 'abc......' from dual > >Can/how I do it on Informix? > > Huh? Can you have a SELECT statement in Informix? Of course. Yes, but that isn't the question. In Sybase, you can write *just* SELECT 'abc' and you get a result. In Oracle, there's a single row table called dual so you can write SELECT 'abc' FROM dual and get a single row result. There isn't a direct analog of Oracle's dual in Informix, though it would be dead easy to provide one, either with a view or with an actual table. The normal trick is to use: SELECT 'abc' FROM "informix".systables WHERE tabid = 1; Most people don't have to bother with the quoted owner name, so you'll usually see it suggested as: SELECT 'abc' FROM systables WHERE tabid = 1; Some people prefer not to specify a filter condition and use SELECT UNIQUE 'abc' FROM systables instead. I don't know whetherthe optimizer spots that this is largely redundant, but in principle, it will generate a copy of the selected string for each table in the database and then remove the duplicates. Ideally, the optimizer should spot that it would be silly to do all that work, but it is silly, in my view, to give it the opportunity to be obtuse. Finally, if you happen to be using ISQL (and probably, but not necessarily DB-Access) with a script file: isql dbase script dbaccess dbase script then you can use: !echo Hi to leave an audit trail. Of course, both these programs give you a trace of the order of 'table created', 'index created', etc. No real details (such as table name), in other words. Another possibility is to use the SQLCMD program from the IIUG archives (at http://www.iiug.org). It gives you a different set of controls over what is echoed. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>