By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,812 Members | 875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,812 IT Pros & Developers. It's quick & easy.

Any DDL Information on the DB2 Logs?

P: n/a
RdR
Would anyone know if DDL changes such as creating a table, altering a table,
dropping the table, dropping a tablespace be in the DB2 logs? I am writing a
log capture program so that I can replicate changes on my source DB2 by
getting change data information , get this information, convert it to a SQL
command, send it to another DB2 instance and run it there, in effect
replicating the DDL change. I am already able to do this with DML.
Furthermore, I am also looking at replicating DDL and DML changes to other
non-DB2 databases.

JWK
Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Hi,
Would anyone know if DDL changes such as creating a table,
altering a table, dropping the table, dropping a tablespace be
in the DB2 logs? I am writing a log capture program so that I
can replicate changes on my source DB2 by getting change data
information , get this information, convert it to a SQL command,
send it to another DB2 instance and run it there, in effect
replicating the DDL change.


sounds a little bit like re-inventing the wheel to me.

Take a look at the documentation, "high availability desaster
recovery". Works fine for me in a 24/7 production environment (DB2
8.2 on Win32).

Regards,
Bernd
Nov 12 '05 #2

P: n/a
RdR wrote:
Would anyone know if DDL changes such as creating a table, altering a table,
dropping the table, dropping a tablespace be in the DB2 logs? I am writing a
log capture program so that I can replicate changes on my source DB2 by
getting change data information , get this information, convert it to a SQL
command, send it to another DB2 instance and run it there, in effect
replicating the DDL change. I am already able to do this with DML.
Furthermore, I am also looking at replicating DDL and DML changes to other
non-DB2 databases.

JWK

All DDL that I can think of ends up in the SYSIBM.SYS* tables at some
point. What you would have to do is reverse engineer out of the catalog
updates the DDL and apply it. db2look does this.
Note though that looking at SYSIBM.SYS* tables is a bit dangerous
because develeopment may reshuffle the deck on you on any given full
release - which is why the tables are not documented.
But again.. a simple look at SYSCAT.VIEWS will show how SYSCAT.* views
are derived.... not much of a secret.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
RdR
Hi Bernd,

What I am trying to do is to catch DDL and DML changes from the logs and
based on that information, package it, send it to a target DB2 and maybe
later on other databases and replicate a DDL change or a DML change. If you
are talking about Log Shipping, this is not log shipping that I am doing,
this is more for replicating DDL and DML changes in near real time. We have
done the same for Oracle redo logs but that is only Oracle as the source but
I can replicate DMl and DDL changes to a DB2, Oracle, SQL Server, MySQL
targets. In a heterogenoues environment, I can change a table structure in
Oracle and replicate that change to other databases, as well as row changes.
I want now DB2 to be the source but the log information I see is very
cryptic.

I do not think I am re-inventing the wheel here because there is no such
product that I have seen for DB2 to do this, that is why I am creating it.

Thanks,

JWK

"Bernd Giegerich"
<bgi0815-for-news-use-only@this-is-a-valid-mailaddress-but-please-do-not-spa
m-me.de> wrote in message news:vl************@news37.b-giegerich.de...
Hi,
Would anyone know if DDL changes such as creating a table,
altering a table, dropping the table, dropping a tablespace be
in the DB2 logs? I am writing a log capture program so that I
can replicate changes on my source DB2 by getting change data
information , get this information, convert it to a SQL command,
send it to another DB2 instance and run it there, in effect
replicating the DDL change.


sounds a little bit like re-inventing the wheel to me.

Take a look at the documentation, "high availability desaster
recovery". Works fine for me in a 24/7 production environment (DB2
8.2 on Win32).

Regards,
Bernd

Nov 12 '05 #4

P: n/a
RdR
Hi Serge,

Thanks for the information. That will be a nice work-around for what I am
trying to do (please see my reply to Bernd on this same thread). It would be
more efficient though if I get it straight from the logs maybe through DB2's
IFI.

Thanks,

