Sorting primary key

[Joost Kraaijeveld]

Hi all,

Is it possible to create a sorted primary key in DB/2 and if so, what is the
SQL script syntax for that?

I need it to convert a Topspeed (Clarion) database into DB/2: they use
sorted primary keys. I have written a tool that imports the Clarion
dictionary and makes it DB/2 compatible. It also generates a SQL script to
recreate the databse. I also have writen a copy-database program to copy the
date through ODBC from Topspeed to DB/2).

Anyone interested?

Groeten

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@A skesis.nl
web: www.askesis.nl

[Blair Adamache]

Re: Sorting primary key

You need to create a clustered unique index, and base your primary key
on this. However, nothing in a relational database is truly 100% sorted
until you use an ORDER BY clause in a SELECT statement.

Joost Kraaijeveld wrote:
[color=blue]
> Hi all,
>
> Is it possible to create a sorted primary key in DB/2 and if so, what is the
> SQL script syntax for that?
>
> I need it to convert a Topspeed (Clarion) database into DB/2: they use
> sorted primary keys. I have written a tool that imports the Clarion
> dictionary and makes it DB/2 compatible. It also generates a SQL script to
> recreate the databse. I also have writen a copy-database program to copy the
> date through ODBC from Topspeed to DB/2).
>
> Anyone interested?
>
> Groeten
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@A skesis.nl
> web: www.askesis.nl
>
>[/color]

[Mark A]

Re: Sorting primary key

"Joost Kraaijeveld" <J.Kraaijeveld@ Askesis.nl> wrote in message
news:x5gGc.1940 7$qK.4904@amsne ws02.chello.com ...[color=blue]
> Hi all,
>
> Is it possible to create a sorted primary key in DB/2 and if so, what is[/color]
the[color=blue]
> SQL script syntax for that?
>
> I need it to convert a Topspeed (Clarion) database into DB/2: they use
> sorted primary keys. I have written a tool that imports the Clarion
> dictionary and makes it DB/2 compatible. It also generates a SQL script to
> recreate the databse. I also have writen a copy-database program to copy[/color]
the[color=blue]
> date through ODBC from Topspeed to DB/2).
>
> Anyone interested?
>
> Groeten
>
> Joost Kraaijeveld[/color]

Are you talking about IBM's DB2 (not DB/2)? Occasionally we get some Dbase
questions here.

If so, just specify ORDER BY in the select clause when you want the result
set returned in a particular order. Performance may be improved if a primary
key is defined on the table (or a unique index) and the index is altered to
be the clustered index (see alter index). Reorg the table and indexes as
appropriate for inserts, updates, and deletes on the table.

[Joost Kraaijeveld]

Re: Sorting primary key

Hi Blair,

"Blair Adamache" <badamache@2muc hspam.yahoo.com > wrote in message
news:ccc42g$gd3 $1@hanover.toro lab.ibm.com...[color=blue]
> You need to create a clustered unique index, and base your primary key
> on this.[/color]
And how do you do that with DB2 7.2 Linux/W2K (in terms of "Alter table" or
"Create index")?
[color=blue]
> However, nothing in a relational database is truly 100% sorted
> until you use an ORDER BY clause in a SELECT statement.[/color]
As far as I am concerned / told that is the problem of Clarion. But han
again, that's what I thought of the whole conversion ;-)

Thanks,

Joost

[Joost Kraaijeveld]

Re: Sorting primary key

Hi Mark,

"Mark A" <nobody@nowhere .com> wrote in message
news:BfhGc.29$R x.21261@news.us west.net...[color=blue]
> Are you talking about IBM's DB2 (not DB/2)? Occasionally we get some Dbase
> questions here.[/color]

Yep. DB2 7.1 UDB (I am from the OS/2 time ;-), hence DB/2 ).
[color=blue]
> If so, just specify ORDER BY in the select clause when you want the result
> set returned in a particular order.[/color]

I am looking for th equivalent of
"CREATE INDEX indexname on tableanme (columnname DESC)"

which I could not find in the docs BTW so I expect the answer to be "you
can't"


Bye

Joost

[Blair Adamache]

Re: Sorting primary key

Version 7 CREATE INDEX syntax is in the SQL Reference manual:

http://www-306.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/document.d2w/report?fn=db2v7s0sqls0618.htm#HDRCINDX

Joost Kraaijeveld wrote:
[color=blue]
> Hi Blair,
>
> "Blair Adamache" <badamache@2muc hspam.yahoo.com > wrote in message
> news:ccc42g$gd3 $1@hanover.toro lab.ibm.com...
>[color=green]
>>You need to create a clustered unique index, and base your primary key
>>on this.[/color]
>
> And how do you do that with DB2 7.2 Linux/W2K (in terms of "Alter table" or
> "Create index")?
>
>[color=green]
>>However, nothing in a relational database is truly 100% sorted
>>until you use an ORDER BY clause in a SELECT statement.[/color]
>
> As far as I am concerned / told that is the problem of Clarion. But han
> again, that's what I thought of the whole conversion ;-)
>
> Thanks,
>
> Joost
>
>[/color]

[Mark A]

Re: Sorting primary key

> > Are you talking about IBM's DB2 (not DB/2)? Occasionally we get some
Dbase[color=blue][color=green]
> > questions here.[/color]
>
> Yep. DB2 7.1 UDB (I am from the OS/2 time ;-), hence DB/2 ).
>[/color]

