Re: AW: Help with Sybase indexes
Good suggestion Joe... fortunately, it appears that everything is synced. Thanks. Larry On Feb 22, 2010, at 3:18 PM, Joe D'Souza wrote: > If you have a large number of results after Conny's query, you may want to > auto generate a script to create all the missing indexes using that same > query to generate the result in the form of SQL statements to create an > index.. > > Joe > > -Original Message- > From: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org]on Behalf Of L G Robinson > Sent: Monday, February 22, 2010 2:30 PM > To: arslist@ARSLIST.ORG > Subject: Re: AW: Help with Sybase indexes > > > Thanks Conny... this is very helpful. > Larry > > On Feb 22, 2010, at 9:00 AM, Conny Martin wrote: > >> Larry, >> >> you should compare the metatables with the indexes oracle knows. >> >> If one of these 2 statements gives you any rows, you have messed up your > indexes. >> >> SELECT name,'pk_idx for T-Table' FROM arschema WHERE schematype = 1 AND > NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IT'||schemaid) >> UNION ALL >> SELECT name,'pk_idx for H-Table' FROM arschema WHERE schematype = 1 AND > NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IH'||schemaid) >> UNION ALL >> SELECT name,'pk_idx for B-Table' FROM arschema WHERE schematype = 1 AND > NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IB'||schemaid) >> >> >> SELECT > name,arschema.schemaid,schema_index.numfields,uniqueflag,indexname,f1,f2,f3 > FROM arschema,schema_index WHERE indexname NOT IN (SELECT INDEX_name FROM > user_indexes) AND schema_index.schemaid = arschema.schemaid >> >> HTH >> >> Kind Regards Conny Larry Robinson n...@ncsu.edu Office of Information Technology NC State University 919-515-5432 Voice Raleigh, NC 27695-7109 919-513-0877 FAX ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
Re: AW: Help with Sybase indexes
If you have a large number of results after Conny's query, you may want to auto generate a script to create all the missing indexes using that same query to generate the result in the form of SQL statements to create an index.. Joe -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org]on Behalf Of L G Robinson Sent: Monday, February 22, 2010 2:30 PM To: arslist@ARSLIST.ORG Subject: Re: AW: Help with Sybase indexes Thanks Conny... this is very helpful. Larry On Feb 22, 2010, at 9:00 AM, Conny Martin wrote: > Larry, > > you should compare the metatables with the indexes oracle knows. > > If one of these 2 statements gives you any rows, you have messed up your indexes. > > SELECT name,'pk_idx for T-Table' FROM arschema WHERE schematype = 1 AND NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IT'||schemaid) > UNION ALL > SELECT name,'pk_idx for H-Table' FROM arschema WHERE schematype = 1 AND NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IH'||schemaid) > UNION ALL > SELECT name,'pk_idx for B-Table' FROM arschema WHERE schematype = 1 AND NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IB'||schemaid) > > > SELECT name,arschema.schemaid,schema_index.numfields,uniqueflag,indexname,f1,f2,f3 FROM arschema,schema_index WHERE indexname NOT IN (SELECT INDEX_name FROM user_indexes) AND schema_index.schemaid = arschema.schemaid > > HTH > > Kind Regards Conny > > -Ursprüngliche Nachricht- > Von: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] Im Auftrag von L G Robinson > Gesendet: Montag, 22. Februar 2010 14:33 > An: arslist@ARSLIST.ORG > Betreff: Re: Help with Sybase indexes > > Hi Joe, > > Thanks for the reply. I did compare the information in the AR System's meta-tables between the two systems (schema_index) and it matches up as expected. However, I believe it is possible for there to be a mismatch between what ARS thinks and what actually exists in the DB. The situation I am trying to detect is the one where ARS thinks there is an index, but for whatever reason, the index no longer exists in the DB. > > I have been using Misi's wonderful RRRchive utility and on occasion, I have to interrupt a copy operation before it finishes. In this case, I believe that the target table will be left without its index since RRRchive does not get the chance to recreate the index on the target table when the program is killed. I recreate the index(es) when this happens... I just want to make sure I have not missed any. > > Thanks again. > Larry > > On Feb 21, 2010, at 8:53 AM, Joe D'Souza wrote: > >> Larry, >> >> Why not look this same information up from the AR System's meta information? >> That way it would be easier to compare the two systems indexes if they >> should be identical.. >> >> Joe >> >> -Original Message- >> From: Action Request System discussion list(ARSList) >> [mailto:arsl...@arslist.org]on Behalf Of L G Robinson >> Sent: Friday, February 19, 2010 5:47 PM >> To: arslist@ARSLIST.ORG >> Subject: Help with Sybase indexes >> >> >> Hi Folks, >> >> I need some Sybase help regarding indexes. >> >> I am migrating from Sybase to Oracle and I want to compare the two >> systems to make sure that the Oracle system has all of the same >> indexes on the T-tables as currently exist in the Sybase system. I >> don't doubt that they were there originally as a result of the >> workflow import that I did to create the Oracle system. I just want to >> make sure that I have not inadvertently messed any of the indexes up >> while I have been working in the Oracle system and copying data over (Thanks Misi!!!). >> >> On the Oracle side, I can do the following: >> >> SELECT s.name as "Form Name", b.column_name, a.table_name, >> a.index_name, a.uniqueness,a.index_type FROM USER_INDEXES a, >> USER_IND_COLUMNS b, arschema s WHERE a.index_name = b.index_name AND >> cast(s.schemaId as varchar2(40)) = >> substr(a.table_name,2) AND substr(a.table_name,1,1) = 'T' >> ORDER by 1,2; >> >> This gives me exactly what I need. However, I have been spelunking >> through the Sybase system tables all day and I can not seem to locate >> all of the information I need. I can see "almost" everything in the "sysindexes" table. >> However, I do not see the name or id number of the column that the >> index is built upon. Maybe I'm just missing something obvious but I >> sure don't see it. >
Re: AW: Help with Sybase indexes
Thanks Conny... this is very helpful. Larry On Feb 22, 2010, at 9:00 AM, Conny Martin wrote: > Larry, > > you should compare the metatables with the indexes oracle knows. > > If one of these 2 statements gives you any rows, you have messed up your > indexes. > > SELECT name,'pk_idx for T-Table' FROM arschema WHERE schematype = 1 AND NOT > EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IT'||schemaid) > UNION ALL > SELECT name,'pk_idx for H-Table' FROM arschema WHERE schematype = 1 AND NOT > EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IH'||schemaid) > UNION ALL > SELECT name,'pk_idx for B-Table' FROM arschema WHERE schematype = 1 AND NOT > EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IB'||schemaid) > > > SELECT > name,arschema.schemaid,schema_index.numfields,uniqueflag,indexname,f1,f2,f3 > FROM arschema,schema_index WHERE indexname NOT IN (SELECT INDEX_name FROM > user_indexes) AND schema_index.schemaid = arschema.schemaid > > HTH > > Kind Regards Conny > > -Ursprüngliche Nachricht- > Von: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org] Im Auftrag von L G Robinson > Gesendet: Montag, 22. Februar 2010 14:33 > An: arslist@ARSLIST.ORG > Betreff: Re: Help with Sybase indexes > > Hi Joe, > > Thanks for the reply. I did compare the information in the AR System's > meta-tables between the two systems (schema_index) and it matches up as > expected. However, I believe it is possible for there to be a mismatch > between what ARS thinks and what actually exists in the DB. The situation I > am trying to detect is the one where ARS thinks there is an index, but for > whatever reason, the index no longer exists in the DB. > > I have been using Misi's wonderful RRRchive utility and on occasion, I have > to interrupt a copy operation before it finishes. In this case, I believe > that the target table will be left without its index since RRRchive does not > get the chance to recreate the index on the target table when the program is > killed. I recreate the index(es) when this happens... I just want to make > sure I have not missed any. > > Thanks again. > Larry > > On Feb 21, 2010, at 8:53 AM, Joe D'Souza wrote: > >> Larry, >> >> Why not look this same information up from the AR System's meta information? >> That way it would be easier to compare the two systems indexes if they >> should be identical.. >> >> Joe >> >> -Original Message- >> From: Action Request System discussion list(ARSList) >> [mailto:arsl...@arslist.org]on Behalf Of L G Robinson >> Sent: Friday, February 19, 2010 5:47 PM >> To: arslist@ARSLIST.ORG >> Subject: Help with Sybase indexes >> >> >> Hi Folks, >> >> I need some Sybase help regarding indexes. >> >> I am migrating from Sybase to Oracle and I want to compare the two >> systems to make sure that the Oracle system has all of the same >> indexes on the T-tables as currently exist in the Sybase system. I >> don't doubt that they were there originally as a result of the >> workflow import that I did to create the Oracle system. I just want to >> make sure that I have not inadvertently messed any of the indexes up >> while I have been working in the Oracle system and copying data over (Thanks >> Misi!!!). >> >> On the Oracle side, I can do the following: >> >> SELECT s.name as "Form Name", b.column_name, a.table_name, >> a.index_name, a.uniqueness,a.index_type FROM USER_INDEXES a, >> USER_IND_COLUMNS b, arschema s WHERE a.index_name = b.index_name AND >> cast(s.schemaId as varchar2(40)) = >> substr(a.table_name,2) AND substr(a.table_name,1,1) = 'T' >> ORDER by 1,2; >> >> This gives me exactly what I need. However, I have been spelunking >> through the Sybase system tables all day and I can not seem to locate >> all of the information I need. I can see "almost" everything in the >> "sysindexes" table. >> However, I do not see the name or id number of the column that the >> index is built upon. Maybe I'm just missing something obvious but I >> sure don't see it. >> >> Anyone have any insights into how to accomplish the above in Sybase? >> >> Thanks. >> Larry >> >> ARS:5.1.2 >> Sybase: 12.5.2 >> Oracle: 10.2.0.4.0 >> >> Larry Robinson n...@ncsu.edu >> Office of Information Technology >> NC State University 919-515-5432 Voice >> Raleigh, NC 27695-7109 919-513-0877 FAX >> >> __ >> _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum > Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > > _
AW: Help with Sybase indexes
Larry, you should compare the metatables with the indexes oracle knows. If one of these 2 statements gives you any rows, you have messed up your indexes. SELECT name,'pk_idx for T-Table' FROM arschema WHERE schematype = 1 AND NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IT'||schemaid) UNION ALL SELECT name,'pk_idx for H-Table' FROM arschema WHERE schematype = 1 AND NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IH'||schemaid) UNION ALL SELECT name,'pk_idx for B-Table' FROM arschema WHERE schematype = 1 AND NOT EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IB'||schemaid) SELECT name,arschema.schemaid,schema_index.numfields,uniqueflag,indexname,f1,f2,f3 FROM arschema,schema_index WHERE indexname NOT IN (SELECT INDEX_name FROM user_indexes) AND schema_index.schemaid = arschema.schemaid HTH Kind Regards Conny -Ursprüngliche Nachricht- Von: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] Im Auftrag von L G Robinson Gesendet: Montag, 22. Februar 2010 14:33 An: arslist@ARSLIST.ORG Betreff: Re: Help with Sybase indexes Hi Joe, Thanks for the reply. I did compare the information in the AR System's meta-tables between the two systems (schema_index) and it matches up as expected. However, I believe it is possible for there to be a mismatch between what ARS thinks and what actually exists in the DB. The situation I am trying to detect is the one where ARS thinks there is an index, but for whatever reason, the index no longer exists in the DB. I have been using Misi's wonderful RRRchive utility and on occasion, I have to interrupt a copy operation before it finishes. In this case, I believe that the target table will be left without its index since RRRchive does not get the chance to recreate the index on the target table when the program is killed. I recreate the index(es) when this happens... I just want to make sure I have not missed any. Thanks again. Larry On Feb 21, 2010, at 8:53 AM, Joe D'Souza wrote: > Larry, > > Why not look this same information up from the AR System's meta information? > That way it would be easier to compare the two systems indexes if they > should be identical.. > > Joe > > -Original Message- > From: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org]on Behalf Of L G Robinson > Sent: Friday, February 19, 2010 5:47 PM > To: arslist@ARSLIST.ORG > Subject: Help with Sybase indexes > > > Hi Folks, > > I need some Sybase help regarding indexes. > > I am migrating from Sybase to Oracle and I want to compare the two > systems to make sure that the Oracle system has all of the same > indexes on the T-tables as currently exist in the Sybase system. I > don't doubt that they were there originally as a result of the > workflow import that I did to create the Oracle system. I just want to > make sure that I have not inadvertently messed any of the indexes up > while I have been working in the Oracle system and copying data over (Thanks > Misi!!!). > > On the Oracle side, I can do the following: > > SELECT s.name as "Form Name", b.column_name, a.table_name, > a.index_name, a.uniqueness,a.index_type FROM USER_INDEXES a, > USER_IND_COLUMNS b, arschema s WHERE a.index_name = b.index_name AND > cast(s.schemaId as varchar2(40)) = > substr(a.table_name,2) AND substr(a.table_name,1,1) = 'T' > ORDER by 1,2; > > This gives me exactly what I need. However, I have been spelunking > through the Sybase system tables all day and I can not seem to locate > all of the information I need. I can see "almost" everything in the > "sysindexes" table. > However, I do not see the name or id number of the column that the > index is built upon. Maybe I'm just missing something obvious but I > sure don't see it. > > Anyone have any insights into how to accomplish the above in Sybase? > > Thanks. > Larry > > ARS:5.1.2 > Sybase: 12.5.2 > Oracle: 10.2.0.4.0 > > Larry Robinson n...@ncsu.edu > Office of Information Technology > NC State University 919-515-5432 Voice > Raleigh, NC 27695-7109 919-513-0877 FAX > > __ > _ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
