2 Replies Latest reply on Feb 19, 2019 6:58 AM by Ernst Schmitt

    Migration from 7.6 to 9.1: column names in user_tab_columns contaning special chars or German umlauts gets changed (exceeding 30 bytes now)

    Ernst Schmitt
      Share:|

      When migrating from AR 7.6 to 9.1: column names with 30 characters in user_tab_columns contaning special chars or German umlauts gets changed to UTF-8 bytes and so exceed the 30 bytes max length restriction, the column name gets cut.

      When the first 26 characters of 2 fieldnames are identically, the migration changes the last 4 characters of the fieldnames to 0001, 0002, ...

       

      Any sugggestions to suppress this?

      Can anybody describe in detail the mapping rule for column-names and view-names as well: old_name (ISO-8859, max. 30 characters) -> new_name (UTF-8, max. 30 bytes)?

        • 1. Re: Migration from 7.6 to 9.1: column names in user_tab_columns contaning special chars or German umlauts gets changed (exceeding 30 bytes now)
          Marie Johnson

            You can look at sp_configureErnst

           

          I’ve honestly not run into this view issue.  Can you supply a bit of information?

          1. what is the originating DB, version, and charset and language is set?
          2. is the dB (ARSystem) in Unicode? 
          3. what is the new DB, version, and charset and language is set?
          4. is this dB in Unicode?
          5. the login properties for the ID which is performing the install, what is its preferred language on each server dB (English is default)?
          6. Is sp_configure ‘default language’ set to 1 which is German? (This assumes MSSQL)
          7. what is this server charset and language?  You can setup a server to one language and a dB on it to another!  So run a (this assumes MSSQL) SELECT @@VERSION get the SQL server info and tell us what it says, then:  SELECT Collation_name from sys.databases where name=‘ARSysytem’ [or whatever you’ve called it]
          8. are you doing an in-place upgrade?  If not what are you doing?
          9. is this form in which the view names are associated with custom or an overlay to ITSM, or other?
          10. Did you install and configure remedy and set the German language to default before loading the applications? ( doesn’t apply if this is an in place upgrade)

          I think once you answer some of this, then Peter Adams can do validate one things to make sure there isn’t a defect With the install.  The bottom line is that remedy needs to fully understand what characterset it is reading

          1 of 1 people found this helpful
          • 2. Re: Migration from 7.6 to 9.1: column names in user_tab_columns contaning special chars or German umlauts gets changed (exceeding 30 bytes now)
            Ernst Schmitt

            Hello,

             

            we are using ORACLE 12:

             

             

            AR-Server7.6.04 Build 00 SP5-S
              RTNK_dbg_210716
            AR-Server
              Server Language
            de_DE.iso88591;WESTERN
            Oracle Database 12c Enterprise Edition12.1.0.2.064bit Production
            NLS_NCHAR_CHARACTERSETAL16UTF16NCHAR Character set
            NLS_CHARACTERSETWE8ISO8859P15Character set
            NLS_LANGUAGEAMERICANLanguage
            IBM
              WebSphere MQ
            8.0.0.7p800-007-170629
            LinuxLinux 4.4.59-92.20-defaultSUSE Linux Enterprise Server 12
              SP2
            Tomcat
              WMSTI
            7.0.75.0Apache Tomcat/7.0.75
            JVM
              Version WMSTI
            1.8.0_51-b16
            Midtier
              Version
            9.1.05 201805242356

             

             

            Wenn we migrate to AR Server 9.1 we get:

             

             

            AR-Server9.1.06 201811060802
            AR-Server
              Server Language
            de;UTF-8
            Oracle
              Database 12c Enterprise Edition
            12.1.0.2.064bit Production
            NLS_NCHAR_CHARACTERSETAL16UTF16NCHAR Character set
            NLS_CHARACTERSETWE8ISO8859P15Character set
            NLS_LANGUAGEAMERICANLanguage
            IBM
              WebSphere MQ
            8.0.0.7p800-007-170629
            LinuxLinux 4.4.131-94.29-defaultSUSE Linux Enterprise Server 12
              SP3
            Tomcat
              WMSTI
            9.0.10.0Apache Tomcat/9.0.10
            JVM
              Version WMSTI
            1.8.0_192-b12
            Midtier
              Version
            9.1.06 201808280304

             

             

            There are several problems:

            1. BMC changed the internal data type for tablenames/viewnames/columnnames from CHAR30 to byte30. So every German umlaut has now 2 bytes, and so columnnames (in user_tab_columns) of exactly 30 character will now be longer than 30 bytes, and so they are cut off.

            2. BMC changed the internal mapping rules for view names and column names, for example view name 'SAD:Hello World' and 'SAD:Hello-World' and 'SAD:Hello_World' are now all mapped to the same name: 'SAD_HELLO_WORLD', and to avoid naming conflicts they get a incremental suffix 'SAD_HELLO_WORLD_0001', 'SAD_HELLO_WORLD_0002' and so on.

             

            When the first 26 characters of different field names of one single form differ only in blanks, underlines, minus-sign (anything else?) they get renamed with incremental suffix 0000-9999.

            So 'SADIS:User Administration Name' and 'SADIS:User-Administration-Name' will be migrated to 'SADIS_USER_ADMINISTRATION_0001' and .

            'SADIS_USER_ADMINISTRATION_0002'

             

            But not only the characters -, blank and underline are replaced, but a lot of other 'special' characters. But there is no documentation about it. Without documentation you can't avoid such names and even you cant search your database for such names.

             

            The renaming occurs only when you 'touch' the form and modify it. That means your system looks nice after migration to 9.1, but whenever a developer modifies a form with 'strange' name the internal name changes after saving the form. This behaviour is very bad.

             

            We have a lot of C/C++- and Java-Code (and even workflow with DirectSQL) which access the internal tables by name, and now the internal names changed -> big problem.