DB2 Bind & Plan

Robby Khanal

DB2 Bind & Plan
DB2 Experts -



I am new to DB2 - transitioning from Adabas - which is non-SQL and
non-relational DB.



Can you please explain briefly what a DB2 bind and a DB2 Plan/Packages
are? I am having hard time grasping this concept.



Any info on this topic will be appreciated.



Thanks,

Robby Khanal




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: DB2 Bind & Plan
(in response to Robby Khanal)
WOW

You're going to get a LOT of different explanations, so let me try first :)

When you write SQL, you say WHAT you want but you do not tell DB2 HOW to get the data for you

BINDing is the process of DB2 determining HOW best to get your data (the key word being BEST - this is not just any old way of getting the data, but the least expensive based on some fairly sophisticated cost-based algorithms)

A PLAN or PACKAGE is the result of the BIND and contains DB2s executable instructions on HOW to execute the SQL in your program

So, you end up with an executable load module and a PLAN (or PACKAGE) that goes with it

Then we get into the discussion of STATIC vs DYNAMIC SQL. All that I said above refers to STATIC (the access path to your data is STATIC in the PLAN/PACKAGE)

With DYNAMIC SQL, the access path is determined by DB2 at run time (not in advance) through a process called PREPAREing

Hope this helps

Phil Grainger
Product Management Director
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Khanal Robby (DTI)
Sent: Tue 20/11/2007 18:47
To: [login to unmask email]
Subject: [DB2-L] DB2 Bind & Plan



DB2 Experts -



I am new to DB2 - transitioning from Adabas - which is non-SQL and non-relational DB.



Can you please explain briefly what a DB2 bind and a DB2 Plan/Packages are? I am having hard time grasping this concept.



Any info on this topic will be appreciated.



Thanks,

Robby Khanal



--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Isaac Yassin

Re: DB2 Bind & Plan
(in response to Phil Grainger)
Hi,



All the best in the new venue ;-)

Think of BIND as the work DB2 does at compile time to connect the program to the DBMS as the STOW in Natural does.

It's not the same but gives you the idea.



Isaac Yassin

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Khanal Robby (DTI)
Sent: Tuesday, November 20, 2007 8:48 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Bind & Plan



DB2 Experts –



I am new to DB2 – transitioning from Adabas – which is non-SQL and non-relational DB.



Can you please explain briefly what a DB2 bind and a DB2 Plan/Packages are? I am having hard time grasping this concept.



Any info on this topic will be appreciated.



Thanks,

Robby Khanal



--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go
to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The
IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.0/1139 - Release Date: 19/11/2007 12:35


No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.0/1139 - Release Date: 19/11/2007 12:35


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

George Lewandowski

Re: DB2 Bind & Plan
(in response to Isaac Yassin)
Here is a RedBook that may help.

http://www.redbooks.ibm.com/abstracts/gg244001.html?Open#alsodownloaded



George H Lewandowski
Database Administrator - High Availability Database Support
GTS Database Engineering
GB17, J46-2E

Work (847) 938-5526
Pager (800) 209-1305
[login to unmask email]




This communication may contain information that is proprietary,
confidential, or exempt from disclosure. If you are not the intended
recipient, please note that any other dissemination, distribution, use or
copying of this communication is strictly prohibited. Anyone who receives
this message in error should notify the sender immediately by telephone or
by return e-mail and delete it from his or her computer.




Isaac Yassin <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
11/20/2007 01:56 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] DB2 Bind & Plan






Hi,

All the best in the new venue ;-)
Think of BIND as the work DB2 does at compile time to connect the program
to the DBMS as the STOW in Natural does.
It's not the same but gives you the idea.

Isaac Yassin
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Khanal Robby (DTI)
Sent: Tuesday, November 20, 2007 8:48 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 Bind & Plan

DB2 Experts -

I am new to DB2 - transitioning from Adabas - which is non-SQL and
non-relational DB.

Can you please explain briefly what a DB2 bind and a DB2 Plan/Packages
are? I am having hard time grasping this concept.

Any info on this topic will be appreciated.

Thanks,
Robby Khanal

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.0/1139 - Release Date:
19/11/2007 12:35

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.0/1139 - Release Date:
19/11/2007 12:35
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

Peter Vanroose

Re: DB2 Bind & Plan
(in response to George Lewandowski)
Binding is sort of compiling the SQL part of a source program.
The resulting Plan hence is the executable (within DB2) part of the
main program.
When running the executable, you are actually running both the pgm
(outside DB2) and the plan (inside DB2).

A Package is similar, but is the "compiled" SQL part of a subprogram,
just like e.g. a dynamic link library (DLL) or shared library (.so or
.sl) is the compiled version of a source subprogram.
Hence, one (or more) Packages will be called by a Plan; or: a set of
Packages can be bound into a Plan.

