Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Retrieve data from second database using stored procedure
- Thread starter PawelTru
- Start date
In an OS/390 platform, you can use DDF (Distributed Data Facility.) This is an OS/390 address space.
This requires binding remote packages locally in the BIND PLAN PKLIST parm by specifying the remote node, collection name, package name or wildcard.
You CONNECT in you application code to the remote subsystem. (Remote can mean an LPAR on a different machine, a different LPAR on the same machine, or the same LPAR. DDF doesn't care.)
Like a toggle, you CONNECT to the remote system, DML is executed in that databases. Then RELEASE the connection. By virue of the release, you are now connected locally. Insert the rows locally or update or whatever. Connect again if more rows are needed, for singleton selects, or until the end of the cursor.
In short, Package A (a stored procedure) is compiled and bound in two different systems, to two databases. But both the local and remote Package As are bound within a local plan. It is called locally but invoked both locally and remotely, line by line, with control passing back and forth.
You can interogate the SQLCA (SQLCODE) while connected locally or remotely.
local: CONNECT
remote: OPEN CURSOR
remote: FETCH
remote: RELEASE
local: INSERT
local: CONNECT
remote: FETCH
remote: RELEASE
local: INSERT
local: CONNECT ...
continue until END OF CURSOR (+100)
If the remote packages are using cursors, then use FOR FETCH ONLY or FOR READ ONLY (synonymous terms) to use a 32K VTAM buffer transport mode. When used in conjunction with sequential prefetch, this will be the most efficient transport of data. Also, you should specify the WITH HOLD option, so the cursor doesn't close after a commit.
This requires binding remote packages locally in the BIND PLAN PKLIST parm by specifying the remote node, collection name, package name or wildcard.
You CONNECT in you application code to the remote subsystem. (Remote can mean an LPAR on a different machine, a different LPAR on the same machine, or the same LPAR. DDF doesn't care.)
Like a toggle, you CONNECT to the remote system, DML is executed in that databases. Then RELEASE the connection. By virue of the release, you are now connected locally. Insert the rows locally or update or whatever. Connect again if more rows are needed, for singleton selects, or until the end of the cursor.
In short, Package A (a stored procedure) is compiled and bound in two different systems, to two databases. But both the local and remote Package As are bound within a local plan. It is called locally but invoked both locally and remotely, line by line, with control passing back and forth.
You can interogate the SQLCA (SQLCODE) while connected locally or remotely.
local: CONNECT
remote: OPEN CURSOR
remote: FETCH
remote: RELEASE
local: INSERT
local: CONNECT
remote: FETCH
remote: RELEASE
local: INSERT
local: CONNECT ...
continue until END OF CURSOR (+100)
If the remote packages are using cursors, then use FOR FETCH ONLY or FOR READ ONLY (synonymous terms) to use a 32K VTAM buffer transport mode. When used in conjunction with sequential prefetch, this will be the most efficient transport of data. Also, you should specify the WITH HOLD option, so the cursor doesn't close after a commit.
- Thread starter
- #3
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question