exporting db/2 to xml

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.

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:

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.

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.

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.

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?

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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.

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

tkshatriyaAuthor Commented: 2009-07-07

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.

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.