SYSPACKDEP not containing dependencies

Sally Mir

SYSPACKDEP not containing dependencies
Six months ago or so, I posted an inquiry asking if anyone had run across the problem we were having: Native stored procedures with static SQL, and yes, they are valid, did not have their dependencies recorded in SYSPACKDEP. This was causing problems for our DBAs, who wanted to find out which procedures would become invalidated whenever they changed certain tables in production.

Well, after much digging and back-and-forth with IBM, here is their answer:

If you bind a native stored procedure with VALIDATE(RUN) and EXPLAIN(YES), and the plan tables (!) do not exist at the time, the dependencies will not be recorded in the catalog. If you use EXPLAIN(NO), everything will work OK.

They claim this is working as designed. I am pretty upset about their answer and am wondering how you all feel about this. The existence of USER TABLES determines whether or not the catalog is reliable?

Sally A. Mir
Vice President
BB&T Enterprise Services
Software Systems Programmer Specialist
2400 Reynolda Road
001-94-02-80
Winston-Salem, NC 27106
336.733.0946




The information in this transmission may contain proprietary and non-public information of BB&T or its affiliates and may be subject to protection under the law. The message is intended for the sole use of the individual or entity to which it is addressed. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited. If you received this message in error, please delete the material from your system without reading the content and notify the sender immediately of the inadvertent transmission.

Raymond Bell

SYSPACKDEP not containing dependencies
(in response to Sally Mir)
Hi Sally,

If that's truly what's happening then I'd say the design needs changing. EXPLAIN YES|NO should, IMHO, have no bearing whatsoever on the packages' recorded object dependencies.

But I'm confused. When you say, 'bind a native stored procedure' do you mean create a native SQL stored proc which implicitly binds a package? Because whether to explain the SQL in a native SQL SP is down to whether WITH EXPLAIN or WITHOUT EXPLAIN is part of the SP definition. I guess you do. I just did a quick test; I've tried to create a native SQL SP with WITH EXPLAIN after having dropped the related PLAN_TABLE. Sure enough, I get +219 (no explain table) and, sure enough, the SP is created, the SP package exists - and it has no dependencies recorded in Syspackdep! WTAF?

So yes, that's what's truly happening. And yes, the design needs changing. Because OK, I'd rather not have the bind fail just because a PLAN_TABLE doesn't exist, but I'd far rather have the package dependencies recorded in the Catalog correctly.

Cheers,


Raymond

