Better design: Informatica readingfrom Table or MQ?

anshuljain26 · Posted: Thu Sep 08, 2011 11:48 pm

HI,

We have a design change. we have an options with us, for Informatica B2B to read from table. Now the data record is too large, i.e. 66000.
Now we should go for Varchar datatype with 64K page size or a table with CLOB datatype.

I know that CLOB data resides in auxiliary space, so i think Informatica B2B will take more time to read. Is that so?

Which one is better and feasible.

Note: Feasibility also depends on the resources available with the technology at the organization level. Kindly, please help me ignore this fact.

dick scherrer · Posted: Fri Sep 09, 2011 12:16 am

Hello,

You need to provide a much better explanatoin of what you are trying to accomplish.

If the data is 66000 bytes, how will a 64k varchar help . . .

Sounds like there is / has been a design flaw. Why did someone believe there should be so long a bit of data?

If you clearly explain what business function you are trying to implement, someone may have a suggestion as people here have most likely done something very similar in much different ways.

dick scherrer · Posted: Fri Sep 09, 2011 12:18 am

Follow on:

enrico-sorichetti · Posted: Fri Sep 09, 2011 12:58 am

anshuljain26 · Posted: Fri Sep 09, 2011 1:48 am

Thanks, Dick & Enrico-sorichetti.

Firstly, i forgot to write somewhere around 66k, to be exact 65041bytes and as 64k is 65536 bytes, therefore I think data may reside.

Secondly, we are writing the end of day report to an MQ, but due to design issues like loosing the data on outage, issues and exponential increase of size of MQ(discussed in one of the post), we want to use DB2 to store the data from mainframes and B2B reading and deleting it.

We want to communicate between mainframes and Informatica B2B for message of 65041bytes and at one go we can expect generation of 1lakh such messages from mainframes at EOD.

Thridly, on the feasibility thing, implementing of design and idea is all i mean

Thanks.

dick scherrer · Posted: Fri Sep 09, 2011 2:00 am

Hello,

If my multiplication is correct, you want to transfer 6,504,100,000 (65041 * 100000 = 6.5 billion bytes) at about the same time every day. . .

Have you ever tried such a massive amount of data in a short timeframe? What is the expected performance?

If the data is put into a db2 table, it will not "get lost".

anshuljain26 · Posted: Fri Sep 09, 2011 2:11 am

HI,

but that will be on diff records. and yes it is happening at present via MQ.

And on performance definitely MQs are ease for B2B to read from.

But here should we go for Varchar datatype or CLOB?

dick scherrer · Posted: Fri Sep 09, 2011 2:34 am

Hello,

GuyC · Posted: Fri Sep 09, 2011 1:14 pm

DB2 SQL reference guide :

sushanth bobby · Posted: Mon Sep 12, 2011 9:46 am

Hi Anshul,

You may have to use a CLOB, since its 65536 bytes.