1 INTEGRATE Makes definitions in a database and in a repository correspond by changing definitions in either the database or the repository. The INTEGRATE statement can also create database definitions in the repository by copying from a database file to a specified repository. 2 Environment You can issue the INTEGRATE statement only in interactive SQL. 2 Format INTEGRATE ---+ +------------+ ++-> DATABASE --+ | +------------+ | ++--> FILENAME -> -> CREATE PATHNAME -> +-> | +--> PATHNAME -> +-+-> ALTER FILES ------+---------+ | | +-> ALTER DICTIONARY -+ +-> DOMAIN --> -----+ +-> TABLE ---> ------+ domain-name = -+---------------------------+-> ---> +-+-> -+-> . -+ +-> -------+ table-name = -+-------------------------+-> +-> +-+-> +> . -+ | | +-> ------+ | +---> " " --------------+ 2 Arguments 3 DATABASE_FILENAME_CREATE_PATHNAME Stores existing database system file definitions in the repository for the first time. Use the INTEGRATE DATABASE FILENAME clause if you did not specify PATHNAME or the repository was not installed when you created the database. If you use the INTEGRATE DATABASE FILENAME clause, the repository database node specified in the path name must not exist. If older repository definitions do exist with the path name you are specifying, specify a different repository path name, placing the new database definitions elsewhere. The file-name clause is the full or partial file specification that specifies the source of the database definitions. You do not need to specify the file extension. The database system automatically uses the database root file ending with the .rdb file extension. Path-name-2 is the repository path name for the repository where the INTEGRATE statement creates the database definitions (using the database system files as the source). You can specify either a full repository path name or a relative repository path name. This must be the path name, not the name of the database itself. 3 DATABASE_PATHNAME_ALTER_FILES Alters any table and domain definitions created with the CREATE TABLE FROM statement or the CREATE DOMAIN FROM statement so they match their sources in the repository. The INTEGRATE . . . ALTER FILES statement has no effect on definitions not created with the FROM clause. This is useful if the database file definitions no longer match the definitions in the repository. Path-name-1 is the repository path name for the repository database that is the source for altering the definitions in the database. You can specify either a full repository path name or a relative repository path name. CAUTION Using the ALTER FILES clause may destroy data associated with definitions in your database file if those definitions are not defined in your repository. In this situation, you will lose real data. For this reason, use the ALTER FILES clause with caution. 3 DATABASE_PATHNAME_ALTER_DICTIONARY Alters the database definitions in the dictionary so they are the same as those in the database. This is useful if repository definitions no longer match the definitions in the database file. Note, though, that altering database definitions in the repository may affect other applications that refer to these definitions. The repository must already exist and may contain definitions. Path-name-1 is the repository path name for the repository database that SQL alters using the definitions in the database file as a source. You can specify either a full repository path name or a relative path name. 3 DOMAIN_ALTER_FILES Alters the domain definitions in the database to match the field definitions in the repository. Collating sequences referenced by the domain and columns that are based on the domain and the tables that contain them may also be altered if they have changed in the repository. 3 DOMAIN_ALTER_DICTIONARY Alters the field definitions in the repository to match the domain definitions in the database. Collating sequences referenced by the domain and columns that are based on the domain and the tables that contain them may also be altered if they have changed in the database. 3 TABLE_ALTER_FILES Alters the table definitions in the database to match the record definitions in the repository. Other objects referencing the table or that are referenced by it and have changed definition in the repository may be altered. These other objects are: o Domains o Collating sequences o Other referenced tables and columns o Foreign key constraints and check constraints o Indexes o Views that reference the table o Storage maps and storage areas referenced by an index 3 TABLE_ALTER_DICTIONARY Alters the record definitions in the repository to match the table definitions in the database. Other objects referencing the table or that are referenced by it and have changed definitions in the database may be altered. These other objects are: o Fields o Collating sequences o Other referenced records and fields o Foreign key constraints and check constraints o Indexes 2 Examples The following example shows how to use the INTEGRATE statement with the ALTER FILES clause. In this example, fields (domains) are defined in the repository. Then, using SQL, a table is created based on the repository definitions. Subsequently, the repository definitions are changed so the definitions in the database file and the repository no longer match. The INTEGRATE statement resolves this situation by altering the database definitions using the repository definitions as the source. $ ! $ ! Define CDD$DEFAULT $ ! $ DEFINE CDD$DEFAULT SYS$COMMON:[REPOSITORY]CATALOG $ ! $ ! Enter the CDO to create new field and record definitions: $ ! $ REPOSITORY CDO> ! CDO> ! Create two field (domain) definitions in the repository: CDO> ! CDO> DEFINE FIELD PART_NUMBER DATATYPE IS WORD. CDO> DEFINE FIELD PRICE DATATYPE IS WORD. CDO> ! CDO> ! Define a record called INVENTORY using the two CDO> ! fields previously defined: CDO> ! CDO> DEFINE RECORD INVENTORY. CDO> PART_NUMBER. CDO> PRICE. CDO> END RECORD INVENTORY. CDO> ! CDO> EXIT $ ! $ ! Enter SQL: $ ! $ SQL SQL> ! SQL> ! In SQL, create the database ORDERS: SQL> ! SQL> CREATE DATABASE ALIAS ORDERS PATHNAME ORDERS; SQL> ! SQL> ! Create a table in the database ORDERS using the SQL> ! INVENTORY record (table) just created in the repository: SQL> ! SQL> CREATE TABLE FROM SYS$COMMON:[REPOSITORY]CATALOG.INVENTORY cont> ALIAS ORDERS; SQL> ! SQL> ! Use the SHOW TABLE statement to see information about SQL> ! INVENTORY the table: SQL> ! SQL> SHOW TABLE ORDERS.INVENTORY Information for table ORDERS.INVENTORY CDD Pathname: SYS$COMMON:[REPOSITORY]CATALOG.INVENTORY;1 Columns for table ORDERS.INVENTORY: Column Name Data Type Domain ----------- --------- ------ PART_NUMBER SMALLINT ORDERS.PART_NUMBER PRICE SMALLINT ORDERS.PRICE . . . SQL> COMMIT; SQL> EXIT $ ! $ ! Enter CDO again: $ ! $ REPOSITORY CDO> ! CDO> ! Verify that the fields PART_NUMBER and PRICE are cdo> ! in the record INVENTORY: CDO> ! CDO> SHOW RECORD INVENTORY Definition of record INVENTORY | Contains field PART_NUMBER | Contains field PRICE CDO> ! CDO> ! Define the fields VENDOR_NAME and QUANTITY. Add them to CDO> ! the record INVENTORY using the CDO CHANGE RECORD command. Now, the CDO> ! definitions used by the database no longer match the definitions CDO> ! in the respository, as the CDO message indicates: CDO> ! CDO> DEFINE FIELD VENDOR_NAME DATATYPE IS TEXT 20. CDO> DEFINE FIELD QUANTITY DATATYPE IS WORD. CDO> ! CDO> CHANGE RECORD INVENTORY. CDO> DEFINE VENDOR_NAME. CDO> END. CDO> DEFINE QUANTITY. CDO> END. CDO> END INVENTORY RECORD. %CDO-I-DBMBR, database SQL_USER:[PRODUCTION]CATALOG.ORDERS(1) may need to be INTEGRATED CDO> ! CDO> ! Use the SHOW RECORD command to see if the fields VENDOR_NAME CDO> ! and QUANTITY are part of the INVENTORY record: CDO> ! CDO> SHOW RECORD INVENTORY Definition of record INVENTORY | Contains field PART_NUMBER | Contains field PRICE | Contains field VENDOR_NAME | Contains field QUANTITY CDO> ! CDO> EXIT $ ! $ ! Enter SQL again: $ ! $ SQL SQL> ! SQL> ! Use the INTEGRATE . . . ALTER FILES statement to update SQL> ! the definitions in the database file, using the repository definitions SQL> ! as the source. Note the INTEGRATE statement implicitly attaches to SQL> ! the database. SQL> ! SQL> INTEGRATE DATABASE PATHNAME SYS$COMMON:[REPOSITORY]CATALOG.ORDERS cont> ALTER FILES; SQL> ! SQL> ! Use the SHOW TABLE statement to see if the table INVENTORY has SQL> ! changed. SQL has added the VENDOR_NAME and QUANTITY domains SQL> ! to the database file: SQL> ! SQL> SHOW TABLE INVENTORY Information for table INVENTORY CDD Pathname: SYS$COMMON:[REPOSITORY]CATALOG.INVENTORY;1 Columns for table INVENTORY: Column Name Data Type Domain ----------- --------- ------ PART_NUMBER SMALLINT PART_NUMBER PRICE SMALLINT PRICE VENDOR_NAME CHAR(20) VENDOR_NAME QUANTITY SMALLINT QUANTITY . . . SQL> COMMIT; SQL> EXIT The following example shows how to update the repository using the database files as the source by issuing the INTEGRATE statement with the ALTER DICTIONARY clause. The example starts with the definitions in the repository matching the definitions in the database file. There is a table in the database and a record in the repository, both called CUSTOMER_ORDERS. The CUSTOMER_ORDERS table has four columns based on four domains of the same name: FIRST_ORDER, SECOND_ORDER, THIRD_ORDER, and FOURTH_ORDER. This example adds to the database file a domain called FIFTH_DOM, on which the local column called FIFTH_ORDER is based. At this point, the database file and the repository definitions no longer match. The INTEGRATE . . . ALTER DICTIONARY statement resolves this situation by altering the repository using the database file definitions as the source. SQL> ! Create the database using the PATHNAME clause: SQL> ! SQL> CREATE DATABASE FILENAME TEST1 cont> PATHNAME SYS$COMMON:[REPOSITORY]TEST1; SQL> ! SQL> ! Create domains for the TEST1 database: SQL> ! SQL> CREATE DOMAIN FIRST_ORDER CHAR(4); SQL> CREATE DOMAIN SECOND_ORDER CHAR(4); SQL> CREATE DOMAIN THIRD_ORDER CHAR(4); SQL> CREATE DOMAIN FOURTH_ORDER CHAR(4); SQL> CREATE TABLE CUSTOMER_ORDERS cont> (FIRST_ORDER FIRST_ORDER, cont> SECOND_ORDER SECOND_ORDER, cont> THIRD_ORDER THIRD_ORDER, cont> FOURTH_ORDER FOURTH_ORDER); SQL> COMMIT; SQL> DISCONNECT DEFAULT; SQL> ! SQL> ! Attach to the database with the FILENAME clause so the SQL> ! repository is not updated: SQL> ! SQL> ATTACH 'ALIAS TEST1 FILENAME TEST1'; SQL> ! SQL> ! Use the SHOW TABLE statement to see what columns and domains SQL> ! are part of the table CUSTOMER_ORDERS: SQL> ! SQL> SHOW TABLE (COLUMNS) TEST1.CUSTOMER_ORDERS; Information on table TEST1.CUSTOMER_ORDERS Columns for table TEST1.CUSTOMER_ORDERS: Column Name Data Type Domain ----------- --------- ------ FIRST_ORDER CHAR(4) FIRST_ORDER SECOND_ORDER CHAR(4) SECOND_ORDER THIRD_ORDER CHAR(4) THIRD_ORDER FOURTH_ORDER CHAR(4) FOURTH_ORDER SQL> ! SQL> ! Create a new domain called FIFTH_DOM. Add a new SQL> ! column to the CUSTOMER_ORDERS table called FIFTH_ORDER SQL> ! and base it on the domain FIFTH_DOM: SQL> ! SQL> CREATE DOMAIN TEST1.FIFTH_DOM CHAR(4); SQL> ALTER TABLE TEST1.CUSTOMER_ORDERS ADD FIFTH_ORDER TEST1.FIFTH_DOM; SQL> ! SQL> ! Check the CUSTOMER_ORDERS table to verify that the column FIFTH_ORDER SQL> ! was created: SQL> ! SQL> SHOW TABLE (COLUMNS) TEST1.CUSTOMER_ORDERS; Information on table TEST1.CUSTOMER_ORDERS Column Name Data Type Domain ----------- --------- ------ FIRST_ORDER CHAR(4) TEST1.FIRST_ORDER SECOND_ORDER CHAR(4) TEST1.SECOND_ORDER THIRD_ORDER CHAR(4) TEST1.THIRD_ORDER FOURTH_ORDER CHAR(4) TEST1.FOURTH_ORDER FIFTH_ORDER CHAR(4) TEST1.FIFTH_DOM SQL> COMMIT; SQL> EXIT $ ! $ ! Invoke CDO: $ ! $ REPOSITORY CDO> ! CDO> ! Note that only the database definition for TEST1 appears in the CDO> ! repository directory: CDO> ! DIRECTORY Directory SYS$COMMON:[REPOSITORY] TEST1(1) CDD$DATABASE CDO> ! CDO> ! Check the record CUSTOMER_ORDERS. The field FIFTH_ORDER is not part of CDO> ! the record CUSTOMER_ORDERS. This means that the definitions in the CDO> ! database file do not match the definitions in the repository. CDO> ! CDO> ! CDO> SHOW RECORD CUSTOMER_ORDERS FROM DATABASE TEST1 Definition of the record CUSTOMER_ORDERS | Contains field FIRST_ORDER | Contains field SECOND_ORDER | Contains field THIRD_ORDER | Contains field FOURTH_ORDER CDO> EXIT $ ! $ ! Enter SQL again: $ ! $ SQL SQL> ! SQL> ! To make the definitions in the repository match those in the database SQL> ! file, use the INTEGRATE statement with the ALTER DICTIONARY clause. SQL> ! Note that the INTEGRATE statement implicitly attaches to the SQL> ! database. SQL> ! SQL> INTEGRATE DATABASE PATHNAME TEST1 ALTER DICTIONARY; SQL> COMMIT; SQL> EXIT $ ! $ ! Enter CDO again: $ ! $ REPOSITORY CDO> ! CDO> ! Use the SHOW RECORD command to verify that the field FIFTH_ORDER is now CDO> ! part of the record CUSTOMER_ORDERS. Now, the definitions in both the CDO> ! repository and the database file are the same. CDO> ! CDO> SHOW RECORD CUSTOMER_ORDERS FROM DATABASE TEST1 Definition of record CUSTOMER_ORDERS | Contains field FIRST_ORDER | Contains field SECOND_ORDER | Contains field THIRD_ORDER | Contains field FOURTH_ORDER | Contains field FIFTH_ORDER CDO> ! CDO> ! Use the ENTER command to make the record (table) CUSTOMER_ORDERS and CDO> ! its fields (domains) appear in the repository. The ENTER command CDO> ! assigns a repository directory name to an element. CDO> ! CDO> ENTER FIELD FIRST_ORDER FROM DATABASE TEST1 CDO> ! CDO> ! Verify that a repository path name was assigned to the field CDO> ! FIRST_ORDER: CDO> ! CDO> DIRECTORY Directory SYS$COMMON:[REPOSITORY] FIRST_ORDER(1) FIELD TEST1(1) CDD$DATABASE CDO> ENTER FIELD SECOND_ORDER FROM DATABASE TEST1 . . . CDO> ENTER FIELD FIFTH_DOM FROM DATABASE TEST1 CDO> ! CDO> ! Now all the domains and tables in TEST1 have been assigned a CDO> ! repository directory name: CDO> DIRECTORY Directory SYS$COMMON:[REPOSITORY] CUSTOMER_ORDERS(1) RECORD FIFTH_DOM(1) FIELD FIRST_ORDER(1) FIELD FOURTH_ORDER(1) FIELD SECOND_ORDER(1) FIELD TEST1(1) CDD$DATABASE THIRD_ORDER(1) FIELD To store existing database file definitions in the repository for the first time, use the INTEGRATE statement with the CREATE PATHNAME clause. This statement builds repository definitions using the database file as the source. The following example shows how to store existing database system file definitions in the repository for the first time. This example first creates a database only in a database file, not in the repository. Next, the INTEGRATE statement with the CREATE PATHNAME clause updates the repository with the data definitions from the database system file. SQL> ! SQL> ! Create a database without requiring the repository (the default) SQL> ! or specifying a path name: SQL> ! SQL> CREATE DATABASE ALIAS DOGS; SQL> ! SQL> ! Now create a table for the breed of dog, poodles. The SQL> ! columns in the table are types of poodles: SQL> ! SQL> CREATE TABLE DOGS.POODLES cont> ( STANDARD CHAR(10), cont> MINIATURE CHAR(10), cont> TOY CHAR(10) ); SQL> ! SQL> ! Use the SHOW TABLE statement to see the table POODLES: SQL> ! SQL> SHOW TABLE (COLUMNS) DOGS.POODLES Information on table DOGS.POODLES Columns for table DOGS.POODLES: Column Name Data Type Domain ----------- --------- ------ STANDARD CHAR(10) MINIATURE CHAR(10) TOY CHAR(10) SQL> COMMIT; SQL> EXIT $ ! $ ! Enter CDO: $ ! $ REPOSITORY CDO> ! CDO> ! Use the DIRECTORY command to check if the database definition DOGS is CDO> ! in the repository: CDO> ! CDO> DIRECTORY Directory SYS$COMMON:[REPOSITORY] %CDO-E-NOTFOUND, entity not found in dictionary CDO> ! CDO> ! DOGS is not in the repository. CDO> ! CDO> EXIT $ ! $ ! Enter SQL again: $ ! $ SQL SQL> ! SQL> ! Use the INTEGRATE statement using the CREATE PATHNAME clause to SQL> ! update the repository using the DOGS database file: SQL> ! SQL> INTEGRATE DATABASE FILENAME SQL_USER:[PRODUCTION.ANIMALS]DOGS cont> CREATE PATHNAME SYS$COMMON:[REPOSITORY]DOGS; SQL> COMMIT; SQL> EXIT $ ! $ ! Enter CDO again: $ ! $ REPOSITORY CDO> ! CDO> ! Use the DIRECTORY command to check if the database definition DOGS CDO> ! has been integrated into the repository: CDO> ! CDO> DIRECTORY Directory SYS$COMMON:[REPOSITORY] DOGS(1) CDD$DATABASE CDO> ! CDO> ! You can also use the SHOW USED_BY command to see CDO> ! if the record (table) POODLES and the fields (columns) CDO> ! STANDARD, MINIATURE, and TOY are part of the database CDO> ! definition DOGS. CDO> ! CDO> SHOW USED_BY/FULL DOGS Members of SYS$COMMON:[REPOSITORY]DOGS(1) | DOGS (Type : CDD$RDB_DATABASE) | | via CDD$DATABASE_SCHEMA . . . | SYS$COMMON:[REPOSITORY]CDD$RDB_SYSTEM_METADATA.RDB$CDD_NAME;1(Type : FIELD) | | | | via CDD$DATA_AGGREGATE_CONTAINS | | POODLES (Type : RECORD) | | | via CDD$RDB_DATA_AGGREGATE | | | STANDARD (Type : FIELD) | | | | via CDD$DATA_AGGREGATE_CONTAINS | | | | SQL$10CHR (Type : FIELD) | | | | | via CDD$DATA_ELEMENT_BASED_ON | | | MINIATURE (Type : FIELD) | | | | via CDD$DATA_AGGREGATE_CONTAINS | | | | SQL$10CHR (Type : FIELD) | | | | | via CDD$DATA_ELEMENT_BASED_ON | | | TOY (Type : FIELD) | | | | via CDD$DATA_AGGREGATE_CONTAINS | | | | SQL$10CHR (Type : FIELD) | | | | | via CDD$DATA_ELEMENT_BASED_ON . . . CDO> EXIT The following example shows how to update a repository field using the database files as the source by issuing the INTEGRATE DOMAIN statement with the ALTER DICTIONARY clause. The example starts with the definitions in the repository matching the definitions in the database file. There is a domain in the database and a field in the repository, both called DOMTEST. This example alters the domain in the database file name TESTDB. At this point, the database file and the repository definitions no longer match. The INTEGRATE DOMAIN . . . ALTER DICTIONARY statement resolves this situation by altering the repository using the database file definitions as the source. SQL> -- Create a database, domain, and table. SQL> -- SQL> CREATE DATABASE FILENAME TESTDB PATHNAME TESTDB; SQL> CREATE COLLATING SEQUENCE FRENCH FRENCH; SQL> CREATE DOMAIN DOMTEST cont> CHAR(5) cont> COLLATING SEQUENCE IS FRENCH; SQL> CREATE DOMAIN TEST_DOM_1 cont> CHAR(1); SQL> CREATE TABLE TEMP_TAB cont> (ROW1 CHAR(5), cont> ROW2 DOMTEST, cont> ROW3 TEST_DOM_1, cont> ROW4 INT); SQL> COMMIT; SQL> SHOW DOMAIN DOMTEST DOMTEST CHAR(5) Collating sequence: FRENCH SQL> -- SQL> -- Disconnect from the database and invoke Oracle CDD/Repository SQL> -- user interface and show the field DOMTEST from the TESTDB SQL> -- database. SQL> -- SQL> DISCONNECT ALL; SQL> EXIT $ CDO CDO> SHOW FIELD DOMTEST FROM DATABASE TESTDB Definition of field DOMTEST | Datatype text size is 5 characters | Collating sequence 'FRENCH' CDO> ! CDO> ! Exit from Oracle CDD/Repository and attach to the database by file name CDO> ! only. CDO> ! CDO> EXIT SQL> ATTACH 'FILENAME TESTDB'; SQL> -- SQL> -- Alter the domain DOMTEST. SQL> -- SQL> ALTER DOMAIN DOMTEST cont> CHAR(10) cont> COLLATING SEQUENCE IS FRENCH; SQL> COMMIT; SQL> SHOW DOMAIN DOMTEST DOMTEST CHAR(10) Collating sequence: FRENCH SQL> -- SQL> -- Disconnect from the database and attach by path name only to issue SQL> -- the INTEGRATE DOMAIN statement. SQL> -- SQL> DISCONNECT ALL; SQL> ATTACH 'PATHNAME TESTDB'; SQL> INTEGRATE DOMAIN DOMTEST ALTER DICTIONARY; SQL> COMMIT; SQL> -- SQL> -- Disconnect from the database and invoke Oracle CDD/Repository V6.1 SQL> -- user interface and show the altered field DOMTEST from the TESTDB SQL> -- database. SQL> -- SQL> DISCONNECT ALL; SQL> EXIT $ CDO CDO> SHOW FIELD DOMTEST FROM DATABASE TESTDB Definition of field DOMTEST | Datatype text size is 10 characters | Collating sequence 'FRENCH' | Generic CDD$DATA_ELEMENT_CHARSET is '0'