How many DBAs does it take...

Lynne Flatley

How many DBAs does it take...
to support a company's test and production environments? I'm trying to
gather some statistics or rule-of-thumb for my boss who would like to
increase our staff.

There are 6 of us, including my boss to support IMS, DB2 (for OS/390), MS
SQL Server and we're starting to get into UDB/NT. There are about 30 IMS
databases, 115 DB2 databases and 20 SQL Server databases in production. I
would say that the DB2 and SQL Server databases have 30 - 40 tables each.
We have twice that number of databases in test, given that we typically have
a unit-test database and a QA database in test. I would say that the IMS
tuning and maintenance is pretty much on auto-pilot and the bulk of our time
is spent on DB2 and SQL Server. We don't spend any time pro-actively tuning
and/or monitoring except for space. The developers sometimes do their own
DBA work in test, i.e. create/alter tables, create indices, etc. I would
like us to move away from that (i.e. the DBAs 'own' the structures) but I'm
not sure our staff is large enough to handle the volume of work. There are
approximately 200 developers in our organization.

Are we understaffed?

"A foolish consistency is the hobgoblin of small minds" - Ralph Waldo
Emerson

Lynne A. Flatley
New England Financial
(617) 578-4079
[login to unmask email]



[login to unmask email]

Re: How many DBAs does it take...
(in response to Lynne Flatley)
Lynne,

We're in a somewhat similar situation, and we know that we're understaffed. We
have 4 DBA's supporting four different DBMS platforms: DB2 on OS/390, MS SQL
Server running on NT, Oracle/Rdb running on VMS, and just recently UDB on AIX.
Each of the four of us are supporting two of the above systems, so that we have
two people for each DBMS.

Our environment currently consists of the following:

DB2: approximately 85 production databases, with a total of a little over 1600
tables, and 221 test databases, with a total of a little over 4700 tables. In
general, for each production database, we have three corresponding test
databases: unit test, system test, and model office (QA).

SQL Server: approximately 65 production databases, and 70 test databases. Each
production database has one corresponding unit test database.

Oracle/Rdb: 6 production databases, and 12 test databases. Each production
database has two corresponding test databases: unit test and model office (QA).

UDB: 3 test databases, all currently under development. We're not yet sure what
our ultimate production/test configuration will be for this environment.

As far as supporting these environments go, the DBA's fully support the
production environments for all four DBMS's. Test support is a little
different, though: for DB2, the DBA's fully support the creation and maintenance
of the schemas (tables/indexes, etc.). Our applications do not currently
utilize stored procedures in DB2, but when they do, the application developers
will be responsible for the coding and testing of the SP's. For MS SQL Server,
the developers are responsible for creating and maintaining the schemas as well
as stored procedures. For Rdb, the DBA's fully support the test environment.
For UDB, the DBA's are currently doing all the work in the test environment so
far, but it's far too early in the game to determine if it will stay that way
over the long haul.

It's just a guess, but 200 developers probably sounds right for our organization
as well.

Are we understaffed? Absolutely, painfully so! Fortunately for us, new
development slowed to a crawl in 1999 due to Y2K preparations, but we have a
fear that once things start to pick up in mid-to-late first quarter 2000 or so,
we may get hit hard with work, and we're not sure if we'll be able to handle
what comes our way with only 4 DBA's.

Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT





Lynne Flatley <[login to unmask email]> on 12/30/99 07:36:27 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc: (bcc: BILL GALLAGHER/Phoenix Home Life Mutual Insurance)
bcc: BILL GALLAGHER/Phoenix Home Life Mutual Insurance
Subject: How many DBAs does it take...



to support a company's test and production environments? I'm trying to
gather some statistics or rule-of-thumb for my boss who would like to
increase our staff.

There are 6 of us, including my boss to support IMS, DB2 (for OS/390), MS
SQL Server and we're starting to get into UDB/NT. There are about 30 IMS
databases, 115 DB2 databases and 20 SQL Server databases in production. I
would say that the DB2 and SQL Server databases have 30 - 40 tables each.
We have twice that number of databases in test, given that we typically have
a unit-test database and a QA database in test. I would say that the IMS
tuning and maintenance is pretty much on auto-pilot and the bulk of our time
is spent on DB2 and SQL Server. We don't spend any time pro-actively tuning
and/or monitoring except for space. The developers sometimes do their own
DBA work in test, i.e. create/alter tables, create indices, etc. I would
like us to move away from that (i.e. the DBAs 'own' the structures) but I'm
not sure our staff is large enough to handle the volume of work. There are
approximately 200 developers in our organization.

Are we understaffed?

"A foolish consistency is the hobgoblin of small minds" - Ralph Waldo
Emerson

Lynne A. Flatley
New England Financial
(617) 578-4079
[login to unmask email]








Jim Knisley

Re: How many DBAs does it take...
(in response to BILL_GALLAGHER@PHL.COM)
Lynne,

I'll look back through my "archives" (that means the mess in my filing cabinet)
but I remember from an IS management seminar I attended earlier in the year that
on average 1 DBA per 12-20 programmers is ideal. I don't remember who came up
with that number or how they justified it. Of course, that depends on how
active your developement staff is in new development as opposed to supporting
purchased packages, if you have a Warehousing environment you are supporting,
and how self-sufficient are your end-users. We have 3 DBA's supporting 50
programmers in an environment with DB2 UDB for OS/390 and NT, Oracle on AIX, and
SUPRA on OS/390. We support two operating companies with manufacturing plants
and offices world-wide. We have a fairly robust DSS and operational data store
environment set up in it's own DB2 subsystem on OS/390 and our end-users create
90+% or their own reports using tools like QMF for Windows, Business Objects,
and Infomaker. They have gotten very good at verfiying the accurracy of their
reports against data from online screens. That frees up our developers from
doing report generation. We also have a corporate policy that new project
requests from end-users must directly apply to a stated company goal that was
approved by the corporate executive team at the beginning of the year, and/or it
must show a significant ROI. Each department can only have 1 active project in
IS at a time and they must prioritize the projects before submitting them to IS.
We'll work on anything they want as long as it meets the company objective
standard and they have given us the priority they want it completed in. Our
DBA's do all of the schema type things (create tables and indices) and DBMS type
tasks (i.e. size Bufferpools, system tuning, ...) for all the DBMS's in test,
QA, and production.
We use a lot of automation tools in DB2 for tasks like reorgs, image copies,
tuning, and space management. We also cross-train within the technical staff.
For example, if I get swamped with a project, and one of the CICS system
programmers that has received training in DB2 can step in and help out creating
tables or indexes from the physical ER model, or work on a DB2 problem call from
the help-desk. Likewise, I have been trained on CICS system programming and
SMP/E, so I can help them if they have a major upgrade, maintenance to apply,
or a new CICS region to setup. The cross-training has been a God-send for
things like vacations so I don't have to carry a pager with me on vacation.

Given the ratio above, It sounds like you are very under-staffed. But, it's
probably not likely that you'll be able to hire 6-8 more DBA's, so you also
have to look at what else you can do to relieve mundane tasks from the DBA
staff. Software utilities can be expensive initially, but if you look at it in
comparison to head-count (salary, benefits, insurance, retirement plans, ...) it
may be more cost-effective in the long run.

Good luck and Happy New Year,
Jim Knisley
Lead DBA
Hillenbrand Industries



Lynne Flatley

Re: How many DBAs does it take...
(in response to Jim Knisley)
Jim,
I like that metric! I'll forward this on to my boss. We have tools to
automate our work (Platinum Database Analyzer, Plan Analyzer, etc. but we
don't know how to use them and we haven't made time to learn how to use
them...vicious cycle, eh?).

Thanks much!

