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

DB2 and extended ASCII table

P: n/a
Hi!

Does DB2 handle extended ASCII table?
Example:
VALUES(CHR(65)) =A
VALUES(CHR(129)) =null, but according to www.asciitable.com should be u
with umlaut.

Any idea ?

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Oct 27 '06 #1
Share this Question
Share on Google+
17 Replies


P: n/a
Gregor Kovac( wrote:
Hi!

Does DB2 handle extended ASCII table?
Example:
VALUES(CHR(65)) =A
VALUES(CHR(129)) =null, but according to www.asciitable.com should be u
with umlaut.

Any idea ?
I quote from the URL:
The _most_popular_ is presented below.
For single byte code pages I don't see a reason not to support all 255
characters and do whatever the DB code page mandates.

Anyway, the easiest workaround is probably to imply write a trivial UDF
in C/Java/CLR which does the job.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 27 '06 #2

P: n/a
Serge Rielau wrote:
Gregor Kovac( wrote:
>Hi!

Does DB2 handle extended ASCII table?
Example:
VALUES(CHR(65)) =A
VALUES(CHR(129)) =null, but according to www.asciitable.com should be u
with umlaut.

Any idea ?
I quote from the URL:
The _most_popular_ is presented below.
For single byte code pages I don't see a reason not to support all 255
characters and do whatever the DB code page mandates.

Anyway, the easiest workaround is probably to imply write a trivial UDF
in C/Java/CLR which does the job.
Of course a big case expression will also work ;-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 27 '06 #3

P: n/a
Serge Rielau wrote:
Serge Rielau wrote:
>Gregor Kovac( wrote:
>>Hi!

Does DB2 handle extended ASCII table?
Example:
VALUES(CHR(65)) =A
VALUES(CHR(129)) =null, but according to www.asciitable.com should be
u with umlaut.

Any idea ?
I quote from the URL:
The _most_popular_ is presented below.
For single byte code pages I don't see a reason not to support all 255
characters and do whatever the DB code page mandates.

Anyway, the easiest workaround is probably to imply write a trivial UDF
in C/Java/CLR which does the job.
Of course a big case expression will also work ;-)

Cheers
Serge
Hmmm....

The thing is that I have to replace some characters in a VARCHAR field.
For example: Č (C with a caron) goes into CHR(219). I'm not sure quite what
are you talking about.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Oct 27 '06 #4

P: n/a
Gregor Kovač wrote:
Serge Rielau wrote:
>Serge Rielau wrote:
>>Gregor Kovac( wrote:
Hi!

Does DB2 handle extended ASCII table?
Example:
VALUES(CHR(65)) =A
VALUES(CHR(129)) =null, but according to www.asciitable.com should be
u with umlaut.

Any idea ?
I quote from the URL:
The _most_popular_ is presented below.
For single byte code pages I don't see a reason not to support all 255
characters and do whatever the DB code page mandates.

Anyway, the easiest workaround is probably to imply write a trivial UDF
in C/Java/CLR which does the job.
Of course a big case expression will also work ;-)

Cheers
Serge

Hmmm....

The thing is that I have to replace some characters in a VARCHAR field.
For example: Č (C with a caron) goes into CHR(219). I'm not sure quite what
are you talking about.

Best regards,
Kovi
CREATE FUNCTION extendedchr(arg INT) RETURNS CHAR(1)
RETURN CASE WHEN arg BETWEEN 0 AND 127 THEN CHR(arg)
WHEN arg = 219 THEN 'Č'
END

Wouldn't that work?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 27 '06 #5

P: n/a
Serge Rielau wrote:
Gregor Kovač wrote:
>Serge Rielau wrote:
>>Serge Rielau wrote:
Gregor Kovac( wrote:
Hi!
>
Does DB2 handle extended ASCII table?
Example:
VALUES(CHR(65)) =A
VALUES(CHR(129)) =null, but according to www.asciitable.com should
be u with umlaut.
>
Any idea ?
I quote from the URL:
The _most_popular_ is presented below.
For single byte code pages I don't see a reason not to support all 255
characters and do whatever the DB code page mandates.

Anyway, the easiest workaround is probably to imply write a trivial UDF
in C/Java/CLR which does the job.
Of course a big case expression will also work ;-)

Cheers
Serge

Hmmm....

The thing is that I have to replace some characters in a VARCHAR field.
For example: Č (C with a caron) goes into CHR(219). I'm not sure quite
what are you talking about.

Best regards,
Kovi
CREATE FUNCTION extendedchr(arg INT) RETURNS CHAR(1)
RETURN CASE WHEN arg BETWEEN 0 AND 127 THEN CHR(arg)
WHEN arg = 219 THEN 'Č'
END

Wouldn't that work?

Hmm.. Not exactly, because the right way to write this FUNCTION would be:
CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(A) = 219 THEN CHR(219)
END

The problem is that I cannot get CHR(219) to display properly.

Best regards,
Kovi

P.S.: I'm preparing the database for you, but I have a problem deleting
large tables. How would you recommend deleting a table really fast? The
problem is that I do not want to drop tables. :))

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Oct 28 '06 #6

