The best way is create a STORED PROCEDURE on ISeries.
On Iseries there are two differents type of procedures:
"SQL stored procedure" and "External stored procedure".
An Sql stored procedure is vincolate at SQL
with External procedure you can call an external program on Iseries (RPGLE, CL, C , COBOL.....)
there are two way to create a procedure:
1) in a SQL session on Iseries ( IBM manual "DB2 for i SQL Reference"
I.e.
Create procedure procedure_name
(in field1 char(5), out field2 decimal(3))
language sql reads sql data
seletct max(qta) from pfile_name where fieldx=field1
2) on Windows program bar "IBM System I Access for Windows" and then "System I Navigator".
In System I Navigator open your Iseries tree Database/library/Procedure.
Here you can create a procedure
In a procedure you can define IN, OUT and INOUT parameters.
Simple example without parameters:
On Iseries create a CL program lib/procedure_name
es:
PGM
CLRPFM FILE(LIB/FILENAME)
ENDPGM
On SQL session on Iseries
CRATE PROCEDURE lib/procedure_name
LANGUAGE CL
PARAMETER STYLE GENERAL
this create in lib the procedure procedure_name that start program with the same name.
So in lib there are the procedure and the program with the same name.
On Qlik script you create the ODBC string to connect to iseries and the command to call the procedure:
CONNECT TO [Provider..................
sql
call iseries.procedure_name;
