Topic
  • 10 replies
  • Latest Post - ‏2014-06-17T02:53:12Z by SamSamHK
keerthi.m
keerthi.m
9 Posts

Pinned topic How to configure DB2 for DBCS ?

‏2008-05-01T05:56:29Z |
We use Windows XP. We have a requirement to store Japanese character data in DB2 9.1+. For storing Japanese character data in DB2, we find that we need to use DBCS(Double Byte Character Set) and need to configure DB2 for DBCS (Is my understanding correct ?). But I dont know how exactly to configure DBCS in DB2. Can anyone please illustrate how to do this and point me to any good article/tutorial that explains about configuring DB2 with DBCS.

Thank you.

Message was edited by: keerthi.m
Updated on 2008-05-22T21:03:31Z at 2008-05-22T21:03:31Z by doole
  • Saurabh.Jain
    Saurabh.Jain
    164 Posts

    Re: How to configure DB2 for DBCS ?

    ‏2008-05-01T13:21:51Z  
    What you need is a Unicode (UTF-8) database.

    -Saurabh
  • doole
    doole
    208 Posts

    Re: How to configure DB2 for DBCS ?

    ‏2008-05-01T14:38:08Z  
    Setting up the database for DBCS or Unicode is easy. When you create the database, you simply need to specify the code set and territory. For example, to create a Unicode database with the territory set to Japan, you could issue:

    db2 create database jp_db using codeset utf-8 territory JA

    Personally, I'd use a Unicode database over a DBCS database. Unicode is the standard for international data and gives you flexibility if you ever need to use another language in the database.
    As of 9.5 DB2 LUW will default to Unicode for new databases.

    If you still want to use a DBCS database, you can find the complete list of supported code sets and territories here: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.nls.doc/doc/r0004565.html

    Scroll down the page to table 29 to find the supported code sets for Japan. (The standard Windows code set for Japanese is IBM-943.)
    <hr />
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Labs
  • keerthi.m
    keerthi.m
    9 Posts

    Re: How to configure DB2 for DBCS ?

    ‏2008-05-01T19:25:56Z  
    • doole
    • ‏2008-05-01T14:38:08Z
    Setting up the database for DBCS or Unicode is easy. When you create the database, you simply need to specify the code set and territory. For example, to create a Unicode database with the territory set to Japan, you could issue:

    db2 create database jp_db using codeset utf-8 territory JA

    Personally, I'd use a Unicode database over a DBCS database. Unicode is the standard for international data and gives you flexibility if you ever need to use another language in the database.
    As of 9.5 DB2 LUW will default to Unicode for new databases.

    If you still want to use a DBCS database, you can find the complete list of supported code sets and territories here: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.nls.doc/doc/r0004565.html

    Scroll down the page to table 29 to find the supported code sets for Japan. (The standard Windows code set for Japanese is IBM-943.)
    <hr />
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Labs
    Thank you all for the prompt responses.

    I changed the Regional Settings in my Windows Control Panel and used Japanese keyboard for inserting Japanese characters into the newly created UTF8 database by executing below commands in Command Editor:

    1. create database jp_db using codeset utf-8 territory JA
    2. create table jptbl (name varchar(20))
    3. insert into jptbl values 'お'

    When I executed the above 3rd command I get error message saying "DB21007E End of file reached while reading the command."

    Can you please explain what am I doing wrong here ?
  • Saurabh.Jain
    Saurabh.Jain
    164 Posts

    Re: How to configure DB2 for DBCS ?

    ‏2008-05-02T00:55:03Z  
    • keerthi.m
    • ‏2008-05-01T19:25:56Z
    Thank you all for the prompt responses.

    I changed the Regional Settings in my Windows Control Panel and used Japanese keyboard for inserting Japanese characters into the newly created UTF8 database by executing below commands in Command Editor:

    1. create database jp_db using codeset utf-8 territory JA
    2. create table jptbl (name varchar(20))
    3. insert into jptbl values 'お'

    When I executed the above 3rd command I get error message saying "DB21007E End of file reached while reading the command."

    Can you please explain what am I doing wrong here ?
    You might need to put a semicolon after each statement. (in the command editor, it shows the current termination character at the bottom of the window)

    Saurabh

    Message was edited by: Saurabh.Jain
    Updated on 2008-05-02T00:55:03Z at 2008-05-02T00:55:03Z by Saurabh.Jain
  • keerthi.m
    keerthi.m
    9 Posts

    Re: How to configure DB2 for DBCS ?

    ‏2008-05-02T10:47:36Z  
    • doole
    • ‏2008-05-01T14:38:08Z
    Setting up the database for DBCS or Unicode is easy. When you create the database, you simply need to specify the code set and territory. For example, to create a Unicode database with the territory set to Japan, you could issue:

    db2 create database jp_db using codeset utf-8 territory JA

    Personally, I'd use a Unicode database over a DBCS database. Unicode is the standard for international data and gives you flexibility if you ever need to use another language in the database.
    As of 9.5 DB2 LUW will default to Unicode for new databases.

    If you still want to use a DBCS database, you can find the complete list of supported code sets and territories here: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.nls.doc/doc/r0004565.html

    Scroll down the page to table 29 to find the supported code sets for Japan. (The standard Windows code set for Japanese is IBM-943.)
    <hr />
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Labs
    Is it true that UTF-8 codeset does not store all the Japanese characters. Is it true? If so then do I need to use DBCS and configure DBCS with DB2 instead of configuring my DB2 database for UTF-8 ?
  • doole
    doole
    208 Posts

    Re: How to configure DB2 for DBCS ?

    ‏2008-05-05T20:02:11Z  
    • keerthi.m
    • ‏2008-05-02T10:47:36Z
    Is it true that UTF-8 codeset does not store all the Japanese characters. Is it true? If so then do I need to use DBCS and configure DBCS with DB2 instead of configuring my DB2 database for UTF-8 ?
    I don't know of any Japanese characters that do not have a UTF-8 representation. (Japanese is such a major language, I really can't imagine the Unicode consortium leaving a hole like that.)

    <hr />
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Labs
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: How to configure DB2 for DBCS ?

    ‏2008-05-20T19:27:54Z  
    • keerthi.m
    • ‏2008-05-01T19:25:56Z
    Thank you all for the prompt responses.

    I changed the Regional Settings in my Windows Control Panel and used Japanese keyboard for inserting Japanese characters into the newly created UTF8 database by executing below commands in Command Editor:

    1. create database jp_db using codeset utf-8 territory JA
    2. create table jptbl (name varchar(20))
    3. insert into jptbl values 'お'

    When I executed the above 3rd command I get error message saying "DB21007E End of file reached while reading the command."

    Can you please explain what am I doing wrong here ?
    Were you able to resolve this issue? I find i am unable to INSERT or UPDATE tables in my UTF-8 -based database when the value is お or some such character.

    Does anyone have any insight as to why and what needs to be done to enable it? I'm using DB2 9.1. I don't think the issue is the termination character.

    thanks.
  • keerthi.m
    keerthi.m
    9 Posts

    Re: How to configure DB2 for DBCS ?

    ‏2008-05-21T08:49:43Z  
    Were you able to resolve this issue? I find i am unable to INSERT or UPDATE tables in my UTF-8 -based database when the value is お or some such character.

    Does anyone have any insight as to why and what needs to be done to enable it? I'm using DB2 9.1. I don't think the issue is the termination character.

    thanks.
    I tried to input the Japanese characters thro' the command center and it worked.
  • doole
    doole
    208 Posts

    Re: How to configure DB2 for DBCS ?

    ‏2008-05-22T21:03:31Z  
    Were you able to resolve this issue? I find i am unable to INSERT or UPDATE tables in my UTF-8 -based database when the value is お or some such character.

    Does anyone have any insight as to why and what needs to be done to enable it? I'm using DB2 9.1. I don't think the issue is the termination character.

    thanks.
    What error are you getting on your failed INSERT/IPDATE (please provide the SQLCODE and full message text). How are you issuing the statements?

    (My hunch is that whatever command interpreter you are using is getting confused by the Japanese characters, and DB2 is seeing a garbled statement.)

    <hr />
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Labs
  • SamSamHK
    SamSamHK
    1 Post

    Re: How to configure DB2 for DBCS ?

    ‏2014-06-17T02:53:12Z  
    • doole
    • ‏2008-05-05T20:02:11Z
    I don't know of any Japanese characters that do not have a UTF-8 representation. (Japanese is such a major language, I really can't imagine the Unicode consortium leaving a hole like that.)

    <hr />
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Labs

    It is quite embarrassing when trying IBM tools from old days control center or even command prompt with no luck.

    I have faced similar problem when creating a view with UTF-8 Chinese Characters.  Then, I have used Toad for DB2 to create the view successfully.  You can make a trial. (using Toad for IBM DB2 5.6.0.2916)


    Samuel Sum

    Independent Consultant, specialized in BI, DW, Big Data in Hong Kong