unload data for 3 fields from large informix table

singhald · #1 (**permalink**) 05-29-07, 10:23

Hello All Informix Guru,

I am using informix DB in my DataStage project. I want to unload the three columns from a very big table which having more than 100000000 records.

Please sugest me some efficient way to unload the data from table.

I have tried Unload command but it is not working because of large volume of data.

First column out of those 3 is primary key .

Thanks

Singhal

Tyveleyn · #2 (**permalink**) 05-29-07, 15:26

Hi,

Don't know exactly why it can't be done with one statement but I guess the number of logical logs is to small to fit in the complete transaction.
If that's the case then probably the easiest way, without writing a small application program, is to split the number of rows in the table into an even amount of parts with acceptable results. E.g. if the primary key values range from 1 to 100,000,000 execute 10 unload statements from 1-10,000,000, 10,000,001-20,000,000, etc.
Another way maybe is to create a temporary table without logging, fill it with a select statement and unload this table within one session, like:

Code:

CREATE TEMP TABLE example (                                
    key     INTEGER,                                       
    name    CHAR(10),                                      
    ref     INTEGER) WITH NO LOG;                          
                                                           
INSERT INTO example SELECT col1, col2, col3 FROM basetable;
                                                           
UNLOAD TO example.unl SELECT * FROM example;

Don't know if this works, but this way 'stuffing' and 'stripping' of the temp table will not be logged and therefore could be done with one unload action.

Regards

singhald · #3 (**permalink**) 05-30-07, 04:23

Thank you veryu much , I resolved the issue ,

Singhal

ceinma · #4 (**permalink**) 05-30-07, 07:50

OR ... use HPL...
OR... if your IDS is version 10 you can extract from a backup with archecker....
, without impact your system

Tyveleyn · #5 (**permalink**) 05-30-07, 14:09

Hi singhald, I'm curious how you solved the problem, can you tell me how? The largest table I have to deal with has about half a million rows, completely different from your stuff...

Greets

singhald · #6 (**permalink**) 06-27-07, 05:25

Hello

I am sorry , Now i feel that problem is still there. I am able to unload max 2 GB data from the table . I am using the below command from unix

Deepak

singhald · #7 (**permalink**) 06-27-07, 05:29

Hello

Iused the following command from unix prompt

unload to File
select coloumn1 , Max(coloumn2) form tablename group by column1 having column1>0;

But it is unloading only 2 GB data,

could you please tell me how can i use HPL to unload more then 2 GB data from a table using this query.

Deepak

ceinma · #8 (**permalink**) 06-27-07, 09:29

if you had said before this is the problem...that is easy...

What is your system? Unix? Windows?

The limitation of 2GB probably is limitation of your system.
If is unix, check with comand "ulimit"

Or if your IDS is version 7.31 and 32bits...

To workaround in unix, you can use piped files to unload all data, but the performance will degrade...

to more information , search here for "2gb unload":
http://groups.google.com/group/comp....pics?lnk=gschg

sorry my bad english...