[pyaranoid]

Hi Gurus,

I have to unload around 1 million data from informix to DAT file and load it back to SQL through SQL Loader.

As the data contains all the special characters that a keyboard contains i want to load the data to DAT file using Fixed width format.

Could any one of you guys let me know how to unload informix data using fixed width format??

It would be a great help to me if you can provide me solution asap..

Thanks once again

Suresh

[ceinma]

Use the HPL ( High Performance Load). I don't believe you can found another way to execute a unload (or load) faster on the Informix.

With this function you can load and unload to a lot types of formats, include fixed width and custom formats.

study about the commands:
onpladm ( IDS version >= 9.40)
onpload (all versions)
ipload ( the GUI version of onpladm, but i do not recomend, is not a friendly GUI on my opinion)

Take a look on the HPL manual , is very easily to use...
with 2 line command you start your unload.
You maybe must to use more commands if want to custom the output...

Simple Example:

Create a job over a table:

onpladm create job job_table_xyz -d "/tmp/table_xyz.unl" -D my_database -t table_xyz -fu -zFA

Execute the unload

onpload -j job_table_xyz -fu -l /tmp/table.xyz.log 

[pyaranoid]

Thanks for the info. Could any one give me clear example as i am a novice to infomix? I need to load the data into Oracle Sql after unloading such a large file in informix...

Thankss

[ceinma]

more clear that the example above?

Just look the manuals about the syntax...

[pyaranoid]

Hi,

I am taking a backup of data from informix DB to oracle DB.

For getting data from Informix DB..i am using unload to command using dbaccess.

I am able to get all the 1 million records with a data limiter '|'.

I am using this Data file as a input to oracle DB using SQL Loader.

But I am able to get only 600 thousand records as the data itself has '|' character. So its taking that to bad record.

So can you tell me how to get all records??

Thanks

[ceinma]

You can change the delimiter on the unload:

unload to "/xyz" delimiter ";"
select * from xyz

check what delimiter the SQL loader are expected ...

[pyaranoid]

Hi,

I have tried with all the characters in the keyboard, still i am missing the data while loading thru sql loader.. Please help me out !!

Thanks

[ceinma]

well...
For me, the problem is in SQL Loader...

My suggestion in this case is split your file and load in small pieces.
To split file i know 2 ways...
1) use the "split" command.
2) with HPL, configure more than 1 device (5 devices for example) and when the unload will generate all data over this 5 files...

Then you load file by file and discover where is the problem... what record... or if in SQL Loader

[pyaranoid]

Hi,

Thanks you for your suggestion :-).

I will use split command..Can you give me an example of using that as i am novice to informix?

I cant use HPL over here..i need to use only through dbaccess.

Thanks once again

[ceinma]

Split command is a unix/linux command.

The output of the "man split"

SPLIT(1) User Commands SPLIT(1)

NAME
split - split a file into pieces

SYNOPSIS
split [OPTION] [INPUT [PREFIX]]

DESCRIPTION
Output fixed-size pieces of INPUT to PREFIXaa, PREFIXab, ...; default size is 1000 lines, and default
PREFIX is `x'. With no INPUT, or when INPUT is -, read standard input.

Mandatory arguments to long options are mandatory for short options too.

-a, --suffix-length=N
use suffixes of length N (default 2)

-b, --bytes=SIZE
put SIZE bytes per output file

-C, --line-bytes=SIZE
put at most SIZE bytes of lines per output file

-d, --numeric-suffixes
use numeric suffixes instead of alphabetic

-l, --lines=NUMBER
put NUMBER lines per output file

--verbose
print a diagnostic to standard error just before each output file is opened

--help display this help and exit

--version
output version information and exit

SIZE may have a multiplier suffix: b for 512, k for 1K, m for 1 Meg.
l

Eg: split -l 100000 my_table.unl my_table.part.
will create files with 100.000 lines each.
The name of files in this example is some like this:
my_table.part.aa
my_table.part.ab
my_table.part.ac
my_table.part.ad

[pyaranoid]

Hi,

Thank you very much for your detailed examples..It solved my problem..kudos to you