Hi,
I think writing the information into a RPG variable, moving and inserting the result into a column in a table will slow down speed.
If you really need to concatenate the information on the fly in your RPG program and cannot use the SQL Publishing Functions (as Nick proposed) for writing the data directly into the file using a LOB_LOCATOR would be a better choice.
A LOB locator is a special type of pointer, that can be used in composition with SQL functions and statements like a varying character field.
Instead of using the RPG EVAL OpCode, you'll concatenate your XML-String within a SET statement.
Attention: When using LOB locators, commitment control is required and the locator must be explicitly freed by executing either an COMMIT, ROLLBACK or FREE LOCATOR statement.
Using commitment control does not require your tables are registered in a journal, just add WITH NC (=with no commit) at the end of your INSERT, UPDATE or DELETE statement and it is performed without commitment control.
Here an example:
D ClobData S SQLTYPE(CLOB_Locator)
*------------------------------------------------------------------------
/FREE
Exec SQL Set Option Commit=*Chg, DatFmt=*ISO;
*InLR = *On;
Exec SQL Set :CLOBData = '<Root><Element1>Value1</Element1>';
//Data is added at the end of the string
Exec SQL Set :CLOBData = :CLOBData concat
'<Element2 Attribute1="ABC">Value2' concat
'</Element2></Root>';
Exec SQL Insert into YourTable Values(:CLOBData, ....) with NC;
//Error handling
Exec SQL Free Locator :CLOBData;
Return;
/End-Free
As an aside, try to reduce the number of SET statements that populate the CLOB data to a minimum.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Tim Wright
Gesendet: Wednesday, 02.10 2013 22:37
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: Pushing XML out to a browser request
Here's more detailed info about what we are doing. Agree that 1s seems pretty fast, but dealership based users were getting 0.1s response time with direct SQL hits so they will notice - and according to the web team - complain. So we are trying to speed things up as much as possible. What the end users are not understanding is that we are sending more data back now than we were with the direct SQL. Plus - I do NOT want to continue to direct SQL. It makes the applications too tightly coupled.
This is how we generate the XML.
First, this is the relationships of the tables involved. It’s similar to an indented bill of material, where many records may be nested within a parent.
Basically, we read the Header and begin building a string of text like this.
@Xml = %Trim(@Xml)
+'<Claim>'
+'<Header>'
+'<ClaimNo>' + %Editc(Cmclm:'X') + '</ClaimNo>'
+'<ProdPlant><![CDATA[' + %Trim(Cmplnt) + ']]></ProdPlant>'
+'<ClaimType><![CDATA[' + %Trim(@ClaimType) + ']]></ClaimType>'
+'<ProcPlant><![CDATA[' + %Trim(Cmpplt) + ']]></ProcPlant>'
+'<RecStatus><![CDATA[' + %Trim(Cmrcst) + ']]></RecStatus>'
We continue processing all the “child” records and continue building the string as above.
After the XML “string” is complete we insert it into a work file as a CLOB data type that is returned to a stored procedure.
From: Henrik Rützou <hr@xxxxxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)"
<rpg400-l@xxxxxxxxxxxx>,
Date: 10/02/2013 04:00 PM
Subject: Re: Pushing XML out to a browser request
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
It all depends ...
I have SQL REST based CGI services that generates XML in 0.02-0.04 seconds on the server, the roundtrip on the external internet browser is about 71 ms
On Wed, Oct 2, 2013 at 9:36 PM, Richard Schoen
<richard@xxxxxxxxxxxxxxx>wrote:
One second doesn't seem to be bad for response time.
If you're writing to a DB and they are reading it maybe all the
channels are slowing it down a little.
Would have to know more about the plumbing sequence but it sounds like
you're writing to a DB entry and then they are reading it ?
Why not create an RPG based XML URL requestor instead so the XML
response
is immediate to the java app from the HTTP call to the RPG app. No DB
or clob interaction needed.
Then again I don't know the app architecture and 1 second response
sounds
good to me :-)
Regards,
Richard Schoen
RJS Software Systems Inc.
Where Information Meets Innovation
Document Management, Workflow, Report Delivery, Forms and Business
Intelligence
Email: richard@xxxxxxxxxxxxxxx
Web Site: http://www.rjssoftware.com
Tel: (952) 736-5800
Fax: (952) 736-5801
Toll Free: (888) RJSSOFT
------------------------------
message: 4
date: Wed, 2 Oct 2013 14:44:20 -0400
from: Tim Wright <Tim.Wright@xxxxxxxxxxxxxxxxxxxxxx>
subject: Pushing XML out to a browser request
In a nutshell, we have a back end home grown RPG ERP system with a
J2EE based dealer extranet on top. Part of the function of the dealer
interface
is the management of warranty claims. The browser requests claim
information from the back end and the back end produces an XML
document
in
return. This works great - except - our web comrades complain that it
takes
over a full second to get the XML back. They do direct SQL hits to the
DB
and get results in a tenth of the time - so they complain. Do you know
of
anyone who has struggled with response time related to creating XML
via
RPG
and passing it back to a web interface? We don't know where the
bottleneck
is. It might be on the browser side, but we can't seem to isolate it.
If anyone has insight about this, I can provide more details to help
you answer.
Many thanks!
**************************
This e-mail message may contain confidential or privileged information.
If
you are not the intended recipient, please delete the message and any
attachments and notify the sender by return e-mail. You should not
retain,
distribute, disclose or use any of the information in this message.
**************************
--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.
--
Regards,
Henrik Rützou
http://powerEXT.com <
http://powerext.com/>
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
**************************
This e-mail message may contain confidential or privileged information. If you are not the intended recipient, please delete the message and any attachments and notify the sender by return e-mail. You should not retain, distribute, disclose or use any of the information in this message.
**************************
midrange.com
