We are doing a proof of concept. In it we want to create a database for
each of our 2000+ customers. In each database we will have about 300
tables and about 800 indexes. Does someone have experience with
informix servers running with this many databases and tables? Does it
work well? What kind of issues will we have or for which we might have
to keep a look out? Each database won't have a lot of data on average
so I am worried about things like the optimizer deciding to sequential
scan for every select no matter what you do. Which might be ok but if
we have several hundred users and several in one company would this
cause a locking error? I know Informix can handle many tables and many
databases I just don't want to be an outlier in what Informix expects.
You always seem to run into more bugs when you are the only one doing
it that way.

Thanks for any help, experience or insight that you may have on this
issue.

bozon said:
> We are doing a proof of concept. In it we want to create a database for
> each of our 2000+ customers. In each database we will have about 300
> tables and about 800 indexes. Does someone have experience with
> informix servers running with this many databases and tables? Does it
> work well? What kind of issues will we have or for which we might have
> to keep a look out? Each database won't have a lot of data on average
> so I am worried about things like the optimizer deciding to sequential
> scan for every select no matter what you do. Which might be ok but if
> we have several hundred users and several in one company would this
> cause a locking error? I know Informix can handle many tables and many
> databases I just don't want to be an outlier in what Informix expects.
> You always seem to run into more bugs when you are the only one doing
> it that way.
>
> Thanks for any help, experience or insight that you may have on this
> issue.

Sounds like a normal instance of SAP, really.

--
Bye now,
Obnoxio

"... no bill is required as no value was provided."
-- Christine Normile

So are you saying it doesn't run well.

Obnoxio The Clown wrote:
> bozon said:
> > We are doing a proof of concept. In it we want to create a database for
> > each of our 2000+ customers. In each database we will have about 300
> > tables and about 800 indexes. Does someone have experience with
> > informix servers running with this many databases and tables? Does it
> > work well? What kind of issues will we have or for which we might have
> > to keep a look out? Each database won't have a lot of data on average
> > so I am worried about things like the optimizer deciding to sequential
> > scan for every select no matter what you do. Which might be ok but if
> > we have several hundred users and several in one company would this
> > cause a locking error? I know Informix can handle many tables and many
> > databases I just don't want to be an outlier in what Informix expects.
> > You always seem to run into more bugs when you are the only one doing
> > it that way.
> >
> > Thanks for any help, experience or insight that you may have on this
> > issue.
>
> Sounds like a normal instance of SAP, really.
>
> --
> Bye now,
> Obnoxio
>
> "... no bill is required as no value was provided."
> -- Christine Normile

bozon wrote:
> So are you saying it doesn't run well.
>
> Obnoxio The Clown wrote:
>> bozon said:
>>> We are doing a proof of concept. In it we want to create a database for
>>> each of our 2000+ customers. In each database we will have about 300
>>> tables and about 800 indexes. Does someone have experience with
>>> informix servers running with this many databases and tables? Does it
>>> work well? What kind of issues will we have or for which we might have
>>> to keep a look out? Each database won't have a lot of data on average
>>> so I am worried about things like the optimizer deciding to sequential
>>> scan for every select no matter what you do. Which might be ok but if
>>> we have several hundred users and several in one company would this
>>> cause a locking error? I know Informix can handle many tables and many
>>> databases I just don't want to be an outlier in what Informix expects.
>>> You always seem to run into more bugs when you are the only one doing
>>> it that way.
>>>
>>> Thanks for any help, experience or insight that you may have on this
>>> issue.
>> Sounds like a normal instance of SAP, really.
>>
>> --
>> Bye now,
>> Obnoxio
>>
>> "... no bill is required as no value was provided."
>> -- Christine Normile
>

It runs fine

SAP have some odd "suggestions" regarding how to set up the $ONCONFIG,
but you should be fine.

Make sure your dictionary cache is pretty big (DD_HASHSIZE and
DD_HASHMAX); seeing as you will have about 2000 * 300 tables so 600,000
tables plus system tables :O

You haven't said what machinery you will be running on, nor the version
of IDS.

bozon said:
> So are you saying it doesn't run well.

Not at all. SAP runs just fine on IDS, you just have to take a different
approach to managing it (i.e., SAP's approach. )

--
Bye now,
Obnoxio

"... no bill is required as no value was provided."
-- Christine Normile

Have seen systems running fine with 100.000 tables (Baan..)
however plan to configure your SHMVIRTSIZE correct (BIG!!)
and all the DD_HASH... params in onconfig also BIG.
do not know them at the top of my head; search for them.
as far as i can remember Art has put a responce or whatever to the
newsgroup which describes them.
DO also test backups level 0 and watch how much memory is required.

> Each database won't have a lot of data on average
> so I am worried about things like the optimizer deciding to sequential

Have a look at optcompind; set it to 0 and it should use an index.

Superboer.

BTW i would if possible try to minimize the # of tables; 600.000 is
really excesive.

bozon schreef:

> We are doing a proof of concept. In it we want to create a database for
> each of our 2000+ customers. In each database we will have about 300
> tables and about 800 indexes. Does someone have experience with
> informix servers running with this many databases and tables? Does it
> work well? What kind of issues will we have or for which we might have
> to keep a look out? Each database won't have a lot of data on average
> so I am worried about things like the optimizer deciding to sequential
> scan for every select no matter what you do. Which might be ok but if
> we have several hundred users and several in one company would this
> cause a locking error? I know Informix can handle many tables and many
> databases I just don't want to be an outlier in what Informix expects.
> You always seem to run into more bugs when you are the only one doing
> it that way.
>
> Thanks for any help, experience or insight that you may have on this
> issue.

bozon wrote:
> We are doing a proof of concept. In it we want to create a database for
> each of our 2000+ customers. In each database we will have about 300
> tables and about 800 indexes. Does someone have experience with
> informix servers running with this many databases and tables? Does it
> work well? What kind of issues will we have or for which we might have
> to keep a look out? Each database won't have a lot of data on average
> so I am worried about things like the optimizer deciding to sequential
> scan for every select no matter what you do. Which might be ok but if
> we have several hundred users and several in one company would this
> cause a locking error? I know Informix can handle many tables and many
> databases I just don't want to be an outlier in what Informix expects.
> You always seem to run into more bugs when you are the only one doing
> it that way.
>
> Thanks for any help, experience or insight that you may have on this
> issue.
>

From IDS 10.0 release notes:
+----------------------------------------------+----------------------+
| Access Capabilities | Maximum Capacity per |
| | System |
+----------------------------------------------+----------------------+
| Maximum databases per Dynamic Server system | 21 million |
+----------------------------------------------+----------------------+
| Maximum tables per Dynamic Server system | 477,102,080 |
+----------------------------------------------+----------------------+
| Maximum number of chunks | 32,767 |
+----------------------------------------------+----------------------+

With 2000 databases and 600,000 tables you are far from the limits. You can have each database in its own dbspace, so restore of one
database is easy.
Every database has its own system catalog, so this will give about 50 extra tables per db. That sums up to 100,000 tables. As TBP mentioned
configuring data dictionary parameters is important. If you add fx 10 UDRs per database it sums up to 20,000 UDRs totally, so you would also
need to configure the UDR cache (PC_HASHSIZE and PC_POOLSIZE).

You don't tell the reasons for this setup, but I think the most common setup for situations like yours, is to have one database, put a
customer_id in each table and then create a view per table per customer, revoke all permissions from the tables and grant permissions to the
views. IDS 10.0 has the INSTEAD_OF trigger, so you can do updates thru views.

regards
Claus