Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Design Suggestions : Mainframes to Informatica B2B

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Wed Aug 31, 2011 11:32 pm    Post subject: Design Suggestions : Mainframes to Informatica B2B
Reply with quote

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.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Sep 01, 2011 12:24 am    Post subject:
Reply with quote

Hello,

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

Quote:
The process cannot be created as a batch process due to interaction of various online systems while report generation.
If your solution uses a new db2 table why would this be true?

One of the more powerful aspecs of using a database is the ability to run batch and online processes concurrently.
Back to top
View user's profile Send private message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Thu Sep 01, 2011 12:33 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Sep 01, 2011 12:48 am    Post subject:
Reply with quote

Hello,

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

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.

Quote:
In case you find any design error with the above solution, I will be happy to have your suggestions
I don't know enough about the process to comment - too many missing piece. . .

Quote:
The process cannot be created at batch due to limitation of dependent systems, which can only be accessed through online system
Then do this as port of the online? If the online writes entries into a db2 table, why can some batch process not read the db2 table and do what is necessary. I still don't understand the restriction . . . icon_confused.gif
Back to top
View user's profile Send private message
anshuljain26

New User


Joined: 04 Apr 2010
Posts: 37
Location: Chandigarh

PostPosted: Thu Sep 01, 2011 1:01 am    Post subject:
Reply with quote

Dick, really can't say anything for a Dba icon_wink.gif

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Sep 01, 2011 1:36 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Design of ISPF panel prino TSO/ISPF 2 Wed Jan 25, 2017 10:31 pm
No new posts Are there any Freeware utilties for i... johnmull IBM Tools 2 Wed Dec 21, 2016 8:29 am
No new posts creating a physical sequential file i... Shaheen Shaik JCL & VSAM 8 Wed Jun 29, 2016 3:46 pm
No new posts How to read a excel sheet and load it... Radhika Nambiar Java & MQSeries 10 Wed Mar 30, 2016 4:49 pm
No new posts NDM Packed Data file from Mainframes ... santhosh5983 IBM Tools 2 Wed Mar 30, 2016 2:36 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us