searched the web for answer and failed to find any. also searched for recovery. backups available turned out useless. I can't unload the 2 tables - not found, cant validate - not found. in desperation, I tried to drop the two tables in that dbspace and then delete the dbspace and recreate the dbspace and the tables. However, I can't drop the tables - not found, can't delete the dbspace - tables exist your suggestions will be much appreciated |
Does the other dbspace file exist and is it located at the correct path? Try "select * from sysdbfile" to see the path associated. Use "ALTER DBSPACE ... RENAME ..." to change the path. |
Is that somewhat related to this older question?
It is possible the tables exist but are not visible to you because you need to specify the owner name as in SELECT * FROM ownername.tablename.
Try running this query:
thx, Brecker but no such luck. I guess on Sunday I just have to go thru the unload and reload suggested in the older question referred by Volker.
Good luck!
To $all: This is a cautionary tale about databases and the difficulties of ensuring the integrity of multiple data files... it is always more difficult and dangerous to have two files rather than one.
If you must have multiple files (say, in a system that manages millions of videos) then so be it.
But, the need for separate dbspace files has vanished over the years: modern operating systems and disk drives can handle huge single files. If dbpaces offered administrative advantages (backup, deployment, sharding, whatever), it might be a different story, but they do not: dbspaces offer only administrative grief.
Dbspaces should not be used in new applications.
regrettably I had to deal with existing app with more than one dbspace.
For point 1:
Not with a single statement. Cf that similar, but opposite question... - When using the "full unload"-approach to reload into a single-space database, you can simply use dbunload with the -kd option, i.e. there won't be a need to modify the created reload.sql file in-between.
For point 2:
The question is how much has the separate dbspace helped to improve performance. If it was one a different physical medium, it should have helped ("different heads in use") (*). If it was just a different file on the same physical medium, I guess you won't notice any disadvantage. Just my wild guess, apparently:)
(*) - Here's another discussion on the pros and cons of dbspaces.
> move table between dbspace
Check out this blog post from eight years ago: UNLOAD and LOAD Via Named Pipes.
It is used by Foxhound every time an older Foxhound database is migrated to a newer version. In Foxhound's case, the two different dbspaces are the SYSTEM dbspace in two different databases which is a harder problem than yours, but still possible using the FORWARD TO statement.
So, as proof that the named pipes technique works in production, for gigantic tables, here are some snippets from Foxhound: