Cobol - Accessing two different db2 subsystems

Author

Accessing two different db2 subsystems

cobolman

2006-09-14, 3:55 am

I have the need to create a cobol program that compares a specified
table in two different DB2 subsystems. This is to validate that the
table is identical or not (including field values) for the two
subsystems.

Of course, I could solve this by running a program in both environments
creating an output file, and then compare the two files. But if it's
possible it would be preferred that this could be done using only one
cobol program (running once).

Could anyone help me with this, or maybe direct me towards somewhere on
the internet where I can find information on how this is done?

Currently the version of DB2 i v7.

2006-09-14, 3:55 am

In article <1158223257.217052.19020@i42g2000cwa.googlegroups.com>,
cobolman <olafbrungot@hotmail.com> wrote:
>I have the need to create a cobol program that compares a specified
>table in two different DB2 subsystems.

I was once assigned a task to write a COBOL program that would generate
individual INSERTs into a couple of tables... nothing too large, the
biggest was, I think, 26 million rows... and I said 'Gosh, don't you think
it would be better if I generated files that could be loaded directly into
the tables by utilities?'

The response was something like 'When we want your opinion we'll give it
to you'... so I wrote code and procs and ran them... and ran them... and
when the third trial was cancelled by Ops, like the first two, after
running for some thirty-odd hours someone said 'Oh, all *right*, if you're
going to make so much noise about it then go ahead and run your silly
utilities...'

.... and when the utilities finished the job in under eight hours the
response was 'See? We *told* you to use the system utilities, why were
you trying to milk us with this COBOL-program access stuff?'

So... if the tables are anywhere close to similar what is there to prevent
you, likewise, from using utilities to accomplish what you present as a
simple file-compare task?

DD

cobolman

2006-09-14, 3:55 am

docdwarf@panix.com skrev:
>
> So... if the tables are anywhere close to similar what is there to prevent
> you, likewise, from using utilities to accomplish what you present as a
> simple file-compare task?
>
> DD

Were the tables in different subsystems?

The task at hand here concerns 3-4 small tables containing system
parameters. I guess the largest of them contains not more than 2500
rows. I need to make sure the system parameters are identical at a
given time in both subsystems (one for production, one for systemtest).

So this is not a question of inserting values from one table to another.

cobolman

2006-09-14, 3:55 am

Binyamin Dissen

2006-09-14, 7:55 am

On 14 Sep 2006 01:40:58 -0700 "cobolman" <olafbrungot@hotmail.com> wrote:

:>I have the need to create a cobol program that compares a specified
:>table in two different DB2 subsystems. This is to validate that the
:>table is identical or not (including field values) for the two
:>subsystems.

:>Of course, I could solve this by running a program in both environments
:>creating an output file, and then compare the two files. But if it's
:>possible it would be preferred that this could be done using only one
:>cobol program (running once).

Why?

What is the business reason for not using an output file?

:>Could anyone help me with this, or maybe direct me towards somewhere on
:>the internet where I can find information on how this is done?

If you want to concurrently access the tables in different systems and you
don't have three-part naming you can create a maintask and two subtasks (for
simplicity), each connected to one of the DB2's. The main task would tell the
subtasks which SQL to run, and it would receive the results and compare them

--
Binyamin Dissen <bdissen@dissensoftware.com>
http://www.dissensoftware.com

Director, Dissen Software, Bar & Grill - Israel

Should you use the mailblocks package and expect a response from me,
you should preauthorize the dissensoftware.com domain.

I very rarely bother responding to challenge/response systems,
especially those from irresponsible companies.

Michael Mattias

2006-09-14, 7:55 am

"cobolman" <olafbrungot@hotmail.com> wrote in message
news:1158223257.217052.19020@i42g2000cwa.googlegroups.com...
> I have the need to create a cobol program that compares a specified
> table in two different DB2 subsystems. This is to validate that the
> table is identical or not (including field values) for the two
> subsystems.
>
> Of course, I could solve this by running a program in both environments
> creating an output file, and then compare the two files. But if it's
> possible it would be preferred that this could be done using only one
> cobol program (running once).