P: n/a
Gregor Kovač wrote:
Serge Rielau wrote:
>Gregor Kovač wrote:
>>Serge Rielau wrote:

Serge Rielau wrote:
Gregor Kovac( wrote:
>Hi!
>>
>Does DB2 handle extended ASCII table?
>Example:
>VALUES(CHR(65)) =A
>VALUES(CHR(129)) =null, but according to www.asciitable.com should
>be u with umlaut.
>>
>Any idea ?
I quote from the URL:
The _most_popular_ is presented below.
For single byte code pages I don't see a reason not to support all 255
characters and do whatever the DB code page mandates.
>
Anyway, the easiest workaround is probably to imply write a trivial UDF
in C/Java/CLR which does the job.
Of course a big case expression will also work ;-)

Cheers
Serge

Hmmm....

The thing is that I have to replace some characters in a VARCHAR field.
For example: Č (C with a caron) goes into CHR(219). I'm not sure quite
what are you talking about.

Best regards,
Kovi
CREATE FUNCTION extendedchr(arg INT) RETURNS CHAR(1)
RETURN CASE WHEN arg BETWEEN 0 AND 127 THEN CHR(arg)
WHEN arg = 219 THEN 'Č'
END

Wouldn't that work?

Hmm.. Not exactly, because the right way to write this FUNCTION would be:
CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(A) = 219 THEN CHR(219)
END
Uhm.. isn't that a no-op?
If you have problems with display in CLP or wherever that sounds like a
code page problem.
P.S.: I'm preparing the database for you, but I have a problem deleting
large tables. How would you recommend deleting a table really fast? The
problem is that I do not want to drop tables. :))
ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 28 '06 #7

P: n/a
Serge Rielau wrote:
Gregor Kovač wrote:
>Serge Rielau wrote:
>>Gregor Kovač wrote:
Serge Rielau wrote:

Serge Rielau wrote:
>Gregor Kovac( wrote:
>>Hi!
>>>
>>Does DB2 handle extended ASCII table?
>>Example:
>>VALUES(CHR(65)) =A
>>VALUES(CHR(129)) =null, but according to www.asciitable.com should
>>be u with umlaut.
>>>
>>Any idea ?
>I quote from the URL:
>The _most_popular_ is presented below.
>For single byte code pages I don't see a reason not to support all
>255 characters and do whatever the DB code page mandates.
>>
>Anyway, the easiest workaround is probably to imply write a trivial
>UDF in C/Java/CLR which does the job.
Of course a big case expression will also work ;-)
>
Cheers
Serge
>
Hmmm....

The thing is that I have to replace some characters in a VARCHAR field.
For example: Č (C with a caron) goes into CHR(219). I'm not sure quite
what are you talking about.

Best regards,
Kovi
CREATE FUNCTION extendedchr(arg INT) RETURNS CHAR(1)
RETURN CASE WHEN arg BETWEEN 0 AND 127 THEN CHR(arg)
WHEN arg = 219 THEN 'Č'
END

Wouldn't that work?

Hmm.. Not exactly, because the right way to write this FUNCTION would be:
CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(A) = 219 THEN CHR(219)
END
Uhm.. isn't that a no-op?
If you have problems with display in CLP or wherever that sounds like a
code page problem.
I'm sorry. This should be like this:
CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(C) = 219 THEN CHR(219)
END

I don't see the right output in my DB tool (DbVisualizer) and also not in
db2 interactive mode.
>P.S.: I'm preparing the database for you, but I have a problem deleting
large tables. How would you recommend deleting a table really fast? The
problem is that I do not want to drop tables. :))
ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;
Hmm.. Running this give me:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1596N WITH EMPTY TABLE cannot be specified for "TABLE1".
SQLSTATE=42928

Docs say that when specifying WITH EMPTY TABLE:
"A partitioned table with attached data partitions cannot be emptied
(SQLSTATE 42928"
But this table is not partitioned.
>
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Oct 28 '06 #8

P: n/a
Gregor Kovač wrote:
Serge Rielau wrote:
>Gregor Kovač wrote:
>>Serge Rielau wrote:

Gregor Kovač wrote:
Serge Rielau wrote:
>
>Serge Rielau wrote:
>>Gregor Kovac( wrote:
>>>Hi!
>>>>
>>>Does DB2 handle extended ASCII table?
>>>Example:
>>>VALUES(CHR(65)) =A
>>>VALUES(CHR(129)) =null, but according to www.asciitable.com should
>>>be u with umlaut.
>>>>
>>>Any idea ?
>>I quote from the URL:
>>The _most_popular_ is presented below.
>>For single byte code pages I don't see a reason not to support all
>>255 characters and do whatever the DB code page mandates.
>>>
>>Anyway, the easiest workaround is probably to imply write a trivial
>>UDF in C/Java/CLR which does the job.
>Of course a big case expression will also work ;-)
>>
>Cheers
>Serge
>>
Hmmm....
>
The thing is that I have to replace some characters in a VARCHAR field.
For example: Č (C with a caron) goes into CHR(219). I'm not sure quite
what are you talking about.
>
Best regards,
Kovi
CREATE FUNCTION extendedchr(arg INT) RETURNS CHAR(1)
RETURN CASE WHEN arg BETWEEN 0 AND 127 THEN CHR(arg)
WHEN arg = 219 THEN 'Č'
END

Wouldn't that work?
Hmm.. Not exactly, because the right way to write this FUNCTION would be:
CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(A) = 219 THEN CHR(219)
END
Uhm.. isn't that a no-op?
If you have problems with display in CLP or wherever that sounds like a
code page problem.

I'm sorry. This should be like this:
CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(C) = 219 THEN CHR(219)
END

I don't see the right output in my DB tool (DbVisualizer) and also not in
db2 interactive mode.
>>P.S.: I'm preparing the database for you, but I have a problem deleting
large tables. How would you recommend deleting a table really fast? The
problem is that I do not want to drop tables. :))
ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

Hmm.. Running this give me:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1596N WITH EMPTY TABLE cannot be specified for "TABLE1".
SQLSTATE=42928

Docs say that when specifying WITH EMPTY TABLE:
"A partitioned table with attached data partitions cannot be emptied
(SQLSTATE 42928"
But this table is not partitioned.
OK, well then what about doing a LOAD REPLACE or IMPORT REPLACE?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 29 '06 #9

P: n/a
Serge Rielau wrote:
Gregor Kovač wrote:
>Serge Rielau wrote:
>>Gregor Kovač wrote:
Serge Rielau wrote:

Gregor Kovač wrote:
>Serge Rielau wrote:
>>
>>Serge Rielau wrote:
>>>Gregor Kovac( wrote:
>>>>Hi!
>>>>>
>>>>Does DB2 handle extended ASCII table?
>>>>Example:
>>>>VALUES(CHR(65)) =A
>>>>VALUES(CHR(129)) =null, but according to www.asciitable.com
>>>>should be u with umlaut.
>>>>>
>>>>Any idea ?
>>>I quote from the URL:
>>>The _most_popular_ is presented below.
>>>For single byte code pages I don't see a reason not to support all
>>>255 characters and do whatever the DB code page mandates.
>>>>
>>>Anyway, the easiest workaround is probably to imply write a trivial
>>>UDF in C/Java/CLR which does the job.
>>Of course a big case expression will also work ;-)
>>>
>>Cheers
>>Serge
>>>
>Hmmm....
>>
>The thing is that I have to replace some characters in a VARCHAR
>field. For example: Č (C with a caron) goes into CHR(219). I'm not
>sure quite what are you talking about.
>>
>Best regards,
> Kovi
CREATE FUNCTION extendedchr(arg INT) RETURNS CHAR(1)
RETURN CASE WHEN arg BETWEEN 0 AND 127 THEN CHR(arg)
WHEN arg = 219 THEN 'Č'
END
>
Wouldn't that work?
Hmm.. Not exactly, because the right way to write this FUNCTION would
be: CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(A) = 219 THEN CHR(219)
END
Uhm.. isn't that a no-op?
If you have problems with display in CLP or wherever that sounds like a
code page problem.

I'm sorry. This should be like this:
CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(C) = 219 THEN CHR(219)
END

I don't see the right output in my DB tool (DbVisualizer) and also not in
db2 interactive mode.
>>>P.S.: I'm preparing the database for you, but I have a problem deleting
large tables. How would you recommend deleting a table really fast? The
problem is that I do not want to drop tables. :))

ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

Hmm.. Running this give me:
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL1596N WITH EMPTY TABLE cannot be specified for "TABLE1".
SQLSTATE=42928

Docs say that when specifying WITH EMPTY TABLE:
"A partitioned table with attached data partitions cannot be emptied
(SQLSTATE 42928"
But this table is not partitioned.
OK, well then what about doing a LOAD REPLACE or IMPORT REPLACE?
Yes, I can do this, but ( :)) ) when I try to use IMPORT REPLACE it wants me
to drop tables that have foreign keys to the one im importing to. Ahh....
Any suggestions? I've also tried LOAD REPLACE, but didn't succeed with it.
I was using command:
LOAD FROM TABLE1.IXF OF IXF REPLACE INTO TABLE1
and it was working ok. :)

Thanks and best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Oct 30 '06 #10

P: n/a
Gregor Kovač wrote:
Serge Rielau wrote:
>Gregor Kovač wrote:
>>Serge Rielau wrote:

Gregor Kovač wrote:
Serge Rielau wrote:
>
>Gregor Kovač wrote:
>>Serge Rielau wrote:
>>>
>>>Serge Rielau wrote:
>>>>Gregor Kovac( wrote:
>>>>>Hi!
>>>>>>
>>>>>Does DB2 handle extended ASCII table?
>>>>>Example:
>>>>>VALUES(CHR(65)) =A
>>>>>VALUES(CHR(129)) =null, but according to www.asciitable.com
>>>>>should be u with umlaut.
>>>>>>
>>>>>Any idea ?
>>>>I quote from the URL:
>>>>The _most_popular_ is presented below.
>>>>For single byte code pages I don't see a reason not to support all
>>>>255 characters and do whatever the DB code page mandates.
>>>>>
>>>>Anyway, the easiest workaround is probably to imply write a
>>>>trivial UDF in C/Java/CLR which does the job.
>>>Of course a big case expression will also work ;-)
>>>>
>>>Cheers
>>>Serge
>>>>
>>Hmmm....
>>>
>>The thing is that I have to replace some characters in a VARCHAR
>>field. For example: Č (C with a caron) goes into CHR(219). I'm not
>>sure quite what are you talking about.
>>>
>>Best regards,
>> Kovi
>CREATE FUNCTION extendedchr(arg INT) RETURNS CHAR(1)
>RETURN CASE WHEN arg BETWEEN 0 AND 127 THEN CHR(arg)
> WHEN arg = 219 THEN 'Č'
> END
>>
>Wouldn't that work?
Hmm.. Not exactly, because the right way to write this FUNCTION would
be: CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(A) = 219 THEN CHR(219)
END
Uhm.. isn't that a no-op?
If you have problems with display in CLP or wherever that sounds like a
code page problem.
I'm sorry. This should be like this:
CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(C) = 219 THEN CHR(219)
END

