| Author |
Message
|
| priyesh |
 Posted: Thu Jul 09, 2015 10:44 pm Post subject: ESQL Cache |
 |
|
Newbie
Joined: 11 Jun 2015
Posts: 4
|
Hi All,
I got a scenario like we can use Shared variables as bellow...
CREATE PROCEDURE getCity_v02 (IN airportCode CHARACTER) RETURNS CHARACTER
BEGIN
-- v02: "ESQL" cache
-- PERFORMANCE TEST ONLY! No ATOMIC blocks.
-- Do not use if Additional Instances > 0.
IF FIELDNAME(CACHE.*[1]) IS NULL THEN
-- load the cache
DECLARE TEMPCACHE ROW;
SET TEMPCACHE.AIRPORT[] = SELECT A.CODE, A.CITY
FROM Database.AIRPORTS AS A;
FOR cacheline AS TEMPCACHE.AIRPORT[] DO
CREATE LASTCHILD OF CACHE NAME cacheline.CODE
VALUE cacheline.CITY;
END FOR;
END IF;
RETURN CACHE.{airportCode};
END;
..............................................
But here I want to put a record as value in place of above statement in bold as I have to store complete records of database . Can I ?....
If possible then please guide me...
Thanks  |
|
| Back to top |
|
 |
| Vitor |
| Posted: Fri Jul 10, 2015 4:14 am Post subject: Re: ESQL Cache |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| priyesh wrote: |
| But here I want to put a record as value in place of above statement in bold as I have to store complete records of database . Can I ?.... |
Yes, you can insert a row into a database from ESQL.
| priyesh wrote: |
| If possible then please guide me... |
I'd consider the INSERT statement. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| priyesh |
| Posted: Fri Jul 10, 2015 5:51 am Post subject: |
|
|
Newbie
Joined: 11 Jun 2015
Posts: 4
|
Sorry Vitor but I want to store in chache not in database
My task is to retrieve the records from database and store it in chache.
| Quote: |
CREATE LASTCHILD OF CACHE NAME cacheline.CODE
VALUE cacheline.CITY; |
I want to store a record as key and value pair , taking primary key value as key and complete record as value
so can I ???
Thanks..!!!!!!  |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jul 10, 2015 6:06 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| priyesh wrote: |
| Sorry Vitor but I want to store in chache not in database |
Ah, I was confused by your reference to Shared Variables and reading your post on only 1 cup of coffee. My bad. 
Yes, you can store key value pairs in the IIB Global Cache. The cache is only accessible from Java so you'll need to write a couple of Java methods to store and retrieve the values (and this Java is so simple even I've managed to do it!) and then reference the Java from the ESQL as usual.
Works very well.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Jul 10, 2015 6:20 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Vitor wrote: |
Yes, you can store key value pairs in the IIB Global Cache. The cache is only accessible from Java so you'll need to write a couple of Java methods to store and retrieve the values (and this Java is so simple even I've managed to do it!) and then reference the Java from the ESQL as usual.
Works very well. |
The searching for data in the Global Cache (As supplied with IIB) is IMHO its big Achillies heel.
I did a POC where I loaded up the cache with a number of DB tables that contained almost static data (such as Component Suppliers) but the overhead of searching for one supplier was far, far worse than getting it with a DB table read (without locks).
I'm yet to be convinced that the Global Cache is worth using but your application might lead to different results.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jul 10, 2015 6:43 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| smdavies99 wrote: |
| I'm yet to be convinced that the Global Cache is worth using but your application might lead to different results. |
To be clear, when I said "works very well" I meant having to drop into Java from ESQL to access the cache.
I'm sure that there are any number of use cases where a DB lookup remains preferable to using the cache, especially when the DB itself is caching the data (e.g. where you repeatedly read the same tables of static data).
Where we've found it useful is to store things like session ids and status data so as to facilitate breaking synchronous calls into asynchronous. You could of course use a DB for this, but the cache is convenient, self cleaning (with the time to live set appropriately) and doesn't require huge numbers of database request forms.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| maurito |
| Posted: Mon Jul 13, 2015 12:32 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
|
| Back to top |
|
|
| lfrestrepog |
| Posted: Wed Jul 15, 2015 2:38 am Post subject: |
|
|
Novice
Joined: 08 Jul 2014
Posts: 22
|
Hi...
You can also add the whole cacheline to the cache, something like this:
| Code: |
CREATE PROCEDURE getCity_v02 (IN airportCode CHARACTER) RETURNS CHARACTER
BEGIN
-- v02: "ESQL" cache
-- PERFORMANCE TEST ONLY! No ATOMIC blocks.
-- Do not use if Additional Instances > 0.
IF FIELDNAME(CACHE.*[1]) IS NULL THEN
-- load the cache
DECLARE TEMPCACHE ROW;
SET TEMPCACHE.AIRPORT[] = SELECT A.CODE, A.CITY FROM Database.AIRPORTS AS A;
FOR cacheline AS TEMPCACHE.AIRPORT[] DO
CREATE LASTCHILD OF CACHE FROM cacheline;
END FOR;
END IF;
RETURN CACHE.{airportCode};
END;
|
Haven't tried myself, but I believe it should work just fine.
_________________
--
Luis Fernando Restrepo Gutiérrez |
|
| Back to top |
|
|
|
|
