An alternative to getPhase() that provides additional options for variable mode table UDFs. getPhaseEx() provides a way for table functions to know when they are being passed in the last logical, or qualified row on an AMP. getPhaseEx() also provides users with more control of table phase transitions. Users can reduce the number of phase transitions required during execution of a table function, thus reducing the number of UDF invocations and improving table function performance.
Arguments
The following values are valid for the option argument only for variable mode table UDFs. These options are ignored if you specify them in a constant mode table UDF.
| Options | Value | Description and Usage |
|---|---|---|
| Tbl.TBL_NOOPTIONS | 0 | Indicates that no options are specified. Use this option when you only want to retrieve the processing phase in which the function was called. |
| Tbl.TBL_LASTROW | 1 | Allows a function to determine when it is being passed the last input row on an AMP. Use this option if your table function processes a set of input rows before returning an output row. You must set the EOF indicator when using TBL_LASTROW to signal the end of the processing when the last row has been encountered. The function then moves from the TBL_BUILD phase to the TBL_BUILD_EOF phase where the row is built. |
| Tbl.TBL_NEWROW | 2 | If this option is set and the phase is TBL_BUILD, the function is called with a new row with a phase of TBL_BUILD. Use this option when you want to get a new row on each function invocation. If end of file, TBL_LASTROW or ProcessLastRow is true, then this option is ignored. |
| Tbl.TBL_NEWROW and Tbl.TBL_LASTROW | 3 | The behavior for both the TBL_NEWROW and TBL_LASTROW options are in effect. |
| Tbl.TBL_NEWROWEOF | 4 | If this option is set, and the phase is TBL_BUILD and EOF is set, then the function is called with a new row with a phase of TBL_BUILD. Use this option when you want to get a new row when EOF is signaled. If end of file, TBL_LASTROW or ProcessLastRow is true, then this option is ignored. |
| Tbl.TBL_NEWROWEOF and Tbl.TBL_LASTROW | 5 | The behavior for both the TBL_NEWROWEOF and TBL_LASTROW options are in effect. |
If you specify Tbl.TBL_LASTROW, the option remains in effect for the duration of the request. The TBL_NEWROW and TBL_NEWROWEOF options are set and reset based on the options specified during each call to getPhaseEx() where the option value is nonzero.
For example, calling getPhaseEx() with the option Tbl.TBL_NOOPTIONS does not reset the TBL_NEWROW or TBL_NEWROWEOF behavior.
The processing phases that getPhaseEx() returns in the phase argument depend on the mode.
| Mode | Phase | Meaning |
|---|---|---|
| Tbl.TBL_MODE_VARY | Tbl.TBL_PRE_INIT | The table UDF is being called for the first time for all the rows that it will be called for. The input arguments to the UDF contain the first set of data. During this phase, the UDF has an opportunity to establish overall global context, but should not build a result row. The UDF continues to the TBL_INIT phase. |
| Tbl.TBL_INIT | The input arguments to the UDF contain the first set of data. During this phase, the UDF should not build a result row. The UDF continues to the TBL_BUILD phase. |
|
| Tbl.TBL_BUILD | If the TBL_NEWROW option is set, then call the UDF with a new row with a phase of TBL_BUILD. If the TBL_NEWROWEOF option and EOF are set, then call the UDF with a new row with a phase of TBL_BUILD. If the TBL_LASTROW option is set, the function remains in the TBL_BUILD phase until it is passed in the last set of data, where it continues to the TBL_BUILD_EOF phase. The UDF can process any data that it wants to return in the TBL_BUILD_EOF phase. The UDF remains in the TBL_BUILD phase until it throws an SQLException with the sqlstate set to "02000" to indicate no data, whereupon it continues to the TBL_FINI phase. |
|
| Tbl.TBL_BUILD_EOF | The UDF should fill out the result arguments to build a row. The UDF remains in the TBL_BUILD_EOF phase until it throws an SQLException with the sqlstate set to "02000" to indicate no data, whereupon it continues to the TBL_END phase. |
|
| Tbl.TBL_FINI | The UDF returns to the TBL_INIT phase with more variable input data. | |
| Tbl.TBL_END | The table function is not called again after this phase. | |
| Tbl.TBL_ABORT | The table UDF is being aborted. A UDF can be invoked at any time with this phase, which is only entered when a copy of the table UDF invokes Tbl.abort(). It is not entered when the function is aborted for an external reason, such as a user abort. | |
| Tbl.TBL_MODE_CONST | Tbl.TBL_PRE_INIT | The UDF may decide whether it should be the controlling copy of all table functions running on other AMP vprocs. If the function wants to provide control context to all other copies of the table function, the function must call Tbl.control(). If the function does not want to be the controlling copy of the table function, or if the function is designed without the need for a controlling function, the function can simply return and do nothing during this phase. All copies of the table function must complete this phase before any copy continues to the TBL_INIT phase. |
| Tbl.TBL_INIT | Any copy of the UDF that does not want to participate further must call Tbl.optOut(). After the function returns, it is not called again. All copies of the table UDF must complete this phase before any copy continues to the TBL_BUILD phase. |
|
| Tbl.TBL_BUILD | The table UDF should fill out the result arguments to build a row. The function remains in the Tbl.TBL_BUILD phase until it throws an SQLException, setting the sqlstate to "02000" to indicate no data, whereupon it continues to the Tbl.TBL_END phase. |
|
| Tbl.TBL_END | The table function is not called again after it returns from this phase. The controlling copy of the table function, if one exists, is called with this phase after all other copies of the table function have completed this phase, which allows the controlling function to do any final cleanup or notification to the external world. |
|
| Tbl.TBL_ABORT | The table UDF is being aborted and should perform cleanup, if necessary. A function can be called at any time with this phase, which is only entered when one of copies of the table function calls Tbl.abort(). It is not entered when the function is aborted for an external reason, such as a user abort. |
Return Value
| Value | Meaning |
|---|---|
| Tbl.TBL_MODE_CONST | The table UDF arguments are constant. Although getPhaseEx() can be used successfully by a table function that is passed in constant arguments, it is most useful to a table function that is passed in variable arguments. |
| Tbl.TBL_MODE_VARY | The table UDF arguments vary and are based on the rows produced by the correlated table specification in the SELECT statement. The table UDF might only be called on specific AMP vprocs in this mode. |