Storing bufferpool information in a table

Tom Glaser

Storing bufferpool information in a table

Hi everyone,

The bufferpool definitions are stored in the BSDS and I know I can dump this information.  Using rexx, I can take this data and load it into a table.  Is anyone aware of a stored procedure that will do this?

Thanks, Tom

 

Michael Hannan

RE: Storing bufferpool information in a table
(in response to Tom Glaser)

Can I just ask out of curiosity, what makes Stored Procedures so appealing, that they are seen as a desirable solution to so many problems. I ask to learn from what others do. I have not been writing them, generally. Instead I get concerned about the performance of SPROCs at times when they get misused (when called too often). I thought Stored Procs were designed to alleviate unnecessary SQL interactions between the Client and the Server. I appreciate that Native Stored Procs have a lot less overheads and that the overheads don't even matter for very infrequent calls, likely in your situation. So is it the ease of a calling a Stored Proc the key point? What would you call it from?

I can imagine considering a Stored Proc for producing output I can't quite manage with a complex SQL, due to procedural logic possibilities and where a Table function won't quite do the job.

I could imagine an SQL that Inserts rows from an external table function (instead of a load), but not sure it is relevant to your desire to use a Stored Proc. You can also write an external Stored Proc if that turns you on, to insert the data into the table. Could be in REXX maybe.

Naturally it is extremely unlikely that such a thing will already exist.
 
In Reply to Tom Glaser:

Hi everyone,

The bufferpool definitions are stored in the BSDS and I know I can dump this information.  Using rexx, I can take this data and load it into a table.  Is anyone aware of a stored procedure that will do this?

Thanks, Tom

 



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Philip Sevetson

Storing bufferpool information in a table
(in response to Michael Hannan)
Michael,

Just speculating on Tom’s motives, of course… but it looks to me like he’s thinking of a home-built application to track changes to BP configuration over time, and/or possibly write a rebuild script of the DSNZPARM module based on the actual contents of the subsystem.

--Phil S.

