Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Aviod Calls to Stored Procs

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Imran.Mohammed

New User


Joined: 06 Jul 2007
Posts: 5
Location: India

PostPosted: Thu Jul 28, 2011 2:42 pm    Post subject: Aviod Calls to Stored Procs
Reply with quote

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.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Thu Jul 28, 2011 3:02 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Imran.Mohammed

New User


Joined: 06 Jul 2007
Posts: 5
Location: India

PostPosted: Thu Jul 28, 2011 3:12 pm    Post subject: Aviod Calls to Stored Procs
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jul 28, 2011 8:00 pm    Post subject:
Reply with quote

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. . .
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Jul 29, 2011 12:08 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Imran.Mohammed

New User


Joined: 06 Jul 2007
Posts: 5
Location: India

PostPosted: Fri Jul 29, 2011 6:29 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Fri Jul 29, 2011 6:48 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 29, 2011 7:23 pm    Post subject:
Reply with quote

Hello,

Quote:
But guess the maintanence cost will add up for SQL server apart from db2 cost for storing same data in both the servers.
In the places i work, there is little cost associated with maintaining a "duplicate" copy close to the processing. . .

The data doesn't change, so there should not be any admin work to speak of (other than setting it up whch is trivial). Server dasd has become extremely inexpensive. . .

Of course, if somone really does not want anything other than the mainframe/db2 alternative . . .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 6 Thu Oct 27, 2016 10:20 am
No new posts Is possible to call subprogram from c... cmsmoon COBOL Programming 3 Fri Aug 05, 2016 6:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us