Solved

exporting db/2 to xml

Posted on 2009-07-02
7
935 Views
Last Modified: 2013-12-11
We are trying to integrated iseries (v5r4) with peoplesoft sql database. I'm looking for some example from as/400 DB2/ to export data (post the data ) in xml format . I read the article about XML-INTO and an XML-SAX op-code. Thisop code deal with  xml data importing to db/2 .

Can anyone shed lights for exporting db/2 to xml ?

Any recommendation, books , article apperciated.
We are trying to integrated iseries (v5r4) with peoplesoft sql database. I'm looking for some example from as/400 DB2/ to export data (post the data ) in xml format . I read the article about XML-INTO and an XML-SAX op-code. Thisop code deal with  xml data importing to db/2 .
 

Can anyone shed lights for exporting db/2 to xml ? 
 

Any recommendation, books , article apperciated.

Open in new window

0
Comment
Question by:tkshatriya
  • 3
  • 3
7 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
Any particular reason you want to go through XML, when you could just connect directly to the Peoplesoft DBMS via ODBC, DRDA, JDBC, etc (depends on which dbms you are running Peoplesoft on)?  

Depending on your requirements, it might be simple to write one piece of code that connects to the Peoplesoft database, extracts data using SQL, and then updates the appropriate DB2 tables.

If you want to explore that option in more detail, let me know.

Now, let's focus on generating XML.

Sounds like you need to pull from iseries and push to Peoplesoft, so maybe you already have code on the Peoplesoft box to process incoming XML files.

There are lots of ways to get XML from DB2.  Far too many ways.

You can write code in RPG or Java to do the task.  IBM offers the XML Toolkit to help with XML application development.  The third link below explains how to use the XML4PR parser from the Toolkit in RPG:

http://www-03.ibm.com/systems/i/software/xml/index.html
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzamj/rzamjmain.htm
http://www.ibmsystemsmag.com/ibmi/may03/developer/8909p1.aspx

There is an IBM product called DB2 XML Extender that you can order (or may already own - GO LICPGM, Option 10 to view installed licensed programs on your system).  Check with your IBM rep or business partner for pricing.

http://www-01.ibm.com/support/docview.wss?rs=72&uid=swg21293639
http://www.redbooks.ibm.com/abstracts/redp0135.html?Open

There is a great free AS/400 utility called SQL2XML that converts SQL results sets to XML.  mcpressonline.com annoyingly requires a lengthy registration to see the article, but it is worth it in the long run, and there is a lot of great content here anyway.  

http://www.mcpressonline.com/tips-techniques/sql/techtip-sql2csv-and-sql2xml.html

