Conference orarep::nomahs::dbintegrator_public_public

    
    Hi Dave,
    
    There are a couple of ways of accomplishing what your customer wants to
    do.  It is probably worth asking a few questions though:
    
    Would she like to schedule transfers on an ongoing basis, or is once in
    a while by hand OK?
    
    Is she willing to do the DDL for the Rdb database herself (to make the
    tables that looks like the access tables)?
    
    If she wants to do the transfers on some scheduled basis then she may
    want to look into data distributor (or as it is now known: The
    Replication Option for Rdb).  If she wants the DDL done automatically,
    then she also would want data distributor also.
    
    If it is an infrequent activity and she doesn't mind creating the
    tables then this is something that she can do with the PC Data Gateway
    and Rdb using multiple attaches and insert into (select from) syntax.
    
    Hope this helps,
    
    Dan

    Thanks Dan!

    Hi Dan,
    
    	  The customer would like some documentation, and unfortunately by
    reading note 2.4 I no longer have access to the kits. I have asked the 
    customer to call ORACLE who claim that the product will not be
    available till JUNE.
    
    	  Is there any documentation that I can make available to the
    customer? They would really like to trial the product, but I can see
    that is impossible at this stage in the sale/move.
    
    	  Can you please give me a pointer to some documentation? Just to 
    keep the customer interested till June.
    
    					Cheers,
    
    						Dave
    

Dave,
     The PC Data Gateway was released as a V3.1A product.  There
was no new documentation released at the time.  All the PC Data
Gateway relevant information can be found in:
	- The Before You Install letter
	- The Release Notes
	- The interactive help that can be accessed by the
	  components that run on the PC
	- The README file that is on the PC (duplicate of
	  some of the release notes).

     The PC Data Gateway works quite well for accomplishing
just what your user has in mind, ie moving data from MS Access
to Rdb.  There are a few steps involved in getting things setup
which, to the uninitiated, may seem tedious.  However, I think
that they are pretty well outlined in the documents I mentioned
above.  In a nut shell, assuming that no ODBC drivers are present
on the PC and assuming that TCP/IP is installed and configured
on the PC, one must:
	- Do a VMS (or Unix) install of the DBI PC Gateway
	- Rename and copy 1 or 2 files (depending on version)
          from the Vax (or Alpha) down to the PC.  These files
	  contain the PC Data Gateway and the ODBC data drivers
	  needed to access the various PC data sources, (like
	  MS Access.)
	- Execute the data drivers file on the PC.  This causes
  	  the file to "explode", ie it spits out lots of other files
	  when you run it.
	- Run the data driver setup that was spit out above.
	- Run the ODBC Administrator that the previous step installed.
	  With it create a named, ODBC data source to access her MS
	  Access database.
	- Execute the PC Data Gateway file on the PC.  Like the ODBC
	  drivers file, it explodes into the parts that comprise the
	   PC Data Gateway PC kit.
	- Run the setup that was just spit out to install PC Data Gateway.
	- Run the PC Data Gateway Agent

That's about it for the PC side.  Now user can go to the vax or alpha,
start up SQL and attach (or using DBI, create a link to) the ODBC data
source that was created several steps above.  The attach string will
look something like:
	/type=ODBC/node=HERPC/database=HER_ODBC_DATASOURCE_NAME
There may be other stuff like /dbuser=admin etc. depending on
the particular datasource being accessed.

Once one attaches to the ODBC data source as outlined above, there will
be lots of activity on the PC as the agent launches an "executor" which
will carry out all of the SQL requests.  User can now do stuff like
SHOW TABLES
and
SELECT * FROM foo
and so forth.

Assuming that the table to be moved is HER_TABLE,
now is a good time to do a SHOW TABLE HER_TABLE.
This will tell you the SQL names and datatypes to use
when creating the destination table in Rdb.

Next, while still in SQL do a DISCONNECT ALL;
and attach to the Rdb database that is to receive
the table.  Using cut and paste from the SHOW TABLE HER_TABLE
above, create a corresponding table in the Rdb database.

	create table HER_TABLE (
		col1		type1,
		col2		type2,
		...
		coln		typen );

commit;
disconnect all;


Once PC data gateway is up and running, the actual transfer to Rdb is quite
easy.  It could be accomplished with or without DBI.  Since this is the DBI
notes file you get stuck with the DBI version.  (Actually, if I knew
what I was doing I would have directed you to create the Rdb version
of HER_TABLE as a DBI pass through table -- but....)

Assuming that user has a DBI catalog already setup, attach
to is using /TYPE=DBI etc.  Create a link to the PC data Gateway MS Access
data source.  Using above example it would be done something like:

create link PC_LINK to '/type=ODBC/node=HERPC/database=HER_ODBC_DATASOURCE_NAME';

Then create a link to the destination Rdb database

create link RDB_LINK to '/type=rdb/database=HER_RDB_DATABASE';

Now create a virtual instance of the MS Access table to be transferred:

create table HER_PC_TABLE link to HER_TABLE using PC_LINK;

If you do a SHOW TABLE HER_PC_TABLE you will see the same table
you did when you did the SHOW TABLE HER_TABLE above.  It just
has a different names.  In fact you can run queries against this
table if you like
	select count(*) from HER_PC_TABLE;

Next create a virtual instance of the Rdb table we created several
steps above:

create table HER_RDB_TABLE link to HER_TABLE using RDB_LINK;


To do the actual transfer:

insert into HER_RDB_TABLE ( select * from HER_PC_TABLE);

COMMIT;

This turned out to be more then a "nut shell", and I fear that I've left
out some critical steps (like creating a DBI catalog if user is not
already a DBI user).  I strongly recommend that your user read the
available documentation.  But this should serve as a relatively complete
outline of what needs to be done.

    
    I've gone and sent Dave the byi, release notes, and install guide.