Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Informix
Expertise: Beginner
May 6, 1997

Exporting data

Question:
I wasn't able to find out what all the command line switches for dbexport do. The only ones I found were -o and -c.

Is there a way to export data that has CR or CR/LF in it?
Right now the data gets exported all right, but the export file has CRs in it so you can't import it back without doing some cleanup to remove the CRs.

Answer:
There are several questions asked so let's go through each, one at a time.

First of all, the dbexport and dbimport utilities were designed to extract and reload databases through the use of ASCII-based files. This enables you to move a database from an instance on one server to another server that might not have the same version of the Informix engine, operating system, or page size.

As I explain in Chapter 5 of The Informix-OnLine Dynamic Server Handbook, this utility is very handy because it is platform- and (to a certain degree) datatype-independent. BLOBs are put through a type of unencoding before being written out to the unload file. A dbschema is created that can be edited (within reason) to remove indexes and index-based constraints prior to importing the data into the target instance.

The big disadvantage to the utility is that if you're going to be creating a database, then loading tables with data, the entire process executes as a single transaction. If you specify that the database should be created in a logged mode, you will undoubtedly run into a long transaction condition. To prevent this from occurring, create and load the database in unlogged mode, then change the logging mode through the ontape utility.

The switches to the dbexport command are fairly straightforward:

dbexport db_name

-c Continue processing unless the tape device cannot be opened, the database does not exist, the dbexport command is incorrectly structured, or writes to the tape or disk are not occurring correctly

-q Suppress all output messages generated by the command. If used, you would need to execute a "tail -f" command on the dbexport.out file to track the process of the dbexport command.

-d Export only the BLOB descriptors stored in the table, not the BLOBs themselves. This option should be set if the BLOB data is available on optical media that can easily be transferred to the new system. There is no need to encode, export and reload this data if it can be moved and set back up without much trouble.

-ss A very useful and long-awaited switch -- this includes original table-creation information in the schema. This information includes first and next extent sizing, lock mode, and dbspace placement. Understand that the information presented is what the original table creator entered only. If a bigger extent size is more appropriate because of the table's current size, the dbexport command will not recalculate and use this size in the schema.

-X recognize and handle hexadecimal data inside a character datatype.

The output from this command can be set either to disk or tape. Using the "-O pathed_directory" flag will redirect the output to that preexisting directory.

If you are going to use tape (which I strongly recommend) to hold the export, you will want to redirect the schema file creation to disk when you set the tape parameters. You accomplish this by using the following switches:

-f file_name the "pathed" file name to contain the schema created by the dbexport command.

-t tape_device the fully pathed symbolic link to the tape device to write the data unload to.

-b blk_size The block size for the device. Consult the documentation that came with the drive to determine the proper setting.

-s tape_size The amount of data, minus five percent, that the tape device can write out in native, uncompressed mode.

Your comments about reloading data containing CRs puzzle me, but because you did not provide more detail, let me see if I can answer what I can. The fact that the data contains CR is not significant. Data containing CRs can be successfully reloaded using the dbimport utility.

For an example, I created the following table in a test database,

create table test1 
(
col1 smallint,
col2 char(500)
);

then attempted to load a row into it through various means.

My third attempt involved introducing a CR in a flat file, then trying to load the row into the table with the "load" command. The row loaded successfully. The accompanying hex dump shows the format of the row:

000000 | 33 7c 20 74 68 69 73 20 : 69 73 20 61 20 74 65 73 |3| this is a tes
000010 | 74 20 72 69 67 68 74 20 : 0c 20 68 65 72 65 7c 0a |t right . here|.

I exported the database and was able to re-import it without the problem. A hex dump of the unload file for this table appears as follows:

000000 | 31 7c 74 68 69 73 20 69 : 73 20 61 20 74 65 73 74 |1|this is a test
000010 | 20 20 20 20 20 20 20 20 : 20 20 20 20 20 20 20 20 |               
*
000030 | 20 20 20 20 20 20 20 20 : 20 20 74 6f 20 73 65 65 |          to see
000040 | 20 77 68 61 74 20 68 61 : 70 70 65 6e 73 20 20 20 | what happens  
000050 | 20 20 20 20 20 20 20 20 : 20 20 20 20 20 20 20 20 |               
*
000070 | 20 20 69 66 20 79 6f 75 : 20 68 69 74 20 43 52 20 |  if you hit CR
000080 | 20 20 20 20 20 20 20 20 : 20 20 20 20 20 20 20 20 |               
*
0000a0 | 20 20 20 20 20 20 20 20 : 20 20 62 65 74 77 65 65 |          betwee
0000b0 | 6e 20 6c 69 6e 65 73 2e : 7c 0a 32 7c 74 68 69 73 |n lines.|.2|this
0000c0 | 20 69 73 20 61 20 74 65 : 73 74 20 72 69 67 68 74 | is a test right
0000d0 | 20 20 20 20 20 20 20 20 : 20 20 20 20 20 20 20 20 |               
*
0000f0 | 20 20 20 20 61 66 74 65 : 72 20 74 68 69 73 20 6d |    after this m
000100 | 61 72 6b 7c 0a 33 7c 20 : 74 68 69 73 20 69 73 20 |ark|.3| this is
000110 | 61 20 74 65 73 74 20 72 : 69 67 68 74 20 0c 20 68 |a test right . h
000120 | 65 72 65 7c 0a          :                         |ere|.

That I was able to do this successfully shows, at least under these conditions, that CRs in a character datatype column should not prevent exporting and re-importing of data with the dbexport/dbimport utilities.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date