If you have Microsoft SQL Server 2005 or 2008 around (don't know about earlier versions), you can create a linked server for your AS/400, and use SQL server XML extensions:

http://dotnetslackers.com/articles/xml/Dynamic_XML_From_SQL_Server.aspx

You can do something similar with mySQL:

http://www.kitebird.com/articles/mysql-xml.html

Sometimes, a little SQL works wonders for simple XML transformations.  redirect output to a file in the STRSQL environment, compile and run as a query management query (CRTQMQRY, STRQMQRY), embed in and RPG program, or use as the SELECT clause if an INSERT INTO.

SELECT DISTINCT '<customer>' as XMLLINE from dummyfile          
   UNION                                                    
SELECT '<name>' || cusnam || '</name><address>' || cusadr || '</address>' as XMLLINE from custfile                          
   UNION                                                    
SELECT DISTINCT '</customer>' as XMLLINE from dummyfile        

Hope this gives you a place to start.

- Gary Patterson


0
 

Author Comment

by:tkshatriya
Comment Utility
Thank you gary for answering my query. The reason we using XML format because we wanted real time integration between db/2 and peoplesoft.  what is best way to approach this. ?
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
OK, I understand "real-time integration", but why, specifically, do you want XML?  Is there some technical reason that you need to format XML?  Do you have Peoplesoft utilities that can easily import XML (that would be a very good reason for XML)?  XML is a great technology, but it is by no means a "one size fits all" solution for data interchange.

There is no one global "perfect solution" for this kind of integration.  It depends on numerous factors:

  • What type of information, specifically, do you need to exchange between the two systems?  (Show us table layouts if you can).
  • How many transactions in a day do you anticipate?  How big (in bytes) is each transaction (unformatted)?
  • What OS platform is Peoplesoft running on, and what database management system?
  • Do you need to transfer information from AS/400 to Peoplesoft, Peoplesoft to AS/400, or in both directions?
  • Since you need "real time", what are the triggering events (and on which platforms)?
  • What is the speed of the slowest network link between the two machines (some techniques use more bandwodth than others.  If the two machines are on the same switch in the same building, that may not be much of a consideration.  If they are in different data centers across a WAN link it may be a very significant design limitation.
  • How reliable does the data exchange need to be?  What is the exposure to the business if a transaction fails?
  • What about security and confidentiality?  Does the data need to be encrypted while it is "in flight" between the systems?
  • Are there existing interfaces, utilities, and/or APIs to handle these data extracts and imports that can be leveraged?
- Gary Patterson
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 32

Expert Comment

by:shalomc
Comment Utility
"Real time" implies some sort of web services.
However, the biggest question is whether the applications are to be loosely coupled or tightly coupled.

On an architectural level, loosely coupled applications use a buffer between them, and asynchronous messaging. In simple words - application A does not have to wait for application B to reply.

Tightly coupled application have no buffer, and the messaging is synchronous. Application A waits for application B to reply.

You can have very fast and loosely coupled integration - for example using MQ or some kind of ESB.

Tightly coupled applications nowadays use web services, as a replacement to remote calls.

XML is a way to encode messages, and is also used to describe services (WSDL) and to wrap the application level messages in standard containers (SOAP).

Now, on top of Gary's questions, can you say which XML are you after? Do you need application level XML, SOAP, or web services?

Will the AS400 serve as a client, server, or both?
0
 

Author Comment

by:tkshatriya
Comment Utility
Appericiate Gary. Thank you for clarification. Here is my project goal.

Currently, we have RPG program on AS/400 which replace file(table) every night at 11.30 PM. SQL 2005(odbc) connection is setup to transfer data from AS/400 to Peoplesoft enviorment. We have temp table which gets replace on peoplesoft enviorment everyday midnight. Application engine job is schedule to trasnfer the data from peoplesoft temp. table to various table in peoplesoft database.

in Nutshell,

1) RPG program replace temp. table
2) make odbc connection through sql 2005 to peoplesoft database
3) transfer temp. table inormation to another peoplesoft temp.table
4)  application engine (peoplecode) job pickup data and update peoplesoft various table.

Since, citizen information get transfer every night. Peoplsoft (CRM call center) have to wait for the day to get update citizen information. So, we decided to have real time intergration between this two system to make call center user life easier.

I was looking best approach to handle this request for real time intergration.  



0
 

Author Comment

by:tkshatriya
Comment Utility
OK, I understand "real-time integration", but why, specifically, do you want XML?

Not necessarily, I was looking for best approach to handle the real time integration request.

Is there some technical reason that you need to format XML?  Do you have Peoplesoft utilities that can easily import XML (that would be a very good reason for XML)?  XML is a great technology, but it is by no means a "one size fits all" solution for data interchange.


No, I dont have any reason to opt the XML format. Like I mentioned I m looking real time integration between this two system and trying to find best approach. The reason I was doing research on XML format as PeopleSoft version PT 8.48 integration broker  sends or receives messages  containing data in a variety of formats. Formats include PeopleSoft rowset-based message format, and nonrowset-based message structures including , XML document object model messages, Simple Object Access Protocol (SOAP) messages, and non-XML files.


There is no one global "perfect solution" for this kind of integration.  It depends on numerous factors:
"      What type of information, specifically, do you need to exchange between the two systems?  (Show us table layouts if you can).  
This is table layout

