Page History
...
FUNCTION OPTIONS(*LIGHTUSAGE *DIRECT)
********** COMMENT(Departmental Summary definitions)
DEFINE FIELD(#OVTDEPSAL) TYPE(*DEC) LENGTH(15) DECIMALS(2) COLHDG('Total' 'Salary' 'Expenditure') EDIT_CODE(3)
DEFINE FIELD(#OVXDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Maximum' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVNDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Minimum' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVADEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Average' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVCDEPSAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) COLHDG('Total' 'Employees') EDIT_CODE(3)
DEFINE FIELD(#OVFDEPSAL) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition')
CHANGE FIELD(#OVFDEPSAL) TO('deptment key(), ovtdepsal sum(salary), ovxdepsal max(salary), ovndepsal min(salary), ovadepsal avg(salary), ovcdepsal count()')
DEFINE FIELD(#OVHDEPSAL) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle')
DEF_LIST NAME(#OVSDEPSAL) FIELDS(#DEPTMENT #OVTDEPSAL #OVCDEPSAL #OVXDEPSAL #OVNDEPSAL #OVADEPSAL)
**********
DEFINE FIELD(#OV_RC) TYPE(*CHAR) LENGTH(2) LABEL('Return Code')
********** COMMENT(Create the indexed space)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFDEPSAL) TO_GET(#OV_RC #OVHDEPSAL)
********** COMMENT((Pass over the data and update the summary details')
SELECT FIELDS(#DEPTMENT #SALARY) FROM_FILE(PSLMST)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHDEPSAL) TO_GET(#OV_RC)
ENDSELECT
********** COMMENT(Now load/show a browse list with the results)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(FIRST #OVHDEPSAL) TO_GET(#OV_RC)
DOWHILE COND('#OV_RC = OK')
ADD_ENTRY TO_LIST(#OVSDEPSAL)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(NEXT #OVHDEPSAL) TO_GET(#OV_RC)
ENDWHILE
DISPLAY BROWSELIST(#OVSDEPSAL)
********** COMMENT(Destroy the indexed space)
USE BUILTIN USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHDEPSAL) TO_GET(#OV_RC)
The following sample RDML function is identical to the previous one except that it uses a second indexed space to aggregate information for all departments and then adds the "grand" aggregates to the end of the aggregate list that is displayed to the user. This demonstrates how indexed spaces can be used to perform multiple level totaling by using multiple indexed spaces:
FUNCTION OPTIONS(*LIGHTUSAGE *DIRECT)
********** COMMENT(Departmental Summary definitions)
DEFINE FIELD(#OVTDEPSAL) TYPE(*DEC) LENGTH(15) DECIMALS(2) COLHDG('Total' 'Salary' 'Expenditure') EDIT_CODE(3)
DEFINE FIELD(#OVXDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Maximum' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVNDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Minimum' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVADEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Average' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVCDEPSAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) COLHDG('Total' 'Employees') EDIT_CODE(3)
DEFINE FIELD(#OVFDEPSAL) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition')
...
CHANGE FIELD(#OVFDEPSAL) TO('deptment key(), ovtdepsal sum(salary), ovxdepsal max(salary), ovndepsal min(salary), ovadepsal avg(salary), ovcdepsal count()')
DEFINE FIELD(#OVHDEPSAL) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle')
DEF_LIST NAME(#OVSDEPSAL) FIELDS(#DEPTMENT #OVTDEPSAL #OVCDEPSAL #OVXDEPSAL #OVNDEPSAL #OVADEPSAL)
**********
DEFINE FIELD(#OVFGRAND) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition')
CHANGE FIELD(#OVFGRAND) TO('ovkgrand key(), ovtdepsal sum(salary), ovxdepsal max(salary), ovndepsal min(salary), ovadepsal avg(salary), ovcdepsal count()')
DEFINE FIELD(#OVHGRAND) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle')
DEFINE FIELD(#OVKGRAND) REFFLD(#DEPTMENT) LABEL('Invariant Key') DEFAULT('''*ALL''')
...
**********
DEFINE FIELD(#OV_RC) TYPE(*CHAR) LENGTH(2) LABEL('Return Code')
********** COMMENT(Create the indexed spaces)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFDEPSAL) TO_GET(#OV_RC #OVHDEPSAL)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFGRAND) TO_GET(#OV_RC #OVHGRAND)
********** COMMENT((Pass over the data and create the summary indexes')
SELECT FIELDS(#DEPTMENT #SALARY) FROM_FILE(PSLMST)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHDEPSAL) TO_GET(#OV_RC)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHGRAND) TO_GET(#OV_RC)
ENDSELECT
********** COMMENT(Now load/show a browse list with the results)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(FIRST #OVHDEPSAL) TO_GET(#OV_RC)
DOWHILE COND('#OV_RC = OK')
ADD_ENTRY TO_LIST(#OVSDEPSAL)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(NEXT #OVHDEPSAL) TO_GET(#OV_RC)
ENDWHILE
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OVHGRAND) TO_GET(#OV_RC)
CHANGE FIELD(#DEPTMENT) TO(#OVKGRAND)
ADD_ENTRY TO_LIST(#OVSDEPSAL)
********** COMMENT(Display the results)
DISPLAY BROWSELIST(#OVSDEPSAL)
********** COMMENT(Destroy the indexed space)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHDEPSAL) TO_GET(#OV_RC)
...
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHGRAND) TO_GET(#OV_RC)
The following example RDML function is again very similar to the previous two, except that it produces two additional aggregation lists by using two additional indexed spaces. The second is by department/section and the third is by salary (i.e., a distribution of how many employees earn a particular salary value) :
FUNCTION OPTIONS(*LIGHTUSAGE *DIRECT)
********** COMMENT(Departmental Summary definitions)
DEFINE FIELD(#OVTDEPSAL) TYPE(*DEC) LENGTH(15) DECIMALS(2) COLHDG('Total' 'Salary' 'Expenditure') EDIT_CODE(3)
DEFINE FIELD(#OVXDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Maximum' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVNDEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Minimum' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVADEPSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Average' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVCDEPSAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) COLHDG('Total' 'Employees') EDIT_CODE(3)
DEFINE FIELD(#OVFDEPSAL) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition')
...
CHANGE FIELD(#OVFDEPSAL) TO('deptment key(), ovtdepsal sum(salary), ovxdepsal max(salary), ovndepsal min(salary), ovadepsal avg(salary), ovcdepsal count()')
DEFINE FIELD(#OVHDEPSAL) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle')
DEF_LIST NAME(#OVSDEPSAL) FIELDS(#DEPTMENT #OVTDEPSAL #OVCDEPSAL #OVXDEPSAL #OVNDEPSAL #OVADEPSAL)
********** COMMENT(Section Summary definitions)
DEFINE FIELD(#OVTSECSAL) TYPE(*DEC) LENGTH(15) DECIMALS(2) COLHDG('Total' 'Salary' 'Expenditure') EDIT_CODE(3)
DEFINE FIELD(#OVXSECSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Maximum' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVNSECSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Minimum' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVASECSAL) TYPE(*DEC) LENGTH(11) DECIMALS(2) COLHDG('Average' 'Salary') EDIT_CODE(3)
DEFINE FIELD(#OVCSECSAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) COLHDG('Total' 'Employees') EDIT_CODE(3)
DEFINE FIELD(#OVFSECSAL) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition')
CHANGE FIELD(#OVFSECSAL) TO('deptment key(), section key(), ovtdepsal sum(salary), ovxdepsal max(salary), ovndepsal min(salary), ovadepsal avg(salary), ovcdepsal count()')
DEFINE FIELD(#OVHSECSAL) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle')
DEF_LIST NAME(#OVSSECSAL) FIELDS(#DEPTMENT #SECTION #OVTDEPSAL #OVCDEPSAL #OVXDEPSAL #OVNDEPSAL #OVADEPSAL)
********** COMMENT(Salary Distribution Definitions)
DEFINE FIELD(#OVCSALSAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) COLHDG('Total' 'Employees') EDIT_CODE(3)
DEFINE FIELD(#OVFSALSAL) TYPE(*CHAR) LENGTH(256) DECIMALS(0) COLHDG('Indexed' 'Space' 'Definition')
CHANGE FIELD(#OVFSALSAL) TO('salary key(), ovcsalsal count()')
DEFINE FIELD(#OVHSALSAL) TYPE(*CHAR) LENGTH(10) LABEL('Space Handle')
DEF_LIST NAME(#OVSSALSAL) FIELDS(#SALARY #OVCSALSAL)
**********
DEFINE FIELD(#OV_RC) TYPE(*CHAR) LENGTH(2) LABEL('Return Code')
********** COMMENT(Create the indexed space)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFDEPSAL) TO_GET(#OV_RC #OVHDEPSAL)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFSECSAL) TO_GET(#OV_RC #OVHSECSAL)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OVFSALSAL) TO_GET(#OV_RC #OVHSALSAL)
********** COMMENT((Pass over the data and create the summary indexes')
SELECT FIELDS(#DEPTMENT #SECTION #SALARY) FROM_FILE(PSLMST)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHDEPSAL) TO_GET(#OV_RC)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHSECSAL) TO_GET(#OV_RC)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(PUT #OVHSALSAL) TO_GET(#OV_RC)
ENDSELECT
********** COMMENT(Now load/show a browse list with the results)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(FIRST #OVHDEPSAL) TO_GET(#OV_RC)
DOWHILE COND('#OV_RC = OK')
ADD_ENTRY TO_LIST(#OVSDEPSAL)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(NEXT #OVHDEPSAL) TO_GET(#OV_RC)
ENDWHILE
DISPLAY BROWSELIST(#OVSDEPSAL)
********** COMMENT(Now load/show a browse list with the results)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(FIRST #OVHSECSAL) TO_GET(#OV_RC)
DOWHILE COND('#OV_RC = OK')
ADD_ENTRY TO_LIST(#OVSSECSAL)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(NEXT #OVHSECSAL) TO_GET(#OV_RC)
ENDWHILE
DISPLAY BROWSELIST(#OVSSECSAL)
********** COMMENT(Now load/show a browse list with the results)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(FIRST #OVHSALSAL) TO_GET(#OV_RC)
DOWHILE COND('#OV_RC = OK')
ADD_ENTRY TO_LIST(#OVSSALSAL)
...
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(NEXT #OVHSALSAL) TO_GET(#OV_RC)
ENDWHILE
DISPLAY BROWSELIST(#OVSSALSAL)
********** COMMENT(Destroy the indexed spaces)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHDEPSAL) TO_GET(#OV_RC)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHSECSAL) TO_GET(#OV_RC)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(DESTROY #OVHSALSAL) TO_GET(#OV_RC)
The following sample RDML function is designed to demonstrate how indexed lists can be used to improve application performance in "batch" style jobs processing large amounts of information.
Logically, the "engine" loop of this function is like this:
...
BEGIN_LOOP TO(#OV_ITER)
...
SELECT FIELDS(#EMPNO #SURNAME #GIVENAME #DEPTMENT #SECTION) FROM_FILE(PSLMST)
...
FETCH FIELDS(#DEPTDESC) FROM_FILE(DEPTAB) WITH_KEY(#DEPTMENT)
...
FETCH FIELDS(#SECDESC) FROM_FILE(SECTAB) WITH_KEY(#DEPTMENT #SECTION)
...
ENDSELECT
...
END_LOOP
This loop selects all the employees in the standard shipped demonstration table PSLMST (repeated for a specified number of iterations). It does this to attempt to emulate the processing of a large number of records typically found in a "batch" job.
...
However, the "engine" loop has actually been coded as:
...
BEGIN_LOOP TO(#OV_ITER)
...
SELECT FIELDS(#EMPNO #SURNAME #GIVENAME #DEPTMENT #SECTION) FROM_FILE(PSLMST)
...
IF COND(*USEINDEX)
...
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OV_DEPTAB) TO_GET(#OV_RC)
...
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OV_SECTAB) TO_GET(#OV_RC)
ELSE
...
FETCH FIELDS(#DEPTDESC) FROM_FILE(DEPTAB) WITH_KEY(#DEPTMENT)
...
FETCH FIELDS(#SECDESC) FROM_FILE(SECTAB) WITH_KEY(#DEPTMENT #SECTION)
...
ENDIF
...
ENDSELECT
...
END_LOOP
which allows you to run the engine loop using either the DBMS (i.e., the FETCH commands) or an indexed space (i.e., the USE OV_INDEXED_SPACE commands) to get the department and section descriptions. By doing this you will be able to see the speed advantage that an indexed space provides over performing a full DBMS access to find information.
...
Notice that you can run the "engine" loop through 1 -> 20 iterations using either the DBMS (specify D) or an indexed space (specify I) to fetch the department and section description details. All department and section descriptions are loaded into their associated indexed spaces at the beginning of the function:
FUNCTION OPTIONS(*LIGHTUSAGE *DIRECT)
********** COMMENT(Define and load the department indexed space)
DEFINE FIELD(#OV_DEPDEF) TYPE(*CHAR) LENGTH(50) LABEL('Dept Index')
CHANGE FIELD(#OV_DEPDEF) TO('deptment key(), deptdesc')
DEFINE FIELD(#OV_DEPTAB) TYPE(*CHAR) LENGTH(10) LABEL('Index Handle')
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OV_DEPDEF) TO_GET(#OV_RC #OV_DEPTAB)
SELECT FIELDS(#DEPTMENT #DEPTDESC) FROM_FILE(DEPTAB)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(INSERT #OV_DEPTAB) TO_GET(#OV_RC)
ENDSELECT
********** COMMENT(Define and load the section indexed space)
...
DEFINE FIELD(#OV_SECDEF) TYPE(*CHAR) LENGTH(50) LABEL(' Section Index')
CHANGE FIELD(#OV_SECDEF) TO('deptment key(), section key(), secdesc')
DEFINE FIELD(#OV_SECTAB) TYPE(*CHAR) LENGTH(10) LABEL('Index Handle')
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(CREATE #OV_SECDEF) TO_GET(#OV_RC #OV_SECTAB)
SELECT FIELDS(#DEPTMENT #SECTION #SECDESC) FROM_FILE(SECTAB)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(INSERT #OV_SECTAB) TO_GET(#OV_RC)
ENDSELECT
********** COMMENT(Define other variables)
OVERRIDE FIELD(#GIVENAME) LENGTH(10)
DEFINE FIELD(#OV_RC) TYPE(*CHAR) LENGTH(2) LABEL('Return Code')
DEFINE FIELD(#OV_MODE) TYPE(*CHAR) LENGTH(1) LABEL('Mode (D/I)') DEFAULT(D)
DEFINE FIELD(#OV_ITER) TYPE(*DEC) LENGTH(3) DECIMALS(0) LABEL('Iterations') EDIT_CODE(3) DEFAULT(5)
DEFINE FIELD(#OV_TOTAL) TYPE(*DEC) LENGTH(7) DECIMALS(0) LABEL('PSLMST Accesses') EDIT_CODE(3)
DEF_COND NAME(*USEINDEX) COND('#OV_MODE = I')
DEF_LIST NAME(#OV_LIST) FIELDS(#SURNAME #GIVENAME #DEPTDESC #SECDESC) COUNTER(#OV_TOTAL)
********** COMMENT(Repeat testing until cancelled)
BEGIN_LOOP
********** COMMENT(Request and validate testing details)
POP_UP FIELDS((#OV_MODE *IN) (#OV_ITER *IN)) EXIT_KEY(*NO) PROMPT_KEY(*NO)
BEGINCHECK
VALUECHECK FIELD(#OV_MODE) WITH_LIST(D I) MSGTXT('Mode must be D (use DBMS) or I (use indexed space)')
RANGECHECK FIELD(#OV_ITER) RANGE((1 20)) MSGTXT(('Number of iterations must be in range 1 to 20'))
ENDCHECK
********** COMMENT(Repeat the test for the number of iterations)
CLR_LIST NAMED(#OV_LIST)
BEGIN_LOOP TO(#OV_ITER)
...
SELECT FIELDS(#EMPNO #SURNAME #GIVENAME #DEPTMENT #SECTION) FROM_FILE(PSLMST)
IF COND(*USEINDEX)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OV_DEPTAB) TO_GET(#OV_RC)
USE BUILTIN(OV_INDEXED_SPACE) WITH_ARGS(GET #OV_SECTAB) TO_GET(#OV_RC)
ELSE
FETCH FIELDS(#DEPTDESC) FROM_FILE(DEPTAB) WITH_KEY(#DEPTMENT)
FETCH FIELDS(#SECDESC) FROM_FILE(SECTAB) WITH_KEY(#DEPTMENT #SECTION)
ENDIF
ADD_ENTRY TO_LIST(#OV_LIST)
ENDSELECT
END_LOOP
********** COMMENT(display the results)
DISPLAY FIELDS(#OV_TOTAL) BROWSELIST(#OV_LIST) EXIT_KEY(*NO) MENU_KEY(*NO) PROMPT_KEY(*NO)
END_LOOP