Unloading Informix Data

pyaranoid · #1 (**permalink**) 01-11-08, 22:26

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 · #2 (**permalink**) 01-13-08, 19:26

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:

Code:

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

Execute the unload

Code:

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

pyaranoid · #3 (**permalink**) 01-13-08, 21:06

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 · #4 (**permalink**) 01-14-08, 05:08

more clear that the example above?

Just look the manuals about the syntax...

pyaranoid · #5 (**permalink**) 01-14-08, 12:46

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 · #6 (**permalink**) 01-14-08, 12:58

You can change the delimiter on the unload:

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

check what delimiter the SQL loader are expected ...

pyaranoid · #7 (**permalink**) 01-14-08, 22:43

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 · #8 (**permalink**) 01-15-08, 05:53

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 · #9 (**permalink**) 01-15-08, 08:17

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 · #10 (**permalink**) 01-15-08, 08:35

Split command is a unix/linux command.

The output of the "man split"

Quote:

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 · #11 (**permalink**) 01-17-08, 09:12

Hi,

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

maneeshamani · #12 (**permalink**) 01-20-09, 19:12

Hello Gurus,
This is my first post on this forum. We need to migrate data from Informix version 11 to Oracle 10g. There are few millions of data. I dont ahve any idea on Informix.
Can some one suggestion what would be the best way for extracting data from Informix DB.

Is IBM Data stage a good option?

Looking forward for your expertise.

thanks,
mm

pyaranoid · #13 (**permalink**) 01-20-09, 21:08

Hi,

Datastage is a good option if you have that software.

You can also unload the data in informix to a dat file and then load the data to oracle using sql loader or any other loading utility.

Thanks

maneeshamani · #14 (**permalink**) 01-20-09, 22:26

Hello,
Thanks for your feedback. When I do unload and load without any tools, how the data reconcilation can be carried out. Do you ahve any suggestion for data conversion?

Appreciate your help.

thanks,
mm

maneeshamani · #15 (**permalink**) 01-27-09, 22:52

Thanks for all the previous suggestions. I'm gonna use onpladm for unloading the Informix data. For performing this what are all the pre-requisite I should look for? Do I need a DBA access? Thanks in advance.

Internet Services

Web Development

Digital Marketing

Consumer Tech