UC Davis Information & Educational Technology

Campus Data Warehouse Connection Instructions

Quick Links

Connection Instructions

  1. Oracle LDAP Service (recommended)
    Requirements: Oracle 8i, 9i, or 10g clients

    Your sqlnet.ora file should contain the following lines: 

            NAMES.DEFAULT_DOMAIN = ucdavis.edu
        NAMES.DIRECTORY_PATH = (LDAP, ONAMES, TNSNAMES)
    You will also need to create a new file in the same directory as sqlnet.ora called "ldap.ora". This file should contain the following lines. Please make sure there is not an extra space before any of the three lines below. Extra spaces are ignored in sqlnet.ora file, but an extra space in ldap.ora will cause it to fail.

            DIRECTORY_SERVERS= (oraldap1.ucdavis.edu:389:636,oraldap2.ucdavis.edu:389:636)
        DEFAULT_ADMIN_CONTEXT = "dc=ucdavis,dc=edu"
        DIRECTORY_SERVER_TYPE = OID
    This is our recommended method for connecting.

  2. Oracle Names Server
    Requirements: Oracle 8i or 9i clients (not available for 10g clients)

    Your sqlnet.ora file should contain the following lines:

            NAMES.DIRECTORY_PATH = (ONAMES, TNSNAMES)
        NAMES.PREFERRED_SERVERS =
          (ADDRESS_LIST =
            (ADDRESS = (COMMUNITY = TCP.ucdavis.edu)(PROTOCOL = TCP)(HOST = oranames1.ucdavis.edu)(PORT = 1522))
            (ADDRESS = (COMMUNITY = TCP.ucdavis.edu)(PROTOCOL = TCP)(HOST = oranames2.ucdavis.edu)(PORT = 1522))
          )
        
        NAMES.DEFAULT_DOMAIN = ucdavis.edu
    If you are not using a 10g client this is probably what you are already using. If you are using this method you don't need to make any changes. You will start connecting to the new CDW database automatically. (We highly recommend that you switch to start using method #1 (LDAP Services) above since the Oracle Names server will be deprecated in the near future.)

  3. TNSNAMES.ORA ( NOT recommended)
    Requirements: Oracle 8i, 9i or clients 10g clients

    Your sqlnet.ora file should contain the following lines:

            NAMES.DIRECTORY_PATH = (TNSNAMES)
    Your tnsnames.ora file in the same directory as sqlnet.ora file should be having the below entry for CDW database.

    	isods_prod.ucdavis.edu =
  		(DESCRIPTION =
	        (ADDRESS =
	         (COMMUNITY = TCP.ucdavis.edu)
	          (PROTOCOL = TCP)
	          (Host = dytiscus.ucdavis.edu)
	          (Port = 1521)
	        )
	      (CONNECT_DATA =
	        (SID = isodprod)
	           (GLOBAL_NAME = isods_prod.ucdavis.edu)
	      )
	  )

Software and Installation/Setup Instructions


Map Q:\ drive to the AFS-proxy server:
If you already have a drive mapped to the AFS server or AFS-proxy server, you do not need to map the Q:\ drive to the AFS-proxy server, just substitute your own letter drive for the "Q:\" in the paths mentioned in the various connection instructions.
  1. Before beginning, seek assistance from your technical support staff if you do not have the NetBEUI networking protocol installed or do not know how to check your network properties to see if it is installed.
  2. Open "Windows Explorer" or "My Computer"
  3. Go to the "Tools" menu and choose "Map Network Drive..."
  4. In the drop-down box next to "Drive," choose letter "Q"
  5. In the "Path" box, type "\\proxy-afs.ucdavis.edu\desktop"
  6. Click the "OK" button
  7. ATTENTION: Seek assistance from your technical support staff if you are using a firewall and seem to be unable to connect to the Q:\ drive. It might be necessary to temporarily disable the firewall.
SqlNet:
  1. If you are upgrading the version of SqlNet, it is a good idea to back up any SQLNET.ORA and TNSNAMES.ORA files present in the client's computer. This will allow going back to the previous version in case problems surface.
  2. Map network drive letter "Q:\" to the AFS-proxy server , if necessary.
  3. Copy file 10201_client_win32.zip from Q:\WISDM\Tools\OracleClients to your file system.
  4. Extract/unzip the above file
  5. Locate the file setup.exe from the extracted files and double click it. Oracle Universal Installer: Welcome window is displayed
  6. Click Next. Oracle Universal Installer: Select Installation Type window is displayed
  7. Select 'Custom' and click Next. Oracle Universal Installer: Specify Home Details window is displayed
  8. Accecpt the defaults and click Next. Oracle Universal Installer: Available Product Components window is displayed. Oracle Client 10.2.0.10 is selected and grayed out.
  9. Select 'Oracle Windows Interfaces 10.2.0.1.0'. Some sub components inside are auto selected
  10. Deselect all of them except Oracle ODBC Driver 10.2.0.1.0 and click Next. Oracle Universal Installer: Product-Specific Prerequisite Checks window is displayed. If everything was OK, should see status 'Succeeded'.
  11. Click Next. Oracle Universal Installer: Summary window is displayed listing all the components selected for installation.
  12. Click Install. Installation begins
  13. Click Exit once installation is complete
  14. Copy the files sqlnet.ora and ldap.ora from Q:\WISDM\Tools\OracleClients to 'Admin' subdirectory within the installation directory structure. If you accepted the defaults in step 8 then it should be 'C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN'.
Microsoft ODBC for Oracle:
  1. Make sure SqlNet is installed. If not, follow the SqlNet instructions above.
  2. For WindowsNT, open Settings>Control Panel>Data Sources(ODBC). For Windows2000, open Settings>Control Panel>Administration>Data Sources(ODBC).
  3. Click on the "System DSN Tab"
  4. Click on the "Add" button
  5. Choose "Microsoft ODBC for Oracle." If this is not an option, you will need to download the appropriate MDAC that contains the drivers you need from http://www.microsoft.com/data/download.htm
  6. The "Microsoft ODBC for Oracle Setup" window will appear.
    • Data Source Name = isods_prod
    • Description = Campus Data Warehouse Repository
    • User Name = your username
    • Server = isods_prod
  7. You should see the name of the new driver in your System DSN list. Click the "OK" button.
  8. ATTENTION MS Access 97 Users: Find the msjet35.dll file, right click on it, choose "Properties," and then click on the "Version" tab to see what version of the file you have. If the version is not at least 3.51.2723.0, please download Microsoft Jet 3.5 and follow the directions carefully. This patch will allow you to successfully place limits on columns from linked tables with outside joins.

JDBC Connection:

Preferred Method:

  1. Setup a sqlnet.ora file or it's equivalent to point to our Oracle nameservers, oranames1 and oranames2:
    • NAMES.PREFERRED_SERVERS =
    • (ADDRESS_LIST =
    • (ADDRESS =
    • (COMMUNITY = TCP.ucdavis.edu)
    • (PROTOCOL = TCP)
    • (Host = oranames1.ucdavis.edu)
    • (Port = 1522)
    • )
    • (ADDRESS =
    • (COMMUNITY = TCP.ucdavis.edu)
    • (PROTOCOL = TCP)
    • (Host = oranames2.ucdavis.edu)
    • (Port = 1522)
    • )
    • )
  2. Use the "isods_prod.ucdavis.edu" connect string in your setup. The nameserver then resolves that to the host, port and sid.

Alternate Method:

Explicitly define the host, port and sid. You should be aware that if for some reason we make any changes, you will need to change your configuration as well.

    • Host: dytiscus.ucdavis.edu
    • Port: 1521
    • SID: isodprod

Specific Instructions for Mac OSX:

  1. Go to http://otn.oracle.com/software/tech/java/sqlj_jdbc/index.html
  2. Follow the instructions to download and install the Oracle 9i/Thin JDBC driver for Mac OSX
  3. Use the classes.zip driver located in the jdbc_thin_9_0_1/jdbc/lib/ folder created during the previous step in conjunction with the instructions of the SQL query program to add the newly installed driver to the collection of drivers that the program can use.
  4. When connecting to the Campus Data Warehouse using the JDBC protocol, use the following information:
    • Host: dytiscus.ucdavis.edu
    • Port: 1521
    • SID: isodprod