Let us look at cache management in a clustered environment.
Consider a scenario in which I have two BI servers in a cluster – server A and server B and I want seed the cache for users who have different data visibility. A common approach to seed the cache is to use ibots. You could run an ibot as an administrator and add the users as the recipients to seed their cache. However, in this case because of the data-level security restrictions, when you try to seed the cache for a particular user you might run into cache-seeding errors. Only users with administrator privileges in the RPD can seed the cache using ibots.
Another issue is that a request scheduled using ibots may be assigned to any one of the clustered BI servers. Even if a cache entry is made, it will exist only in the node that processed the request. As a result, we will see a cache hit only if a query gets directed to the same BI server which has the cache entry.
A workaround to these issues is to use the NQCMD command line utility to seed the cache on all the clustered BI servers. NQCMD needs to be run on all the nodes separately to seed the cache.
First we need to create a non clustered DSN entry in all the nodes in the cluster.
First step would be to purge the cache in all the nodes in the cluster.
Purging all Cache:
Open a text file and enter the following text:
Call SAPurgeAllCache()
Save this file as purgecompletecache.txt
Open another text file and enter the following:
nqcmd -d non_cluster_DSN -u Administrator -p Administrator -s purgecompletecache.txt
Save this file as purgecache.bat
When you execute purgecache.bat all the cache entries will be purged.
Purging Cache for a particular table:
Save the following in a text file:
Call SAPurgeCacheByTable(‘DBNAME’,'CATALOG’,'SCHEMA’,'TABLENAME’)
Call this file in the batch file and execution of the batch file will clear all the cache entries associated with the physical table TABLENAME. Note that the catalog, schema and tablename are the values in OBIEE and not the name in the database. If the CATALOG value is null then use ”.
Repeat this process on all the nodes and verify that the cache entries have been purged.
The next step is to seed the cache for the different users. The following steps outline the steps involved in this:
Seeding the cache:
Copy the Logical SQL for the request you want to cache from cache manager and paste it into a notepad file.
I have named this notepad file: sql_1.
Now open another notepad file and enter the following text:
nqcmd -d non_cluster_DSN -u EMP4 -p EMP4 -s sql_1.txt -o output.txt
You should use the username and password of a user who has the same data visibility as the users whose cache you want to seed.
Save this file with a .bat extension. I have named it seedcache.bat.
When you run this windows batch file, the the following events occur:
1. call the nqcmd utilty
2. Log into OBIEE server using the non-clustered DSN with username: EMP4 and password:EMP4
3. Execute the logical sql in file sql_1.txt
4. Write the output in file: output.txt.
Execute this batch file in every node of your cluster and verify that a cache entry has been made in your RPD for user : EMP4
Posts
