It looks like the number of bytes per character varies between db implementations as well. Oracle's AL32UTF8 uses 3 bytes per character. Your SQL Server looks like it uses 2 bytes per character for whatever character set you are using. On Oracle, Remedy defines the column size according to the workflow definition during the DDL operation:
Bytes: varchar2(100) Character: varchar2(100 char) It assumes a default NLS_LENGTH_SEMANTICS of BYTES and if you change that, Remedy will create all the columns of length X as X chars and not X bytes. This leads to a big mess. On Fri, Mar 4, 2011 at 8:30 AM, Misi Mladoniczky <m...@rrr.se> wrote: > Hi, > > WARNING!!! > > I got a friendly note from BMC about this, and they reserve extra space in > the database to fit any potential data into the fields. > > This means that it is not as easy as changing the attribute, as the > storage of the field in the database can change to CLOB etc, depending on > database version. > > This is how it looked in my AR Server 7.6.04 against SQL Server 2008, when > adding 4+4 fields where Unit equals Byte and Character respectively: > ALTER TABLE T303 ADD > C600000063 nvarchar(63) NULL, # unit = bytes, size = 63 > C600000064 nvarchar(64) NULL, # unit = bytes, size = 64 > C600000254 nvarchar(254) NULL, # unit = bytes, size = 254 > C600000256 nvarchar(256) NULL, # unit = bytes, size = 256 > C600001063 nvarchar(126) NULL, # unit = characters, size = 63 > C600001064 nvarchar(128) NULL, # unit = characters, size = 64 > C600001254 nvarchar(508) NULL, # unit = characters, size = 254 > C600001256 nvarchar(512) NULL # unit = characters, size = 256 > > Hence: > A. It must be changed through the API or DEF-import > B. It is not a good idea to do an overall change to Character, as this may > affect performance and database size. > > I suggest that you do more or less as I suggested at first: > 1. Copy your data using RRR|Chive, and check the log for > records/forms/fields where the data did not fit (ARERR 306) > 2. Decide if you want to truncate the data, extend the field length or > change Unit to Character using DevStudio > 3. Run rrrChive again till everything gets transferred. > > Best Regards - Misi, RRR AB, http://www.rrr.se > > Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): > * RRR|License - Not enough Remedy licenses? Save money by optimizing. > * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. > Find these products, and many free tools and utilities, at http://rrr.se. > > > Hi, > > > > To do an automatic change to CHAR instead of BYTES, I see three ways to > do > > this. > > 1. Create an API-program that updated all character fields > > > > Downsides: There may be a ARAPI-version problem that corrupts the > > CharLimitsStruct if the API-program is of a different version > > > > 2. Export a def-file, manipulate the def file, reimport the def > > > > Downsides: It will require a very big def for large applications, which > > may not be good. > > > > 3. Do an SQL-update in the database and restart your AR Server. > > > > Downsides: The change may not be propagated to the clients, as the > > modify-date of the will not change. But that may not be necessary, as > this > > check is done on the server side. Also this is not really supported by > > BMC. > > > > Number 3 may after all be the best choise, as it should be very fast. > > > > UPDATE field_char SET lengthUnits=1 WHERE lengthUnits=0 > > > > Maybe someone could comment on the possible risks, but I think they > should > > be quite low. > > > > In my AR Server 7.6.04 I found three fields with lengthUnits=1 already: > > Form/FieldId/FieldName > > AR System Ignored Analyzer Results/20007/Object Parent > > AR System Form Field Info/90009/Results List Meta Data > > AR System Administration: Server Information/16799/atriumssoKeystore > > > > Document your own lengthUnits=1 fields before you do the change: > > SELECT schemaId,fieldId FROM field_char WHERE lengthUnits=1 > > > > Best Regards - Misi, RRR AB, http://www.rrr.se > > > > Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): > > * RRR|License - Not enough Remedy licenses? Save money by optimizing. > > * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. > > Find these products, and many free tools and utilities, at http://rrr.se > . > > > >> Hi, > >> > >> I just saw something... > >> > >> You can change the "Length Unit" in the database properties of your > >> field. > >> > >> The possible values are "Bytes" and "Characters". > >> > >> The default is "Bytes", but if you change this setting for all of your > >> character fields, the old field length should be able to hold all your > >> data even in utf8. > >> > >> According to the ar.h file, this functionality did not exist in version > >> 7.0 or 7.1, but was introduced in 7.5. > >> > >> Here is an exerpt form the ARAPI ar.h-file from 7.6.03: > >> > >> typedef struct ARCharLimitsStruct > >> { > >> unsigned int maxLength; /* 0 for maxLength means unlimited length */ > >> ... > >> unsigned int lengthUnits; /* 0 for in-byte, 1 for in-char */ > >> ... > >> } ARCharLimitsStruct; > >> > >> This should be ideal for a small RRR-utility, if there is enough demand. > >> Let me know that you would like a RRR|CharToBytes-utility! > >> > >> Or are there an easier way of doing this, such as a system wide default > >> setting??? > >> > >> Best Regards - Misi, RRR AB, http://www.rrr.se > >> > >> Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): > >> * RRR|License - Not enough Remedy licenses? Save money by optimizing. > >> * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy > >> logs. > >> Find these products, and many free tools and utilities, at > >> http://rrr.se. > >> > >>> Hi, > >>> > >>> I have not tried this on Informix, but on Sybase, Oracle and MS-SQL. > >>> > >>> Remedy counts bytes, so the 255-byte character field will still be 255 > >>> bytes long. > >>> > >>> The problem is when you migrate your data, where a 255-character string > >>> will not allways fit in 255-bytes. In 99% of the cases, the string will > >>> probably fit, but you will have problems with some data. > >>> > >>> In this case you can choose to either truncate your data, or extend the > >>> field length sligthly. > >>> > >>> If you go from cp-1257, I think that most of the characters above code > >>> 127 > >>> will fit in two bytes. When I have converted Swedish systems, very few > >>> records has needed more than 10% extra space. > >>> > >>> The strategy I have most commonly used, on custom applications, is the > >>> following: > >>> 1. Character fields > 128 characters or so, truncate the data in the > >>> source database before transfer > >>> 2. Character fields < 128 characters, extend the target field size by 1 > >>> byte when the merge-operation fails for certain records, and merge > >>> again > >>> > >>> Best Regards - Misi, RRR AB, http://www.rrr.se > >>> > >>> Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): > >>> * RRR|License - Not enough Remedy licenses? Save money by optimizing. > >>> * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy > >>> logs. > >>> Find these products, and many free tools and utilities, at > >>> http://rrr.se. > >>> > >>>> Hi, > >>>> > >>>> I've got the question about Remedy (7.5) and Informix (utf8). > >>>> We are going to migrate the workflow and data from Remedy 7.1 > >>>> (Informix > >>>> cp-1257) to Remedy 7.5 (Informix utf8). > >>>> As far as I know the ARS 7.5 allows to set the Length Unit to > >>>> Characters > >>>> (not Bytes). What will happen with data if the Length Unit will be > >>>> changed > >>>> to Characters? > >>>> For example, if the character field's lenght in ARS 7.1 was 255 and it > >>>> was > >>>> possible to include this field in the query's qualification, does it > >>>> mean > >>>> that the field's length in ARS 7.5 automatically will be 255*4 and the > >>>> search will not be allowed without FTS? > >>>> > >>>> Best regards, > >>>> Irina > >>>> > >>>> > _______________________________________________________________________________ > >>>> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >>>> attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" > >>>> > >>> > >>> > _______________________________________________________________________________ > >>> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >>> attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" > >>> > >> > >> > _______________________________________________________________________________ > >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > >> attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" > >> > > > > > _______________________________________________________________________________ > > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > > attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" > > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
