Creating a System tab on a Custom Object

The information contained on the out-of-box System tab can be very useful for administrators and power users. Here is how to extend a System tab to a custom object:

Add the following fields to your business object using Find:

• triLanguageLI
• triFormLabelSY
• triBusinessObjectLabelSY
• triRecordNameSY
• triProjectNameSY
• triRecordStateSY
• triCreatedSY
• triModifiedByTX
• triModifiedSY

I also like to include spec_id which is:
triRecordIdSY

For the Modified By field to work, you will have to perform a few additional steps:

• Create a new association to the triPeople module, triPeople business object with an association string of Auto Recorded By and a reverse association of Auto Recorded.
• Set the triModifiedByTX field to be a locator field. Locate using triPeople, Auto Recorded By and map to triNameTX
• If your object has a sub action that calls xxBusinessObjectName - Synchronous - Permanent Save Validation, it will call the xxBusinessObjectName - Synchronous - Module level business rules workflow which will update the Modified By field. If not, you will need to write workflows to update this field on any given state transition

Publish the Business Object then open the GUI builder. Create a new tab called xxSystem and a new section under that tab called xxRecordInformation. Add the above fields to the layout as shown below and Publish the GUI.








If you are using triModifiedByTX, don’t forget to include a locator query on the Modified By field.

How to view long-running SQL Processes with Oracle 10g Enterprise Manager

To see the log running queries:

• Open the Oracle Enterprise Manager 10g Web-interface (typically found at http://DBServer:1158/em/console)
• Click on the Performance Tab
• At the bottom of the page, there is a Top Activity link
• In that page, there will be a Top SQL link. If you click on the SQL ID, it will bring up the statement that was executed.
• If it is a select, there will be a Plan tab, it will show how expensive the statement is.
• If it looks like a TRIRIGA query, either coming from a manager, portal, or GUI query section, the query should be refactored and/or redesigned to allow for better performance.
• The report can normally be easily identified by the tables or Module-views it selects from, so for example, if the statement is question looks like:

SELECT ROW1, ROW2, ROW3

FROM T_TRIBUSINESSOBJECT

WHERE ROW1 like ':1'

• Look in the report manager for a report/query/graph, etc, that is looking at the TRIBusinessObject BO.
• Test different reports by opening the report, and clicking the "Export SQL" link. The sql can be verified against what Oracle is reporting back

For more information on how to trace long SQL from long running queries, please refer to my earlier post: http://www.tririga.info/2008/01/analyzing-long-running-sql-queries.html

Gathering Database Statistics

In order to improve performance TRIRIGA recommends gathering database statistics on a daily basis. Normally this is done as part of the daily cleanup jobs. If you notice poor system performance on a day with particularly heavy usage or during a data load you can manually gather statistics by running the following scripts.

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('TRIDATA',DBMS_STATS.AUTO_SAMPLE_SIZE);

If 2.1.9.X is being used, also run:
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('TRIRIGA',DBMS_STATS.AUTO_SAMPLE_SIZE);

Capturing Traffic Between the App and Database Servers

In order to troubleshoot issues you are having you may want to capture the network traffic between your application and database servers. This will allow you to see SQL statements being passed to the database, along with a lot of other potentially useful information. The basic procedure is outlined below (thanks to Eric Glass):

Here are some general instructions for capturing traffic sent from the application server to the database server:

1) Stop the application server.

2) Set up a remote SSH tunnel on any free port on the application server (e.g., 9999), pointing to the database host/port. The exact terminology used for this varies by SSH client, but typically it would be:

Source Port: 9999
Destination Host: database server
Destination Port: database port (typically 1521 for Oracle, 1433 for SQL server)

For Putty, the required settings can be found in the connection configuration dialog under SSH -> Tunnels (source port would be "9999", destination would be of the form "host:port", e.g. "mydbserver:1521"). For OpenSSH, the command line parameter would be "-R 9999:mydbserver:1521".

3) Make the SSH connection. You will need to leave the shell window open for the duration for the tunnel to remain open.

4) Start Wireshark. Go to Capture -> Interfaces; click "Options" for the interface you want to capture. In the Capture Options dialog, enter the following in the Capture Filter:

host mydbserver && port 1521

(substituting the appropriate database server host/port). Ensure "Update list of packets in real time" is checked. Click "Start" to start capturing.

5) Test the tunnel; make another SSH connection to the application server, and type:

telnet localhost 9999

