reorganize database

Could you please tell me how to reorganize database by using alter database command. I mean alter database to another dbspace.

alter database is not a valid command. Also it is not possible to change the location of a database (and the default location of its tables) without unloading, dropping, recreating and reloading. To do this you need to undertake the following steps:- dbexport -ss 'database'edit the sql file to amend any table locations (in dbaccess) drop 'database' dbimport -d 'new_dbspace' 'database' then chnage the logging mode using ontape or onbar Keith -> -----Original Message----- -> From: CHANATIP LA.... [mailto:chanatip.laongsiriwong@gmail.com] -> Sent: Thursday, April 20, 2006 9:46 AM -> To: ids@iiug.org -> Subject: reorganize database [6573] -> -> -> -> Could you please tell me how to reorganize database by using -> alter database -> command. I mean alter database to another dbspace. -> -> -> ************************************************************* -> ****************** -> Forum Note: Use "Reply" to post a response in the -> discussion forum. -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **

Correct, the only way i know of to relocate a database into another dbspace is to unload it and recreate it. If the goal is to reorganize spaces, it could be an option to use the "alter fragment " command, which allows you to put parts of a table (also all parts of a table) into another dbspace. This can be a useful option in case you want to spread a table over more than one dbspace. See informix help for details (http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp). Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Simmons, Keith Sent: Thursday, April 20, 2006 12:33 PM To: ids@iiug.org Subject: RE: reorganize database [6574] alter database is not a valid command. Also it is not possible to change the location of a database (and the default location of its tables) without unloading, dropping, recreating and reloading. To do this you need to undertake the following steps:- dbexport -ss 'database'edit the sql file to amend any table locations (in dbaccess) drop 'database' dbimport -d 'new_dbspace' 'database' then chnage the logging mode using ontape or onbar Keith -> -----Original Message----- -> From: CHANATIP LA.... [mailto:chanatip.laongsiriwong@gmail.com] -> Sent: Thursday, April 20, 2006 9:46 AM -> To: ids@iiug.org -> Subject: reorganize database [6573] -> -> -> -> Could you please tell me how to reorganize database by using alter -> database command. I mean alter database to another dbspace. -> -> -> ************************************************************* -> ****************** -> Forum Note: Use "Reply" to post a response in the discussion forum. -> **************************************************************************** ****** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. **************************************************************************** ****** **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Thank you for your suggestion. But dbexport and dbimport is not successful for me. Because there are some tables that have too many columns( ~ 10000 columns). When export it create table schema that too long statement. It cannot create table when import back.

Hi, You could try with onunload/onload, maybe this works better for your special tables. In onload you can set the target dbspace with the -d option. Otherwise, you could try to not import these special tables by modifying the sql script. Afterwards, create the tables manually (create as much columns as possible and add the remainder with alter table) and then load the data with "load from ". It might be a good idea to create the database initially without logging in case you have many rows in these tables, because a transaction might get too long. Hope this helps, Marcus Haarmann Geschäftsführer Midoco GmbH Otto-Hahn-Str. 12 40721 Hilden Tel. +49 (2103) 28 74 0 Fax. +49 (2103) 28 74 28 www.midoco.de Member of Pisano Holding GmbH Better Travel Technology www.pisano-holding.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CHANATIP LA.... Sent: Friday, April 21, 2006 3:09 AM To: ids@iiug.org Subject: Re: RE: reorganize database [6576] Thank you for your suggestion. But dbexport and dbimport is not successful for me. Because there are some tables that have too many columns( ~ 10000 columns). When export it create table schema that too long statement. It cannot create table when import back. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Try my dbexport/dbimport replacement utility, myexport, which you can download from the IIUG Software Repository. You will also need my dbschema replacement utility, myschema, which is in the package utils2_ak and Jonathan Leffler's sqlcmd utility both also in the Repository. If that doesn't do it, you'll have to edit the schema file that either dbexport or myexport creates to compress the CREATE TABLE statements for these huge tables into statements smaller than 64K if possible by removing white space or to break the CREATE into a MUCH smaller CREATE TABLE followed by one or more ALTER TABLE ADD statements to add in the remaining columns that do not fit in a64K CREATE TABLE statement. As long as you do not muck with the comment immedately prior to the CREATE statement you will be able to use the modified schema with myimport, although I'm not certain that dbimport will handle it correctly - it will likely try to load the data immediately after the CREATE instead of after the ALTER(s). Art S. Kagel ----- Original Message ----- From: Chanatip La.... <ids@iiug.org> At: 4/20 21:11 Thank you for your suggestion. But dbexport and dbimport is not successful for me. Because there are some tables that have too many columns( ~ 10000 columns). When export it create table schema that too long statement. It cannot create table when import back. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

You what ?? 10000 columns per table... ? Weird... I mean never think on it before.... ;) J. CHANATIP LA.... escribió: > Thank you for your suggestion. But dbexport and dbimport is not successful for > me. Because there are some tables that have too many columns( ~ 10000 > columns). When export it create table schema that too long statement. It > cannot create table when import back. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

On 4/30/06, Jean Sagi <jeansagi@myrealbox.com> wrote: > > You what ?? > > 10000 columns per table... ? > > Weird... I mean never think on it before.... ;) > > J. > > CHANATIP LA.... escribió: > > Thank you for your suggestion. But dbexport and dbimport is not successful > for > > me. Because there are some tables that have too many columns( ~ 10000 > > columns). When export it create table schema that too long statement. It > > cannot create table when import back. Nasty database design. Your only plausible hope is to split the CREATE TABLE into a CREATE TABLE and one or more ALTER TABLE statements, each of which adds some extra columns. I have created a table with 32767 char(1) columns - I still have the scripts stashed away somewhere. Each SQL statement can be up to 65535 characters long. With luck, the loading code will be OK as long as you don't attempt to list the columns in the INSERT statement - that would blow the limits again. Heaven help you if you get something wrong - but then again, you've already got problems with a table with that many columns anyway. I hate to think what any query looks like. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/