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

Cannot turn on logging for tables with names > 18 chars

P: n/a
RdR
Hi,

I am using Q Replication, I need to set to logging to capture changes on a
table but the table has more than 18 characters for the name, I looked at
the docs, it mentioned that table names with more than 18 characters are
allowed in DB2 V8 but when you turn on logging, it will not turn on logging.
Is there a work around to this?

Thanks
Nov 12 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
RdR wrote:
Hi,

I am using Q Replication, I need to set to logging to capture changes on a
table but the table has more than 18 characters for the name, I looked at
the docs, it mentioned that table names with more than 18 characters are
allowed in DB2 V8 but when you turn on logging, it will not turn on
logging. Is there a work around to this?


What exactly did you do and what's the error message you got?

Have a look at the doc for ALTER TABLE. It states that the DATA CAPTURE
option is not supported if the schema name is longer than 18 characters.
Maybe that's the case for you?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #2

P: n/a
RdR
Hi Knut,

Thank you for your email.

This is the message I am getting when I set one of those tables with a name
with >18 characters to be propagated. I am sure you know that DATA CAPTURE
needs to be turned on to be able to replicate data using Q Replication (or
for that matter SQL Replication (DPROP) as well).

Other than renaming the table to something with 18 or less than an 18
character name, are there other work arounds, is there a switch somewhere
that I can use to turn DATA CAPTURE ON without going through the ALTER TABLE
command?

What I do is define this table to be replicated and I think QReplication
sends an ALTER TABLE command to DB2 and encounters the more than 18
character limit for turning DATA CAPTURE on. Seems like DB2 Version 8 does
not have the 18 character name limitation but in terms of setting DATA
CAPTURE on, still has this limitation. One reason that DATA CAPTURE is not
on is that this cannot be done manually as well.

It is just a bit of work to rename about 100 or so table with this case.
Knowing a work around will save me lots of time. Appreciate any sugestions.

Again, all my thanks.

RdR

"Knut Stolze" <st****@de.ibm.com> wrote in message
news:dj**********@lc03.rz.uni-jena.de...
RdR wrote:
Hi,

I am using Q Replication, I need to set to logging to capture changes on a table but the table has more than 18 characters for the name, I looked at the docs, it mentioned that table names with more than 18 characters are
allowed in DB2 V8 but when you turn on logging, it will not turn on
logging. Is there a work around to this?


What exactly did you do and what's the error message you got?

Have a look at the doc for ALTER TABLE. It states that the DATA CAPTURE
option is not supported if the schema name is longer than 18 characters.
Maybe that's the case for you?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Nov 12 '05 #3

P: n/a
In article <cs********************@rogers.com>, ro*@delrosario.ca
says...

This is the message I am getting when I set one of those tables with a name
with >18 characters to be propagated. I am sure you know that DATA CAPTURE
needs to be turned on to be able to replicate data using Q Replication (or
for that matter SQL Replication (DPROP) as well).


I assume you are not using DB2 LUW, but DB2 AS/400 or DB2 OS390?
Nov 12 '05 #4

P: n/a
RdR wrote:
Hi Knut,

Thank you for your email.

This is the message I am getting when I set one of those tables with a
name with >18 characters to be propagated. I am sure you know that DATA
CAPTURE needs to be turned on to be able to replicate data using Q
Replication (or for that matter SQL Replication (DPROP) as well).

Other than renaming the table to something with 18 or less than an 18
character name, are there other work arounds, is there a switch somewhere
that I can use to turn DATA CAPTURE ON without going through the ALTER
TABLE command?

What I do is define this table to be replicated and I think QReplication
sends an ALTER TABLE command to DB2 and encounters the more than 18
character limit for turning DATA CAPTURE on. Seems like DB2 Version 8 does
not have the 18 character name limitation but in terms of setting DATA
CAPTURE on, still has this limitation. One reason that DATA CAPTURE is not
on is that this cannot be done manually as well.


Well, what exactly have you done manually and what exactly was the error
message that you got?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #5

P: n/a
Knut,

you are quite right.
when choosing to enable a table for replication in the control center,
it does a
'alter table ... data capture on'

so that is not supported for tablenames longer than 18 character.

Funny, I guess, the replication system tables where not updated for
longer table names ... :-)

Nov 12 '05 #6

P: n/a
RdR
Hi Knut,

I ran the ALTER TABLE command to turn on DATA CAPTURE for that table with
characters with more than 18 characters and the error message I got was the
mesage you pointed to that table schemas with more than 18 characters for
the name cannot be enabled for DATA CAPTURE.

This is on a UDB V8 for AIX.

