10 Replies Latest reply on Oct 3, 2019 7:21 AM by Stefan Hall

    A fresh 1902 installation lacks all IBxxx indexes - right or wrong?

    Stefan Hall
      Share This:

      When I compare a fresh 1902 installation with an upgraded installation, we lack several thousand indexes in the fresh installation!

      A closer look reveals that 1902 no longer create IBxxx indexes.

       

      Now the question to the many other experts and 1902 early birds: Is this correct or a serious problem of the installer (maybe only the linux installer)?

      Maybe we didn't notice it in earlier versions, we usually just upgrade.

       

      Our System

      - RH7

      - Oracle

      - AR 1902

        • 1. Re: A fresh 1902 installation lacks all IBxxx indexes - right or wrong?
          Andreas Mitterdorfer

          I have a testbox where enabled oracle case insensitive feature immediatly after ARS 19.02 install and continued installation of ITSM with it. There I have IB function based indexes.

          If you haven't enbled the feature you will not have IB function-based indexes but only the normal (PK_B) ones. But then your I<> indexes should also be Normal indexes not function based once. Have you checked this?

          • 2. Re: A fresh 1902 installation lacks all IBxxx indexes - right or wrong?
            Stefan Hall

            I don't think I heard you exactly. Maybe I did.

             

            I've never seen PK-B indexes as an equal substitute for IB indexes. The number is also completely different, much more IB than PK-B indexes. PrimaryKey and unique index is not the same.

             

            If I understand you correctly, do you only have IB indexes if you set functional indexes? So how is performance access supposed to occur when functional indexes are not enabled?

            • 3. Re: A fresh 1902 installation lacks all IBxxx indexes - right or wrong?
              Andreas Mitterdorfer

              Maybe we have a misunderstanding.

              Please can you explain with an example, which IB indexes you are missing on a typical itsm form eg. HPD:Help Desk along with its ddl?

              • 4. Re: A fresh 1902 installation lacks all IBxxx indexes - right or wrong?
                Stefan Hall

                There weren't that many answers!?

                • Do you (still) have IBxxxx indexes in your system?
                • Also in a fresh installation?
                • Do they really depend on functional indexes?

                 

                Andreas Mitterdorfer

                Currently I‘ve installed AR only and miss all IBxxxx indixes.

                • 5. Re: A fresh 1902 installation lacks all IBxxx indexes - right or wrong?
                  Mohammad Rehman

                  Hi Stefan Hall,

                   

                  I don't have 19.02 upgrade or fresh install yet. Do you see PK_ indexes instead?

                  For Example:

                  Table B1020, previously had IB1020 index and now it should have PK_B1020

                  I don't exactly remember but from 9.x version BMC started adding Primary Key on field id 1 instead of adding unique index, that applies to all T, H and B tables. But If you were upgrading then installer was so intelligent never converted the existing indexes. ( I had to do it myself, discovered when i created new form then it created the Entry ID/Request ID field index as PK_T<new form id> etc.)

                   

                  Regards,

                  Mohammad

                  3 of 3 people found this helpful
                  • 6. Re: A fresh 1902 installation lacks all IBxxx indexes - right or wrong?
                    Stefan Hall

                    Thank you Mohammad, I'll take a closer look.

                     

                    Then I would have to find in a fresh installation all former unique IBxxxx indexes as PK indexes. I am curious and must now unfortunately wait until tomorrow. My working day is already over

                    • 7. Re: A fresh 1902 installation lacks all IBxxx indexes - right or wrong?
                      Stefan Hall

                      Hi Mohammad,

                      a short intermediate information.

                       

                      • The BMC support and DB expert first claimed that there is no difference between fresh and upgrade. You pay very close attention to this. However, it took only a few hours, and can be clearly proven.
                      • Then I asked for instructions on how to clean up all the old forms, taking into account our functional indexes (binary_ai). I received the constraints command, as Google displays it after 2 seconds. Very annoying ... to be continued.

                       

                      Otherwise we are working on a general implementation ourselves, but binary_ai doesn't make it any easier.

                      I'll keep you and everyone else up to date here.

                      3 of 3 people found this helpful
                      • 8. Re: A fresh 1902 installation lacks all IBxxx indexes - right or wrong?
                        Devita Mujoo

                        Use the following script which finds ARADMIN tables that have a Unique index on column C1 and no matching Primary Key on it. The steps below will create a spool file called uni2pk.sql that will contain individual "alter table ..." commands that can then be run individually or as a file.

                         

                        ________________________________________________

                         

                        set lines 80

                        set pages 10000

                        set heading offselect 'alter table '||j.table_name||' add constraint PK_'||j.table_name||' primary key using index '||j.index_name||';'

                        from all_ind_columns i,

                        all_indexes j

                        where j.owner = 'ARADMIN'

                        and j.uniqueness = 'UNIQUE'

                        and j.index_name = i.index_name

                        and i.column_name = 'C1'

                        and (i.index_name, i.column_name) not in

                        ( select b.index_name,

                        a.column_name

                        from all_cons_columns a,

                        all_constraints b

                        where a.constraint_name = b.constraint_name

                        and a.table_name = j.table_name

                        and b.table_name = j.table_name

                        and b.constraint_type = 'P')

                        /

                        spool off

                        spool uni2pk.sql

                        • 9. Re: A fresh 1902 installation lacks all IBxxx indexes - right or wrong?
                          Devita Mujoo

                          Use the corrected sql below:

                           

                          select 'alter table '||j.table_name||' add constraint PK_'||j.table_name||' primary key ('||i.column_name||') using index '||j.index_name||';'

                          from all_ind_columns i,

                          all_indexes     j

                          where  j.owner      = 'ARADMIN'

                          and    j.uniqueness = 'UNIQUE'

                          and    j.index_name = i.index_name

                          and i.column_name = 'C1'

                          and (i.index_name, i.column_name) not in

                          (  select   b.index_name,

                          a.column_name

                             from     all_cons_columns  a,

                          all_constraints   b

                             where    a.constraint_name = b.constraint_name

                             and      a.table_name = j.table_name

                             and      b.constraint_type = 'P')

                          /

                          3 of 3 people found this helpful
                          • 10. Re: A fresh 1902 installation lacks all IBxxx indexes - right or wrong?
                            Stefan Hall

                            Hi Devita, thanks for your support. Made it easier for me to get started with this topic, even though I solved it a little differently in the end to more accurately replicate the current AR server.

                             

                            @all others

                            It is important to take care of this, because starting with Oracle 12 you can't work with join forms on DB level without primary keys. I need that at least for DB ports from prod to qa or dev.

                            Nevertheless, it is a pity that BMC has adapted the AR server and new forms take this into account, but has not taken care of the existing system.

                            3 of 3 people found this helpful