What on earth is Smart Cache?
Though we never SQL data in to the database, some of you may have come across instances where you are err… directly updating the database with your favourite tool, and yet nothing changes in Smart Office. I never ran across this with DB2 on an AS400, only Windows with SQL Server so far.
Several months ago, when making some significant changes to some existing processes for one of my customers we had need to mass update records as we were embarking on testing. The interesting thing that we came across was, we would execute the SQL statement and we would then looking in Smart Office to verify the data had indeed changed. It didn’t, we’d have the bounce the environment to get M3 to recognise the changes.
As part of this change, we were also going from SES003 to SES400 security, if you look in Companion it explicitly tells you to run a couple of queries directly against the database. Dutifully, this task was executed, however the changes again didn’t appear to be reflected in Smart Office or the M3BE.
I decided to do some digging, and I found something interesting.
In LCM, M3BE application -> Manage Application -> Tools we have Smart Cache
Clicking on Smart Cache we see some interesting information
And most importantly, a Clear button.
After running my SQL statements against the database, I found that I could hit this clear button and Smart Office would now display the updated values.
I did try to track down specifically what it did in InforXtreme, but aside from incidents logged, there wasn’t really much interesting.
Eitherway, if you’re in the situation where you need to manipulate data directly and the BE doesn’t recognise the changes – clearing the Smart Cache is the first place I’d look.
Potato IT 
Nice tip! And Yes, this is a known fact for most of us that have done SQL updates on SQL Server instances. 🙂
Hi Scott. Thank you. It’s good to know. Although, I am surprised you are doing SQL updates. It is known that SQL INSERT/UPDATE/DELETE is not allowed for many reasons: risk of cache collision (your case), affects integrity, impacts performance, and voids the warranty. The safest would be to shut down M3 prior to SQL updates. Also, I am surprised about the M3 Security and Companion that you say recommends SQL updates; I wouldn’t trust that it’s up-to-date.
As most long term customers will have experienced, there are many instances where support, Infor staff, or in this case documentation have required you to update the database directly. As M3 has evolved, certainly for IFL, the need for data manipulation against the database has decreased significantly. The vast majority of data corrections have involved correcting data which was written in earlier versions of M3 causing odd behaviour in new code.
Direct manipulation of the database is something that shouldn’t be done flippantly, but when we do, we have to be realistic about the steps that we take to mitigate risk. Those mitigation steps depend entirely on the scenario.
Example: Clearing Smart Cache when testing the changes in test environments makes a lot more sense than requesting that an environment be bounced – which if you don’t run M3 inhouse, may be many hours later.
Hi Scott,
Before all, congrats you for this great bloog.
It is possible to disable smart cashe only for some table, or disable at all in LCM in “Configure Application” option “transaction.cache.readonly”, you can put the name of table(s) that you want to be disable.
Hi Scott,
Thanks for the information, One thing I need to know If we updating CMNFNG(MNS110) for Auth.req to set it as ‘1’ in the DB using SQL update, Do we need to clear smart cache for that also.
Thanks,
Hi Jeff,
yes, that was one of the settings that prompted the search for what was going on.
If you weren’t already aware, there is a setting on the M3BE that you need to change through LCM to switch from SES003 security to SES400.
And I found that SES990 took a while to regenerate the authorities, so be patient – you can monitor its activity by checking the autojob subsystem.
Cheers,
Scott
Hi Scott,
Thanks for the reply,SES990 is for the manual update of ASJ for SES400/SES401right?
I’m assuming we don’t need to run SES990 manually if SES900 doing the job,
And one other thing if you can answer please do so..If we want to attach more than 1000 functions to a single role with 100 users, will it affect to the performance of Auto job.
Thanks
Hi Jeff,
yes, I believe SES990 will force the update immediately.
From what I have observed, the autojob essentially goes out and calculates the appropriate access for the users and then writes it to CMNPUS (you can see the generated rights in SES401).
So complex assignments will likely take a while.
At IFL we created our roles and then assigned the users. Once we were ready for the cut over in PRD we update the programs so they required the Auth Required (bounced the BE) and then manually kicked off the SES990 – this took several minutes to complete.
Even when we were tweaking the settings later and letting the autojob chug away, the autojob still took several minutes to complete.
Cheers,
Scott
Hi Scott,
Thanks for the reply…I got the Idea. When you running SES990, it says SES900 should be stop. Is that Correct or you guys have done it.
Thanks
It was quite a while ago that we did this, so I can’t remember the specifics sorry.
If it is recommending SES900 should be stopped, it’s probably a good idea. As SES990 takes a long time to run, it would make sense that SES900 would wake up during the process.
Does anyone know why would you enable smart cache in a production M3 system? I have been told this is a debugging type tool.
It’s a cache.
I’ve only seen it enabled on sites that run M3 on Microsoft SQL Server – likely to aid with performance. If it’s not enabled, I wouldn’t without guidance from support.
Cheers,
Scott
M3 caches data frequently read like calendars, users etc into memory and reads directly from that when the record needed is available in the cache. That gives a massive improvement to read speeds from inside M3 especially in a Windows environment meaning it should always be turned on. Updates through SQL is a slightly different story and as previously mentioned it should never be used. Apart from the above mentioned reasons it also shortcuts the M3 Event system meaning anything utilizing that to keep Db in sync, trigger messages etc will miss the SQL updates.
It should always be turned on? Even for other platforms like the AS400?
I’ve checked a few AS400 sites that have been upgraded to 13.x and Smart Cache isn’t on.