Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Unanswered: DB2 Express (Windows) - Set sort order to EBCDIC

    Hi,

    our existing DB2 database is running on a host machine and returns sorted data using ebcdic (IBM-273).

    We're trying to set up a backup database (DB2 Express 9) on a windows box.
    When creating a database with the assistant the sort order options available in the Region tab are System, Identity and Compatibility. Regardless of the selected option and codepage (IBM-1252/UTF-8) the returned rows are sorted using ascii.

    As our etl-tool depends on the host sort order we can't switch to ascii sort.

    Is there any chance to set up the backup db to return the data with the sort order ebcdic?

    One solution seems to be specifying a user defined collation order though we don't know where to configure that.


    Thanks a lot, nr
    Last edited by nimaro; 07-30-07 at 08:36.

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    with create database command, there is an option for collating sequence and can not be changed after this create db command.
    there is no option for collating sequence ebcdic
    .-SYSTEM---------. |
    COLLATE USING--+-COMPATIBILITY--+-'
    +-IDENTITY-------+
    +-IDENTITY_16BIT-+
    +-UCA400_NO------+
    +-UCA400_LTH-----+
    '-NLSCHAR--------'
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jul 2007
    Posts
    1
    Hi nimaro,

    follow this link:
    http://publib.boulder.ibm.com/infoce...c/c0007183.htm

    Hope it might help u with ur sorting problem.

    Greetz
    Monkil

  4. #4
    Join Date
    Jul 2007
    Posts
    3
    Thanks for your help, guys.

    Based on the aforementioned link the only solution to emulate EBCDIC sort on a windows DB2 database seems to be using the sqlecrea-API. There you can create a database with the pDbDescriptor parameter, that refers to a custom collating table. That would be IBM1252->IBM273 in our case.
    Last edited by nimaro; 08-01-07 at 11:56.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What I would consider in your case is to write a UDF that converts your string to the target code page (some EBCDIC cp), and then you use this function in the ORDER BY clause:
    Code:
    SELECT ...
    FROM   ...
    WHERE  ...
    ORDER BY str2ebcdic(text)
    The function can return the first 100 or 1000 (or whichever length you need) bytes using the VARCHAR FOR BIT DATA data type. Then you won't run into any problems due to other collation sequences used by DB2; I'd have a binary comparison of the ebcdic strings.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jul 2007
    Posts
    3
    That would be a much better solution indeed. The problem here is our etl-tool. We've quite a number (many hundreds) of processes, each one of them consisting of up to 100 sql-queries. It would a one hell-of-a-job to change every query. On top of that, you can't apply built-in functions of the etl-tool on an edited query anymore.

    Slowly but surely that windows backup DB2 seems to be all but impossible.

  7. #7
    Join Date
    Dec 2010
    Location
    Mortara - Italy
    Posts
    1

    Smile DB2 LUW (Linux ) - Set sort order to EBCDIC

    Hi Knut,

    I'd like this solution and I want use this for to access to remote federated DB ( db2 9,? linux ) via cobol program on z/os db2 v9 z/os.

    Because I can install db2 luw in Unicode or ascii with standard sort order and retrieve z/os record in cobol program with right order.

    Can I use this function indeed?

    select *
    from t1
    ORDER BY COLLATION_KEY_BIT ( 1 , ‘EBCIDIC’ )
    , COLLATION_KEY_BIT ( 3 , ‘EBCIDIC’ ) ……


    It is better write the statement like this?


    select COLLATION_KEY_BIT ( c1 , ‘EBCIDIC’ ) as c1
    , COLLATION_KEY_BIT ( c3 , ‘EBCIDIC’ ) c3
    , n1 as number
    from t1

    order by 1 , 2

    In this case is the ascii/ebcdic conversion avoided on Host?

    thank you very much :-)

    Giorgio Salvatori
    CRM application Department
    SGS-BP - Italy
    giorgiocelso.salvatori@sgsbp.it

    Quote Originally Posted by stolze View Post
    What I would consider in your case is to write a UDF that converts your string to the target code page (some EBCDIC cp), and then you use this function in the ORDER BY clause:
    Code:
    SELECT ...
    FROM   ...
    WHERE  ...
    ORDER BY str2ebcdic(text)
    The function can return the first 100 or 1000 (or whichever length you need) bytes using the VARCHAR FOR BIT DATA data type. Then you won't run into any problems due to other collation sequences used by DB2; I'd have a binary comparison of the ebcdic strings.

Posting Permissions

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