Bind Package from MVS with MVS and AIX SQL in same pgm

Doug Kestner

Bind Package from MVS with MVS and AIX SQL in same pgm
Hello all, we have been experiencing problems with BIND PACKAGE commands
from MVS to a remote AIX server when the cobol program has both MVS and AIX
SQL within the program. More detail below.

To start with, we have Cobol programs that have SQL calls to OS/390 tables
AND UDB tables on AIX and are having trouble getting the PACKAGE bound,
locally or remotely. We have done tests where we have a cobol program with
only SQL calls referencing the UDB tables on AIX. We are able to BIND the
remote package successfully and run the program, returning rows from AIX
back to the MVS originated program.

Here is more background. We have a PLAN named DHSTDRDA. This plan
contains all the batch packages that will execute either locally, remotely
or both. We have a local location of DB2THLOC and a remote location
defined (on MVS - locations table) as HSDBCZ00. This is the AIX node. We
use a commonly named Collection of DHSTEST_BTCH. The program name, dbrm
name and package name are all DKMVSAIX.

Okay, we Bind the Plan including the local packages and remote packages
with the following PKLIST command

PKLIST(HSDBCZ00.DHSTEST_BTCH.*, *.DHSTEST_BTCH.*) -this should get the
remote packages and local packages that we later bind.

Next we precompile, compile, linkedit and then attempt to Bind a PACKAGE.
Things work fine until the BIND PACKAGE. It appears that we can only BIND
PACKAGE to either "the local location" or "the remote location" at a time.
When we try to BIND PACKAGE to only the local location as
(DB2THLOC.DHSTEST_BTCH), we receive -204 errors related to the SQL calls
that reference the AIX tables in the program. Likewise, when we try to
BIND PACKAGE to only the remote location as (HSDBCZ00.DHSTEST_BTCH), we
receive -204 errors related to the SQL calls that reference the MVS tables
in the program. We also receive a -917 saying the remote bind failed.

Our group has read numerous redbooks from IBM that indicate that we should
be able to perform multiple RDBMS calls from the same program using the
concept of a Distributed Unit of Work (DUW) implementing a one-phase or two-
phase commit technique. We actually are only selecting data from AIX and
don't currently have a need to update so the two-phase commit isn't an
issue now. The part the redbooks don't talk about is BIND a PACKAGE that
contains SQL calls that reference local and remote RDBMSs. we can't seem
to get past the -204 and get a clean BIND PACKAGE.

we have successfully created 2 programs, 1 with only MVS SQL and 1 with
only AIX SQL. We created separate PACKAGES for each program and included
them into the same PLAN. The MVS program made a Cobol CALL to initiate the
AIX program and it all worked. This isn't the desired approach that we
would like.

We are treating the MVS OS/390 as the Application Requestor (AR) and the
AIX box as the Application Server (AS) using TCP/IP. We have the necessary
communication data base (CDB) tables setup in MVS. We are able to connect
our TSO/Spufi to AIX and do remote calls, we are able to remote bind an AIX
program from MVS and get it to work. We just can't get the program that
contains both MVS and AIX SQL to get past the BIND PACKAGE.

<<end>>

Any help will be appreciated.




Doug Kestner - DataBase Administration
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - -
Illinois Department of Human Services
Information Systems, Technical Services, System Support
100 South Grand Avenue East, Springfield, IL 62762 USA
(217) 558-2087 - FAX: (217) 782-6089 - [login to unmask email]
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - -



Dave Nance

Re: Bind Package from MVS with MVS and AIX SQL in same pgm
(in response to Doug Kestner)
Doug,
There may be a better method, but we have always done a bind on both systems for the package using sqlerror(continue). You will get errors for the objects that do not exist on that DB instance, but that's as it should be.

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 12/19/00 09:32AM >>>
Hello all, we have been experiencing problems with BIND PACKAGE commands
from MVS to a remote AIX server when the cobol program has both MVS and AIX
SQL within the program. More detail below.

To start with, we have Cobol programs that have SQL calls to OS/390 tables
AND UDB tables on AIX and are having trouble getting the PACKAGE bound,
locally or remotely. We have done tests where we have a cobol program with
only SQL calls referencing the UDB tables on AIX. We are able to BIND the
remote package successfully and run the program, returning rows from AIX
back to the MVS originated program.

