I'm using the output of DBUNLOAD to compare databases before and after migrations, including the unloaded data. That usually works fine.

However, now and then, the diff tool displays differences for some tables, and that seems to be due to the fact that these tables have (non-unique) clustered indexes that do not build the primary key - and therefore may order data non-deterministically. Well, that's documented behaviour:

ORDER clause With ORDER ON (the default), the exported data is ordered by clustered index if one exists. If a clustered index does not exist, the exported data is ordered by primary key values.

I do understand that this default order makes sense particularly to speed up the reload. And of course I can do separat "UNLOAD SELECT * FROM myTABLE ORDER BY MyPk" statements to unload data in the desired order.

However, if would be nice if DBUNLOAD would have an option (say, "-up") to always use primary key (or at least an unique clustered key) to order the output.


To add: Since DBUNLOAD certainly makes use of the UNLOAD TABLE/MATERIALIZED VIEW statement, the suggested enhancement would apply to that, as well.

asked 06 Aug '15, 05:00

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

edited 06 Aug '15, 05:03

1

I support your request. Dbunload is hugely valuable beyond it's ability to upgrade and reload databases.

As a workaround, try using ALTER statements to temporarily move the CLUSTERED hint-word from the secondary index to the primary key. Caveat: I haven't tried that specific thing, but I have done it to move the CLUSTERED hint-word from one secondary index to another and the ALTER statements run in zero-time... proving the "hint-word" classification.

It's not a perfect workaround since altering a hint even for a short time might not be appreciated (or allowed) for a production system, hence my support for your request.

(06 Aug '15, 06:58) Breck Carter

Ideally, one might consider that with the following suggestion:

DBUNLOAD option to use data file names based on table names - Vincent's script is still in good use:)

(06 Aug '15, 08:03) Volker Barth

Your suggestion is noted. However we would not be able to use the -up switch for dbunload since this switch is already defined to mean "unload passwords" in v17. We'll pick something else (perhaps -uk for "unload by [primary] key"?)

I've added this to our future enhancements list.

Thanks for the suggestion.

permanent link

answered 06 Aug '15, 08:12

Mark%20Culp's gravatar image

Mark Culp
23.3k9132275
accept rate: 40%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×207
×107
×41
×27

question asked: 06 Aug '15, 05:00

question was seen: 731 times

last updated: 06 Aug '15, 08:12