This will open a connection to the remote end of the tunnel; traffic will be routed over the SSH connection, and an outgoing connection will be made from your client box to the database server on the destination port. You should see the outgoing connection traffic in Wireshark. Close the second SSH window and restart capturing in Wireshark (Capture -> Restart from the menu). Alternatively, you can revise the capture filter at this point to also include web traffic from the browser to the application server:

(host mydbserver && port 1521) || (host appserver && port 80)

6) Modify the application server JDBC configuration to point to port 9999 on 127.0.0.1. The application server will be connecting to the tunnel source port locally, which will spawn an outgoing connection from your client box to the database server (which will be captured).

7) Restart the application server and verify that traffic is received in Wireshark.

8) Restart capturing in Wireshark, perform the desired operation in the application, then stop capturing in Wireshark. This will ensure you get a concise capture with minimal "noise" from other application activity.

9) Save the capture file, and reset your configuration to normal.

Note that technically, you don't have to establish the SSH tunnel directly to the application server; any SSH server will work (i.e. a Unix box completely unrelated to the application system). You would need to ensure the source end of the tunnel on the SSH server accepts connections from other hosts (typically an option in the tunnel configuration settings); you would then point the application server to that box instead of 127.0.0.1.

Once you have the capture, there are a variety of tools Wireshark provides to analyze the capture. For example, select Statistics -> Conversations from the Wireshark menu and click the "TCP" tab; this will show all TCP connections active during the capture period. You can start examining these by selecting an entry from the list, right clicking, and selecting "Apply as Filter -> Selected -> A<->B" from the popup menu. This will filter the packet window to display only that connection. You can also view or save all the traffic for a connection as text by selecting a packet in the list and choosing "Follow TCP Stream". For many protocols, Wireshark includes dissectors which will parse out the traffic and present it semantically (for example, it can decode HTTP traffic and display request/response information). I don't believe there is currently a dissector for Oracle, but you can view the raw TCP streams to see the SQL statements going over the wire

Identifying Linked Excel Cells

If you need to modify an Excel spreadsheet that has cells linked to TRIRIGA, it is important to identify which cells are linked. Inserting rows into the spreadsheet will not change the original linked locations so this will result in significant remapping.

Excel linked cells are stored in the following Table ‘SOBJTYPE_EXCEL_PARAMS’. The spec_template_id column is actually the GUI_ID that the excel template is embedded in, not the business object’s spec_template_id. To look up the GUI_ID look up the GUI name in the following Table ‘GUI_FORMS’. This will give you the spec_template_id to use when finding all the excel links.

Use this script and update the spec_template_id to match the GUI you need to pull the cell links from. In this example it is the ‘triRETransactionLeaseTerms’ GUI.

Select EXCEL_ROW, EXCEL_COL, PARAM_ID, PARAM_STR, PARAM_DISP_STR, ROW_NUM TABLE_VIEW_FLAG from SOBJTYPE_EXCEL_PARAMS where spec_template_id = 10003842;

Capturing HTML Packets

While troubleshooting TRIRIGA, you may find yourself needing to analyze the traffic between a workstation and the server or the traffic between your app/process server and the database server. Here are the basic instructions for using the open source application Wireshark to capture this network traffic.

Download and install Wireshark from here:

http://prdownloads.sourceforge.net/wireshark/wireshark-setup-1.0.0.exe

After you get it installed, start it up; under the menu go to "Capture -> Interfaces". It should pop up a dialog showing all the network interfaces on the box; generate some traffic (open a web page etc.) and the "Packets" column should start counting up. That's the interface you want to sniff. From that dialog, click the "Options" button next to the interface that you identified. In the "Capture Filter" field of the options dialog, enter the following:

host ServerName && (port 80 || port 443 || port 8080 || port 8443 || port 8001)

(assuming ServerName is the server you want to monitor). Then click start. Go perform whatever operation you need to trace. Then select "Capture -> Stop" from the menu when it's done. Then save the result (pcap format should be the default, which is correct).

Multi-Line Field Labels

In the GUI, if you have a long label text it pushes the other fields in the section out to the same length which can make the layout look bad. For example, in the image below the "Please enter the Estimated Retirement Date" field label is too long.


To solve this issue an break sequence can be added to split the label to multiple lines as shown below.


Here are the steps:

1. In tririgaweb.properties, remove "<", ">" and "&" from the EXCLUDE_CHARACTERS line. It should look like this: EXCLUDE_CHARACTERS={
2. Save tririgaweb.properties. Make the same changes on your process server, if applicable
3. Restart Jboss, Weblogic or WebShpere on all app and process servers
4. In the GUI Builder, Revise the GUI, select the field and edit the label as shown below by inserting a break sequence where you want to split the label
5. Apply the changes and Publish the GUI