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

Effectively deploy Informix snapshot using Instance Deployment Utility 'ifxdeploy'

IBM Informix facilitates deployment of Informix instances on 'n' number of target servers, by means of capturing Snapshot of Informix Instance, up and running on the source server.

Snapshot provides you a means of taking a backup of a Informix Instance in an 'As - Is' state and then deploy the same on multiple target servers. The advantages of deploying snapshot are:

1. You don't have to go through the process of Installation & Configuration on every target server ( Though, one can Install & Configure IBM Informix with minimal fuss, within few minutes)
2. Capturing the snapshot 'As - Is', facilitates capturing the schema and data of database(s) withing the Informix instance, at that moment.
3. Deployment of such a snapshot on target servers, brings up the instances to the same state as available on source server, significantly saving time and resources and brings the users to the speed, hardly missing a beat, as though, they are working on the source server.

IBM Informix provides following two utilities to effectively perform snapshot deployment:

• ifxdeployassist  : The Deployment Assistant (DA) is used to create a snapshot of an up & running Informix database server instance, with the Instance being in On-Line mode. This utility is used on the Source server.
• ifxdeploy           : The Deployment Utility (DU) makes use of the snapshot as created by the DA on the source server and deploys it on the target server. It optionally refers to the 'ifxdeploy.conf' configuration file to configure the parameters on target Informix instance.

 The following section of the blog briefs you on the step by step procedure to effectively create a snapshot using the DA and then deploy it on the target server making use of the DU.

Creating Informix Instance Snapshot using Deployment Assistant:

1. Choose a Informix Instance to be your choice of source server. Ensure the Informix instance is up and running ( verify the output of “onstat –“ command ). Configure the instance and fine tune the settings for performance, as needed.
2. If the target environment is familiar, then Identify the names of the users who shall be accessing the server and then GRANT necessary privileges, against the DEMODB, SYSADMIN and SYSUSERS databases, else, if you are not familiar with target environment, then plan to grant privileges to the ‘public’, which relates to any user accessing the machine:
   
   DBACCESS --> DEMODB --> SQL EDITOR
   GRANT DBA TO INFORMIX;
   GRANT DBA TO ADMINISTRATOR;
   GRANT DBA TO PUBLIC;
   GRANT DBA TO <USERS>;
    
3. Once the privileges have been granted, we are good to take a snap shot
4. Decide on the archive file format, to have the snap shot on, i.e ZIP, TAR, GZIP, etc
5. Execute the command
   
   Windows         : ifxdeployassist.exe –a zip –d
   
   Linux / Unix    : ifxdeployassist –a tar –d
   
   Here, flag ‘a’ defines the target archive file format and flag ‘d’ represents that data also needs to be picked up as part of the snap shot
6. The deployment assistant pop-up window shall appear on the screen. Follow the instructions on the window to complete the activity of taking a Informix Instance Snapshot
7. Click on Finish to complete the snapshot activity and exit the pop up window
8. Verify that the snap shot creation activity has created two files, one with the Instance details and the other one with the dbspaces details ( *_db extension )
9. Copy the ‘ifxdeploy.exe’ file ( On Windows) or ‘ifxdeploy’ file ( On Linux / Unix ) found under %INFORMIXDIR%/bin, ‘ifxdeploy.conf’ file found under %INFORMIXDIR%/etc, along with the snapshot files obtained in Step 8, to a directory location on the Target server
   
   
   Deploy Informix Instance Snapshot using Deployment Utility (DU)
   
10. On the Target server, confirm that you have received the ‘ifxdeploy’, ‘ifxdeploy.conf’ file and the snap shot files, intact, without any corruptions
    
11. Ensure, the user on the server has Read / access privileges to the files identified in Step 10
12. Extract the contents of one of the two snap shot files, the one that has the DBSpaces and is identified with the file format “*_db”
13. Now edit the ‘ifxdeploy.conf’ file, to update the Target Parameter values, to suit to the target deployment:
    
    User needs to update the ‘ifxdeploy.conf’ file, to update various parameters like:
    INFORMIXSERVER : Name of the Informix instance ( Same name as that of the Instance on source server, else, provide a custom name)
    PROTOCOL1 : Type of Communication protocol to be used. Retain same as source server
    SQLIPORT : Port Number. Retain same as source server or provide unique port number on the target server
    DRDAPORT : If DRDA is available and needed
    SERVERNUM : Unique server number. Retain same as source server or provide a unique number
    INFORMIXSQLHOSTS : Name of the Informix SQLHOSTS file. Retain same as source server
    INFORMIXDIR : Directory Location on file system where Informix Snap shot utility shall deploy Informix on the target machine / server. This directory shouldn’t be created in advance, the utility shall take care of it.
    ONCONFIG : Name of the Informix ONCONFIG file. Retain same as source server
    START : Denotes the number of seconds after which, the utility starts the target Informix instance
    SNAPSHOT : Absolute path to the Snap shot file, as taken on the source server. This is the file that holds all the binaries and executable that makes up the Informix Instance on the target machine.
    RELOCATE : Location of dbspace chunks on the target server. Either provide the location of the directory where you have extracted the “ *_db” zip/tar file or move them to a directory location and provide that path here
    INFORMIXPASSWORD : Password for user ‘informix’
    SYSTEM : Set the value to ‘1’ to use Local System User account, else set it to ‘0’ for Informix
    LOGFILE : Location of log file to capture the output of the snapshot deployment
    ROOTPATH : Absolute Path of the ROOTDBS file, after it has been extracted on the target server
    WIN6432 : Set to ‘1’, if the source and target servers are being ported from 32 bit to 64 bit
14. Refer the Attachment "ifxdeploy.conf" for further details and illustration on how to update the config file
15. Open a Command Prompt on the target server and set the environment for the following two parameters:
    INFORMIXDIR        : Location where Informix Snap shot utility shall deploy Informix on the target machine / server. This directory shouldn’t be created in advance, the utility shall take care of it.
    INFORMIXSERVER    : Name of the Informix instance ( Same name as that of the Instance on source server, else, provide a custom name)
16. Once the environment is set, execute the following command to initiate the Informix Deployment Utility to deploy the snapshot on the target machine
    
    ifxdeploy.exe -f "F:\embed_demo\ol_informix1210_20141229_1210.zip" -config ifxdeploy.conf –verbose
    
    Where:
    " –f "                   : Refers to the physical location of the snapshot file
    " –config "     : Refers to the CONFIGURATION file that shall be referred by the snapshot deployment utility during deployment
17. Refer the attachment "deploy.bat", that illustrates how to quickly prepare a BATCH file ( for Windows ) or attachment "deploy" file that illustrates how to quickly prepare a Shell script ( For Linux / Unix ), to perform the deployment and ensure that the setup is at relevant location to startup the Informix engine )
18. The utility takes few minutes to complete the deployment and exits the execution after completion
19. Verify the successful completion of snapshot deployment by monitoring the status of the Informix instance on the target machine using the command "onstat –"

Please Click Here to download the attachments referred in this blog work.

Additional References:
IBM Informix Embeddability Deployment Tutorial