3 Case Studies
The following case studies for Informix demonstrate some of the features of the Oracle Database Gateway. You can verify that the gateway is installed and operating correctly by using the demonstration files included on the distribution media.
The demonstration files are automatically copied to disk when the gateway is installed.
Topics:
3.1 Case Descriptions
The cases illustrate:
-
A simple query (Case 1)
-
A more complex query (Case 2)
-
Joining Informix tables (Case 3)
-
Write capabilities (Case 4)
-
A data dictionary query (Case 5)
-
The pass-through feature (Case 6)
3.2 Distribution Media Contents
The distribution media contains the following:
-
Demonstration files
-
One SQL script file that creates the demonstration tables in the Informix database
-
One SQL script file that drops the demonstration tables from the Informix database
3.3 Demonstration Files
After a successful gateway installation, use the demonstration files stored in the directory $ORACLE_HOME/dg4ifmx/demo
where $ORACLE_HOME
is the $ORACLE_HOME
directory under which the gateway is installed. The directory contains the following demonstration files:
3.4 Demonstration Requirements
The case studies assume these requirements have been met:
-
The gateway demonstration tables are installed in the Informix database
-
The Oracle database has an account named
SCOTT
with a password ofTIGER
-
The Oracle database has a database link called
GTWLINK
(set up as public or private to the userSCOTT
) which connects the gateway to a Informix database asSCOTT
with passwordTIGER2
For example, you can create the database link as follows:
SQL> CREATE DATABASE LINK GTWLINK CONNECT TO SCOTT 2 IDENTIFIED BY TIGER2 USING 'GTWSID';
-
Oracle Net Services is configured correctly and running.
-
The Informix environment variable,
INFORMIXDIR
, is set correctly.
3.5 Creating Demonstration Tables
The case studies are based on the GTW_EMP
, GTW_DEPT
, and GTW_SALGRADE
tables. If the demonstration tables have not been created in the Informix database, use the bldifmx.sql
script to create them, as follows:
Set environment variable DELIMIDENT
.
If you have the Bourne or Korn Shell, enter the following:
$ DELIMIDENT = y; export DELIMIDENT
If you have the C Shell, enter the following:
$ setenv DELIMIDENT y $ cd $ORACLE_HOME/dg4ifmx/demo$ dbaccess database_name bldifmx.sql
The script creates the demonstration tables in the Informix database accordingly:
CREATE TABLE GTW_EMP ( EMPNO SMALLINT NOT NULL ENAME VARCHAR(10), JOB VARCHAR(9), MGR SMALLINT, HIREDATE DATETIME, SAL NUMERIC(7,2), COMM NUMERIC(7,2), DEPTNO SMALLINT) CREATE TABLE GTW_DEPT ( DEPTNO SMALLINT NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13)) CREATE TABLE GTW_SALGRADE ( GRADE MONEY, LOSAL NUMERIC(9,4), HISAL NUMERIC(9,4))
3.5.1 Demonstration Table Definitions
The following table definitions use information retrieved by the SQL*PLUS DESCRIBE
command:
GTW_EMP
Name Null? Type ------------------------------- -------- ---- EMPNO NOT NULL NUMBER(5) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(5) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(5)
GTW_DEPT
Name Null? Type ------------------------------- -------- ---- DEPTNO NOT NULL NUMBER(5) DNAME VARCHAR2(14) LOC VARCHAR2(13)
GTW_SALGRADE
Name Null? Type ------------------------------- -------- ---- GRADE NUMBER(19,4) LOSAL NUMBER(9,4) HISAL NUMBER(9,4)
3.5.2 Demonstration Table Contents
The contents of the Informix tables are:
GTW_EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ----- --- --- -------- --- ---- ------ 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESPERSON 7698 20-FEB-81 1600 300 30 7521 WARD SALESPERSON 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESPERSON 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESPERSON 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10
GTW_DEPT
DEPTNO DNAME LOC ----- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
GTW_SALGRADE
GRADE LOSAL HISAL ------ ------ ----- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999
3.6 Case 1: Simple Queries
Case 1 demonstrates the following:
-
A simple query.
-
A simple query retrieving full date information.
The first query retrieves all the data from GTW_DEPT
and confirms that the gateway is working correctly. The second query retrieves all the data from GTW_EMP
including the time portion of the hire date because the default date format was set to DD-MON-YY HH24:MM:SS
for the session by an ALTER SESSION
command.
3.8 Case 3: Joining Informix Tables
Case 3 demonstrates the following:
-
Joins between Informix tables.
-
Subselects.
The query retrieves information from three Informix tables and relates the employees to their department name and salary grade, but only for those employees earning more than the average salary.
3.9 Case 4: Write Capabilities
Case 4 is split into three cases and demonstrates the following:
3.10 Case 5: Data Dictionary Query
Case 5 demonstrates data dictionary mapping. It retrieves all the tables and views that exist in the Informix database that begin with "GTW
".