Accessing two different db2 subsystems

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.

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

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.

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.

-obr-

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 th
e
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.

"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

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

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 (f
or
> 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 th
em
>

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

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 y
ou
:>> 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 tel
l 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.

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