UDTF1 ===== 0001.00 H THREAD(*SERIALIZE) BNDDIR('QC2LE') 0002.00 0003.00 D UDTF1 pr extpgm('UDTF1') 0004.00 D parmIn 11p 0 0005.00 D ParmOut 5p 0 0006.00 D n_parmIn 5i 0 0007.00 D n_parmOut 5i 0 0008.00 D SQLSTT 5a 0009.00 D Function 517a varying const 0010.00 D Specific 128a varying const 0011.00 D MsgText 70a varying 0012.00 D CallType 5i 0 const 0013.00 0014.00 D UDTF1 pi 0015.00 D parmIn 11p 0 0016.00 D ParmOut 5p 0 0017.00 D n_parmIn 5i 0 0018.00 D n_parmOut 5i 0 0019.00 D SQLSTT 5a 0020.00 D Function 517a varying const 0021.00 D Specific 128a varying const 0022.00 D MsgText 70a varying 0023.00 D CallType 5i 0 const 0024.00 0025.00 D Counter s 10i 0 0026.00 0027.00 D CALL_STARTUP C CONST(-2) 0028.00 D CALL_OPEN C CONST(-1) 0029.00 D CALL_FETCH C CONST(0) 0030.00 D CALL_CLOSE C CONST(1) 0031.00 D CALL_FINAL C CONST(2) 0032.00 0033.00 D PARM_NULL C CONST(-1) 0034.00 D PARM_NOTNULL C CONST(0) 0035.00 0036.00 /free 0037.00 n_parmIn = PARM_NULL; 0038.00 n_ParmOut = PARM_NULL; 0039.00 0040.00 Counter += 1; 0041.00 0042.00 select; 0043.00 0044.00 when CallType = CALL_STARTUP; 0045.00 dsply ('STARTUP: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' ' 0046.00 + %editc(Counter:'L') + ' ' 0047.00 + %editc(parmIn:'L')); 0048.00 0049.00 when CallType = CALL_OPEN; 0050.00 dsply ('OPEN...: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' ' 0051.00 + %editc(Counter:'L') + ' ' 0052.00 + %editc(parmIn:'L')); 0053.00 0054.00 when CallType = CALL_FETCH; 0055.00 dsply ('FETCH..: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' ' 0056.00 + %editc(Counter:'L') + ' ' 0057.00 + %editc(parmIn:'L')); 0058.00 parmOut = Counter; 0059.00 n_parmOut = PARM_NOTNULL; 0060.00 if Counter>5; 0061.00 SQLSTT = '02000'; 0062.00 return; 0063.00 endif; 0064.00 0065.00 when CallType = CALL_CLOSE; 0066.00 dsply ('CLOSE..: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' ' 0067.00 + %editc(Counter:'L') + ' ' 0068.00 + %editc(parmIn:'L')); 0069.00 *inLR = *on; 0070.00 0071.00 when CallType = CALL_FINAL; 0072.00 dsply ('FINAL..: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' ' 0073.00 + %editc(Counter:'L') + ' ' 0074.00 + %editc(parmIn:'L')); 0075.00 0076.00 other; 0077.00 dsply ('OTHER..: ' + %editc(CallType:'L') + ' ' + SQLSTT + ' ' 0078.00 + %editc(Counter:'L') + ' ' 0079.00 + %editc(parmIn:'L')); 0080.00 0081.00 endsl; 0082.00 0083.00 return; 0084.00 /end-free UDTF1S ===== DROP FUNCTION UDTF1( decimal(11,0) ); CREATE FUNCTION UDTF1( parmIn decimal(11,0) ) RETURNS TABLE ( parmOut decimal(5,0) ) EXTERNAL NAME 'HS#LIBR/UDTF1(UDTF1)' LANGUAGE RPGLE PARAMETER STYLE DB2SQL NO SQL NOT DETERMINISTIC NO EXTERNAL ACTION NOT FENCED NO SCRATCHPAD DISALLOW PARALLEL CARDINALITY 1;