DB2 and extended ASCII table

[Gregor Kovač]

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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

Re: DB2 and extended ASCII table

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/ondemandbusiness/conf2006/

[Serge Rielau]

Re: DB2 and extended ASCII table

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/ondemandbusiness/conf2006/

[Gregor Kovač]

Re: DB2 and extended ASCII table

Serge Rielau wrote:

Serge Rielau wrote:

>Gregor Kovac( wrote:

>>Hi!
>>>
>>Does DB2 handle extended ASCII table?
>>Example:
>>VALUES(CHR(65 )) =A
>>VALUES(CHR(12 9)) =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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

Re: DB2 and extended ASCII table

Gregor Kovač wrote:

Serge Rielau wrote:
>

>Serge Rielau wrote:

>>Gregor Kovac( wrote:
>>>Hi!
>>>>
>>>Does DB2 handle extended ASCII table?
>>>Example:
>>>VALUES(CHR(6 5)) =A
>>>VALUES(CHR(1 29)) =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/ondemandbusiness/conf2006/

[Gregor Kovač]

Re: DB2 and extended ASCII table

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(CHA R 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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

Re: DB2 and extended ASCII table

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
>>>>character s 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(CHA R 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/ondemandbusiness/conf2006/

[Gregor Kovač]

Re: DB2 and extended ASCII table

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(CH R(65)) =A
>>>>>>VALUES(CH R(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(CHA R 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(CHA R 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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Serge Rielau]

Re: DB2 and extended ASCII table

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?
>>>>>>>Exampl e:
>>>>>>>VALUES(C HR(65)) =A
>>>>>>>VALUES(C HR(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(CHA R 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(CHA R 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/ondemandbusiness/conf2006/

[Gregor Kovač]

Re: DB2 and extended ASCII table

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
>>>>>>>>shoul d 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(CHA R 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(CHA R 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=429 28
>>
>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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Knut Stolze]

Re: DB2 and extended ASCII table

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:
>>>>>>>>Grego r Kovac( wrote:
>>>>>>>>>Hi!
>>>>>>>>>>
>>>>>>>>>Does DB2 handle extended ASCII table?
>>>>>>>>>Exampl e:
>>>>>>>>>VALUES (CHR(65)) =A
>>>>>>>>>VALUES (CHR(129)) =null, but according to www.asciitable.com
>>>>>>>>>shou ld 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.
>>>>>>>>>
>>>>>>>>Anywa y, the easiest workaround is probably to imply write a
>>>>>>>>trivi al 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(CHA R 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(CHA R 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 misconfiguratio n 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=4292 8
>>>
>>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

[Gregor Kovač]

Re: DB2 and extended ASCII table

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:
>>>>>>>>>Greg or Kovac( wrote:
>>>>>>>>>>Hi!
>>>>>>>>>>>
>>>>>>>>>>Doe s DB2 handle extended ASCII table?
>>>>>>>>>>Examp le:
>>>>>>>>>>VALUE S(CHR(65)) =A
>>>>>>>>>>VALUE S(CHR(129)) =null, but according to www.asciitable.com
>>>>>>>>>>shoul d 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
>>>>>>>>>trivia l UDF in C/Java/CLR which does the job.
>>>>>>>>Of course a big case expression will also work ;-)
>>>>>>>>>
>>>>>>>>Cheer s
>>>>>>>>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(CHA R 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(CHA R 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 misconfiguratio n 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=429 28
>>>>
>>>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. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Knut Stolze]

Re: DB2 and extended ASCII table

Gregor Kovač wrote:

>Can your tool and the shell actually show the 'Č' correctly (independent
>of
>DB2)? If not, then you probably have a misconfiguratio n 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

[Gregor Kovač]

Re: DB2 and extended ASCII table

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 misconfiguratio n 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(cas e 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(cas e when 'č' = 'Č' then chr(129) else 'č' end)"

1
--
č

1 record(s) selected.


Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[Knut Stolze]

Re: DB2 and extended ASCII table

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(cas e 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(cas e 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