Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQLCODE -805 but the collection id is in Plan

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: SQLCODE -805 but the collection id is in Plan
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10211
Location: italy

PostPosted: Sun Nov 04, 2007 6:19 pm    Post subject: Reply to: SQLCODE -805 but the collection id is in Plan
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    Post subject:
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: 149
Location: Ottawa Canada

PostPosted: Tue Nov 06, 2007 1:55 am    Post subject:
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    Post subject:
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: 149
Location: Ottawa Canada

PostPosted: Tue Nov 06, 2007 8:27 pm    Post subject:
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    Post subject: great explanation
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: Reply to: SQLCODE -805 but the collection id is in Plan
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    Post subject:
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: 149
Location: Ottawa Canada

PostPosted: Tue Nov 20, 2007 1:32 am    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Program and its corresponding plan vickey_dw DB2 4 Thu Apr 07, 2016 9:27 pm
No new posts Should we Rebind Plan if no SQL changes sappy_mf DB2 2 Thu Mar 03, 2016 2:13 pm
No new posts What's plan, package, bind ? jackzhang75 DB2 2 Sun Feb 28, 2016 7:29 pm
No new posts SQLCODE-000000080N priya91 DB2 1 Mon Feb 01, 2016 4:35 pm
No new posts DB2 plan for DSNTEP2 / DSNTEP4 Mainak_Dalal DB2 3 Thu Jan 21, 2016 1:08 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us