Stelo Technical Documents

Cookbook: Using StarSQL/Linux with the Oracle Database Gateway for ODBC

Last Update: 15 January 2025
Product: StarSQL
Version: 5.x or later
Article ID: SQV00SQ069

Abstract

The Oracle Database Gateway for ODBC (DG4ODBC), combined with StarSQL, makes it possible to integrate DB2 data with Oracle applications.

This streamlined cookbook documents the procedure for installing & configuring StarSQL/Linux for use with the Database Gateway for ODBC on an Oracle Cloud Infrastructure (OCI) DB System or other Linux-based environments.

This document consolidates information from the following Stelo documents. If you encounter an error, search for the symptoms in the Stelo Info Center, as it is likely the solution is documented in one of the following.

• StarSQL for UNIX User's Guide   
• StarSQL for UNIX Quick Start Guide 
• Troubleshooting Tips for StarSQL for UNIX 
• Using StarSQL with the Oracle Database Gateway for ODBC 
• StarLicense Server for UNIX User’s Guide 
• StarLicense for UNIX Quick Start Guide 
• How to Add StarLicense to the Boot Process 

 

Solution

• Prerequisites
• Bind packages with StarAdmin
  
• Download and install StarLicense
  
• Download and install StarSQL
  
• Set up DG4ODBC

Prerequisites

• Confirm that you can access the DB System using ssh and the ssh keys that you supplied or generated when you created the DB System.
  
  e.g.from your own Linux system. Note that the private key must be mode 700 (accessible only by the owner).

$ ssh -i ./ssh-key-date.key [opc@IP-address]

• After connecting with ssh, determine the OS level:

$ cat /etc/oracle-release
Oracle Linux Server release 8.8

This procedure was tested on Oracle Linux 8.8. If you have an older DB System, it may be running Oracle Linux 7.x, in which case it should be updated or a new DB system created. If it is newer (e.g. Oracle Linux 9.x) some of the procedure may differ slightly (e.g. autostart of StarLicense Service).

• Use the following command to list the internal IP addresses and send the output to Stelo Support for generating a Stelo license:

$ ifconfig -a | grep "inet " | grep -v 127.0.0.1 | awk '{print $2 }'

Bind packages with StarAdmin

On a workstation with a GUI environment, use StarAdmin to create SQL packages on the host with StarAdmin, . StarAdmin is delivered as a separate installer, and can be uninstalled after completing this task.

See Binding StarSQL Packages Using StarAdmin.

In general, we recommend installing StarAdmin on a Windows workstation.

If you are currently using a much older version of StarSQL elsewhere in your environment and want to avoid disrupting a production environment, we recommend binding packages into a separate schema (library/collection) and specifying that schema name as the Package Collection ID when configuring an ODBC data source.

Download and install StarLicense

Create a directory for the installers and make a note of currently installed packages:

$ mkdir PACKAGES
$ cd PACKAGES
$ rpm -a -q > todays_date.txt

Fetch and expand the StarLicense installer using the URL supplied by Stelo Support:

$ wget https://URL-path/starlicense-linux64-rpm.tar
$ mkdir starlicense
$ cd starlicense
$ tar xf ../starlicense-linux64-rpm.tar

Install and configure the software as root

$ sudo sh
# ./setup

At the end of setup, you can administer the StarLicense server. You can run /usr/share/starlicense64/configure at a later time as well.

• Chose option 2 Add license with License Key
• Enter the license supplied by Stelo Support.
  
• Choose Option 4 to start the service
  
• Choose Option 7 to test checkout
  

Run the following commands to configure the StarLicense service to start automatically when Linux boots:

# ln -s /usr/share/starlicense64 /usr/share/starlicense
# /sbin/chkconfig --add sqlicd

Download and install StarSQL

• Fetch and expand the StarSQL installer using the URL supplied by Stelo Support:

$ cd $HOME/PACKAGES
$ wget https://URL-path/starsql64-version.x86_64.rpm

• Install unixODBC and StarSQL:

$ sudo sh
# yum install unixODBC
# rpm -i starsql64-6.50.0419-1.x86_64.rpm

• Determine the locale of the Linux user who will be invoking StarSQL (e.g. oracle) and create a symbolic link in the StarSQL nls directory if necessary. Failure to do so will result in blank error messages.

# su - oracle
$ echo $LANG
en_US.utf-8
# sudo sh
# cd /opt/stelo/starsql64/nls/
# ln -s en_US.UTF-8 en_US.utf-8

• Edit /etc/odbc.ini to create a system data source. Additional samples can be found in /opt/stelo/starsql64/etc/odbc.ini.

[MYSYS]
Server=MYRDB
Driver=StarSQL (64-bit)
HostName=myhost or myIP
Port=myport (e.g. 446 or 50000)
PkgColId=STARSQL
IsolationLevel=2

