You can see when a user logged in and logged out using USAGE_HISTORY with the user information being in NODETABLE and PROPERTYTABLE.

But you cannot determine when or which objects are accessed/viewed. That information is not stored in the database. Given the login/logout date/time values, you could search CHANGE_HISTORY and ITEM_HISTORY to see what activities (if any) the user performed, but these will only be create/modify/delete actions, not what they looked at.

As for what a user looked at or accessed in attachments, the codes that Keith provided are for CHANGE_HISTORY. The codes for ITEM_HISTORY.ACTION would be : 0 – Print Attachment, 1 – Get Attachment, 13 – View Attachment, 15 – Get File Attachment, 16 – Get Image Attachment, 21 – Checkout Image Attachment, 22 – Checkout File Attachment, 29 – View File Attachment, 30 – View Image Attachment, 36 – Open Attachment.

What user account is the PX running as??? Does that user have all needed user administrator privileges and roles?

And I agree with Raj, make darn sure you always add default roles to the user before you make them active.

At 17GB, something is definitely wrong.  Interesting that the AGILE_DATA2 tablespace file is so large in comparison to all the others (26GB vs. 2GB for all others). Of course, that is where CHANGE_HISTORY is located. But also PROPERTYTABLE, ATTACHMENTS, ITEM, NOTIFICATION and NOTIFICATION_FIELDS. That is 2 (CH and NF) out of the 4 tables that usually take the most time during import or export (also ITEM_HISTORY and A_DW_TXN_LOG).

As Keith suggested, try using DataPump and also truncate the A_DW_TXN_LOG table. Also get a count of records from CHANGE_HISTORY, see how large it is. And get a count of records by class from CHANGE_HISTORY (select class_id, count(*) from change_history group by class_id order by class_id). It might be interesting to see if you are getting some weird records in there (like a lot of PDP records or null records). Right now, I think that is a good place to start looking.

You stated “server upgrade”. Was this the application server (as in, an O/S change), or an upgrade to Agile itself?

What version of Agile are you running, before and after?  If you were running something previous to 9.3.4.0 and are now running 9.3.4.0 or later, you could be having issues with the web services stack used by Agile, which changed from AXIS to JAX-WS.   As such, it *will* require changes to be made to your PXs, but only if they use web services – normal PXs that simply call the SDK API should be fine.

Pros :

1. Your application will be under official support for much longer – at least to 2025, IIRC.
2. You will have some added functionality and a number of bug fixes. And more as they are found. Extra functionality would be for PPM (Calendar and GANNT), some Design Change Order updates and content authoring improvements.
3. Your version of Java that you use (8.0.xx) will be MUCH more secure and can be upgraded as needed.

Cons:

1. It is going to cost you, both in terms of the upgrade itself and in testing everything to make sure it all still works.
2. If you use web services in your PXs, it will also cost you to migrate from the AXIS framework to JAX-WS, as 9.3.4.0 requires you to use JAX-WS.

I am certain there are others, but these are the ones that come to mind.

What is shown in the Workflow tab comes from the SIGNOFF and WORKFLOW_PROCESS tables. WORKFLOW_PROCESS shows the stages of the workflow that is being used by the change, and how things can progress (although not completely). SIGNOFF shows all of the folks that have commented on/approved/rejected things during the workflow processing.

CHANGE.PROCESS_ID has the ID from WORKFLOW_PROCESS that is tied to the current status of the change, where CHANGE.ID = WORKFLOW_PROCESS.CHANGE_ID and CHANGE.WORKFLOW_ID = WORKFLOW_PROCESS.WORKFLOW_ID.

SIGNOFF lists who/when someone signed off on the workflow, as well as what their response was and if it was required, where CHANGE.ID – SIGNOFF.CHANGE_ID and SIGNOFF.PROCESS_ID = WORKFLOW_PROCESS.ID (showing which status this happened at). In addition, SIGNOFF.HISTORY_ID links to the CHANGE_HISTORY record showing what happened.

There can be MANY duplicate records between the 4 tables involved here, and it is not an easy task to figure out what should be there. But if you get the ordering correct, and only use the set of WORKFLOW_PROCESS records that include the ID in CHANGE.PROCESS_ID for each change, you should be good. A lot of things you will get from NODETABLE, like status values, the workflow name and the signoff_status. However, signoff_status also uses some hard-coded values 1-10 that are NOT in a list but merely defined (list “Action List” shows most of them, but not all).

I start with the following query :

SELECT ID, WORKFLOW_ID, STATE, CHANGED_BY, CHANGED_ON, LOCAL_DATE, STATE_CODE, CHANGED_BY_USERNAME, STATUSTYPE
FROM WORKFLOW_PROCESS WHERE CHANGE_ID = chngID
 ORDER BY CHANGED_ON DESC, ORDER_BY ASC;

