ca.portal.admin

An update to the Performance Problem after Database

Discussion created by ca.portal.admin on Feb 9, 2006
Enlarge/Exten d

From: Bill Vohs, SC Data Center, Monroe, Wi
Date: February 9, 2006

Subject: Enlarge/Expand IDMS Database Causes Severe Performance
Degradation

I really hate to tell you this, but
A lot of these questions I am being asked are going right over my head.

We don't have a DBA.
I am a 'Systems Programmer' that was volunteered to do this stuff.
I know absolutely nothing about IDMS.
I am just copying/using JCL that was created years ago.

I do know:
These job are all run CV-Mode.
We have over 100,000 buffer pages allocated.
The buffers are split between files. The busier the file the
more
buffers it has.
PREFETCH=NO.

"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: An update to the Performance Problem after Database Enlarge/Exten d
"Hi Bill,
Sounds like your company could get some good payback at
the moment to bring in a qualified, DB
performance-oriented IDMS consultant to get you back on
track!

Best regards,
Joe Perkins
On Thu, 9 Feb 2006 10:08:05 -0600
""Vohs, Bill"" <vohs@SCDATACENTER.COM> wrote:
From: Bill Vohs, SC Data Center, Monroe,
Wi
Date: February 9, 2006

Subject: Enlarge/Expand IDMS Database Causes
Severe Performance
Degradation

I really hate to tell you this, but
A lot of these questions I am being asked are going
right over my head.

We don't have a DBA.
I am a 'Systems Programmer' that was volunteered to do
this stuff.
I know absolutely nothing about IDMS.
I am just copying/using JCL that was created years ago.

I do know:
These job are all run CV-Mode.
We have over 100,000 buffer pages allocated.
The buffers are split between files. The busier
the file the more
buffers it has.
PREFETCH=NO.
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: An update to the Performance Problem after Database Enlarge/Exten d
"Hi Don,

Thanks for the plug! We appreciate it.

Laura Rochon
IUA

Casey, Don J wrote:
I really hate to tell YOU this, but you may need to tell your management
they need to hire, rent or somehow borrow an IDMS DBA to sort this out.
Are there any people in your application development groups with IDMS
training you can enlist in figuring this out?

The thought that there are shops out there running production workloads
without available expertise in the underlying subsytems is scary.

Have they given you any training in IDMS? If not, and you're going to
be supporting this longterm you should agitate for some classes; going
to the IUA Workshop in Dallas would be a start (see Terry's posting
yesterday).

Best of luck.

* DQC *=20

-----Original Message-----
From: IDMS Public Discussion Forum [mailTo:IDMS-L@LISTSERV.IUASSN.COM]
On Behalf Of Vohs, Bill
Sent: Thursday, February 09, 2006 8:08 AM
To: IDMS-L@LISTSERV.IUASSN.COM
Subject: An update to the Performance Problem after Database
Enlarge/Exten d

From: Bill Vohs, SC Data Center, Monroe, Wi
Date: February 9, 2006

Subject: Enlarge/Expand IDMS Database Causes Severe Performance
Degradation

I really hate to tell you this, but
A lot of these questions I am being asked are going right over my head.

We don't have a DBA.
I am a 'Systems Programmer' that was volunteered to do this stuff.
I know absolutely nothing about IDMS.
I am just copying/using JCL that was created years ago.

I do know:
These job are all run CV-Mode.
We have over 100,000 buffer pages allocated.
The buffers are split between files. The busier the file the
more
buffers it has.
PREFETCH=3DNO.


"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Performance Degradation after Database Enlarge/Expand
"To help to get a 'clue' on how many I/O's are happening within your job within IDMS. (So, if you have 15,000 TRKS, how many IO's are we dealing with).

From you unload reload, you should have seen record counts (this also can give insights on how many records you are dealing with).

With the job your running that is taking 3-4 hours to run:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If it is running LOCAL mode, (absence of a SYSCTL, pointing to a CV, )

then add the following statement to your LOCAL JCL;
//SYSIDMS DD *
BUFFERSTAT NOTE: OUTPUT will go TO //SYSLST dd *, so make sure you
have one.

This will give stats on the buffer performance on your LOCAL run.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If your running though you CV then prior to the run issue the COMMAND (within your CV):
DCMT DISPLAY STATISTICS BUFFERS

You can also issue the commands:
"" DCMT DISPLAY STATSITICS BUFFER 'your-buffer-name' ALL"" on each of your buffers, to get more detailed information on how many I/O's for each area with the buffer during this run:

