IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Rebind issue


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Wed Apr 24, 2013 7:59 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Thu Apr 25, 2013 9:29 am
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu Apr 25, 2013 10:14 am
Reply with quote

Unless 'am mistaken -- " SQLCODE -904" is "UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE" -- so is it really a REBIND issue?
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy

PostPosted: Thu Apr 25, 2013 10:31 am
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Apr 25, 2013 2:01 pm
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Apr 25, 2013 2:05 pm
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu Apr 25, 2013 6:55 pm
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Thu Apr 25, 2013 7:23 pm
Reply with quote

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
View user's profile Send private message
Karthikeyan Subbarayan

New User


Joined: 24 Feb 2008
Posts: 62
Location: Boston

PostPosted: Fri Apr 26, 2013 1:54 am
Reply with quote

Does your application program try to bind automatically ?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Apr 26, 2013 10:33 am
Reply with quote

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
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Apr 26, 2013 2:06 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Apr 26, 2013 3:30 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Issue after ISPF copy to Linklist Lib... TSO/ISPF 1
No new posts Facing ABM3 issue! CICS 3
No new posts Panvalet - 9 Character name - Issue c... CA Products 6
No new posts Issue with EXEC CICS QUERY SECURITY c... CICS 6
Search our Forums:

Back to Top