Add an option to DBUNLOAD to export data in primary key order

[VolkerBarth]

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.

[Breck_Carter]

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.

[VolkerBarth]

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:)

[MarkCulp · Accepted solution]

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.