I don't see the right output in my DB tool (DbVisualizer) and also not
in db2 interactive mode.
Can your tool and the shell actually show the 'Č' correctly (independent of
DB2)? If not, then you probably have a misconfiguration in your
environment that should be fixed. Have you tried a Java application since
its Unicode support may get rid of this for you all right?
>>>ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

Hmm.. Running this give me:
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1596N WITH EMPTY TABLE cannot be specified for "TABLE1".
SQLSTATE=42928

Docs say that when specifying WITH EMPTY TABLE:
"A partitioned table with attached data partitions cannot be emptied
(SQLSTATE 42928"
But this table is not partitioned.
OK, well then what about doing a LOAD REPLACE or IMPORT REPLACE?
Yes, I can do this, but ( :)) ) when I try to use IMPORT REPLACE it wants
me to drop tables that have foreign keys to the one im importing to.
Well, if you have dependent records in other tables, those dependencies
(referential integrity aka foreign key) wouldn't be satisfied after the
table is truncated. Thus, you can either truncate those dependent tables
first (possibly cascading), or you drop the foreign key constraints.
Ahh.... Any suggestions? I've also tried LOAD REPLACE, but didn't succeed
with it. I was using command:
LOAD FROM TABLE1.IXF OF IXF REPLACE INTO TABLE1
and it was working ok. :)
Was it "working ok" or "didn't you succeed"? If it failed, then what's the
error that you got?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Oct 30 '06 #11

P: n/a
Knut Stolze wrote:
Gregor Kovač wrote:
>Serge Rielau wrote:
>>Gregor Kovač wrote:
Serge Rielau wrote:

Gregor Kovač wrote:
>Serge Rielau wrote:
>>
>>Gregor Kovač wrote:
>>>Serge Rielau wrote:
>>>>
>>>>Serge Rielau wrote:
>>>>>Gregor Kovac( wrote:
>>>>>>Hi!
>>>>>>>
>>>>>>Does DB2 handle extended ASCII table?
>>>>>>Example:
>>>>>>VALUES(CHR(65)) =A
>>>>>>VALUES(CHR(129)) =null, but according to www.asciitable.com
>>>>>>should be u with umlaut.
>>>>>>>
>>>>>>Any idea ?
>>>>>I quote from the URL:
>>>>>The _most_popular_ is presented below.
>>>>>For single byte code pages I don't see a reason not to support
>>>>>all 255 characters and do whatever the DB code page mandates.
>>>>>>
>>>>>Anyway, the easiest workaround is probably to imply write a
>>>>>trivial UDF in C/Java/CLR which does the job.
>>>>Of course a big case expression will also work ;-)
>>>>>
>>>>Cheers
>>>>Serge
>>>>>
>>>Hmmm....
>>>>
>>>The thing is that I have to replace some characters in a VARCHAR
>>>field. For example: Č (C with a caron) goes into CHR(219). I'm not
>>>sure quite what are you talking about.
>>>>
>>>Best regards,
>>> Kovi
>>CREATE FUNCTION extendedchr(arg INT) RETURNS CHAR(1)
>>RETURN CASE WHEN arg BETWEEN 0 AND 127 THEN CHR(arg)
>> WHEN arg = 219 THEN 'Č'
>> END
>>>
>>Wouldn't that work?
>Hmm.. Not exactly, because the right way to write this FUNCTION would
>be: CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
>RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
> WHEN ASCII(A) = 219 THEN CHR(219)
> END
Uhm.. isn't that a no-op?
If you have problems with display in CLP or wherever that sounds like
a code page problem.
>

I'm sorry. This should be like this:
CREATE FUNCTION extendedchr(CHAR C) RETURNS CHAR(1)
RETURN CASE WHEN ASCII(C) BETWEEN 0 AND 127 THEN C
WHEN ASCII(C) = 219 THEN CHR(219)
END

I don't see the right output in my DB tool (DbVisualizer) and also not
in db2 interactive mode.

Can your tool and the shell actually show the 'Č' correctly (independent
of
DB2)? If not, then you probably have a misconfiguration in your
environment that should be fixed. Have you tried a Java application since
its Unicode support may get rid of this for you all right?

The tool I'm using is written in Java and I can see Č as I should. only when
I do the replace on a VARCHAR with Č characters in I don't get anything
back from DB2 (the tool shows (null). (null) is shown for every column that
has a NULL value).
>>>>ALTER TABLE T ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
COMMIT;

Hmm.. Running this give me:
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1596N WITH EMPTY TABLE cannot be specified for "TABLE1".
SQLSTATE=42928

Docs say that when specifying WITH EMPTY TABLE:
"A partitioned table with attached data partitions cannot be emptied
(SQLSTATE 42928"
But this table is not partitioned.
OK, well then what about doing a LOAD REPLACE or IMPORT REPLACE?
Yes, I can do this, but ( :)) ) when I try to use IMPORT REPLACE it wants
me to drop tables that have foreign keys to the one im importing to.

Well, if you have dependent records in other tables, those dependencies
(referential integrity aka foreign key) wouldn't be satisfied after the
table is truncated. Thus, you can either truncate those dependent tables
first (possibly cascading), or you drop the foreign key constraints.
>Ahh.... Any suggestions? I've also tried LOAD REPLACE, but didn't succeed
with it. I was using command:
LOAD FROM TABLE1.IXF OF IXF REPLACE INTO TABLE1
and it was working ok. :)

Was it "working ok" or "didn't you succeed"? If it failed, then what's
the error that you got?
I've found what the problem was. :))) It was a plain typoo in the LOAD
command. :)) Sorry...
>
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Oct 30 '06 #12

P: n/a
Gregor Kovač wrote:
>Can your tool and the shell actually show the 'Č' correctly (independent
of
DB2)? If not, then you probably have a misconfiguration in your
environment that should be fixed. Have you tried a Java application
since its Unicode support may get rid of this for you all right?

The tool I'm using is written in Java and I can see Č as I should. only
when I do the replace on a VARCHAR with Č characters in I don't get
anything back from DB2 (the tool shows (null). (null) is shown for every
column that has a NULL value).
Well, at least we know that your tool can handle the 'Č'.

Now we have two questions here:
(1) You say that NULL is shown instead of the VARCHAR value. What's your
query? I'd guess that there is some sort of problem with the replace
and how you use it. Otherwise, there shouldn't be a NULL.
(2) Once you get a non-NULL, compare the code points of 'Č' and the
respective character in your VARCHAR value. It should be the same if
you create the correct character on DB2 side.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 1 '06 #13

P: n/a
Knut Stolze wrote:
Gregor Kovač wrote:
>>Can your tool and the shell actually show the 'Č' correctly (independent
of
DB2)? If not, then you probably have a misconfiguration in your
environment that should be fixed. Have you tried a Java application
since its Unicode support may get rid of this for you all right?

The tool I'm using is written in Java and I can see Č as I should. only
when I do the replace on a VARCHAR with Č characters in I don't get
anything back from DB2 (the tool shows (null). (null) is shown for every
column that has a NULL value).