From: Michael Hannan [mailto:[login to unmask email]
Sent: Monday, April 30, 2018 4:25 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Storing bufferpool information in a table


Can I just ask out of curiosity, what makes Stored Procedures so appealing, that they are seen as a desirable solution to so many problems. I ask to learn from what others do. I have not been writing them, generally. Instead I get concerned about the performance of SPROCs at times when they get misused (when called too often). I thought Stored Procs were designed to alleviate unnecessary SQL interactions between the Client and the Server. I appreciate that Native Stored Procs have a lot less overheads and that the overheads don't even matter for very infrequent calls, likely in your situation. So is it the ease of a calling a Stored Proc the key point? What would you call it from?

I can imagine considering a Stored Proc for producing output I can't quite manage with a complex SQL, due to procedural logic possibilities and where a Table function won't quite do the job.

I could imagine an SQL that Inserts rows from an external table function (instead of a load), but not sure it is relevant to your desire to use a Stored Proc. You can also write an external Stored Proc if that turns you on, to insert the data into the table. Could be in REXX maybe.

Naturally it is extremely unlikely that such a thing will already exist.

In Reply to Tom Glaser:

Hi everyone,

The bufferpool definitions are stored in the BSDS and I know I can dump this information. Using rexx, I can take this data and load it into a table. Is anyone aware of a stored procedure that will do this?

Thanks, Tom





Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

James Campbell

Storing bufferpool information in a table
(in response to Tom Glaser)
SYSPROC.ADMIN_COMMAND_DB2.

To answer some other queries about "why stored procedures", because
- they can get you into a different environment (eg DSNACICS) or security environment
(WLM_REFRESH)
- they can be 'easily' invoked from a remote environment (as this one is intended)
- they can be invoked from a trigger, providing a 'this must be done' process
- because brand x has them.

Are they of use? In those ancient word - it depends on what you want to use them for.

James Campbell


On 30 Apr 2018 at 12:40, Tom Glaser wrote:

>
> Hi everyone,
> The bufferpool definitions are stored in the BSDS and I know I can dump this information.  Using
> rexx, I can take this data and load it into a table.  Is anyone aware of a stored procedure that will
> do this?
> Thanks, Tom
>  


---
This email has been checked for viruses by AVG.
http://www.avg.com

Michael Hannan

RE: Storing bufferpool information in a table
(in response to Philip Sevetson)



In Reply to Philip Sevetson:

Just speculating on Tom’s motives, of course… but it looks to me like he’s thinking of a home-built application to track changes to BP configuration over time, and/or possibly write a rebuild script of the DSNZPARM module based on the actual contents of the subsystem.

I think you missed the point of my question. His basic purpose or aim was clear enough. I was curious to know why the Stored Procs technique was so ideal. I think James has addressed that point of my question.

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Tom Glaser

RE: Storing bufferpool information in a table
(in response to Michael Hannan)

Hi Michael,

Today, I use rexx to call various stored procedures.  For example, I'm storing all zparm values into a table by calling procedure DSNWZP.  I add a date and now I'm able to go back in time see when a zparm value had changed...or what it was.  You can call stored procedures for IDAA and parsing through the xml, pull out really good information.

I was looking to do the same for buffer pools.  Another option is to display each and every bufferpool, pull out the data, then store that into a table.  But, if a stored procedure already existed today, I wouldn't have to start from scratch.

Tom

 

Johnny Mossin

RE: Storing bufferpool information in a table
(in response to Tom Glaser)

maybe use admin_command_db2 stored procedure, with type BP and -display bufferpool(active).
 and remember to write the command fully, not abbreviations.

I have a small rexx, that makes a csv dataset of the outcome from the stored proc. That dataset could be loaded into a table.

Johnny Mossin

Nordea

Edited By:
Johnny Mossin[Organization Members] @ May 02, 2018 - 12:46 PM (Europe/Copenhagen)

alain pary

RE: Storing bufferpool information in a table
(in response to Johnny Mossin)

Hello , 

If you call the procedure with the following syntax 

SYSPROC.ADMIN_COMMAND_DB2('-DISPLAY BUFFERPOOL(*) DETAIL' ,64 ,'BP' , , ?,?,?,?,?,?,?,?)

you will get a result set with the majority of the parameters of your bufferpool . 

this result as 80 rows , one for each BP .

In attach you will find a csv for our test system in Dallas .

 

regards 

 

Alain Pary

Attachments

  • Display_bp.csv (4.7k)

Michael Hannan

RE: Storing bufferpool information in a table
(in response to alain pary)

In Reply to alain pary:

If you call the procedure with the following syntax 

SYSPROC.ADMIN_COMMAND_DB2('-DISPLAY BUFFERPOOL(*) DETAIL' ,64 ,'BP' , , ?,?,?,?,?,?,?,?)

you will get a result set with the majority of the parameters of your bufferpool . 

this result as 80 rows , one for each BP .

In attach you will find a csv for our test system in Dallas . 

I see from the IBM Knowledge site that output of the Stored Proc can go to a Created "Global" Temp table

SYSIBM.BUFFERPOOL_STATUS when processing-type = "BP":

Presumably easy to code an INSERT into your own table selecting from there, providing you keep your table at same location. That Insert could be inside your own Stored Proc that calls SYSPROC.ADMIN_COMMAND_DB2, is that right?  Just trying to fill in the gaps for me, not so experienced in using Stored Procs.

I am liking to use SQL more than REXX, so I like to keep all my vital performance or other info in DB2 tables, even more than in CSVs. I also use CSVs for extract output (rather than a repository), but generally with fixed column widths, so it is highly readable dumped into a simple text file (as well as being able to go to a spreadsheet). Text files still can be zip compressed to save space.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ May 02, 2018 - 05:45 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ May 02, 2018 - 05:48 PM (Europe/Berlin)

Jørn Thyssen

RE: Storing bufferpool information in a table
(in response to Tom Glaser)

Hi Tom,

I know this is not what you are looking for, but bufferpool information is stored in Db2 statistics records (IFCID 2 and IFCID 202).

If your shop is already loading these into Db2 tables, you might have easy access to the bufferpool data.Usually the formatting of SMF records requires a vendor tool such as Omegamon Db2 from IBM or the equivalent from other vendors.

In Reply to Tom Glaser:

Hi everyone,

The bufferpool definitions are stored in the BSDS and I know I can dump this information.  Using rexx, I can take this data and load it into a table.  Is anyone aware of a stored procedure that will do this?

Thanks, Tom

 



 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

Horacio Villa

Storing bufferpool information in a table
(in response to Jørn Thyssen)
If the information you want to load into a table is displayed from a '-DIS
BPOOL ....' command you can collect it with a REXX program.

Horacio Villa



Tom Glaser

RE: Storing bufferpool information in a table
(in response to Horacio Villa)

Jorn,

I did try mainview, but was told this is not possible.

Michael,

I did try:

SYSPROC.ADMIN_COMMAND_DB2('-DISPLAY BUFFERPOOL(*) DETAIL' ,64 ,'BP' , , ?,?,?,?,?,?,?,?)

...and this works great. I can run this with QMF for Workstation, it lists everything I need, and load the results directly into a table.  I can then use the QMF job scheduler on QMF for WebSphere to automate this process for each week.

Thanks everyone!

Tom