Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Unanswered: COL display in CHAR instead of in HEX

    I am doing huge delete from a table and it takes long time and huge logfilsiz to be configured. I am using differnet approach by only keeping useful data in new table TABLE1 and DROP the OLD table. When I SELECT * of 2 tables, 2 columns looks different. Any Idea?


    existing table - OLD

    A CHARACTER(1)
    B CHARACTER(1)


    CREATE TABLE TABLE1 (
    X CHARACTER(1) NOT NULL,
    Y CHARACTER(1) NOT NULL);


    INSERT INTO TABLE1
    SELECT A, B FROM OLD;


    SELECT * FROM OLD;

    A B
    ------- --------
    x'4E' x'4F'
    x'4E' x'4F'
    x'4E' x'4F'
    x'4E' x'4F'


    SELECT * FROM TABLE1;

    X Y
    ------- --------
    N O
    N O
    N O
    N O


    It is HEX format in OLD, How can I store the same format in TABLE1?
    I have tried SELECT HEX(A), HEX(B) FROM OLD. It returned new TABLE1 not have sufficient length.

    You comment is appreciated.
    Last edited by ekmsit; 08-01-12 at 13:45.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ekmsit View Post
    It is HEX format in OLD
    There can be no "format" when data are stored in a table. Formatting happens in the client.

    Depending on your DB2 platform, which for some unknown reason you chose not to tell us, I'd venture to say that the columns in the old table may be defined as FOR BIT DATA or with the CCSID that DB2 does not translate.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2012
    Posts
    4
    Thanks n_i for the reply.

    Just figured out Db server using is DB2/NT 8.1.3. , Using Command Center. I listed the OLD table info on screen and is showing Column name - A,B | Datatype - CHARACTER | Length -1 | Nullable - No.

    Was it the right way to define X,Y in the TABLE1 table. I tested front end with the new TABLE1 and it seems like it is working fine. Any concern?

    Your comment is appreciated.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    select colname, typename, codepage
    from syscat.columns
    where tabschema = 'SCHEMA1' and tabname = 'TABLE1'
    Replace the schema and table names as needed. Post the output.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2012
    Posts
    4
    Here it is.

    OLD TABLE
    colname typename codepage
    -------- --------- ---------
    A CHARACTER 0
    B CHARACTER 0


    TABLE1 TABLE (NEWLY CREATED)
    colname typename codepage
    -------- --------- ---------
    X CHARACTER 1252
    Y CHARACTER 1252

  6. #6
    Join Date
    Aug 2012
    Posts
    4
    This lead me try to use

    CREATE TABLE TABLE1 CODEPAGE = 0 (Fieldname filedtype goes here...)

    ... see syntax below,(I got it online)

    However it returned me ...

    SQL0104N An unexpected token "CODEPAGE=0" was found following "TABLE TABLE1". Expected token may include: "<space> ".

    Explanation:
    A syntax error in the SQL statment was detected at the specified token following the text "<text>". The "<text>" field indicates the 20 characters of the SQL statment that preceded the token that is not valid.


    CREATE TABLE | DBF Table_Name
    [CODEPAGE = nCodePage]
    (FieldName1 FieldType [( nFieldWidth [, nPrecision] )] [NULL | NOT NULL]
    [AUTOINC [NEXTVALUE NextValue [STEP StepValue]]]
    [, FieldName2 ... ] [ ... ] )


    Your comment is greatly appreciated.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ekmsit View Post

    OLD TABLE
    colname typename codepage
    -------- --------- ---------
    A CHARACTER 0
    B CHARACTER 0
    Just as I thought, these columns are defined as CHAR(1) FOR BIT DATA.

    Quote Originally Posted by ekmsit
    see syntax below,(I got it online)
    Not everything found online is worth trying. I suggest you use documentation for the software you're trying to use, DB2 LUW v8. It can be easily found by searching for "DB2 LUW 8 documentation" on Google.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •