The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.7k377106
accept rate: 40%

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
29.3k287438644

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:

×37
×14

question asked: 23 Aug '11, 11:59

question was seen: 1,344 times

last updated: 24 Aug '11, 12:26