SM 9.41P2, operator record, is cap.exec no longer an array?

Hi all,

During the upgrade of one of our development environments we got this warning in exception.log:

2016-02-01 15:33:55 No hay soporte del cambio de tipo SQL para operator en cap.exec dbdict de CLOB a VARCHAR2(50)

Which means that it tried to convert cap.exec from CLOB to VARCHAR2(50) during the Application Upgrade. Is that coirrect, I mean operators are supposed to have only one cap.exec instead of an array of Capabilities? Or is that an error in the Application Upgrade process?


  • you shouldn't convert clob to varchar2(50), you will lose data. Please verify how much data this array has and then just ignore this change. There are two possibility, this field is Varchar2(50) oob and was converted/customized in your environment. Any exceptions that you decide not to proceed with should be documented.

  • I know that I will loose data if this is done, operators have several cap.execs assigned to them. But it's not me, it's the Upgrade Assistant that tried to do that, so I'm asking why.


  • Several arrays are mapped to varchar fields in the OOB datadicts (e.g. for a long time the assignment group field in the operator table was). While the data stores successfully, it's problematic if 1) you exceed the allocated length or 2) you remap to an array to fix it which often results in data loss. 

    You can ignore the error. 

  • I suggest you ignore this exception especially knowing that the cap.exes has array data. I am glad the upgrade utility didn't change it but threw an exception that can be ignored.


  • Verified Answer

    I think the change proposed by the upgrade utility is correct, while the message is not exactly getting the point.



    Because my OOB system works fine like this.

    And why that?

    Because the array cap.exec is mapped to a separated table, where each element is stored in a separate record - so VARCHAR(50) is for the element, and I can't think of an capability word exceeding 50 characters.


    Looking at the dbdict (attached) will demonstrate the mapping.

    This new mapping has the advantage, that you can efficiently run queries against cap.exec field: SM will generate a SQL statement that joins OPERATORM1 and OPERATORA1 table and the RDBMS can efficiently process this.

    When the array is mapped as LOB data (CLOB, BLOB, IMAGE, MEMO, ...), SM will replace the query clause by (1=1) - so the SQL is a relaxed version of the query: RDBMS has to fetch more records, send them over the network to SM, SM needs to reselect in its memory - a chain of inefficiency.

  • AFranke, that now answers my question. For some reason here in SM cap.exec is an array of character, not an array of varchar2, so we have this in the DBDICT

    Furthermore, it is stored in M1 as a CLOB:

    Which of course creates the inefficiencies you mention. That's why SM tried to convert it to VARCHAR2, it actually wanted to convert it into an Array of VARCHAR2.

    With this we have enough information to try revert it to the default, and store it in a new table instead of a CLOB.

    Thank you

  • That is correct; the field should be mapped to an A table,. You may also want to confirm that the assignment.groups field is also not mapped to a CLOB: that impacts the response when selecting an assignee significantly. It should also be mapped to an A table. Both changes will improve performance.

  • That is correct; the field should be mapped to an A table,. You may also want to confirm that the assignment.groups field is also not mapped to a CLOB: that impacts the response when selecting an assignee significantly. It should also be mapped to an A table. Both changes will improve performance.

  • Ok, I have succesfully converted this field from CLOB to an Array of Varchar2. It was quite easy in fact, so I'm posting it here in case someone else has this same issue:

    1. Open the dbdict of operator,
    2. Go to tables and see which is the last "a" table defined there, in my case it's a2-OPERATORA2-oracle10,
    3. Go back to fields tab and open the first line where cap.exec is defined, the line where the SQL Fields are blank, and in the SQL Table add the name of the next "a" table that doesn't exist in step 2, in my case "a3",
    4. Open the second line of cap.exec where the actual data type is defined, here I had SQL Name: CAP_EXEC, SQL Type: CLOB, SQL Table: m1 and change it to SQL Name: CAP_EXEC, SQL Type: VARCHAR2(50) SQL Table: a3,
    5. Double check that you modified the parameters in step 3 and 4 and save the dbdict. This will trigger a process that will take some time depending on how many operator records you have, it can be hours.

    Of course make backups first, if you miss the step 3 you'll end with lost data and no way to revert the changes.

  • Good to see that this changed worked for you, and thanks a lot for posting the steps here.

    Regarding other array fields, our R&D takes the decision how to map these. There is a tradeoff for both options, so you have to think about the benefit.


    Here roughly how SM executes queries - so you may understand this balance:

    1. Translate query to SQL and fetch unique key columns of the matches (in blocks of 500 (configurable)- more will be fetched when required (i.e. user scrolls down, the database operation is sorted, or a count was done).

    2. SM fetches the records for record list (QBE, virtual join -  only displayed columns) in blocks of 128 (configurable) records

    3. SM fetches all columns for the current record (first in list - except when using <SCFile>.doSelect() in JS with <SCFile>.setFields() (for readonly access only))


    - With LOB field mapping:

       * fetching unique keys: The query clause on the LOB field is no translateable to SQL (like functions as well), and is translated to (1=1). As explained already, this may cause a lot of inefficiency.

       * fetching: When fetching all or some columns including LOB field, an additional network roundtrip per LOB column (at least with Oracle)


    - With SQL-selectable mapping (multi-row-array table):

      * SM can translate the query to SQL to join of all tables that fields in query are mapped to. RDBMS is able to process this efficiently.

       Query clause for is a value is contained in array is:    array="A"

       Query clauses on multiple indexed elements in table however may fail (QCCR1E126770):    1 in array="A" and 2 in array="B".

     * Fetching records requires multiple network round trips as records of multiple tables have to be access: One per element in table.


    Note: ServiceManager has also other mapping options. However, over time, these are the two crystalized to be what you really need.