Here is more background. We have a PLAN named DHSTDRDA. This plan
contains all the batch packages that will execute either locally, remotely
or both. We have a local location of DB2THLOC and a remote location
defined (on MVS - locations table) as HSDBCZ00. This is the AIX node. We
use a commonly named Collection of DHSTEST_BTCH. The program name, dbrm
name and package name are all DKMVSAIX.

Okay, we Bind the Plan including the local packages and remote packages
with the following PKLIST command

PKLIST(HSDBCZ00.DHSTEST_BTCH.*, *.DHSTEST_BTCH.*) -this should get the
remote packages and local packages that we later bind.

Next we precompile, compile, linkedit and then attempt to Bind a PACKAGE.
Things work fine until the BIND PACKAGE. It appears that we can only BIND
PACKAGE to either "the local location" or "the remote location" at a time.
When we try to BIND PACKAGE to only the local location as
(DB2THLOC.DHSTEST_BTCH), we receive -204 errors related to the SQL calls
that reference the AIX tables in the program. Likewise, when we try to
BIND PACKAGE to only the remote location as (HSDBCZ00.DHSTEST_BTCH), we
receive -204 errors related to the SQL calls that reference the MVS tables
in the program. We also receive a -917 saying the remote bind failed.

Our group has read numerous redbooks from IBM that indicate that we should
be able to perform multiple RDBMS calls from the same program using the
concept of a Distributed Unit of Work (DUW) implementing a one-phase or two-
phase commit technique. We actually are only selecting data from AIX and
don't currently have a need to update so the two-phase commit isn't an
issue now. The part the redbooks don't talk about is BIND a PACKAGE that
contains SQL calls that reference local and remote RDBMSs. we can't seem
to get past the -204 and get a clean BIND PACKAGE.

we have successfully created 2 programs, 1 with only MVS SQL and 1 with
only AIX SQL. We created separate PACKAGES for each program and included
them into the same PLAN. The MVS program made a Cobol CALL to initiate the
AIX program and it all worked. This isn't the desired approach that we
would like.

We are treating the MVS OS/390 as the Application Requestor (AR) and the
AIX box as the Application Server (AS) using TCP/IP. We have the necessary
communication data base (CDB) tables setup in MVS. We are able to connect
our TSO/Spufi to AIX and do remote calls, we are able to remote bind an AIX
program from MVS and get it to work. We just can't get the program that
contains both MVS and AIX SQL to get past the BIND PACKAGE.

<<end>>

Any help will be appreciated.




Doug Kestner - DataBase Administration
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - -
Illinois Department of Human Services
Information Systems, Technical Services, System Support
100 South Grand Avenue East, Springfield, IL 62762 USA
(217) 558-2087 - FAX: (217) 782-6089 - [login to unmask email]
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - -






Vikram Khatri

Re: Bind Package from MVS with MVS and AIX SQL in same pgm
(in response to Dave Nance)
I do not know much about DB2 on OS/390 but we did similar POC work on UDB on
Sun and NT and we had used Type 2 connection for distributed computing
(bind).

Vikram

-----Original Message-----
From: David Nance [mailto:[login to unmask email]
Sent: Tuesday, December 19, 2000 10:16 AM
To: [login to unmask email]
Subject: Re: Bind Package from MVS with MVS and AIX SQL in same pgm


Doug,
There may be a better method, but we have always done a bind on both
systems for the package using sqlerror(continue). You will get errors for
the objects that do not exist on that DB instance, but that's as it should
be.

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 12/19/00 09:32AM >>>
Hello all, we have been experiencing problems with BIND PACKAGE commands
from MVS to a remote AIX server when the cobol program has both MVS and AIX
SQL within the program. More detail below.

To start with, we have Cobol programs that have SQL calls to OS/390 tables
AND UDB tables on AIX and are having trouble getting the PACKAGE bound,
locally or remotely. We have done tests where we have a cobol program with
only SQL calls referencing the UDB tables on AIX. We are able to BIND the
remote package successfully and run the program, returning rows from AIX
back to the MVS originated program.

Here is more background. We have a PLAN named DHSTDRDA. This plan
contains all the batch packages that will execute either locally, remotely
or both. We have a local location of DB2THLOC and a remote location
defined (on MVS - locations table) as HSDBCZ00. This is the AIX node. We
use a commonly named Collection of DHSTEST_BTCH. The program name, dbrm
name and package name are all DKMVSAIX.

Okay, we Bind the Plan including the local packages and remote packages
with the following PKLIST command

PKLIST(HSDBCZ00.DHSTEST_BTCH.*, *.DHSTEST_BTCH.*) -this should get the
remote packages and local packages that we later bind.

Next we precompile, compile, linkedit and then attempt to Bind a PACKAGE.
Things work fine until the BIND PACKAGE. It appears that we can only BIND
PACKAGE to either "the local location" or "the remote location" at a time.
When we try to BIND PACKAGE to only the local location as
(DB2THLOC.DHSTEST_BTCH), we receive -204 errors related to the SQL calls
that reference the AIX tables in the program. Likewise, when we try to
BIND PACKAGE to only the remote location as (HSDBCZ00.DHSTEST_BTCH), we
receive -204 errors related to the SQL calls that reference the MVS tables
in the program. We also receive a -917 saying the remote bind failed.

Our group has read numerous redbooks from IBM that indicate that we should
be able to perform multiple RDBMS calls from the same program using the
concept of a Distributed Unit of Work (DUW) implementing a one-phase or two-
phase commit technique. We actually are only selecting data from AIX and
don't currently have a need to update so the two-phase commit isn't an
issue now. The part the redbooks don't talk about is BIND a PACKAGE that
contains SQL calls that reference local and remote RDBMSs. we can't seem
to get past the -204 and get a clean BIND PACKAGE.

we have successfully created 2 programs, 1 with only MVS SQL and 1 with
only AIX SQL. We created separate PACKAGES for each program and included
them into the same PLAN. The MVS program made a Cobol CALL to initiate the
AIX program and it all worked. This isn't the desired approach that we
would like.

We are treating the MVS OS/390 as the Application Requestor (AR) and the
AIX box as the Application Server (AS) using TCP/IP. We have the necessary
communication data base (CDB) tables setup in MVS. We are able to connect
our TSO/Spufi to AIX and do remote calls, we are able to remote bind an AIX
program from MVS and get it to work. We just can't get the program that
contains both MVS and AIX SQL to get past the BIND PACKAGE.

<<end>>

Any help will be appreciated.




Doug Kestner - DataBase Administration
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - -
Illinois Department of Human Services
Information Systems, Technical Services, System Support
100 South Grand Avenue East, Springfield, IL 62762 USA
(217) 558-2087 - FAX: (217) 782-6089 - [login to unmask email]
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - -








http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]



James Campbell

Re: Bind Package from MVS with MVS and AIX SQL in same pg m
(in response to Vikram Khatri)
Another, off the top, possibility is to create suitable objects so that the
binds do work. You don't have to populate them or have indexes - just the
objects.

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442
[login to unmask email]


-----Original Message-----
From: David Nance [mailto:[login to unmask email]
Sent: Wednesday, December 20, 2000 2:16 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Bind Package from MVS with MVS and AIX SQL in same
pgm


Doug,
There may be a better method, but we have always done a bind on both
systems for the package using sqlerror(continue). You will get errors for
the objects that do not exist on that DB instance, but that's as it should
be.

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 12/19/00 09:32AM >>>
Hello all, we have been experiencing problems with BIND PACKAGE commands
from MVS to a remote AIX server when the cobol program has both MVS and AIX
SQL within the program. More detail below.

<snip>


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************



Alan Smith

Re: Bind Package from MVS with MVS and AIX SQL in same pgm
(in response to James Campbell)
We get round this by using VALIDATE(RUN) in the bind. You just get warnings
then.

Alan Smith


> ------------------------------
>
> Date: Tue, 19 Dec 2000 10:16:00 -0500
> From: David Nance <[login to unmask email]>
> Subject: Re: Bind Package from MVS with MVS and AIX SQL in same pgm
>
> Doug,
> There may be a better method, but we have always done a bind on both =
> systems for the package using sqlerror(continue). You will get errors for =
> the objects that do not exist on that DB instance, but that's as it should =
> be.
>
> Dave Nance
> First Health Services, Corp.
> (804)527-6841