???

You can't define which subsytem to use when establishing the connection? I
know I did this once for a major insurance company, connecting to nine
different DB/2 subsystems . This was on IBM mainframe (well actually
mainframeS (plural), as the nine subsytems were actually resident on (hosted
by? installed upon?) three different computers). Then again, they had a
heck of a DB/2 setup which made it pretty easy for the applications
programmers.

And I know I've done this on Windows' PC using ODBC, connecting to two data
sources at one time. I'm not totally sure how "ODBC Datasource" compares to
"DB/2 Subsystem" but hey, it sounds pretty close, and best of all it worked!

MCM

cobolman

2006-09-14, 7:55 am

Michael Mattias skrev:
> You can't define which subsytem to use when establishing the connection?

I thought the bind would determine which subsystem (creator) the sql
would be run towards?? I am not sure of how to create a 'dynamic' bind
in my cobol program

cobolman

2006-09-14, 7:55 am

Binyamin Dissen skrev:
> Why?
>
> What is the business reason for not using an output file?

I guess there's really no reason not go for the output file solutions.
I could write a program which selects from the given tables, and run
this twice in the same job (jcl) but towards two different db2
subsystems. And then compare the result of these two files.

It's just that if it's possible I'd like to give it a try :o) Let's
call it a learning project!

> If you want to concurrently access the tables in different systems and you
> don't have three-part naming you can create a maintask and two subtasks (for
> simplicity), each connected to one of the DB2's. The main task would tell the
> subtasks which SQL to run, and it would receive the results and compare them
>

Not quite sure how to do this, but it seems like the solution I'm
looking for. Do you have any examples?

Binyamin Dissen

2006-09-14, 7:55 am

On 14 Sep 2006 05:58:07 -0700 "cobolman" <olafbrungot@hotmail.com> wrote:

:>Binyamin Dissen skrev:
:>> Why?

:>> What is the business reason for not using an output file?

:>I guess there's really no reason not go for the output file solutions.
:>I could write a program which selects from the given tables, and run
:>this twice in the same job (jcl) but towards two different db2
:>subsystems. And then compare the result of these two files.

Two DSNTIAL steps and then a line compare.

No muss, no fuss.

:>It's just that if it's possible I'd like to give it a try :o) Let's
:>call it a learning project!

:>> If you want to concurrently access the tables in different systems and you
:>> don't have three-part naming you can create a maintask and two subtasks (for
:>> simplicity), each connected to one of the DB2's. The main task would tell the
:>> subtasks which SQL to run, and it would receive the results and compare them

:>Not quite sure how to do this, but it seems like the solution I'm
:>looking for. Do you have any examples?

Talk to your DBA.

--
Binyamin Dissen <bdissen@dissensoftware.com>
http://www.dissensoftware.com

Director, Dissen Software, Bar & Grill - Israel

Should you use the mailblocks package and expect a response from me,
you should preauthorize the dissensoftware.com domain.

I very rarely bother responding to challenge/response systems,
especially those from irresponsible companies.

2006-09-14, 6:55 pm

In article <1158227686.438674.114340@m73g2000cwd.googlegroups.com>,
cobolman <olafbrungot@hotmail.com> wrote:
>docdwarf@panix.com skrev:
>
>Were the tables in different subsystems?

This can be addressed by invocation parameters, eg:

//UNLOAD1 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSIN DD *
DSN SYSTEM(SYS1)
RUN PROGRAM(DSNTIAUL) PLAN(planname) PARMS('SQL') -
LIB('system.load.lib')

.... and for the next step ...

//UNLOAD2 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSIN DD *
DSN SYSTEM(sys2)
RUN PROGRAM(DSNTIAUL) PLAN(planname) PARMS('SQL') -
LIB('system.load.lib')

>The task at hand here concerns 3-4 small tables containing system
>parameters. I guess the largest of them contains not more than 2500
>rows. I need to make sure the system parameters are identical at a
>given time in both subsystems (one for production, one for systemtest).

