Multiple company support on OS/390 and DB2.

Sibimon Philip

Multiple company support on OS/390 and DB2.
With happy new year to everyone, let me start my first question this year.

We are trying to support multiple companies data in our OS/390 and DB2. I am
sure that somebody in this list might be doing this already. My questions is

1. How do you separate one companies data from other?
2. Do you use separated subsystem of for each company or multiple table with
same name but with different schema/owner.
3. How do you manage the programs, since there is some customization
required for some company.

I just started thinking about this, so any additional information on this
will be helpful.


Thanks..sibi



Walter Trovijo Jr

Re: Multiple company support on OS/390 and DB2.
(in response to Sibimon Philip)
Sibi,

It has been used to work fine for us:

We run all companies in the same subsystem (actually 3). Database names,
collections, plans and creators contain some letters to
identify each company - 3 letters of database name, 3 letters of collection
name and one letter of creator and plan name.
All programs work with unqualified names which are actually synonyms of real
table names. Synonyms are mapped to
each company at bind time, using OWNER keyword of bind package. We have
coded a rexx program that act as a
front end to bind command, just to enforce standards, like collection name,
owner , qualifier and so on.
Our programs are the same for all companies, so we precompile/compile just
once and bind one time for each company
into company´s collection. There´s some small functionality specific to each
company, but this is handled by application logic.


Hope this helps,
Walter Trovijo.

> We are trying to support multiple companies data in our OS/390 and DB2. I
am
> sure that somebody in this list might be doing this already. My questions
is
>
> 1. How do you separate one companies data from other?
> 2. Do you use separated subsystem of for each company or multiple table
with
> same name but with different schema/owner.
> 3. How do you manage the programs, since there is some customization
> required for some company.
>
> I just started thinking about this, so any additional information on this
> will be helpful.



Dave Nance

Re: Multiple company support on OS/390 and DB2.
(in response to Walter Trovijo Jr)
Sibi,
We have something similar at our company. We sell an application to our customers and the practice had always been to create another cics region, another set of the same tables(different creator), another set of the same load modules in a different loadlib, etc... I am, currently, trying to get them to change this process. Why not one CICS region, one set of tables? This can be done with minimal changes to the application itself.
First, recreate a copy of your tables with an additional column, something like client_id(this column would be populated with a unique identifier for each client and made a secondary auth group with the correct people from the clients connected to them), and a different table name, maybe prefix them all with a t. Create a view on these new tables selecting all columns, except the new column, name the views same as the existing table names where client_id = current sqlid. Make a change to the logon process that sets a users current sqlid. Set up a skeleton program that gets executed by every cics transaction,that hits a table with the client_id, transaction, and prog_name to find if there is a different program for this particular client.
This is a pretty rough draft of what could be done. Yes it gives you a table as a single point of failure for all your clients, but that can, also, be worked around with partitioning and other tricks, besides your mvs system is a single point of failure anyway, right.

>>> [login to unmask email] 01/10/02 12:05PM >>>
With happy new year to everyone, let me start my first question this year.

We are trying to support multiple companies data in our OS/390 and DB2. I am
sure that somebody in this list might be doing this already. My questions is

1. How do you separate one companies data from other?
2. Do you use separated subsystem of for each company or multiple table with
same name but with different schema/owner.
3. How do you manage the programs, since there is some customization
required for some company.

I just started thinking about this, so any additional information on this
will be helpful.


Thanks..sibi




This message, including any attachments, is intended solely for the use of the named
recipient(s) and may contain confidential and/or privileged information. Any
unauthorized review, use, disclosure or distribution of this communications is expressly
prohibited. If you are not the intended recipient, please contact the sender by reply e-mail
and destroy any and all copies of the original message. Thank you.



Dale Smock

Re: Multiple company support on OS/390 and DB2.
(in response to Dave Nance)
We use different owners and databases including a business code, with the
same tablespace/table/view names. Some are in a shared DB2 system and some
are separate, depends on if they are running on the same or different
LPAR's. Programs are directed to different plan/collection bound with the
respective table owners for the business. If a business needs access to
another's table, we create a view directed to the other owner (plus grants
for the required access).

