Creating Data Objects Using Update Schema
You can create objects in OneData from existing DDL files in the work area (STG) using the Update Schema feature. You can also execute the DDL directly in the data repository and create corresponding objects in OneData.
Update Schema creates the table in the Release area, if it does not already exist, and adds audit columns to the tables in both the Work area and Release area. The update schema option also automatically imports any comments created in the remote database for version of Oracle 9 and above.
For example, you can create a view in the database and then use Update Schema to create an object.
Note: You can create only data objects using the Update Schema feature.
Important: When you perform an Update Schema action from SQL Server, make sure that you create the base table using SQL Server data types supported in OneData. For example, when you add a table that has a Varchar column in the work area to OneData through an Update Schema action, the table that is created in the release area has the column data type NVarchar. To avoid this, make sure the table in Work area is created with SQL Server data types supported in OneData (in this case, NVarchar).
In SQL Server, if the ID column is numeric in the external system, after you execute the update schema, you must change the data type of the ID column to Database Sequence. This is necessary because OneData reads this column as a numeric column when you add an object using Update Schema.
To create a data object using update schema
1. If the table that you want to import in OneData already exists in the Work area schema, skip to step 5. To execute the DDL file directly in the external system, on the Menu toolbar, click Define > Structures > Import DDL.
2. Choose the connection in Select Connection.
3. Click Next.
OneData displays the DDL statements in the file.
4. Click Run to execute the statements in the file.
The table is created in the remote database.
5. On the Menu toolbar, click Define > Objects.
6. Click Update Schema.
A list of available tables in the Work area (STG) that are not yet imported into OneData are displayed under Available Tables.
7. Select the required table from Available Tables.
Note: Select Auto generate column caption to convert the caption of the source columns into uppercase.
8. Click Get Table Structure.
A screen with the information imported from the database is displayed.
9. Click Next.
10. In the object definition screen, click Object Type as Data Object and enter other details.
11. Click Save.