That appears to be addressed in the JCL given above.

>
>
>So this is not a question of inserting values from one table to another.

A good thing I did not state that it was such, wouldn't you say?

DD

Sergey Kashyrin

2006-09-14, 6:55 pm

"cobolman" <olafbrungot@hotmail.com> wrote
> Michael Mattias skrev:
>
> I thought the bind would determine which subsystem (creator) the sql
> would be run towards?? I am not sure of how to create a 'dynamic' bind
> in my cobol program

From IBM docs:

Example: Execute SQL statements at TOROLAB1, execute SQL statements at
TOROLAB2, and then execute more SQL statements at TOROLAB1.
EXEC SQL CONNECT TO TOROLAB1;
-- execute statements referencing objects at TOROLAB1
EXEC SQL CONNECT TO TOROLAB2;
-- execute statements referencing objects at TOROLAB2
EXEC SQL SET CONNECTION TOROLAB1;
-- execute statements referencing objects at TOROLAB1

The first CONNECT statement creates the TOROLAB1 connection, the second
CONNECT statement places it in the dormant state, and the SET CONNECTION
statement returns it to the current state.

Richard

2006-09-14, 6:55 pm

cobolman wrote:

> I guess there's really no reason not go for the output file solutions.
> I could write a program which selects from the given tables, and run
> this twice in the same job (jcl) but towards two different db2
> subsystems. And then compare the result of these two files.

If I were to do then it may be similar to:

#!/bin/sh

psql -U user -h host1 database <<**** >data1
select * from controls order by control_code;
****
psql -U user -h host2 database <<**** >data2
select * from controls order by control_code;
****
diff data1 data2

Robert Jones

2006-09-15, 6:55 pm

Bottom posting

Michael Mattias wrote:
> "cobolman" <olafbrungot@hotmail.com> wrote in message
> news:1158223257.217052.19020@i42g2000cwa.googlegroups.com...
>
> ???
>
> You can't define which subsytem to use when establishing the connection? I
> know I did this once for a major insurance company, connecting to nine
> different DB/2 subsystems . This was on IBM mainframe (well actually
> mainframeS (plural), as the nine subsytems were actually resident on (hosted
> by? installed upon?) three different computers). Then again, they had a
> heck of a DB/2 setup which made it pretty easy for the applications
> programmers.
>
> And I know I've done this on Windows' PC using ODBC, connecting to two data
> sources at one time. I'm not totally sure how "ODBC Datasource" compares to
> "DB/2 Subsystem" but hey, it sounds pretty close, and best of all it worked!
>
> MCM

to add my tuppence worth to Michael's response

I believe it can be done via a PC using what is called federation.
This has been discussed on comp.databases.ibm-db2, you could also try
asking via the listserve email group for DB2-L ( which can be
subscribed to via see below) and on bit.listserve.ibm-main.
************
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
DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG
conferences at http://conferences.idug.org/index.cfm
************

Robert

mvs20plusyears

2006-09-17, 8:59 am

quote:
Originally posted by cobolman
I have the need to create a cobol program that compares a specified
table in two different DB2 subsystems. This is to validate that the
table is identical or not (including field values) for the two
subsystems.

Of course, I could solve this by running a program in both environments
creating an output file, and then compare the two files. But if it's
possible it would be preferred that this could be done using only one
cobol program (running once).

Could anyone help me with this, or maybe direct me towards somewhere on
the internet where I can find information on how this is done?

Currently the version of DB2 i v7.

-------------------------------

The answer is the "Call Attachment Facility" documented in the Application Programming and SQL Guide, Chapter 29. Use hlq.SDSNSAMP(DSN8SCM) calls DSN8CC as examples.

As far as alternatives go, the suggestions to run more than one step will work. Once you withdraw the COBOL requirement, consider using REXX with the DB2 API. I have connected to two DB2 subsystems at the same time with REXX.

Sponsored Links