DB2 Catalog Tables

Mohamed Esmael

DB2 Catalog Tables

Dear All 

    please i need your support on how can i Backup and restore security catalog tables and also want to know the consideration 

hint:- security catalog tables is tables that held privileges over DB2 objects 

Jørn Thyssen

RE: DB2 Catalog Tables
(in response to Mohamed Esmael)

Hi,

The sample job DSNTIJIC can be used to backup of the catalog and directory. Some vendor tools can also build the required COPY statements.

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/ugref/src/tpc/db2z_cpycatanddirobjs.html

Hopefully you are already doing this in your shop on a regular schedule. 

 

 

Recovery of the catalog and directory is documented here:

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/ugref/src/tpc/db2z_recovercatalogdirectoryobjects.html

If you accidentally lose authorisations on an object it might be more prudent to use a log analysis tool to report and create the DCL to restore authorisations. Partial recovery of the catalog could cause some unwanted side effects. 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal. 

Edited By:
Jørn Thyssen[Organization Members] @ Feb 25, 2018 - 01:59 PM (Europe/Copenhagen)

Mohamed Esmael

RE: DB2 Catalog Tables
(in response to Jørn Thyssen)

Thanks alot for your reply 

Can i Backup and restore only of security catalog tables ?

Roy Boxwell

DB2 Catalog Tables
(in response to Mohamed Esmael)
Just doing all the xxxxAUTH tables is possible but partial recovery of the catalog is always „fraught with danger“...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 25 Feb 2018, at 14:09, Mohamed Esmael <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Thanks alot for your reply

Can i Backup and restore only of security catalog tables ?

-----End Original Message-----

Lizette Koehler

DB2 Catalog Tables
(in response to Roy Boxwell)
What version are you running? And what platform?



z/OS or LUW?



And what Roy and Jorn said, be careful at selecting one DB2 table that is system oriented. You need to ensure that you back up all of them to prevent errors should you only restore ONE table.



If you are not the DB2 System Programmer at your shop, you should ask that person for assistance on what you are trying to do.



If you could provide more details on why you are asking about this one table, that will help





Lizette



From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Sunday, February 25, 2018 7:40 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Catalog Tables



Just doing all the xxxxAUTH tables is possible but partial recovery of the catalog is always „fraught with danger“...

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.

-Product Development-

Heinrichstrasse 83-85

40239 Düsseldorf/Germany

Tel. +49 (0)211 96149-675

Fax +49 (0)211 96149-32

Email: [login to unmask email] <mailto:[login to unmask email]>

http://www.seg.de



Software Engineering GmbH

Amtsgericht Düsseldorf, HRB 37894

Geschäftsführung: Gerhard Schubert


On 25 Feb 2018, at 14:09, Mohamed Esmael <[login to unmask email] <mailto:[login to unmask email]> > wrote:

Thanks alot for your reply

Can i Backup and restore only of security catalog tables ?



-----End Original Message-----



-----End Original Message-----

Jim Tonchick

DB2 Catalog Tables
(in response to Mohamed Esmael)
Why are you singling out just the security tables? Have auditors asked you to do that?

You really should be backing up ALL the tablespaces in DSNDB01 and DSNDB06.


-----Original Message-----
From: Mohamed Esmael <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Sun, Feb 25, 2018 07:09 AM
Subject: [DB2-L] - RE: DB2 Catalog Tables



<div id="AOLMsgPart_2_ad601f02-f84d-44d9-b2ce-249b23a7af0d">
<div class="aolReplacedBody"><p>Thanks alot for your reply </p>
<p>Can i Backup and restore only of security catalog tables ?</p>
<hr size="1" style="color:#ccc"><div id="aolmail_socfooter" style="font-size:80%"><span style="font-weight:bold">Site Links: </span>
<a target="_blank" rel="noopener noreferrer" href="https://www.idug.org/p/fo/st/?post=184962&anc=p184962#p184962">View post online</a>
<a target="_blank" rel="noopener noreferrer" href="https://www.idug.org/p/fo/si/?topic=19">View mailing list online</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">Start new thread via email</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]?Subject=Unsubscribe">Unsubscribe from this mailing list</a>
<a target="_blank" rel="noopener noreferrer" href="https://www.idug.org/p/us/to/">Manage your subscription</a>


