DB2 Replicator

Mike Fatula

DB2 Replicator
Hi:

I working with the Replicator to see if I'm able to perform the tasks our
company run nightly using batch jobs to build our warehouses.
I have the Replicator working in a test environment.
I'm able to capture changes made on one database and have the changes
reflected to another database I'll call the warehouse.

The next thing I want to test is the update of one table in the warehouse
from a combination of two or more tables from the main source database.

We currently use views on our production databases now to perform these
mergers.

Can anyone tell me how to set this up using the Replicator.
Do I register both source tables? How do I perform the join logic I'm able
to perform with a View. How do I select only the columns I want?

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Fatula

DB2 Replicator
(in response to Mike Fatula)
Hi:

I working with the Replicator to see if I'm able to perform the tasks our
company run nightly using batch jobs to build our warehouses.
I have the Replicator working in a test environment.
I'm able to capture changes made on one database and have the changes
reflected to another database I'll call the warehouse.

The next thing I want to test is the update of one table in the warehouse
from a combination of two or more tables from the main source database.

We currently use views on our production databases now to perform these
mergers.

Can anyone tell me how to set this up using the Replicator.
Do I register both source tables? How do I perform the join logic I'm able
to perform with a View. How do I select only the columns I want?

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


*************************************************************************************
The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.
*************************************************************************************

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

S. Kumar

DB2
(in response to Mike Fatula)
HAI

can any one know about any function in db2 on z/OS to identify nth value?

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sorensen Henrik (KAFC 21)

Re: DB2
(in response to S. Kumar)
On z/OS, DB2 V7

I wrote a UDF, that returns an increasing number each time it gets invoked.

That way, you can write queries like this:

SELECT ID,N#
FROM (
SELECT ID,UDF.COUNTER(1) AS N#
FROM mytable
GROUP BY ID
) AS MYTB
WHERE MOD(N#,10000)=0

ID N#
--- ----------
S0A 10000
S0B 20000
S0C 30000
S0D 40000
S0E 50000
S0F 60000
S0G 70000
S0H 80000
S0I 90000
S0J 100000

Don't expect this to be fast for large tables. Obviously DB2 has little choice but to scan the whole table


Henrik




-----Original Message-----
From: S. Kumar [mailto:[login to unmask email]
Sent: Friday, December 12, 2003 9:35 AM
To: [login to unmask email]
Subject: DB2


HAI

can any one know about any function in db2 on z/OS to identify nth value?

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Tom Duerbusch

Re: DB2 Replicator
(in response to Sorensen Henrik (KAFC 21))
I can't talk about your specific question as it would require a lot more
information. But....

A source table is a table that you want to capture changes from. The
manual recommends that, for any particular table that you want to
capture, that you capture all data, both before and after image. When
the capture program reads the DB2 log it will populate the CD tables
(the Changed Data tables) with this information. The CD tables exist on
the same database as the source tables are located.

Then comes a separate step called APPLY. The apply function can be run
on the same system as where the source tables are, or on the same cpu as
the replicated tables are, or on an entirely separate cpu. Fun huh?

DB2 Control Center is what is used to generate all the control
statements needed to do what...where..etc. I use DB2/UDB to do generate
the control statements. Once generated, DB2 Control Center steps out of
the picture, and doesn't have to be running unless you want to make
changes to Data Replication.

When you generate the control statements for the APPLY function, you
say what columns you want copy from the CD tables, what table(s) you
want to copy this data to, should the data in the CD tables be deleted
after you replicated it, or should it stay (perhaps so the same data can
be used to replication to other tables), etc. You also have the option
of putting in SQL statements to "do what you need to, to the data to
make it suitable for the replicated tables".

So, if you have multiple warehouses, you can select only the right data
for that warehouse to be replicated to that warehouse. The data not
selected may optionally, be put in differenet tables (tables that
contain data for items in this warehouse, vs tables that contain data
for items in remote warehouse(s)).

It may be, that the two tables you are using, should be replicated to
two tables on the warehouse side, and then use a view there, just like
you have on the host.

Tom Duerbusch
THD Consulting

>>> [login to unmask email] 12/11/03 10:39AM >>>
Hi:

I working with the Replicator to see if I'm able to perform the tasks
our
company run nightly using batch jobs to build our warehouses.
I have the Replicator working in a test environment.
I'm able to capture changes made on one database and have the changes
reflected to another database I'll call the warehouse.

The next thing I want to test is the update of one table in the
warehouse
from a combination of two or more tables from the main source
database.

We currently use views on our production databases now to perform
these
mergers.

Can anyone tell me how to set this up using the Replicator.
Do I register both source tables? How do I perform the join logic I'm
able
to perform with a View. How do I select only the columns I want?

---------------------------------------------------------------------------------
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". If you will be out of the office,
send the SET DB2-L NO MAIL command to [login to unmask email] The
IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm