SYSPACKDEP missing entries for native stored procedures?

Sally Mir

SYSPACKDEP missing entries for native stored procedures?
Has anyone noticed that some packages for native stored procedures do not have entries in SYSPACKDEP? We ran a query:

WITH PKGS AS
( SELECT SUBSTR(STRIP(COLLID) || '.' || STRIP(NAME),1,40) PKGID
FROM SYSIBM.SYSPACKAGE P
WHERE
TYPE = 'N'
AND NOT EXISTS
(SELECT 1
FROM SYSIBM.SYSPACKDEP D
WHERE D.DNAME = P.NAME
AND D.DCOLLID = P.COLLID
AND D.DLOCATION = P.LOCATION
AND D.DCONTOKEN = P.CONTOKEN
FETCH FIRST 1 ROW ONLY))
SELECT *
FROM PKGS
ORDER BY PKGID
FETCH FIRST 500 ROWS ONLY
WITH UR
;

and came up with several. Just wondering if anyone else has had this issue.... Or knows why this is happening....

Sally A. Mir
BB&T Mainframe Support
2825 Reynolda Road
001-93-01-30
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.

alain pary

RE: SYSPACKDEP missing entries for native stored procedures?
(in response to Sally Mir)

Hello Sally, 

It is perhaps Dynamic SQL , in this case there is no link with db2 objects. 

Try to see the contents in sysibm.sysroutines to have a view upon the content .

regards 

Alain

Jørn Thyssen

RE: SYSPACKDEP missing entries for native stored procedures?
(in response to Sally Mir)

Hi Sally,

Your query gives two hits on my demo system at Rocket Software.

The first procedure has no SQL at all (only a SET ... statement)

The other procedure has dynamic SQL only as mentioned by Alain

 

Best regards,

Jørn Thyssen

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

2018 IBM Champion.

Views are personal. 

Chris Tee

SYSPACKDEP missing entries for native stored procedures?
(in response to Sally Mir)
Sally


Could it be because the package is no longer valid? Try adding AND VALID <> 'N' to see if that makes any difference. Other than that, it could be a bug, there have been a number of APARs for missing SYSPACKDEP entries in the past.


regards


Chris


________________________________
From: Mir, Sally <[login to unmask email]>
Sent: 13 June 2018 13:38
To: [login to unmask email]
Subject: [DB2-L] - SYSPACKDEP missing entries for native stored procedures?


Has anyone noticed that some packages for native stored procedures do not have entries in SYSPACKDEP? We ran a query:



WITH PKGS AS

( SELECT SUBSTR(STRIP(COLLID) || '.' || STRIP(NAME),1,40) PKGID

FROM SYSIBM.SYSPACKAGE P

WHERE

TYPE = 'N'

AND NOT EXISTS

(SELECT 1

FROM SYSIBM.SYSPACKDEP D

WHERE D.DNAME = P.NAME

AND D.DCOLLID = P.COLLID

AND D.DLOCATION = P.LOCATION

AND D.DCONTOKEN = P.CONTOKEN

FETCH FIRST 1 ROW ONLY))

SELECT *

FROM PKGS

ORDER BY PKGID

FETCH FIRST 500 ROWS ONLY

WITH UR

;



and came up with several. Just wondering if anyone else has had this issue…. Or knows why this is happening….



Sally A. Mir
BB&T Mainframe Support
2825 Reynolda Road
001-93-01-30
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-----

Sally Mir

SYSPACKDEP missing entries for native stored procedures?
(in response to Chris Tee)
No, the packages are valid. And the SQL is static, not dynamic, as others have suggested.

Hmmm....

Sally A. Mir
BB&T Mainframe Support
2825 Reynolda Road
001-93-01-30
Winston-Salem, NC 27106
336.733.0946


