Updating LASR table from SAS VA Stored Process with and without IMSTAT

[TedP]

I am interested in finding out what the standard operating flow is for deploying a SAS VA app to many hundreds (possibly thousands) of users who have the option of utilizing a stored process within the app. This stored process allows the user to make edits to the underlying LASR table. I would obviously like the user to see the changes immediately on SAS VA.

It seems that this is exactly what proc IMSTAT was built for but currently we don't have the license for it.

Below are my assumptions and questions for the process flow both with and without proc imstat. Please correct any wrong assumptions in the flows below and answer questions if possible.

Flow With Proc IMSTAT

1. Stored process gets kicked off.

2. IMSTAT does the updates. Updates should happen extremely fast (as fast as VA makes interactions in filters - which has been extremely fast for our 4 million row LASR table)

3. Done 

Questions with Proc IMSTAT

1. Since therecould be many concurrent updates happening at the same time, how does proc IMSTAT deal with updates happening at the same time? Is there locking/waiting available? 

2. What happens when we want to replace the entire LASR table (because of some new source data not from the stored process)? Is there always going to be down time when this replacement is made because the old LASR table must be deleted?

Flow without Proc IMSTAT

1. Since I can't make updates directly to the table (outside of appends), I would have to delete the current LASR table.

2. Then I would make updates to the LASR table outside of the LASR server which would be very slow on a 4 million row table. Would have to lock the table to do updates one request at a time.

3. Reload table into LASR which is also slow

4. All users would experience report downtime during this time.

5. This should be done only seldomly - perhaps once a day to avoid report downtime.

Questions without Proc IMSTAT

1. This is not a feasible solution correct? Or are there workarounds?

Thanks for any help provided.

[MikeMcKiernan]

The IMSTAT procedure can help with most of what you identify.  I don't know many details of licensing, but check whether you can use the data and server management statements for the IMSTAT procedure, such as the TABLE and UPDATE statements.

For your "Flow with PROC IMSTAT" comments:

• Your three items are accurate predictions, it should be very fast. I’d also suggest a SAVE statement or some other means to make a copy for reload.
• For the questions:
• Q1—there is table-level locking. Requests for data that are already in-flight will see the old data. After the UPDATE request is complete, everyone sees new data.
• Q2—There will be down time while the in-memory table is dropped and the replacement is loaded.

For the "Flow without PROC IMSTAT", consider reording the steps:

1) Update the “original” data (SAS data set, Oracle, other data source) first.

2) DROPTABLE the in-memory table and then PROC LASR ADD from the updated replacement

Users will experience downtime between the DROPTABLE and ADD.

Some of what I wrote assumes a distributed server.  Let me know what I can clarify, and I'm really hoping that you have the IMSTAT procedure and find it useful.