This email has been sent to: <a href="mailto:[login to unmask email]">[login to unmask email]</a>
<p>
** ** ** IDUG DB2 Data and Analytics Technical Summit in Bengaluru, India 2018 ** ** **

---> Bengaluru, India, March 27, 2018 <---

<a target="_blank" rel="noopener noreferrer" href="http://ibm.biz/IDUGBengaluru2018">http://ibm.biz/IDUGBengaluru2018</a>

</p>
<p>
Use of this email content is governed by the terms of service at:
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/cm/ld/fid=2">http://www.idug.org/p/cm/ld/fid=2</a></p>

<hr size="1" style="color:#ccc"></div>
</div>
</div>

Mohamed Esmael

RE: DB2 Catalog Tables
(in response to Roy Boxwell)

why it's danger and what is the consequence ?

Mohamed Esmael

RE: DB2 Catalog Tables
(in response to Lizette Koehler)

DB2 V.11 and platform z /os , we are security admins on our shop and we are on our way to implement separation of duties , so we need to be sure we can backup security catalog tables and able to restore it if needed 

James Campbell

DB2 Catalog Tables
(in response to Mohamed Esmael)
The privileges required to COPY and RECOVER a tablespace are at the database level. As
the security tables are all in DSNDB06 (which shares it privileges with DSNDB01) either

- you allow your system administrators to backup/recover all the DSNDB01 and DSNDB06
tablespaces - including the security ones; or

- you do not allow them to backup/recover any DSNDB01 or DSNDB06 tablespace. You do
the lot.

Typically system administrators are responsible for the backup/recovery of DSNDB01/6
tablespaces. As others have mentioned, recovering any DSNDB01/6 tablespace to a point in
time different from any other tablespace can give you an interesting situation. And, yes,
some security information is kept in DSNDB01 tablespaces.

James Campbell


On 25 Feb 2018 at 23:02, Mohamed Esmael wrote:

>
> DB2 V.11 and platform z /os , we are security admins on our shop and we are on our way to
> implement separation of duties , so we need to be sure we can backup security catalog tables and
> able to restore it if needed 
>


---
This email has been checked for viruses by AVG.
http://www.avg.com

Peter Vanroose

RE: DB2 Catalog Tables
(in response to Mohamed Esmael)

In Reply to Mohamed Esmael:

why it's danger and what is the consequence ?

Just to mention one:
The catalog security tables SYSIBM.SYS*AUTH contain foreign keys pointing to other catalog tables.
E.g. SYSTABAUTH has foreign key [login to unmask email] referring SYSIBM.SYSTABLES.

Restoring e.g. SYSTABAUTH to a different point-in-time will place it in check pending, since some authorizations could refer tables that no longer exist.
The main danger would be for foreign keys on other catalog tables, referring to one of the *AUTH tables, since then that other catalog table would become check pending.
Luckily there is just one: SYSCOLAUTH referring SYSTABAUTH (foreign key [login to unmask email] with columns CREATOR,TNAME,TIMESTAMP). And luckily it's also a *AUTH table.

So when restoring all of the *AUTH tables, and then running CHECK DATA (with delete) on all of them, everything should be fine.
But e.g. just restoring SYSTABAUTH would place SYSCOLAUTH in check pending which you might not expect..
Anyhow, you should carefully run tests of the whole procedure on a test system.


--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Edited By:
Peter Vanroose[Organization Members] @ Feb 26, 2018 - 10:44 AM (Europe/Brussels)

Bill Gallagher

DB2 Catalog Tables
(in response to James Campbell)
To add on to James' points, which are spot-on:

Separation of duties with respect to DB2 administration typically involves separating the administrative functions of (1) defining and "care and feeding" (i.e. reorgs, runstats, backups, recoveries, etc.) of the database objects, and (2) securing the database objects for user access (via grants and revokes).