CUST_ID_1      char      no      9                            no      no      no      
CUST_NM_TY      char      no      1                            no      no      no
CUST_AREA_CODE      char      no      3                            no      no      no
CUST_PHONE      char      no      7                            no      no      no      
CUST_NM      char      no      30                            no      no      no      
CUST_ADD1      char      no      30                            no      no      no      
CUST_ADD2      char      no      30                            no      no      no      
CUST_CITY      char      no      20                            no      no      no      
CUST_STATE      char      no      2                            no      no      no      
CUST_ZIP      char      no      9                            no      no      no      
CUST_ST_NO      char      no      7                            no      no      no      
CUST_DIR_CODE      char      no      2                            no      no      no      
CUST_QUAL_PRE      char      no      10                            no      no      no      
CUST_ST_NM      char      no      25                            no      no      no      
CUST_ST_SUFF      char      no      4                            no      no      no      
CUST_POST_ST_DIR      char      no      2                            no      no
CUST_ST_QUL_POST      char      no      5                            no      no      no      
CUST_ST_APT_NO      char      no      5                            no      no      no
CUST_LOC_CITY      char      no      20                            no      no      no      
CUST_ZIP_CODE      char      no      9                            no      no      no      
CUST_EMAIL      char      no      50                            no      no      no      
CUST_ACCNO      char      no      20                            no      no      no
CUST_ALT_PHONE      nchar      no      80                            yes      (n/a)      (n/a)
CUST_WRK_PHONE      nchar      no      80                            yes      (n/a)      (n/a)
CUST_EMAIL_ADD      nchar      no      80                            yes      (n/a)      (n/a)      
LAST_PHN_CHNG_DT      nchar      no      80                            yes      (n/a)      (n/a)

      
"      How many transactions in a day do you anticipate?  How big (in bytes) is each transaction (unformatted)?  

Approx. 1000 records back and forth transfer from AS/400 to PeopleSoft daily .  When Tarrant district makes mages changes then appx. 5000 to 6000 records expected every six months.



"      What OS platform is Peoplesoft running on, and what database management system?  

O/S 2003 , SQL 2005

"      Do you need to transfer information from AS/400 to Peoplesoft, Peoplesoft to AS/400, or in both directions?  

Yes
"      Since you need "real time", what are the triggering events (and on which platforms)?  
Real time integration when call center user make any changes in address, phone or information of citizen , we need trigger that go back and update PeopleSoft system and vice versa.



"      What is the speed of the slowest network link between the two machines (some techniques use more bandwodth than others.  If the two machines are on the same switch in the same building, that may not be much of a consideration.  If they are in different data centers across a WAN link it may be a very significant design limitation.  

Both the system/server is located in same building.

"      How reliable does the data exchange need to be?  What is the exposure to the business if a transaction fails?  


Since it involved citizen sensitive information we need this information to transfer at very secure level.

"      What about security and confidentiality?  Does the data need to be encrypted while it is "in flight" between the systems?  

Still trying to figure it out best approach and wants the data to be transfer securely.

"      Are there existing interfaces, utilities, and/or APIs to handle these data extracts and imports that can be leveraged?
Yes,  Peoplesoft do accept generic API from third party . Like I mentioned earlier still in research phase and trying my best to find the right approach for this integration.


Thank you


0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 75 total points
Comment Utility
Well, for the AS/400 to Peoplesoft interface, it looks like you have a lot of options for your interface.  Without seeing the Integration Broker documentation, it is difficult to advise as to the best mechanism.

If you have a single program that updates the Citizen database on the AS/400, then you can just modify that program to kick out an Integration Agent message in whatever format you choose (XML, rowset, SOAP message, etc.).

If the database is updated from multiple programs, you might find it easier to create an update trigger program on the underlying table and have that trigger program handle the message creation.  When I design trigger programs, I often opt for an asynchronous model, where the trigger program just puts an entry on a data queue, and a seperate service job blocks on the data queue and handles any IO and communications part of the process.  This keeps your trigger program from slowing down database operations and the inderlying programs that update the Citizen database.

Process 1
TRIGGER PROGRAM calls QSNDDTAQ ...

Process 2 (Never-ending service job)
QRCVDTAQ -> Format and send message to Peoplesoft

- Gary Patterson
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now