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.***********@Askesis.nl
web: www.askesis.nl 12 4270
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: 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.***********@Askesis.nl web: www.askesis.nl
"Joost Kraaijeveld" <J.***********@Askesis.nl> wrote in message
news:x5*****************@amsnews02.chello.com... 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
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.
Hi Blair,
"Blair Adamache" <ba*******@2muchspam.yahoo.com> wrote in message
news:cc**********@hanover.torolab.ibm.com... You need to create a clustered unique index, and base your primary key on this.
And how do you do that with DB2 7.2 Linux/W2K (in terms of "Alter table" or
"Create index")?
However, nothing in a relational database is truly 100% sorted until you use an ORDER BY clause in a SELECT statement.
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
Hi Mark,
"Mark A" <no****@nowhere.com> wrote in message
news:Bf***************@news.uswest.net... Are you talking about IBM's DB2 (not DB/2)? Occasionally we get some Dbase questions here.
Yep. DB2 7.1 UDB (I am from the OS/2 time ;-), hence DB/2 ).
If so, just specify ORDER BY in the select clause when you want the result set returned in a particular order.
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
Version 7 CREATE INDEX syntax is in the SQL Reference manual: http://www-306.ibm.com/cgi-bin/db2ww...8.htm#HDRCINDX
Joost Kraaijeveld wrote: Hi Blair,
"Blair Adamache" <ba*******@2muchspam.yahoo.com> wrote in message news:cc**********@hanover.torolab.ibm.com...
You need to create a clustered unique index, and base your primary key on this.
And how do you do that with DB2 7.2 Linux/W2K (in terms of "Alter table" or "Create index")?
However, nothing in a relational database is truly 100% sorted until you use an ORDER BY clause in a SELECT statement.
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
> > Are you talking about IBM's DB2 (not DB/2)? Occasionally we get some
Dbase questions here.
Yep. DB2 7.1 UDB (I am from the OS/2 time ;-), hence DB/2 ).
Many years ago, there was DB2/2 (for OS/2) but never DB/2. If so, just specify ORDER BY in the select clause when you want the
result set returned in a particular order.
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"
You can download all the manuals here in PDF format (including the SQL
Reference): http://www-306.ibm.com/cgi-bin/db2ww...bs.d2w/en_main
Here is an example:
CREATE UNIQUE INDEX index-name
ON PROJECT(PROJNAME) CLUSTER
You can use the DESC option if you will have ORDER BY statements in
decending sequence. The UNIQUE and CLUSTER parms are optional.
Joost Kraaijeveld wrote: 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).
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
Hi Knut,
"Knut Stolze" <st****@de.ibm.com> wrote in message
news:cc**********@fsuj29.rz.uni-jena.de... What do you really want to do with the "sorted" primary keys?
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.***********@Askesis.nl
web: www.askesis.nl
Joost Kraaijeveld wrote: Hi Knut,
"Knut Stolze" <st****@de.ibm.com> wrote in message news:cc**********@fsuj29.rz.uni-jena.de... What do you really want to do with the "sorted" primary keys?
The person who is developing the Clarion application expects a sorted primary key to show that in his GUI.
What does it mean "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).
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
Knut Stolze wrote: The person who is developing the Clarion application expects a sorted primary key to show that in his GUI.
What does it mean "to show that in his GUI"?
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. 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).
Again, what does that mean and what kind of errors does he get in which environment?
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.***********@Askesis.nl
web: www.askesis.nl
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: Basically he does a "SELECT * FROM TABLE" without specifying any ordering and expects the result to be sorted by an descending sorted primary key.
Joost Kraaijeveld wrote: Knut Stolze wrote: The person who is developing the Clarion application expects a sorted primary key to show that in his GUI. What does it mean "to show that in his GUI"?
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Shaunak Kashyap |
last post by:
Does anyone know what sorting algorithm(s) -- quicksort, mergesort,
radix sort, etc. -- does PHP use internally in its sort function?
|
by: David Pratt |
last post by:
I have been using the following for sorting a list of dictionaries.
This works but only provides sorting on a single key. I am wanting to
extend this with a better comparison expression so that it...
|
by: D. Roshani |
last post by:
Hello !
I wonder if any one can help me to create a cosomize sorting order (as Macro or added small program
in c++ or c# which does this work) in a Access Database contaning one table only words...
|
by: jwedel_stolo |
last post by:
Hi
I'm creating a dataview "on the fly" in order to sort some data prior to writing out the information to a MS SQL table
I have used two methods in order to determine the sort order of the...
|
by: noureensyed |
last post by:
Hi there,
Any ideas on how to sort an xml of the type below. I want to output the
exact same xml as well just sorted. However in my case I want to select
a particular column tag below, say...
|
by: suzy |
last post by:
hello.
how can i sort data in a dataset? all the examples i have seen on msdn, etc
are sorting a dataview. this works fine, but i want to return the results
in xml and the dataview doesn't...
|
by: Mike |
last post by:
Hello,
I have a few rather urgent questions that I hope someone can help with (I
need to figure this out prior to a meeting tomorrow.) First, a bit of
background: The company I work for is...
|
by: Jeweladdict |
last post by:
I have 2 linked tables,
The first table (PRODUCT) has product name, pricing, size, etc with an
autonumber primary key.
The second table is a UPC table with the primary key from PRODUCT
linked...
|
by: Jimmy |
last post by:
Is there a way to sort/group a report based on the second column of a combo
box, i.e. the text associated with the primary key number?
|
by: Oonz |
last post by:
Hi Friends,
How can we insert records in sorted order
like consider a table
No Name Phone
1 test1 12345
1 test1 23455
2 test2 68638
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |