Marek Pawelczyk

Query Converter - a New Tool to Migrate ClarifyCRM Queries to fcQuery Objects

Marek — Tue, 18 Nov 2008 20:43:30 GMT

Long time ClarifyCRM users may have a number of traditional Clarify queries accumulated over time. After such a business acquires Dovetail Software CRM products for Clarify database, it becomes one of the migration issues: how to reuse Clarify queries in the new thin client.

Dovetail Software Agent has no built-in support for Clarify queries, and for a good reason. fcQuery is far superior to traditional Clarify query model and all the users are highly encouraged to abandon Clarify queries in favor of fcQueries. If the business no longer uses Clarify Classic Client, migration is a must.

Until the release of Dovetail Agent v.4.2. the only way to move a Clarify query to fcQuery was to recreate it manually.

The new Dovetail Query Converter makes migration of Clarify queries into fcQueries very easy.

Clarify Query vs. fcQuery

These are the main points of distinction between both designs:

ClarifyCRM Queries:

only nine database objects can have queries defined,
search results are presented using a view not the base table, which limits the set of available columns,
search results can be sorted only by one data element,
to add a column to a query three Clarify forms and the view must be customized,
a query executes in two steps: filtering step followed by presentation step. First, a base-table query is executed using defined keywords and properties to retrieve a list of objids. Next, a view query uses the list of found objids to retrieve the final set of records.

fcQueries:

any database object can have queries defined,
the way search results are presented are defined by system administrator who decides which columns are included and are visible,
search results can be sorted by up to two data elements,
to add a column to a query no customizations are required, can be done online by system administrator,
a query executes in one step, all selection criteria are applied immediately.

There are many more goodies available in fcQuery making it flexible and efficient. More details can be found in these blog posts: Opening View Objects from Query Results and Run-Time Query Parameter Evaluation by Sam Tyson.

The query migration process

The tool is designed as a batch script to run on the server in either 'preview' or 'convert' mode. The 'preview' mode allows for evaluation of final results to make sure they meet the expectations. Even after the actual conversion is done and it is found that a resulting query is deficient, it can be modified in Dovetail or deleted and recreated.

The script can be run against a single query, all the queries created by a single user, or all the queries in the database. The original Clarify queries are not affected in any way, they remain available for use. The script may be re-run over and over again, if a query has already been migrated it will be ignored (if it has changed since last run, it won't be recreated or updated. In such a case it is better to delete it in Dovetail and recreate entirely).

As a result:

each Clarify query will have its fcQuery counterpart created under the same name,
all selection and sort criteria will be preserved,
the output columns will have the same headings and sequence as the original result view,
query ownership will be preserved,
if a query was iconified in Clarify it will be iconified in Dovetail Agent,
if a query was shared in Clarify it will be shared in Dovetail Agent with the same users.

Things to consider

When it is done manually, fcQueries are created in two places, in this sequence:

system administrator defines an fcQuery Object which defines a custom 'view' of the data. It is not a view in database terms, rather, it is a 'mask'. Only the 'unmasked' data elements defined for the fcQuery Object will be available later to the users.
agents personalize the fcQuery Object and create actual queries used to retrieve data.

Query Converter will create one fcQuery Object for each base Clarify query table - that means that up to nine fcQuery Objects may be created. Then, all the Clarify queries for a specific object will be tied to the same fcQuery Object, i.e. all the Clarify case queries defined by all the users will refer to the same fcQuery Object with alias name of 'case query'. This 'case query' fcQuery Object will then contain all the columns:

defined as result view columns in the database,
defined as search criteria properties in any case query,
defined as sort properties in any case query,
case_history column because it is used by keyword search.

It may also happen that Clarify Query forms have been customized to include additional result view columns and/or search/sort criteria properties. Some users may be grouped in base resource configuration having access only to out-of-box queries while others may have access to the customized ones. Query Converter treats these queries as they are separate entities, both base and custom queries will be migrated. The custom ones for cases will be associated with a separate fcQuery Object with alias name of 'case query ver_customer' where ver_customer is the version name of Clarify Query Edit form #806.

After the migration

After the Clarify queries are migrated to fcQueries, it is recommended that system administrator does a thorough review of the fcQuery Objects and eventually add/remove/reorder the columns. Then it is up to the owners to further personalize, iconify, share and refuse the queries.

Site And Contact Merge Tools in Dovetail Agent

Marek — Tue, 18 Nov 2008 15:16:41 GMT

Dovetail Agent v.4.1 has been expanded to include two tools to merge duplicate sites and contacts.

Before Dovetail Agent v.4.1, this functionality was available as Clarify Classic Client extension known as fcMerge, which is still available as a separate product. While fcMerge is also capable of handling duplicate address, part, and employee records, Dovetail Agent version of the tool concentrates on sites and contacts only, as these are the most likely ones in need of merging. The new tools do not use any Clarify technology. No more Clarify form customizations are required.

I'd like to use this opportunity to stress out the importance of having contact and site duplicates eliminated from the business process.

Why is this important ?

Due to spelling errors made by Call Center agents or automated feeds, there may be a situation when one contact or site is represented more than once in the database. For example, three contact records: "Mary Smith", "Mary J. Smith" and "Mary Joan Smith" represent the same individual customer "Mary Smith". This may lead to problems with proper identification of the contact later, resulting possibly with service being provided to a wrong customer.

It is highly desired to eliminate these duplicate records as soon as they appear.

How to deal with this problem ?

The superfluous records must be identified and made obsolete. All the objects associated with these duplicates (i.e. for contacts they include: alerts, commit logs, phone logs, contract-site roles, demand headers, cases, and contracts) must be reassigned to the record being kept. For this very reason, it is not recommended to do it manually! The Dovetail Agent Site/Contact Merge tools are the best choice to have it done safely and effectively. After the merge, duplicate records are no longer active and the remaining 'master' record now represents the contact providing access to all the associated objects.

The duplicate record elimination is a two-step process:

selection of the records to be kept or eliminated,
actual merging.

Step 1. is a manual action taken by a Dovetail Agent user. For a contact which is represented by more than one record it must be decided which one to keep and designate it as a 'master'. A new 'Mark Contacts for Merge' screen makes it possible. The same screen is used to identify the duplicates to be eliminated - they must be marked as 'aliases'. Similar 'Mark Sites for Merge' screen is available to perform this step for sites.

Step 2. is a batch script run which can be triggered manually on demand or scheduled to run periodically. The script reassigns all the objects from 'alias' contacts (or sites) to the 'master' contact (or site), and makes the 'alias' ones obsolete.

What is the best way to do it ?

It depends on how you become aware of the fact that duplicate contact or site records proliferate in your database.

For example, a call center agent may realize during a conversation with a customer that more than one contact records can be found for that person. This may be the best opportunity to take immediate action, obtain clarification of customer's details, and mark duplicate contact records as 'aliases'.

On the other hand, system administrators and business analysts who periodically run database queries and reports may realize that multiple contact records exist for the same customer. They can use 'Mark for Merge' screens to designate 'master' and 'alias' records.

No matter when the designation process takes place and by whom, it is later required to run the batch merge script to do actual merging. It is important to remember that until this script is executed in 'merge' mode, duplicate records remain visible and accessible to the users - just marking them as 'aliases' does not yet prevent the system from using them. The frequency of running the merge script depends on how quickly new duplicates appear, but the script may run 'on empty' without any harm to the database - if no records are marked for elimination, none will be affected. Therefore it is safe to run it regularly, unattended.

One more thing: a record marked as 'master' or 'alias' can be 'unmarked'. This is an 'undo' capability for phase one of the merge process. For as long as the merge script has not been run, 'master'/'alias' designations of contacts and sites can be modified. Once the merge script has been executed, there is no going back - the duplicates are eliminated.

Parent and Child Cases In Dovetail Agent

Marek — Wed, 20 Aug 2008 21:04:47 GMT

The 4.0 Release of Dovetail Agent brings support for parent-child relationship among support cases to the users.

Having a functionality available to designate one support case as parent of other cases may play important role in the way your customer support responds to a situation when a large number of cases report the same problem, but in many different ways.

Let's consider an example of a communication company with a range of services usually bundled to include: Internet access, VOIP, and cable TV. When a local hub goes down, customers start reporting problems with symptoms like: 'my phone line is dead', 'can't watch the news', 'my e-mail is not working', etc. These are some examples of case titles, which at first may not suggest that all these cases have much in common. Once a root cause if found, all these cases may be identified as 'child' cases of just one case which either was reported first, or explains the situation best.

Having a number of cases linked as child ones to a parent case has many advantages:

all the cases related to specific problem are now easy to find,
they can be managed together, for example closed in response to the parent case being closed.

Parent-child management for support cases has been known to ClarifyCRM users for a long time, Dovetail Agent now offers the same functionality but in a slightly different manner.

Our implementation follows the same general rules as ClarifyCRM:

A parent case can have many child cases and cannot have a parent case.
A child case can have only one parent case and cannot have child cases.
A case cannot be its own parent.

This is where we are different:

The Parent Case check-box on the Case screen is not available.
The Parent Case and Child Cases tabs are combined into one tab.
Attaching a child case to a new parent case is instantaneous upon clicking the Attach button. You do not need to click Save.
The Parent-Child relationship can be altered regardless of case ownership.
The Parent-Child relationship can be altered regardless of case status.
Parent Case tab is not available on the New Case screen. The Parent/Child tab becomes available once the new case has been created.
A new screen to search for a parent case has been added.
A new function to search for and attach multiple child cases to a parent case has been added.

Let me explain these differences in more detail:

#1: Parent Case check-box on the Case screen not available

You no longer designate a case to be a parent one by checking the Parent Case box on the Case screen in Dovetail Agent before making another case a child of this one. Although the table_case.is_supercase field is still there and we maintain it for compatibility with ClarifyCRM, it is set to 1 at the time a case becomes an actual parent. It gets reset to 0 when the last child case is detached.

#2: Only one tab with caption of Parent/Child, Parent Case, or Child Cases

Because a case can be either a parent or a child at the same time and never both, there is only one tab in Dovetail Agent dedicated to this subject and it changes its contents and caption depending on the situation. If a case is not a parent and not a child, this tab is used to explain what parent-child relationship for support cases is about and provide links as starting points to make a case a parent or a child.

Once a case becomes a parent or a child of another case, this tab changes its caption accordingly and shows the parent or a list of child cases for the current case.

#3: Immediate bonding of child case to its new parent

If you are on Parent Case tab and you have chosen a new parent case for the current one, there is an 'Attach' button to be used to associate these two cases. Selecting a new parent case does not make the case screen 'dirty'. Therefore, clicking on 'Save' instead of 'Attach' will not have any effect on parent-child relationship for this case. On the other hand, if you made some other changes to the current case, i.e. you changed its title, clicking 'Attach' button will only change parent-child relationship for this case but will not save the case itself, you must also follow up by clicking 'Save'.

#4: Power to the administrator

In fact, you don't have to even be member of any administrative privilege class to be able to manipulate parent-child relationships for cases you don't own. All you need is to have the Parent-Child Cases privilege. This is intended to allow bundling of cases by anybody who knows they belong together without yanking from and assigning back to the current owner.

#5: Old cases count too

A case may be made a parent or a child of another case even if it is now closed. This responds to a situation when some of the cases related to the root cause have been already resolved before you knew what the root cause was. Later, when you find out that they refer to this cause you may find them and assign to the right parent case without a need to reopen.

#6: Create a case first, make it a parent or child of another case later

This is a side effect of not having the Parent Case box on the Case screen available in Dovetail Agent. So you have to create a case, save it, and only then the Parent/Child tab becomes available.

#7: Finding new parent case is easy

When you are trying to locate a parent case for the current one, you have a 'Find' button available in 'New Parent Case' section of the 'Parent Case' tab. It takes you to 'Select Parent Case For Case: id_number' screen where you have access to a set of convenient filtering and sorting features, no need to define and store any 'filters'. Your search results are not limited to only those cases which are not closed.

#8: Attaching many cases at once to a parent is now possible

This function is not available in ClarifyCRM.

Imagine a situation when you have a large number of cases identified as being related to the same root cause. Now it's time to pick one of them and make it a parent of all the other ones. Some of them may have been already closed. How to do it quickly ? All you need to do is pick one of these cases, go to 'Parent/Child' tab, and follow the 'attach child case(s)' link which takes you to 'Select New Child Case(s) For 'Parent Case: id_number' screen. Here you have access to familiar search/sort features which may allow you to locate the cases you want to attach to the chosen parent. Once the grid is filled, you may attach the cases selectively or use 'Select All' button to mark them all. One important thing to mention is that if a case being attached to a new parent had a parent case before, it will be 'moved' from the old parent to the new one.

Please refer to Dovetail Agent documentation, Parent-Child Cases chapter, for more information about how to use this new functionality. It contains the screen shots and description of typical scenarios.

There is also an article by Gary Sherman about how to close all child cases when their parent case is closed.

Unicode Database Conversion And Usage

Marek — Tue, 19 Aug 2008 14:47:45 GMT

Introduction

Long-time ClarifyCRM users may find themselves in a situation driven by their business international expansion which calls for ability to handle client data in languages other than English and character set other than ASCII. If the database was not originally planed to store non-ASCII data, it needs to be 'converted' to be able to use Unicode.

The conversion may involve entire database, or only selected columns, depending on the needs.

Once the decision is made to store Unicode characters in existing non-Unicode Clarify database, there are several conversion scenarios to consider, depending on:

database system: MS SQL or Oracle
whether all the information stored must be in Unicode or only selected data items.

Dovetail Software, Inc. researched four scenarios how to make a database which was not originally intended to store Unicode data capable of just that, and allow manipulation by both Clarify Classic Client and Dovetail Software Agent.

Terms

Database Conversion means the process of modifying a database such that Unicode characters can be properly handled by Clarify and Dovetail applications that use that database for all string columns.

Partial Database Conversion means the process of modifying a database such that Unicode characters can be properly handled by Clarify and Dovetail applications that use that database for selected string columns.

Clarify documentation describes only entire database conversion process and does not mention any possibility of partial conversion or its adverse impact. Because entire database conversion is going to result in significant size increase of the database, partial conversion scenarios may be of interest to some users.

Dovetail Software applications are fully capable of handling Unicode databases, however, Dovetail Software doesn't currently offer any product that performs the process of converting a database to Unicode.

Summary Of Results

	ClarifyCRM v. 11.1 or higher	Dovetail Software
supports Unicode data before database conversion	no	MS SQL - no Oracle - yes (*)
supports Unicode data after entire database conversion	yes	yes
supports Unicode data after partial database conversion	yes	yes
ability to store/display Unicode via GUI	via multiline textbox fields only (**)	yes
relies on Clarify Unicode flag	yes	yes

(*) Need to be aware of issues with multibyte characters in regards to defined column sizes. As long as the data will fit in the column, all is well, see Oracle Database Concepts, explanation of VARCHAR2 datatype dependencies.

(**) During our testing, Unicode data only seemed to work properly if the UI control was a multiline textbox. Unicode data in simple textboxes did not work properly. Further research on this issue is suggested.

Oracle Database Concepts

Oracle databases support Unicode by default. Therefore, in this article we understand 'Oracle non-Unicode database' in Clarify terms, not Oracle terms.

Oracle supports a reliable Unicode datatype through NCHAR, NVARCHAR2, and NCLOB. The character sets used by NCHAR/NVARCHAR2 can be either UTF8 or AL16UTF16, depending on the setting of the national character set when the database is created. These datatypes allow character data in Unicode to be stored in a database that may or may not use Unicode as database character set. The NCHAR datatype stores fixed-length character strings that correspond to the national character set. The NVARCHAR2 datatype stores variable length character strings. When you create a table with an NCHAR or NVARCHAR2 column, the maximum size specified is always in character length semantics which is the default and only length semantics for NCHAR or NVARCHAR2.

VARCHAR2 and VARCHAR Datatypes: the VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle stores each value in the column as a variable-length field unless a value exceeds the column’s maximum length, in which case Oracle returns an error. Using VARCHAR2 and VARCHAR saves on space used by the table. Columns of both types can store Unicode characters, you need to be careful when specifying the size if you use byte length semantics to make sure there is enough room for expected data.

CLOB and NCLOB datatypes store single-byte and multibyte character strings of up to (4 gigabytes - 1) * (the value obtained from DBMS_LOB.GETCHUNKSIZE). Columns of both types can store Unicode characters.

LONG datatype stores variable-length character strings containing up to two gigabytes, but with many restrictions. This datatype is provided only for backward compatibility with existing applications. In general, new applications should use CLOB and NCLOB datatypes to store large amounts of character data, and BLOB and BFILE to store large amounts of binary data.

As a summary: CHAR, VARCHAR2, CLOB, and LONG columns can also store Unicode text. If the VARCHAR2 column is defined in character length semantics then it can store Unicode text up to the length specified. If the VARCHAR2 column is defined in byte length semantics then the length of the Unicode text it can handle (in Unicode characters) varies.

Database Character Set: An Oracle database has a database character set defined either at the installation time or as UTF-8 by default. This database character set can be changed after the database has been created. In most cases, a full export/import is needed to properly convert all data to the new character set. However, if, and only if, the new character set is a strict superset of all of the schema data, then it is possible to use the CSALTER script to expedite the change in the database character set. This article does not elaborate on the use of CSALTER script, refer to Oracle documentation for details.

Character Set Conversion Between Clients and the Server: If the database character set is different from the character set on the client operating system, then the Oracle database can convert the operating system character set to the database character set. Character set conversion increases the overhead and can sometimes cause data loss. Any characters that are not available in target character set are converted to a replacement character. The replacement character is often specified as a question mark or as a linguistically related character. The best way to prevent character conversion problems is to set a character set on Client system the same as the database character set. You set up the character set on Client MS Windows system by updating HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient10g_home1\NLS_LANG registry key. For example, AMERICAN_AMERICA.AL32UTF8 value defines AL32UTF8 as Client system character set.

The best way to minimize problems with future conversions of a database to Unicode is to make it the most Unicode-ready by setting these values at the database creation time:

NLS_CHARACTERSET as AL32UTF8
NLS_NCHAR_CHARACTERSET as AL16UTF16

These are the most recent versions of Unicode character set and should also be used on Client systems accessing the database. Oracle Database 10g Release 2 supports the Unicode Standard, Version 4.0.

Note: All Oracle conversion scenarios described in this article have been performed on Oracle Database 10g Express Edition Release 10.2.0.1.0 database, ClarifyCRM e|FrontOffice 12.5, Schema Revision 60.

MS SQL Database Concepts

In MS SQL Server, only NCHAR, NVARCHAR, and NTEXT data types support Unicode data. Use of NCHAR, NVARCHAR, and NTEXT is the same as CHAR, VARCHAR, and TEXT, respectively, except that:

- Unicode supports a wider range of characters.
- More space is needed to store Unicode characters.
- The maximum size of NCHAR columns is 4,000 characters, not 8,000 characters like CHAR and VARCHAR.
- The maximum size for NVARCHAR columns, using the max specifier, is 2^31-1 bytes.
- NTEXT columns store variable-length Unicode data with a maximum length of (2^30–2)/2 (536,870,911) characters. Storage size, in bytes, is two times the number of characters entered. NTEXT is no longer supported in string functions.
- All Unicode data uses the character set defined by the Unicode standard. Unicode collations used for Unicode columns are based on attributes such as case sensitivity, accent sensitivity, Kana sensitivity, width sensitivity, and binary.

SQL Server 2005 supports the Unicode Standard, Version 3.2.

Unicode data types store each character with two bytes instead of one byte. SQL Server column limit is 8000 bytes for both Non-Unicode and Unicode column. The maximum size of a Unicode column is 4000 characters. When migrating to Unicode, all column data beyond 4000 characters will be truncated. Column data should be scanned to prevent data loss. If a Unicode column needs to hold more than 4000 characters, the column should be converted to NVARCHAR(max). Using NVARCHAR(max) also prevents problems related to SQL Server page overflow - the server will automatically allow page overflow in case of a record length greater than 8060 bytes, serious performance factor.

Note: All MS SQL conversion scenarios described in this article have been performed on MS SQL Server 2005 database, ClarifyCRM e|FrontOffice 12.5, Schema Revision 60.

ClarifyCRM Concepts

Database Flags

A column named 'flags' of type INT (on MS SQL Server) or NUMBER (on Oracle) in ADP_DB_HEADER table:

bit number from right to left	hexadecimal mask	decimal mask	meaning
1	0x0001	1	case insensitive ('searchable' columns added for certain columns with names starting with S_ )
2	0x0002	2	password encrypted
3	0x0004	4	DWE enabled
4	0x0008	8	secure query enabled
5	0x0010	16	reserved
6	0x0020	32	Traveler enabled
7	0x0040	64	Unicode database

Unicode Flag

The 7th bit from right in ADP_DB_HEADER.FLAGS column. If set to 1 it indicates that this database is Unicode-enabled in ClarifyCRM terms. Both ClarifyCRM and Dovetail Software products rely on this flag when handling Unicode data.

The Unicode Flag is the only bit in ADP_DB_HEADER.FLAGS of concern for the purpose of this article.

Entire Database Conversion To Unicode

MS SQL Database

The goal: convert entire MS SQL non-Unicode database to a Unicode-enabled one and allow both Clarify Classic Client and Dovetail Agent to store/retrieve Unicode text.

Steps to perform:

Use Clarify dataex utility to export database schema:

dataex -gen_sc dbname.sch

Use Clarify ddcomp utility to upgrade and convert the database (note that there will be no actual upgrade unless the schema file changes):

ddcomp dbname servername userid password dbname.sch -unicode -upgrade -sqllog
the run should finish with the following message: "*** Schema Upgrade SUCCEEDED! ***"

Verify successful conversion to Unicode database:

Run SQL statement:
SELECT flags FROM adp_db_header
Expected result:
The Unicode Flag is set to 1
Examine table column types: former char, varchar, and text columns should now have types of nchar, nvarchar, and ntext.

Prepare Clarify Classic Client:

Run SQL statement: select * from table_locale
Find the row corresponding to the target locale. For Japanese text look for iso_cntry = 'JP', note the value of win32_lcid column (should be equal to 1041).
Delete Clarify Classic Client cache files.
Update Clarify Classic Client command line parameters: add -lcid 1041 (1041 should be the value of table_locale.win32_lcid column for Japanese)

Execute Verification Test for both Clarify Classic Client and Dovetail Agent.

Oracle Database

The goal: convert entire Oracle non-Unicode database to a Unicode-enabled one and allow both Clarify Classic Client and Dovetail Agent to store/retrieve Unicode text.

Initial verification:

Run SQL statement:
SELECT * from NLS_DATABASE_PARAMETERS;
Expected result:
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA
NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET values are not set.
Run SQL statement:
SELECT flags FROM adp_db_header
Expected result:
The Unicode Flag is set to 0

Steps to perform:

Use Clarify dataex utility to export database schema:

dataex -gen_sc dbname.sch

Use Clarify ddcomp utility to upgrade and convert the database (note that there will be no actual upgrade unless the schema file changes):

ddcomp dbname servername userid password dbname.sch -unicode -upgrade -allow_drops -sqllog
the run should finish with the following message: "*** Schema Upgrade SUCCEEDED! ***"

Verify successful conversion to Unicode database:

Run SQL statement:
SELECT flags FROM adp_db_header
Expected result:
The Unicode Flag is set to 1
Run SQL statement:
SELECT * from NLS_DATABASE_PARAMETERS;
Expected result:
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA
NLS_CHARACTERSET = AL32UTF8
NLS_NCHAR_CHARACTERSET = AL16UTF1
Examine table column types: VARCHAR2 columns should now have their sizes increased threefold, for example table_act_entry.addnl_info column size increases from 255 to 765.

Change NLS_LANG for Client Windows:

Update Windows registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient10g_home1
Change NLS_LANG entry to AMERICAN_AMERICA.AL32UTF8

Prepare Clarify Classic Client:

Run SQL statement: select * from table_locale
Find the row corresponding to the target locale. For Japanese text look for iso_cntry = 'JP', note the value of win32_lcid column (should be equal to 1041).
Delete Clarify Classic Client cache files.
Update Clarify Classic Client command line parameters: add -lcid 1041 (1041 should be the value of table_locale.win32_lcid column for Japanese)

Execute Verification Test for both Clarify Classic Client and Dovetail Agent.

What Happens During Conversion Of Entire Database To Unicode

To convert entire database to Unicode, Clarify tool ddcomp does the following:

for every user table (with name starting with table_):

- creates a temporary table with text columns enlarged (i.e. VARCHAR2 for Oracle) or text columns types changed (i.e. VARCHAR to NVARCHAR for MS SQL, the declared length remains unchanged),
- grants access privileges on the newly created table,
- copies the data from original table to the temporary one,
- drops the original table,
- renames the temporary table to original name,
- recreates all indexes defined for the table,
- drops all the views referring to the table,
- creates all the views referring to the table,
- drops all the csp_get_name stored procedures referring to the table or a view,
- creates all the csp_get_name stored procedures referring to the table or a view

for adp tables

- updates adp_db_header with new timestamp (last_modify, clarify_schema_date, and cust_schema_date fields),
- updates adp_db_header: the Unicode Flag is set to 1,
- recreates adp_sch_index entry for every index,
- recreates adp_tbl_name_map entry for every table,
- recreates adp_sch_info entry for every table column (ARRAY_SIZE value remains unchanged),
- recreates adp_sch_rel_info entry for every table relation,
- adds new entry to adp_db_comments table with comment = 'Upgraded database to Clarify schema revision 60, customer revision '

for internal DBMS tables (Oracle only)

sets NLS_DATABASE_PARAMETERS.NLS_CHARACTERSET and NLS_DATABASE_PARAMETERS.NLS_NCHAR_CHARACTERSET fields.

Partial Database Conversion To Unicode

Clarify does not support partial database conversion process through its products. Such possibility is not even mentioned anywhere in Clarify literature.

Dovetail Software has researched Partial Unicode Database Conversion scenarios and executed them successfully in test environment. The key to success lies in proper determination of all the data elements that need to be Unicode-enabled. For example, if the goal is to have all the notes Unicode-enabled the user must determine the list of all table columns which must be converted.

Partial conversion scenarios described here are based on the following assumptions:

- tables are not recreated,
- table columns types are redefined,
- indexes are not recreated,
- views are not recreated,
- stored procedures are not recreated,
- adp tables are not recreated,
- adp_db_header.flags field is updated with Unicode Flag set to 1,
- no Clarify schema changes are needed, the values of all CMN_TYPE, DB_TYPE, and ARRAY_SIZE columns for all adp_sch_info rows remain unchanged,
- no export/import of entire database or even affected tables is required,
- Oracle CSALTER script is not used,
- no Clarify tools (i.e. ddcomp) are used,
- no verification has been made whether the database after partial conversion remains compatible with other ClarifyCRM components, like: Rule Manager, DDEditor, ddcomp, dataex.

MS SQL Database

The goal: convert selected columns in MS SQL non-Unicode database and allow both Clarify Classic Client and Dovetail Agent to store/retrieve Unicode text in these columns.

Initial verification:

Run SQL statement:
SELECT flags FROM adp_db_header
Expected result:
The Unicode Flag is set to 0

Steps to perform:

Change column types:

For each columns being converted execute SQL statement:
ALTER TABLE table_name ALTER COLUMN column_name new_type
where new_type = NVARCHAR(max) when converting from TEXT
NVARCHAR(n) when converting from VARCHAR(n)
NCHAR(n) when converting from CHAR(n)
where n = original column width
For each column being converted with original type of TEXT also execute SQL statement:
ALTER TABLE table_name ALTER COLUMN column_name NTEXT
(a column would not directly convert from TEXT to NTEXT, must go through intermediate state of NVARCHAR(max))

Set Unicode Flag

Run SQL statement:
UPDATE adp_db_header SET flags = (SELECT flags + 64 FROM adp_db_header)

Convert table_source.code_chunk column to NVARCHAR(max)

Run SQL statement:
ALTER TABLE table_source ALTER COLUMN code_chunk NVARCHAR(max)
Without this conversion Clarify Client would not start.

Prepare Clarify Classic Client:

Run SQL statement: select * from table_locale
Find the row corresponding to the target locale. For Japanese text look for iso_cntry = 'JP', note the value of win32_lcid column (should be equal to 1041).
Delete Clarify Classic Client cache files.
Update Clarify Classic Client command line parameters: add -lcid 1041 (1041 should be the value of table_locale.win32_lcid column for Japanese)

Execute Verification Test for both Clarify Classic Client and Dovetail Agent.

Oracle Database

The goal: convert selected columns in Oracle non-Unicode database and allow both Clarify Classic Client and Dovetail Agent to store/retrieve Unicode text in these columns.

Initial verification:

Steps to perform:

Change column types:

For each columns being converted execute SQL statement:
ALTER TABLE table_name MODIFY (column_name VARCHAR2(n)) where n = 3 * ARRAY_SIZE

Set Unicode Flag

Run SQL statement:
UPDATE adp_db_header SET flags = (SELECT flags + 64 FROM adp_db_header)

Change NLS_LANG for Client Windows:

Update Windows registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient10g_home1
Change NLS_LANG entry to AMERICAN_AMERICA.AL32UTF8

Prepare Clarify Classic Client:

Run SQL statement: select * from table_locale
Find the row corresponding to the target locale. For Japanese text look for iso_cntry = 'JP', note the value of win32_lcid column (should be equal to 1041).
Delete Clarify Classic Client cache files.
Update Clarify Classic Client command line parameters: add -lcid 1041 (1041 should be the value of table_locale.win32_lcid column for Japanese)

Execute Verification Test for both Clarify Classic Client and Dovetail Agent.

Verification Test

After each scenario is executed, the following test is performed to verify successful conversion (use Japanese text sample which may be obtained here: http://www.lorem-ipsum.info/generator3 ):

Start Clarify Classic Client

Test Clarify application:

Open a screen which accesses the column(s) modified, in edit mode,
Paste a sample of Japanese text into these fields. It should show Japanese characters. Save the data.
Open the same screen again. These fields should show the text in Japanese.

Prepare Dovetail Agent:

Unload the Dovetail Agent web page in IIS.

Start Dovetail Agent

Test Dovetail Agent:

Open a screen which accesses the column(s) modified, in edit mode,
Paste a sample of Japanese text into these fields. It should show Japanese characters. Save the data.
Open the same screen again. These fields should show the text in Japanese.

Test inter-application compatibility:

Save a sample of Japanese text into the modified fields in Clarify Client.
Go to Dovetail Agent, access the same information. It should show Japanese characters.
Save a sample of Japanese text into the modified fields in Dovetail Agent.
Go to Clarify Client, access the same information. It should show Japanese characters.

References

For more information on ClarifyCRM, MS SQL Server, and Oracle refer to the following documentation:

Clarify Upgrade Guide
Clarify System Administration Guide
MS SQL Server Database Engine Guide
MSDN Articles on MS SQL Server
Oracle Database Concepts Guide
Oracle Database Application Developer’s Guide
Oracle Globalization Support Guide
Oracle Unicode Support Guide