Proper backup and recovery strategies, whether for the DB2 system catalog tables, or for application databases, should be owned and executed by database administrators, who have a full understanding of the complexities of the backup/recovery process. And since the DB2 catalog itself is a slightly different animal than a normal DB2 application database, with its own eccentricities in its design, plus the fact that it is the heart of the DB2 subsystem, backup and recovery of the DB2 catalog should be and typically is confined to the DB2 system administrators. Ceding all or part of that responsibility to non DB2 sys-admins is a risky proposition at best, and can put the health of your DB2 environment at risk.

Just my $0.02.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Monday, February 26, 2018 3:25 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Catalog Tables

The privileges required to COPY and RECOVER a tablespace are at the database level. As the security tables are all in DSNDB06 (which shares it privileges with DSNDB01) either

- you allow your system administrators to backup/recover all the DSNDB01 and DSNDB06 tablespaces - including the security ones; or

- you do not allow them to backup/recover any DSNDB01 or DSNDB06 tablespace. You do the lot.

Typically system administrators are responsible for the backup/recovery of DSNDB01/6 tablespaces. As others have mentioned, recovering any DSNDB01/6 tablespace to a point in time different from any other tablespace can give you an interesting situation. And, yes, some security information is kept in DSNDB01 tablespaces.

James Campbell


On 25 Feb 2018 at 23:02, Mohamed Esmael wrote:

>
> DB2 V.11 and platform z /os , we are security admins on our shop and
> we are on our way to implement separation of duties , so we need to be
> sure we can backup security catalog tables and able to restore it if
> needed
>


---
This email has been checked for viruses by AVG.
http://www.avg.com

-----End Original Message-----

Avram Friedman

RE: DB2 Catalog Tables
(in response to Mohamed Esmael)

In DB2 for zos tables are not backed up and recovered.  Table spaces are.
To summerize
Databases are the basis for operations (start / stop)
Tablespaces are the basis for backup / recovery
Tables are the basis for application access

A table can be unloaded and reloaded but the data is outside the control of DB2 and is therefore not reliable.

In Reply to Mohamed Esmael:

Dear All 

    please i need your support on how can i Backup and restore security catalog tables and also want to know the consideration 

hint:- security catalog tables is tables that held privileges over DB2 objects 



Avram Friedman
DB2-L hall of fame contributer
DB2-L acting administrator

[login to unmask email]

Michael Hannan

RE: DB2 Catalog Tables
(in response to Avram Friedman)

In Reply to Avram Friedman:

In DB2 for zos tables are not backed up and recovered.  Table spaces are.
To summarize
Databases are the basis for operations (start / stop)
Tablespaces are the basis for backup / recovery

This takes me to an interesting little tangent to main question here. The significance of "Databases". To me, a Database is a meaningless or somewhat arbitrary small collection of objects.

Even though there is a "Start Database" command in name, since DB2 V6 ( I believe) you could enter a database name pattern or range with quite a bit of flexibility, and similarly for the names of the spaces in the Database(s) to be started. If this was not enough flexibility, you could build a simple SQL to generate a set of START commands for any collection of Objects you care to come up with, based on criteria other than DB or Space names.

Referential Integrity can reference parent tables at same DB Server, not relevant to Database. Database seems to be a collection of objects that you want to provide similar authorities to, for operations like Stop, Start, Reorg, Recover, etc. that are actually performed at Tablespace or Object level. So if you need different Authorities for Operations, don't use a common Database.

Don't make the Database collection of Objects too large! Change the last part of Database name to make the Object Groups smaller. The reason is the DBD in the DB2 Directory. If it is large, then before and after images logged for each change can use up enormous Log Space. I tried it just once! Ha ha. So you don't want hundreds of Objects in the same DBD. Also DDL operations are locking much less if the DBD is small.

In fact, I remember a Richard Yevich lecture on DB2 V2.3 where he suggested that for maximum flexibility of your system and minimum directory contention, a site could well consider just one Tablespace per Database. LOL. Perhaps not many sites have gone that extreme, and DB2 improved a lot for concurrency of DDL with other operations at maybe DB2 V5 (trying to remember a Jim Teng presentation).

Plan is another somewhat meaningless collection, to me (of Package Lists). Some sites have only one or two Plans for the Application. DB2 Accounting Detail is stored at Thread Level and at Thread-Package level. When people talk about Plan level, it is a bit funny to me. Takes a lot more interesting fields to identify a Thread than just a Plan Name. LOL

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd 

