Re: Dealing with very large blocks of character data

On 01-Dec-2011 12:28 , David Gibbs wrote:

On 12/1/2011 2:20 PM, CRPence wrote:

How about using some SQL procedures [and\or functions] to perform
the work using CLOB data type, since SQL has similar capability to
various RPG BIFs? For example a scan implemented as a procedure
might look like:

I'm still limited to 64k for the text variable ... since I would need
to pass it to the stored procedure.

I am not sure I understand that comment. What is the "text variable" in that comment?

The example showed how to pass multiple "chunks" of 32K of data which are then processed by the SQL as CLOB concatenated [32K] CHAR data; up to the full length of all contiguous chunks combined by concatenation. The CLOB could have been defined as 1M instead of 64K; I merely chose 64K and only two 32K chunks, because I was under the impression that 64K in a newer release would have been satisfactory.? Thus the LOCATE in place of %scan is operating on the full length of data [up to the length of any number of combined 32K parameters, within the limit of a CLOB] in the memory of the RPG program; the memory having been mapped as any number of address-"based" 32K chunks. Note: In this case the 32K limit [for the chunks] is due to the database, not the RPG, since the CHAR declarations in the SQL stored procedure have that limit.

PLUS (and this is just a personal preference) I really dislike RPG
SQL ... I like the concept, but not the implementation (I dislike
code generators in general).

As negative as the original expressed distaste [and seconded with comments about the need for null-terminating the data as strings] for using the C approach? What about treating the SQLRPGLE as a separate ILE language, like C would be, and limit any SQLRPGLE to those source members built into modules of a service program rather than making the main program SQLRPGLE?

Implementing just a scan procedure to replace some existing %scan BIFs for a test version of the program could give an idea whether the SQL approach is even feasible for performance requirements. The performance of the program mapping chunks of its memory via based max-length variables passed as parameters of the procedure should be great, per passing by reference. However, that the SQL LOCATE scalar then operates only after CASTing the data from those chunks into concatenated CLOB data, the database will possibly have to copy the data, so performance may not even meet expectations. If poorly performing, the SQLRPGLE could be eliminated as an option. Or maybe the ease of using the SQL as compared to C would outweigh some level of performance impact.

Regards, Chuck