Converting From Universe to SQL
Converting From Universe to SQL
This is a topic for an orphaned message.
Jacqui,
Every tool Ive tried (apart from DataStage) comes to grief trying to export schema definitions from UniVerse, because none of the tools knows about multi-valued columns or how to deal with them. DataStage does, obviously, but lets not go there for the moment.
You may be able to create one of your own by executing appropriate SELECT statements on the UniVerse SQL Catalog, in which there are five tables of interest here; UV_SCHEMA, UV_TABLES, UV_COLUMNS, UV_ASSOC and (possible) UV_VIEWS.
The only time Ive done this without DataStage, I got UniVerse working as required for ODBC access (legal names, empty-to-null conversion, views based on saved Select Lists, identifying those tables and columns that would be visible to ODBC clients, etc.), then attached MS Access to it via linked tables (select all tables). The schema definition in Access shows 1NF (first normal form) tables, with the multi-valued columns (or associations of them - think of these as nested table definitions) showing up as separate tables in the 1NF environment. This schema definition can then be grabbed by most tools. MS Access also has a cute tool for drawing a pictorial representation of the relationships between tables.
If you do have DataStage its somewhat easier - DataStage can import the table definitions including the multi-valued column definitions. However, it does not garner any information about the relationships between tables. DataStage can export these table definitions into almost any other database (via its Reporting Assistant), and a these could be grabbed by any tool, but the relationships between base tables and nested tables will not be understood.
I suspect this doesnt help much. I am posting this back to the list, and cross-posting to the DataStage list, in case theres a tool out there of which I am unaware but someone else is.
Regards,
Ray
-----Original Message-----
From: Jacqui Caren [mailto:jacqui.caren@ig.co.uk]
Sent: Friday, 02 February 2001 11:27
To: ray.wurlod@informix.com
Cc: Donna Linsdey; Jacqui Caren
Subject: Re: Converting From Universe to SQL
Hello I found your posting (below) and wondered which tools you may have used to export schema definitions from universe.
My tool of preference is Erwin but I cannot determine which database could best match Universe for schema extraction? Any suggestions or recommendations?
FYI: we have been asked to develop a web interface to a proprietary Universe app and plan upon using a perl/DBI/SQL based interface to the database to implement what should hopefully be a clean (and simple) set of reports.
We foresee no problem with perl or DBI - the one unknown at the mo is the structure of their DB. Having been involved with data extraction/reporting against a number of nasties in the past we do not want to assume the schema is clean...
Any advice of comments will be deeply appreciated.
Jacqui Caren
Subject: Re: Converting From Universe to SQL
Date: 11/01/2000
Author: Ray Wurlod
Its not necessary, because UniVerse is an SQL-capable RDBMS if you want it to be.
At release 9.4 and later you can use all DML statements against regular files. In current development are the capabilities to use some DDL statements, without needing to convert the account into a schema.
If you DO convert an account to a schema (I have done quite a few of these now), there is no impact at all on regular operations, though there are a couple of extra things you need to do if restoring from backup media to a different location.
In short, its really straightforward (particularly at later releases of
UniVerse) and it works.
Jim Thomas wrote in message ... >Anyone been through or attempting to do this type of conversion before? > >
Jacqui Caren, Ingram Group Ltd. jacqui@ig.co.uk
ph: +44 (0) 1483 8628xx main=00 fax=01 ddi=65 http://www.ig.co.uk/ http://www.sitedirector.org/ http://www.perl.co.uk/
Every tool Ive tried (apart from DataStage) comes to grief trying to export schema definitions from UniVerse, because none of the tools knows about multi-valued columns or how to deal with them. DataStage does, obviously, but lets not go there for the moment.
You may be able to create one of your own by executing appropriate SELECT statements on the UniVerse SQL Catalog, in which there are five tables of interest here; UV_SCHEMA, UV_TABLES, UV_COLUMNS, UV_ASSOC and (possible) UV_VIEWS.
The only time Ive done this without DataStage, I got UniVerse working as required for ODBC access (legal names, empty-to-null conversion, views based on saved Select Lists, identifying those tables and columns that would be visible to ODBC clients, etc.), then attached MS Access to it via linked tables (select all tables). The schema definition in Access shows 1NF (first normal form) tables, with the multi-valued columns (or associations of them - think of these as nested table definitions) showing up as separate tables in the 1NF environment. This schema definition can then be grabbed by most tools. MS Access also has a cute tool for drawing a pictorial representation of the relationships between tables.
If you do have DataStage its somewhat easier - DataStage can import the table definitions including the multi-valued column definitions. However, it does not garner any information about the relationships between tables. DataStage can export these table definitions into almost any other database (via its Reporting Assistant), and a these could be grabbed by any tool, but the relationships between base tables and nested tables will not be understood.
I suspect this doesnt help much. I am posting this back to the list, and cross-posting to the DataStage list, in case theres a tool out there of which I am unaware but someone else is.
Regards,
Ray
-----Original Message-----
From: Jacqui Caren [mailto:jacqui.caren@ig.co.uk]
Sent: Friday, 02 February 2001 11:27
To: ray.wurlod@informix.com
Cc: Donna Linsdey; Jacqui Caren
Subject: Re: Converting From Universe to SQL
Hello I found your posting (below) and wondered which tools you may have used to export schema definitions from universe.
My tool of preference is Erwin but I cannot determine which database could best match Universe for schema extraction? Any suggestions or recommendations?
FYI: we have been asked to develop a web interface to a proprietary Universe app and plan upon using a perl/DBI/SQL based interface to the database to implement what should hopefully be a clean (and simple) set of reports.
We foresee no problem with perl or DBI - the one unknown at the mo is the structure of their DB. Having been involved with data extraction/reporting against a number of nasties in the past we do not want to assume the schema is clean...
Any advice of comments will be deeply appreciated.
Jacqui Caren
Subject: Re: Converting From Universe to SQL
Date: 11/01/2000
Author: Ray Wurlod
Its not necessary, because UniVerse is an SQL-capable RDBMS if you want it to be.
At release 9.4 and later you can use all DML statements against regular files. In current development are the capabilities to use some DDL statements, without needing to convert the account into a schema.
If you DO convert an account to a schema (I have done quite a few of these now), there is no impact at all on regular operations, though there are a couple of extra things you need to do if restoring from backup media to a different location.
In short, its really straightforward (particularly at later releases of
UniVerse) and it works.
Jim Thomas wrote in message ... >Anyone been through or attempting to do this type of conversion before? > >
Jacqui Caren, Ingram Group Ltd. jacqui@ig.co.uk
ph: +44 (0) 1483 8628xx main=00 fax=01 ddi=65 http://www.ig.co.uk/ http://www.sitedirector.org/ http://www.perl.co.uk/