From: Mir, Sally [mailto:[login to unmask email]
Sent: 15 November 2018 13:41
To: [login to unmask email]
Subject: [DB2-L] - SYSPACKDEP not containing dependencies


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

Six months ago or so, I posted an inquiry asking if anyone had run across the problem we were having: Native stored procedures with static SQL, and yes, they are valid, did not have their dependencies recorded in SYSPACKDEP. This was causing problems for our DBAs, who wanted to find out which procedures would become invalidated whenever they changed certain tables in production.

Well, after much digging and back-and-forth with IBM, here is their answer:

If you bind a native stored procedure with VALIDATE(RUN) and EXPLAIN(YES), and the plan tables (!) do not exist at the time, the dependencies will not be recorded in the catalog. If you use EXPLAIN(NO), everything will work OK.

They claim this is working as designed. I am pretty upset about their answer and am wondering how you all feel about this. The existence of USER TABLES determines whether or not the catalog is reliable?

Sally A. Mir
Vice President
BB&T Enterprise Services
Software Systems Programmer Specialist
2400 Reynolda Road
001-94-02-80
Winston-Salem, NC 27106
336.733.0946




The information in this transmission may contain proprietary and non-public information of BB&T or its affiliates and may be subject to protection under the law. The message is intended for the sole use of the individual or entity to which it is addressed. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited. If you received this message in error, please delete the material from your system without reading the content and notify the sender immediately of the inadvertent transmission.
-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

Mike Vaughan

RE: SYSPACKDEP not containing dependencies
(in response to Raymond Bell)

Curious – you do get a row on SYSPACKSTMT that shows the correct text? One thing that makes this almost make sense is the description of the +219 SQLCode. Under “System action” it indicates that while a valid package will be created, “The statement is bound dynamically on each execution of the statement”. If it’s turning all of the SQL into dynamic execution then I think it’s reasonable that they would not be on SYSPACKDEP. Personally I’d prefer a -219 and failed bind in that situation, which I believe is what happens on a normal package bind if you bind explain(yes) without a plan_table.



In Reply to Raymond Bell:

Hi Sally,

If that's truly what's happening then I'd say the design needs changing. EXPLAIN YES|NO should, IMHO, have no bearing whatsoever on the packages' recorded object dependencies.

But I'm confused. When you say, 'bind a native stored procedure' do you mean create a native SQL stored proc which implicitly binds a package? Because whether to explain the SQL in a native SQL SP is down to whether WITH EXPLAIN or WITHOUT EXPLAIN is part of the SP definition. I guess you do. I just did a quick test; I've tried to create a native SQL SP with WITH EXPLAIN after having dropped the related PLAN_TABLE. Sure enough, I get +219 (no explain table) and, sure enough, the SP is created, the SP package exists - and it has no dependencies recorded in Syspackdep! WTAF?

So yes, that's what's truly happening. And yes, the design needs changing. Because OK, I'd rather not have the bind fail just because a PLAN_TABLE doesn't exist, but I'd far rather have the package dependencies recorded in the Catalog correctly.

Cheers,


Raymond

From: Mir, Sally [mailto:[login to unmask email]
Sent: 15 November 2018 13:41
To: [login to unmask email]
Subject: [DB2-L] - SYSPACKDEP not containing dependencies


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

Six months ago or so, I posted an inquiry asking if anyone had run across the problem we were having: Native stored procedures with static SQL, and yes, they are valid, did not have their dependencies recorded in SYSPACKDEP. This was causing problems for our DBAs, who wanted to find out which procedures would become invalidated whenever they changed certain tables in production.

Well, after much digging and back-and-forth with IBM, here is their answer:

If you bind a native stored procedure with VALIDATE(RUN) and EXPLAIN(YES), and the plan tables (!) do not exist at the time, the dependencies will not be recorded in the catalog. If you use EXPLAIN(NO), everything will work OK.

They claim this is working as designed. I am pretty upset about their answer and am wondering how you all feel about this. The existence of USER TABLES determines whether or not the catalog is reliable?

Sally A. Mir
Vice President
BB&T Enterprise Services
Software Systems Programmer Specialist
2400 Reynolda Road
001-94-02-80
Winston-Salem, NC 27106
336.733.0946




The information in this transmission may contain proprietary and non-public information of BB&T or its affiliates and may be subject to protection under the law. The message is intended for the sole use of the individual or entity to which it is addressed. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited. If you received this message in error, please delete the material from your system without reading the content and notify the sender immediately of the inadvertent transmission.
-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Visit our website at www.rbs.com http://www.rbs.com

Marcus Davage

SYSPACKDEP not containing dependencies
(in response to Raymond Bell)
RFE anyone?

But, honestly? To admit to a design error is one thing (if followed by an apology and intent to rectify), but to claim, "Yes, we did actually design it to do that," with no apology and intent to rectify is something else! Really, IBM?

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator
LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG http://www.idug.org/p/us/sn/uid=24500 | Twitter http://twitter.com/spufidoo | About.me http://about.me/spufidoo
From: Bell, Raymond (Hosting Services, Technology) [mailto:[login to unmask email]
Sent: 15 November 2018 14:41
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: SYSPACKDEP not containing dependencies

-- This email has reached the Bank via an external source --

Hi Sally,

If that's truly what's happening then I'd say the design needs changing. EXPLAIN YES|NO should, IMHO, have no bearing whatsoever on the packages' recorded object dependencies.

But I'm confused. When you say, 'bind a native stored procedure' do you mean create a native SQL stored proc which implicitly binds a package? Because whether to explain the SQL in a native SQL SP is down to whether WITH EXPLAIN or WITHOUT EXPLAIN is part of the SP definition. I guess you do. I just did a quick test; I've tried to create a native SQL SP with WITH EXPLAIN after having dropped the related PLAN_TABLE. Sure enough, I get +219 (no explain table) and, sure enough, the SP is created, the SP package exists - and it has no dependencies recorded in Syspackdep! WTAF?

So yes, that's what's truly happening. And yes, the design needs changing. Because OK, I'd rather not have the bind fail just because a PLAN_TABLE doesn't exist, but I'd far rather have the package dependencies recorded in the Catalog correctly.

Cheers,


Raymond

From: Mir, Sally [mailto:[login to unmask email]
Sent: 15 November 2018 13:41
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - SYSPACKDEP not containing dependencies


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************
Six months ago or so, I posted an inquiry asking if anyone had run across the problem we were having: Native stored procedures with static SQL, and yes, they are valid, did not have their dependencies recorded in SYSPACKDEP. This was causing problems for our DBAs, who wanted to find out which procedures would become invalidated whenever they changed certain tables in production.

Well, after much digging and back-and-forth with IBM, here is their answer:

If you bind a native stored procedure with VALIDATE(RUN) and EXPLAIN(YES), and the plan tables (!) do not exist at the time, the dependencies will not be recorded in the catalog. If you use EXPLAIN(NO), everything will work OK.

They claim this is working as designed. I am pretty upset about their answer and am wondering how you all feel about this. The existence of USER TABLES determines whether or not the catalog is reliable?

Sally A. Mir
Vice President
BB&T Enterprise Services
Software Systems Programmer Specialist
2400 Reynolda Road
001-94-02-80
Winston-Salem, NC 27106
336.733.0946




The information in this transmission may contain proprietary and non-public information of BB&T or its affiliates and may be subject to protection under the law. The message is intended for the sole use of the individual or entity to which it is addressed. If you are not the intended recipient, you are notified that any use, distribution or copying of the message is strictly prohibited. If you received this message in error, please delete the material from your system without reading the content and notify the sender immediately of the inadvertent transmission.
-----End Original Message-----
The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and Prudential Regulation Authority. The Royal Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands, and is registered in the Commercial Register under number 33002587. Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are authorised to act as agent for each other in certain jurisdictions.

National Westminster Bank Plc. Registered in England No. 929027. Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster Bank Plc is authorised by the Prudential Regulation Authority, and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

The Royal Bank of Scotland plc and National Westminster Bank Plc are authorised to act as agent for each other.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank Plc or any affiliated entity (RBS or us) does not accept responsibility for changes made to this message after it was sent. RBS may monitor e-mails for business and operational purposes. By replying to this message you understand that the content of your message may be monitored.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by RBS in this regard and the recipient should carry out such virus and other checks as it considers appropriate.


Visit our website at www.rbs.com http://www.rbs.com
-----End Original Message-----


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

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank Corporate Markets plc. Registered office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 10399850.

Lloyds Bank plc, Bank of Scotland plc and Lloyds Bank Corporate Markets plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

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.

Sam Baugh

SYSPACKDEP not containing dependencies
(in response to Raymond Bell)
Just to pile on, we found a situation with EXPLAIN(YES) in a native stored
procedure using a FOR loop would generate the SQL statement with invalid
host variable definitions, causing the statement to get a -206 sqlcode when
executed. The statement was generated correctly with EXPLAIN(NO). The fix
was to create a new return code about missing a plan table. I would have
rather had the SQL generated correctly regardless of whether using EXPLAIN
YES or NO.

On Thu, Nov 15, 2018 at 8:41 AM Bell, Raymond (Hosting Services,
Technology) <[login to unmask email]> wrote:

> Hi Sally,
>
>
>
> If that’s truly what’s happening then I’d say the design needs changing.
> EXPLAIN YES|NO should, IMHO, have no bearing whatsoever on the packages’
> recorded object dependencies.
>
>
>
> But I’m confused. When you say, ‘bind a native stored procedure’ do you
> mean create a native SQL stored proc which implicitly binds a package?
> Because whether to explain the SQL in a native SQL SP is down to whether
> WITH EXPLAIN or WITHOUT EXPLAIN is part of the SP definition. I guess you
> do. I just did a quick test; I’ve tried to create a native SQL SP with
> WITH EXPLAIN after having dropped the related PLAN_TABLE. Sure enough, I
> get +219 (no explain table) and, sure enough, the SP is created, the SP
> package exists – and it has no dependencies recorded in Syspackdep! WTAF?
>
>
>
> So yes, that’s what’s truly happening. And yes, the design needs
> changing. Because OK, I’d rather not have the bind fail just because a
> PLAN_TABLE doesn’t exist, but I’d far rather have the package dependencies
> recorded in the Catalog correctly.
>
>
>
> Cheers,
>
>
>
>
>
> Raymond
>
>
>
> *From:* Mir, Sally [mailto:[login to unmask email]
> *Sent:* 15 November 2018 13:41
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - SYSPACKDEP not containing dependencies
>
>
>
>
> *********************************************
> " This message originates from outside our organisation. Consider
> carefully whether you should click on any links, open any attachments or
> reply. If in doubt, forward to ~ Phishing"
> *********************************************
>
> Six months ago or so, I posted an inquiry asking if anyone had run across
> the problem we were having: Native stored procedures with static SQL, and
> yes, they are valid, did not have their dependencies recorded in
> SYSPACKDEP. This was causing problems for our DBAs, who wanted to find out
> which procedures would become invalidated whenever they changed certain
> tables in production.
>
>
>
> Well, after much digging and back-and-forth with IBM, here is their answer:
>
>
>
> If you bind a native stored procedure with VALIDATE(RUN) and EXPLAIN(YES),
> and the plan tables (!) do not exist at the time, the dependencies will not
> be recorded in the catalog. If you use EXPLAIN(NO), everything will work
> OK.
>
>
>
> They claim this is working as designed. I am pretty upset about their
> answer and am wondering how you all feel about this. The existence of USER
> TABLES determines whether or not the catalog is reliable?
>
>
>
> Sally A. Mir
> Vice President
>
> BB&T Enterprise Services
>
> Software Systems Programmer Specialist
> 2400 Reynolda Road
> 001-94-02-80
> Winston-Salem, NC 27106
> 336.733.0946
>
>
>
>
>
>
> *The information in this transmission may contain proprietary and
> non-public information of BB&T or its affiliates and may be subject to
> protection under the law. The message is intended for the sole use of the
> individual or entity to which it is addressed. If you are not the intended
> recipient, you are notified that any use, distribution or copying of the
> message is strictly prohibited. If you received this message in error,
> please delete the material from your system without reading the content and
> notify the sender immediately of the inadvertent transmission.*
> -----End Original Message-----
>
> The Royal Bank of Scotland plc. Registered in Scotland No 83026.
> Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank
> of Scotland is authorised by the Prudential Regulation Authority, and
> regulated by the Financial Conduct Authority and Prudential Regulation
> Authority. The Royal Bank of Scotland N.V. is authorised and regulated by
> the De Nederlandsche Bank and has its seat at Amsterdam, the Netherlands,
> and is registered in the Commercial Register under number 33002587.
> Registered Office: Gustav Mahlerlaan 350, Amsterdam, The Netherlands. The
> Royal Bank of Scotland N.V. and The Royal Bank of Scotland plc are
> authorised to act as agent for each other in certain jurisdictions.
>
>
>
> National Westminster Bank Plc. Registered in England No. 929027.
> Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster
> Bank Plc is authorised by the Prudential Regulation Authority, and
> regulated by the Financial Conduct Authority and the Prudential Regulation
> Authority.
>
>
>
> The Royal Bank of Scotland plc and National Westminster Bank Plc are
> authorised to act as agent for each other.
>
>
>
> This e-mail message is confidential and for use by the addressee only. If
> the message is received by anyone other than the addressee, please return
> the message to the sender by replying to it and then delete the message
> from your computer. Internet e-mails are not necessarily secure. The
> Royal Bank of Scotland plc, The Royal Bank of Scotland N.V., National
> Westminster Bank Plc or any affiliated entity (RBS or us) does not accept
> responsibility for changes made to this message after it was sent. RBS may
> monitor e-mails for business and operational purposes. By replying to this
> message you understand that the content of your message may be monitored.
>
>
>
> Whilst all reasonable care has been taken to avoid the transmission of
> viruses, it is the responsibility of the recipient to ensure that the
> onward transmission, opening or use of this message and any attachments
> will not adversely affect its systems or data. No responsibility is
> accepted by RBS in this regard and the recipient should carry out such
> virus and other checks as it considers appropriate.
>
>
> Visit our website at www.rbs.com
> -----End Original Message-----
>

Daniel Luksetich

SYSPACKDEP not containing dependencies
(in response to Marcus Davage)
I have not tested it myself, but it is hard to believe that it is working as
designed.

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator - DB2 11 DBA for z/OS

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Application Developer - DB2 11 for z/OS

IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and
Windows



From: Davage, Marcus (ITS Database Services - DB2) <[login to unmask email]>
Sent: Thursday, November 15, 2018 9:27 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: SYSPACKDEP not containing dependencies



RFE anyone?



But, honestly? To admit to a design error is one thing (if followed by an
apology and intent to rectify), but to claim, "Yes, we did actually design
it to do that," with no apology and intent to rectify is something else!
Really, IBM?



Regards,

Marcus Davage CEng CITP MBCS

IBM Certified DB2 Database Administrator

LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG
http://www.idug.org/p/us/sn/uid=24500 | Twitter
http://twitter.com/spufidoo | About.me http://about.me/spufidoo

From: Bell, Raymond (Hosting Services, Technology)
[mailto:[login to unmask email]
Sent: 15 November 2018 14:41
To: '[login to unmask email]' <[login to unmask email]
<mailto:[login to unmask email]> >
Subject: [DB2-L] - RE: SYSPACKDEP not containing dependencies



-- This email has reached the Bank via an external source --


Hi Sally,



If that's truly what's happening then I'd say the design needs changing.
EXPLAIN YES|NO should, IMHO, have no bearing whatsoever on the packages'
recorded object dependencies.



But I'm confused. When you say, 'bind a native stored procedure' do you
mean create a native SQL stored proc which implicitly binds a package?
Because whether to explain the SQL in a native SQL SP is down to whether
WITH EXPLAIN or WITHOUT EXPLAIN is part of the SP definition. I guess you
do. I just did a quick test; I've tried to create a native SQL SP with WITH
EXPLAIN after having dropped the related PLAN_TABLE. Sure enough, I get
+219 (no explain table) and, sure enough, the SP is created, the SP package
exists - and it has no dependencies recorded in Syspackdep! WTAF?



So yes, that's what's truly happening. And yes, the design needs changing.
Because OK, I'd rather not have the bind fail just because a PLAN_TABLE
doesn't exist, but I'd far rather have the package dependencies recorded in
the Catalog correctly.



Cheers,





Raymond



From: Mir, Sally [mailto:[login to unmask email]
Sent: 15 November 2018 13:41
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - SYSPACKDEP not containing dependencies




*********************************************
" This message originates from outside our organisation. Consider carefully
whether you should click on any links, open any attachments or reply. If in
doubt, forward to ~ Phishing"
*********************************************

Six months ago or so, I posted an inquiry asking if anyone had run across
the problem we were having: Native stored procedures with static SQL, and
yes, they are valid, did not have their dependencies recorded in SYSPACKDEP.
This was causing problems for our DBAs, who wanted to find out which
procedures would become invalidated whenever they changed certain tables in
production.



Well, after much digging and back-and-forth with IBM, here is their answer:



If you bind a native stored procedure with VALIDATE(RUN) and EXPLAIN(YES),
and the plan tables (!) do not exist at the time, the dependencies will not
be recorded in the catalog. If you use EXPLAIN(NO), everything will work
OK.



They claim this is working as designed. I am pretty upset about their
answer and am wondering how you all feel about this. The existence of USER
TABLES determines whether or not the catalog is reliable?



Sally A. Mir
Vice President

BB&T Enterprise Services

Software Systems Programmer Specialist
2400 Reynolda Road
001-94-02-80
Winston-Salem, NC 27106
336.733.0946






The information in this transmission may contain proprietary and non-public
information of BB&T or its affiliates and may be subject to protection under
the law. The message is intended for the sole use of the individual or
entity to which it is addressed. If you are not the intended recipient, you
are notified that any use, distribution or copying of the message is
strictly prohibited. If you received this message in error, please delete
the material from your system without reading the content and notify the
sender immediately of the inadvertent transmission.

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


The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered
Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland
is authorised by the Prudential Regulation Authority, and regulated by the
Financial Conduct Authority and Prudential Regulation Authority. The Royal
Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche
Bank and has its seat at Amsterdam, the Netherlands, and is registered in
the Commercial Register under number 33002587. Registered Office: Gustav
Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V.
and The Royal Bank of Scotland plc are authorised to act as agent for each
other in certain jurisdictions.



National Westminster Bank Plc. Registered in England No. 929027.
Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster
Bank Plc is authorised by the Prudential Regulation Authority, and regulated
by the Financial Conduct Authority and the Prudential Regulation Authority.



The Royal Bank of Scotland plc and National Westminster Bank Plc are
authorised to act as agent for each other.



This e-mail message is confidential and for use by the addressee only. If
the message is received by anyone other than the addressee, please return
the message to the sender by replying to it and then delete the message from
your computer. Internet e-mails are not necessarily secure. The Royal Bank
of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank
Plc or any affiliated entity (RBS or us) does not accept responsibility for
changes made to this message after it was sent. RBS may monitor e-mails for
business and operational purposes. By replying to this message you
understand that the content of your message may be monitored.



Whilst all reasonable care has been taken to avoid the transmission of
viruses, it is the responsibility of the recipient to ensure that the onward
transmission, opening or use of this message and any attachments will not
adversely affect its systems or data. No responsibility is accepted by RBS
in this regard and the recipient should carry out such virus and other
checks as it considers appropriate.



Visit our website at www.rbs.com http://www.rbs.com

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



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

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN.
Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ.
Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank Corporate Markets plc. Registered office: 25 Gresham Street,
London EC2V 7HN. Registered in England and Wales no. 10399850.

Lloyds Bank plc, Bank of Scotland plc and Lloyds Bank Corporate Markets plc
are authorised by the Prudential Regulation Authority and regulated by the
Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in
Scotland no. SC218813.

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.



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

Joe Geller

RE: SYSPACKDEP not containing dependencies
(in response to Daniel Luksetich)

As Mike Vaughan said, +219 says that the statement will be bound dynamically at each execution.  Is that correct?  I thought that VALIDATE RUN will result in the statement (that failed validation at bind time) being validate and bound at the first execution only (if the missing objects now exist), not dynamically each time.  Do I remember correctly?

If a normal package gets a -219, then why should a native SP get only a warning +219?

Finally, just because something is working as designed, doesn't mean that there isn't a bug in the design.  I agree it is a bug, not something that should need an RFE.

Joe

In Reply to Daniel Luksetich:

I have not tested it myself, but it is hard to believe that it is working as
designed.

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator - DB2 11 DBA for z/OS

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Application Developer - DB2 11 for z/OS

IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and
Windows



From: Davage, Marcus (ITS Database Services - DB2) <[login to unmask email]>
Sent: Thursday, November 15, 2018 9:27 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: SYSPACKDEP not containing dependencies



RFE anyone?



But, honestly? To admit to a design error is one thing (if followed by an
apology and intent to rectify), but to claim, "Yes, we did actually design
it to do that," with no apology and intent to rectify is something else!
Really, IBM?



Regards,

Marcus Davage CEng CITP MBCS

IBM Certified DB2 Database Administrator

LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG
http://www.idug.org/p/us/sn/uid=24500 | Twitter
http://twitter.com/spufidoo | About.me http://about.me/spufidoo

From: Bell, Raymond (Hosting Services, Technology)
[mailto:[login to unmask email]
Sent: 15 November 2018 14:41
To: '[login to unmask email]' <[login to unmask email]
<mailto:[login to unmask email]> >
Subject: [DB2-L] - RE: SYSPACKDEP not containing dependencies



-- This email has reached the Bank via an external source --


Hi Sally,



If that's truly what's happening then I'd say the design needs changing.
EXPLAIN YES|NO should, IMHO, have no bearing whatsoever on the packages'
recorded object dependencies.



But I'm confused. When you say, 'bind a native stored procedure' do you
mean create a native SQL stored proc which implicitly binds a package?
Because whether to explain the SQL in a native SQL SP is down to whether
WITH EXPLAIN or WITHOUT EXPLAIN is part of the SP definition. I guess you
do. I just did a quick test; I've tried to create a native SQL SP with WITH
EXPLAIN after having dropped the related PLAN_TABLE. Sure enough, I get
+219 (no explain table) and, sure enough, the SP is created, the SP package
exists - and it has no dependencies recorded in Syspackdep! WTAF?



So yes, that's what's truly happening. And yes, the design needs changing.
Because OK, I'd rather not have the bind fail just because a PLAN_TABLE
doesn't exist, but I'd far rather have the package dependencies recorded in
the Catalog correctly.



Cheers,





Raymond



From: Mir, Sally [mailto:[login to unmask email]
Sent: 15 November 2018 13:41
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - SYSPACKDEP not containing dependencies




*********************************************
" This message originates from outside our organisation. Consider carefully
whether you should click on any links, open any attachments or reply. If in
doubt, forward to ~ Phishing"
*********************************************

Six months ago or so, I posted an inquiry asking if anyone had run across
the problem we were having: Native stored procedures with static SQL, and
yes, they are valid, did not have their dependencies recorded in SYSPACKDEP.
This was causing problems for our DBAs, who wanted to find out which
procedures would become invalidated whenever they changed certain tables in
production.



Well, after much digging and back-and-forth with IBM, here is their answer:



If you bind a native stored procedure with VALIDATE(RUN) and EXPLAIN(YES),
and the plan tables (!) do not exist at the time, the dependencies will not
be recorded in the catalog. If you use EXPLAIN(NO), everything will work
OK.



They claim this is working as designed. I am pretty upset about their
answer and am wondering how you all feel about this. The existence of USER
TABLES determines whether or not the catalog is reliable?



Sally A. Mir
Vice President

BB&T Enterprise Services

Software Systems Programmer Specialist
2400 Reynolda Road
001-94-02-80
Winston-Salem, NC 27106
336.733.0946






The information in this transmission may contain proprietary and non-public
information of BB&T or its affiliates and may be subject to protection under
the law. The message is intended for the sole use of the individual or
entity to which it is addressed. If you are not the intended recipient, you
are notified that any use, distribution or copying of the message is
strictly prohibited. If you received this message in error, please delete
the material from your system without reading the content and notify the
sender immediately of the inadvertent transmission.

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


The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered
Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland
is authorised by the Prudential Regulation Authority, and regulated by the
Financial Conduct Authority and Prudential Regulation Authority. The Royal
Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche
Bank and has its seat at Amsterdam, the Netherlands, and is registered in
the Commercial Register under number 33002587. Registered Office: Gustav
Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V.
and The Royal Bank of Scotland plc are authorised to act as agent for each
other in certain jurisdictions.



National Westminster Bank Plc. Registered in England No. 929027.
Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster
Bank Plc is authorised by the Prudential Regulation Authority, and regulated
by the Financial Conduct Authority and the Prudential Regulation Authority.



The Royal Bank of Scotland plc and National Westminster Bank Plc are
authorised to act as agent for each other.



This e-mail message is confidential and for use by the addressee only. If
the message is received by anyone other than the addressee, please return
the message to the sender by replying to it and then delete the message from
your computer. Internet e-mails are not necessarily secure. The Royal Bank
of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank
Plc or any affiliated entity (RBS or us) does not accept responsibility for
changes made to this message after it was sent. RBS may monitor e-mails for
business and operational purposes. By replying to this message you
understand that the content of your message may be monitored.



Whilst all reasonable care has been taken to avoid the transmission of
viruses, it is the responsibility of the recipient to ensure that the onward
transmission, opening or use of this message and any attachments will not
adversely affect its systems or data. No responsibility is accepted by RBS
in this regard and the recipient should carry out such virus and other
checks as it considers appropriate.



Visit our website at www.rbs.com http://www.rbs.com

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



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

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN.
Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ.
Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank Corporate Markets plc. Registered office: 25 Gresham Street,
London EC2V 7HN. Registered in England and Wales no. 10399850.

Lloyds Bank plc, Bank of Scotland plc and Lloyds Bank Corporate Markets plc
are authorised by the Prudential Regulation Authority and regulated by the
Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in
Scotland no. SC218813.

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.



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

Roy Boxwell

SYSPACKDEP not containing dependencies
(in response to Joe Geller)
It would get my vote in one second...



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: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Joe Geller [mailto:[login to unmask email]
Sent: Thursday, November 15, 2018 5:16 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: SYSPACKDEP not containing dependencies



As Mike Vaughan said, +219 says that the statement will be bound dynamically at each execution. Is that correct? I thought that VALIDATE RUN will result in the statement (that failed validation at bind time) being validate and bound at the first execution only (if the missing objects now exist), not dynamically each time. Do I remember correctly?

If a normal package gets a -219, then why should a native SP get only a warning +219?

Finally, just because something is working as designed, doesn't mean that there isn't a bug in the design. I agree it is a bug, not something that should need an RFE.

Joe

In Reply to Daniel Luksetich:

I have not tested it myself, but it is hard to believe that it is working as
designed.

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator - DB2 11 DBA for z/OS

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Application Developer - DB2 11 for z/OS

IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and
Windows



From: Davage, Marcus (ITS Database Services - DB2)
Sent: Thursday, November 15, 2018 9:27 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: SYSPACKDEP not containing dependencies



RFE anyone?



But, honestly? To admit to a design error is one thing (if followed by an
apology and intent to rectify), but to claim, "Yes, we did actually design
it to do that," with no apology and intent to rectify is something else!
Really, IBM?



Regards,

Marcus Davage CEng CITP MBCS

IBM Certified DB2 Database Administrator

LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG
http://www.idug.org/p/us/sn/uid=24500 | Twitter
http://twitter.com/spufidoo | About.me http://about.me/spufidoo

From: Bell, Raymond (Hosting Services, Technology)
[mailto:[login to unmask email]
Sent: 15 November 2018 14:41
To: '[login to unmask email]' <[login to unmask email]
<mailto:[login to unmask email] <mailto:[login to unmask email]%3e> > >
Subject: [DB2-L] - RE: SYSPACKDEP not containing dependencies



-- This email has reached the Bank via an external source --


Hi Sally,



If that's truly what's happening then I'd say the design needs changing.
EXPLAIN YES|NO should, IMHO, have no bearing whatsoever on the packages'
recorded object dependencies.



But I'm confused. When you say, 'bind a native stored procedure' do you
mean create a native SQL stored proc which implicitly binds a package?
Because whether to explain the SQL in a native SQL SP is down to whether
WITH EXPLAIN or WITHOUT EXPLAIN is part of the SP definition. I guess you
do. I just did a quick test; I've tried to create a native SQL SP with WITH
EXPLAIN after having dropped the related PLAN_TABLE. Sure enough, I get
+219 (no explain table) and, sure enough, the SP is created, the SP package
exists - and it has no dependencies recorded in Syspackdep! WTAF?



So yes, that's what's truly happening. And yes, the design needs changing.
Because OK, I'd rather not have the bind fail just because a PLAN_TABLE
doesn't exist, but I'd far rather have the package dependencies recorded in
the Catalog correctly.



Cheers,





Raymond



From: Mir, Sally [mailto:[login to unmask email]
Sent: 15 November 2018 13:41
To: [login to unmask email] <mailto:[login to unmask email] <mailto:[login to unmask email]%3e> >
Subject: [DB2-L] - SYSPACKDEP not containing dependencies




*********************************************
" This message originates from outside our organisation. Consider carefully
whether you should click on any links, open any attachments or reply. If in
doubt, forward to ~ Phishing"
*********************************************

Six months ago or so, I posted an inquiry asking if anyone had run across
the problem we were having: Native stored procedures with static SQL, and
yes, they are valid, did not have their dependencies recorded in SYSPACKDEP.
This was causing problems for our DBAs, who wanted to find out which
procedures would become invalidated whenever they changed certain tables in
production.



Well, after much digging and back-and-forth with IBM, here is their answer:



If you bind a native stored procedure with VALIDATE(RUN) and EXPLAIN(YES),
and the plan tables (!) do not exist at the time, the dependencies will not
be recorded in the catalog. If you use EXPLAIN(NO), everything will work
OK.



They claim this is working as designed. I am pretty upset about their
answer and am wondering how you all feel about this. The existence of USER
TABLES determines whether or not the catalog is reliable?



Sally A. Mir
Vice President

BB&T Enterprise Services

Software Systems Programmer Specialist
2400 Reynolda Road
001-94-02-80
Winston-Salem, NC 27106
336.733.0946






The information in this transmission may contain proprietary and non-public
information of BB&T or its affiliates and may be subject to protection under
the law. The message is intended for the sole use of the individual or
entity to which it is addressed. If you are not the intended recipient, you
are notified that any use, distribution or copying of the message is
strictly prohibited. If you received this message in error, please delete
the material from your system without reading the content and notify the
sender immediately of the inadvertent transmission.

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


The Royal Bank of Scotland plc. Registered in Scotland No 83026. Registered
Office: 36 St Andrew Square, Edinburgh EH2 2YB. The Royal Bank of Scotland
is authorised by the Prudential Regulation Authority, and regulated by the
Financial Conduct Authority and Prudential Regulation Authority. The Royal
Bank of Scotland N.V. is authorised and regulated by the De Nederlandsche
Bank and has its seat at Amsterdam, the Netherlands, and is registered in
the Commercial Register under number 33002587. Registered Office: Gustav
Mahlerlaan 350, Amsterdam, The Netherlands. The Royal Bank of Scotland N.V.
and The Royal Bank of Scotland plc are authorised to act as agent for each
other in certain jurisdictions.



National Westminster Bank Plc. Registered in England No. 929027.
Registered Office: 135 Bishopsgate, London EC2M 3UR. National Westminster
Bank Plc is authorised by the Prudential Regulation Authority, and regulated
by the Financial Conduct Authority and the Prudential Regulation Authority.



The Royal Bank of Scotland plc and National Westminster Bank Plc are
authorised to act as agent for each other.



This e-mail message is confidential and for use by the addressee only. If
the message is received by anyone other than the addressee, please return
the message to the sender by replying to it and then delete the message from
your computer. Internet e-mails are not necessarily secure. The Royal Bank
of Scotland plc, The Royal Bank of Scotland N.V., National Westminster Bank
Plc or any affiliated entity (RBS or us) does not accept responsibility for
changes made to this message after it was sent. RBS may monitor e-mails for
business and operational purposes. By replying to this message you
understand that the content of your message may be monitored.



Whilst all reasonable care has been taken to avoid the transmission of
viruses, it is the responsibility of the recipient to ensure that the onward
transmission, opening or use of this message and any attachments will not
adversely affect its systems or data. No responsibility is accepted by RBS
in this regard and the recipient should carry out such virus and other
checks as it considers appropriate.



Visit our website at www.rbs.com http://www.rbs.com

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



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

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN.
Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ.
Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank Corporate Markets plc. Registered office: 25 Gresham Street,
London EC2V 7HN. Registered in England and Wales no. 10399850.

Lloyds Bank plc, Bank of Scotland plc and Lloyds Bank Corporate Markets plc
are authorised by the Prudential Regulation Authority and regulated by the
Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in
Scotland no. SC218813.

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.



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



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

Attachments

  • smime.p7s (5.1k)

James Campbell

SYSPACKDEP not containing dependencies
(in response to Sally Mir)
I think the underlying misunderstanding here is that some people think the purpose of
SYSPACKDEP is to record tables and views which a package uses. It isn't.

The purpose is to identify packages that have to be invalidated when a table changes in such
a way that the package needs to be rebound. If there is an at-run-time process that can
handle those changes then a SYSPACKDEP row is not required.

Another situation where this happens is using declared temporary tables. As SQL using
these have to be dynamically prepared (after all the DGTT doesn't exist at bind time) a
statement with a DGTT and permanent tables will not result in SYSPACKDEP rows for those
tables. (Other statements might.) But RFE 90391 was rejected.

> whether or not the catalog is reliable?
The catalog is reliable for the purpose for which it is designed. Which is not necessarily what
you would like to use it for.

James Campbell


On 15 Nov 2018 at 13:40, Mir, Sally wrote:

>
> Six months ago or so, I posted an inquiry asking if anyone had run across the problem we were
> having:  Native stored procedures with static SQL, and yes, they are valid, did not have their
> dependencies recorded in SYSPACKDEP.  This was causing problems for our DBAs, who wanted
> to find out which procedures would become invalidated whenever they changed certain tables
> in production.
>  
> Well, after much digging and back-and-forth with IBM, here is their answer:
>  
> If you bind a native stored procedure with VALIDATE(RUN) and EXPLAIN(YES), and the plan
> tables (!) do not exist at the time, the dependencies will not be recorded in the catalog.  If you
> use EXPLAIN(NO), everything will work OK.
>  
> They claim this is working as designed.  I am pretty upset about their answer and am wondering
> how you all feel about this.  The existence of USER TABLES determines whether or not the
> catalog is reliable?
>  
> Sally A. Mir
> Vice President