> -----Original Message-----
> From: Jim Knisley [SMTP:[login to unmask email]
> Sent: Thursday, December 30, 1999 9:09 AM
> To: [login to unmask email]
> Subject: Re: How many DBAs does it take...
>
> Lynne,
>
> I'll look back through my "archives" (that means the mess in my filing
> cabinet)
> but I remember from an IS management seminar I attended earlier in the
> year that
> on average 1 DBA per 12-20 programmers is ideal. I don't remember who
> came up
> with that number or how they justified it. Of course, that depends on how
> active your developement staff is in new development as opposed to
> supporting
> purchased packages, if you have a Warehousing environment you are
> supporting,
> and how self-sufficient are your end-users. We have 3 DBA's supporting
> 50
> programmers in an environment with DB2 UDB for OS/390 and NT, Oracle on
> AIX, and
> SUPRA on OS/390. We support two operating companies with manufacturing
> plants
> and offices world-wide. We have a fairly robust DSS and operational data
> store
> environment set up in it's own DB2 subsystem on OS/390 and our end-users
> create
> 90+% or their own reports using tools like QMF for Windows, Business
> Objects,
> and Infomaker. They have gotten very good at verfiying the accurracy of
> their
> reports against data from online screens. That frees up our developers
> from
> doing report generation. We also have a corporate policy that new project
> requests from end-users must directly apply to a stated company goal that
> was
> approved by the corporate executive team at the beginning of the year,
> and/or it
> must show a significant ROI. Each department can only have 1 active
> project in
> IS at a time and they must prioritize the projects before submitting them
> to IS.
> We'll work on anything they want as long as it meets the company objective
> standard and they have given us the priority they want it completed in.
> Our
> DBA's do all of the schema type things (create tables and indices) and
> DBMS type
> tasks (i.e. size Bufferpools, system tuning, ...) for all the DBMS's in
> test,
> QA, and production.
> We use a lot of automation tools in DB2 for tasks like reorgs, image
> copies,
> tuning, and space management. We also cross-train within the technical
> staff.
> For example, if I get swamped with a project, and one of the CICS system
> programmers that has received training in DB2 can step in and help out
> creating
> tables or indexes from the physical ER model, or work on a DB2 problem
> call from
> the help-desk. Likewise, I have been trained on CICS system programming
> and
> SMP/E, so I can help them if they have a major upgrade, maintenance to
> apply,
> or a new CICS region to setup. The cross-training has been a God-send for
> things like vacations so I don't have to carry a pager with me on
> vacation.
>
> Given the ratio above, It sounds like you are very under-staffed. But,
> it's
> probably not likely that you'll be able to hire 6-8 more DBA's, so you
> also
> have to look at what else you can do to relieve mundane tasks from the DBA
> staff. Software utilities can be expensive initially, but if you look at
> it in
> comparison to head-count (salary, benefits, insurance, retirement plans,
> ...) it
> may be more cost-effective in the long run.
>
> Good luck and Happy New Year,
> Jim Knisley
> Lead DBA
> Hillenbrand Industries
>
>
>
>
>



Lynne Flatley

Re: How many DBAs does it take...
(in response to Lynne Flatley)
Bill,
Not to depress you but it sounds like you're even more understaffed than us!
I forwarded your email on to my boss. Luckily, he's a joy to work for (and
no, he's not on the listserv :-) ).

Thanks for the feedback!


> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Thursday, December 30, 1999 8:39 AM
> To: [login to unmask email]
> Subject: Re: How many DBAs does it take...
>
> Lynne,
>
> We're in a somewhat similar situation, and we know that we're
> understaffed. We
> have 4 DBA's supporting four different DBMS platforms: DB2 on OS/390, MS
> SQL
> Server running on NT, Oracle/Rdb running on VMS, and just recently UDB on
> AIX.
> Each of the four of us are supporting two of the above systems, so that we
> have
> two people for each DBMS.
>
> Our environment currently consists of the following:
>
> DB2: approximately 85 production databases, with a total of a little over
> 1600
> tables, and 221 test databases, with a total of a little over 4700 tables.
> In
> general, for each production database, we have three corresponding test
> databases: unit test, system test, and model office (QA).
>
> SQL Server: approximately 65 production databases, and 70 test databases.
> Each
> production database has one corresponding unit test database.
>
> Oracle/Rdb: 6 production databases, and 12 test databases. Each
> production
> database has two corresponding test databases: unit test and model office
> (QA).
>
> UDB: 3 test databases, all currently under development. We're not yet
> sure what
> our ultimate production/test configuration will be for this environment.
>
> As far as supporting these environments go, the DBA's fully support the
> production environments for all four DBMS's. Test support is a little
> different, though: for DB2, the DBA's fully support the creation and
> maintenance
> of the schemas (tables/indexes, etc.). Our applications do not currently
> utilize stored procedures in DB2, but when they do, the application
> developers
> will be responsible for the coding and testing of the SP's. For MS SQL
> Server,
> the developers are responsible for creating and maintaining the schemas as
> well
> as stored procedures. For Rdb, the DBA's fully support the test
> environment.
> For UDB, the DBA's are currently doing all the work in the test
> environment so
> far, but it's far too early in the game to determine if it will stay that
> way
> over the long haul.
>
> It's just a guess, but 200 developers probably sounds right for our
> organization
> as well.
>
> Are we understaffed? Absolutely, painfully so! Fortunately for us, new
> development slowed to a crawl in 1999 due to Y2K preparations, but we have
> a
> fear that once things start to pick up in mid-to-late first quarter 2000
> or so,
> we may get hit hard with work, and we're not sure if we'll be able to
> handle
> what comes our way with only 4 DBA's.
>
> Bill Gallagher, DBA
> Phoenix Home Life
> Enfield, CT
>
>
>
>
>
> Lynne Flatley <[login to unmask email]> on 12/30/99 07:36:27 AM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
>
> To: [login to unmask email]
> cc: (bcc: BILL GALLAGHER/Phoenix Home Life Mutual Insurance)
> bcc: BILL GALLAGHER/Phoenix Home Life Mutual Insurance
> Subject: How many DBAs does it take...
>
>
>
> to support a company's test and production environments? I'm trying to
> gather some statistics or rule-of-thumb for my boss who would like to
> increase our staff.
>
> There are 6 of us, including my boss to support IMS, DB2 (for OS/390), MS
> SQL Server and we're starting to get into UDB/NT. There are about 30 IMS
> databases, 115 DB2 databases and 20 SQL Server databases in production. I
> would say that the DB2 and SQL Server databases have 30 - 40 tables each.
> We have twice that number of databases in test, given that we typically
> have
> a unit-test database and a QA database in test. I would say that the IMS
> tuning and maintenance is pretty much on auto-pilot and the bulk of our
> time
> is spent on DB2 and SQL Server. We don't spend any time pro-actively
> tuning
> and/or monitoring except for space. The developers sometimes do their own
> DBA work in test, i.e. create/alter tables, create indices, etc. I would
> like us to move away from that (i.e. the DBAs 'own' the structures) but
> I'm
> not sure our staff is large enough to handle the volume of work. There
> are
> approximately 200 developers in our organization.
>
> Are we understaffed?
>
> "A foolish consistency is the hobgoblin of small minds" - Ralph Waldo
> Emerson
>
> Lynne A. Flatley
> New England Financial
> (617) 578-4079
> [login to unmask email]
>
>
>
> the
>
>
>
>
>
>
>



Lynne Flatley

Re: How many DBAs does it take...
(in response to Philip Gunning)
I forgot to include in our DBA headcount the DBA who has been working to
implement clustering on SQL Server 7.0. So we have a total of 7 DBAs,
including our boss. We have 2 full-time data modelers. Development
projects are not required to use them (and they couldn't anyway, as the data
modelers are under-staffed). We have 3 people who support our gateway
products (MDI, Direct Connect and DB2 Connect, which is our strategic
gateway product) and replication products (Sybase and DataJoiner). Through
the gateways comes about 60% of our SQL, that's right...you guessed it,
dynamically. We don't have a tool to capture/monitor our dynamic SQL?!
Maybe in the 2000 budget...

The monitoring/tuning of our 4 DB2 subsystems (3 test, 1 production) is done
by our sole DB2 systems programmer...dare I say her function is
under-staffed too?

When I mentioned the number of SQL Server databases, I didn't include the
ones that are in the 80+ agencies across the country. Our IMS and DB2
databases combined take up about 200 gig of DASD. Our SQL Server databases
take up about 40 gig.

> -----Original Message-----
> From: Philip Gunning [SMTP:[login to unmask email]
> Sent: Thursday, December 30, 1999 2:34 PM
> To: [login to unmask email]
> Subject: Re: How many DBAs does it take...
>
> Lynn, We have 4 fulltime employees doing day to day DBA work. All in all
> we
> support just short of a terabyte of data including UDB on NT. We also use
> contractors for specific tasks when we need them. At times we have had a
> total of 9 DBAs. We have about 60 developers both onsite and offsite. I
> can tell you that we still don't have enough hands. We try and review all
> SQL during development, and fix bad SQL and tweak access paths, that means
> capturing SQL using Detector and Plan Analyzer, or using event monitors in
> UDB. Experience has shown that if you let garbage SQL into produciton you
> will pay sooner or later. Our Director does logical modeling and I am
> involved in that to some extent. At times we bring in an additional
> professional modeler. We use ERWin. We do all physical design. We do
> not
> let developers create objects in test or anywhere. We have Peoplesoft
> Financials. Experience has shown that at least one DBA must be dedicated
> to it. IF not, you have constant problems. Who does the modeling at your
> location? Is that included in the 6? We also monitor our 2 production
> subsystems extensively using Subsystem Analyzer, Detector, and DB2PM and
> we
> are in a trial on the Bufferpool Tool from Responsive Systems. We use
> DBArtisan and event monitors and the NT performance monitor for UDB. We
> used to have only 2 DBA's. But the later came sooner. We support DB2
> Connect and have it all over the place.. So the real answer is it depends
> on your environment. We like to think of ourselves as high speed low drag
> so we are constantly staying abreast of the latest in the profession. We
> are doing JAVA stored procedures in UDB on NT using the Stored Procedure
> Builder and that took 2 of us out of the loop for awhile. We are also all
> cross trained so we can maximize the sharing of information and provide
> new
> opportunities along the way. We also write standards when we get a chance.
> Try administering UDB on NT without directory, container (tablespace and
> index), and other naming conventions, HTH Regards, Phil
>
> Philip K. Gunning
> DB2 DBA
> IBM Certified Solutions Expert -- DB2 UDB
> IBM Cetfified Solutions Expert -- CICS TS
> Assoc List Owner
>
> -----Original Message-----
> From: Lynne Flatley [SMTP:[login to unmask email]
> Sent: Thursday, December 30, 1999 7:12 AM
> To: [login to unmask email]
> Subject: Re: How many DBAs does it take...
>
> Jim,
> I like that metric! I'll forward this on to my boss. We have tools to
> automate our work (Platinum Database Analyzer, Plan Analyzer, etc. but we
> don't know how to use them and we haven't made time to learn how to use
> them...vicious cycle, eh?).
>
> Thanks much!
>
> > -----Original Message-----
> > From: Jim Knisley [SMTP:[login to unmask email]
> > Sent: Thursday, December 30, 1999 9:09 AM
> > To: [login to unmask email]
> > Subject: Re: How many DBAs does it take...
> >
> > Lynne,
> >
> > I'll look back through my "archives" (that means the mess in my filing
> > cabinet)
> > but I remember from an IS management seminar I attended earlier in the
> > year that
> > on average 1 DBA per 12-20 programmers is ideal. I don't remember who
> > came up
> > with that number or how they justified it. Of course, that depends on
> how
> > active your developement staff is in new development as opposed to
> > supporting
> > purchased packages, if you have a Warehousing environment you are
> > supporting,
> > and how self-sufficient are your end-users. We have 3 DBA's supporting
> > 50
> > programmers in an environment with DB2 UDB for OS/390 and NT, Oracle on
> > AIX, and
> > SUPRA on OS/390. We support two operating companies with manufacturing
> > plants
> > and offices world-wide. We have a fairly robust DSS and operational
> data
> > store
> > environment set up in it's own DB2 subsystem on OS/390 and our end-users
> > create
> > 90+% or their own reports using tools like QMF for Windows, Business
> > Objects,
> > and Infomaker. They have gotten very good at verfiying the accurracy of
> > their
> > reports against data from online screens. That frees up our developers
> > from
> > doing report generation. We also have a corporate policy that new
> project
> > requests from end-users must directly apply to a stated company goal
> that
> > was
> > approved by the corporate executive team at the beginning of the year,
> > and/or it
> > must show a significant ROI. Each department can only have 1 active
> > project in
> > IS at a time and they must prioritize the projects before submitting
> them
> > to IS.
> > We'll work on anything they want as long as it meets the company
> objective
> > standard and they have given us the priority they want it completed in.
> > Our
> > DBA's do all of the schema type things (create tables and indices) and
> > DBMS type
> > tasks (i.e. size Bufferpools, system tuning, ...) for all the DBMS's in
> > test,
> > QA, and production.
> > We use a lot of automation tools in DB2 for tasks like reorgs, image
> > copies,
> > tuning, and space management. We also cross-train within the technical
> > staff.
> > For example, if I get swamped with a project, and one of the CICS system
> > programmers that has received training in DB2 can step in and help out
> > creating
> > tables or indexes from the physical ER model, or work on a DB2 problem
> > call from
> > the help-desk. Likewise, I have been trained on CICS system programming
> > and
> > SMP/E, so I can help them if they have a major upgrade, maintenance to
> > apply,
> > or a new CICS region to setup. The cross-training has been a God-send
> for
> > things like vacations so I don't have to carry a pager with me on
> > vacation.
> >
> > Given the ratio above, It sounds like you are very under-staffed. But,
> > it's
> > probably not likely that you'll be able to hire 6-8 more DBA's, so you
> > also
> > have to look at what else you can do to relieve mundane tasks from the
> DBA
> > staff. Software utilities can be expensive initially, but if you look
> at
> > it in
> > comparison to head-count (salary, benefits, insurance, retirement plans,
> > ...) it
> > may be more cost-effective in the long run.
> >
> > Good luck and Happy New Year,
> > Jim Knisley
> > Lead DBA
> > Hillenbrand Industries
> >
> >
> >
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can
> >
>
>
>
>
>
>
>
>
>
>



Philip Gunning

Re: How many DBAs does it take...
(in response to Lynne Flatley)
Lynn, We have 4 fulltime employees doing day to day DBA work. All in all we
support just short of a terabyte of data including UDB on NT. We also use
contractors for specific tasks when we need them. At times we have had a
total of 9 DBAs. We have about 60 developers both onsite and offsite. I
can tell you that we still don't have enough hands. We try and review all
SQL during development, and fix bad SQL and tweak access paths, that means
capturing SQL using Detector and Plan Analyzer, or using event monitors in
UDB. Experience has shown that if you let garbage SQL into produciton you
will pay sooner or later. Our Director does logical modeling and I am
involved in that to some extent. At times we bring in an additional
professional modeler. We use ERWin. We do all physical design. We do not
let developers create objects in test or anywhere. We have Peoplesoft
Financials. Experience has shown that at least one DBA must be dedicated
to it. IF not, you have constant problems. Who does the modeling at your
location? Is that included in the 6? We also monitor our 2 production
subsystems extensively using Subsystem Analyzer, Detector, and DB2PM and we
are in a trial on the Bufferpool Tool from Responsive Systems. We use
DBArtisan and event monitors and the NT performance monitor for UDB. We
used to have only 2 DBA's. But the later came sooner. We support DB2
Connect and have it all over the place.. So the real answer is it depends
on your environment. We like to think of ourselves as high speed low drag
so we are constantly staying abreast of the latest in the profession. We
are doing JAVA stored procedures in UDB on NT using the Stored Procedure
Builder and that took 2 of us out of the loop for awhile. We are also all
cross trained so we can maximize the sharing of information and provide new
opportunities along the way. We also write standards when we get a chance.
Try administering UDB on NT without directory, container (tablespace and
index), and other naming conventions, HTH Regards, Phil

Philip K. Gunning
DB2 DBA
IBM Certified Solutions Expert -- DB2 UDB
IBM Cetfified Solutions Expert -- CICS TS
Assoc List Owner

-----Original Message-----
From: Lynne Flatley [SMTP:[login to unmask email]
Sent: Thursday, December 30, 1999 7:12 AM
To: [login to unmask email]
Subject: Re: How many DBAs does it take...

Jim,
I like that metric! I'll forward this on to my boss. We have tools to
automate our work (Platinum Database Analyzer, Plan Analyzer, etc. but we
don't know how to use them and we haven't made time to learn how to use
them...vicious cycle, eh?).

Thanks much!

> -----Original Message-----
> From: Jim Knisley [SMTP:[login to unmask email]
> Sent: Thursday, December 30, 1999 9:09 AM
> To: [login to unmask email]
> Subject: Re: How many DBAs does it take...
>
> Lynne,
>
> I'll look back through my "archives" (that means the mess in my filing
> cabinet)
> but I remember from an IS management seminar I attended earlier in the
> year that
> on average 1 DBA per 12-20 programmers is ideal. I don't remember who
> came up
> with that number or how they justified it. Of course, that depends on
how
> active your developement staff is in new development as opposed to
> supporting
> purchased packages, if you have a Warehousing environment you are
> supporting,
> and how self-sufficient are your end-users. We have 3 DBA's supporting
> 50
> programmers in an environment with DB2 UDB for OS/390 and NT, Oracle on
> AIX, and
> SUPRA on OS/390. We support two operating companies with manufacturing
> plants
> and offices world-wide. We have a fairly robust DSS and operational data
> store
> environment set up in it's own DB2 subsystem on OS/390 and our end-users
> create
> 90+% or their own reports using tools like QMF for Windows, Business
> Objects,
> and Infomaker. They have gotten very good at verfiying the accurracy of
> their
> reports against data from online screens. That frees up our developers
> from
> doing report generation. We also have a corporate policy that new
project
> requests from end-users must directly apply to a stated company goal that
> was
> approved by the corporate executive team at the beginning of the year,
> and/or it
> must show a significant ROI. Each department can only have 1 active
> project in
> IS at a time and they must prioritize the projects before submitting them
> to IS.
> We'll work on anything they want as long as it meets the company
objective
> standard and they have given us the priority they want it completed in.
> Our
> DBA's do all of the schema type things (create tables and indices) and
> DBMS type
> tasks (i.e. size Bufferpools, system tuning, ...) for all the DBMS's in
> test,
> QA, and production.
> We use a lot of automation tools in DB2 for tasks like reorgs, image
> copies,
> tuning, and space management. We also cross-train within the technical
> staff.
> For example, if I get swamped with a project, and one of the CICS system
> programmers that has received training in DB2 can step in and help out
> creating
> tables or indexes from the physical ER model, or work on a DB2 problem
> call from
> the help-desk. Likewise, I have been trained on CICS system programming
> and
> SMP/E, so I can help them if they have a major upgrade, maintenance to
> apply,
> or a new CICS region to setup. The cross-training has been a God-send
for
> things like vacations so I don't have to carry a pager with me on
> vacation.
>
> Given the ratio above, It sounds like you are very under-staffed. But,
> it's
> probably not likely that you'll be able to hire 6-8 more DBA's, so you
> also
> have to look at what else you can do to relieve mundane tasks from the
DBA
> staff. Software utilities can be expensive initially, but if you look at
> it in
> comparison to head-count (salary, benefits, insurance, retirement plans,
> ...) it
> may be more cost-effective in the long run.
>
> Good luck and Happy New Year,
> Jim Knisley
> Lead DBA
> Hillenbrand Industries
>
>
>
> the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can
>








Jim Drewe

Re: How many DBAs does it take...
(in response to Lynne Flatley)
Lynne

If you were asking for an informal answer, it would seem that you
need more staff -- either full time employees or contract workers.
The diversity of DBMSes and platforms seem to indicate a need
there. A more complete answer would depend on your actual data
base volume, both current and forecasted. There can alsobe a
problem when program developers design the structures that lead
to data quality issues and low reusability.

If you are asking for a more involved answer, a serious study was
done on this topic by the Gartner Group during the last half of 1999.
There were four Gartner Group Research Notes on this subject
(under the sub-type of Strategic Data Management, or SDM).
R. Burton and R. Paquet authored them. The titles and dates are
as follows: 1) DBA Investment Process Requires Benchmarking
and TCO (TG-09-1846), dated August 31, 1999; 2) Database
Administration Investment Justification Process (TG-09-1847),
dated August 31, 1999; 3) DBA Investment Justification:
Benefits, Budgeting and Risk (TG-09-5649), dated October 27, 1999;
and 4) DBA Investment Justification: ROI and Prioritization
(TG-09-5650), dated November 1, 1999.