Seems like UDB allows for table names more than 18 characters but if you use
QReplication or DPROP to replicate tables with more than 18 characters for
the name, it is not supported for replication. Hmmmmmmm.

RdR
"Knut Stolze" <st****@de.ibm.com> wrote in message
news:dj**********@lc03.rz.uni-jena.de...
RdR wrote:
Hi Knut,

Thank you for your email.

This is the message I am getting when I set one of those tables with a
name with >18 characters to be propagated. I am sure you know that DATA
CAPTURE needs to be turned on to be able to replicate data using Q
Replication (or for that matter SQL Replication (DPROP) as well).

Other than renaming the table to something with 18 or less than an 18
character name, are there other work arounds, is there a switch somewhere that I can use to turn DATA CAPTURE ON without going through the ALTER
TABLE command?

What I do is define this table to be replicated and I think QReplication
sends an ALTER TABLE command to DB2 and encounters the more than 18
character limit for turning DATA CAPTURE on. Seems like DB2 Version 8 does not have the 18 character name limitation but in terms of setting DATA
CAPTURE on, still has this limitation. One reason that DATA CAPTURE is not on is that this cannot be done manually as well.


Well, what exactly have you done manually and what exactly was the error
message that you got?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Nov 12 '05 #7

P: n/a
juliane26 wrote:
Knut,

you are quite right.
when choosing to enable a table for replication in the control center,
it does a
'alter table ... data capture on'

so that is not supported for tablenames longer than 18 character.


The thing is that the documentation only talks about schema names that must
not be longer than 18 characters. It doesn't say anything about the
unqualified table name. Therefore, I really would like to see the _exact_
SQL statement and the _exact_ output that was produced by DB2. So far, we
only got some verbose descriptions of the scenario, which makes it more
complicated to reproduce and verify. Thus, I would not make a call whether
this works as documented (and is actually a user error) or if the
documentation is wrong and there is an additional restriction. So please,
post the complete ALTER TABLE SQL statement and the complete error message.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #8

P: n/a
RdR
Hi Knut,

I will get you the actual commands and the result, only thing is, I have
already renamed the tables as my workaround so I cannot readily run it right
now, I would either have to sift through the logs or create another table
with the same characteristics to get you what you mentioned, once I
implemented this, I will get you these and post it here. I just need to beat
my implementation date before I can do further troubleshooting.

But it is easy to recreate the issue, create a table that has a schema name
that has more than 18 characters for the name and do an ALTER TABLE on the
schema name to set full logging to ON. I might also have column names with
names greater than 18 characters, table is just a simple table composed of
chars, varchars, etc. (no BLOBS, CLOBS and the like).

My question was not whether the documentation was correct or not, it is more
if there is a workaround other than renaming the tables concerned. In my
situation, the issue was trivial to open a PMR with IBM with this issue if
ever it was really an issue.

Thanks for all the ideas though.

RdR
"Knut Stolze" <st****@de.ibm.com> wrote in message
news:dj**********@lc03.rz.uni-jena.de...
juliane26 wrote:
Knut,

you are quite right.
when choosing to enable a table for replication in the control center,
it does a
'alter table ... data capture on'

so that is not supported for tablenames longer than 18 character.
The thing is that the documentation only talks about schema names that

must not be longer than 18 characters. It doesn't say anything about the
unqualified table name. Therefore, I really would like to see the _exact_
SQL statement and the _exact_ output that was produced by DB2. So far, we
only got some verbose descriptions of the scenario, which makes it more
complicated to reproduce and verify. Thus, I would not make a call whether this works as documented (and is actually a user error) or if the
documentation is wrong and there is an additional restriction. So please,
post the complete ALTER TABLE SQL statement and the complete error message.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Nov 12 '05 #9

P: n/a
I did some digging. Replication does support 128 character table names.
This is plain and simple an outdated restriction in ALTER TABLE (aka a
bug).

Cheers
Serge

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

P: n/a
RdR
Hi Serge,

Thanks for the information. I was wondering if there was a work around other
than renaming the table names to less than 18 characters?

Thanks,

RdR

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3s************@individual.net...
I did some digging. Replication does support 128 character table names.
This is plain and simple an outdated restriction in ALTER TABLE (aka a
bug).

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #11

P: n/a
RdR wrote:
Hi Serge,

Thanks for the information. I was wondering if there was a work around other
than renaming the table names to less than 18 characters?

Try and see if CREATE TABLE works. If it does, there is another
workaround which may or may not be more feasible than renaming.

Again, it's a bug in the DDL check. If you open an APAR you can receive
a fix.

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

This discussion thread is closed

Replies have been disabled for this discussion.