Edited By:
Michael Hannan[Organization Members] @ Feb 27, 2018 - 10:27 AM (Europe/Berlin)

Patrick Bossman

RE: DB2 Catalog Tables
(in response to Michael Hannan)

The only thing I can say about breaking your catalog is the results can be unpredictable.  By unpredictable I mean unpredictable.  In IT, we do not like unpredictable.  One potential outcome is some future migration that expects the catalog to be a particular way might fail because the catalog was not that way.  Your tooling to manage Db2 objects might fail, or it might not fail when it should fail.  John Lyle and Jay Yothers could probably have a lively expert panel on this.

On Michaels point - 

So, databases in Db2 z are a logical thing, but I imagine quite a few storage admin policies depend on it for dataset placement.  There are also quite a few messages that report out dbname - tsname, so having implicitly (= inconsistently) named databases and table spaces can make administration and troubleshooting more ... fun and interesting!

There are some requirements out there to allow certain management functions to be against schema + table rather than dbname + tsname for this reason.

Best regards,

Patrick Bossman

 



In Reply to Michael Hannan:

In Reply to Avram Friedman:

In DB2 for zos tables are not backed up and recovered.  Table spaces are.
To summarize
Databases are the basis for operations (start / stop)
Tablespaces are the basis for backup / recovery

This takes me to an interesting little tangent to main question here. The significance of "Databases". To me, a Database is a meaningless or somewhat arbitrary small collection of objects.

Even though there is a "Start Database" command in name, since DB2 V6 ( I believe) you could enter a database name pattern or range with quite a bit of flexibility, and similarly for the names of the spaces in the Database(s) to be started. If this was not enough flexibility, you could build a simple SQL to generate a set of START commands for any collection of Objects you care to come up with, based on criteria other than DB or Space names.

Referential Integrity can reference parent tables at same DB Server, not relevant to Database. Database seems to be a collection of objects that you want to provide similar authorities to, for operations like Stop, Start, Reorg, Recover, etc. that are actually performed at Tablespace or Object level. So if you need different Authorities for Operations, don't use a common Database.

Don't make the Database collection of Objects too large! Change the last part of Database name to make the Object Groups smaller. The reason is the DBD in the DB2 Directory. If it is large, then before and after images logged for each change can use up enormous Log Space. I tried it just once! Ha ha. So you don't want hundreds of Objects in the same DBD. Also DDL operations are locking much less if the DBD is small.

In fact, I remember a Richard Yevich lecture on DB2 V2.3 where he suggested that for maximum flexibility of your system and minimum directory contention, a site could well consider just one Tablespace per Database. LOL. Perhaps not many sites have gone that extreme, and DB2 improved a lot for concurrency of DDL with other operations at maybe DB2 V5 (trying to remember a Jim Teng presentation).

Plan is another somewhat meaningless collection, to me (of Package Lists). Some sites have only one or two Plans for the Application. DB2 Accounting Detail is stored at Thread Level and at Thread-Package level. When people talk about Plan level, it is a bit funny to me. Takes a lot more interesting fields to identify a Thread than just a Plan Name. LOL

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd 

Edited By:
Patrick Bossman[Organization Members] @ Feb 27, 2018 - 10:37 AM (America/Eastern)

Jim Tonchick

DB2 Catalog Tables
(in response to Patrick Bossman)
Maybe the better solution is to convert from internal DB2 security control to external security control (RACF, ACF2, etc.).

This looks like it would be a better fit since it removes all security definitions out of the DB2 Catalog tables. The security database can be backed up and restored without DBA involvement and there is no danger to DB2 Catalog integrity.


-----Original Message-----
From: Patrick Bossman <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Tue, Feb 27, 2018 09:36 AM
Subject: [DB2-L] - RE: DB2 Catalog Tables



