SQLCODE -805 but the collection id is in Plan

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi,

I am getting -805 SQLCODE eventhough the collection id is present in plan. The sysout messages are as follows,

enrico-sorichetti · Posted: Sun Nov 04, 2007 6:19 pm

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi e.s.,

Below is the message from the link,

TG Murphy · Posted: Tue Nov 06, 2007 1:55 am

I noticed something strange about your contoken. Your message says the contoken is:

181C26B80

This is not a valid contoken. Our contokens look like this...

... ZX4RROA.182DF20B050FA411 NOT FOUND IN PLAN ZXY1UL.

(Maybe your contoken is fine - just that I have never seen one like yours)

Anyways - if you know for a fact that the plan you are running under has access to the collection that contains your package - then the most likely cause is that your JCL is not using the right load library OR your compile JCL is not sticking the load module in the right load library.

The contoken shows up in the following locations:

1. DB2 catalog
2. Embedded in your DBRMLIB member
3. Embedded in the load module.

When you hunt for contoken in the load module - know that the contoken value gets cut in half and the locations are switched. For example - using my contoken as an example - 182DF20B050FA411 - you must search using this...

FIND '050FA411182DF20B'x

Anyways - the contoken is key to solving these -805s.

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi,

Sorry for the confusion, the actual contoken is

TG Murphy · Posted: Tue Nov 06, 2007 8:27 pm

When you compile and bind a program, there are 2 outputs that get produced. One is the load module and the other is the DB2 package. (Actually there is a 3rd called the DBRMLIB but you can ignore that one)

When you go to run your program, you must run with the same outputs that were created by your compile step. Think of your load module and its corresponding package as tag-team wrestling partners who insist on always wrestling together.

However, when you execute your program it can happen that...

1. You do not pick up the correct version of the load module.
2. You pick up the correct version of the load module BUT the correct version of the package cannot be found. There are a few reasons why a package cannot be found.

2.1 It was bound into the wrong collection and your plan does not have access to it. To fix this, you must fix your BIND command.
2.2 It was deleted. To fix this, simply recompile/bind.
2.3 The plan you are running under does not have access to the collection that the package lives in. Ask your DBAs to add the collection to your plan.

The DB2 people knew that these types of mistakes were very likely to happen. So what they did was they invented the contoken. When a program is compiled and bound all of its outputs are stamped with the same contoken value.

The contoken gets stamped on the package and if you query the DB2 catalog tables you will find it. (We use a product called Platinum to do this). As well, the same contoken value gets embedded inside the load module. You may be aware that the compile date/time also gets stamped inside the load module so this is another useful piece of info that you can use to diagnose the -805.

Anyways - your error message contains the contoken. This value is the value that is embedded within your load module. Your load module passing this contoken to DB2 and DB2 uses this contoken as the search key in order to find the correct version of the package. Basically - what happened is that DB2 failed to find a match using the contoken value.

So search the catalog for a package that has this contoken value. If you find a match, then likely the problem is with your plan - it does not contain the correct collection.

One thing that happens to us often is this. A unit test load module is missing but because the JOBLIB concatenation brings in libraries from the upper testing environments the wrong version of the load module gets used. Typically - a unit test plan will not have access to any of the packages that belong to these upper environments.

sandy_venkat · New User Joined: 16 May 2007 Posts: 35 Location: India

Hi Murphy,

The explanation was fantastic...It was really very useful. I did not know these concepts. thanks a lot....[/img]

dbzTHEdinosauer · Posted: Fri Nov 09, 2007 9:27 am

nbalajibe,

is testpgm2 a main module or is it statically linked / CALLed by any modules?

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi Dick,

Testpgm2 is a subroutine.

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi Murphy,

In the above scenario we are having the contoken in the error message, but in some cases these are not displayed (Only the SQLCODE is displayed). How can we find out the contoken in such cases?

frudo · New User Joined: 16 Aug 2005 Posts: 5

If testpgm2 is a subprogram, then do the following,
check if the programs which call this testpgm2 has other subprograms which contain sql stms. If so,then you will need to include
the package of testpgm2 in the pklist of the Plan or include the member of (testpgm2) in the plan (depending on how the plan of the driver pgm is defined) and then bind.

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi,

How can we find out the contoken of a already existing module?

TG Murphy · Posted: Tue Nov 20, 2007 1:32 am

Use Platinum to browse a list of DB2 packages. You will see the contoken value for each package.

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi Murphy,

Is Platinum a utility?