Jim Drewe
DBA

= = = = = = = = = = = = = = = = = = = =

Date: Thu, 30 Dec 1999 07:36:27 -0500
From: Lynne Flatley <[login to unmask email]>
Subject: How many DBAs does it take...

to support a company's test and production environments? I'm trying to
gather some statistics or rule-of-thumb for my boss who would like to
increase our staff.

There are 6 of us, including my boss to support IMS, DB2 (for OS/390), MS
SQL Server and we're starting to get into UDB/NT. There are about 30 IMS
databases, 115 DB2 databases and 20 SQL Server databases in production. I
would say that the DB2 and SQL Server databases have 30 - 40 tables each.
We have twice that number of databases in test, given that we typically have
a unit-test database and a QA database in test. I would say that the IMS
tuning and maintenance is pretty much on auto-pilot and the bulk of our time
is spent on DB2 and SQL Server. We don't spend any time pro-actively tuning
and/or monitoring except for space. The developers sometimes do their own
DBA work in test, i.e. create/alter tables, create indices, etc. I would
like us to move away from that (i.e. the DBAs 'own' the structures) but I'm
not sure our staff is large enough to handle the volume of work. There are
approximately 200 developers in our organization.

Are we understaffed?

"A foolish consistency is the hobgoblin of small minds" - Ralph Waldo
Emerson

Lynne A. Flatley
New England Financial
(617) 578-4079
[login to unmask email]

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =



Dennis Raher

How many DBAs does it take...
(in response to Jim Drewe)
Lynn,

I'm speaking from my own experience when I say the best thing you can do for
yourself is to MAKE THE TIME to learn how to use those tools. I can
sympathise with your comment about not having the time, but the same scenario
works in the other direction if you get it started that way. The
maintaining and caring for DB2 databases part of the DBA's job is greatly
reduced in the time and effort required once you start utilizing those
tools, thus leaving more time for the other aspects of the DBA's job. We
now have the routine tasks of image copies, reorg's, tablespace sizing
and disk management automated through the use of those tools, thus
leaving our staff much more time for working with the developers on
designing, enhancing and creating systems to meet the customers ever
growing needs. This is both a better use of the DBA resources, as
well as improving the DBA's effectiveness and satisfaction level.
I would strongly urge you and your staff to make a real
commitment to moving in this direction. You will be amazed at
the improvement in your job situation and satisfaction that it
can make.

Dennis Raher
Corporate Data Administration
Pharmacia & Upjohn Inc