<div id="AOLMsgPart_2_6a2b85ab-4792-4b15-9f65-55fee05805f5">
<div class="aolReplacedBody"><p>The only thing I can say about breaking your catalog is the results can be unpredictable. By unpredictable I mean unpredictable. In IT, we do not like unpredictable. One potential outcome is some future migration that expects the catalog to be a particular way might fail because the catalog was not that way. Your tooling to manage Db2 objects might fail, or it might not fail when it should fail. John Lyle and Jay Yothers could probably have a lively expert panel on this.</p>
<p>On Michaels point - </p>
<p>So, databases in Db2 z are a logical thing, but I imagine quite a few storage admin policies depend on it for storage placement. There are also quite a few messages that report out dbname - tsname, so having implicitly (= inconsistently) named databases and table spaces can make administration and troubleshooting more ... fun and interesting!</p>
<p>There are some requirements out there to allow certain management functions to be against schema + table rather than dbname + tsname for this reason.</p>
<p>Best regards,</p>
<p>Patrick Bossman</p>
<p> </p>
<p>

In Reply to Michael Hannan:</p>
<blockquote>
<p>In Reply to Avram Friedman:</p>
<blockquote>
<p>In DB2 for zos tables are not backed up and recovered. Table spaces are.
To summarize
Databases are the basis for operations (start / stop)
Tablespaces are the basis for backup / recovery</p>
</blockquote>
<p>This takes me to an interesting little tangent to main question here. The significance of "Databases". To me, a Database is a meaningless or somewhat arbitrary small collection of objects.</p>
<p>Even though there is a "Start Database" command in name, since DB2 V6 ( I believe) you could enter a database name pattern or range with quite a bit of flexibility, and similarly for the names of the spaces in the Database(s) to be started. If this was not enough flexibility, you could build a simple SQL to generate a set of START commands for any collection of Objects you care to come up with, based on criteria other than DB or Space names.</p>
<p>Referential Integrity can reference parent tables at same DB Server, not relevant to Database. Database seems to be a collection of objects that you want to provide similar authorities to, for operations like Stop, Start, Reorg, Recover, etc. that are actually performed at Tablespace or Object level. So if you need different Authorities for Operations, don't use a common Database.</p>
<p>Don't make the Database collection of Objects too large! Change the last part of Database name to make the Object Groups smaller. The reason is the DBD in the DB2 Directory. If it is large, then before and after images logged for each change can use up enormous Log Space. I tried it just once! Ha ha. So you don't want hundreds of Objects in the same DBD. Also DDL operations are locking much less if the DBD is small.</p>
<p>In fact, I remember a Richard Yevich lecture on DB2 V2.3 where he suggested that for maximum flexibility of your system and minimum directory contention, a site could well consider just one Tablespace per Database. LOL. Perhaps not many sites have gone that extreme, and DB2 improved a lot for concurrency of DDL with other operations at maybe DB2 V5 (trying to remember a Jim Teng presentation).</p>
<p>Plan is another somewhat meaningless collection, to me (of Package Lists). Some sites have only one or two Plans for the Application. DB2 Accounting Detail is stored at Thread Level and at Thread-Package level. When people talk about Plan level, it is a bit funny to me. Takes a lot more interesting fields to identify a Thread than just a Plan Name. LOL</p>
<p>Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd </p>
</blockquote>
<hr size="1" style="color:#ccc"><div id="aolmail_socfooter" style="font-size:80%"><span style="font-weight:bold">Site Links: </span>
<a target="_blank" rel="noopener noreferrer" href="https://www.idug.org/p/fo/st/?post=184987&anc=p184987#p184987">View post online</a>
<a target="_blank" rel="noopener noreferrer" href="https://www.idug.org/p/fo/si/?topic=19">View mailing list online</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]">Start new thread via email</a>
<a target="_blank" rel="noopener noreferrer" href="mailto:[login to unmask email]?Subject=Unsubscribe">Unsubscribe from this mailing list</a>
<a target="_blank" rel="noopener noreferrer" href="https://www.idug.org/p/us/to/">Manage your subscription</a>


This email has been sent to: <a href="mailto:[login to unmask email]">[login to unmask email]</a>
<p>
** ** ** IDUG DB2 Data and Analytics Technical Summit in Chennai, India 2018 ** ** **

---> Chennai, India, March 29, 2018 <---

<a target="_blank" rel="noopener noreferrer" href="http://ibm.biz/IDUGChennai2018">http://ibm.biz/IDUGChennai2018</a>