Well, at least we know that your tool can handle the 'Č'.

Now we have two questions here:
(1) You say that NULL is shown instead of the VARCHAR value. What's your
query? I'd guess that there is some sort of problem with the replace
and how you use it. Otherwise, there shouldn't be a NULL.
(2) Once you get a non-NULL, compare the code points of 'Č' and the
respective character in your VARCHAR value. It should be the same if
you create the correct character on DB2 side.
As an example you can use this query:
VALUES(CASE WHEN 'č' = 'č' THEN CHR(129) ELSE 'č' END) should return
CHR(129) (u with umlaut), but it dows not return anything:
$ db2 "values(case when 'č' = 'č' then chr(129) else 'č' end)"

1
--
1 record(s) selected.
On the other hand, query
VALUES(CASE WHEN 'č' = 'Č' THEN CHR(129) ELSE 'č' END) does the following:
$ db2 "values(case when 'č' = 'Č' then chr(129) else 'č' end)"

1
--
č

1 record(s) selected.
Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Nov 2 '06 #14

P: n/a
Gregor Kovač wrote:
Knut Stolze wrote:
>Now we have two questions here:
(1) You say that NULL is shown instead of the VARCHAR value. What's your
query? I'd guess that there is some sort of problem with the replace
and how you use it. Otherwise, there shouldn't be a NULL.
(2) Once you get a non-NULL, compare the code points of 'Č' and the
respective character in your VARCHAR value. It should be the same if
you create the correct character on DB2 side.
As an example you can use this query:
VALUES(CASE WHEN 'č' = 'č' THEN CHR(129) ELSE 'č' END) should return
CHR(129) (u with umlaut), but it dows not return anything:
$ db2 "values(case when 'č' = 'č' then chr(129) else 'č' end)"

1
--
1 record(s) selected.
Not very surprising. You get the same result if you run "VALUES CHR(129)"
directly. CHR(129) is not a printable character. So your query returns a
single row with one value. The length of that value is 1. Nevertheless,
your shell won't print this character - neither does mine.
On the other hand, query
VALUES(CASE WHEN 'č' = 'Č' THEN CHR(129) ELSE 'č' END) does the following:
$ db2 "values(case when 'č' = 'Č' then chr(129) else 'č' end)"

1
--
č

1 record(s) selected.
This is not surprising either. The comparison evaluates to FALSE, so that
your query is equivalent to "VALUES 'č'". The length of that is 2. This
tells you that 'č' is a multi-byte character comprised of the two bytes
0xC4 and 0x8D (in UTF-8).

My suggestion would be that you do not rely on anything that does not belong
to the ASCII character set, i.e. anything above the code point 127, or that
you stick with multi-bytes if you have characters that are not part of
ASCII. Otherwise, you will probably run into a lot of troubles on
different platforms and environments.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 2 '06 #15

P: n/a
Knut Stolze wrote:
Gregor Kovač wrote:
>Knut Stolze wrote:
>>Now we have two questions here:
(1) You say that NULL is shown instead of the VARCHAR value. What's
your
query? I'd guess that there is some sort of problem with the
replace
and how you use it. Otherwise, there shouldn't be a NULL.
(2) Once you get a non-NULL, compare the code points of 'Č' and the
respective character in your VARCHAR value. It should be the same
if you create the correct character on DB2 side.
As an example you can use this query:
VALUES(CASE WHEN 'č' = 'č' THEN CHR(129) ELSE 'č' END) should return
CHR(129) (u with umlaut), but it dows not return anything:
$ db2 "values(case when 'č' = 'č' then chr(129) else 'č' end)"

1
--
1 record(s) selected.