Many years ago, there was DB2/2 (for OS/2) but never DB/2.
[color=blue][color=green]
> > If so, just specify ORDER BY in the select clause when you want the[/color][/color]
result[color=blue][color=green]
> > set returned in a particular order.[/color]
>
> I am looking for th equivalent of
> "CREATE INDEX indexname on tableanme (columnname DESC)"
>
> which I could not find in the docs BTW so I expect the answer to be "you
> can't"
>[/color]
You can download all the manuals here in PDF format (including the SQL
Reference):

http://www-306.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7pubs.d2w/en_main

Here is an example:

CREATE UNIQUE INDEX index-name
ON PROJECT(PROJNAM E) CLUSTER

You can use the DESC option if you will have ORDER BY statements in
decending sequence. The UNIQUE and CLUSTER parms are optional.

[Knut Stolze]

Re: Sorting primary key

Joost Kraaijeveld wrote:
[color=blue]
> Hi all,
>
> Is it possible to create a sorted primary key in DB/2 and if so, what is
> the SQL script syntax for that?
>
> I need it to convert a Topspeed (Clarion) database into DB/2: they use
> sorted primary keys. I have written a tool that imports the Clarion
> dictionary and makes it DB/2 compatible. It also generates a SQL script to
> recreate the databse. I also have writen a copy-database program to copy
> the date through ODBC from Topspeed to DB/2).[/color]

What do you really want to do with the "sorted" primary keys?

DB2 uses B-Trees internally to manage indexes. And as you know, B-Trees
store the data ordered. That sounds to me as if you already have
implicitly what you want.

Besides, when you look at the syntax of the CREATE INDEX statement, you will
see:

CREATE INDEX ... ON ... ( column ASC/DESC )

So you can determine whether the index keys should be sorted ascending or
descending.

Am I missing something?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Joost Kraaijeveld]

Re: Sorting primary key

Hi Knut,

"Knut Stolze" <stolze@de.ibm. com> wrote in message
news:ccdhhs$b8k $2@fsuj29.rz.un i-jena.de...[color=blue]
> What do you really want to do with the "sorted" primary keys?[/color]
The person who is developing the Clarion application expects a sorted
primary key to show that in his GUI. And although a unique index and a
primary key are functionally the same they appear not to be for the Clarion
environment (he gets strange errors).

--
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@A skesis.nl
web: www.askesis.nl

[Knut Stolze]

Re: Sorting primary key

Joost Kraaijeveld wrote:
[color=blue]
> Hi Knut,
>
> "Knut Stolze" <stolze@de.ibm. com> wrote in message
> news:ccdhhs$b8k $2@fsuj29.rz.un i-jena.de...[color=green]
>> What do you really want to do with the "sorted" primary keys?[/color][/color]
[color=blue]
> The person who is developing the Clarion application expects a sorted
> primary key to show that in his GUI.[/color]

What does it mean "to show that in his GUI"?
[color=blue]
> And although a unique index and a
> primary key are functionally the same they appear not to be for the
> Clarion environment (he gets strange errors).[/color]

Again, what does that mean and what kind of errors does he get in which
environment?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Joost Kraaijeveld]

Re: Sorting primary key

Knut Stolze wrote:[color=blue][color=green]
>> The person who is developing the Clarion application expects a sorted
>> primary key to show that in his GUI.[/color]
>
> What does it mean "to show that in his GUI"?[/color]
Basically he does a "SELECT * FROM TABLE" without specifying any ordering
and expects the result to be sorted by an descending sorted primary key.
Which is the observed behaviour of his Topspeed database.
[color=blue][color=green]
>> And although a unique index and a
>> primary key are functionally the same they appear not to be for the
>> Clarion environment (he gets strange errors).[/color]
>
> Again, what does that mean and what kind of errors does he get in
> which environment?[/color]
All in the Clarion environment itself. The reason is that the Clarion
datadictionary and the database definitionare out of sync.

But we have worked it out. No decending orderd primary keys and he should
use indexes for what he wants.

--
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@A skesis.nl
web: www.askesis.nl

[Blair Adamache]

Re: Sorting primary key

What you ask for is not supported in a true relational database. Without
an ORDER BY, a set of rows has no order.

Joost Kraaijeveld wrote:[color=blue]
> Basically he does a "SELECT * FROM TABLE" without specifying any ordering
> and expects the result to be sorted by an descending sorted primary key.[/color]

[Knut Stolze]

Re: Sorting primary key

Joost Kraaijeveld wrote:
[color=blue]
> Knut Stolze wrote:[color=green][color=darkred]
>>> The person who is developing the Clarion application expects a sorted
>>> primary key to show that in his GUI.[/color]
>>
>> What does it mean "to show that in his GUI"?[/color][/color]
[color=blue]
> Basically he does a "SELECT * FROM TABLE" without specifying any ordering
> and expects the result to be sorted by an descending sorted primary key.
> Which is the observed behaviour of his Topspeed database.[/color]

I suggest that this guy reads a basic SQL book. Tables contain a set of
rows and a set is, per definition, not sorted. DB2 follows that basic
paradigm and only guarantees a certain ordering of the rows iff the user
specified an ORDER BY clause.

The closest thing would be to reorganize the table data by the primary key
(or another index) to impose a certain order. That should work for the
simple queries. But again, there is no guarantee for that order and it
might change.

Also note that the problem would become worse on partitioned databases where
each partition returns the data independent of the other partitions, and if
the coordinator agent doesn't have an ORDER BY and doesn't sort all the
rows, then you will usually get a very arbitrary and permanently changing
order of the rows.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena