novicesybase
asked on
sybase: Finding the missing indexes between databases of 2 different ASE servers.
Hello,
my task is to compare and find all the missing indexes between 2 servers...
For example:-
lets say server 1 has database "ABC" and server 2 also has database ABC (server 2 database ABC was copied from server 1 ABC, but when they copied somehow indexes was not moved and now the indexes are missing in server 2 "ABC" database. ) so my task is to compare these 2 databases of server 1 and server 2 and make a difference sheet of missing indexes from server 1 to server 2....The goal is all the indexes which exists in server 1's "ABC" should be identified and should be recreated in Server 2 "ABC" database.
I have a query ready to get all the indexes with tablename, indexname and included columns in the indexes.... But now my question is, how should i compare the results of both the servers....my friend suggested me to keep both the results in an excel sheet and then compare... but i am not understanding the right way to do it.
is there any way to do this comparison? else i may endup doing it manually which is time consuming.....
Can experts in this forum, please shed some light ? it would be really very helpful....
my task is to compare and find all the missing indexes between 2 servers...
For example:-
lets say server 1 has database "ABC" and server 2 also has database ABC (server 2 database ABC was copied from server 1 ABC, but when they copied somehow indexes was not moved and now the indexes are missing in server 2 "ABC" database. ) so my task is to compare these 2 databases of server 1 and server 2 and make a difference sheet of missing indexes from server 1 to server 2....The goal is all the indexes which exists in server 1's "ABC" should be identified and should be recreated in Server 2 "ABC" database.
I have a query ready to get all the indexes with tablename, indexname and included columns in the indexes.... But now my question is, how should i compare the results of both the servers....my friend suggested me to keep both the results in an excel sheet and then compare... but i am not understanding the right way to do it.
is there any way to do this comparison? else i may endup doing it manually which is time consuming.....
Can experts in this forum, please shed some light ? it would be really very helpful....
Also, one can generate DDL of indexes from both databases using in-built Sybase utilities OR third party tools like DBArtisan, toad etc. and compare both text files DDL.
Another way is to use database comparison tools like DBArtisan, DBComparator, WinSQL etc. where in you should select only indexes check box and the tool will directly give the list of all differences in indexes.
One more way is to simply use queries in above post and get sorted names of indexes in text files one for each database indexes and then simply do a compare in editors like TextPad, EditPlus.
Another way is to use database comparison tools like DBArtisan, DBComparator, WinSQL etc. where in you should select only indexes check box and the tool will directly give the list of all differences in indexes.
One more way is to simply use queries in above post and get sorted names of indexes in text files one for each database indexes and then simply do a compare in editors like TextPad, EditPlus.
ASKER
can you please let me know, the steps which i need to follow when using the database comparison tools like DBArtisan, DBComparator etc?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks Murali
How to set up a linked server to a Sybase database server and troubleshoot problems that may occur
http://support.microsoft.com/kb/280102
Idea is to query database System Table sysindexes.
System Table sysindexes
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.tables/html/tables/tables34.htm
Using all above, one can use Java JDBC OR SQLJ OR a stored procedure to form Result Set OR iterator OR cursor for both databases in either server & then in the loop compare indexes and print the results OR store in a suitable table which can be queried later.
Sample query on sysindexes from Server 1 ABC database will be:
select name from sysindexes order by name;
select name from server2.ABC.dbo.sysindexes
It could have been much simpler if there was a MINUS set operator in Sybase. If no linked server is used, then one cannot achieve like this from a stored procedure since only one connection context can exist at a time but in JDBC OR SQLJ, it is possible due to multiple connection contexts.