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

SQLCODE -805 but the collection id is in Plan


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

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Sun Nov 04, 2007 4:04 am
Reply with quote

Hi,

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

Quote:
DSNT408I SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME RGN4..ITDE02U1.181C26B80 NOT FOUND IN PLAN SAMPLE4. REASON
03
DSNT418I SQLSTATE = 51002 SQLSTATE RETURN CODE

DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING ERROR

DSNT416I SQLERRD = -251 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION

DSNT416I SQLERRD = X'FFFFFF05' X'00000000' X'00000000' X'FFFFFFFF' X'00



Any hint from these messages?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10888
Location: italy

PostPosted: Sun Nov 04, 2007 6:19 pm
Reply with quote

Quote:
Any hint from these messages?


You would have gotten an hint if You had read up to the fourth line...

You should not stop at the first status code....
-805 is a container for more codes related to plan/package/collection/dbrm mismatches

if You look at the manual SQL codes with all the info You have
available I am sure You can find out by Yourself what' s wrong in Your sql statement

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnc1k10/APPENDIX1.3?ACTION=MATCHES&REQUEST=-251&TYPE=FUZZY&SHELF=DSNSHKA0&DT=20070131011436&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Mon Nov 05, 2007 7:59 pm
Reply with quote

Hi e.s.,

Below is the message from the link,

Quote:
A character that is invalid in a name has been
detected


But the same code is running fine in other regions.
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 148
Location: Ottawa Canada

PostPosted: Tue Nov 06, 2007 1:55 am
Reply with quote

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.
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Tue Nov 06, 2007 4:52 am
Reply with quote

Hi,

Sorry for the confusion, the actual contoken is

Quote:
RGN4..TESTPGM2.181C26B80FEB8E57 NOT FOUND IN PLAN SAMPLE4


But i cannot understand identifying the following,

Quote:
The contoken shows up in the following locations:

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



Could you please help on this?
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 148
Location: Ottawa Canada

PostPosted: Tue Nov 06, 2007 8:27 pm
Reply with quote

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.
Back to top
View user's profile Send private message
sandy_venkat

New User


Joined: 16 May 2007
Posts: 35
Location: India

PostPosted: Thu Nov 08, 2007 10:18 pm
Reply with quote

Hi Murphy,

The explanation was fantastic...It was really very useful. I did not know these concepts. thanks a lot....[/img]
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Nov 09, 2007 9:27 am
Reply with quote

nbalajibe,

is testpgm2 a main module or is it statically linked / CALLed by any modules?
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Thu Nov 15, 2007 5:06 am
Reply with quote

Hi Dick,

Testpgm2 is a subroutine.
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Thu Nov 15, 2007 6:28 pm
Reply with quote

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?
Back to top
View user's profile Send private message
frudo

New User


Joined: 16 Aug 2005
Posts: 5

PostPosted: Fri Nov 16, 2007 12:46 pm
Reply with quote

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.
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Tue Nov 20, 2007 12:29 am
Reply with quote

Hi,

How can we find out the contoken of a already existing module?
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 148
Location: Ottawa Canada

PostPosted: Tue Nov 20, 2007 1:32 am
Reply with quote

Use Platinum to browse a list of DB2 packages. You will see the contoken value for each package.
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Tue Nov 20, 2007 1:37 am
Reply with quote

Hi Murphy,

Is Platinum a utility?
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 SQLCODE=-311 in Cobol SP-DB2. COBOL Programming 2
No new posts SQLCODE = -122 while using the scalar... DB2 4
No new posts SQLCODE = -16002 when using XMLEXISTS DB2 1
No new posts current batch plan DB2 3
No new posts Is SQLCODE -811 possible while fetchi... DB2 1
Search our Forums:

Back to Top