Collating Sequence 
Author Message
 Collating Sequence

My windows-based (NT) application does not work properly when adding it's
database to the AS400
because it uses SQL statements like:
SELECT FROM table WHERE columnName > '0' (<--zero)
AND columnName < 'z'
This will never return records because '0' > 'z' in the EBCDIC collating
sequence
This is an unfortunate problem that exists for months now, despite the
effort of many people.
We have come nearby the solution however. Someone pointed out (thank you
Aad) that the AS400 has the capability to use Alternative Collating Sequence
Tables. With the ALTSEQ keyword one can define what table to use, you can
even create your own altseq table, but it works only inside AS400: The above
SQL statement indeed returns the records when typed in on the AS400 console.
With Remote access however (my application) the EBCDIC collating sequence
stays in use, even after many related things we tried like specifying the
alternative table as a job attribute using a program.
It is an open IBM case from July
We still feel that the purpose of the altseq stuff in the AS400 reflects my
problem but time flies .
Now i also look for alternative ways to avoid copied tables on both systems
that may not be
consistent.
By the way, the application cannot be changed, I do not have the sources.
I do very much appreciate your help
Best regards
John

Quote:

> Hello,

> Is it possible to create views in DB2 on tables that reside on an other
> system using nicknames?

> Basically I want a windows-application to use some tables of an AS400-db2
> database
> Adding the applications tables to the AS400 database appears to be no
option
> because of collating sequence differences due to ASCII-standard (used in
NT)
> and EBCDIC used in AS400.

> Any other ideas or suggestions are very welcome.

> On NT runs UDB 6.1 and DB2connect both Enterprise edition
> To give it a try I seem to need UDB 6.1 Extended Enterprise edition (to
> create nicknames)
> Can trial software be downloaded somewhere?

Can you elaborate on the collating system differences problem?

--

Karl Hanson



Mon, 24 Feb 2003 17:46:26 GMT
 Collating Sequence

Not sure where you're specifying the ALTSEQ keyword, but Sort Sequence is the
SQL attribute that also controls this on the AS/400.

Looks like you're using DB2 Connect to access the AS/400 - does it have some
type of sort setting for the AS/400 connection.  The Client Access AS/400 ODBC
driver has this type of setting so that the user-specified sort sequence is
applied to database access.  

--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, AS/400

(opinions stated are not necessarily those of my employer)



Fri, 28 Feb 2003 22:32:21 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. How to use client's collating sequence

2. Impact of collating sequence on queries?

3. Error "incorrect collating sequence"

4. Incorrect Collating Sequence - SQL2000 DTS Import of Paradox 4 (International)

5. Selected Collating Sequence... Error 3228

6. collating sequence not supported

7. Runtime error '3228': Selected collating sequence not supported by the operating system

8. Collating Sequence not supported by OS

9. Incorrect collating sequence in Paradox

10. Selected collating sequence not supported... error

11. Runtime error '3228': Selected collating sequence not supported by the operating system

12. Selecting collating sequence .....


 
Powered by phpBB® Forum Software