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.

asked 23 Aug '11, 11:59

Doug's gravatar image

Doug
30234
accept rate: 0%

1

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.

(23 Aug '11, 12:31) Siger Matt

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
permanent link

answered 23 Aug '11, 12:14

Vincent%20Buck's gravatar image

Vincent Buck
70681520
accept rate: 15%

edited 23 Aug '11, 12:16

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.

permanent link

answered 23 Aug '11, 14:22

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

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
permanent link

answered 24 Aug '11, 07:36

Doug's gravatar image

Doug
30234
accept rate: 0%

edited 24 Aug '11, 09:35

Volker%20Barth's gravatar image

Volker Barth
30.6k306456663

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

×41
×14

question asked: 23 Aug '11, 11:59

question was seen: 1,428 times

last updated: 24 Aug '11, 12:26