AWM Connection Methods

Connecting to a database using Analytic Workspace Manager (AWM) seems to cause some interesting postings on OTN. Why? Mainly because AWM provides two different connection methods and each method has its own requirements:

• JDBC - this uses the normal host:port:sid connection format and this is I suspect how most people connect since this is the way AWM is typically demonstrated
  

• TNS - this uses either the full TNS protocol string or references a TNS entry in the TNSNAMES.ORA file.

so let's look at these methods in a bit more detail:

Creating a JDBC Connection
This is the easiest method to use since AWM is configured out of the box to use JDBC connections. Connecting to a database using JDBC is very straightforward. After launching AWM, right-mouse click on the node "Database" and select "Add Database to tree", as shown here:


The connection dialog provides prompts to enter a descriptive label and the connection information. For a JDBC connection this is simply the hostname, the port for the database listener and the database SID. This is the information shown here:



Once you have supplied this information the database will be added to the tree and then you can connect to your chose database instance and provide a user name and password, as shown here:


The alternative method is to use a TNS entry and this method always seems to cause errors. Typical errors are:

• AWM simply aborts with no error message or warning
• OLAPI exception error stating : Unable to resolve type "SYS.SQLOLAPIEXCEPTION"
• An unexpected exception has been detected in native code outside the VM....... Library=D:\Oracle\product\10.2.0\db_1\BIN\ocijdbc10.dll

Creating a TNS connection

Firstly, we need to change the way AWM is typically launched.

Trapping errors with AWM
To get diagnostic information, to trap any errors not shown in the AWM GUI, I always recommend using the AWMC.EXE file. This launches a DOS command window that can be used to track error messages. With the 10.2.0.3A version of AWM there are some instances where the GUI will just simply crash or hang with no visible error messages. In this case if you try to use a TNS connection method, when AWM connects to the database instance and tries to retrieve the list of available AWs it simply aborts with no warning and the DOS command window disappears. To resolve this I created a batch file called AWM.BAT which launches AWM by calling awmc.exe. Running this from a command line window allows me to see all the relevant error messages.

Using a TNS connection
To connecting via the TNS method requires some additional steps in terms of configuration that might not be totally clear. The main problem appears to be the lack of any error messages if you make a mistake. If you get the basic connection string wrong, AWM will give you a reasonable error message that points you in the right direction (" TNS:could not resolve the connect identifier specified...."). However, as we all probably have lots of different Oracle products installed on our desktops/laptops, AWM is able to find, without any prompting, some of the files it needs to make a TNS connection and this is what causes the problem.

So which files does AWM need to make a TNS connection?

It needs a database client installation to be run to install the SQLNet layer. This will then provide the necessary DLLs etc to support a SQLNet connection. At this point, this is where AWM can go wrong and just simply crash without warning.

To make a TNS connection you can either reference one of the entries in the TNSNAMES.ORA file or you can paste in the full TNS connection string, such as:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=klaker-uk.uk.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=beans)))

into the connection dialog box instead of the JDBC connection string. If you want to reference an entry in the TNSNAMES, then assuming you have multiple Oracle homes, I would recommend setting the TNS_ADMIN environment variable so you know which TNSNAMES.ORA is being used. If you do not specify this environment variable the ORACLE_HOME environment variable will be used to source the TNSNAMES.ORA file. Therefore, you need to make sure you have the ORACLE_HOME environment variable set as a minimum before you start AWM.

Using the above batch file to run AWM, I added some additional environment variable statements as follows:

set TNS_ADMIN=D:\oracle\product\10.2.0.1\db_1\NETWORK\ADMIN
set PATH=D:\oracle\awm\awm\jre\bin;D:\oracle\product\10.2.0.1\db_1\bin;
set CLASSPATH=D:\oracle\awm\awm\jre\bin
set ORACLE_HOME=D:\oracle\product\10.2.0.1\db_1
call awmc.exe

In this case I have referenced my 10gR2 database installation. This, however, does cause an error when AWM tries to return a list of available AWs for my TNS connection. An error log is now created that contains the following information:

An unexpected exception has been detected in native code outside the VM.
Unexpected Signal : EXCEPTION_ACCESS_VIOLATION (0xc0000005) occurred at PC=0x61D35968
Function=xaolog+0x6338
Library=D:\oracle\product\10.2.0\db_1\bin\OraClient10.Dll

Current Java thread:
at oracle.jdbc.driver.T2CStatement.t2cParseExecuteDescribe(Native Method)
at oracle.jdbc.driver.T2CPreparedStatement.executeForDescribe(T2CPreparedStatement.java:518)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1030)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1123)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3284)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3328)
- locked <0x1002eaf8> (a oracle.jdbc.driver.T2CPreparedStatement)
- locked <0x102312c8> (a oracle.jdbc.driver.T2CConnection)
at oracle.olap.awm.util.jdbc.SQLWrapper.execute(SQLWrapper.java:184)
at oracle.olap.awm.util.jdbc.SQLWrapper.execute(SQLWrapper.java:62)
at oracle.olap.awm.businessobject.aw.WorkspaceBO.getWorkspacesOwnedBySchemaInStandardForm(WorkspaceBO.java:200)
at oracle.olap.awm.navigator.node.WorkspaceFolderNode.getChildren(WorkspaceFolderNode.java:111)
at oracle.olap.awm.navigator.node.BaseNodeModel.refreshData(BaseNodeModel.java:74)
at oracle.olap.awm.navigator.node.BaseNodeModel.dTreeItemExpanding(BaseNodeModel.java:221)
at oracle.bali.ewt.dTree.DTreeDeferredParent.__fireExpansionChanging(Unknown Source)
at oracle.bali.ewt.dTree.DTreeDeferredParent.setExpanded(Unknown Source)
at oracle.olap.awm.navigator.node.BaseNode.expandHelper(BaseNode.java:2185)
- locked <0x100159f8> (a java.lang.Object)
at oracle.olap.awm.navigator.node.BaseNode.access$400(BaseNode.java:109)
at oracle.olap.awm.navigator.node.BaseNode$ExpansionThread.run(BaseNode.java:2135)

Dynamic libraries:
0x00400000 - 0x0041B000 D:\oracle\awm\awm\bin\awmc.exe
0x7C900000 - 0x7C9B0000 C:\WINDOWS\system32\ntdll.dll
0x7C800000 - 0x7C8F5000 C:\WINDOWS\system32\kernel32.dll
0x7E410000 - 0x7E4A0000 C:\WINDOWS\system32\USER32.dll
0x77F10000 - 0x77F57000 C:\WINDOWS\system32\GDI32.dll
0x76390000 - 0x763AD000 C:\WINDOWS\system32\IMM32.DLL
0x77DD0000 - 0x77E6B000 C:\WINDOWS\system32\ADVAPI32.dll
0x77E70000 - 0x77F01000 C:\WINDOWS\system32\RPCRT4.dll
0x629C0000 - 0x629C9000 C:\WINDOWS\system32\LPK.DLL
0x74D90000 - 0x74DFB000 C:\WINDOWS\system32\USP10.dll
0x77C10000 - 0x77C68000 C:\WINDOWS\system32\msvcrt.dll
0x08000000 - 0x08138000 D:\oracle\awm\awm\jre\bin\client\jvm.dll
0x76B40000 - 0x76B6D000 C:\WINDOWS\system32\WINMM.dll
0x10000000 - 0x10007000 D:\oracle\awm\awm\jre\bin\hpi.dll
0x00A20000 - 0x00A2E000 D:\oracle\awm\awm\jre\bin\verify.dll
0x00A30000 - 0x00A49000 D:\oracle\awm\awm\jre\bin\java.dll
0x00A50000 - 0x00A5D000 D:\oracle\awm\awm\jre\bin\zip.dll
0x03D70000 - 0x03E7F000 D:\oracle\awm\awm\jre\bin\awt.dll
0x73000000 - 0x73026000 C:\WINDOWS\system32\WINSPOOL.DRV
0x774E0000 - 0x7761D000 C:\WINDOWS\system32\ole32.dll
0x5AD70000 - 0x5ADA8000 C:\WINDOWS\system32\uxtheme.dll
0x03E80000 - 0x03ED0000 D:\oracle\awm\awm\jre\bin\fontmanager.dll
0x755C0000 - 0x755EE000 C:\WINDOWS\system32\msctfime.ime
0x038C0000 - 0x038DE000 D:\oracle\awm\awm\jre\bin\jpeg.dll
0x62F00000 - 0x62F13000 D:\oracle\product\10.2.0\db_1\BIN\ocijdbc10.dll
0x045D0000 - 0x04629000 D:\oracle\product\10.2.0\db_1\BIN\OCI.dll
0x7C340000 - 0x7C396000 C:\WINDOWS\system32\MSVCR71.dll
0x76BF0000 - 0x76BFB000 C:\WINDOWS\system32\PSAPI.DLL
0x61C20000 - 0x61E76000 D:\oracle\product\10.2.0\db_1\bin\OraClient10.Dll
0x60870000 - 0x60957000 D:\oracle\product\10.2.0\db_1\bin\oracore10.dll
0x60A80000 - 0x60B4B000 D:\oracle\product\10.2.0\db_1\bin\oranls10.dll
0x63690000 - 0x636A8000 D:\oracle\product\10.2.0\db_1\bin\oraunls10.dll
0x60EB0000 - 0x60EB7000 D:\oracle\product\10.2.0\db_1\bin\orauts.dll
0x71AB0000 - 0x71AC7000 C:\WINDOWS\system32\WS2_32.dll
0x71AA0000 - 0x71AA8000 C:\WINDOWS\system32\WS2HELP.dll
0x636B0000 - 0x636B6000 D:\oracle\product\10.2.0\db_1\bin\oravsn10.dll
0x60FA0000 - 0x61093000 D:\oracle\product\10.2.0\db_1\bin\oracommon10.dll
0x60300000 - 0x6086C000 D:\oracle\product\10.2.0\db_1\bin\orageneric10.dll
0x63430000 - 0x63457000 D:\oracle\product\10.2.0\db_1\bin\orasnls10.dll
0x63750000 - 0x638C6000 D:\oracle\product\10.2.0\db_1\bin\oraxml10.dll
0x04640000 - 0x04651000 C:\WINDOWS\system32\MSVCIRT.dll
0x60960000 - 0x60A73000 D:\oracle\product\10.2.0\db_1\bin\oran10.dll
0x62740000 - 0x6277E000 D:\oracle\product\10.2.0\db_1\bin\oranl10.dll
0x62790000 - 0x627A7000 D:\oracle\product\10.2.0\db_1\bin\oranldap10.dll
0x627F0000 - 0x628FC000 D:\oracle\product\10.2.0\db_1\bin\orannzsbb10.dll
0x62530000 - 0x62583000 D:\oracle\product\10.2.0\db_1\bin\oraldapclnt10.dll
0x62670000 - 0x6268B000 D:\oracle\product\10.2.0\db_1\bin\orancrypt10.dll
0x71AD0000 - 0x71AD9000 C:\WINDOWS\system32\WSOCK32.dll
0x77120000 - 0x771AB000 C:\WINDOWS\system32\OLEAUT32.dll
0x62920000 - 0x6296D000 D:\oracle\product\10.2.0\db_1\bin\oranro10.dll
0x626B0000 - 0x626B7000 D:\oracle\product\10.2.0\db_1\bin\oranhost10.dll
0x62660000 - 0x62666000 D:\oracle\product\10.2.0\db_1\bin\orancds10.dll
0x04660000 - 0x04668000 D:\oracle\product\10.2.0\db_1\bin\orantns10.dll
0x04670000 - 0x049D6000 D:\oracle\product\10.2.0\db_1\bin\orapls10.dll
0x049E0000 - 0x049E9000 D:\oracle\product\10.2.0\db_1\bin\oraslax10.dll
0x63080000 - 0x63284000 D:\oracle\product\10.2.0\db_1\bin\oraplp10.dll
0x61ED0000 - 0x61F6A000 D:\oracle\product\10.2.0\db_1\bin\orahasgen10.dll
0x62AB0000 - 0x62B1F000 D:\oracle\product\10.2.0\db_1\bin\oraocr10.dll
0x62B20000 - 0x62B66000 D:\oracle\product\10.2.0\db_1\bin\oraocrb10.dll
0x5B860000 - 0x5B8B4000 C:\WINDOWS\system32\NETAPI32.dll
0x62980000 - 0x62990000 D:\oracle\product\10.2.0\db_1\bin\orantcp10.dll
0x63520000 - 0x635BA000 D:\oracle\product\10.2.0\db_1\bin\orasql10.dll
0x77FE0000 - 0x77FF1000 C:\WINDOWS\system32\Secur32.dll
0x71A50000 - 0x71A8F000 C:\WINDOWS\System32\mswsock.dll
0x76F20000 - 0x76F47000 C:\WINDOWS\system32\DNSAPI.dll
0x76FB0000 - 0x76FB8000 C:\WINDOWS\System32\winrnr.dll
0x76F60000 - 0x76F8C000 C:\WINDOWS\system32\WLDAP32.dll
0x751D0000 - 0x751EE000 C:\WINDOWS\system32\wshbth.dll
0x77920000 - 0x77A13000 C:\WINDOWS\system32\SETUPAPI.dll
0x04CF0000 - 0x04D15000 C:\Program Files\Bonjour\mdnsNSP.dll
0x76D60000 - 0x76D79000 C:\WINDOWS\system32\Iphlpapi.dll
0x76FC0000 - 0x76FC6000 C:\WINDOWS\system32\rasadhlp.dll
0x662B0000 - 0x66308000 C:\WINDOWS\system32\hnetcfg.dll
0x71A90000 - 0x71A98000 C:\WINDOWS\System32\wshtcpip.dll
0x71F80000 - 0x71F84000 C:\WINDOWS\system32\security.dll
0x77C70000 - 0x77C93000 C:\WINDOWS\system32\msv1_0.dll
0x76C90000 - 0x76CB8000 C:\WINDOWS\system32\imagehlp.dll
0x59A60000 - 0x59B01000 C:\WINDOWS\system32\DBGHELP.dll
0x77C00000 - 0x77C08000 C:\WINDOWS\system32\VERSION.dll

