dbunload -e "User ID" does not exist

[RADical_Systems]

Hi All,

I've checked the search and also been through this with Justin but we are unsure what we are doing incorrectly. The syntax for our DBUnload works and when not using the "-e" parameter and the unload/reload works fine. With this in place we receive an error:

* SQL error: User ID 'spaceman' does not exist

The command line is below:

"C:\\Program Files\\SQL Anywhere 16\\Bin64\\dbunload.exe" -e blobstore -c "UID=XXXXXXXXXX;PWD=XXXXXXXXXXX;eng=V316SQL;dbn=SpaceMan;links=TCPIP{host=localhost:2652}" -an "F:\\Temp\\spaceman.db"

As you can see this is SQL Anywhere 16 so not sure if it works in 17. Is there anything wrong with the above and/or can anyone shed light on what the error means and how to correct it?

FYI - I have screenshots but the forum says they contain viruses, (possible but not convinced), so I can't upload them.

Thanks,

Alasdair

[chris_keating]

An unload that uses -e (excude tables) or -t (included tables) will only unable table related objects such as tables (and their data), indexes, triggers, and statistics. It will not include other database objects such as user definitions, procedures, and views.

If you have table owned by 'spaceman', such unloads will not include that user definition but will unload the table definition.

[VolkerBarth]

So this means -e and -t are not really usable with -an and the other "immediate rebuild" options, right?

[chris_keating]

Correct. The -e and -t are not complete unloads of the database schema and as a result generally not usable with automatic rebuild options such as -an.

[VolkerBarth]

Well, it would be nice if the docs would explain this...

[RADical_Systems]

As Volker says it would be useful to explain this and/or not allow the dbunload to start when -e and -an are used but then I "guess" I could build a blank db with everything it needs then the -an could load into that - maybe!

By way of explanation for what I am trying to do, (in case you feel it could be useful for a modification on dbunload), I have two tables that relate to blob storage. In the DB this accounts for 65GB of 80GB and one of the data warehousing requirements has not need for all these blobs so to save transfer time I am trying to automate a removal of these tables and release of space i.e. not unload these two tables.

If this could be done in the dbunload directly that would be perfect. I appreciate that the process would only work if the tables dropped had nothing referring to them so an unload/reload may fail if the "wrong" tables were dropped so perhaps a catch 22 on whether it is a sensible feature or not but hopefully this explains and may be considered a useful feature for a new parameter to actually unload/reload everything.

[VolkerBarth]

In case you are using a separate dbspace for that particular blob storage, if would also be handy if there where an unload that would omit an additional dbspace with all it's tables... But of course that could also be done via a modified reload.sql file.

[chris_keating]

I will ask the doc team to add clarification.

[VolkerBarth]

I'm very wild guessing that option -e might not work with -an. The docs tell for -e:

Excludes the specified tables from the reload.sql file. Table names are always case insensitive, even in case sensitive databases.
A reload.sql file created with the -e option should not be used to rebuild a database because the file will not include all the database tables. If a table has foreign keys referring to it, the database cannot be rebuilt without the contents of the table.
It is recommended that you only use the -e option with the -d option to unload data for all tables except those identified by -e.

With option -an, you certainly ARE rebuilding a database, so using -e seems not recommended...

You might check the differences of the reload.sql files when using -e vs. when not. Probably the former is missing a CREATE USER statement whereas the latter is not.

[RADical_Systems]

Volker - you are absolutely correct, (I checked the outputs), and I guess I knew that to some degree. Chris has explained what the process does above and that answers my query. Thanks for your help

[chris_keating]

Not that this is helpful with the command line dbunload, SQL Central Unload warns that the reload will fail if you exclude tables when performing an Unload and reload into a new database.