-- Peter Vanroose
ABIS Training & Consulting.


















___________________________________________________
Sök efter kärleken!
Hitta din tvillingsjäl på Yahoo! Dejting: http://se.meetic.yahoo.net

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Parvathavardhini Kannan

Re: DB2 Bind & Plan
(in response to Peter Vanroose)
Hi,

Below links contain articles on DB2 Bind, Plan etc which is easy to
understand :

http://www.db2mag.com/story/showArticle.jhtml?articleID=15300107
http://www.db2mag.com/story/showArticle.jhtml?articleID=17602333
http://db2mag.com/story/showArticle.jhtml?articleID=18901299


Thanks,
Vardhini
Technical Leader
DB2 DBA Group - INDIA,
iNautix Technologies India Private Limited,


DB2 Data Base Discussion List <[login to unmask email]> wrote on 11/21/2007
12:17:57 AM:

> DB2 Experts -
>
> I am new to DB2 - transitioning from Adabas - which is non-SQL and
> non-relational DB.
>
> Can you please explain briefly what a DB2 bind and a DB2
> Plan/Packages are? I am having hard time grasping this concept.
>
> Any info on this topic will be appreciated.
>
> Thanks,
> Robby Khanal
>
>
---------------------------------------------------------------------------------

> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
> and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
> that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
at
> http://conferences.idug.org/index.cfm

******************************************************
This message and any files or attachments sent with this message contain confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, copy or use any part of this email. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return Email.

Email transmission cannot be guaranteed to be secure or error-free as information can be intercepted, corrupted, lost, destroyed, late, incomplete or may contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message, which arise as a result of email transmission.
******************************************************

Parvathavardhini Kannan

Re: DB2 Bind & Plan
(in response to Parvathavardhini Kannan)

Hi,

Below links contain articles on DB2 Bind, Plan etc which is easy to
understand :

http://www.db2mag.com/story/showArticle.jhtml?articleID=15300107
http://www.db2mag.com/story/showArticle.jhtml?articleID=17602333
http://db2mag.com/story/showArticle.jhtml?articleID=18901299


Thanks,
Vardhini
Technical Leader
DB2 DBA Group - INDIA,
iNautix Technologies India Private Limited,


DB2 Data Base Discussion List <[login to unmask email]> wrote on 11/21/2007
12:17:57 AM:

> DB2 Experts -
>
> I am new to DB2 - transitioning from Adabas - which is non-SQL and
> non-relational DB.
>
> Can you please explain briefly what a DB2 bind and a DB2
> Plan/Packages are? I am having hard time grasping this concept.
>
> Any info on this topic will be appreciated.
>
> Thanks,
> Robby Khanal
>
>
---------------------------------------------------------------------------------

> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives
> and home page at http://www.idugdb2-l.org/archives/db2-l.html. From
> that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
at
> http://conferences.idug.org/index.cfm

******************************************************
This message and any files or attachments sent with this message contain confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, copy or use any part of this email. If you have received this message in error, please delete it and all copies from your system and notify the sender immediately by return Email.

Email transmission cannot be guaranteed to be secure or error-free as information can be intercepted, corrupted, lost, destroyed, late, incomplete or may contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message, which arise as a result of email transmission.
******************************************************

Robert Catterall

Re: DB2 Bind & Plan
(in response to Parvathavardhini Kannan)
I wrote an article on this topic in DB2 Magazine.  It might help to answer some of your questions.  Here's a link to the article:

http://www.db2mag.com/db_area/archives/2001/q1/db2dba.shtml

Robert
 

Khanal Robby (DTI) wrote:

DB2 Experts –

 

I am new to DB2 – transitioning from Adabas – which is non-SQL and non-relational DB. 

 

Can you please explain briefly what a DB2 bind and a DB2 Plan/Packages are?  I am having hard time grasping this concept. 

 

Any info on this topic will be appreciated.

 

Thanks,

Robby Khanal

 

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. >From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
--
Robert Catterall
President
Catterall Consulting
770-263-5322

www.catterallconsulting.com

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: DB2 Bind & Plan
(in response to Robert Catterall)
Bob,

Thanks for that link. I've got a couple of people I'm trying to break in to packages who need a good explanation.

--Phil Sevetson, NYCAPS DBA Support
Financial Information Services Agency of The City of New York
450 West 33rd Street, 4th Floor
New York, NY 10001
phone: (212) 857-1688
mailto: [login to unmask email]
________________________________________
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Robert Catterall
Sent: Thursday, November 22, 2007 9:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Bind & Plan

I wrote an article on this topic in DB2 Magazine.  It might help to answer some of your questions.  Here's a link to the article:

http://www.db2mag.com/db_area/archives/2001/q1/db2dba.shtml

Robert

Khanal Robby (DTI) wrote:
DB2 Experts -
 
