|
View previous topic :: View next topic
|
| Author |
Message |
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi,
We are maintaining multiple versions of the same package. Usually after the alter of any table we generate the rebind statements from the catalog by assuming the latest package version will be the one which has the latest timestamp.
We had a problem with one of the rebinds recently. When we bound the packages with the above assumption, one of the package failed with -904 when the program executed. Later the oncall DBA had to do a rebind with the correct version of the package. As in this case the latest package version was not the correct version in use.
My question here is - How do we identify the latest package version being executed to generate the rebind statements? |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Gylbharat,
If you are using V10, check SYSPACKAGE table for the column LASTUSED.
What is the query you are currently using to generate REBIND statements after an alter ?
Regards,
Sushanth |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| Unless 'am mistaken -- " SQLCODE -904" is "UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE" -- so is it really a REBIND issue? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Anuj,
There are few instances where I resolved -904 by rebind (I really couldnt recollect)
Also just to add on we might face -904 while BIND when we use EXPLAIN(YES) |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi,
Please find the query below used to generate the rebind cards.
| Code: |
WITH PACKDEP AS (
SELECT DISTINCT 'REBIND ' AS A,
CASE WHEN DTYPE = 'T' THEN 'TRIGGER PACKAGE' ELSE 'PACKAGE' END AS B,
'('||STRIP (DCOLLID, T)||'.'||DNAME||'.('||STRIP(VERSION,T)||')' AS C,
' QUALIFIER('||STRIP(QUALIFIER,T)||')' AS D,
' VALIDATE(BIND)' AS E,
' ISOLATION(UR)' AS F,
' EXPLAIN(YES) ' AS G,
' DYNAMICRULES(RUN)' AS H,
' DEGREE(1 )' AS I,
' DBPROTOCOL(DRDA)' AS J,
' OWNER('||STRIP(OWNER,T)||')' AS K
FROM SYSIBM.SYSPACKDEP A, SYSIBM.SYSPACKAGE B
WHERE A.BTYPE IN ('T','V')
AND A.BNAME IN (
'TABLE01'
)
AND A.BQUALIFIER IN ( 'COLL01')
AND A.DCOLLID = B.COLLID
AND A.DNAME = B.NAME
AND B.BINDTIME = ( SELECT MAX(C.BINDTIME) FROM
SYSIBM.SYSPACKAGE C
WHERE B.COLLID = C.COLLID
AND B.NAME = C.NAME
)
)
SELECT A||B||C||D||E||F||G||H||I||J||K FROM PACKDEP ORDER BY 1 WITH UR;
|
In this we are using MAX(C.BINDTIME) - but this time it picked up the wrong version as the the latest version picked up by this statement was not the currently used version. |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
| I think one way could be - read the consistency token of all the versions and try to match that with the loadlib. But for this it requires lot of automation to be done. If we can find the same using SQL query - from DB2 catalog that would be much better approach. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Pandora-Box -- As Bharat said, emphasis added by me
| Quote: |
| Later the oncall DBA had to do a rebind with the correct version of the package. As in this case the latest package version was not the correct version in use. |
- so in other words, "in-correct version" of the package was used -- for DB2, if any user who attempts to run the 'old version' of the application will receive the SQLCODE -818, indicating a mismatched timestamp error as described here. (though the link is old but still valid).
So the inference from what is posted is -- from DB2 prespective, the resource (version of the package) with which the REBIND was performed was not "available" at the time of REBIND and for DB2 "resource was unavailable" and it issues -904 instead of -818. That's why a question comes, is it really a REBIND issue? |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi Anuj,
I think this package was inoperative as it was bound with incorrect version. When the program ran - DB2 tried to do an automatic bind with the correct package version but some how that failed due to may be catalog contention - which could have resulted in -904. |
|
| Back to top |
|
|
Karthikeyan Subbarayan
New User
Joined: 24 Feb 2008
Posts: 62
Location: Boston
|
|
|
|
| Does your application program try to bind automatically ? |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi,
Its not a rebind issue, the issue is what version of the package is actually being executed.
In a scenario, where you implement something in production and decide to back it out after implementing it, in that case you would put the old load modules back in place and that load will be using the second latest not the latest one.
You are using,
| Code: |
AND B.BINDTIME = ( SELECT MAX(C.BINDTIME) FROM
SYSIBM.SYSPACKAGE C
|
another thought
| Code: |
AND A.TIMESTAMP = (SELECT MAX(TIMESTAMP) FROM SYSIBM.SYSPACKAGE
|
Figuring out what packages are actually being run, i can think of couple of ways, but i haven't tried it out,
1. CA Detector capable of storing all the tracing information in tables underneath, we use to get all the exceptional programs and error details from those tables. You can check if there are any contoken columns there.
2. DB2PM tables
3. From DB2 V10, SYSPACKAGE table for the column LASTUSED
Regards,
Sushanth |
|
| Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi Sushanth,
We are still in the process of migrating to V10.
Can you please elaborate more on
1. CA Detector capable of storing all the tracing information in tables underneath, we use to get all the exceptional programs and error details from those tables. You can check if there are any contoken columns there.
2. DB2PM tables |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi,
CA Detector
All the real-time information which is seen in CA Detector are stored in Datastores(not tables, possibly in VSAM). But detector lets you unload that information and load it into a DB2 Tables, which can be used for analysis. You can get details regarding it from Detector User Guide & Reference Manauals. This word document, which i found by googling, check this out, it will give you an idea.
My thought is, if you can see the consistency tokens of packages in CA-Detector when drilling-down, it will be definately available in the datastore, so there is a possibility to unload from datastore and load it into a table.
Setting it up will be a pretty big task, in my previous company the process was already setup by CA person, when the product was bought.
DB2PM Tables
If you have DB2 Performance Database already setup to capture the required information. You can get the details on the package and its token from the table DB2PMFACCT_PROGRAM and columns(PCK_COLLECTION_ID, PCK_ID, PCK_CONSIST_TOKEN).
Thanks,
sushanth |
|
| Back to top |
|
|
|
|
