Hi Folks,

I have to move around a terabyte of a blobby table (not TEXT or BYTE)
to another machine. I'm trying without success to make the HPL emit
the blobs into the pipeline stream but getting nowhere. The source
does not have enough space for the local /tmp/blob*.* file HPL wants
to use. The local "rules" deny allowing an NFS mount between the
machines so I can't go there.

The "Ext Type String" and similar types seem to /sound/ like they
should go inline, but it's not happening. If I use the "Ext Type
String Length" with a Fixed ASCII format, then the length is written
in, but the /tmp file is still created. The slightly-inadequate HPL
user guide seems to suggest that each blob should end up in a
"variable length" part at the end of each fixed-length record, but it
just ain't happening.

What am I missing? Can it be done?

If i were you i would phone IBM informix tech support and open a case.
Have them file a bug against it. It is time this is implemented since
they want everyone
to use blob/clob instead of text/byte....(sbspaces can be used for HDR
blobspaces not .)

It is not that hard to change the code for clob/blob to put the info
into memory or inline
in an unloadfile so it can be written to a pipe.

If you are getting nowhere you may want to create your own unload
program.
you do need to describe blob and clobs see a bit of code below.

If you have trouble i can sent the whole thing or you may get it from
someone else.....
i must warn you, the code i have is full of bugs mem leaks etc. so you
have to do some major repair work...

or if it is a one off you could hard code it, there are examples of
howto handle blob/clob stuff in the
demo dir.



Superboer.

....... incomplete code of howto describe clob/blob.

case CLVCHARPTRTYPE : fprintf (stderr,"");
EXEC SQL BEGIN DECLARE SECTION;
lvarchar *lv;
fixed binary 'clob' ifx_lo_t clob_loptr;
fixed binary 'blob' ifx_lo_t blob_loptr;
int8 theseekpos;
int8 thesize;
ifx_lo_stat_t *statsforlo;
char thestatement[100];
EXEC SQL END DECLARE SECTION;
int issblob=0;
if ( strcmp("clob", (char * ) mydesc-
returnIFXdatatoQT ");
sprintf(thestatement,"%s", "execute function
clobinput(? ) ");
issblob=1;
} else {
if ( strcmp("blob", (char * ) mydesc-
returnIFXdatatoQT ");
sprintf(thestatement,"%s", "execute function
blobinput(? ) ");
issblob=2;
}
}
if (issblob > 0) {

lv = ( void * ) mydesc->sqlvar[i].sqldata;
EXEC SQL prepare transflvtoclob
from :thestatement;
if ( sqlca.sqlcode != 0 ) {
fprintf ( stderr ,"prepare %s failed
sqlca.sqlcode %d \n",
thestatement,sqlca.sqlcode);
*thedatareturned=NULL;
return MYSQLIFXERROR;
}

switch ( issblob ) {

case 1 : EXEC SQL execute transflvtoclob
into :clob_loptr using :lv;
break;

case 2 : EXEC SQL execute transflvtoclob
into :blob_loptr using :lv;

.... etc.






On 10 jan, 00:54, aclarke <a.andrew.cla... (AT) gmail (DOT) com> wrote:

On Jan 11, 7:34*am, Superboer <superbo... (AT) t-online (DOT) de> wrote:
Thanks - you've made it clear I'm not missing anything! Damn you
Informix. While we're on the subject of hating HPL, how nasty is that
ipload program? I didn't think clumsy X rubbish like that existed any
more.

I'm not in a hurry to write/modify an unloader, thanks for the offer
though! I'm now waiting for procurement of the target machine's IDS
installer bundle and I'm hoping to draw down the data remotely - once
the large space is allocated to it too. Beaurocracy rules. I'm hoping
HPL in the freebie developer edition of 11.7 will be able to extract
the near-terabyte lump despite the 3G limit of dbspaces DE has... All
I need is a timing and also to see how large the text export is before
planning the real step! Of course even that is waiting for my space
allocation to be actioned.

Cheers and thanks for your reply.

FYI, the free Innovator Edition has no disk space limitation, use that
instead of DE.

Also if you are copying the tables with TEXT columns from one server to
another, you can use my dbcopy utility.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.



On Tue, Jan 10, 2012 at 9:27 PM, aclarke <a.andrew.clarke (AT) gmail (DOT) com> wrote:

hmmmm i would still call IBM informix if i were you...
one could nag about hpl and its interface but if this does not
end up at IBM informix then nothing will be done about it and i think
that is a pitty since loading data in express mode no conversion
is the fastest i have ever seen.....

See you


Superboer.


On 11 jan, 03:27, aclarke <a.andrew.cla... (AT) gmail (DOT) com> wrote:

Hmm, you know you could use an external table, it's just as fast as HPL!

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.



On Wed, Jan 11, 2012 at 1:00 PM, Superboer <superboer7 (AT) t-online (DOT) de> wrote: