Problem Statement

How can a query (in the Aspen SQLplus Query Writer) be run against a *DIFFERENT* Aspen InfoPlus.21 database without using the Aspen SQLplus Query Writer menu commands 'Query' | 'Host' to change the connection to a different IP.21 system?  In other words, how can a command be run to talk to a different InfoPlus.21 by just changing the syntax of the query to designate the name of a different IP.21 system?

Solution

An ODBC connection must be made to the alternate system.  Following that, an additional database link must be set up in the SQLplus Query Writer's Tables tool.  Finally, the syntax of the query must be adjusted to utilize the new database link.  Here are additional details:

Note:  The following example is performed on a 64-bit Windows Server 2012 R2 running a 64-bit version of Aspen InfoPlus.21.

Establish ODBC connection to alternate system

1.  In the Windows Start menu search using 'ODBC 64' and select either of the two choices that appear:



2.  In the ODBC Data Source Administrator switch to the 'System DSN' tab then select the 'Add...' button on the right.



3.  Select AspenTech SQLplus on the next screen and then click 'Finish':



4.  On the screen that appears fill in a name and (optionally) a description and then click 'Advanced':



5.  On the 'SQLplus Advanced Setup' screen unselect the 'Use Aspen Data Sources (ADSA) choice and click 'OK':



6.  Back on the Setup screen the bottom of the box will have changed.  Fill in the name of the remote IP.21 system and the port number (default port number is 10014 though it may need to be adjusted according to the TSK_SQL_SERVER settings on the remote system).  The name of our remote system is HigginsBoat in this example.  (Optional) If you click the 'Test' button a message indicating 'Connection Test OK' should be returned.



Set up database link in Query Writer

7.  Open the SQLplus Query Writer and bring up the Tables wizard (use this button): 

8.  Click on 'Add Link' button on the right:



9.  Fill out the 'SQLplus Database Link' screen and click OK and then dismiss the Tables wizard:



Adjust Query to use new link

10.  Open the SQLplus Query Writer and adjust the query to use the new link name.  The syntax is:

          "LinkName".table_name

For example - the following query can be used to get a count of the number of IP_ANALOGDEF records present on the system (the system to which we are currently connected - called Liberator):



The query could be adjusted to make the same request of the remote system (HigginsBoat):



Note that the double-quotes around the data source name are required in certain circumstances (if there are spaces or special characters).  They are not required here specifically but their presence does not have a negative impact.

Keywords

select count(name) from IP_ANALOGDEF;
select count(name) from "HigginsBoat".IP_ANALOGDEF;
DSN
Data Source Name