This will be a bit long and rambling. Hopefully it will cover your
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
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
b-i) execute the command
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>)
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
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
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)
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)
- 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
REXX is interpreted, uses a fixed plan
HTH although I'm sure I've made mistakes/left stuff out
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.
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.
List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm