How do I CREATE EXISTING TABLE When Connection is Not Available

[JimDiaz]

12.0.1.4266

I have a script to create a database which includes a remote server and several proxy tables. The remote server is not yet available is there a way to force the creation of these existing tables?

Thanks,

Jim

[VolkerBarth]

You are asking whether one can create proxy tables (existing or not) without accessing the remote server?

If so, I surely would expect a "No, you cannot."

[JimDiaz]

Yes that is what I'm asking. We are upgrading existing databases, part of the update includes the establishment of proxy tables and remote procedures into another database which does not always exist at the time of the upgrade. We would like to perform the upgrade and have the proxy tables activate once the remote database is installed.

The software issues a RPC to the database wrapped in a local function to test if the remote database is present and won't offer the functionality to the end user unless it exists.

[VolkerBarth]

CREATE SERVER can be used with variables with v12.0.1 and above, furthermore you can ALTER SERVER lateron. So would it be possible to use a "dummy remote database" (possibly just containing the desired remote tables, possibly only with empty tables) and link to these as long as the real remote database is not available? (Of course that database would have to be physically existent during the upgrade but you might be able to create it as part of your scripts, too, in case it's a SQL Anywhere database...).

Note, that is just another wild guess, I have not tried to do so. However, there have been some FAQs on CREATE SERVER with variables in this forum, say, that one.

[VolkerBarth]

If so, I surely would expect a "No, you cannot."

Because Volker - naively, but somewhat inevitably - does not expect undocumented features:)

[MarkCulp]

There is a way to do this. It is not documented and is not recommended to be used since it can lead you into trouble - e.g. when you do this there is no check done to confirm that the table column specifications match the remote table specifications - but if you know what you are doing and know that your column specifications are correct (which should be the case if you are rebuilding a database) then here is what you can use:

CREATE [ EXISTING ] TABLE name ( _column-specifications_ )
    AT '...location-specification...'
    LOCAL ONLY;

The LOCAL ONLY clause tells the server to not check the definition of the table and will therefore not attempt to connect to the remote server. USE WITH CAUTION!

[Breck_Carter]

That would have saved me a lot of time.

> if you know what you are doing

When did that become a necessary condition for documenting a feature?

> USE WITH CAUTION!

Is there anything in SQL that can be used without caution?

IMO, if some option or setting is rarely needed, simply don't make it the default rather than hiding its existence.

[VolkerBarth]

Just to understand:

Does the LOCAL ONLY clause only make a difference when the CREATE TABLE statement is executed (by omitting the remote access and thereby ignoring the remote schema) or does it have an influence on the usage of the proxy table itself, i.e. when doing queries?

(I'm asking as even a "regularly created" proxy table bears the risk that the according remote object may not be accessible lateron or may have been dropped or altered in a way that the proxy table's definition does not fit anymore or that permissions have been changed, so that caution is true for all proxy tables...)

[VolkerBarth]

And just another question:

Am I correct that the EXISTING clause is ineffective when LOCAL ONLY is used?

(Frankly, I would expect that CREATE EXISTING TABLE would not need the LOCAL ONLY undocumented clause, not even for "script compatibility" - if you expect the exceptional case that the remote table may not be existing/accessible when creating proxy objects, you explicitly have to add that LOCAL ONLY clause, and then you could as well replace CREATE EXISTING TABLE with CREATE TABLE, particularly as the column definition - optional for CREATE EXISTING TABLE - cannot be optional with LOCAL ONLY. - Sorry, I should not comment on an undocumented feature...)

[MarkCulp]

Yes, it only affects the creation time. You are correct that there are other conditions - e.g. changes to the remote table made on the remote server - that can also lead to the proxy table definition getting out of sync with the remote table definition. In these cases the user needs to take action to update the proxy table definition.

[MarkCulp]

The LOCAL ONLY says "don't do anything other than put the meta data into the local system tables" and hence it does not do anything to the remote server / database. Beyond that, the operation is identical to when LOCAL ONLY is not used. If you used CREATE TABLE then the "normal" operation would have been to go to the remote server and create the table, so if you used LOCAL ONLY then the remote table had better already exists (in which case it would have made more sense to have used CREATE EXISTING TABLE).

Note also that if you use CREATE TABLE ... LOCAL ONLY then when (and if) the proxy table is dropped then the server will attempt to also drop the remote table. If you had used CREATE EXISTING TABLE ... LOCAL ONLY then the server will not attempt to drop the remote table when the proxy table is dropped.

HTH

[VolkerBarth]

Ah, I see - I had not thought of the difference when dropping the proxy table. Thanks for the further details!

Just to clarify: Is that effect of DROP TABLE in conjunction with CREATE TABLE (i.e. to also drop the remote object) documented anywhere?

[VolkerBarth]

the user needs to take action to update the proxy table definition.

Is that possible? AFAIK (see that according FAQ) one would have to drop and re-create the proxy table...

[MarkCulp]

That is correct.

[MarkCulp]

Yes, it is documented on the Creating Proxy Tables page in the documentation. See the second paragraph in the Context section.

Quote:

If you use the CREATE TABLE statement to create both a local and remote table, and then subsequently use the DROP TABLE statement to drop the proxy table, the remote table is also dropped. Use the DROP TABLE statement to drop a proxy table created using the CREATE EXISTING TABLE statement however. In this case, the remote table is not dropped.

[VolkerBarth]

Hm, I have the impression that this should make it to the DROP TABLE documentation, too...