DB2 and extended ASCII table
in [DB2]
|
Prev: Result set from ADMIN_CMD
Next: SQL error 805
From: Gregor Kova? on 28 Oct 2006 03:30 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. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
From: Serge Rielau on 28 Oct 2006 10:59 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/ondemandbusiness/conf2006/
From: Gregor Kova? on 28 Oct 2006 13:24 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. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
From: Serge Rielau on 29 Oct 2006 06:01 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/ondemandbusiness/conf2006/
From: Gregor Kova? on 30 Oct 2006 03:35
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. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~- |