How to avoid Schema owner / Table owner names while working with Informix DBEXPORT and DBIMPORT utilities

IBM Informix provides variety of tools and utilities to help assist in performing data movement activities. Considering various factors such as size of the database, source location and target location, type of data, nature of environment, etc, the end user can make a choice of the right tool or utility to perform the data movement, either from one database to another, from one instance to another or from one machine to another, etc. Each set of tools and utilities defines their own set of scope for usage, are optimal and work best when used within the defined scope.

Please click here to access the Database Migration Utilities section on IBM Informix v12.10 Knowledge Center, that introduces you to couple of utilities that prove to be handy during day-to-day activities.

In the interest of time, the current blog work concentrates only on the DBEXPORT and DBIMPORT utilities and provides step by step instructions to perform data export from one source database and import the same on the target instance to create the exported database, while successfully negotiating the challenges of working with Schema owner / Table owner names, which are usually different for different machines / servers.

Export a Informix database using Informix DBEXPORT utility

1. Ensure that the Informix server is up and running (verify the output of " onstat - " command)
   
2. Make note of the DBSPACE names that are available on the setup or make note of the DBSpace name which hosts the database and all of its associated tables. Capture the output of the command " onstat -d " to a file, which shall show the list of DBSpaces available and filter it out to choose all or only relevant set of DBSpaces
   
3. At the Informix command prompt, execute the following command to export the contents of the said database:
   
   Syntax:
   dbexport -cq -nw <database_name>
   
   Example: Assuming the name of the database is demo_db
   dbexport -cq -nw demo_db
   
4. On successful completion, the DBExport operation would have created two files:
   
   • <database_name>.exp ( demo_db.exp as per the example assumption in Step 3)
   • dbexport.out
   
   Verify their successful creation and availability
   
5. Copy the <database_name>.exp file to the target computer / server
   
   
   Import a Informix database using DBIMPORT utility
   
   
6. Ensure, the <database_name>.exp ( demo_db.exp as per the example assumption in Step 3) was correctly copied from source server to target server
   
7. Ensure the user has all the privileges to access and work with <database_name>.exp ( demo_db.exp as per the example assumption in Step 3) to perform DBImport operation
   
8. Create the DBSpaces to match the names of the DBSpaces that were available on the Source server. The physical location of these DBSpaces on the file system can differ from source to target and so are the size of the DBSpaces ( as long as the DBSpaces sizes are large enough to hold the database being imported). However, the DBSpace names mapping is mandatory. If the DBSpaces with same names are already available, then ignore creating them again. Just ensure, you have sufficient space to perform the Import operation
   
9. If the name of the system owner who is going to perform the DBImport operation is same as that of the source server, then, we can directly execute the DBImport command. Else, we need to edit the <database_name>.sql file ( demo_db.sql as per the example assumption in Step 3) to change the owner name or remove the owner details, so that, the DBImport command creates the database with new ownership details
   
10. If the Owner is different, then, Locate the <database_name>.sql file ( demo_db.sql as per the example assumption in Step 3) that is available within the <database_name>.exp ( demo_db.exp as per the example assumption in Step 3). Identify the entries where table owner details are provided and remove / delete them. The owner name will be prefixed to the table names, as shown in the following illustration:
    
    Example:
    create trigger "informix".se_uominstrig insert on "sde".st_units_of_measure
    create table "sde".geometry_columns
    create table "informix".sysbldobjects
    
    Here, in the above illustration, we need to remove "informix". and "sde". entries, so that, all the tables and database components are created with new ownership details
    
11. Once the file is ready, we need to execute the following command to perform DBImport operation
    
    Syntax:
    dbimport -c <database_name> -l
    
    Example:
    dbimport -c demo_db -l
    
    The flags used here are c ( c for cat ) and l ( l for lion )

Additional References:

• Click Here to know more about IBM Informix DBExport and DBImport utilities