DB2 Precompile, Plan, Package and bind



IBM's flagship relational database management system

DB2 Precompile, Plan, Package and bind

Postby km_abdullah » Thu Dec 10, 2009 4:22 pm

Hi,

I'm a beginner in DB2.

Regarding precompile, till now what i have understood are -

1. During precompile, the precompiler places the SQL statements in DBRM and would replace all SQL statements in a COBOL-DB2 program to 'CALL' statements (this would essentially call SQL statements in DBRM during run time). The order of SQL statements in COBOL program and DBRM would be same.
DBRM will contain SQL statements of various program at the same time.
2. Also, the precompiler would generate Timestamp for each SQL statement so that Timestamp for any SQL statement in COBOL program and in DBRM is same.
3. The 'Pure' COBOL program is now compiled, link edited to form a load module
4. The DBRM is bound (BIND). Then DB2 stores the DBRM timestamp for run time use.
This ends the formation of load module steps (i believe)

During run time, the timestamp in COBOL program is compared with timestamp in DBRM to ensure that COBOL program executes correct SQL statements.

My Questions are,

1) During run time, when time stamp is checked, how does system identifies the SQL statement of a particular COBOL program in DBRM when there are SQL statements of various programs in DBRM ? If the answer is 'timestamp', then why does -818 error (timestamp mismatch) occur.
2) What is a Plan,
3) What is a Package,
4) What is Bind Plan / Package
5) Also please tell me whether my understanding so far is correct


Your help would address a long standing doubt in me.
Thanks in advance.
Sorry if i have written a big story.

Regards,
Mohamed Abdullah
km_abdullah
 
Posts: 4
Joined: Thu Dec 10, 2009 11:04 am
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Precompile, Plan, Package and bind

Postby dick scherrer » Fri Dec 11, 2009 3:10 am

Hello and welcome to the forum,

Suggest you become familiar and comfortable with the db2 documentation. Here are links to the SQL Reference and the Programming Guide for DB2 V8:

http://publibz.boulder.ibm.com/cgi-bin/ ... 0/CONTENTS

http://publibz.boulder.ibm.com/cgi-bin/ ... 0/CONTENTS

More manuals for V8 as well as manuals for all current releases of DB2 are aslo available from IBM for free.

If you find something in a manual that is not clear, post what you found and your doubt and someone should be able to clarify.

Sorry if i have written a big story.
Not so long - not to worry :)

The forum is set up to answer questions/solve problems rather than provide training. It is just too large an undertaking.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: DB2 Precompile, Plan, Package and bind

Postby GuyC » Tue Dec 15, 2009 6:37 pm

- a DBRM does not contain SQL from different programs.

Some confusion about version and timestamp :
- A version is a unique identifier you specify during recompile, if you do not specify DB2 will use the timestamp of the precompile as version.
- the same timestamp is also kept in the DBRM
- by specifying a version you can have the same version but with different timestamps
So in most sites the version contains a timestamp but it is not always the timestamp of the -818

Plans and package have an history with different possibilities, but I'll focus on the one that should be used .
- when a DBRM is bound it becomes a package/version in a specific collection, (like a cobol becomes a load)
- a plan is what you say to the program where to look for a package (in JCL or PCT).
- a plan is a list of collections
- a collection is a list of packages.

At runtime you specify the plan. that plan is searched in order in each collecion of the plan to find a matching package / version.
When not found -805
when found but the timestamps don't match -818
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: DB2 Precompile, Plan, Package and bind

Postby GuyC » Tue Dec 15, 2009 6:42 pm

each statement of DBRM receives a unique STMTNO
this STMTNO is both known in the load as in the package.
It is not especially the "order" of statements that has to be equal.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: DB2 Precompile, Plan, Package and bind

Postby km_abdullah » Wed Jun 02, 2010 5:19 pm

I am getting back on this -

At runtime you specify the plan. that plan is searched in order in each collecion of the plan to find a matching package / version.
When not found -805


I receive -805 error saying DBRM or Package is not found in PLAN.

Could you tell me how to view all packages that is linked to a plan in any DB2 catalogue tables or by any other way?
km_abdullah
 
Posts: 4
Joined: Thu Dec 10, 2009 11:04 am
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Precompile, Plan, Package and bind

Postby dick scherrer » Wed Jun 02, 2010 11:27 pm

Hello,

I found this - have not verified it. . . Maybe this will help?
Usually a PLAN connects to a Package using Collection-id(also called packlist or pklist). When a PLAN is bound, in such a scenario, usually packlist is given a wild card or *. And no package name is given.

So check in a catalog table called sysibm.syspacklist to see if you have a PLANNAME that matches your plan name and it has a matching COLLID. By matching COLLID I mean the value you use to do SET PACKAGESET in the program.

Then check in sysibm.syspackage if you have a NAME and COLLID that match the package you are calling.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post