Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

asked 21 Sep '18, 17:42

gg99's gravatar image

gg99
227293038
accept rate: 10%

edited 22 Sep '18, 00:58

Is that somewhat related to this older question?

(21 Sep '18, 18:40) Volker Barth
Comment Text Removed

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:

SELECT USER_NAME ( SYSTABLE.creator ) AS owner_name,
       SYSTABLE.table_name
  FROM SYSTABLE
 WHERE table_name = 'whatever';

owner_name,table_name
'dba','whatever'
(22 Sep '18, 11:25) Breck Carter

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.

(22 Sep '18, 17:29) gg99
Replies hidden
3

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.

(23 Sep '18, 07:21) Breck Carter
Comment Text Removed

regrettably I had to deal with existing app with more than one dbspace.

  1. is it possible to move table between dbspace?
  2. can I assume 100 to 400KB pdf rows will not impact database performance?
(23 Sep '18, 22:51) gg99

For point 1:

is it possible to move table between dbspace?

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:

can I assume 100 to 400KB pdf rows will not impact database performance?

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.

(24 Sep '18, 03:18) Volker Barth

> 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:

DECLARE @sql   LONG VARCHAR;
...
SET @sql = 'UNLOAD TABLE DBA.rroad_group_2_property_pivot 
   TO ''\\.\pipe\data''';
...
SET @sql = REPLACE ( @sql, '''', '''''' );
SET @sql = REPLACE ( @sql, '\', '\\' );
SET @sql = REPLACE ( @sql, '\', '\\' );
SET @sql = 'FORWARD TO old_foxhound1 ''' + @sql + '''';
...
TRIGGER EVENT rroad_execute_immediate ( @sql = @sql );
...
LOAD TABLE rroad_group_2_property_pivot ( 
   sampling_id,
   sample_set_number,
   connection_number,
   ...
   ) FROM '\\.\pipe\data' 
   DEFAULTS ON;
...
CREATE EVENT rroad_execute_immediate
HANDLER BEGIN
DECLARE @sql    LONG VARCHAR;
SET @sql = COALESCE ( EVENT_PARAMETER ( '@sql' ), '' );
IF @sql <> '' THEN
   EXECUTE IMMEDIATE @sql;
END IF;
END;

(24 Sep '18, 07:24) Breck Carter
showing 3 of 7 show all flat view

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.

permanent link

answered 27 Sep '18, 11:18

John%20Smirnios's gravatar image

John Smirnios
12.0k396166
accept rate: 37%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×23

question asked: 21 Sep '18, 17:42

question was seen: 1,795 times

last updated: 27 Sep '18, 11:18