From: Chris Tee [mailto:[login to unmask email]
Sent: Wednesday, June 13, 2018 12:29 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: SYSPACKDEP missing entries for native stored procedures?


Sally



Could it be because the package is no longer valid? Try adding AND VALID <> 'N' to see if that makes any difference. Other than that, it could be a bug, there have been a number of APARs for missing SYSPACKDEP entries in the past.



regards



Chris

________________________________
From: Mir, Sally <[login to unmask email]>
Sent: 13 June 2018 13:38
To: [login to unmask email]
Subject: [DB2-L] - SYSPACKDEP missing entries for native stored procedures?


Has anyone noticed that some packages for native stored procedures do not have entries in SYSPACKDEP? We ran a query:



WITH PKGS AS

( SELECT SUBSTR(STRIP(COLLID) || '.' || STRIP(NAME),1,40) PKGID

FROM SYSIBM.SYSPACKAGE P

WHERE

TYPE = 'N'

AND NOT EXISTS

(SELECT 1

FROM SYSIBM.SYSPACKDEP D

WHERE D.DNAME = P.NAME

AND D.DCOLLID = P.COLLID

AND D.DLOCATION = P.LOCATION

AND D.DCONTOKEN = P.CONTOKEN

FETCH FIRST 1 ROW ONLY))

SELECT *

FROM PKGS

ORDER BY PKGID

FETCH FIRST 500 ROWS ONLY

WITH UR

;



and came up with several. Just wondering if anyone else has had this issue.... Or knows why this is happening....



Sally A. Mir
BB&T Mainframe Support
2825 Reynolda Road
001-93-01-30
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-----

alain pary

RE: SYSPACKDEP missing entries for native stored procedures?
(in response to Sally Mir)

Hello, 

I make some xtra searchs .

package in my list are Dynamic  or have validate = 'R'   ==> in each case db2 evaluate the accespath and the objects need at run time and not at bind time .

regards 

 

Alain

Chris Tee

SYSPACKDEP missing entries for native stored procedures?
(in response to alain pary)
Alain / Sally


Yes, I thought it might be to do with that but, when I checked a stored proc with VALIDATE RUN, the SYSPACKDEP entries were present. On further investigation, I found that if the dependent objects did not exist at SP creation time, you get a +204, the package for the SP is bound but the relevant SYSPACKDEP entries aren't created. If the dependent objects do exist at creation time, then the SYSPACKDEP entries will be there.


regards


Chris

________________________________
From: alain pary <[login to unmask email]>
Sent: 14 June 2018 07:30
To: [login to unmask email]
Subject: [DB2-L] - RE: SYSPACKDEP missing entries for native stored procedures?


Hello,

I make some xtra searchs .

package in my list are Dynamic or have validate = 'R' ==> in each case db2 evaluate the accespath and the objects need at run time and not at bind time .

regards



Alain

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

alain pary

RE: SYSPACKDEP missing entries for native stored procedures?
(in response to alain pary)

Hello, 

 

with this query you will eliminate 

validate(Bind)

and dynamic package   (avoid prepare statement) 

 

WITH PKGS AS
(SELECT SUBSTR(STRIP(COLLID) || '.' || STRIP(NAME), 1, 40) PKGID
FROM SYSIBM.SYSPACKAGE P
WHERE TYPE = 'N'
AND VALIDATE = 'B'
AND NOT EXISTS
(SELECT 1
FROM SYSIBM.SYSPACKDEP D
WHERE D.DNAME = P.NAME
AND D.DCOLLID = P.COLLID
AND D.DLOCATION = P.LOCATION
AND D.DCONTOKEN = P.CONTOKEN FETCH FIRST 1 ROW ONLY)
AND NOT EXISTS
(SELECT 1
FROM SYSIBM.SYSPACKSTMT PSTMT
WHERE PSTMT.COLLID= P.COLLID
AND PSTMT.NAME = P.NAME
AND PSTMT.CONTOKEN = P.CONTOKEN
AND CAST(STATEMENT AS CHAR(50)) LIKE 'PREPARE%' FETCH FIRST 1 ROW ONLY))
SELECT *
FROM PKGS
ORDER BY PKGID FETCH FIRST 500 ROWS ONLY WITH UR

 

I have after that only 135  packages in place of 1344 with the first query.

If anybody have other ideas to explain those remaining packages

Alain

alain pary

RE: SYSPACKDEP missing entries for native stored procedures?
(in response to alain pary)

Hello, 

 

Here is my last finding the package must be VALID 

now , only one remaining package   ;-) 

 

