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 itback 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 weredesigned 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 anotherserver that might not have the same version of the Informix engine, operatingsystem, 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 beingwritten out to the unload file. A dbschema is created that can be edited(within reason) to remove indexes and index-based constraints prior to importingthe data into the target instance.
The big disadvantage to the utility is thatif you’re going to be creating a database, then loading tables with data, theentire process executes as a single transaction. If you specify that thedatabase should be created in a logged mode, you will undoubtedly run into along transaction condition. To prevent this from occurring, create and load thedatabase in unlogged mode, then change the logging mode through the ontapeutility.
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, orwrites to the tape or disk are not occurring correctly
-q Suppress all output messages generated by the command. Ifused, you would need to execute a “tail -f” command on thedbexport.out file to track the process of the dbexport command.
-d Export only the BLOB descriptors stored in the table, not theBLOBs themselves. This option should be set if the BLOB data is available onoptical media that can easily be transferred to the new system. There is noneed to encode, export and reload this data if it can be moved and set back upwithout much trouble.
-ss A very useful and long-awaited switch — this includes originaltable-creation information in the schema. This information includes first andnext extent sizing, lock mode, and dbspace placement. Understand that theinformation presented is what the original table creator entered only. If abigger extent size is more appropriate because of the table’s current size, thedbexport command will not recalculate and use this size in the schema.
-X recognize and handle hexadecimal data inside a characterdatatype.
The output from this command can be set either to disk or tape. Using the “-Opathed_directory” flag will redirect the output to that preexistingdirectory.
If you are going to use tape (which I strongly recommend) to hold the export, youwill want to redirect the schema file creation to disk when you set the tapeparameters. You accomplish this by using the following switches:
-f file_name the “pathed” file name to contain the schema created bythe dbexport command.
-t tape_device the fully pathed symbolic link to the tape device towrite the data unload to.
-b blk_size The block size for the device. Consult thedocumentation that came with the drive to determine the proper setting.
-s tape_size The amount of data, minus five percent, that the tapedevice can write out in native, uncompressed mode.
Your comments about reloading data containing CRs puzzle me, but because you didnot provide more detail, let me see if I can answer what I can. The fact thatthe data contains CR is not significant. Data containing CRs can besuccessfully 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 thirdattempt involved introducing a CR in a flat file, then trying to load the rowinto 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 tes000010 | 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. Ahex 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 test000010 | 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 see000040 | 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 CR000080 | 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 | betwee0000b0 | 6e 20 6c 69 6e 65 73 2e : 7c 0a 32 7c 74 68 69 73 |n lines.|.2|this0000c0 | 20 69 73 20 61 20 74 65 : 73 74 20 72 69 67 68 74 | is a test right0000d0 | 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 m000100 | 61 72 6b 7c 0a 33 7c 20 : 74 68 69 73 20 69 73 20 |ark|.3| this is000110 | 61 20 74 65 73 74 20 72 : 69 67 68 74 20 0c 20 68 |a test right . h000120 | 65 72 65 7c 0a : |ere|.
That I was able to do this successfully shows, at least under theseconditions, that CRs in a character datatype column should not prevent exportingand re-importing of data with the dbexport/dbimport utilities.