JWK (RdR)

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:35*************@individual.net...
RdR wrote:
Would anyone know if DDL changes such as creating a table, altering a table, dropping the table, dropping a tablespace be in the DB2 logs? I am writing a log capture program so that I can replicate changes on my source DB2 by
getting change data information , get this information, convert it to a SQL command, send it to another DB2 instance and run it there, in effect
replicating the DDL change. I am already able to do this with DML.
Furthermore, I am also looking at replicating DDL and DML changes to other non-DB2 databases.

JWK

All DDL that I can think of ends up in the SYSIBM.SYS* tables at some
point. What you would have to do is reverse engineer out of the catalog
updates the DDL and apply it. db2look does this.
Note though that looking at SYSIBM.SYS* tables is a bit dangerous
because develeopment may reshuffle the deck on you on any given full
release - which is why the tables are not documented.
But again.. a simple look at SYSCAT.VIEWS will show how SYSCAT.* views
are derived.... not much of a secret.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #5

P: n/a
RdR wrote:
Hi Serge,

Thanks for the information. That will be a nice work-around for what I am
trying to do (please see my reply to Bernd on this same thread). It would be
more efficient though if I get it straight from the logs maybe through DB2's
IFI.

I'm talking about the logs! Catch the logs applied to SYSIBM.* tables.
That's all there is. DB2 does not log the "logical" DDL. All you'll see
is the effect on the tables.

What is IFI?

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

P: n/a
RdR
Hi Serge,

Thanks again.

DB2 IFI is DB2 Instrumentation Facility Interface. IFCID 306 gets changed
data information from the logs, IFCID126 detects if there are new entries to
read in the logs. I was hoping to use DB2 to read the log information for me
(IFICD 306). Also, so that I will not be polling to look for changes, I will
run a DB2 trace in one of the OP buffers and will alert my packaging
mechanism (through IFCID126).

Your suggestion can work, I will log the changes on the SYSIBM.* tables
using DB2's logging and find the before and after images in the logs the
changes of these SYSIBM.* tables, I will then compare the UB (Before Image)
abd the UP (After Image) and the net will give you the DDL change. I think
that information will be sufficient, a lot of testing is what is left.

JWK (RdR)

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:35*************@individual.net...
RdR wrote:
Hi Serge,

Thanks for the information. That will be a nice work-around for what I am trying to do (please see my reply to Bernd on this same thread). It would be more efficient though if I get it straight from the logs maybe through DB2's IFI.

I'm talking about the logs! Catch the logs applied to SYSIBM.* tables.
That's all there is. DB2 does not log the "logical" DDL. All you'll see
is the effect on the tables.

What is IFI?

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #7

P: n/a
Hi,
What I am trying to do is to catch DDL and DML changes from the
logs and based on that information, package it, send it to a
target DB2 and maybe later on other databases and replicate a
DDL change or a DML change.


HADR does at least large parts of this - but only if you are staying
with DB2.

Taken from the (8.1.7/8.2) documentation:

| In high availability disaster recovery (HADR), the following
| operations are replicated from the primary to the standby
| database:
|
| * Data definition language (DDL)
| * Data manipulation language (DML)
| * Buffer pool operations
| * Table space operations
| * Online reorganization
| * Offline reorganization
| * Meta-data for stored procedures and user defined
| functions (UDF)
| (but not the related object or library files)

I can affirm that it *does work* very well for table drops, table
creations, reorgs and loads besides the usual DML stuff.

90 days uptime for a W2K3 system until I had to reboot the box to get
some MS patches active, and still up since > 100 days if I consider
that I was able to do a "rolling upgrade" on the HADR pair.

That may give you at least a little bit certainty that it can be done
with DB2 - Serge's posts sound promising.

Regards,
Bernd
Nov 12 '05 #8

P: n/a
Bernd Giegerich wrote:
Hi,

What I am trying to do is to catch DDL and DML changes from the
logs and based on that information, package it, send it to a
target DB2 and maybe later on other databases and replicate a
DDL change or a DML change.

HADR does at least large parts of this - but only if you are staying
with DB2.

Taken from the (8.1.7/8.2) documentation:

| In high availability disaster recovery (HADR), the following
| operations are replicated from the primary to the standby
| database:
|
| * Data definition language (DDL)
| * Data manipulation language (DML)
| * Buffer pool operations
| * Table space operations
| * Online reorganization
| * Offline reorganization
| * Meta-data for stored procedures and user defined
| functions (UDF)
| (but not the related object or library files)

I can affirm that it *does work* very well for table drops, table
creations, reorgs and loads besides the usual DML stuff.

90 days uptime for a W2K3 system until I had to reboot the box to get
some MS patches active, and still up since > 100 days if I consider
that I was able to do a "rolling upgrade" on the HADR pair.

That may give you at least a little bit certainty that it can be done
with DB2 - Serge's posts sound promising.

Regards,
Bernd

Actually HADR applies the logs directly. The OP will need to go through
an SQL apply interface. Very much like Q-Replication.
Q-Replication today does not support DDL.... they know why.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #9

P: n/a
RdR
Hi Bernd and Serge,

Thank you for your valueable inputs. Bernd, where does HADR get information
when a DDL change is registered, is it from the logs (logs from changes from
the sysibm.* tables) or from the sysibm.* tables itself?

Actually, the scraping concept model that I started to work on is very
similar to Data Propagator but instead of CDC - DML data only, I was hoping
to get the DDL changes as well. I think the same concept applies to
Q-Replication as well but Q Replication relies on MQ to forward the
replicated change. But Q replication's design is better because it does not
use DB2 tables for staging Change Data Capture Data. Staging in DB2 tables
puts more data on the DB2 logs and has a tripling effect (staging tables,
pruning tables, etc.), this will make scraping of the logs slower because it
will scrape staging table and pruning table changes as well.

Bernd, now that you mentioned online reorgs, I think I will need to take
that into account to consider tablespace drains (this is starting to become
complicated) and see if it has a disqualifying effect on table or data
integrity. I am starting to see how a simple idea of replicating DDL and DML
can be very very complicated if I do it in real time. Will find out when I
start testing.

Thanks for all your help.

JWK (RdR)

"Bernd Giegerich"
<bgi0815-for-news-use-only@this-is-a-valid-mailaddress-but-please-do-not-spa
m-me.de> wrote in message news:16************@news37.b-giegerich.de...
Hi,
What I am trying to do is to catch DDL and DML changes from the
logs and based on that information, package it, send it to a
target DB2 and maybe later on other databases and replicate a
DDL change or a DML change.


HADR does at least large parts of this - but only if you are staying
with DB2.

Taken from the (8.1.7/8.2) documentation:

| In high availability disaster recovery (HADR), the following
| operations are replicated from the primary to the standby
| database:
|
| * Data definition language (DDL)
| * Data manipulation language (DML)
| * Buffer pool operations
| * Table space operations
| * Online reorganization
| * Offline reorganization
| * Meta-data for stored procedures and user defined
| functions (UDF)
| (but not the related object or library files)

I can affirm that it *does work* very well for table drops, table
creations, reorgs and loads besides the usual DML stuff.

90 days uptime for a W2K3 system until I had to reboot the box to get
some MS patches active, and still up since > 100 days if I consider
that I was able to do a "rolling upgrade" on the HADR pair.

That may give you at least a little bit certainty that it can be done
with DB2 - Serge's posts sound promising.

Regards,
Bernd

Nov 12 '05 #10

P: n/a
HADR operates very simple. It simply forwards the log to the secondary
and the secondary replays the log. Very low level. HADR has no clue
whatsoever about the involved SQL statements.

Now, I don't want to discourage yoru efforts, but reorg is only one
issue. Other issues are triggers. If your target has thd same triggers
you again have a cascading affect because you replay the triggering
change as well as the triggered change.
Further more you have a hard time distinguishing knowing where a
statement starts or stops.
Let me give you a very simple example:

CREATE TABLE T(c1 INT NOT NULL RRIMARY KEY);
INSERT INTO T VALUES (-1, 1);
Now:
UPDATE T SET c1 = -c1;
If you replay one row at a time:
UPDATE T SET c1 = <newval> WHERE c1 = <oldval>
You're in trouble... The first update will violate the unique constraint.
Only set-oriented the update can succeed.

Replication is a dark art...
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #11

P: n/a
Hi Serge,

The reorg still bugs me but the example you gave, I have already solved that
working with the redo logs of Oracle. I plan to do it the same way in DB2.
every Insert, Update, Delete has a Relative Byte Address and part of the log
information can let me know if an entry is a Begin Commit, End Commit, and
if it is a part of a unit of work and what part of unit of work (because
there can be a begin commit within a unit of work or several of them).
Since you know this, you can stage the scraped information on a primary
staging area, then refine the entries on a secondary staging area. I did not
use DB2 tables for staging because it will cause my DB2 log to have extra
entries, I did not use Message Queues either because the queues have a good
chance of blowing up and recovery might be a headache. I am currently
developing a patent on the staging area I am using and the method of staging
it, I will give more info about it once my patent has been approved but
suffice to say, it funcitons like the CDC and Pruning tables that Data
Propagator uses but does not use DB2 tables nor queues. In your example, I
will scrape changes as they go, if the database does not write the update
then I should be scraping a rollback or an error. The idea is if the source
accepts it, then I should be able to replicate it. If it is a whole unit of
work, then I should be able to send them in the manner they were APPLYed.

The DML part is working properly that is why I am concentrating on the DDL
portion but Bernd mentioned something I neglected to consider and I know
there will be lots that I failed to consider and that is reorgs, the offline
should be OK but the online may not be. I need to figure out some mechanism
to detect reorgs, tablespace drains, and other unknown stuff.

Thanks for the information you are giving.

JWK(RdR)

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:35*************@individual.net...
HADR operates very simple. It simply forwards the log to the secondary
and the secondary replays the log. Very low level. HADR has no clue
whatsoever about the involved SQL statements.

Now, I don't want to discourage yoru efforts, but reorg is only one
issue. Other issues are triggers. If your target has thd same triggers
you again have a cascading affect because you replay the triggering
change as well as the triggered change.
Further more you have a hard time distinguishing knowing where a
statement starts or stops.
Let me give you a very simple example:

CREATE TABLE T(c1 INT NOT NULL RRIMARY KEY);
INSERT INTO T VALUES (-1, 1);
Now:
UPDATE T SET c1 = -c1;
If you replay one row at a time:
UPDATE T SET c1 = <newval> WHERE c1 = <oldval>
You're in trouble... The first update will violate the unique constraint.
Only set-oriented the update can succeed.

Replication is a dark art...
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #12

P: n/a
PMFJI,

I think RdR talks about DB2 on z/OS while Bernd and Serge are talking about
DB2 for UNIX/Windows.
I'm not sure if the log mechanism works the same on these different
platforms (in fact I doubt it)..

Just wanted to clear up things
Joachim

"RdR" <rr******@datamirror.com> schrieb im Newsbeitrag
news:0a********************@nnrp1.uunet.ca...
Hi Serge,

Thanks again.

DB2 IFI is DB2 Instrumentation Facility Interface. IFCID 306 gets changed
data information from the logs, IFCID126 detects if there are new entries
to
read in the logs. I was hoping to use DB2 to read the log information for
me
(IFICD 306). Also, so that I will not be polling to look for changes, I
will
run a DB2 trace in one of the OP buffers and will alert my packaging
mechanism (through IFCID126).

Your suggestion can work, I will log the changes on the SYSIBM.* tables
using DB2's logging and find the before and after images in the logs the
changes of these SYSIBM.* tables, I will then compare the UB (Before
Image)
abd the UP (After Image) and the net will give you the DDL change. I think
that information will be sufficient, a lot of testing is what is left.

JWK (RdR)

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:35*************@individual.net...
RdR wrote:
> Hi Serge,
>
> Thanks for the information. That will be a nice work-around for what I am > trying to do (please see my reply to Bernd on this same thread). It would be > more efficient though if I get it straight from the logs maybe through DB2's > IFI.

I'm talking about the logs! Catch the logs applied to SYSIBM.* tables.
That's all there is. DB2 does not log the "logical" DDL. All you'll see
is the effect on the tables.

What is IFI?

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab


Nov 12 '05 #13

P: n/a
RdR
Joachim is correct, most of my comments primarily relates to the mainframe
but I think can still apply to UNIX and Windows but with major differences
in the scraping, I think IFI is something else in UNIX/Windows but there
seems to be an equivalent. The log mechanisms are very different but the
similarities will be enough to scrape DML changes and hopefully DDL as well.

JWK(RdR)

"Joachim Klassen" <jo*******@mail.com> wrote in message
news:41***********************@newsread4.arcor-online.net...
PMFJI,

I think RdR talks about DB2 on z/OS while Bernd and Serge are talking about DB2 for UNIX/Windows.
I'm not sure if the log mechanism works the same on these different
platforms (in fact I doubt it)..

Just wanted to clear up things
Joachim

"RdR" <rr******@datamirror.com> schrieb im Newsbeitrag
news:0a********************@nnrp1.uunet.ca...
Hi Serge,

Thanks again.

DB2 IFI is DB2 Instrumentation Facility Interface. IFCID 306 gets changed data information from the logs, IFCID126 detects if there are new entries to
read in the logs. I was hoping to use DB2 to read the log information for me
(IFICD 306). Also, so that I will not be polling to look for changes, I
will
run a DB2 trace in one of the OP buffers and will alert my packaging
mechanism (through IFCID126).

Your suggestion can work, I will log the changes on the SYSIBM.* tables
using DB2's logging and find the before and after images in the logs the
changes of these SYSIBM.* tables, I will then compare the UB (Before
Image)
abd the UP (After Image) and the net will give you the DDL change. I think that information will be sufficient, a lot of testing is what is left.

JWK (RdR)

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:35*************@individual.net...
RdR wrote:
> Hi Serge,
>
> Thanks for the information. That will be a nice work-around for what
I am
> trying to do (please see my reply to Bernd on this same thread). It

would be
> more efficient though if I get it straight from the logs maybe
through DB2's
> IFI.
I'm talking about the logs! Catch the logs applied to SYSIBM.* tables.
That's all there is. DB2 does not log the "logical" DDL. All you'll see
is the effect on the tables.

What is IFI?

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab



Nov 12 '05 #14

P: n/a
JS
"Joachim Klassen" <jo*******@mail.com> wrote in message news:<41***********************@newsread4.arcor-online.net>...
PMFJI,

I think RdR talks about DB2 on z/OS while Bernd and Serge are talking about
DB2 for UNIX/Windows.
I'm not sure if the log mechanism works the same on these different
platforms (in fact I doubt it)..

Just wanted to clear up things
Joachim

"RdR" <rr******@datamirror.com> schrieb im Newsbeitrag
news:0a********************@nnrp1.uunet.ca...
Hi Serge,

Thanks again.

DB2 IFI is DB2 Instrumentation Facility Interface. IFCID 306 gets changed
data information from the logs, IFCID126 detects if there are new entries
to
read in the logs. I was hoping to use DB2 to read the log information for
me
(IFICD 306). Also, so that I will not be polling to look for changes, I
will
run a DB2 trace in one of the OP buffers and will alert my packaging
mechanism (through IFCID126).

Your suggestion can work, I will log the changes on the SYSIBM.* tables
using DB2's logging and find the before and after images in the logs the
changes of these SYSIBM.* tables, I will then compare the UB (Before
Image)
abd the UP (After Image) and the net will give you the DDL change. I think
that information will be sufficient, a lot of testing is what is left.

JWK (RdR)

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:35*************@individual.net...
RdR wrote:
> Hi Serge,
>
> Thanks for the information. That will be a nice work-around for what I

am
> trying to do (please see my reply to Bernd on this same thread). It

would be
> more efficient though if I get it straight from the logs maybe through

DB2's
> IFI.
I'm talking about the logs! Catch the logs applied to SYSIBM.* tables.
That's all there is. DB2 does not log the "logical" DDL. All you'll see
is the effect on the tables.

What is IFI?

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab



Perhaps this link may help you:
http://publib.boulder.ibm.com/infoce...n/r0001910.htm
Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.