Batch uses different JCL or a JCL parameter to point to the plan. We use a
separate CICS for each business with a different RCT pointing to the
respective plans, or by using the SET CURRENT PACKAGESET command to set the
collection in the program. In a previous job, I used the dynamic plan
allocation exit in CICS to do this, which saves coding in the programs, but
adds some overhead.

Dale Smock
BMG

-----Original Message-----
From: Philip, Sibimon [mailto:[login to unmask email]
Sent: Thursday, January 10, 2002 12:05 PM
To: [login to unmask email]
Subject: Multiple company support on OS/390 and DB2.


With happy new year to everyone, let me start my first question this year.

We are trying to support multiple companies data in our OS/390 and DB2. I am
sure that somebody in this list might be doing this already. My questions is

1. How do you separate one companies data from other?
2. Do you use separated subsystem of for each company or multiple table with
same name but with different schema/owner.
3. How do you manage the programs, since there is some customization
required for some company.

I just started thinking about this, so any additional information on this
will be helpful.


Thanks..sibi








tim malamphy

Re: Multiple company support on OS/390 and DB2.
(in response to Dale Smock)
DB2 would allow you to do this in a single or multiple
regions. OS/390 will allow you to do this in a single
or multiple LPARs.

A major factor to consider are the service level
agreements and chargeback you have with the other
companies, as well as the relationship between the
companies' staffs (friendly or adversarial). As long
as you're only dealing with a couple of outside
companies, I'd recommend a separate region for each
company. Especially test regions. You're company
probably isn't going to want to take an outage on its
region, because you have to bounce DB2 for the other
company. When it's time to upgrade, how hard will it
be to get both company's to support the upgrade on the
same date? What if the other company's apps fail
after an upgrade and it has to be rolled back?
Do both companies use the same quality assurance
procedures? What if one company has an app that runs
a long time without commits? Will it effect any
system wide utilities needed by the other?
Yes, these are the same issues you deal with now
between different departments, but there is usually
ONE person somewhere up the management chain who can
make a decision. With multiple companies, you'll be
dealing with several bosses.

Bottom line...
Due to mostly political factors, and assuming they
aren't sharing any data between apps,
I'd go with multiple regions. Except for weekend
upgrades, I've found it a lot easier to put each
political organization in their own region. I spent
WAY too much time trying to coordinate different
companies scheduling needs, than actually doing the
extra support required by having extra regions. I'd
even stick them in their own LPAR if I had the
capacity.

Tim
--- "Philip, Sibimon" <[login to unmask email]> wrote:
> With happy new year to everyone, let me start my
> first question this year.
>
> We are trying to support multiple companies data in
> our OS/390 and DB2. I am
> sure that somebody in this list might be doing this
> already. My questions is
>
> 1. How do you separate one companies data from
> other?
> 2. Do you use separated subsystem of for each
> company or multiple table with
> same name but with different schema/owner.
> 3. How do you manage the programs, since there is
> some customization
> required for some company.
>
> I just started thinking about this, so any
> additional information on this
> will be helpful.
>
>
> Thanks..sibi
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/



Philip Nelson

Re: Multiple company support on OS/390 and DB2.
(in response to tim malamphy)
David,

The reason we use separate tables with different schemas is for legal
reasons. I suppose the financial services industry regulators are
probably stricter than most when it comes to data separation !!!

Sibi,

You'd be best to check this out before embarking on the single table
method.

Phil

On Thu, 2002-01-10 at 19:44, David Nance wrote:
> Sibi,
> We have something similar at our company. We sell an application to our customers and the practice had always been to create another cics region, another set of the same tables(different creator), another set of the same load modules in a different loadlib, etc... I am, currently, trying to get them to change this process. Why not one CICS region, one set of tables? This can be done with minimal changes to the application itself.
> First, recreate a copy of your tables with an additional column, something like client_id(this column would be populated with a unique identifier for each client and made a secondary auth group with the correct people from the clients connected to them), and a different table name, maybe prefix them all with a t. Create a view on these new tables selecting all columns, except the new column, name the views same as the existing table names where client_id = current sqlid. Make a change to the logon process that sets a users current sqlid. Set up a skeleton program that gets executed by every cics transaction,that hits a table with the client_id, transaction, and prog_name to find if there is a different program for this particular client.
> This is a pretty rough draft of what could be done. Yes it gives you a table as a single point of failure for all your clients, but that can, also, be worked around with partitioning and other tricks, besides your mvs system is a single point of failure anyway, right.
>
> >>> [login to unmask email] 01/10/02 12:05PM >>>
> With happy new year to everyone, let me start my first question this year.
>
> We are trying to support multiple companies data in our OS/390 and DB2. I am
> sure that somebody in this list might be doing this already. My questions is
>
> 1. How do you separate one companies data from other?
> 2. Do you use separated subsystem of for each company or multiple table with
> same name but with different schema/owner.
> 3. How do you manage the programs, since there is some customization
> required for some company.
>
> I just started thinking about this, so any additional information on this
> will be helpful.
>
>
> Thanks..sibi
>
>
>
>
> This message, including any attachments, is intended solely for the use of the named
> recipient(s) and may contain confidential and/or privileged information. Any
> unauthorized review, use, disclosure or distribution of this communications is expressly
> prohibited. If you are not the intended recipient, please contact the sender by reply e-mail
> and destroy any and all copies of the original message. Thank you.
>
>
>



Dale Smock

Re: Multiple company support on OS/390 and DB2.
(in response to Philip Nelson)
Separate tables also makes availability, security, and chargeback for disk
space easier to separate by company.

Dale Smock
BMG

-----Original Message-----
From: Philip Nelson (DBA) [mailto:[login to unmask email]
Sent: Friday, January 11, 2002 12:52 PM
To: [login to unmask email]
Subject: Re: Multiple company support on OS/390 and DB2.


David,

The reason we use separate tables with different schemas is for legal
reasons. I suppose the financial services industry regulators are
probably stricter than most when it comes to data separation !!!

Sibi,

You'd be best to check this out before embarking on the single table
method.

Phil

On Thu, 2002-01-10 at 19:44, David Nance wrote:
> Sibi,
> We have something similar at our company. We sell an application to our
customers and the practice had always been to create another cics region,
another set of the same tables(different creator), another set of the same
load modules in a different loadlib, etc... I am, currently, trying to get
them to change this process. Why not one CICS region, one set of tables?
This can be done with minimal changes to the application itself.
> First, recreate a copy of your tables with an additional column,
something like client_id(this column would be populated with a unique
identifier for each client and made a secondary auth group with the correct
people from the clients connected to them), and a different table name,
maybe prefix them all with a t. Create a view on these new tables selecting
all columns, except the new column, name the views same as the existing
table names where client_id = current sqlid. Make a change to the logon
process that sets a users current sqlid. Set up a skeleton program that gets
executed by every cics transaction,that hits a table with the client_id,
transaction, and prog_name to find if there is a different program for this
particular client.
> This is a pretty rough draft of what could be done. Yes it gives you a
table as a single point of failure for all your clients, but that can, also,
be worked around with partitioning and other tricks, besides your mvs system
is a single point of failure anyway, right.
>
> >>> [login to unmask email] 01/10/02 12:05PM >>>
> With happy new year to everyone, let me start my first question this
year.
>
> We are trying to support multiple companies data in our OS/390 and DB2. I
am
> sure that somebody in this list might be doing this already. My questions
is
>
> 1. How do you separate one companies data from other?
> 2. Do you use separated subsystem of for each company or multiple table
with
> same name but with different schema/owner.
> 3. How do you manage the programs, since there is some customization
> required for some company.
>
> I just started thinking about this, so any additional information on this
> will be helpful.
>
>
> Thanks..sibi
>
>
>


>
> This message, including any attachments, is intended solely for the use of
the named
> recipient(s) and may contain confidential and/or privileged information.
Any
> unauthorized review, use, disclosure or distribution of this
communications is expressly
> prohibited. If you are not the intended recipient, please contact the
sender by reply e-mail
> and destroy any and all copies of the original message. Thank you.
>
>
>










Sibimon Philip

Re: Multiple company support on OS/390 and DB2.
(in response to Dale Smock)
Thanks to all. I hope I will get some more mail.

Based on the e-mail, I got 3 solutions.

1. Create separate database and create tables with different schema/owner.
Pros
- More security at table level.
- No problem even if one companies table is down. So better availability.
Cons
- More work to DBA, since we need to make the same changes in many
database.
- Existing programs need to be modified to set correct package to read
correct companies tables.
- Programmer need to create one package for each company for one program.

2. Create a separate subsystems.
Pros
- More security than first.
- No problem to another company even if one sub system is down.
- No modification/less modification to existing programs.
Cons
- More work to system programmer and DBA.
- Need to migrate the program to multiple subsystems.
- More system over head.

3. Same table with view with current SQLid.
Pros
- No modification/less modification to existing programs.
- Once everything is set up,(Means tables and indexes with company id),
not much work to DBA. So managing is easy.
- Taking some report on all the company for internal purpose will be very
easy.
Cons
- Security can be tight based on view, but if anybody has access on
table, it will be a problem.
- One table is down, all the company is down

Based how much work we need to do, I prefer 3, but based on the security
requirement on data, I do not know which one we will choose. Also please let
me know if there is any other pros and cons for any of this solution.

Thanks..Sibi





-----Original Message-----
From: Dale Smock [mailto:[login to unmask email]
Sent: Friday, January 11, 2002 12:55 PM
To: [login to unmask email]
Subject: Re: Multiple company support on OS/390 and DB2.


Separate tables also makes availability, security, and chargeback for disk
space easier to separate by company.

Dale Smock
BMG

-----Original Message-----
From: Philip Nelson (DBA) [mailto:[login to unmask email]
Sent: Friday, January 11, 2002 12:52 PM
To: [login to unmask email]
Subject: Re: Multiple company support on OS/390 and DB2.


David,

The reason we use separate tables with different schemas is for legal
reasons. I suppose the financial services industry regulators are
probably stricter than most when it comes to data separation !!!

Sibi,

You'd be best to check this out before embarking on the single table
method.

Phil

On Thu, 2002-01-10 at 19:44, David Nance wrote:
> Sibi,
> We have something similar at our company. We sell an application to our
customers and the practice had always been to create another cics region,
another set of the same tables(different creator), another set of the same
load modules in a different loadlib, etc... I am, currently, trying to get
them to change this process. Why not one CICS region, one set of tables?
This can be done with minimal changes to the application itself.
> First, recreate a copy of your tables with an additional column,
something like client_id(this column would be populated with a unique
identifier for each client and made a secondary auth group with the correct
people from the clients connected to them), and a different table name,
maybe prefix them all with a t. Create a view on these new tables selecting
all columns, except the new column, name the views same as the existing
table names where client_id = current sqlid. Make a change to the logon
process that sets a users current sqlid. Set up a skeleton program that gets
executed by every cics transaction,that hits a table with the client_id,
transaction, and prog_name to find if there is a different program for this
particular client.
> This is a pretty rough draft of what could be done. Yes it gives you a
table as a single point of failure for all your clients, but that can, also,
be worked around with partitioning and other tricks, besides your mvs system
is a single point of failure anyway, right.
>
> >>> [login to unmask email] 01/10/02 12:05PM >>>
> With happy new year to everyone, let me start my first question this
year.
>
> We are trying to support multiple companies data in our OS/390 and DB2. I
am
> sure that somebody in this list might be doing this already. My questions
is
>
> 1. How do you separate one companies data from other?
> 2. Do you use separated subsystem of for each company or multiple table
with
> same name but with different schema/owner.
> 3. How do you manage the programs, since there is some customization
> required for some company.
>
> I just started thinking about this, so any additional information on this
> will be helpful.
>
>
> Thanks..sibi
>
>
>


>
> This message, including any attachments, is intended solely for the use of
the named
> recipient(s) and may contain confidential and/or privileged information.
Any
> unauthorized review, use, disclosure or distribution of this
communications is expressly
> prohibited. If you are not the intended recipient, please contact the
sender by reply e-mail
> and destroy any and all copies of the original message. Thank you.
>
>
>