Re: Performance of ODBC vs. other access methods

On 9/6/06, Elvis Budimlic <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

> what extra work is needed to admin DB2 on AIX compared to 100% SQL on
i5/OS?

Take a look at slide 60 on this IBM powerpoint:
http://www-304.ibm.com/jct09002c/university/scholars/products/iseries/images
/module8.ppt

There are couple of good sections on slides just before that as well.
That should suffice.

The question has to do with comparing DB2 on AIX to DB2 on i5/OS.  I
have my doubts that the items from slide 60 actually apply to a modern
database like DB2, but there is a lot I dont know.

manage DASD space allocation

review table space allocations and extents

review and balance indexes

application rebinding

maintain database integrity

update database statistics

Syncronized OS and DB user security

Reload data for hardware and software upgrades

> how do MTIs work? Does the system retain ( and maintain ) them once
> they are no longer in use? A programmer or sys admin should not have
> to keep track of the indexes the database needs to satisfy all the
> queries being thrown at it. That is the job of a ton of system code
> that runs on a modern, market priced, quad core system.

> http://www-03.ibm.com/systems/p/hardware/entry/520q/91311k7a.html

MTIs will be built selectively by the system for the queries used frequently
that could benefit from it.  There are additional cost-to-benefit
considerations before actual decision is made but you can read up on that at
InfoCenter.  Once all jobs/queries that are using the MTI leave the system
and those queries are purged from the plan cache, MTI may be dropped.
Normally though, they'll stick around till IPL.

System will never and should never build all possible indexes.  That is the
job of a DBA.  There is always a trade-off and you can not expect the system
to take everything into consideration.  I mean, what if you (or system per
your contention) build 3000 indexes on your table, and your read-only
queries scream, but your batch process update takes 18 hours?

I dont see why not. The system knows how many indexes it had to create
and how long it took. It also knows how much effort it took to
maintain the index and how frequently it is used. Put it all together
and run a lot of code that weighs all the factors and spits out an
answer.   Read this as you want. My point is that by selling crippled
i5 systems IBM makes it hard to add features to i5/OS.  Look at
associative arrays in PHP. How much better would RPG be if its arrays
were boundless, the index could be a key value and each entry of the
array could itself contain a boundless array? It would be great, but
it takes a lot of CPU to support such a thing.

-Steve