Design Suggestions : Mainframes to Informatica B2B

anshuljain26 · Posted: Wed Aug 31, 2011 11:32 pm

Hi All,

In the existing application we are passing data from Mainframes to Interfacing system i.e. Informatica B2B over an MQ. The scenario is:

1)Online system at Mainframes will generate a End of Day Report for different customers i.e for their different accounts.
2)As the data is huge, we write the report for each account as a different message onto MQ and ask Informatica B2B to read it.

Problems:

1)First and foremost, the MQ capability is being exploited in this scenario as MQs are generally used for triggering.

2)As the data is bound to grow exponentially in future, MQ queue depth will have to be increased. presently it is 100 thousand and is expected to grow.

3)In case MQ fails, or any issues with MQ, we loose the MQ message and the due to system constraints the MQ message cannot be regenerated.

Thus, due to above issues we want to redesign this part of our application.

Constraints:

1)The process cannot be created as a batch process due to interaction of various online systems while report generation.

Solutions:
1)From, mainframes write the data onto DB2 table and in parallel ask Informatica B2B to read and delete the row whenever it has finished the processing related to that record.
Cons:
As in DB2 in our application we have locks at Page level, in case mainframes in writing record at same page as Informatica want's to access, Informatica B2B will face resource access issues.

2)From, Mainframes write the data onto DB2 table until mainframes finishes the processing and ask Informatica B2B to read and delete, once Mainframes completes the writing.
Cons:
In case we have issues at mainframes in processing report for any customer, the report processing at Informatica B2B will be delayed until the issue is resolved, hence affecting other customers.

3)From, mainframes write the data for different customers on Db2 portioned table, where portions are on basis of customer ID.
We assume that different partitions lie on different pages, so that in case Informatica B2B is reading for Customer A, and Mainframes is writing for Customer B, both are happening at different pages.

So according to us, we think option 3 is most suitable. Kindly please suggest issues that we can possibly face with option 3 and do we have any other solution to this problem? are we missing anything here?

Note: I am sorry for such a long post, but i wanted to detail out everything in my first post.

Thanks.

dick scherrer · Posted: Thu Sep 01, 2011 12:24 am

Hello,

As this is a new table/tablespace suggest the lock be changed to row rather than page.

anshuljain26 · Posted: Thu Sep 01, 2011 12:33 am

Thanks, Dick.

Due to standards issues, the DBA would not agree for row level lock.
Secondly, Yes solution uses new Db2 Table. The process cannot be created at batch due to limitation of dependent systems, which can only be accessed through online system. In case we need to go for batch, those systems would also be required to transformed in a batch.

In case you find any design error with the above solution, I will be happy to have your suggestions, Dick.

Thanks.

dick scherrer · Posted: Thu Sep 01, 2011 12:48 am

Hello,

Then it may be time for a new dba . . .

I know of no really good reason to lock at the page level. The few places i've seen this keep page level locking because that is the way db2 was installed and they are afraid to change it.

anshuljain26 · Posted: Thu Sep 01, 2011 1:01 am

Dick, really can't say anything for a Dba

The report is generated by accessing 5 different systems. The different systems have different source of information. Even restriction on instances of process running, under online, which will be altogether a lot big process, to move to batch. Db2 table is the one last thing, where data will be written.

Thanks, Dick.

dbzTHEdinosauer · Posted: Thu Sep 01, 2011 1:36 am

don't really understand why the queues are not drained by the reciever.

?write a process on the receiving end, triggered by a MQ msg,
that writes to a database,
thus the mq is not continuously full.

since the queue space being used is at the receiving end, screw-em,
make the queue depth deeper
if they are not bothered about processing the messages.

what platform is the informatica running?

i installed an interface between the MF and 'a laptop' which hosted SAP R/3,
10 million msgs back and forth in a 9/hour period. each message potentially 500k.

queue depth never exceeded 300.

there is no reason to have such a problem. you are not telling us everything about the receiving end.....

and I agree with Dick about the DBA. he sounds like a real jerk.
sounds as if the row size is large, no reason not to have row-level locking.
especially since you are inserting and the other side is deleting.