exporting db/2 to xml

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

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. ?

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

"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?

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.  

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