|
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. |
|
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 || '\X0A\X0A--tableid ' || this_table_id || '\X0A' || 'select count(*) into @count from ' || this_table_name ; execute immediate 'select count(*) into @count from ' || this_table_name ; set @sqlresults = @sqlresults || '\X0AReturned ' || cast (@count as varchar(100)) || ' rows' ; end for ; CALL xp_write_file( 'd:/resultset.txt', @sqlresults ); end 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...
(23 Aug '11, 13:16)
Volker Barth
1
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.
(24 Aug '11, 07:30)
Doug
|
|
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. |
|
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 || '\X0A' || 'Table ' || this_table_name || ': ' || cast (@count as varchar(100)) || ' rows' ; end for ; call xp_write_file( 'e:/resq/resultset5_new.txt', @sqlresults ); end Just curious, did the files end up matching or was something missing?
(24 Aug '11, 10:04)
Siger Matt
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.
(24 Aug '11, 12:26)
dejstone
|
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.