DB2 z/OS : DSNDB06 RI

Steen Rasmussen

DB2 z/OS : DSNDB06 RI
After having worked with DB2 for 26 years (and still learning), I was very surprised yesterday. I'm working on an IDUG presentation and by accident created a FK on my table to SYSIBM.SYSPACKAGE.
I was surprised I could do that, but since I used DELETE CASCADE, I thought that could be the reason (had NO idea I could create FK's on the catalog objects). Then I changed the constraint to have DELETE RESTRICT, and I still was successful ???!!!

Am I the only one who didn't know you can create FK's from your own tables to catalog PK's ??

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Joe Geller

Re: DB2 z/OS : DSNDB06 RI
(in response to Steen Rasmussen)
It seems that the DB2 manual writers didn't know that either. The V10 SQL Reference manual still says that the referred to table cannot be a catalog table.

Joe


After having worked with DB2 for 26 years (and still learning), I was very surprised yesterday. I'm working on an IDUG presentation and by accident created a FK on my table to SYSIBM.SYSPACKAGE.
I was surprised I could do that, but since I used DELETE CASCADE, I thought that could be the reason (had NO idea I could create FK's on the catalog objects). Then I changed the constraint to have DELETE RESTRICT, and I still was successful ???!!!

Am I the only one who didn't know you can create FK's from your own tables to catalog PK's ??

Steen Rasmussen

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: DB2 z/OS : DSNDB06 RI
(in response to Joe Geller)
Steen

Have you actually tried to see if DB2 honours your RI constraint?

In the past it has been (accidentally) possible to add things to the catalog that subsequently get ignored

I'm wondering though if this is a side effect of IBM removing all those nasty links from the catalog and using DB2 RI instead

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: 22 January 2011 21:30
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS : DSNDB06 RI

After having worked with DB2 for 26 years (and still learning), I was very surprised yesterday. I'm working on an IDUG presentation and by accident created a FK on my table to SYSIBM.SYSPACKAGE.
I was surprised I could do that, but since I used DELETE CASCADE, I thought that could be the reason (had NO idea I could create FK's on the catalog objects). Then I changed the constraint to have DELETE RESTRICT, and I still was successful ???!!!

Am I the only one who didn't know you can create FK's from your own tables to catalog PK's ??

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Steen Rasmussen

Re: DB2 z/OS : DSNDB06 RI
(in response to Phil Grainger)
Good point Phil - so here we go (and it isn't even Friday).
I created the FK from my SYSPACKSTMT copy pointing to SYSPACKAGE (just like the DB2 RI).
I tried to INSERT an "invalid" row in my SYSPACKSTMT version and received SQL-530, so the RI is honored.
Guess this could be a method to avoid unintentional DROPs, FREE PACKAGE etc. - don't try this at home :-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Wednesday, January 26, 2011 9:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Steen

Have you actually tried to see if DB2 honours your RI constraint?

In the past it has been (accidentally) possible to add things to the catalog that subsequently get ignored

I'm wondering though if this is a side effect of IBM removing all those nasty links from the catalog and using DB2 RI instead

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: 22 January 2011 21:30
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS : DSNDB06 RI

After having worked with DB2 for 26 years (and still learning), I was very surprised yesterday. I'm working on an IDUG presentation and by accident created a FK on my table to SYSIBM.SYSPACKAGE.
I was surprised I could do that, but since I used DELETE CASCADE, I thought that could be the reason (had NO idea I could create FK's on the catalog objects). Then I changed the constraint to have DELETE RESTRICT, and I still was successful ???!!!

Am I the only one who didn't know you can create FK's from your own tables to catalog PK's ??

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Ken McDonald

Re: DB2 z/OS : DSNDB06 RI
(in response to Steen Rasmussen)
Boy howdy... Y'all are really messing with Mother Nature...

Ken

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Thursday, January 27, 2011 12:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Good point Phil - so here we go (and it isn't even Friday).
I created the FK from my SYSPACKSTMT copy pointing to SYSPACKAGE (just like the DB2 RI).
I tried to INSERT an "invalid" row in my SYSPACKSTMT version and received SQL-530, so the RI is honored.
Guess this could be a method to avoid unintentional DROPs, FREE PACKAGE etc. - don't try this at home :-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Wednesday, January 26, 2011 9:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Steen

Have you actually tried to see if DB2 honours your RI constraint?

In the past it has been (accidentally) possible to add things to the catalog that subsequently get ignored

I'm wondering though if this is a side effect of IBM removing all those nasty links from the catalog and using DB2 RI instead

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: 22 January 2011 21:30
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS : DSNDB06 RI

After having worked with DB2 for 26 years (and still learning), I was very surprised yesterday. I'm working on an IDUG presentation and by accident created a FK on my table to SYSIBM.SYSPACKAGE.
I was surprised I could do that, but since I used DELETE CASCADE, I thought that could be the reason (had NO idea I could create FK's on the catalog objects). Then I changed the constraint to have DELETE RESTRICT, and I still was successful ???!!!

Am I the only one who didn't know you can create FK's from your own tables to catalog PK's ??

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: DB2 z/OS : DSNDB06 RI
(in response to Ken McDonald)
But what if you insert a valid row into your table and then free the "parent"?

Does your row disappear??

Phil G

Sent from my iPod so typing may be worse than usual

On 27 Jan 2011, at 18:29, "Rasmussen, Steen" <[login to unmask email]> wrote:

> Good point Phil - so here we go (and it isn't even Friday).
> I created the FK from my SYSPACKSTMT copy pointing to SYSPACKAGE (just like the DB2 RI).
> I tried to INSERT an "invalid" row in my SYSPACKSTMT version and received SQL-530, so the RI is honored.
> Guess this could be a method to avoid unintentional DROPs, FREE PACKAGE etc. - don't try this at home :-)
>
> Steen Rasmussen
> CA Technologies
> Sr Engineering Services Architect
>
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
> Sent: Wednesday, January 26, 2011 9:23 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI
>
> Steen
>
> Have you actually tried to see if DB2 honours your RI constraint?
>
> In the past it has been (accidentally) possible to add things to the catalog that subsequently get ignored
>
> I'm wondering though if this is a side effect of IBM removing all those nasty links from the catalog and using DB2 RI instead
>
> Phil Grainger
> Cogito Ltd.
> [login to unmask email]
> +44 (0) 1298 872 148
> +44 (0) 7505 266 768
> www.cogito.co.uk
>
> Attend IDUG 2011 - the premiere events for DB2 professionals.
> IDUG North America, 2-6 May, Anaheim California
> IDUG EMEA, 14-18 November, Prague Czech Republic
>
>
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
> Sent: 22 January 2011 21:30
> To: [login to unmask email]
> Subject: [DB2-L] DB2 z/OS : DSNDB06 RI
>
> After having worked with DB2 for 26 years (and still learning), I was very surprised yesterday. I'm working on an IDUG presentation and by accident created a FK on my table to SYSIBM.SYSPACKAGE.
> I was surprised I could do that, but since I used DELETE CASCADE, I thought that could be the reason (had NO idea I could create FK's on the catalog objects). Then I changed the constraint to have DELETE RESTRICT, and I still was successful ???!!!
>
> Am I the only one who didn't know you can create FK's from your own tables to catalog PK's ??
>
> Steen Rasmussen
> CA Technologies
> Sr Engineering Services Architect
>
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2 information. *
> ** The best DB2 technical sessions in the world
> ** Independent, not-for-profit, User Run - the IDUG difference!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
>
> _____________________________________________________________________
> * IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
> * Your only source for independent, unbiased, and trusted DB2 information. *
> ** The most DB2 technical sessions of any conference
> ** Access IBM experts and developers
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
>
> _____________________________________________________________________
> * IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
> * If you are going to attend only one conference this year, this is it! *
> ** The best DB2 technical sessions in the world
> ** Independent, not-for-profit, User Run - the IDUG difference!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Raymond Bell

Re: DB2 z/OS : DSNDB06 RI
(in response to Phil Grainger)
Ah, let those (ex) CA boys dance with the devil... ;o)

Hey, how about a new RI relationship where a user table is the parent of Sysdatabase? On Delete Cascade, for a little extra frisson. Then delete the row from your user table with a Creator of Sysibm.

Light blue touch paper, stand well back...

C'mon, it's Friday in NZ already.

Cheers,


Raymond

________________________________________
From: IDUG DB2-L [[login to unmask email] On Behalf Of McDonald, Ken [[login to unmask email]
Sent: 27 January 2011 18:40
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Boy howdy... Y'all are really messing with Mother Nature...

Ken

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Thursday, January 27, 2011 12:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Good point Phil - so here we go (and it isn't even Friday).
I created the FK from my SYSPACKSTMT copy pointing to SYSPACKAGE (just like the DB2 RI).
I tried to INSERT an "invalid" row in my SYSPACKSTMT version and received SQL-530, so the RI is honored.
Guess this could be a method to avoid unintentional DROPs, FREE PACKAGE etc. - don't try this at home :-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Wednesday, January 26, 2011 9:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Steen

Have you actually tried to see if DB2 honours your RI constraint?

In the past it has been (accidentally) possible to add things to the catalog that subsequently get ignored

I'm wondering though if this is a side effect of IBM removing all those nasty links from the catalog and using DB2 RI instead

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: 22 January 2011 21:30
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS : DSNDB06 RI

After having worked with DB2 for 26 years (and still learning), I was very surprised yesterday. I'm working on an IDUG presentation and by accident created a FK on my table to SYSIBM.SYSPACKAGE.
I was surprised I could do that, but since I used DELETE CASCADE, I thought that could be the reason (had NO idea I could create FK's on the catalog objects). Then I changed the constraint to have DELETE RESTRICT, and I still was successful ???!!!

Am I the only one who didn't know you can create FK's from your own tables to catalog PK's ??

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv
_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Steen Rasmussen

Re: DB2 z/OS : DSNDB06 RI
(in response to Raymond Bell)
Don't push it Raymond ;-)
I like excitement in life, so I want to dance with the devil !!

To continue Mr. Grainger's idea, I re-established my FK to SYSPACKAGE,
did a BIND PACKAGE and inserted the 71 rows from SYSIBM.SYSPACKSTMT into
my own version (now having a FK to SYSPACKAGE) - all went well.
Then I did a FREE PACKAGE - and voila - my personal version of
SYSPACKSTMT nicely cleaned up.

Time for a beer since it's Friday in Aussie land ;-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Thursday, January 27, 2011 2:50 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Ah, let those (ex) CA boys dance with the devil... ;o)

Hey, how about a new RI relationship where a user table is the parent of
Sysdatabase? On Delete Cascade, for a little extra frisson. Then
delete the row from your user table with a Creator of Sysibm.

Light blue touch paper, stand well back...

C'mon, it's Friday in NZ already.

Cheers,


Raymond

________________________________________
From: IDUG DB2-L [[login to unmask email] On Behalf Of McDonald, Ken
[[login to unmask email]
Sent: 27 January 2011 18:40
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Boy howdy... Y'all are really messing with Mother Nature...

Ken

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen,
Steen
Sent: Thursday, January 27, 2011 12:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Good point Phil - so here we go (and it isn't even Friday).
I created the FK from my SYSPACKSTMT copy pointing to SYSPACKAGE (just
like the DB2 RI).
I tried to INSERT an "invalid" row in my SYSPACKSTMT version and
received SQL-530, so the RI is honored.
Guess this could be a method to avoid unintentional DROPs, FREE PACKAGE
etc. - don't try this at home :-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Wednesday, January 26, 2011 9:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Steen

Have you actually tried to see if DB2 honours your RI constraint?

In the past it has been (accidentally) possible to add things to the
catalog that subsequently get ignored

I'm wondering though if this is a side effect of IBM removing all those
nasty links from the catalog and using DB2 RI instead

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen,
Steen
Sent: 22 January 2011 21:30
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS : DSNDB06 RI

After having worked with DB2 for 26 years (and still learning), I was
very surprised yesterday. I'm working on an IDUG presentation and by
accident created a FK on my table to SYSIBM.SYSPACKAGE.
I was surprised I could do that, but since I used DELETE CASCADE, I
thought that could be the reason (had NO idea I could create FK's on the
catalog objects). Then I changed the constraint to have DELETE RESTRICT,
and I still was successful ???!!!

Am I the only one who didn't know you can create FK's from your own
tables to catalog PK's ??

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv
_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Raymond Bell

Re: DB2 z/OS : DSNDB06 RI
(in response to Steen Rasmussen)
Aussie? AUSSIE? Aaaargh! ;o)

Seriously though, it might be interesting, but do you really want user-defined RI on the Catalog? Or you just playing? My cobber (geddit?) Phil's probably right in that it's a knock-on effect of those imbedded pointers being removed from the Catalog. Wouldn't be surprised to see that 'feature' closed in a PTF v. soon.

Dunno about a beer but it'll be time for a G&T later tonight.

Laters,


Raymond

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: 27 January 2011 22:14
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Don't push it Raymond ;-)
I like excitement in life, so I want to dance with the devil !!

To continue Mr. Grainger's idea, I re-established my FK to SYSPACKAGE,
did a BIND PACKAGE and inserted the 71 rows from SYSIBM.SYSPACKSTMT into
my own version (now having a FK to SYSPACKAGE) - all went well.
Then I did a FREE PACKAGE - and voila - my personal version of
SYSPACKSTMT nicely cleaned up.

Time for a beer since it's Friday in Aussie land ;-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Thursday, January 27, 2011 2:50 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Ah, let those (ex) CA boys dance with the devil... ;o)

Hey, how about a new RI relationship where a user table is the parent of
Sysdatabase? On Delete Cascade, for a little extra frisson. Then
delete the row from your user table with a Creator of Sysibm.

Light blue touch paper, stand well back...

C'mon, it's Friday in NZ already.

Cheers,


Raymond

________________________________________
From: IDUG DB2-L [[login to unmask email] On Behalf Of McDonald, Ken
[[login to unmask email]
Sent: 27 January 2011 18:40
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Boy howdy... Y'all are really messing with Mother Nature...

Ken

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen,
Steen
Sent: Thursday, January 27, 2011 12:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Good point Phil - so here we go (and it isn't even Friday).
I created the FK from my SYSPACKSTMT copy pointing to SYSPACKAGE (just
like the DB2 RI).
I tried to INSERT an "invalid" row in my SYSPACKSTMT version and
received SQL-530, so the RI is honored.
Guess this could be a method to avoid unintentional DROPs, FREE PACKAGE
etc. - don't try this at home :-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Wednesday, January 26, 2011 9:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Steen

Have you actually tried to see if DB2 honours your RI constraint?

In the past it has been (accidentally) possible to add things to the
catalog that subsequently get ignored

I'm wondering though if this is a side effect of IBM removing all those
nasty links from the catalog and using DB2 RI instead

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen,
Steen
Sent: 22 January 2011 21:30
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS : DSNDB06 RI

After having worked with DB2 for 26 years (and still learning), I was
very surprised yesterday. I'm working on an IDUG presentation and by
accident created a FK on my table to SYSIBM.SYSPACKAGE.
I was surprised I could do that, but since I used DELETE CASCADE, I
thought that could be the reason (had NO idea I could create FK's on the
catalog objects). Then I changed the constraint to have DELETE RESTRICT,
and I still was successful ???!!!

Am I the only one who didn't know you can create FK's from your own
tables to catalog PK's ??

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv
_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Marcus Davage

Re: DB2 z/OS : DSNDB06 RI
(in response to Raymond Bell)
Nooo!!!! Must... Not... Attempt...

Marcus

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: 27 January 2011 20:50
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Ah, let those (ex) CA boys dance with the devil... ;o)

Hey, how about a new RI relationship where a user table is the parent of
Sysdatabase? On Delete Cascade, for a little extra frisson. Then
delete the row from your user table with a Creator of Sysibm.

Light blue touch paper, stand well back...

C'mon, it's Friday in NZ already.

Cheers,


Raymond

________________________________________
Lloyds TSB Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales, number 2065. Telephone: 020 7626 1500.
Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland, number 327000. Telephone: 0870 600 5000

Lloyds TSB Scotland plc. Registered Office: Henry Duncan House, 120 George Street, Edinburgh EH2 4LH. Registered in Scotland, number 95237. Telephone: 0131 225 4555.
Cheltenham & Gloucester plc. Registered Office: Barnett Way, Gloucester GL4 3RL. Registered in England and Wales, number 2299428. Telephone: 01452 372372.

Lloyds TSB Bank plc, Lloyds TSB Scotland plc, Bank of Scotland plc and Cheltenham & Gloucester plc are authorised and regulated by the Financial Services Authority.
Halifax is a division of Bank of Scotland plc. Cheltenham & Gloucester Savings is a division of Lloyds TSB Bank plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland, number 218813. Telephone: 0870 600 5000

Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland, number 95000. Telephone: 0131 225 4555

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments.

Telephone calls may be monitored or recorded.


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Roy Boxwell

Re: DB2 z/OS : DSNDB06 RI
(in response to Marcus Davage)
too late....all production catalog tables empty.....nothing left to do now
apart from switch off the lights and tootle off home for a VLB (Very
Large Beer)...

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

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



"Davage, Marcus" <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
28.01.2011 10:51
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
Re: [DB2-L] DB2 z/OS : DSNDB06 RI






Nooo!!!! Must... Not... Attempt...

Marcus

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: 27 January 2011 20:50
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Ah, let those (ex) CA boys dance with the devil... ;o)

Hey, how about a new RI relationship where a user table is the parent of
Sysdatabase? On Delete Cascade, for a little extra frisson. Then
delete the row from your user table with a Creator of Sysibm.

Light blue touch paper, stand well back...

C'mon, it's Friday in NZ already.

Cheers,


Raymond

________________________________________
Lloyds TSB Bank plc. Registered Office: 25 Gresham Street, London EC2V
7HN. Registered in England and Wales, number 2065. Telephone: 020 7626
1500.
Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ.
Registered in Scotland, number 327000. Telephone: 0870 600 5000

Lloyds TSB Scotland plc. Registered Office: Henry Duncan House, 120 George
Street, Edinburgh EH2 4LH. Registered in Scotland, number 95237.
Telephone: 0131 225 4555.
Cheltenham & Gloucester plc. Registered Office: Barnett Way, Gloucester
GL4 3RL. Registered in England and Wales, number 2299428. Telephone: 01452
372372.

Lloyds TSB Bank plc, Lloyds TSB Scotland plc, Bank of Scotland plc and
Cheltenham & Gloucester plc are authorised and regulated by the Financial
Services Authority.
Halifax is a division of Bank of Scotland plc. Cheltenham & Gloucester
Savings is a division of Lloyds TSB Bank plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in
Scotland, number 218813. Telephone: 0870 600 5000

Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ.
Registered in Scotland, number 95000. Telephone: 0131 225 4555

This e-mail (including any attachments) is private and confidential and
may contain privileged material. If you have received this e-mail in
error, please notify the sender and delete it (including any attachments)
immediately. You must not copy, distribute, disclose or use any of the
information in it or any attachments.

Telephone calls may be monitored or recorded.


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Raymond Bell

Re: DB2 z/OS : DSNDB06 RI
(in response to Roy Boxwell)
Get them in, would ya? I'll be there in a mo'...

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Roy Boxwell
Sent: 28 January 2011 09:58
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI


too late....all production catalog tables empty.....nothing left to do now apart from switch off the lights and tootle off home for a VLB (Very Large Beer)...

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

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

"Davage, Marcus" <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>

28.01.2011 10:51
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An

[login to unmask email]

Kopie

Thema

Re: [DB2-L] DB2 z/OS : DSNDB06 RI







Nooo!!!! Must... Not... Attempt...

Marcus

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: 27 January 2011 20:50
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Ah, let those (ex) CA boys dance with the devil... ;o)

Hey, how about a new RI relationship where a user table is the parent of
Sysdatabase? On Delete Cascade, for a little extra frisson. Then
delete the row from your user table with a Creator of Sysibm.

Light blue touch paper, stand well back...

C'mon, it's Friday in NZ already.

Cheers,


Raymond

________________________________________
Lloyds TSB Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales, number 2065. Telephone: 020 7626 1500.
Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland, number 327000. Telephone: 0870 600 5000

Lloyds TSB Scotland plc. Registered Office: Henry Duncan House, 120 George Street, Edinburgh EH2 4LH. Registered in Scotland, number 95237. Telephone: 0131 225 4555.
Cheltenham & Gloucester plc. Registered Office: Barnett Way, Gloucester GL4 3RL. Registered in England and Wales, number 2299428. Telephone: 01452 372372.

Lloyds TSB Bank plc, Lloyds TSB Scotland plc, Bank of Scotland plc and Cheltenham & Gloucester plc are authorised and regulated by the Financial Services Authority.
Halifax is a division of Bank of Scotland plc. Cheltenham & Gloucester Savings is a division of Lloyds TSB Bank plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland, number 218813. Telephone: 0870 600 5000

Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland, number 95000. Telephone: 0131 225 4555

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments.

Telephone calls may be monitored or recorded.


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Steen Rasmussen

Re: DB2 z/OS : DSNDB06 RI
(in response to Raymond Bell)
Hey Raymond - I am NOT serious. What happened was, I was researching
some DB2 9 / 10 catalog changes for my IDUG presentation. I created a
copy of SYSPACKSTMT and by accident also migrated the FK. Since I do
like to challenge DB2, the fact that I could create a FK against a
catalog table made me curious and the ball was rolling. You know the
rest from this email string ;-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Friday, January 28, 2011 2:45 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Aussie? AUSSIE? Aaaargh! ;o)

Seriously though, it might be interesting, but do you really want
user-defined RI on the Catalog? Or you just playing? My cobber
(geddit?) Phil's probably right in that it's a knock-on effect of those
imbedded pointers being removed from the Catalog. Wouldn't be surprised
to see that 'feature' closed in a PTF v. soon.

Dunno about a beer but it'll be time for a G&T later tonight.

Laters,


Raymond

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Phil Grainger

Re: DB2 z/OS : DSNDB06 RI
(in response to Steen Rasmussen)
But can't you see a use for a delete cascade from a catalog table to a user table?

Think of all those tools that have information related to DB2 objects that currently need to be manually tidied up!

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: 28 January 2011 08:45
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Aussie? AUSSIE? Aaaargh! ;o)

Seriously though, it might be interesting, but do you really want user-defined RI on the Catalog? Or you just playing? My cobber (geddit?) Phil's probably right in that it's a knock-on effect of those imbedded pointers being removed from the Catalog. Wouldn't be surprised to see that 'feature' closed in a PTF v. soon.

Dunno about a beer but it'll be time for a G&T later tonight.

Laters,


Raymond

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: 27 January 2011 22:14
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Don't push it Raymond ;-)
I like excitement in life, so I want to dance with the devil !!

To continue Mr. Grainger's idea, I re-established my FK to SYSPACKAGE,
did a BIND PACKAGE and inserted the 71 rows from SYSIBM.SYSPACKSTMT into
my own version (now having a FK to SYSPACKAGE) - all went well.
Then I did a FREE PACKAGE - and voila - my personal version of
SYSPACKSTMT nicely cleaned up.

Time for a beer since it's Friday in Aussie land ;-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Thursday, January 27, 2011 2:50 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Ah, let those (ex) CA boys dance with the devil... ;o)

Hey, how about a new RI relationship where a user table is the parent of
Sysdatabase? On Delete Cascade, for a little extra frisson. Then
delete the row from your user table with a Creator of Sysibm.

Light blue touch paper, stand well back...

C'mon, it's Friday in NZ already.

Cheers,


Raymond

________________________________________
From: IDUG DB2-L [[login to unmask email] On Behalf Of McDonald, Ken
[[login to unmask email]
Sent: 27 January 2011 18:40
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Boy howdy... Y'all are really messing with Mother Nature...

Ken

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen,
Steen
Sent: Thursday, January 27, 2011 12:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Good point Phil - so here we go (and it isn't even Friday).
I created the FK from my SYSPACKSTMT copy pointing to SYSPACKAGE (just
like the DB2 RI).
I tried to INSERT an "invalid" row in my SYSPACKSTMT version and
received SQL-530, so the RI is honored.
Guess this could be a method to avoid unintentional DROPs, FREE PACKAGE
etc. - don't try this at home :-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Wednesday, January 26, 2011 9:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Steen

Have you actually tried to see if DB2 honours your RI constraint?

In the past it has been (accidentally) possible to add things to the
catalog that subsequently get ignored

I'm wondering though if this is a side effect of IBM removing all those
nasty links from the catalog and using DB2 RI instead

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California
IDUG EMEA, 14-18 November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen,
Steen
Sent: 22 January 2011 21:30
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS : DSNDB06 RI

After having worked with DB2 for 26 years (and still learning), I was
very surprised yesterday. I'm working on an IDUG presentation and by
accident created a FK on my table to SYSIBM.SYSPACKAGE.
I was surprised I could do that, but since I used DELETE CASCADE, I
thought that could be the reason (had NO idea I could create FK's on the
catalog objects). Then I changed the constraint to have DELETE RESTRICT,
and I still was successful ???!!!

Am I the only one who didn't know you can create FK's from your own
tables to catalog PK's ??

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv
_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Aurora Dell'Anno

Re: DB2 z/OS : DSNDB06 RI
(in response to Phil Grainger)
Steen,

If you want your revenge on Raymond, call him an Aussie AND also call
him Ray...



Thanks.


Aurora




Aurora Emanuela Dell'Anno
CA Technology - MSC
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
[login to unmask email]

CA Technology R&D Limited, Ditton Park, Riding Court Road, Datchet,
Slough, Berkshire, England SL3 9LL.

CA Technology R&D Limited is a company registered in England and Wales
under company registration number 07251836 with its registered office at
the address set out above. VAT number 697904179.


http://www.ca.com/



P please don't print this e-mail unless you really need to!







-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: 28 January 2011 08:45
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Aussie? AUSSIE? Aaaargh! ;o)

Seriously though, it might be interesting, but do you really want
user-defined RI on the Catalog? Or you just playing? My cobber
(geddit?) Phil's probably right in that it's a knock-on effect of those
imbedded pointers being removed from the Catalog. Wouldn't be surprised
to see that 'feature' closed in a PTF v. soon.

Dunno about a beer but it'll be time for a G&T later tonight.

Laters,


Raymond

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen,
Steen
Sent: 27 January 2011 22:14
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Don't push it Raymond ;-)
I like excitement in life, so I want to dance with the devil !!

To continue Mr. Grainger's idea, I re-established my FK to SYSPACKAGE,
did a BIND PACKAGE and inserted the 71 rows from SYSIBM.SYSPACKSTMT into
my own version (now having a FK to SYSPACKAGE) - all went well.
Then I did a FREE PACKAGE - and voila - my personal version of
SYSPACKSTMT nicely cleaned up.

Time for a beer since it's Friday in Aussie land ;-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Thursday, January 27, 2011 2:50 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Ah, let those (ex) CA boys dance with the devil... ;o)

Hey, how about a new RI relationship where a user table is the parent of
Sysdatabase? On Delete Cascade, for a little extra frisson. Then
delete the row from your user table with a Creator of Sysibm.

Light blue touch paper, stand well back...

C'mon, it's Friday in NZ already.

Cheers,


Raymond

________________________________________
From: IDUG DB2-L [[login to unmask email] On Behalf Of McDonald, Ken
[[login to unmask email]
Sent: 27 January 2011 18:40
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Boy howdy... Y'all are really messing with Mother Nature...

Ken

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen,
Steen
Sent: Thursday, January 27, 2011 12:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Good point Phil - so here we go (and it isn't even Friday).
I created the FK from my SYSPACKSTMT copy pointing to SYSPACKAGE (just
like the DB2 RI).
I tried to INSERT an "invalid" row in my SYSPACKSTMT version and
received SQL-530, so the RI is honored.
Guess this could be a method to avoid unintentional DROPs, FREE PACKAGE
etc. - don't try this at home :-)

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Wednesday, January 26, 2011 9:23 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 z/OS : DSNDB06 RI

Steen

Have you actually tried to see if DB2 honours your RI constraint?

In the past it has been (accidentally) possible to add things to the
catalog that subsequently get ignored

I'm wondering though if this is a side effect of IBM removing all those
nasty links from the catalog and using DB2 RI instead

Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG 2011 - the premiere events for DB2 professionals.
IDUG North America, 2-6 May, Anaheim California IDUG EMEA, 14-18
November, Prague Czech Republic



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen,
Steen
Sent: 22 January 2011 21:30
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS : DSNDB06 RI

After having worked with DB2 for 26 years (and still learning), I was
very surprised yesterday. I'm working on an IDUG presentation and by
accident created a FK on my table to SYSIBM.SYSPACKAGE.
I was surprised I could do that, but since I used DELETE CASCADE, I
thought that could be the reason (had NO idea I could create FK's on the
catalog objects). Then I changed the constraint to have DELETE RESTRICT,
and I still was successful ???!!!

Am I the only one who didn't know you can create FK's from your own
tables to catalog PK's ??

Steen Rasmussen
CA Technologies
Sr Engineering Services Architect


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv
_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is
it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv