Lesson Learned: What Table is this?

  • 6 October 2021
  • 4 replies
  • 214 views

Userlevel 7
Badge +18

It's often useful to know which table contains the data you're seeing in the system. This is helpful both for knowing where to get data for a report and for knowing which LU's API to call to make a change.

IFS Enterprise Explorer has the System Info tab for this, but this doesn't always show these details, particularly under the Solution Manager.

What we can do instead is make a change inside the application--in the relevant screen and field that we want to learn about--then look inside the database to see what recently got changed.


Step 1:

BEGIN
   dbms_stats.flush_database_monitoring_info;
END;
/

 

Step2:

Make your change in the application.

 

Step 3:

-- (Again)
BEGIN
   dbms_stats.flush_database_monitoring_info;
END;
/

 

Step 4:

-- As app owner, e.g. IFSAPP
SELECT *
 FROM user_tab_modifications
WHERE timestamp = (SELECT MAX(timestamp) FROM user_tab_modifications);

 

Between steps 1 and 3, you'll want to work quickly to ensure only your changes get captured, either in a nonproduction environment or during off-hours when other users aren't making concurrent changes. Note that changes made by background jobs will also get captured.


This topic has been closed for comments

4 replies

Userlevel 7
Badge +21

Hi @durette ,

 

Great information I think it will be even more important as we move to Aurena and projections.  We have quite a lot of custom packages we’ve developed to enhance IFS which we call from custom events and actions.  Knowing where data is stored and the API’s to use to read, create, update and delete data is very important when we create our custom events, actions and packages.

 

Regards,

William Klotz

Userlevel 6
Badge +12

@durette,

Thanks for the great information! Useful as always!

I have found that another good way of seeing where data is coming from, going to, and what methods are in play is the Aurena log. It seems to contain more information, even in spots where the IEE System Info and debugger come up empty. Unfortunately, Aurena doesn’t always have the same screens and flow as IEE.

Also, nothing can beat going all the way back to the source (database) to see what is going on. Wonderful! Thanks again!

 

Joe Kaufman

Userlevel 6
Badge +12

@durette,

Also, (to make sure I am doing this right), the DBMS_STATS part appears to require DBA privileges? When I try it from App Owner I get:

Error starting at line : 7 in command -
BEGIN
dbms_stats.flush_database_monitoring_info;
END;
Error report -
ORA-20000: Insufficient privileges
ORA-06512: at "SYS.DBMS_STATS", line 10348
ORA-06512: at "SYS.DBMS_STATS", line 43437
ORA-06512: at line 2
20000. 00000 - "%s"
*Cause: The stored procedure 'raise_application_error'
was called which causes this error to be generated.
*Action: Correct the problem as described in the error message or contact
the application administrator or DBA for more information.

It looks like the flush just fills in timestamp -- is that right? Because I was able to make a change in IEE then see the table affected in USER_TAB_MODIFICATIONS. It just had a null in timestamp. But when using am environment where no one else is in it, it still worked to find the table that got used, because everything else had timestamp filled in…

 

Thanks,

Joe Kaufman

Userlevel 7
Badge +18

It looks like the flush just fills in timestamp -- is that right? Because I was able to make a change in IEE then see the table affected in USER_TAB_MODIFICATIONS. It just had a null in timestamp. But when using am environment where no one else is in it, it still worked to find the table that got used, because everything else had timestamp filled in…

Forcing a flush before and after narrows the time window and improves the signal-to-noise ratio. If your system isn’t getting hit by constant background jobs, then that could be an optional step.