SERVER DEFINITION STATEMENTS¶

CREATE SERVER¶

Server Definition¶

Create remote access information using the CREATE SERVER statement. The created server is used to designate a remote server when executing a SELECT query using DBLINK. For how to use the prepared server, refer to Remote Table.

CREATE SERVER <dblink_server_name> (<connect_info>) ;

    <dblink_server_name> ::= [owner_name.] server_name

    <connect_info> ::=
            <connect_info>, <connect_item>
            | <connect_item>
    <connect_item> ::=
            HOST = host_string
            | PORT = port_number
            | DBNAME = db_name
            | USER = user_name
            | PASSWORD = [password_string]
            | PROPERTIES = [properties_string]
            | COMMENT = [server_comment_string]

owner_name: Specifies the name of the owner of the server to be created.
server_name: Specifies the name of the server to be created. (up to 254 bytes)
<connect_info>: HOST, PORT, DBNAME, and USER are mandatory items in the <connect_item> list of access information.
<connect_item>: It consists of HOST, PORT, DBNAME, USER, PASSWOED, PROPERTIES, and COMMENT items, and the same item cannot be duplicated.
- host_string: It is the hose name or IP address of the broker server that has DBMS information to be accessed remotely.
- port_number: The port number of the broker server that has DBMS information to be accessed remotely.
- db_name: The database name to connect to remotely.
- user_name: The user name to use when connecting to the database to be accessed remotely.
- password_string: Password string for user_name used to connect to the database to be accessed remotely.
- properties_string: Property information string used when connecting to the database to be accessed remotely. (up to 2047 bytes)
- server_comment_string: Specifies comments about server information. (up to 1023 bytes)

Note

db_name and user_name can be written in the form of identifier or string literal.

Example of identifier format
t123db, “123db”, 123db, [124db]
Example of string format
‘t123db’, ‘123db’

CREATE SERVER dblink_srv1 (
       HOST='192.168.1.8',
       PORT=3300,
       DBNAME=demodb,
       USER=dba,
       PASSWORD='password1234',
       PROPERTIES='?rcTime=600',
       COMMENT='this is dblink_srv1'
);

The following is an example that includes minimal information when creating a server. It indicates that the remote demodb will be connected to the dev1 account without a password. srv1, srv2, and srv3 have the same information.

 CREATE SERVER srv1 (
        HOST='192.168.1.8',
        PORT=3300,
        DBNAME=demodb,
        USER=dev1
 );

CREATE SERVER srv2 (
        HOST='192.168.1.8',
        PORT=3300,
        DBNAME=demodb,
        USER=dev1,
        PASSWORD=
 );

 CREATE SERVER srv3 (
        HOST='192.168.1.8',
        PORT=3300,
        DBNAME=demodb,
        USER=dev1,
        PASSWORD=''
 );

The following example specifies the owner when creating the server. In case of CREATE without designating the owner, the current user becomes the owner. Later, you can change the owner using the ALTER SERVER statement. In the example below, the two servers have the same name as srv2, but have different owners as dba and cub, respectively.

 -- When the current account is dba
 CREATE SERVER srv2 (
        HOST='192.168.1.8',
        PORT=3300,
        DBNAME=demodb,
        USER=dev1,
        PASSWORD='dev1-password',
        COMMENT='The owner of this server is dba'
 );

CREATE SERVER cub.srv2 (
        HOST='192.168.1.8',
        PORT=3300,
        DBNAME=demodb,
        USER=dev2,
        PASSWORD='dev2-password',
        COMMENT='The owner of this server is cub.'
 );

CREATE SERVER srv1 ( HOST='localhost', PORT=3300, DBNAME=demodb, USER=dev1 );
CREATE SERVER "srv 1" ( HOST='localhost', PORT=3300, DBNAME=demodb, USER=dev1 );
CREATE SERVER "srv.1" ( HOST='localhost', PORT=3300, DBNAME=demodb, USER=dev1 );
CREATE SERVER cub.srv1 ( HOST='localhost', PORT=3300, DBNAME=demodb, USER=dev1 );

