Net.Data for i5/OS

Net.Data for i5/OS now supports stored procedure calls. And the support has been made available in V3R2, V3R7 and subsequent releases.

A stored procedure is a programming construct that can be called to perform a set of operations. The operations can contain host language statements (e.g. C, RPG, COBOL, CL, etc.) and SQL statements.

Stored procedures are typically classified as either SQL stored procedures or external stored procedures. SQL stored procedures contain only SQL statements. External stored procedures reference a host language program which may or may not contain SQL statements. Support for external stored procedures has been available since V3R1, while support for SQL stored procedures was added in V4R2.

Advantages of Stored Procedures
Net.Data now supports direct two way passing of macro variables as parameters to stored procedures. This enables you to call any of your existing stored procedures, without modification, via Net.Data. The datatypes of stored procedure parameters can be any of the datatypes supported by SQL for i5/OS. If an INOUT or OUT type parameter is updated by the stored procedure, the Net.Data macro variable that represents that parameter will reflect that change when the DTW_SQL LE function ends.

The main advantage derived by using a stored procedure is performance. Particularly if it is used to perform complex SQL operations. Here is a list of a few of the benefits:

• Access Plans required by i5/OS to access records in a physical file (rows in an SQL table) must be built each time a normal DTW_SQL LE function is called. This is due to the dynamic nature of Net.Data. Because of the nature of stored procedures, access plans are built when the program is compiled and then saved with the program object. You pay the onetime cost of building the access plan when the program is compiled, and then reuse it whenever the program is called. This can result in substantial performance benefits.
• Assuming that your stored procedures do contain embedded SQL statements, a single Net.Data DTW_SQL LE function call can now execute many SQL statements. This has several ramifications. You can use the program logic of your high level language to determine what should be done; which file(s) need to be updated, which and how many records to alter, when to commit or rollback a change, produce multiple result sets that will be placed in INOUT or OUT type Net.Data Table variables that were passed to the DTW_SQL LE function.
• Connecting to a remote system on which the stored procedure exists and is identified to SQL for i5/OS, you can now dramatically decrease the communications line usage by issuing a single request to execute that stored procedure rather than multiple individual requests to accomplish the same amount of work.

Example 1
Here is a macro that calls a stored procedure written in PL1 which produces multiple result sets:

  %{***********************************************************
  /*   DEFINE BLOCK
  /************************************************************%}
 %DEFINE  {
   MACRO_NAME         = "TEST 5 TABLES RETURNED"
   DTW_HTML_TABLE     = "YES"
   DTW_DEFAULT_REPORT = "NO"
   NUM_TABLES         = ""
   Procedure          = "SILBER.RSMLTCS"
   TABLE01            = %TABLE(0)
   TABLE02            = %TABLE(0)
   TABLE03            = %TABLE(0)
   TABLE04            = %TABLE(0)
   TABLE05            = %TABLE(0)
   TABLE06            = %TABLE(0)
 %}

 %FUNCTION(DTW_SYSTEM) CRTSQLPLI(){
  %EXEC{/QSYS.lib/CRTSQLPLI.CMD PGM(SILBER/RSMLTCS)
                                SRCFILE(SILBER/PLISRC)
                                SRCMBR(RSMLTCS)
                                CLOSQLCSR(*ENDJOB)
  %}
  %MESSAGE{
     default : "$(DTW_DEFAULT_MESSAGE) : continuing.
": continue
  %}
 %}

 %FUNCTION(DTW_SQL) CRTPROC(){
  CREATE PROCEDURE $(Procedure) ()
   EXTERNAL NAME $(Procedure) LANGUAGE PLI SIMPLE CALL
  %MESSAGE{
     default : "$(DTW_DEFAULT_MESSAGE) : continuing.
": continue
  %}
 %}

 %{ This stored procedure actally only builds 6 tables...
    table06 test the LE's ability to ignore excess table parms %}
 %FUNCTION(DTW_SQL)   myProc
  (OUT table01,table02,table03,table04,table05,table06){
 CALL $(Procedure)
 %}

 %FUNCTION(DTW_SQL) DROP_PROC(){
   DROP PROCEDURE $(Procedure)
 %}

 %{ Display a table with the default report format %}
 %MACRO_FUNCTION showTable(INOUT table){
 %}

 %HTML(drop)  {
    @DROP_PROC()
 %}

 %HTML(create)  {