How to find the Infomix table name used by Sage Line 500

For years I’ve had trouble finding the correct Informix table (name) used by Sage Line 500’s Data Dictionary – until I discovered this nifty trick:

Note:  Requires one to have Demo/Test company installed – accessing a database differently named to the live/production db.  I work in a SuSE Linux Enterpise Server (SLES) environment.

1.  Access the desired Sage screen/window.  (e.g.   if you’d like to know which table stores Sales Order Processing order detail – then go into any example order’s detail screen/window). 

2.  Open another session, log in as Infomix and run ‘onstat  -g sql’.  This will show all current database(s) threads.  Most of these will (might) relate to the production db’s processes – so look for the process with the Demo/Test db’s name in it.  Make a note of the process number (integer number on the left).

3.  As Informix, run ‘onstat -g sql <process number>’  (e.g.  ‘onstat -g sql 16342’) and look for the table name in the output (usually after ‘from’). 

Let me know if problems!

Comment (2)

  • shaun| August 25, 2011

    Besides the data dictionary or reading the data directly out the cisam table. An even better way is to see what Sage do when doing a particular process! Set an environmental variable ISQLLOG to a filename to output a log of what Sage do. You set this before going into Sage e.g. if using BASH -> “export ISQLLOG=mysqltrace.log”. Also you can get your process number by starting a second session and looking up the first session’s process from the user activity report under system manager.

    • Hannes| August 26, 2011

      Hi Shaun!

      ISQLLOG is awesome – thanks for introducing me to it! Wish I knew about this a long time ago.

      I hear you on the data dictionary – but how would you pick up (just by looking at it) that (say) porecp2m is used instead of porecpm?

      How does one read directly from the cisam table?

      Re user activity: in my environment, I unfortunately only see the Operating System (Linux) process number. Not sure if one can change this (?)

      Thank you for your post!