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

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--------'

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

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.

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:
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.

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.

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