What are good questions for a DBA?

[James Conrad StJohn Foreman]

After 3 years of using DB2 on Linux, I'm leaving my current employers
to go work for a SQL Server shop instead. In order to find my
replacement, they're trying to put together a set of questions to get
both some understanding of how wide candidates knowledge is, and how
much DB2 specifics they know. Of the questions below, how many do you
think are useful in determining if you've got somebody capable of
keeping a DB2 instance up, developing new structures as new business
problems arrive, and writing useful/performant SQL? (these therefore
range from the inane to quite specific - how many do people feel are
actually unfair? or should I just get some questions from Celko?)
[I'm not trawling for a job or for other recruitment purposes here,
honest...]

1. I've got a bookings table with 20 million rows, broken down by
product, region, date and customer number. It's not loading new data
or querying very quickly. What should I do?
2. Do you like stored procedures?
3. What's an inline view?
4. How do you do a running sum when GROUP BY just gives you a total
per row?
5. Do you prefer star schemas or snowflake schemas? Why?
6. I have a table with three columns: date (date), country
(varchar(10)), bookings (int).
Write me a query that gives me a total by country of bookings made
in the last 2 weeks, AND that displays the total number of bookings
made in the last 2 weeks too.
7. Do you know what an MQT is?
8. If all my customers have been given x-y coordinates that describe
the position of their house (ie every line in customer has x_coordinate
(int) and y_coordinate (int), and Birmingham is at (35000, 55000), and
Manchester is at (25000, 65000) and York is at (33000, 70000), then,
assuming Euclidean geometry, give me a WHERE clause against the
customer table to return only customers who live north of Birmingham
and south of a line that passes through both York and Manchester.
9. I have two sets of customers with the same 15 columns. What's the
best way to return only those customers that are in both sets, for whom
the data is the same in both sets?
10. Give me a way to get the database to check on a daily basis if
something surprising is happening to sales when compared with last year
at the same time.
11. DB2 UDB LUW 8.1.4. For the next six months, ten new tables need
to be created every day and automated routines set up to load them
every day after they're created. You are the only person in the company
responsible for administering the database. Do you use a DMS or SMS
tablespace? Why?
12. Your manager has written some code that 'should take 5 minutes to
run'. It takes 4 hours. What do you do?
13. Cube Views?
14. What SQL that you've written are you most proud of?
15. What are the disadvantages of HADR vis a vis RAC?
16. SQL Server federation vs DB2 Information Integrator - any
thoughts?
Thanks

JCSJF

[Mark A]

"James Conrad StJohn Foreman" <ja************ **********@gmai l.comwrote in
message news:11******** *************@m 73g2000cwd.goog legroups.com...

After 3 years of using DB2 on Linux, I'm leaving my current employers
to go work for a SQL Server shop instead. In order to find my
replacement, they're trying to put together a set of questions to get
both some understanding of how wide candidates knowledge is, and how
much DB2 specifics they know. Of the questions below, how many do you
think are useful in determining if you've got somebody capable of
keeping a DB2 instance up, developing new structures as new business
problems arrive, and writing useful/performant SQL? (these therefore
range from the inane to quite specific - how many do people feel are
actually unfair? or should I just get some questions from Celko?)
[I'm not trawling for a job or for other recruitment purposes here,
honest...]

1. I've got a bookings table with 20 million rows, broken down by
product, region, date and customer number. It's not loading new data
or querying very quickly. What should I do?
2. Do you like stored procedures?
3. What's an inline view?
4. How do you do a running sum when GROUP BY just gives you a total
per row?
5. Do you prefer star schemas or snowflake schemas? Why?
6. I have a table with three columns: date (date), country
(varchar(10)), bookings (int).
Write me a query that gives me a total by country of bookings made
in the last 2 weeks, AND that displays the total number of bookings
made in the last 2 weeks too.
7. Do you know what an MQT is?
8. If all my customers have been given x-y coordinates that describe
the position of their house (ie every line in customer has x_coordinate
(int) and y_coordinate (int), and Birmingham is at (35000, 55000), and
Manchester is at (25000, 65000) and York is at (33000, 70000), then,
assuming Euclidean geometry, give me a WHERE clause against the
customer table to return only customers who live north of Birmingham
and south of a line that passes through both York and Manchester.
9. I have two sets of customers with the same 15 columns. What's the
best way to return only those customers that are in both sets, for whom
the data is the same in both sets?
10. Give me a way to get the database to check on a daily basis if
something surprising is happening to sales when compared with last year
at the same time.
11. DB2 UDB LUW 8.1.4. For the next six months, ten new tables need
to be created every day and automated routines set up to load them
every day after they're created. You are the only person in the company
responsible for administering the database. Do you use a DMS or SMS
tablespace? Why?
12. Your manager has written some code that 'should take 5 minutes to
run'. It takes 4 hours. What do you do?
13. Cube Views?
14. What SQL that you've written are you most proud of?
15. What are the disadvantages of HADR vis a vis RAC?
16. SQL Server federation vs DB2 Information Integrator - any
thoughts?

Thanks

JCSJF

Most of the questions are very open-ended and highly subjective, and I doubt
that the person reading the answers from an answer sheet will be able to
understand equally valid answers that don't agree with the answer sheet.

The questions seem highly skewed toward decision support databases, but
maybe you are looking for someone with those skills. But if you are, I don't
see any questions about intra or inter partition parallelism which is much
more important than most of the questions you asked..

Seem to be a lot of questions about complex SQL. Are you looking for a DBA
or an SQL person?

If you are looking for a DB2 DBA, whey do they need to know about Oracle
RAC?

BTW, what is an inline view in DB2?

[Bob Jones]

"James Conrad StJohn Foreman" <ja************ **********@gmai l.comwrote in
message news:11******** *************@m 73g2000cwd.goog legroups.com...

After 3 years of using DB2 on Linux, I'm leaving my current employers
to go work for a SQL Server shop instead. In order to find my
replacement, they're trying to put together a set of questions to get
both some understanding of how wide candidates knowledge is, and how
much DB2 specifics they know. Of the questions below, how many do you
think are useful in determining if you've got somebody capable of
keeping a DB2 instance up, developing new structures as new business
problems arrive, and writing useful/performant SQL? (these therefore
range from the inane to quite specific - how many do people feel are
actually unfair? or should I just get some questions from Celko?)
[I'm not trawling for a job or for other recruitment purposes here,
honest...]

1. I've got a bookings table with 20 million rows, broken down by
product, region, date and customer number. It's not loading new data
or querying very quickly. What should I do?
2. Do you like stored procedures?
3. What's an inline view?
4. How do you do a running sum when GROUP BY just gives you a total
per row?
5. Do you prefer star schemas or snowflake schemas? Why?
6. I have a table with three columns: date (date), country
(varchar(10)), bookings (int).
Write me a query that gives me a total by country of bookings made
in the last 2 weeks, AND that displays the total number of bookings
made in the last 2 weeks too.
7. Do you know what an MQT is?
8. If all my customers have been given x-y coordinates that describe
the position of their house (ie every line in customer has x_coordinate
(int) and y_coordinate (int), and Birmingham is at (35000, 55000), and
Manchester is at (25000, 65000) and York is at (33000, 70000), then,
assuming Euclidean geometry, give me a WHERE clause against the
customer table to return only customers who live north of Birmingham
and south of a line that passes through both York and Manchester.
9. I have two sets of customers with the same 15 columns. What's the
best way to return only those customers that are in both sets, for whom
the data is the same in both sets?
10. Give me a way to get the database to check on a daily basis if
something surprising is happening to sales when compared with last year
at the same time.
11. DB2 UDB LUW 8.1.4. For the next six months, ten new tables need
to be created every day and automated routines set up to load them
every day after they're created. You are the only person in the company
responsible for administering the database. Do you use a DMS or SMS
tablespace? Why?
12. Your manager has written some code that 'should take 5 minutes to
run'. It takes 4 hours. What do you do?
13. Cube Views?
14. What SQL that you've written are you most proud of?
15. What are the disadvantages of HADR vis a vis RAC?
16. SQL Server federation vs DB2 Information Integrator - any
thoughts?

If you think these questions are going to be enough, good luck. I have a
certified DBA who can't do squat in front of a keyboard.

[Mark A]

"Bob Jones" <em***@me.notwr ote in message
news:LD******** *********@newss vr29.news.prodi gy.net...

>
If you think these questions are going to be enough, good luck. I have a
certified DBA who can't do squat in front of a keyboard.

Hmm. None of the questions the OP listed is anything like those on the IBM
DB2 DBA Certification test.

If your DBA can't do squat in front of keyboard, is that because they don't
Linux/UNIX, vi, etc, or some other reason.

[Mark Townsend]

James Conrad StJohn Foreman wrote:

15. What are the disadvantages of HADR vis a vis RAC?

This doesn't seem to be a valid question. I'm not sure of it's relevance
to the interview process, but assuming it has some, then it should be
HADR vs Oracle Data Guard. RAC is not a DR solution.

[Bob Jones]

"Mark A" <no****@nowhere .comwrote in message
news:aZ******** *************** *******@comcast .com...

"Bob Jones" <em***@me.notwr ote in message
news:LD******** *********@newss vr29.news.prodi gy.net...

>>
If you think these questions are going to be enough, good luck. I have a
certified DBA who can't do squat in front of a keyboard.

Hmm. None of the questions the OP listed is anything like those on the IBM
DB2 DBA Certification test.

Those questions are not enough regardless.

If your DBA can't do squat in front of keyboard, is that because they
don't Linux/UNIX, vi, etc, or some other reason.

Not just UNIX, but basic DBA stuff like configurating CLI, moving
containers, upgrading, and most of all using the right tool for the job.

Certifications may look good on paper, but they are no replacement for real
experience.

[DA Morgan]

James Conrad StJohn Foreman wrote:

After 3 years of using DB2 on Linux, I'm leaving my current employers
to go work for a SQL Server shop instead. In order to find my
replacement, they're trying to put together a set of questions to get
both some understanding of how wide candidates knowledge is, and how
much DB2 specifics they know. Of the questions below, how many do you
think are useful in determining if you've got somebody capable of
keeping a DB2 instance up, developing new structures as new business
problems arrive, and writing useful/performant SQL? (these therefore
range from the inane to quite specific - how many do people feel are
actually unfair? or should I just get some questions from Celko?)
[I'm not trawling for a job or for other recruitment purposes here,
honest...]

1. I've got a bookings table with 20 million rows, broken down by
product, region, date and customer number. It's not loading new data
or querying very quickly. What should I do?
2. Do you like stored procedures?
3. What's an inline view?
4. How do you do a running sum when GROUP BY just gives you a total
per row?
5. Do you prefer star schemas or snowflake schemas? Why?
6. I have a table with three columns: date (date), country
(varchar(10)), bookings (int).
Write me a query that gives me a total by country of bookings made
in the last 2 weeks, AND that displays the total number of bookings
made in the last 2 weeks too.
7. Do you know what an MQT is?
8. If all my customers have been given x-y coordinates that describe
the position of their house (ie every line in customer has x_coordinate
(int) and y_coordinate (int), and Birmingham is at (35000, 55000), and
Manchester is at (25000, 65000) and York is at (33000, 70000), then,
assuming Euclidean geometry, give me a WHERE clause against the
customer table to return only customers who live north of Birmingham
and south of a line that passes through both York and Manchester.
9. I have two sets of customers with the same 15 columns. What's the
best way to return only those customers that are in both sets, for whom
the data is the same in both sets?
10. Give me a way to get the database to check on a daily basis if
something surprising is happening to sales when compared with last year
at the same time.
11. DB2 UDB LUW 8.1.4. For the next six months, ten new tables need
to be created every day and automated routines set up to load them
every day after they're created. You are the only person in the company
responsible for administering the database. Do you use a DMS or SMS
tablespace? Why?
12. Your manager has written some code that 'should take 5 minutes to
run'. It takes 4 hours. What do you do?
13. Cube Views?
14. What SQL that you've written are you most proud of?
15. What are the disadvantages of HADR vis a vis RAC?
16. SQL Server federation vs DB2 Information Integrator - any
thoughts?
Thanks

JCSJF

Assuming your DBA is intended to perform DBA duties how about?

1. What are the things that can go wrong that may require recovery
and/or restoration. What are the various strategies that you would
recommend to mitigate the dangers thing that can go wrong and for each
contrast the strengths and weaknesses.

2. Describe the architecture of DB2 and the basic concepts for locking
and the transaction model.

3. What is the most oldest version of DB2 on which you have worked? The
newest? Contrast them in terms of capabilities and features.

4. What is the worst situation you have ever faced as a DBA and what
did you personally do to solve it?

5. What is the largest database on which you have worked in terms of the
number of objects and the amount of data? Describe the challenges faced.

6. What is the single biggest mistake you see when you look at DB2
databases and what do you recommend to rectify it.

Also open ended. But pointedly intended to ferret out the poser.

And as Mark Townsend said ... DB2 has no technology equivalent to RAC so
bringing up an Oracle product serves no obvious purpose.
--
Daniel Morgan
University of Washington

[Mark A]

"Bob Jones" <em***@me.notwr ote in message
news:X5******** ********@newssv r21.news.prodig y.com...

Certifications may look good on paper, but they are no replacement for
real experience.

No one is suggesting that there is any substitute for experience, but it is
useful in an interview to ask the applicant technical questions as one part
of the qualification process.

In my "experience " the questions on the DB2 DBA certification exam are
specifically designed so they are difficult to answer if one has not had
experience with the product. But obviously, there are no perfect questions.

[James Conrad StJohn Foreman]

Thanks Mark,

>
Most of the questions are very open-ended and highly subjective, and I doubt
that the person reading the answers from an answer sheet will be able to
understand equally valid answers that don't agree with the answer sheet.

Indeed. These are questions intended to be asked at interview, rather
than on paper. I tend to like open ended questions because there's
more opportunity for the candidate to get themselves in/out of trouble
than where there's one obvious right answer.

The questions seem highly skewed toward decision support databases, but
maybe you are looking for someone with those skills. But if you are, I don't
see any questions about intra or inter partition parallelism which is much
more important than most of the questions you asked..

Thanks. I'll follow up on that.

>
Seem to be a lot of questions about complex SQL. Are you looking for a DBA
or an SQL person?

Very good point, and my current employer has a certain level of
ambiguity about this. I guess I would have doubts about any DBA that
wasn't highly competent with SQL, but maybe that's an unfair
constraint.

>
If you are looking for a DB2 DBA, whey do they need to know about Oracle
RAC?

Some advice we hear is that a DBA is a DBA (or sometimes, a SQL
Server/Oracle DBA might be closer to what we want than a DB2 on z/OS
DBA) - trying to ferret out experience of clustering, federation, or
any other high availability or partitioned solution (yes, I know
there's a lot of concepts packed up in there that could be successfully
taken apart...)

BTW, what is an inline view in DB2?

OK:
SELECT max(bookings)
FROM (SELECT booking_date, count(bookings)
FROM generic.booking _table
GROUP BY booking_date) as a;
This gives me the most bookings I've had in one day. What's being
referred to as a is an inline view.

Or at least, that's what we've been told to call it in the past.

[James Conrad StJohn Foreman]

DA Morgan wrote:

>
And as Mark Townsend said ... DB2 has no technology equivalent to RAC so
bringing up an Oracle product serves no obvious purpose.
--
Daniel Morgan
University of Washington

Thanks Daniel. 4 & 6 are particularly nice questions. As to the RAC
question, as I think I said in my reply to Mark, it's more a general
question to get an idea of what they've touched in that general space.
Plus when you have salespeople telling you that HADR is the same as
RAC, and you have your own misgivings about believing this, it's always
interesting to canvas a few other opinions.