Aviod Calls to Stored Procs

Imran.Mohammed · New User Joined: 06 Jul 2007 Posts: 5 Location: India

Hi
Its been long since a silent reader in this forum. I have a Question. I need to call my stored procs from .net to get some info on the front end with the input of a string.

My stored proc inturn get info from DB2 database. My situation is, The stored proc gets called with same input string many times. e.g. today's transactions for an account.

Is there is anyway i can aviod going to db2 to get the same info from SP, rather i store in some cache?

i.e. Once fetched from db2, then i need to store that info in some cache and later when same string comes, i will get the info from cache. In this way i can aviod multiple db2 calls.

Thanks in advance for your advice.

dbzTHEdinosauer · Posted: Thu Jul 28, 2011 3:02 pm

well,
1. are we talking about mainframe db2 or pc/server db2?

2. by the way, front end is the .net, back-end is where you get the info.

MAINFRAME

the bufferpools (db2 data in memory as apposed to disc access) contain (normally) that last used data. so if your repeated calls from the front end concern the same data, chances are that the db2 data is available in the bufferpool and no requirement for disc access exists.

now, as far as retrieving the same info multiple times to process a transaction,
you have to take care that some other task does not change this data between two of your calls. if so, then your idea of cached data is dangerous.

plus, to store the data in a cache? what do you mean. every cache for the mainframe is based on disc data.

if you want to take chances, you can store the data yourself in the front end and avoid the backend db2 call, but you still run the risk of using 'old' data that could have been changed.

Imran.Mohammed · New User Joined: 06 Jul 2007 Posts: 5 Location: India

Hi Dick
1. Its mainframe DB2
2. DB2.

I meant cache as some temp storage.

No data change between the calls for sure. Because the DB2 table getting refreshed end of the day. So no worry on that. But calls from SPs with different inputs are possible.
e.g. 1. I want info on account A - Call 1 from SP to DB2
2. I want info on account B - Call 2 from SP to DB2
3. I want info on account A again - Call 3 from SP to DB2

Can bufferpool be helpful in this case? If so how can i use them? a little insight on that please?

Access from front end is not working out for me. So i am keeping my scope in mainframe itself.

dick scherrer · Posted: Thu Jul 28, 2011 8:00 pm

Hello,

You might consider downloading the "static" data to the .net platform and store in Access or SQL Server or. . .

If it is only refreshed/updated one time a day, the process that does this coul also transfer the data to the remote system and apply it to a local table. . .

sushanth bobby · Posted: Fri Jul 29, 2011 12:08 am

Hi Imran,

As d.sch. said, we have similar process in place.

Internet DB2 tables which has basic data are processed in the nightly batch jobs and after the batch process, from SQL Server side we have SSIS packages setup to pull the data from DB2 and store it in the SQL Server.

ASP application works in a way like, general policy information is pulled from SQL server, when the customer requires a specific/detailed data, it connects to mainframe access data from VSAM and DB2, processes it and sends it back to ASP.

And do not worry about bufferpools, DB2 will take care of it.

Thanks,
Sushanth

Imran.Mohammed · New User Joined: 06 Jul 2007 Posts: 5 Location: India

Thanks Much All. thats a very good suggestions came up from all. Let me check and work out.

But guess the maintanence cost will add up for SQL server apart from db2 cost for storing same data in both the servers. Let me analyse with your suggestions / views.

Big help. Thanks guys.

dbzTHEdinosauer · Posted: Fri Jul 29, 2011 6:48 pm

Imran.Mohammed,

Sushanth's system is set-up knowing that they will always have out-dated data, and have a process in place to allow for current data when needed.

you haven't indicated any processing problems,
it almost seems as if you are trying to tune something,
they may not require tuning.

the problem of having duplicate data is that there will always be something out-of-date.

just because something is not being 'updated' real-time now,
does not mean that it will remain that way forever.
and your little 'self made cache would be a problem in the future.

as well as the fact that it seems that your cache would reside on the mainframe.
if it the cache were to reside on the server, i would possibly be willing to ok that.

but to duplicate, what is in db2, and the only place you have to duplicate is on disc - because you have no permanent memory resident process to store the data -
means you are in contention with a possibly well tuned db2.

plus you are compilicating your SP with two potential sources, instead of one.

dick scherrer · Posted: Fri Jul 29, 2011 7:23 pm

Hello,