</p>
<p>
Use of this email content is governed by the terms of service at:
<a target="_blank" rel="noopener noreferrer" href="http://www.idug.org/p/cm/ld/fid=2">http://www.idug.org/p/cm/ld/fid=2</a></p>

<hr size="1" style="color:#ccc"></div>
</div>
</div>

Roy Boxwell

DB2 Catalog Tables
(in response to Jim Tonchick)
Yep – That’s the way to go!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: Jim Tonchick [mailto:[login to unmask email]
Sent: Wednesday, February 28, 2018 1:53 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Catalog Tables

Maybe the better solution is to convert from internal DB2 security control to external security control (RACF, ACF2, etc.).

This looks like it would be a better fit since it removes all security definitions out of the DB2 Catalog tables. The security database can be backed up and restored without DBA involvement and there is no danger to DB2 Catalog integrity.


-----Original Message-----
From: Patrick Bossman <[login to unmask email]<mailto:[login to unmask email]>>
To: DB2-L <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Tue, Feb 27, 2018 09:36 AM
Subject: [DB2-L] - RE: DB2 Catalog Tables


The only thing I can say about breaking your catalog is the results can be unpredictable. By unpredictable I mean unpredictable. In IT, we do not like unpredictable. One potential outcome is some future migration that expects the catalog to be a particular way might fail because the catalog was not that way. Your tooling to manage Db2 objects might fail, or it might not fail when it should fail. John Lyle and Jay Yothers could probably have a lively expert panel on this.

On Michaels point -

So, databases in Db2 z are a logical thing, but I imagine quite a few storage admin policies depend on it for storage placement. There are also quite a few messages that report out dbname - tsname, so having implicitly (= inconsistently) named databases and table spaces can make administration and troubleshooting more ... fun and interesting!

There are some requirements out there to allow certain management functions to be against schema + table rather than dbname + tsname for this reason.

Best regards,

Patrick Bossman




In Reply to Michael Hannan:

In Reply to Avram Friedman:

In DB2 for zos tables are not backed up and recovered. Table spaces are.
To summarize
Databases are the basis for operations (start / stop)
Tablespaces are the basis for backup / recovery

This takes me to an interesting little tangent to main question here. The significance of "Databases". To me, a Database is a meaningless or somewhat arbitrary small collection of objects.

Even though there is a "Start Database" command in name, since DB2 V6 ( I believe) you could enter a database name pattern or range with quite a bit of flexibility, and similarly for the names of the spaces in the Database(s) to be started. If this was not enough flexibility, you could build a simple SQL to generate a set of START commands for any collection of Objects you care to come up with, based on criteria other than DB or Space names.

Referential Integrity can reference parent tables at same DB Server, not relevant to Database. Database seems to be a collection of objects that you want to provide similar authorities to, for operations like Stop, Start, Reorg, Recover, etc. that are actually performed at Tablespace or Object level. So if you need different Authorities for Operations, don't use a common Database.

Don't make the Database collection of Objects too large! Change the last part of Database name to make the Object Groups smaller. The reason is the DBD in the DB2 Directory. If it is large, then before and after images logged for each change can use up enormous Log Space. I tried it just once! Ha ha. So you don't want hundreds of Objects in the same DBD. Also DDL operations are locking much less if the DBD is small.

In fact, I remember a Richard Yevich lecture on DB2 V2.3 where he suggested that for maximum flexibility of your system and minimum directory contention, a site could well consider just one Tablespace per Database. LOL. Perhaps not many sites have gone that extreme, and DB2 improved a lot for concurrency of DDL with other operations at maybe DB2 V5 (trying to remember a Jim Teng presentation).

Plan is another somewhat meaningless collection, to me (of Package Lists). Some sites have only one or two Plans for the Application. DB2 Accounting Detail is stored at Thread Level and at Thread-Package level. When people talk about Plan level, it is a bit funny to me. Takes a lot more interesting fields to identify a Thread than just a Plan Name. LOL

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----

Mohamed Esmael

RE: DB2 Catalog Tables
(in response to Jim Tonchick)

Also we think about that solution if we migrate from internal security to RACF External security