Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

dbunload reload ordered data

4 posts in General Discussion Last posting was on 2003-10-17 11:29:32.0Z
gregory durniak Posted on 2003-10-16 20:29:02.0Z
Sender: 77b3.3f8efeec.1804289383@sybase.com
From: gregory durniak
Newsgroups: ianywhere.public.general
Subject: dbunload reload ordered data
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f8eff8d.77d6.846930886@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Oct 2003 13:29:02 -0700
X-Trace: forums-1-dub 1066336142 10.22.241.41 (16 Oct 2003 13:29:02 -0700)
X-Original-Trace: 16 Oct 2003 13:29:02 -0700, 10.22.241.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1861
Article PK: 4113

Adaptive Server Anywhere Version 7.0.4.2788

I need to reload my data, ordered by start_date (an indexed
column)
This is a stand alone table, so there is no primary key

I tried dbunload -c "uid=dba;pwd=sql;dbf=C:\p044.db" -an ...
but this does not let me specify "Order By ...", so the
reloaded database came out the same (I did not use -u)

is there a command to reload, ordered by?


Greg Fenton Posted on 2003-10-17 02:09:22.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4; MultiZilla v1.4.0.4A) Gecko/20030624
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: dbunload reload ordered data
References: <3f8eff8d.77d6.846930886@sybase.com>
In-Reply-To: <3f8eff8d.77d6.846930886@sybase.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: cpe000625d8278c-cm.cpe.net.cable.rogers.com
X-Original-NNTP-Posting-Host: cpe000625d8278c-cm.cpe.net.cable.rogers.com
Message-ID: <3f8f4f52@forums-1-dub>
Date: 16 Oct 2003 19:09:22 -0700
X-Trace: forums-1-dub 1066356562 24.112.254.75 (16 Oct 2003 19:09:22 -0700)
X-Original-Trace: 16 Oct 2003 19:09:22 -0700, cpe000625d8278c-cm.cpe.net.cable.rogers.com
Lines: 34
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1862
Article PK: 4112


gregory wrote:
>
> This is a stand alone table, so there is no primary key
>

Even tables that are "stand-alone" should have a primary key. Unless
you have a valid reason to allow multiple *identical* rows, at least one
of the columns will be a candidate for a UNIQUE INDEX, which is
basically what a Primary Key is.

>
> is there a command to reload, ordered by?

Others may have a better way, but here's one way to do it:

1. Load the data into the database.
2. Create a second table with an identical schema to the loaded table.
3. Use INSERT ... SELECT to populate the second table:
INSERT INTO table_2
SELECT * from table_1 ORDER BY table_1.start_date;
4. DROP the first table
5. Rename the second table with ALTER TABLE ... RENAME


Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Breck Carter [TeamSybase] Posted on 2003-10-17 11:27:53.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: dbunload reload ordered data
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <ptjvovk1h6p7d8tvit7oqap0a3ecrblf97@4ax.com>
References: <3f8eff8d.77d6.846930886@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 17 Oct 2003 04:27:53 -0700
X-Trace: forums-1-dub 1066390073 64.7.134.118 (17 Oct 2003 04:27:53 -0700)
X-Original-Trace: 17 Oct 2003 04:27:53 -0700, bcarter.sentex.ca
Lines: 46
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1866
Article PK: 4115

In addition to what Greg said, here is a good reason to have a primary
key or a not-null unique index on every table:

=====
ASA User's Guide
PART 5. Database Administration and Advanced Use
CHAPTER 21. Backup and Data Recovery
Configuring your database for data protection
Controlling transaction log size

The size of the transaction log can determine what kind of backup is
right for you, and can also affect recovery times.

You can control how fast the transaction log file grows by ensuring
that all your tables have compact primary keys. If you carry out
updates or deletes on tables that do not have a primary key or a
unique index not allowing NULL, the entire contents of the affected
rows is entered in the transaction log. If a primary key is defined,
the database server needs to store only the primary key column values
to uniquely identify a row. If the table contains many columns or wide
columns, the transaction log pages fill up much faster if no primary
key is defined. In addition to taking up disk space, this extra
writing of data affects performance.

If a primary key does not exist, the engine looks for a UNIQUE NOT
NULL index on the table (or a UNIQUE constraint). A UNIQUE index that
allows NULL is not sufficient.
=====

On 16 Oct 2003 13:29:02 -0700, gregory durniak wrote:

>Adaptive Server Anywhere Version 7.0.4.2788
>
>I need to reload my data, ordered by start_date (an indexed
>column)
>This is a stand alone table, so there is no primary key
>
>I tried dbunload -c "uid=dba;pwd=sql;dbf=C:\p044.db" -an ...
>but this does not let me specify "Order By ...", so the
>reloaded database came out the same (I did not use -u)
>
>is there a command to reload, ordered by?

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Breck Carter [TeamSybase] Posted on 2003-10-17 11:29:32.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: dbunload reload ordered data
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <46kvovo9nfg7ndmpgm6ij3mmnklnpoubla@4ax.com>
References: <3f8eff8d.77d6.846930886@sybase.com>
X-Newsreader: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 17 Oct 2003 04:29:32 -0700
X-Trace: forums-1-dub 1066390172 64.7.134.118 (17 Oct 2003 04:29:32 -0700)
X-Original-Trace: 17 Oct 2003 04:29:32 -0700, bcarter.sentex.ca
Lines: 24
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1867
Article PK: 4118

Also please note that ASA 8.0.2 and 9 have clustered indexes which are
good for range queries on date columns. There are many reasons to move
to 9, the Index Consultant being one of the biggest.

Breck

On 16 Oct 2003 13:29:02 -0700, gregory durniak wrote:

>Adaptive Server Anywhere Version 7.0.4.2788
>
>I need to reload my data, ordered by start_date (an indexed
>column)
>This is a stand alone table, so there is no primary key
>
>I tried dbunload -c "uid=dba;pwd=sql;dbf=C:\p044.db" -an ...
>but this does not let me specify "Order By ...", so the
>reloaded database came out the same (I did not use -u)
>
>is there a command to reload, ordered by?

--
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com