CREATE NICKNAME statement - examples for the BioRS wrapper

For example:

CREATE NICKNAME SwissProt
	   (ID VARCHAR(32) OPTIONS (ELEMENT_NAME '_ID_'),
	    ALLTEXT VARCHAR(128),
	    ENTRYDATE VARCHAR (64))
     FOR SERVER biors_server;

Using this simple CREATE NICKNAME syntax limits you in two ways:

1. You are limited to one family of nicknames for each federated database schema. For example, you have two databanks that have a parent-child relationship. The databanks are SWISSPROT and SPFEAT. These databanks form a family. If you use the default syntax for the CREATE NICKNAME statement, you will have the (SWISSPROT nickname for the SWISSPROT databank and the SPFEAT nickname for the SPFEAT databank. To have more than one nickname for SWISSPROT in the schema, you must use the REMOTE_OBJECT nickname option when you register the nickname.
2. You are limited to databanks whose names can be used as nickname names. The databank name must conform to the syntax that is supported by the federated server. For example, if the databank name includes a period or space, you must use the REMOTE_OBJECT nickname option when you register the nickname.

The following example shows the same set of nickname characteristics as the previous example, but changes the nickname name. The example uses the REMOTE_OBJECT nickname option to specify the BioRS databank for which the nickname is being defined:

CREATE NICKNAME NewSP
	   (ID VARCHAR(32) OPTIONS (ELEMENT_NAME '_ID_'),
	    ALLTEXT VARCHAR(128),
	    ENTRYDATE VARCHAR (64))
     FOR SERVER biors_server
     OPTIONS (REMOTE_OBJECT 'SwissProt');

CREATE NICKNAME SwissFT
	 (ID VARCHAR(32) OPTIONS (ELEMENT_NAME '_ID_'),
	  ALLTEXT VARCHAR (128),
	  ENTRYDATE VARCHAR (64),
	  FtLength VARCHAR (16))
    FOR SERVER biors1
	  OPTIONS (REMOTE_OBJECT 'SwissProt.Features');

SwissFT

A unique nickname that is used to identify the BioRS databank.

ID VARCHAR(32) OPTIONS (ELEMENT_NAME '_ID_')

The name and data type for a table column. The ELEMENT_NAME column option is specified for the ID column.

The ELEMENT_NAME column option specifies the BioRS element name. The case sensitivity of this name depends on the case sensitivity of the BioRS server and on the value of the CASE_SENSITIVE server option. You need to specify the BioRS element name only if it is different from the column name. Column option values must be enclosed in single quotation marks.

In general, use the ELEMENT_NAME column option under the following circumstances:

• When a BioRS element name contains characters, such as periods and spaces, that do not conform to valid federated syntax. For example, if your databank has an element named Pub.Date, you cannot use the element name as the column name. Characters such as periods and spaces are not supported. You must map the element name to a valid column name.
• When the syntax of a BioRS element name does not conform to standards that you or your organization have established for your federated system. For example, if your organization has established that the conventions for schemas, nicknames, and columns must include a prefix, a BioRS element name might not be able to be used as a column name.
• When the BioRS element name might not be obvious to federated users.

ALLTEXT VARCHAR(128)

The name and data type for a table column.

ENTRYDATE VARCHAR(64)

The name and data type for a table column.

FtLength VARCHAR(16)

The name and data type for a table column.

SERVER biors1

The name that you assigned to the BioRS server in the CREATE SERVER statement.

OPTIONS (REMOTE_OBJECT 'SwissProt.Features')

Specifies the name of the BioRS databank that is associated with the nickname. This name determines the schema and the BioRS databank for the nickname. This name also specifies the relationship of the nickname to other nicknames.

The case sensitivity of this name depends on the case sensitivity of the BioRS server and on the value of the CASE_SENSITIVE server option.

Important: You cannot change or delete this name with the ALTER NICKNAME statement. If the name of the BioRS databank changes, you must delete and then create the nickname again.

You must specify the REMOTE_OBJECT nickname option when the name of a BioRS databank does not conform to valid federated syntax. In this example, the databank name "SwissProt.Features" does not conform for several reasons. The databank name contains a character, a period, that is not valid federated syntax and contains a mixture of uppercase and lowercase letters.

In general, use the REMOTE_OBJECT nickname option under the following circumstances:

• When a BioRS databank name contains characters, such as periods and spaces, that do not conform to valid federated syntax. You must map the databank name to a valid federated name.
• When the case sensitivity of a BioRS databank name does not conform to standards that you or your organization have established for your federated system. For example, if your organization has established that the conventions for schemas, nicknames, and columns must include a prefix, a BioRS databank name might not be able to be used a name.
• When the BioRS databank name might not be obvious to federated users.