Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

exporting db/2 to xml

Posted on 2009-07-02
7
Medium Priority
?
972 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 24776317
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
ID: 24788590
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 35

Expert Comment

by:Gary Patterson
ID: 24788719
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 33

Expert Comment

by:shalomc
ID: 24792303
"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
ID: 24794191
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
ID: 24794574
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 35

Accepted Solution

by:
Gary Patterson earned 300 total points
ID: 24805176
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most of the developers using Tomcat find it easy to configure the datasource in Server.xml and use the JNDI name in the code to get the connection.  So the default connection pool using DBCP (or any other framework) is made available and the life go…
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

610 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