*---->AND after the run is done again issue these commands again;

SUBTRACTING the Smaller values from the LARGER counts, will give you a ""IDEA"" how many Physical I/O(s) you are experiencing during this jobs run time.

This information can you insights on what is happening within your IDMS system an the number of IO(s) you may be incurring.

Cross referencing this with you program logic , (Bachman diagrams), will help to see if these counts are reasonable (or should the program be redesigned).

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Edward A. Timm
Sallie Mae, Senior Database Analyst
ETIMM@salliemae.com
(317) 596-1182
Fax (317) 595-1494
Voss@SCDATACENTER.COM 02/08/2006 02:03:25 PM >>>
From: Bill Vohs, SC Data Center, Monroe, Wi
Date: February 1, 2006

Subject: Enlarge/Expand IDMS Database Causes Severe Performance
Degradation

We are running:

z/OS 1.4
IDMS Release 16 SP02
EMC DMX2000 DASD

We have a need to enlarge a number of the IDMS databases.

Every time we do, we experience severe performance degradation.
Run times elongate by a factor of 3 to 5.
Job that ran in 30 minutes now takes 3-4 hours.

Our first attempt was back in November 2005.
The approach at that time was to do an UNLOAD and RELOAD to the enlarged
area.
This time we enlarged the databases by a factor of 3.
I.e. One database went from 15,000 tracks to 45,000 tracks.
Another database went from 7,000 tracks to 21,000 tracks.
These all on 3390 DASD on the EMC DMX2000.

Our run times were:

Before UNLOAD/RELOAD 11/09/2005 74 minutes
11/10/2005 46 minutes

After UNLOAD/RELOAD 11/16/2005 174 minutes
11/1/2005 198 minutes

This happened with almost every one of our jobs.

Our second attempt was January 29, 2006..
The approach this time was to do a EXPAND PAGE to an enlarged area
This time the databases were enlarged by a factor of 2.
One database went from PAGE SIZE 5064 to 10796 from 15,000 tracks to 30,000
tracks.
Another database went from PAGE SIZE 5064 to 10796 from 7,000 tracks to
14,000 tracks.

The run times are:

Before EXPAND PAGE 01/26/2006 64
minutes

After EXPAND PAGE 01/31/2006 209
minutes
02/01/2006 212 minutes


We have had an IDMS consultant review our work and he says every thing looks
great.

Our z/OS and IDMS statistics show nothing out of the ordinary.

We are at a bit of a loss.

Might anybody have any suggestion for us?

This E-Mail has been scanned for viruses.

"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
DB Segmentation
"Once again I would like to tap into the vast knowledge pool that is
IDMS-L.

We are considering a major modification to an existing IDMS database.
Due to the growth of the data within IDMS, we are considering splitting
this database into several smaller entities. Example: all customers
whose accounts start with ""1"" go into DB1, accounts that start with ""2""
go into DB2, etc. Some mechanism would be used (system exit or
programmatically) to determine which database is accessed and stored.

Are there any other IDMS shops out there doing something like this?
Would you be willing to discuss this topic with me and let us know the
benefits and pitfalls you are aware of?

Thanks in advance.

Dan Hall
GE
Capital Solutions
Danbury, CT

T 513.217.5060
E dan.hall@ge.com
http://www.ge.com/capitalsolutions/

"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: [IDMSVENDOR-L] DB Segmentation
"seriously, we do such a thing: we have a ""driver"" (or dare I say
partiioning) table -- oops I mean record type that lists the end-point of
the segmentation, the subschema and DBNAME to use.

we use this for updating where would would read the driver, and the bind
to the appopriate dbname,
and for weekly reporting, we walk the driver table and then bind to each
segment in turn .

additionally, of the 6 segments so configured, 2 of the segments are
updatable from each of 3 CVs handling this application (customer calls -
ours have skyrocketed in light of medicare part D) - all 6 can be read by
each of the 3 CVs (we use SHARED CACHE, but not DATA GROUPS)

Chris Hoelscher
IDMS & DB2 Database Administrator
Humana Inc
502-580-2538
choelscher@humana.com




The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.

"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: DB Segmentation
"Dan,
We currently split our database into four segments by storing 4
database records in the area via a direct mode store. The programs that
sweep the database read flat file and go to that record directly then
sweep until the logical point. This method allows 4 program to sweep
and update the database concurrently. I am not sure this is exactly
what you are asking but I thought I would provide you the information.

Rick White
850-453-4141 x1486
Database Administrator
DFAS TSOPE
Pensacola, FL

Outcomes