Heap at VM Abort:
Heap
def new generation total 2176K, used 226K [0x10010000, 0x10260000, 0x12770000)
eden space 1984K, 6% used [0x10010000, 0x100313e0, 0x10200000)
from space 192K, 48% used [0x10230000, 0x102475c0, 0x10260000)
to space 192K, 0% used [0x10200000, 0x10200000, 0x10230000)
tenured generation total 27488K, used 21294K [0x12770000, 0x14248000, 0x30010000)
the space 27488K, 77% used [0x12770000, 0x13c3b850, 0x13c3ba00, 0x14248000)
compacting perm gen total 15616K, used 15595K [0x30010000, 0x30f50000, 0x34010000)
the space 15616K, 99% used [0x30010000, 0x30f4ad60, 0x30f4ae00, 0x30f50000)

Local Time = Mon Dec 31 09:52:15 2007
Elapsed Time = 17
#
# The exception above was detected in native code outside the VM
#
# Java VM: Java HotSpot(TM) Client VM (1.4.2_03-b02 mixed mode)
#

Notice the error is with the OraClient10.dll file. Doing a search across all my Oracle software installations I found multiple copies of this file, with different file sizes. The file in the database home/bin directory was 2348Kb. The file in my OWB10gR2 directory was 1877Kb. Switching the batch file to point to my OWB home directory to use that OraClient10.dll file resolved the connection problem:

set TNS_ADMIN=D:\oracle\OWB10gHome\NETWORK\ADMIN
set PATH=D:\oracle\awm\awm\jre\bin;D:\oracle\OWB10gHome\bin;
set CLASSPATH=D:\oracle\awm\awm\jre\bin
set ORACLE_HOME=D:\oracle\OWB10gHome
call awmc.exe

Therefore, it would appear that the latest database version (10.2.0.3) of the OraClient10.dll file is somehow incompatible with the latest version of AWM10.2.0.3A. Not sure why, but I have logged a bug to try and resolve this.

To summarize, if you want to define a database connection in AWM based on a TNS connection name or TNS string do the following:

1) Make sure you have a database Client installation (or equivalent, such as OWB) that provides 2) SQLNet
Create a batch file to run AWM
3) Add the following environment variables to your batch file:

• TNS_ADMIN to point to your TNSNAMES.ORA file
• or enter the TNS connect string in full ((DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=............................)))
• ORACLE_HOME to point to your database client installation or equivalent.

in addition, to avoid other possible conflicts I also set the following:

• CLASSPATH - limited to just AWM
• PATH - limited to just the ORACLE_HOME and AWM

With all this in place, everything should work as normal. and if you do get an error it should be recorded in the DOS command window, which will not be closed if you call it directly from a command prompt.