informix-community

Open full view…

Database moving error

speres
Thu, 02 Apr 2020 21:45:41 GMT

Hi, I am trying to move some databases to another dbspace on the same server due to extents limit. I am using 12.10.FC14 version and following some instructions given in the past for using ALTER FRAGMENT <table> INIT IN another dbspace. I have done one script to execute the operation over all tables in database. But I start receiving some errors on some tables giving long transaction. I am doing something wrong? There is another way to do it avoiding export/import? I have also tryed to use CDR commands, but I am sure missing some steps on configuration... Thanks for any help, SP

andreasl
Fri, 03 Apr 2020 10:23:36 GMT

ALTER TABLE ... INIT would copy from old to new partitions, row by row, just like an INSERT INTO ... SELECT FROM ... Such ALTER would run as one large transaction (unless multiple such statement put together into an even larger explicite transaction.) You could, instead, define all your new tables, in new database, as raw tables, then use INSERT INTO ... SELECT FROM ..., finally ALTER TABLE ... TYPE (STANDARD), create indices and, when all data got copied, define constraints. A more elegant way would be: for tab in <table name list>; do echo "create raw table $tab as select * from old_db:$tab;" done | dbaccess new_db If new tables should follow specific storage clauses that would be possible too and had to be sneaked into the above. If 'no downtime' should be important (which it doesn't sound like), ER local loopback replication would be an option, but that would be a totally different ball game; you had to make sure all tables can be replicated, had to d efine ERKEYS where necessary, then define + configure two ER servers on same instance and then all those replicates (could use a template), before you'd start with syncing the data through the replicates.

andreasl
Fri, 03 Apr 2020 11:08:00 GMT

Oh, sorry, just realizing I confused new dbspace with new database. Apart from that, most of the above still would apply, you only had to deal with table name duplication - either rename old tables first, or a similar thing with new ones later.

speres
Fri, 03 Apr 2020 14:54:12 GMT

thanks for information, My problem is that despite having free space it reached the limit of extents. So I intend to move the database to another dbspace without doing export / import if possible. I will test the suggestion, as this procedure can help. Best regards, SP

andreasl
Fri, 03 Apr 2020 16:36:32 GMT

But the extent limit usually is being hit by individual tables, so only moving such table, rather than whole database, typically should do? If the current dbspace isn't too heavily fragmented, in terms of extents, and there are large enough contiguous areas of free space (check oncheck -pe), you could do that table copying even within the same old dbspace.

speres
Fri, 03 Apr 2020 18:43:51 GMT

Thanks for your help again, Well, that is one problem that I am facing with some frequency, there is any form to avoid fragmentation on import? Best regards, SP

speres
Sun, 05 Apr 2020 16:40:26 GMT

I have done dbschema -ss to <mydatabase> and after unload all tables and run well. When I try to load I am getting 691 and 111 errors due to constraints. Any help please, SP

andreasl
Sun, 05 Apr 2020 21:25:09 GMT

I guess you should first load all tables/data, only then created indices and finally the constraints - just like dbimport does.

speres
Sun, 05 Apr 2020 22:00:18 GMT

Thanks for your reply Andreas, Yes it is the correct way, but I am using one function to do it and I am doing one dbschema first, there is any form to split dbschema to give only tables and other to have constraints without using 'hands'? I use AWK to do it, but I don't know if this works on all systems and versions?! awk '/^create unique/{flag=1} flag {print>"createindex.sql";next} {print>"createtables.sql"} ' database.sql Best regards, SP