3.1.1 Specification format and rules for the ALTER SERVER statement : Hitachi Advanced Database SQL Reference

(1) Specification format

ALTER-SERVER-statement ::= ALTER SERVER foreign-server-name
                      OPTIONS (foreign-server-definition-modification-option
                              [,foreign-server-definition-modification-option]...)
 
  foreign-server-definition-modification-option ::= {ADD|SET|DROP}
                                     {ACCESS_KEY [access-key-string]
                                     |SECRET_KEY [secret-key-string]
                                     |ENDPOINT [endpoint-string]}

To Page Top

(2) Explanation of specification format

● foreign-server-name

Specifies the name of the foreign server whose definition is to be changed. For rules for specifying foreign server names, see (2) Rules for characters that can be used in names in 6.1.4 Specifying names.

Note that the name of an undefined foreign server cannot be specified.

● foreign-server-definition-modification-option

foreign-server-definition-modification-option ::= {ADD|SET|DROP}
                                   {ACCESS_KEY [access-key-string]
                                   |SECRET_KEY [secret-key-string]
                                   |ENDPOINT [endpoint-string]}

Specifies changes to the foreign server definition (foreign server option).

{ADD|SET|DROP}

Specifies the operation (add, change, or delete) of the foreign server option.

ADD

Adds the foreign server option specification. The following are examples:
```
ALTER SERVER "FOREIGNSERVER1" OPTIONS (ADD ENDPOINT 'http://xxxx-yyz99:1234')
```
The above is a specification example when adding an endpoint to a foreign server.

The following specification rules apply:
- The specification value of the foreign server option (endpoint string in the above example) must be specified.
- If the specified foreign server option is already defined, the ALTER SERVER statement will result in an error.
- You cannot add only one of the access key or the secret key. Be sure to add both an access key and a secret key.
SET

Changes the foreign server option specification. The following are examples:
```
ALTER SERVER "FOREIGNSERVER1"
    OPTIONS (SET SECRET_KEY 'Ql9T84MpeqVis0XteBsiQgH7dPlfhysuLKiuFHg')
```
The above is a specification example when changing to a predefined secret key with the CREATE SERVER statement.

The following specification rules apply:
- The specification value of the foreign server option (secret key string in the above example) must be specified.
- If an undefined foreign server option is specified, the ALTER SERVER statement will result in an error. In the above example, if the secret key is not defined in the CREATE SERVER statement, the ALTER SERVER statement will result in an error.
DROP

Deletes the foreign server option specification. The following are examples:
```
ALTER SERVER "FOREIGNSERVER1" OPTIONS (DROP ENDPOINT)
```
The above is a specification example when deleting a predefined endpoint with the CREATE SERVER statement.

The following specification rules apply:
- The specification value of the foreign server option (endpoint string in the above example) cannot be specified.
- If an undefined foreign server option is specified, the ALTER SERVER statement will result in an error. In the above example, if no endpoints are defined in the CREATE SERVER statement, the ALTER SERVER statement will result in an error.
- It is not possible to delete only one of the access key or the secret key. Be sure to delete both the access key and the secret key.
Important

If you delete the access key and secret key in the AWS environment, you will need to set up authentication information using an IAM role or authentication information file. For details, see How to define a foreign server in the manual HADB Setup and Operation Guide.

ACCESS_KEY [access-key-string]

Specifies the access key to be used for foreign server authentication. Up to 100 bytes of character string data can be specified for the access key string.

SECRET_KEY [secret-key-string]

Specifies the secret key to be used for foreign server authentication. Up to 100 bytes of character string data can be specified for the secret key string.

ENDPOINT [endpoint-string]

When accessing a foreign server using an endpoint, specify the endpoint in URI format in the endpoint string. Up to 100 bytes of character string data can be specified for the endpoint string.

For the endpoint string, specify a string beginning with "http://" or "https://". For example, if the endpoint is "https://XXXX:1234", the endpoint string is specified as "'https://XXXX:1234'".

The rules for specifying the foreign server definition modification option is as follows:

When multiple foreign server definition modification options are specified, different operations cannot be specified.

ALTER SERVER "FOREIGNSERVER1"
    OPTIONS (ADD ACCESS_KEY '6yTZJwzVjl6nYTjKICop'
            ,ADD SECRET_KEY 'Ql9T84MpeqVis0XteBsiQgH7dPlfhysuLKiuFHg')

ALTER SERVER "FOREIGNSERVER1"
    OPTIONS (SET ACCESS_KEY '6yTZJwzVjl6nYTjKICop'
            ,ADD ENDPOINT 'http://xxxx-yyz99:1234')

When multiple foreign server definition modification options are specified, the same foreign server option cannot be specified.

<Example of specification that results in an error>
```
ALTER SERVER "FOREIGNSERVER1"
    OPTIONS (ADD ACCESS_KEY '6yTZJwzVjl6nYTjKICop'
            ,ADD ACCESS_KEY 'Ql9T84MpeqVis0XteBsiQgH7dPlfhysuLKiuFHg')
```

To Page Top

(3) Privileges required at execution

To execute the ALTER SERVER statement, the CONNECT privilege and the DBA privilege are required.

To Page Top

(4) Rules

A user (the HADB user with the authorization identifier that was used for the current connection to the HADB server) can change the definition of those foreign servers defined by that user. You cannot change the definition of a foreign server defined by another HADB user.

To Page Top

(5) Examples

Example 1:

Add an endpoint to the foreign server (FOREIGNSERVER1).

ALTER SERVER "FOREIGNSERVER1" OPTIONS (ADD ENDPOINT 'http://xxxx-yyz99:1234')

Example 2:

Add an access key and secret key to the foreign server (FOREIGNSERVER1).

ALTER SERVER "FOREIGNSERVER1"
    OPTIONS (ADD ACCESS_KEY '6yTZJwzVjl6nYTjKICop'
            ,ADD SECRET_KEY 'Ql9T84MpeqVis0XteBsiQgH7dPlfhysuLKiuFHg')

Example 3:

Change the secret key of the foreign server (FOREIGNSERVER1).

ALTER SERVER "FOREIGNSERVER1"
    OPTIONS (SET SECRET_KEY 'Ql9T84MpeqVis0XteBsiQgH7dPlfhysuLKiuFHg')

Example 4:

Delete an endpoint on the foreign server (FOREIGNSERVER1).

ALTER SERVER "FOREIGNSERVER1" OPTIONS (DROP ENDPOINT)

To Page Top