And then for each workflow record returned that is not a duplicate (see below), check to see what SIGNOFF and CHANGE_HISTORY have for that record :

SELECT A.ID, A.USER_NAME_ASSIGNED, A.REQUIRED, A.Signoff_STATUS, A.USER_NAME_SIGNED,
       TO_Char(null), TO_Char(null), TO_Char(null), TO_Char(null), TO_Char(null)
  FROM Signoff A, CHANGE_HISTORY B
 WHERE A.PROCESS_ID = wfpID AND (A.Signoff_HISTORYID IS NULL OR A.Signoff_HISTORYID = 0) AND A.HISTORY_ID = B.ID
   AND A.Signoff_STATUS NOT IN (6, 9)
   AND (A.REQUIRED = 1 OR A.REQUIRED = 2 OR A.REQUIRED = 5 OR A.REQUIRED = 6 OR A.REQUIRED = 13 OR A.REQUIRED=14 OR A.REQUIRED=15)
UNION ALL
SELECT A.ID, A.USER_NAME_ASSIGNED, A.REQUIRED, A.Signoff_STATUS, A.USER_NAME_SIGNED,
       C.COMMENTS, TO_Char(B.TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'), TO_Char(B.LOCAL_DATE, 'YYYY-MM-DD HH24:MI:SS'),
TO_Char(C.TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'), TO_Char(C.LOCAL_DATE, 'YYYY-MM-DD HH24:MI:SS')
  FROM Signoff A, CHANGE_HISTORY B, CHANGE_HISTORY C
 WHERE A.PROCESS_ID = wfpID AND (A.Signoff_HISTORYID IS not NULL AND A.Signoff_HISTORYID != 0)
   AND A.HISTORY_ID = B.ID AND A.Signoff_HISTORYID = C.ID AND A.Signoff_STATUS NOT IN (6, 9)
   AND (A.REQUIRED = 1 OR A.REQUIRED = 2 OR A.REQUIRED = 5 OR A.REQUIRED = 6 OR A.REQUIRED = 13 OR A.REQUIRED=14 OR A.REQUIRED=15)
ORDER BY 4, 3, 2;

You also need to make sure you have no duplicates using the first query over workflow_id, changed_by and changed_on, as well as making sure that state_code is an even number (0=Current process, 2=Process moved Forward, 4=Skipped – look at “Status Code List”). The list for status type is “Status Type Selection”, and the list for Required seems to be odd = “Yes” and even = “No” (I suspect that these are again constants somewhere).

Bottom line, this can be a hot mess with lots of things that are not easily found. and getting both the ordering and inclusion correct isn’t easy. What I have shown above is where I am in extracting workflow history (basic history tab data is easy in comparison). I am still working on getting it to 100%, but keep finding weird stuff.

Does it need to be done all at once, or a bit at a time?

If you want to extract smaller sets of data, then an SDK app could be the way to go. If you need the entire set of data all together, using PL/SQL would probably be more efficient. You can do the smaller data sets using PL/SQL scripts as well, but you have to have a good criteria by which to then pick and choose the objects you need at that time.

 I am very familiar with the Agile schema, and I have a set of scripts that are intended to extract data into tables approximating the load tables. From there, it can be reformatted and output into the format(s) you need.

If you are running any version before 936 RUP4, you need to get the current value(s) and append the new value to them.

Import up to 936 RUP4 assumes that you provide everything that should be in an attribute, and so it replaces A,B,C with what you give it – D. And yes, if there are existing values, it should not red-line them, but it does. Except for initial data loading, I advise folks to avoid processing multi-list attributes with Import.

User data is stored in NODETABLE, where parentid = 5005. You can get the ID and full name (description) from there. The user email will then be in PROPERTYTABLE, where PROPERTYID=46. The login of the user is then in PROPERTYTABLE where PROPERTYID=29.

 Look in the MSATT table.

 The problem is that the PRODUCT_LINES attribute can only hold so many ID values (it is 255 characters). So if you have a lot of ID values to put in there (and it certainly looks like you do), then there has to be another way to handle the overflow. And the MSATT table is it. PARENTID is the ID from the ITEM table, ATTID is the ID of the attribute (1004 for ITEM Product_Lines, IIRC), and VALUE is an ID for that attribute.

 MSATT should have the entire list of IDs for the attribute. And that you should use MSATT is noted as a flag bit, which is the 10th bit (starting from zero) in the FLAGS attribute. So if that bit is 1, just go to MSATT to get the full list of ID values for the attribute.