CREATE SERVER "cub"."srv 2" ( HOST='localhost', PORT=3300, DBNAME=demodb, USER=dev1 );
CREATE SERVER [cub].[srv.2] ( HOST='localhost', PORT=3300, DBNAME=demodb, USER=dev1 );

In the above example, creation with “srv.1” and [cub].[srv.2] names fails. In the case of cub.srv1, cub will be recognized as the user name and the server name will be recognized as srv1.

Note

A dot (.’.) cannot be used for the server name. You cannot use a dot (‘.’) in the server name, even if it is enclosed in quotation marks or [].

RENAME SERVER¶

You can change the server name using the RENAME SERVER syntax.

RENAME SERVER [owner_name.] old_server_name {AS | TO} new_server_name ;

owner_name: Specify the owner name of the target server to be renamed.
old_server_name: Specify the name of the server to be renamed.
new_server_name: Specifies the name of the server to be newly assigned. (up to 254 bytes)

Note

Only the owner of the server or members of the ownership group can change the information. In particular, DBA or members of DBA can change all server information.

Even after the change, the owners remain the same. To change the owner, refer to ALTER SERVER syntax.

-- When the current account is dba
RENAME SERVER srv1 AS srv2;
RENAME SERVER dev1.srv1 AS srv3;

Even if the above example is performed under the dba account, the owner of srv2 is not changed and is maintained as cub, the owner of the srv1 server. Also, the owner of the srv3 server remains dev1.

DROP SERVER¶

Existing servers can be removed using DROP SERVER syntax. If the IF EXISTS clause is used together, no error occurs even if the server does not exist.

DROP SERVER [IF EXISTS] [owner_name.] server_name  ;

owner_name: Specify the owner name of the server to be removed..
server_name: Specify the name of the server to be removed.

DROP SERVER srv1;
DROP SERVER cub.srv1;
DROP SERVER IF EXISTS srv2;

Warning

When deleting a user with the DROP USER statement, if there is a server owned by the user, an error is processed and the user is not deleted. First, remove the server owned by the user with the DROP SERVER statement, and then delete the user account.

-- When the current account is dba
csql> create user cub;
Execute OK. (0.000371 sec) Committed.

1 command(s) successfully processed.
csql> create server cub.tsrv (HOST='localhost', PORT=3300, DBNAME=demdb, USER=dev1);
Execute OK. (0.000761 sec) Committed.

1 command(s) successfully processed.
csql> drop user cub;

In the command from line 1,

ERROR: Cannot drop the user who owns database objects(class/trigger/serial/server etc).

0 command(s) successfully processed.
csql> drop server cub.tsrv;
Execute OK. (0.000761 sec) Committed.

1 command(s) successfully processed.
csql> drop user cub;
Execute OK. (0.001650 sec) Committed.

1 command(s) successfully processed.
csql>

In the example above, you can see that the drop user cub statement is failing while the tsrv server owned by the cub account is created. After removing the cub.tsrv server, you can see that the cub account could be deleted normally..

ALTER SERVER¶

You can change the server information by using the ALTER statement. You can change the owner of the target server, or update information about HOST, PORT, DBNAME, USER, PASSWOED, PROPERTIES, and COMMENT.

ALTER SERVER <dblink_server_name> <alter_server_list> ;

    <dblink_server_name> ::=  [owner_name.] server_name

    <alter_server_list> ::=
            <alter_server_list>, <alter_server_item>
            | <alter_server_item>
    <alter_server_item> ::=
            OWNER TO owner_name
            | CHANGE <connect_item>
    <connect_item> ::=
            HOST = host_string
            | PORT = port_number
            | DBNAME = db_name
            | USER = user_name
            | PASSWORD = [password_string]
            | PROPERTIES = [properties_string]
            | COMMENT = [server_comment_string]

Note

Only the owner of the server or members of the ownership group can change the information. In particular, DBA or DBA members can change all server information.

Warning

It is not possible to update to remove values for HOST, PORT, DBNAME, and USER.

OWNER TO clause¶

You can change the owner of the server using the OWNER TO clause.

ALTER SERVER [owner_name.] server_name  OWNER TO new_owner_name ;

owner_name: Specifies the owner name of the target server whose owner is to be changed.
server_name: Specifies the name of the target server whose owner is to be changed.
new_owner_name: Specifies the new owner name.

Warning

There is no OWNER TO clause in an ALTER SERVER clause, or it must be specified only once.

CREATE SERVER srv1 (HOST='broker-server-name', PORT=3300, DBNAME=demodb, USER=dev1);
ALTER SERVER srv1 OWNER TO usr1;
ALTER SERVER usr1.srv1 OWNER TO usr2;

CHANGE clause¶

The CHANGE section is used to change the values of HOST, PORT, DBNAME, USER, PASSWOED, PROPERTIES, and COMMENT items.

Warning

Multiple CHANGE clauses in one ALTER SERVER statement can be listed by separating them with commas (,). However, at this time, only one CHANGE clause for the same item should be specified.
Items that were not mentioned when performing the ALTER SERVER syntax do not initialize or delete the value, but retain the existing value.

ALTER SERVER  [owner_name.] server_name CHANGE <connect_item> [, CHANGE <connect_item>] ... ;

   <connect_item> ::=
           HOST = host_string
           | PORT = port_number
           | DBNAME = db_name
           | USER = user_name
           | PASSWORD = [password_string]
           | PROPERTIES = [properties_string]
           | COMMENT = [server_comment_string]

owner_name: Specifies the user name of the server to be created.
server_name: Specifies the name of the server to be created. (up to 254 bytes)
host_string: It is the hose name or IP address of the broker server that has DBMS information to be accessed remotely.
port_number: The port number of the broker server that has DBMS information to be accessed remotely.
db_name: The database name to connect to remotely.
user_name: he user name to use when connecting to the database to be accessed remotely.
password_string: Password string for user_name used to connect to the database to be accessed remotely.
properties_string: Property information string used when connecting to the database to be accessed remotely. (up to 2047 bytes)
server_comment_string: Specifies comments about server information. (up to 1023 bytes)

CREATE SERVER srv1 ( HOST='localhost', PORT=3300, DBNAME=demodb, USER=dev1 );

ALTER SERVER srv1 CHANGE HOST='127.0.0.1';
ALTER SERVER srv1 CHANGE HOST='127.0.0.1', OWNER TO usr1;
ALTER SERVER srv1 CHANGE USER=dev2, CHANGE PASSWORD='dev2-pawword', CHANGE PORT=3500;

The above example shows that the CHANGE clause can be used to list multiple items at once, or it can be used together with the OWNER TO clause.

ALTER SERVER srv1 CHANGE PORT=;
ALTER SERVER srv1 CHANGE DBNAME=;
ALTER SERVER srv1 CHANGE USER=;
ALTER SERVER srv1 CHANGE HOST=;
ALTER SERVER srv1 CHANGE HOST='';

All of the above examples are examples of not supported cases. Since HOST, PORT, DBNAME, and USER, which are essential elements in the configuration of server information, must have values, setting changes that delete values are not supported. In particular, in the case of HOST, setting it to an empty string is also not allowed.

ALTER SERVER srv1 CHANGE PASSWORD=;
ALTER SERVER srv1 CHANGE PASSWORD='';

ALTER SERVER srv1 CHANGE PROPERTIES=;
ALTER SERVER srv1 CHANGE PROPERTIES='';

ALTER SERVER srv1 CHANGE COMMENT=;
ALTER SERVER srv1 CHANGE COMMENT='';

The above examples are all supported examples. PASSWORD, PROPERTIES, COMMENT, which are not essential elements in the configuration of server information, do not necessarily have a value, so it is possible to change the setting to delete the value.