DB2 and extended ASCII table
in [DB2]
|
Prev: Result set from ADMIN_CMD
Next: SQL error 805
From: Knut Stolze on 30 Oct 2006 04:06 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
From: Gregor Kova? on 30 Oct 2006 05:53 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. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
From: Knut Stolze on 1 Nov 2006 02:58 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
From: Gregor Kova? on 2 Nov 2006 03:39 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. | -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
From: Knut Stolze on 2 Nov 2006 03:49
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 |