How many DBAs does it take...

Lynette Roberts

How many DBAs does it take...
And I naively thought that it was only my company that short-staffed the
technical groups, DBAs included! We have been attempting to argue for
increased staffing, either permanent or contract, for the last three years,
only to receive the standard "things are tough all over" response. It seems
that our company only appreciates the technical staff when a major problem
crops up, which unfortunately doesn't occur often enough around budget time,
when they decide the headcount figures for next year. Are we understaffed?
YES, YES, and YES, and we have a SAP implementation looming for the year
2000 without any increase in headcount. We are so understaffed (or possibly
have our staffing distributed incorrectly) that I am not sure our
information will provide you with enough justification. We do have a big
problem with burn out and turn over, but our company hasn't figured out why
to date. Anyhow, here are our numbers, and I sincerely hope that your
company will recognize that there is a real need for more skilled people in
this area.

We currently have a staff of 3 DBAs who support Oracle/RS6000/AIX. These 3
are exclusively Oracle support, have 24x7 call-out duties which rotate
weekly. We have 20 Oracle 8.0.5 instances spread across 5 RS6000 servers, 1
Oracle 8.1.5 instance on a single RS6000, and 3 Oracle 7.3.2 instances on 2
RS6000's. Average number of tables per instance is around 150. The DBAs do
all schema changes in the test/production environments, and all data loads
in the production environment. Applications development will do their own
schema modifications in a development environment, along with data loads in
test. Total number of developers for this environment is around 25. The
only scheduled maintenance for this environment is nightly backups.

Our remaining staff of 2 DBAs support our IMS/OS390, DB2/OS390, DB2/RS6000,
DB2/NT, and SQL Server installations. We of course have 24x7 support, and
alternate call-out duties on a weekly basis. Total number of mainframe job
streams that we support is around 500. Most of our mainframe developers
code for IMS as well as DB2 (we have many of IMS/DB2 combo programs), and
the total number of developers for this platform is around 75.

For IMS: We have three IMS subsystems, Prod, Test, and training, with 123
databases in Prod and Test, and 105 databases in training. Total volume of
data for production is approximately 55 Gigabytes (or 21 3390 DASD packs),
same amount for test, and a paltry 2 Gigabytes for training (1 pack). We
reorg all databases once a month in both test and production. The DBAs do
all schema changes in all three environments. Any mass data loads are done
via application programs. Developers do not have security to use any IMS
utilities.

For DB2/OS390: We have two active DB2 subsystems, one for test and one for
production. In production, we have 79 databases, 115 databases in test.
Number of tables in production is 6868, while we have 18,852 in test (due to
three installations of PeopleSoft HRMS). We reorg all production databases
monthly, back them up nightly, and have several other unload, load, FTP,
jobs and such. The DBAs do all schema changes, data loads, and data unloads
in both production and test. Developers do not have security to use the DB2
utilities.

For DB2/RS6000: We have one instance currently, containing 2 databases,
with 250 tables in each database. Total data size is about 1.5 Gigabytes
for each database. The production database is backed up nightly and reorged
monthly. We will be adding an additional instance during the month of
January, and moving the production database into this instance. We will
also be adding a clone of the production database to this instance to be
used for training purposes. By the end of January, we will have 2
instances, and 4 databases, all the same size, running on this platform.
The DBAs do all schema changes, data loads, and data unloads in all
environments. Total number of developers for this platform is around 10.

For DB2/NT: Thankfully, we have just decommissioned the system we had
running on this environment. It now runs on a proprietary DBMS which we do
not support, but will have to convert into Oracle on NT during 2000.

For SQL Server: We have 3 SQL installations of SQL server 6.5. Each
installation has a production and a test database of the same schema/size.
We have a total of 600 production tables and about 2.5 Gigabytes worth of
data for the production installations. The DBAs do all schema changes in
both test and production. We back up everything nightly, and rebuild
indexes weekly. Total number of developers for this environment is around
5.



Philip Gunning

Re: How many DBAs does it take...
(in response to Lynette Roberts)
Lynette, I would definitely recommend getting another DBA or two for the
SAP implementation. Will it be on OS/390, NT, or AIX? Do you have any
time for performance tuning and monitoring? Doesn't sound like it. If you
don't have tools for OS/390 to aid in capturing and analyzing dynamic SQL
you should get one. HTH Phil

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

-----Original Message-----
From: Lynette Roberts [SMTP:[login to unmask email]
Sent: Saturday, January 01, 2000 9:19 AM
To: [login to unmask email]
Subject: Re: How many DBAs does it take...

And I naively thought that it was only my company that short-staffed the
technical groups, DBAs included! We have been attempting to argue for
increased staffing, either permanent or contract, for the last three years,
only to receive the standard "things are tough all over" response. It
seems
that our company only appreciates the technical staff when a major problem
crops up, which unfortunately doesn't occur often enough around budget
time,
when they decide the headcount figures for next year. Are we understaffed?
YES, YES, and YES, and we have a SAP implementation looming for the year
2000 without any increase in headcount. We are so understaffed (or
possibly
have our staffing distributed incorrectly) that I am not sure our
information will provide you with enough justification. We do have a big
problem with burn out and turn over, but our company hasn't figured out why
to date. Anyhow, here are our numbers, and I sincerely hope that your
company will recognize that there is a real need for more skilled people in
this area.

We currently have a staff of 3 DBAs who support Oracle/RS6000/AIX. These 3
are exclusively Oracle support, have 24x7 call-out duties which rotate
weekly. We have 20 Oracle 8.0.5 instances spread across 5 RS6000 servers,
1
Oracle 8.1.5 instance on a single RS6000, and 3 Oracle 7.3.2 instances on 2
RS6000's. Average number of tables per instance is around 150. The DBAs
do
all schema changes in the test/production environments, and all data loads
in the production environment. Applications development will do their own
schema modifications in a development environment, along with data loads in
test. Total number of developers for this environment is around 25. The
only scheduled maintenance for this environment is nightly backups.

Our remaining staff of 2 DBAs support our IMS/OS390, DB2/OS390, DB2/RS6000,
DB2/NT, and SQL Server installations. We of course have 24x7 support, and
alternate call-out duties on a weekly basis. Total number of mainframe job
streams that we support is around 500. Most of our mainframe developers
code for IMS as well as DB2 (we have many of IMS/DB2 combo programs), and
the total number of developers for this platform is around 75.

For IMS: We have three IMS subsystems, Prod, Test, and training, with 123
databases in Prod and Test, and 105 databases in training. Total volume of
data for production is approximately 55 Gigabytes (or 21 3390 DASD packs),
same amount for test, and a paltry 2 Gigabytes for training (1 pack). We
reorg all databases once a month in both test and production. The DBAs do
all schema changes in all three environments. Any mass data loads are done
via application programs. Developers do not have security to use any IMS
utilities.

For DB2/OS390: We have two active DB2 subsystems, one for test and one for
production. In production, we have 79 databases, 115 databases in test.
Number of tables in production is 6868, while we have 18,852 in test (due
to
three installations of PeopleSoft HRMS). We reorg all production databases
monthly, back them up nightly, and have several other unload, load, FTP,
jobs and such. The DBAs do all schema changes, data loads, and data
unloads
in both production and test. Developers do not have security to use the
DB2
utilities.

For DB2/RS6000: We have one instance currently, containing 2 databases,
with 250 tables in each database. Total data size is about 1.5 Gigabytes
for each database. The production database is backed up nightly and
reorged
monthly. We will be adding an additional instance during the month of
January, and moving the production database into this instance. We will
also be adding a clone of the production database to this instance to be
used for training purposes. By the end of January, we will have 2
instances, and 4 databases, all the same size, running on this platform.
The DBAs do all schema changes, data loads, and data unloads in all
environments. Total number of developers for this platform is around 10.

For DB2/NT: Thankfully, we have just decommissioned the system we had
running on this environment. It now runs on a proprietary DBMS which we do
not support, but will have to convert into Oracle on NT during 2000.

For SQL Server: We have 3 SQL installations of SQL server 6.5. Each
installation has a production and a test database of the same schema/size.
We have a total of 600 production tables and about 2.5 Gigabytes worth of
data for the production installations. The DBAs do all schema changes in
both test and production. We back up everything nightly, and rebuild
indexes weekly. Total number of developers for this environment is around
5.








Lynne Flatley

Re: How many DBAs does it take...
(in response to Philip Gunning)
James,

Yes! The references to the Gartner Group studies was one of the things I
was looking for. I will get a look at those research notes and pass the
information on to my boss. It would appear that our group is under-staffed,
although not as much as the groups of some of the other respondents to this
question!

Thanks for your valuable feedback!

> -----Original Message-----
> From: James Drewe [SMTP:[login to unmask email]
> Sent: Friday, December 31, 1999 10:55 AM
> To: [login to unmask email]
> Subject: Re: How many DBAs does it take...
>
> 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]
>
> = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
>
>
>
>
>



Maximiliano Alvarez

Re: How many DBAs does it take...
(in response to Lynne Flatley)
Lynette how many hours per week do you have to work to do all that?

> ----------
> From: Lynette Roberts[SMTP:[login to unmask email]
> Sent: Saturday, January 01, 2000 9:18 AM
> Subject: Re: How many DBAs does it take...
>
> And I naively thought that it was only my company that short-staffed the
> technical groups, DBAs included! We have been attempting to argue for
> increased staffing, either permanent or contract, for the last three
> years,
> only to receive the standard "things are tough all over" response. It
> seems
> that our company only appreciates the technical staff when a major problem
> crops up, which unfortunately doesn't occur often enough around budget
> time,
> when they decide the headcount figures for next year. Are we
> understaffed?
> YES, YES, and YES, and we have a SAP implementation looming for the year
> 2000 without any increase in headcount. We are so understaffed (or
> possibly
> have our staffing distributed incorrectly) that I am not sure our
> information will provide you with enough justification. We do have a big
> problem with burn out and turn over, but our company hasn't figured out
> why
> to date. Anyhow, here are our numbers, and I sincerely hope that your
> company will recognize that there is a real need for more skilled people
> in
> this area.
>
> We currently have a staff of 3 DBAs who support Oracle/RS6000/AIX. These
> 3
> are exclusively Oracle support, have 24x7 call-out duties which rotate
> weekly. We have 20 Oracle 8.0.5 instances spread across 5 RS6000 servers,
> 1
> Oracle 8.1.5 instance on a single RS6000, and 3 Oracle 7.3.2 instances on
> 2
> RS6000's. Average number of tables per instance is around 150. The DBAs
> do
> all schema changes in the test/production environments, and all data loads
> in the production environment. Applications development will do their own
> schema modifications in a development environment, along with data loads
> in
> test. Total number of developers for this environment is around 25. The
> only scheduled maintenance for this environment is nightly backups.
>
> Our remaining staff of 2 DBAs support our IMS/OS390, DB2/OS390,
> DB2/RS6000,
> DB2/NT, and SQL Server installations. We of course have 24x7 support, and
> alternate call-out duties on a weekly basis. Total number of mainframe
> job
> streams that we support is around 500. Most of our mainframe developers
> code for IMS as well as DB2 (we have many of IMS/DB2 combo programs), and
> the total number of developers for this platform is around 75.
>
> For IMS: We have three IMS subsystems, Prod, Test, and training, with 123
> databases in Prod and Test, and 105 databases in training. Total volume
> of
> data for production is approximately 55 Gigabytes (or 21 3390 DASD packs),
> same amount for test, and a paltry 2 Gigabytes for training (1 pack). We
> reorg all databases once a month in both test and production. The DBAs do
> all schema changes in all three environments. Any mass data loads are
> done
> via application programs. Developers do not have security to use any IMS
> utilities.
>
> For DB2/OS390: We have two active DB2 subsystems, one for test and one
> for
> production. In production, we have 79 databases, 115 databases in test.
> Number of tables in production is 6868, while we have 18,852 in test (due
> to
> three installations of PeopleSoft HRMS). We reorg all production
> databases
> monthly, back them up nightly, and have several other unload, load, FTP,
> jobs and such. The DBAs do all schema changes, data loads, and data
> unloads
> in both production and test. Developers do not have security to use the
> DB2
> utilities.
>
> For DB2/RS6000: We have one instance currently, containing 2 databases,
> with 250 tables in each database. Total data size is about 1.5 Gigabytes
> for each database. The production database is backed up nightly and
> reorged
> monthly. We will be adding an additional instance during the month of
> January, and moving the production database into this instance. We will
> also be adding a clone of the production database to this instance to be
> used for training purposes. By the end of January, we will have 2
> instances, and 4 databases, all the same size, running on this platform.
> The DBAs do all schema changes, data loads, and data unloads in all
> environments. Total number of developers for this platform is around 10.
>
> For DB2/NT: Thankfully, we have just decommissioned the system we had
> running on this environment. It now runs on a proprietary DBMS which we
> do
> not support, but will have to convert into Oracle on NT during 2000.
>
> For SQL Server: We have 3 SQL installations of SQL server 6.5. Each
> installation has a production and a test database of the same schema/size.
> We have a total of 600 production tables and about 2.5 Gigabytes worth of
> data for the production installations. The DBAs do all schema changes in
> both test and production. We back up everything nightly, and rebuild
> indexes weekly. Total number of developers for this environment is around
> 5.
>



Bill

Re: How many DBAs does it take...
(in response to Maximiliano Alvarez)
Here's some info:

DB2 V5.1
7 DB2 subsystems all non-data sharing
SAP R/2 - Prod, QA, Test
non-SAP - Prod, QA, Test (includes DW)
Experimental - DBA new release, maint, etc.

Total structures:
327 databases
6100 tables
5300 indexes
most are 1 table/tablespace
most large tables use hardware compression including ALL the SAP data

Total dasd used by DB2 structures:
200-250 GB for SAP
50-75 GB for DW

DB2 DBA staff:
1.0 (me) SMPE install of all DB2 products
install all vendor DB2 tools (BMC)
system tuning
DBA for SAP
0.5 tuning/development for non-SAP environment
DW maintenance/tuning

As you can see we have 1.5 people allocated for all DB2 activity with
myself on-call 7x24 for all DB2 system related and SAP related problems the
past 7 years. Fortunately, I average less than 1 off-hours call per month.
Our SAP environment will be migrating to R/3 using Oracle on HP/UX. The DW
will follow suit shortly after and DB2 as well as OS/390 will go away.

We have used DB2 since 1987 (R1.3) and have never had more than 2 DBAs
assigned for all DB2 support. All DB2 development has been using C/S tools
for small applications since SAP R/2 was installed around 1992.



Lynne Flatley

Re: How many DBAs does it take...
(in response to Bill)
Do they let you take vacations?! :-)

> -----Original Message-----
> From: Bill [SMTP:[login to unmask email]
> Sent: Tuesday, January 04, 2000 7:08 PM
> To: [login to unmask email]; [login to unmask email]
> Cc: Bill
> Subject: Re: How many DBAs does it take...
>
> Here's some info:
>
> DB2 V5.1
> 7 DB2 subsystems all non-data sharing
> SAP R/2 - Prod, QA, Test
> non-SAP - Prod, QA, Test (includes DW)
> Experimental - DBA new release, maint, etc.
>
> Total structures:
> 327 databases
> 6100 tables
> 5300 indexes
> most are 1 table/tablespace
> most large tables use hardware compression including ALL the SAP data
>
> Total dasd used by DB2 structures:
> 200-250 GB for SAP
> 50-75 GB for DW
>
> DB2 DBA staff:
> 1.0 (me) SMPE install of all DB2 products
> install all vendor DB2 tools (BMC)
> system tuning
> DBA for SAP
> 0.5 tuning/development for non-SAP environment
> DW maintenance/tuning
>
> As you can see we have 1.5 people allocated for all DB2 activity with
> myself on-call 7x24 for all DB2 system related and SAP related problems
> the
> past 7 years. Fortunately, I average less than 1 off-hours call per month.
> Our SAP environment will be migrating to R/3 using Oracle on HP/UX. The DW
> will follow suit shortly after and DB2 as well as OS/390 will go away.
>
> We have used DB2 since 1987 (R1.3) and have never had more than 2 DBAs
> assigned for all DB2 support. All DB2 development has been using C/S tools
> for small applications since SAP R/2 was installed around 1992.