WIXLIB

Rocket UniVerseExternal Database Access (EDA)Version 11.2.5July2015UNV-1125-EDA-1

NoticesEditionPublication date: July2015Book number: UNV-1125-EDA-1Product version: Rocket UniVerse V11.2.5Copyright Rocket Software, Inc. or its affiliates 1985-2015. All Rights Reserved.TrademarksRocket is a registered trademark of Rocket Software, Inc. For a list of Rocket registered trademarksgo to: www.rocketsoftware.com/about/legal. All other products or services mentioned in thisdocument may be covered by the trademarks, service marks, or product names of theirrespective owners.ExamplesThis information might contain examples of data and reports. The examples include the names ofindividuals, companies, brands, and products. All of these names are fictitious and any similarityto the names and addresses used by an actual business enterprise is entirely coincidental.License agreementThis software and the associated documentation are proprietary and confidential to RocketSoftware, Inc. or its affiliates, are furnished under license, and may be used and copied only inaccordance with the terms of such license.Note: This product may contain encryption technology. Many countries prohibit or restrict theuse, import, or export of encryption technologies, and current use, import, and exportregulations should be followed when exporting this product.2

Corporate informationRocket Software, Inc. develops enterprise infrastructure products in four key areas: storage,networks, and compliance; database servers and tools; business information and analytics; andapplication development, integration, and modernization.Website: www.rocketsoftware.comRocket Global Headquarters77 4th Avenue, Suite 100Waltham, MA 02451-1468USATo contact Rocket Software by telephone for any reason, including obtaining pre-salesinformation and technical support, use one of the following telephone numbers.CountryUnited yJapanNetherlandsNew ZealandSouth AfricaUnited KingdomToll-free telephone 0-980-8180800-520-0439Contacting technical supportThe Rocket Customer Portal is the primary method of obtaining support. If you have currentsupport and maintenance agreements with Rocket Software, you can access the Rocket CustomerPortal and report a problem, download an update, or find answers in the U2 Knowledgebase. Tolog into the Rocket Customer Portal or to request a Rocket Customer Portal account, go towww.rocketsoftware.com/support.In addition to using the Rocket Customer Portal to obtain support, you can send email tou2support@rocketsoftware.com or use one of the following telephone numbers.CountryNorth AmericaUnited Kingdom/FranceEurope/AfricaAustraliaNew ZealandToll-free telephone number 1 800 729 3553 44(0) 800 773 771 or 44(0) 20 8867 3691 44 (0) 20 88673692 1 800 707 703 or 61 (0) 29412 5450 0800 505 5153

Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta Beta BetaTable ofContentsTable of ContentsChapter 1Chapter 1: External Database Access (EDA)External Database Access . . . . . . . . .First normal form (1NF) vs. non-first normal formTable concepts. . . . . . . . . . . . .Representing multivalues . . . . . . .Associations . . . . . . . . . . . .I-descriptors . . . . . . . . . . . .Mapping example . . . . . . . . . . .Chapter Chapter 2: The EDA Schema ManagerThe EDA Schema Manager steps . . . . .The EDA Schema Manager . . . . . . .Create a new UniVerse server connection .Connect to UniVerse server . . . . .Managing connections . . . . . . . . .About data sources . . . . . . . .Defining a data source . . . . . . .Selecting EDA schema files . . . . . . .Selectively mapping attributes . . . .View EDA server details . . . . . .View UniVerse server details . . . . .View options . . . . . . . . . .Verifying the EDA schema . . . . . . .Viewing the EDA schema . . . . . .Viewing the DDL scripts . . . . . . . .Converting data . . . . . . . . . . .Viewing EDA files. . . . . . . . .Listing and selecting data . . . . . . . .Listing data using RetrieVe. . . . . .Listing data using UniVerse SQL. . . .C:\U2Doc\UniVerse\11.2\Source\EDA\EDATOC.fm (bookTOC.template)March 17, 2014 12:08 pm.

Chapter 3Chapter 3: External database drivers supplied with EDAEDA Oracle driver . . . . . . . . . . . . .Set up the EDA environment. . . . . . . .Set up the Oracle connection file . . . . . .Set up dynamic-loading library . . . . . . .Create ORACLEPATH Environment Variable . .Create the EDA data source . . . . . . . .Oracle data type mapping . . . . . . . .EDA DB2 driver. . . . . . . . . . . . . .Set up the EDA environment. . . . . . . .Install DB2 or the DB2 client . . . . . . . .Set up connection to the DB2 database . . . .Create the EDA data source . . . . . . . .DB2 data type mapping . . . . . . . . .EDA SQL Server driver . . . . . . . . . . .Install SQL Server and create ODBC data source .Create the EDA data source . . . . . . . .Set up the EDA configuration file . . . . . .SQL Server data types . . . . . . . . . .EDA SQL Server driver for UNIX and Linux . . . .Set up the EDA environment. . . . . . . .Install unixODBC and third-party ODBC driver .Set up connection to external database . . . .Set up the ODBC dynamic loading library path .Create the EDA data source . . . . . . . .Automatic data type mapping . . . . . . .Chapter 4-104-114-134-154-16Chapter 4: External Database Access Driver APIExternal Database Access Driver APIRegistering an EDA driver . . . .EDA Driver functions . . . . . .EDADRV LoadSymbols . . .EDADRV Connect . . . . .EDADRV Disconnect . . . .EDADRV EndTransaction . .EDADRV PrepareStmt. . . .EDADRV ExecuteStmt. . . .EDADRV CloseStmt . . . .EDADRV DropStmt . . . .5 External Data Access (EDA).

EDADRV FetchStmt . .EDADRV Perform . .EDADRV GetEDAAttr .EDADRV GetErrmsg. .EDADRV Cleanup . .EDADRV FreeResult . .EDADRV GetDBInfo . .EDADRV GetSpecialInfoECL Command . . . . .EDA Driver log files . . . .The EDA Driver header file .Chapter necting to the external database. . . . . .Disconnecting from the external database. . . .Converting UniVerse data to an external database .VERIFY.EDAMAP . . . . . . . . . . . .LIST.EDAMAP . . . . . . . . . . . . .SAVE.EDAMAP . . . . . . . . . . . .SELECT.EDA.NONCONFORMING . . . . .Retrieving information about the EDA driver . .5-25-35-35-45-55-75-85-9Chapter 5: EDA ECL commandsChapter 6Chapter 6: EDA exception handlingChapter 7Chapter 7: EDA ReplicationEDA Replication . . . . . . . . . . .Setting up a server . . . . . . . . .Create a new UniVerse server connection .Defining a data source . . . . . . .Defining EDA replication parameters . .Chapter 8. . . . . . . . 7-2. . . . . . . . 7-3. . . . . . . . 7-4. . . . . . . . 7-8. . . . . . . . 7-13Chapter 8: EDA best practicesMap selected fields . . . . . . . . .Avoid multiple multivalued associations . .Avoid restrictive data types . . . . . .RECORD BLOB . . . . . . . . . .Updating an EDA tile from the external database. . . . . . . . . . .8-18-18-18-28-3Table of Contents 6

ChapterChapter 1: External DatabaseAccess (EDA)External Database Access . . . . . . . . . . .First normal form (1NF) vs. non-first normal form. . .Table concepts . . . . . . . . . . . . . . .Representing multivalues . . . . . . . . . .Associations . . . . . . . . . . . . . .I-descriptors . . . . . . . . . . . . . .Mapping example . . . . . . . . . . . . . rch 17, 2014 12:08 pm. . . . . . . . . . . . . . .11-21-31-41-41-51-51-7

External Database AccessExternal Database Access (EDA) enables you to convert data stored in theUniVerse database to a 1NF database, such as Microsoft SQLServer, thenaccess that data using existing UniVerse BASIC programs, RetrieVe, orUniVerse SQL.Note: EDA was not designed to access data that already resides in a 1NF database.To access this type of data, use the UniVerse SQL Client Interface (BCI).You must create an EDA Map Schema to define the translation betweenUniVerse and the external database model, which may differ from theUniVerse model. Additionally, the UniVerse dictionary record does not fullydescribe the data it defines. For example, the UniVerse dictionary record maynot define the data type.In order to use EDA, you must have the external database client installed onthe machine where you are running UniVerse. In addition, you must be ableto access the external database server where you want the data to resideusing that client.1-2 External Database Access (EDA)

First normal form (1NF) vs. non-first normal formMany relational databases, including DB2, Oracle, and SQL Server, follow theFirst Normal Form (1NF) data model. In this model, the relation is consideredto be 1NF if and only if each attribute of the relation is atomic, meaning thateach column must contain only a single value, and each row must contain thesame columns.UniVerse follows the nested relational, or Non-First Normal Form model,referred to as NF2. This model enables you to store data in singlevalued ormultivalued attributes, avoiding data redundancy.First normal form (1NF) vs. non-first normal form 1-3

Table conceptsThis section is provided to help you understand how the EDA SchemaManager generates tables on an external database, such as DB2, Oracle, orSQL Server, so you can plan your mapping strategy. The EDA SchemaManager imposes rules on creating, modifying, and dropping tables.Representing multivaluesTo represent the two nested levels of data within UniVerse files or UniVerseSQL tables (singlevalued, and multivalued, the EDA Schema Managercreates two types of tables, one for each nested level: Singlevalued attributes (S) – a table that represents all singlevaluedattributes. In this document this table is also called the primary table.For each association: Multivalued attributes (MV) – a table containing multivalued attributes of the association.These tables are “linked” through primary and foreign keys.Note: Each nonassociated multivalued attribute is mapped to a single externaldatabase table linked to the primary table through the primary and foreign keys.Primary and foreign keysThe primary and foreign keys establish the same data relationship betweentables as associations do in UniVerse files or UniVerse SQL tables.The purpose of a primary key is to specify one or more attributes whose datavalues uniquely identify each row of a table.The purpose of a foreign key is to represent a hierarchical, or parent/child,relationship between two tables. For example, a table containing multivaluedattributes is the child of the primary table. The foreign key to this table pointsto the primary key of the parent, or primary, table.1-4 External Database Access (EDA)