WITH PKGS AS
(SELECT SUBSTR(STRIP(COLLID) || '.' || STRIP(NAME), 1, 40) PKGID,
VERSION
FROM SYSIBM.SYSPACKAGE P
WHERE TYPE = 'N'
AND VALIDATE = 'B'
AND VALID = 'Y'
AND NOT EXISTS
(SELECT 1
FROM SYSIBM.SYSPACKDEP D
WHERE D.DNAME = P.NAME
AND D.DCOLLID = P.COLLID
AND D.DLOCATION = P.LOCATION
AND D.DCONTOKEN = P.CONTOKEN FETCH FIRST 1 ROW ONLY)
AND NOT EXISTS
(SELECT 1
FROM SYSIBM.SYSPACKSTMT PSTMT
WHERE PSTMT.COLLID= P.COLLID
AND PSTMT.NAME = P.NAME
AND PSTMT.CONTOKEN = P.CONTOKEN
AND UPPER(CAST(STATEMENT AS CHAR(50))) LIKE '%PREPARE%' FETCH FIRST 1 ROW ONLY))
SELECT *
FROM PKGS
ORDER BY PKGID FETCH FIRST 500 ROWS ONLY WITH UR

 

Regards 

Alain

Patrick Bossman

RE: SYSPACKDEP missing entries for native stored procedures?
(in response to alain pary)

Some customers bind with validate run first, to avoid having to sequence references to other stored procedures.  Then they can run create in any order.  Then they follow deployment with rebind, which will resolve all of the dependencies and avoid runtime performance overhead of incremental rebind.

Sally Mir

SYSPACKDEP missing entries for native stored procedures?
(in response to Chris Tee)
Well, that certainly sounds plausible, and I got excited for a moment, thinking that it was the answer, but unfortunately, I found a case where the tables were created a day before the stored procedure was.

So still no explanation for our case.

Sally A. Mir
BB&T Mainframe Support
2825 Reynolda Road
001-93-01-30
Winston-Salem, NC 27106
336.733.0946


From: Chris Tee [mailto:[login to unmask email]
Sent: Thursday, June 14, 2018 4:08 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: SYSPACKDEP missing entries for native stored procedures?


Alain / Sally



Yes, I thought it might be to do with that but, when I checked a stored proc with VALIDATE RUN, the SYSPACKDEP entries were present. On further investigation, I found that if the dependent objects did not exist at SP creation time, you get a +204, the package for the SP is bound but the relevant SYSPACKDEP entries aren't created. If the dependent objects do exist at creation time, then the SYSPACKDEP entries will be there.


regards



Chris

________________________________
From: alain pary <[login to unmask email]>
Sent: 14 June 2018 07:30
To: [login to unmask email]
Subject: [DB2-L] - RE: SYSPACKDEP missing entries for native stored procedures?


Hello,

I make some xtra searchs .

package in my list are Dynamic or have validate = 'R' ==> in each case db2 evaluate the accespath and the objects need at run time and not at bind time .

regards



Alain

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


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.

Tami Van Dreese

RE: SYSPACKDEP missing entries for native stored procedures?
(in response to Sally Mir)

Sally,

We discovered a similar situation on a Windows database used by Federated Server a few months ago. I opened a PMR with IBM and they were able to reproduce it as a problem. I haven’t heard of any fix available yet.

In our case, we had a plan with all of the nickname objects listed in sysplandep. If we dropped and recreated the nicknames, the rows still existed in sysplandep but the plan was marked as invalid.  The next time the plan ran, it triggered an autorebind and after that some of the rows were missing from sysplandep. My guess is that the missing objects weren’t referenced in that run, but I don’t know for sure. The plan was bound as validate run. When we rebound the plan, all the rows were back in sysplandep.

Tami Van Dreese