Not very surprising. You get the same result if you run "VALUES CHR(129)"
directly. CHR(129) is not a printable character. So your query returns a
single row with one value. The length of that value is 1. Nevertheless,
your shell won't print this character - neither does mine.
>On the other hand, query
VALUES(CASE WHEN 'č' = 'Č' THEN CHR(129) ELSE 'č' END) does the
following: $ db2 "values(case when 'č' = 'Č' then chr(129) else 'č' end)"

1
--
č

1 record(s) selected.

This is not surprising either. The comparison evaluates to FALSE, so that
your query is equivalent to "VALUES 'č'". The length of that is 2. This
tells you that 'č' is a multi-byte character comprised of the two bytes
0xC4 and 0x8D (in UTF-8).

My suggestion would be that you do not rely on anything that does not
belong to the ASCII character set, i.e. anything above the code point 127,
or that you stick with multi-bytes if you have characters that are not
part of
ASCII. Otherwise, you will probably run into a lot of troubles on
different platforms and environments.
Hi... Thanks for the in.depth explaination. But... :)
The thing is that I do need those chars above 127.
We have a scale that contains PLU codes for products (you know when yo go to
shop and you buy apples, you put them on a scale, press 45 for example and
out comes a sticker with the bar code, name of the product, .... This bar
code is then scanned by the cashier when you pay.) For the scale, if we
want it to properly display our characters (like č in the above example) we
have to translate our characters into couple of characters that are above
ASCII 127. We could translate our characters into the ones without the
caron, but that is not pretty. :))

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Nov 2 '06 #16

P: n/a
Gregor Kovač wrote:
The thing is that I do need those chars above 127.
We have a scale that contains PLU codes for products (you know when yo go
to shop and you buy apples, you put them on a scale, press 45 for example
and out comes a sticker with the bar code, name of the product, .... This
bar code is then scanned by the cashier when you pay.) For the scale, if
we want it to properly display our characters (like č in the above
example) we have to translate our characters into couple of characters
that are above ASCII 127. We could translate our characters into the ones
without the caron, but that is not pretty. :))
If I got this right, then the situation is
(a) your shell cannot display the CHR(129) correctly,
(b) but the scale (or whichever tool may use this in the end) can handle it?

Then I believe you will need to set up some environment equivalent to the
scale, i.e. one that can properly show the 'č' and similar characters. In
particular, it has to support the exact same character set as the scale.
Then you should also see the 'č' in the output because it would not be
interpreted as non-printable character.

That's pretty much the same as if you would query DB2 on LUW and have EBCDIC
at the client - w/o proper conversion in between. The client gets some
data that it can't correctly handle because the code points in ASCII and
EBCDIC are not exactly identical.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 2 '06 #17

P: n/a
Knut Stolze wrote:
Gregor Kovač wrote:
>The thing is that I do need those chars above 127.
We have a scale that contains PLU codes for products (you know when yo go
to shop and you buy apples, you put them on a scale, press 45 for example
and out comes a sticker with the bar code, name of the product, .... This
bar code is then scanned by the cashier when you pay.) For the scale, if
we want it to properly display our characters (like č in the above
example) we have to translate our characters into couple of characters
that are above ASCII 127. We could translate our characters into the ones
without the caron, but that is not pretty. :))

If I got this right, then the situation is
(a) your shell cannot display the CHR(129) correctly,
(b) but the scale (or whichever tool may use this in the end) can handle
it?

Then I believe you will need to set up some environment equivalent to the
scale, i.e. one that can properly show the 'č' and similar characters. In
particular, it has to support the exact same character set as the scale.
Then you should also see the 'č' in the output because it would not be
interpreted as non-printable character.

That's pretty much the same as if you would query DB2 on LUW and have
EBCDIC
at the client - w/o proper conversion in between. The client gets some
data that it can't correctly handle because the code points in ASCII and
EBCDIC are not exactly identical.
You are right.
I was thinking of replacing č with something like ${c} and then feeding the
output through a Java program and replace ${c} with CHR(219).

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Nov 2 '06 #18

This discussion thread is closed

Replies have been disabled for this discussion.