I am new to DB2 - transitioning from Adabas - which is non-SQL and non-relational DB. 
 
Can you please explain briefly what a DB2 bind and a DB2 Plan/Packages are?  I am having hard time grasping this concept. 
 
Any info on this topic will be appreciated.
 
Thanks,
Robby Khanal
 
--
Robert Catterall
President
Catterall Consulting
770-263-5322

www.catterallconsulting.com


=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========



IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Robby Khanal

Re: DB2 Bind & Plan
(in response to Philip Sevetson)
Thanks everyone for some good info on plans & packages. One more
question:



If I write a COBOL program with SQL statements, how to execute the
program. Do I only execute the COBOL program, or do I execute both the
program & the plan? And does it make any difference if it's online, or
batch?



And is it the same for COBOL vs. Java vs. other languages?



How about if I only have just SQL statements (no other languages
involved in my program)?



Appreciate it.



Thanks,

Robby

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Robert Catterall
Sent: Thursday, November 22, 2007 9:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Bind & Plan



I wrote an article on this topic in DB2 Magazine. It might help to
answer some of your questions. Here's a link to the article:

http://www.db2mag.com/db_area/archives/2001/q1/db2dba.shtml

Robert


Khanal Robby (DTI) wrote:

DB2 Experts -



I am new to DB2 - transitioning from Adabas - which is non-SQL and
non-relational DB.



Can you please explain briefly what a DB2 bind and a DB2 Plan/Packages
are? I am having hard time grasping this concept.



Any info on this topic will be appreciated.



Thanks,

Robby Khanal



------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
>From that page select "Join or Leave the list". The IDUG DB2-L FAQ is
at http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



--
Robert Catterall
President
Catterall Consulting
770-263-5322

www.catterallconsulting.com

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Seibert

Re: DB2 Bind & Plan
(in response to Robby Khanal)
Hi Robby,

The means with which you invoke your program depends on the attach
interface you use.
These attachment interfaces are documented in the Appl. PGM & SQL guide
and Admin guide, I believe.

The common ones are

TSO
Call Attach (CAF)
RRS attach (RRSAF)
CICS and IMS attachments.

Stored procedures are another matter.

CAF and RRSAF DB2 programs are invoked directly --
//Step1 EXEC PGM=pgmname

TSO attachment programs are invoked under foreground TSO or batch TSO.

JCL for a batch TSO DB2 program looks something like

//Step1 EXEC PGM=IKJEFT01 (or one of its relatives)
...
//SYSTSIN DD *
DSN SYSTEM(ssid)
RUN PROGRAM(pgmname)
END
//*

This is a complete oversimplification of the answer to your question.
You can probably get JCL from your DB2 folks for compiling, binding,
linkediting, and executing your program.

Good luck.

Dave




The contents of this e-mail are intended for the named addressee only.
It contains information that may be confidential. Unless you are the
named addressee or an authorized designee, you may not copy or use it,
or disclose it to anyone else. If you received it in error please notify
us immediately and then destroy it.


From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Khanal Robby (DTI)
Sent: Tuesday, November 27, 2007 8:44 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Bind & Plan



Thanks everyone for some good info on plans & packages. One more
question:



If I write a COBOL program with SQL statements, how to execute the
program. Do I only execute the COBOL program, or do I execute both the
program & the plan? And does it make any difference if it's online, or
batch?



And is it the same for COBOL vs. Java vs. other languages?



How about if I only have just SQL statements (no other languages
involved in my program)?



Appreciate it.



Thanks,

Robby

_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Robert Catterall
Sent: Thursday, November 22, 2007 9:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 Bind & Plan



I wrote an article on this topic in DB2 Magazine. It might help to
answer some of your questions. Here's a link to the article:

http://www.db2mag.com/db_area/archives/2001/q1/db2dba.shtml

Robert


Khanal Robby (DTI) wrote:

DB2 Experts -



I am new to DB2 - transitioning from Adabas - which is non-SQL and
non-relational DB.



Can you please explain briefly what a DB2 bind and a DB2 Plan/Packages
are? I am having hard time grasping this concept.



Any info on this topic will be appreciated.



Thanks,

Robby Khanal



------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
>From that page select "Join or Leave the list". The IDUG DB2-L FAQ is
at http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm



--
Robert Catterall
President
Catterall Consulting
770-263-5322

www.catterallconsulting.com

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm IMPORTANT NOTICE:


IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org


------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: DB2 Bind & Plan
(in response to David Seibert)
This will be a bit long and rambling. Hopefully it will cover your question somewhere.

Let's start with COBOL (Assembler, PL/I, C (non CLI/ODBC) or Fortran). The process is:
1) pre-compile the source. The two outputs are
a) a source DBRM - which contains the SQL statements; and
b) a pure COBOL program - which contains calls to DSNHLI. Each call has a parameter list
that identifies an SQL statement (to tie into the DBRM) and host variables (so DB2 knows
where to get and put data)
2) compile the pure COBOL program (ie 1b). The output is an object program. (Don't worry
about what this actually means)
3) link the object program. The output is an executable load module. We'll see later that
what is included in this load module is dependant on how we'll invoke the program and DB2.
4) bind the source DBRM (ie 1a) into a package. This determines how the SQL will be
processed
5) bind a plan that includes the package, either explicitly by the package's full name or
implicitly by specifying the collection that the package is in. Actually this is often done once
before the source is processed, because the package is not actually included - only the
package's name is included.

We are now ready to execute the program. To do so we need to get following items active:
- a connection to DB2
- the plan
- the executable program

There are 5 different ways of getting them active:
6) Use TSO-attach.
a) To do this we need to ensure that the calls to DSNHLI are resolved in a module named
DSNELI. The easiest way of doing this is to link DSNELI into the executable load module.
b) from a TSO environment (either on-line TSO or JCL using // EXEC PGM=IKJEFT01)
b-i) execute the command
DSN SYSTEM(<db2-ssid>)
This creates the connection to the specified DB2
b-ii) with the DSN command environment, execute the sub-command
RUN PROGRAM(<load module member name>) PLAN(<item-5-above>)
This identifies the plan, loads and the executes the program.

When the program issues its CALL DSNHLI, the code in DSNELI executes. This uses the
connection the DB2 to invoke internal DB2 code. The internal code uses the parameter
information to find the package, which in turn tells DB2 how to process the SQL.

7) Use IMS-attach. This can only be used in an IMS environment.
a) DFSLI000 needs to be linked into the executable load mode - it supplies the DSNHLI
code.
b) A similar process to TSO-attach is used to create the connection to DB2 and specify the
plan to be used for a program - similar at a high level, the detail is very different. Normally an
IMS system will connect to a single DB2 subsystem. But being clever it is possible to get
different programs to use connections to different DB2 sub-systems.

8) Use CICS-attach. This can only be used in a CICS environment
a) DSNCLI needs to be linked into the load module
b) When CICS starts, it creates a set of connections to a DB2.
c) when a program starts, a plan is chosen when it makes its first call to DSNHLI (there are
rules the CICS administrators set to decide the plan.)

9) CALL-attach. This can be used from a TSO environment (online or JCL), a JCL or a batch
IMS environment. (// EXEC PGM=MYPROG or, in TSO, CALL *(MYPROG) etc)
a) DSNALI needs to be linked into the load module
b) the program is invoked directly.
b-i) the program calls DSNALI to connect to a DB2 subsystem
b-ii) the program calls DSNALI to open the plan
b-iii) the code now calls DSNHLI to execute SQL
Unlike the prior attach methods, with CALL-attach, the program can close one plan and open
another, or disconnect from one DB2 and connect to another.

Various people have written front-ends which perform b-i and b-ii and then call the user
program. In this case your program doesn't need to the know how it is attached - the only
difference from TSO-attach is the use of DSNALI.

10) RRS-attach. This is much like like CALL-attach except DSNRLI is linked and called.

Within the broad outline above there are many variations
- if you structure the STEPLIB libraries and manipulate the external module aliases, you can
dynamically load the language interface (LI - DSNx*LI*, DFS*LI*000) module
- if you are writing a stored procedure or UDF module, you link DSNRLI but don't call DSNRLI
(link DSNALI if still using non-WLM address spaces) - just the DSNHLI calls to execute SQL
- the pre-compile can be executed with the compile step by using the SQL co-processor

Now, if you have called subroutines, then the subroutine needs to use the same attach
method as the caller (since the caller will have created the DB2 environment) and, especially,
the same language interface module. But, with Call- and RRS-attachs, it does not need to do
anything about setting up a connection to DB2.

C with ODBC/CLI uses calls to do specify SQL and host variables. The plan used is fixed, so
no pre-compile, DBRM or bind.

Java with JDBC also uses methods to SQL and host variables. The plan used is fixed, so no
pre-compile, DBRM or bind.

Java with SQLJ can either generate methods like JDBC, or it can generate DBRMs with
resulting packages.

REXX is interpreted, uses a fixed plan

HTH although I'm sure I've made mistakes/left stuff out
James Campbell


On 27 Nov 2007 at 8:44, Khanal Robby (DTI) wrote:

>
> Thanks everyone for some good info on plans & packages. One more question:
>
> If I write a COBOL program with SQL statements, how to execute the program. Do I only execute
> the COBOL program, or do I execute both the program & the plan? And does it make any
> difference if it´s online, or batch?
>
> And is it the same for COBOL vs. Java vs. other languages?
>
> How about if I only have just SQL statements (no other languages involved in my program)?
>
> Appreciate it.
>
> Thanks,
> Robby
>
>
<rest snipped>

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm