IBM Support

How to Improve Performance for Perl Scripts that Load Records

Question & Answer


Question

Perl scripts and hooks that load IBM Rational ClearQuest (CQ) records do not always need to use all the fields in the record. Can some fields be omitted when the record is loaded?

Answer

The LoadPartialEntity CQ API (Application Programming Interface) method has been introduced as of the 2016C product versions, and can be used to load just part of a record. Performance can be improved by skipping the extra SQL statements to get the field data for certain field types. The field types that can be excluded in this way are reference list, attachment list, and history.

Note that newer ClearQuest releases automatically defer loading of some of these field types, and other field types can be configured to be deferred. This capability is known as deferred loading (or "lazy loading"). This API works seamlessly with that functionality, but goes further because it allows skipping some field types in older ClearQuest database Feature Fevels (FL) that do not support deferred loading. An example is the history field, which is automatically deferred when the database is at feature level 9 or above. With the LoadPartialEntity API, the history field can be omitted when using a database at any feature level, using a Client version that supports this API method.


API Definition


    /* Partially load an entity
    * entDefName: record type name of the entity to be partially loaded
    * displayName: display name of the entity to be partially loaded
    * list: directives to control what fields will be excluded.
    */
    CQEntityPtr LoadPartialEntity(const CQString& entDefName,
    const CQString& displayName,
    const CQStringArray& list);

    /* Partially load an entity
    * entDefName: record type name of the entity to be partially loaded
    * dbID: dbid of the entity to be partially loaded
    * list: directives to control what fields will be excluded.
    */
    CQEntityPtr LoadPartialEntityByDbId(const CQString& entDefName,
    const LONG dbID,
    const CQStringArray& list);


The entDefName, displayName, and dbID parameters are used to identify the record type and display name or DBID of the record to be loaded. These parameters are the same as for the GetEntity and GetEntityByDbId APIs. The list parameter contains a sequence of directives that are used to control what fields will or will not be loaded. Only some field types can be excluded when loading the record.

The list must contain field names or type directives. A type directive is used to exclude or include all fields of a specified type. The list is processed in order. Once a type directive is processed, the subsequent field names must name fields of that type.


Type Directives

A type directive is a string value that begins with either '+' or '-' followed by a field type designation. The designation can be either numeric or a string name. A special value of "all" is accepted to designate all field types. The numeric value is accepted because the CQ API definition (in CQPerlExt.pm) only defines numeric values for field types (see examples below). The string name of the field type matches the name of the API constant, but without the "CQ_" prefix, and is not case sensitive.

A leading '+' or '-' means all fields of the given type will be included or excluded, respectively. These type directives establish a mode for the field names that follow. Specifically, those names will be taken as the opposite of the mode established for the field type. Those names can be thought of as exceptions to the rule established for the field type. If more than one type directive is given for a particular field type, only the last one will have any effect. Since each type directive says to either include or exclude all fields of that type, it will override any directive for that type (and the associated field names for that type) that came before it.


Examples

For example, the following two list items will cause the load to exclude all reference list fields except for the "SubTasks" field:
    "-$CQPerlExt::CQ_REFERENCE_LIST", # exclude all reference list fields
    "SubTasks" # ... but do load field "SubTasks"


This is the equivalent specification using the string name for the field type:
    "-Reference_List", # exclude all reference list fields
    "SubTasks" # ... but do load field "SubTasks"


Here is another example. Suppose a script needs to check whether the next action to be performed on a record is past due. For this it only needs the two fields that represent that condition, so the load can be optimized with this list:
    "-all", # exclude all fields
    "Status", # ... but do load field "Status"
    "DueDate" # ... and field "DueDate"


Note that using "all" implicitly applies to just the fields that are allowed to be excluded from the load. In other words, using "-all" will not actually exclude many fields, since not all field types are supported for partial loading, and system-owned fields, such as "dbid", "ID", "State", etc., are always loaded. But this specification will implicitly take advantage of future changes that support excluding more field types, and will always load the smallest possible number of fields.

This next example is for a case where only attachment fields are needed. Again, using "-all" excludes all fields that can be excluded, and the next entry makes sure all attachment fields are loaded.
    "-all", # exclude all fields
    "+$CQPerlExt::CQ_ATTACHMENT_LIST" # … but include all attachment lists



Special Directives

The '@' special directives are used to specify how errors are handled. A goal of this API definition is to provide a way to be robust in the face of schema changes that add or remove fields, as well as optional loading behavior. For example, suppose the specification is to exclude a field, and that field is later removed from the record type. The field cannot be loaded since it no longer exists, which is the same end result as not loading it when it did exist. There is no need for an error in this situation since the end result is the same. However, to help debug script code, it may be useful to get an error in situations like this while the script is being developed.

A similar situation happens for optional lazy loading behavior. History fields are automatically lazy loaded at Feature Level 9 (FL9), but with this API, they can be skipped for databases at any feature level. The API allows naming the history field without error, whether or not it will be automatically lazy loaded.

It is never an error to name a field that is already handled in the way already specified by the current directive or implicitly.

Note that the API will normally throw an error if a named field is asked to be loaded but that field does not exist in the record type. But the opposite is not true, as the example above described.

The '@' directives are as follows. They are accepted in lower case only.

1. "@exists" causes an exception to be thrown if a named field does not exist in the record type, even if the specification is to exclude the field.

2. "@type" causes an exception to be thrown if the type of a named field does not match the current directive. Using this makes the API call sensitive to schema changes where a field is recreated as a different type. It is also useful when developing scripts to get an error if any fields are not specified correctly.

3. "@silent" means to never throw an error, even if a named field does not exist in the record type or its type does not match the current directive. This directive overrides both "@exists" and "@type". Normally an error is thrown when requesting to include a field but that field does not exist. Using this option is only appropriate if the script code is prepared to handle exceptions if it ever tries to access the value of a field that does not exist.

Some of these directives are intended to help when developing script code, to catch coding errors such as a misspelled field name or naming a field that does not match the current type directive. But they may not be suitable for production use as the API may fail when the schema is changed.


Exclusion Rules

1. Any reference list field can be named for exclusion, even if that field can be lazy loaded. Any field and any field type can be named for inclusion.

2. If an excluded field can be lazy loaded, it will be lazy loaded if referenced, and therefore its value will be available if requested.

3. If an excluded field cannot be lazy loaded, an exception will be thrown if the field value is requested.

4. Sharing in-memory instances of an entity:
    4a. If the identified entity is already fully loaded in the current session and no action is in progress on that entity, the result returned from LoadPartialEntity will reference that same instance of that entity. This is similar to the way GetEntity works.
    4b. If the identified entity is already fully loaded and has an action in progress, the result from LoadPartialEntity will be a new instance with values from the database, not from the instance being edited. This is different than GetEntity which would return a reference to the instance being edited.
    4c. The result from LoadPartialEntity will never be a reference to another instance of a partially loaded entity.

Internal Use Only

For the initial implementation, Reload will load the entire record. This is expected to be changed so that the following is true (and can then be added to the list above as point 5):

5. Reload on a partially loaded entity will repeat the same partial load as specified on the original LoadPartialEntity call.

[{"Product":{"code":"SSSH5A","label":"Rational ClearQuest"},"Business Unit":{"code":"BU004","label":"Hybrid Cloud"},"Component":"Designer - Hooks","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"8.0.1.13;9.0.0.3","Edition":""}]

Document Information

Modified date:
17 June 2018

UID

swg21993889