• Test the connection and address any issues. If you do encounter an issue, search for the symptom in the Stelo Info Center.

$ isql -v MYSYS userID password

 

Set up DG4ODBC

The example below shows using the same name (capitalized) for the ODBC data source, the dg4odbc ORA file, and the DBLink.

All the steps below are performed as user oracle.

• Determine the values of ORACLE_HOME environment variable, the domain name of the Oracle global name, the PDB of interest, and the value to use for HS_LANGUAGE:

# sudo su - oracle

$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0.0/dbhome_1

$ sqlplus
user: sys as sysdba
password: supply password created during setup
(or use another userID with adequate permissions)

SQL> select * from global_name;
mydb.SUB08091805550.VCN1.ORACLEVCN.COM
(we are interested in the domain name)

SQL> select name, open_mode from v$pdbs;

SQL> select * from NLS_DATABASE_PARAMETERS;

Use the values of NLS_LANGUAGE, NLS_TERRITORY, and NLS_CHARACTERSET to determine the value of HS_LANGUAGE e.g.

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8MSWIN1252

results in HS_LANGUAGE=american_america.we8mswin1252

• Create & edit a config file starting with the sample initdg4odbc.ora
  

$ cd $ORACLE_HOME/hs/admin
$ cp initdg4odbc.ora initMYSYS.ora
$ vi initMYSYS.ora

# This is the name of the ODBC DSN in /etc/odbc.ini
HS_FDS_CONNECT_INFO = MYSYS

# Other values for HS_FDS_TRACE_LEVEL are OFF, ON, DEBUG
# The trace files are created in hs/log
# You may need to change write permissions on this directory
HS_FDS_TRACE_LEVEL = 4

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE=american_america.we8mswin1252
# (or another appropriate value - see above)

# HS_DB_NAME should match that of the DBLINK to be created
HS_DB_NAME=MYSYS
HS_DB_DOMAIN=my_oracle_domain e.g. SUB08091805550.VCN1.ORACLEVCN.COM

and comment out ODBCINI and envvar.

• Edit tnsnames.ora, appending the sample in the hs/admin directory:
  

vi tnsnames.ora
:$
:r ../../hs/admin/tnsnames.ora.sample

Change the name of the new entry and the SID from dg4odbc to MYSYS

MYSYS =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=MYSYS))
(HS=OK)
)

• Use the command lsnrctl status to identify the location of the listener.ora file. In most cases, we have found it in network/admin subdirectory of the oracle userID's $ORACLE_HOME.
  
  However, on an Oracle 23 system created using ASM (Automatic Storage Management) rather than LVM (Logical Volume Manager), listener.ora was located in the $ORACLE_HOME directory of the grid userID. In that case, you will need to perform the following steps as user grid, adjusting as necessary e.g. use the absolute path to listener.ora.sample rather than the relative path. Note that the contents of listener.ora.sample include $ORACLE_HOME specified as that of user oracle; that should not be changed.
  
  In addition, we added the sample as the second line of listener.ora rather than appending to the end; otherwise the existing listener entries did not appear in the output of lsnrctl status, although they appear to function.
  
  Alternately, you can create and start a local listener in the $ORACLE_HOME of the userID oracle.
  
  If necessary, change the permissions of $ORACLE/hs/log so that user grid has write privileges:
  
  $ cd $ORACLE_HOME/hs; chmod 777 log
  
  
• Edit the listenter.ora, appending the sample in the hs/admin directory:
  

$ cd $ORACLE_HOME/network/admin
$ vi listener.ora
:$ (go to the end of the file)
:r ../../hs/admin/listener.ora.sample

Change SID_NAME=dg4odbc to SID_NAME=MYSYS. Do not change PROGRAM=dg4odbc.

• Refresh the listener:

$ lsnrctl reload

• Enter lsnrctl status to verify that the entry you just added to listener.ora appears.
• If you performed the listener configuration as user grid, switch back to user oracle now; run lsnrctl status in that environment as well.
• Create the DBLINK (typically in the PDB) and test:
  

$ sqlplus
user: sys as sysdba
SQL> alter session set container=MYDB_PDB1;
SQL> CREATE PUBLIC DATABASE LINK MYSYS CONNECT TO "myUser" IDENTIFIED BY "myPWD" USING 'MYSYS';

SQL> SELECT * FROM MYSCHEMA.MYTABLE@MYSYS;

• After completing testing, edit $ORACLE_HOME/hs/admin and reduce the value of HS_FDS_TRACE_LEVEL to OFF, and if you changed the permissions of hs/log, revert the change
  
  $ cd $ORACLE_HOME/hs; chmod 755 log