Feedback
These examples include a comprehensive set of sample queries that you can use to access BioRS data sources and show you the statements that are necessary to create the nicknames that are used in the examples.
CREATE NICKNAME swiss
(
ID CHAR (30) OPTIONS (ELEMENT_NAME '_ID_'),
EntryDate VARCHAR (15),
Update CLOB (15),
Description CLOB (15),
Crossreference CLOB (15),
Authors CLOB (15),
Journal VARCHAR (15),
JournalIssue VARCHAR (15),
PublicationYear CLOB (15),
PublicationTitle CLOB (15),
Gene CLOB (15),
Remarks CLOB (15),
RemarkType VARCHAR (15),
CatalyticActivity VARCHAR (15),
CoFactor VARCHAR (15),
Disease VARCHAR (15),
Function CLOB (15),
Pathway VARCHAR (15),
Similarity CLOB (15),
Complex VARCHAR (15),
FtKey VARCHAR (15),
FtDescription CLOB (15),
FtLength VARCHAR (15),
MolWeight CHAR (15),
Protein_Length VARCHAR (15),
Sequence CLOB (15),
AccNumber VARCHAR (15),
Taxonomy CLOB (15),
Organelle VARCHAR (15),
Organism VARCHAR (15),
Keywords VARCHAR (15),
Localization VARCHAR (15),
FtKey_count VARCHAR (15),
AllText CLOB (15)
)
FOR SERVER biors_server
OPTIONS (REMOTE_OBJECT 'swissprot');CREATE NICKNAME swissft
(
ID VARCHAR (30) OPTIONS (ELEMENT_NAME '_ID_'),
FtKey VARCHAR (15),
FtLength VARCHAR (15),
FtDescription VARCHAR (15),
Parent VARCHAR (30) OPTIONS (REFERENCED_OBJECT 'swissprot'),
AllText CLOB (15)
)
FOR SERVER biors_server
OPTIONS (REMOTE_OBJECT 'swissprot.features');The queries and results in the following table show how you can structure your queries to optimize the workload between the federated system and the BioRS server.
| Query | Result |
|---|---|
| SELECT s.id FROM Swiss s WHERE BIORS.CONTAINS(s.id, '100K_RAT') = 1 FETCH FIRST 3 ROWS ONLY; | |
| SELECT s.id FROM Swiss s WHERE s.id LIKE '%100K_RAT%' FETCH FIRST 3 ROWS ONLY; | |
Both of the queries in this table produce the same results. However, the first query runs much faster than the second query. The first query uses the BIORS.CONTAINS function to specify the input predicate. As a result, the BioRS server selects the data in the SwissProt databank, then passes the selected data to the federated server. In the second query, the LIKE input predicate is specified directly on the swiss nickname. As a result, the BioRS server transfers the entire SwissProt databank to the federated server. After the databank contents are transferred, the federated server then selects the data.
Query performance is typically much better when predicates are sent to the data source for processing.
The queries and results in the following table show the use of wildcard characters in the BIORS.CONTAINS custom function. All of the query results are identical, even though different wildcard characters are used.
| Query | Result |
|---|---|
| SELECT s.crossreference FROM Swiss s WHERE BIORS.CONTAINS(s.crossreference, 'MEDLINE') = 1 FETCH FIRST 3 ROWS ONLY; | |
| SELECT s.crossreference FROM Swiss s WHERE BIORS.CONTAINS(s.crossreference, '?ED?IN?') = 1 FETCH FIRST 3 ROWS ONLY; | |
| SELECT s.crossreference FROM Swiss s WHERE BIORS.CONTAINS(s.crossreference, '*D*N*') = 1 FETCH FIRST 3 ROWS ONLY; | |
The queries and results in the following table show how you can access information in BioRS Author data type elements with the BIORS.CONTAINS custom function. The syntax of all of the queries is nearly identical. The only difference is the presence or absence of the first initial in the query term, and the amount of space between the first name and the last initial.
| Query | Result |
|---|---|
| SELECT s.authors FROM Swiss s WHERE BIORS.CONTAINS(s.authors, 'Mueller') = 1 FETCH FIRST 3 ROWS ONLY; | |
| SELECT s.authors FROM Swiss s WHERE BIORS.CONTAINS(s.authors, 'Mueller,D') = 1 FETCH FIRST 3 ROWS ONLY; | |
| SELECT s.authors FROM Swiss s WHERE BIORS.CONTAINS(s.authors, 'Mueller ,D') = 1 FETCH FIRST 3 ROWS ONLY; | |
| SELECT s.authors FROM Swiss s WHERE BIORS.CONTAINS(s.authors, 'Mueller, D') = 1 FETCH FIRST 3 ROWS ONLY; | |
The queries and results in the following table who how you can access information in BioRS Date type elements with the BIORS.CONTAINS custom function.
When a BioRS Date type field contains a sequence of dates, the results can contain extra information, as shown in the second example in the table. BioRS Numeric data type elements (Date and Number) can contain multiple values. Therefore, the results of the queries that are run on BioRS Date or Number elements can also contain multiple values. Multiple values are always separated by spaces.
| Query | Result |
|---|---|
| SELECT e.entrydate FROM embl e WHERE BIORS.CONTAINS(e.entrydate, date('11/01/1997') ) = 1 FETCH FIRST 3 ROWS ONLY; | |
| SELECT g.update FROM gen g WHERE BIORS.CONTAINS(g.update, date('11/01/1997') ) = 1 FETCH FIRST 3 ROWS ONLY; | |
The queries and results in the following table show how you can use the BIORS.CONTAINS_LE and the BIORS.CONTAINS_GE custom functions.
| Query | Result |
|---|---|
| SELECT s.molweight FROM Swiss s WHERE BIORS.CONTAINS_LE(s.molweight, 100368) = 1 FETCH FIRST 3 ROWS ONLY; | |
| SELECT s.molweight FROM Swiss s WHERE BIORS.CONTAINS_GE(s.molweight, 100368) = 1 FETCH FIRST 3 ROWS ONLY; | |
| SELECT s.journalissue FROM Swiss s WHERE BIORS.CONTAINS_GE(s.journalissue, 172) = 1 FETCH FIRST 3 ROWS ONLY; | |
The queries and results in the following table show how you can use the BIORS.SEARCH_TERM custom function to specify a search term using the BioRS format.
| Query | Result |
|---|---|
| SELECT s.publicationyear FROM Swiss s WHERE BIORS.SEARCH_TERM (s.id, '[PublicationYear EQ number:1997;]')=1 FETCH FIRST 10 ROWS ONLY; | |
| SELECT s.molweight FROM Swiss s WHERE BIORS.SEARCH_TERM (s.id, '[MolWeight EQ number:100368;]') = 1 FETCH FIRST 10 ROWS ONLY; | |
| SELECT s.molweight FROM Swiss s WHERE BIORS.SEARCH_TERM (s.id, '[MolWeight GREATER number:100368;]') = 1 FETCH FIRST 10 ROWS ONLY; | |
The following query shows how to use relational predicates to form an equijoin between two databanks that have a parent-child relationship:
SELECT s.id, f.id, f.parent FROM Swiss s, Swissft f
WHERE (f.parent = s.id) FETCH FIRST 10 ROWS ONLY;ID ID PARENT
-------------------- ------------------ ------------------------------
100K_RAT 100K_RAT.1 swissprot:100K_RAT
100K_RAT 100K_RAT.2 swissprot:100K_RAT
100K_RAT 100K_RAT.3 swissprot:100K_RAT
100K_RAT 100K_RAT.4 swissprot:100K_RAT
100K_RAT 100K_RAT.5 swissprot:100K_RAT
100K_RAT 100K_RAT.6 swissprot:100K_RAT
100K_RAT 100K_RAT.7 swissprot:100K_RAT
100K_RAT 100K_RAT.8 swissprot:100K_RAT
100K_RAT 100K_RAT.9 swissprot:100K_RAT
104K_THEPA 104K_THEPA.1 swissprot:104K_THEPA
10 record(s) selected.