how to determine if sql anywhere dump load missed anything

[Former Member]

Hi. I just moved a customer from sqla 10 to sqla12. The sqla 10 database was about 5 gig. The sqla 12 database is about 1 gig. How can I tell if the sqla 12 database is complete? Thanks, Doug P.S. This question is somewhat urgent as the app is scheduled to be back on-line soon.

[former_SQLA_member1694868]

In addition to any answers below about making sure the databases are the same, also make sure that all of the views are enabled on the new database. We have had a few situations after upgrades with views coming into the new database disabled because the order they are loaded in loads a few that are missing dependencies. I open Sybase Central, select all the disabled ones, right-click and enable, get an error about the dependency, enable that one, and so on until everything is back up.

[former_SQLA_member1694886]

What I do on those (rare) occasions is run the script below on the old and the new database to get a file with the total row count per table. Comparing both files with a diff utility will turn up tables that may not have been reloaded.

begin

declare @sqlresults long varchar ;
declare @count integer ;
set @sqlresults = '' ;

for datatables as curs0 dynamic scroll cursor for

select table_name as this_table_name, cast ( table_id as varchar(200) ) as this_table_id from systable where table_type = 'base' and creator = 1 and remote_location is null
order by 1 asc

do

set @sqlresults = @sqlresults || '\
\
--tableid ' || this_table_id || '\
' || 'select count(*) into @count from ' || this_table_name ;
execute immediate 'select count(*) into @count from ' || this_table_name  ;
set @sqlresults = @sqlresults || '\
Returned ' || cast (@count as varchar(100)) || ' rows' ;

end for ;

CALL xp_write_file( 'd:/resultset.txt', @sqlresults );

end

[VolkerBarth]

Well, I typically do a complete unload of all user tables and diff that (after comparing the row counts). - As I'm dealing with databases on site in such cases, this might not be possible for huge databases located at customer sites...

[Former Member]

Thank you all. I slightly modified the above sql so that table ID's would not display--as each table in the new/rebuilt database received a different table ID and that triggered a "diff" between the documents.

[Former Member]

Here's the slightly modified sql...

begin
declare @sqlresults long varchar ;
declare @count integer ;
set @sqlresults = '' ;

for datatables as curs0 dynamic scroll cursor for

   select table_name as this_table_name, cast ( table_id as varchar(200) ) as this_table_id
   from systable where table_type = 'base' and creator = 1 and remote_location is null
   order by 1 asc

do

   execute immediate 'select count(*) into @count from ' || this_table_name  ;
   set @sqlresults = @sqlresults || '\
' || 'Table ' || this_table_name || ': ' || cast (@count as varchar(100)) || ' rows' ;

end for ;

call xp_write_file( 'e:/resq/resultset5_new.txt', @sqlresults );

end

[former_SQLA_member1694868]

Just curious, did the files end up matching or was something missing?

[Former Member]

By the time I ran the sql, the customer's users had about an hour of time against the new sqla12 database. So, I did not expect an exact match, table-for-table. The match was, though, close enough that I was reassured that the dump/load had not missed data.

[johnsmirnios]

For the system dbspace, you can get the number of pages actually in use by selecting database_property('FileSize')-database_property('CheckpointLogSize')-database_property('FreePages').

For the pages that are in use, you can also use dbinfo -u to determine how densely the data is stored on those pages.