Why is UNLOAD from a proxy table not supported?

[VolkerBarth]

Just a follow-up from this question:

What's the reason that UNLOAD from a proxy table is not supported?

12.0.1.3324 on Windows 32-bit returns SQLCODE -134 (SQLE_NOT_IMPLEMENTED - "Feature not supported") when trying to unload from a MS SQL Server proxy table.

---

I'm aware that unloading from a remote database will be far less efficient as unloading from a local database. However, OUTPUT from a proxy table does work, and in some cases UNLOAD is not preferable over OUTPUT because of its performance but simply because of its nature as a real SQL Statement (in contrast to an ISQL statement) that can be used within code blocks and procedures.

In the current case, I've found a workaround - but I would like to know whether there are particular reasons for the UNLOAD limitation.

[VolkerBarth]

Or am I to select the proxy table's contents to a local temporary table (say by SELECT INTO LOCAL TEMPORARY TABLE) - and then UNLOAD from that?

I have not yet tested that, but it would be an acceptable solution...

[Former Member · Accepted solution]

Volker,

The initial implementation of unload table (many, many, years ago) performed a low level table scan of that table and did not use any intermediate cursor. As a result, unloading a proxy table was impossible since the "low level table scan" would somehow need to be performed on the remote server. However, more recent versions of SQL Anywhere do use a cursor when performing UNLOAD...SELECT, and so it may be possible to at the very least to perform an UNLOAD...SELECT for proxy tables. I will therefore open an enhancement request to revisit the "no unload for proxy table" restriction and see if we can provide at least UNLOAD...SELECT support for proxy tables. In the meantime your workaround of selecting the contents of proxy tables into a temporary table and then unloading the temp table seems to be reasonable.

Karim

[VolkerBarth · Accepted solution]

Thanks for getting into this!

For the workaround, another nice-to-have would be the ability to do an SELECT INTO LOCAL TEMPORARY TABLE and define the PK automatically, particularly when the unload should be ordered by PK.

Currently, it's not possible to ALTER an automatically created temporary table, cf. this question. Creating an index is possible (though somewhat cumbersome).

I'd suggest a clause like "SELECT * INTO LOCAL TEMPORARY TABLE MyTempTable (WITH PRIMARY KEY) FROM" that would auto-create a PK for MyTempTable if the result set has a unique row (and fail if none or more than one are available). AFAIK sa_describe_query allows for such tests...