Why do I get performance issues when retrieving flex field information from Datamart?

Flex fields are stored in XML format in the flex fields table. Due to the complexity involved in parsing the XML data and retrieving the relevant flex field information, you can see some performance issues. To overcome this, the XML to non-XML conversion based solution or feature has been implemented to store the flex fields in non-XML format. This approach has redefined the flex field storage and retrieval mechanism and thus has improved the performance of reporting queries while retrieving flex field information from Datamart.

How to enable or disable this feature in Datamart?

To enable or disable this feature respectively, update the field “attribute_value” to either true or false for the record where entity_name is 'flex_field_xml_to_nonxml_etl' and attribute_name is 'ALLOW_FLEXFIELD_XML_TO_NONXML' in ETL Attributes table.

update etl_attributes set attribute_value='true' where entity_name = 'flex_field_xml_to_nonxml_etl' 
and attribute_name = 'ALLOW_FLEXFIELD_XML_TO_NONXML';

Does a new job have to be executed to get the benefits of this feature?

No, the existing tracker initial job and tracker incremental job will do the required data processing.

What would happen if a parsing error is encountered during XML to non-XML conversion?

In such cases where auto correction is not possible for incorrect XML records, the incorrect records are to be corrected manually and to be updated with the status "closed". Once the next ETL process picks up this record and if it is valid, then the job removes the entry from this table, updates the status from "closed" to "processed" and populates the parsed data into new flex field bridge table. If the manually corrected xml is still invalid, then the job updates the status of that record from “closed” to “open”, so that the user can work on correcting the entry.

What are the changes related to custom reports development on flex fields?

The custom report developers need to query the new flex field bridge tables to get the benefits from the XML to non-XML conversion feature implementation.

How to handle the ETL job failure due to OutofMemoryError: GC overhead limit exceeded?

For a permanent fix, see Why am I getting an email specifying that the ETL job has failed?

To fix this issue during runtime, perform these steps:

Stop the ETL service.
- kill -9 <etl process id>
Open the file /opt/collabnet/teamforge/runtime/conf/set-env.sh.

Increase the JVM heap size in ETL_JAVA_OPTS to Xms512 and Xmx2048m.

ETL_JVM_OPTS="-Xms512m -Xmx2048m -server -XX:+HeapDumpOnOutOfMemoryError -
XX:HeapDumpPath=/tmp -verbose:gc -XX:+PrintGCTimeStamps -XX:+PrintGCDetails -
Dsun.rmi.dgc.client.gcInterval=600000 -Dsun.rmi.dgc.server.gcInterval=600000 -
Djava.awt.headless=true -
Dsourceforge.home=/opt/collabnet/teamforge/runtime/sourceforge_home -
Dsourceforge.logdir=/opt/collabnet/teamforge/log/etl -
Dapp.data=/opt/collabnet/teamforge/var -
Dapp.runtime=/opt/collabnet/teamforge/runtime -
Dapp.distribution=/opt/collabnet/teamforge/dist -
Dapp.log=/opt/collabnet/teamforge/log"

Start the ETL service.
Run this command to make sure that you see the updated memory setting in the command output.
```
ps -ef | grep etl | grep -v jboss
```
Run the ETL job again.

How to redo the entire XML to non-XML conversion if required for some reason?

Workaround 1: Bootstrap the reporting service and execute the tracker initial job. As XML to non-XML conversion is part of initial job as well, this would redo the conversion process.

Workaround 2: Truncate the relevant tables and reset the previously processed artifact flex field key to 0. This would reinitiate the XML to non-XML conversion while executing the tracker incremental job.

Relevant SQL process:

truncate table stage_flex_fields_bridge;
truncate table text_flex_fields_bridge;
truncate table user_flex_fields_bridge;
truncate table ms_flex_fields_bridge;
truncate table ss_flex_fields_bridge;
truncate table date_flex_fields_bridge;
truncate table bad_xml_records;

update etl_attributes set attribute_value = 0 where attribute_name = 'PREV_RUN_ARTIFACT_FLEX_FIELDS_KEY' ;

commit;