Virtual table (VTB) events
Virtual table events are generated by the SQL Engine when a table name is found in an SQL statement. These events are only generated if the SEFVTBEVENTS startup parameter is set to allow them. The rules allow for creating virtual tables dynamically from a Data Mapping facility (DMF) model map and for modifying certain table values.
No keywords are defined for VTB event procedures. Only the SQL engine schedules execution of enabled VTB event procedures for each table name in an SQL statement. VTB event procedures allow you to modify information in the DMF map. VTB event procedures make it possible to access multiple data sets using one DMF map by creating alias maps using a model map. Each alias map can specify a different data set name. The model map must be a map that is created by using DMF.
Only the event procedure criterion value is allowed (and must be present).
/*VTB criterioncriterion is the criterion value for VTB event procedures. This criterion is one of the two event types that are shown in the following table.
Each VTB event procedure has access to server-wide global variables.
| Criterion | Variable | Contents | Data type |
|---|---|---|---|
| Any criterion | VTB.USER | The user area is passed between all event procedures that fire for the same event. | Read-write |
| Any criterion | VTB.OPTBSRID | The search id field contains the criterion used to fire the
current event procedure. The format of the criterion is the string ‘MODIFYTABLE.’ followed by the table name found in the SQL statement. |
Character Read-only |
| Any criterion | VTB.OPTBTBNA | The 1 to 128-character table name from the SQL statement. | Character Read-only |
| MODIFYTABLE.tablename | VTB.OPTBMTNA | Set the model table name. This is the 1 to 50-character name of a DMF map that will be used to create a virtual table with the alias name tablename | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBMRDI | Disable MapReduce. Set this value to 1 to disable map reduce. Setting this value to 0 has no effect. VTB.OPTBMRDI and VTB.OPTBMREN are mutually exclusive. |
Character, write |
| MODIFYTABLE.tablename | VTB.OPTBMREN | Enable MapReduce. Set this value to 1 to enable map reduce.
Setting this value to 0 has no effect. VTB.OPTBMREN and VTB.OPTBMRDI are mutually exclusive. Enabling MapReduce requires that the MapReduce feature is enabled. |
Character, write |
| MODIFYTABLE.tablename | VTB.OPTBMRTC | Set the number of MapReduce threads to use. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBFLAT | Flatten this table. Set this value to 1 to flatten the table.
All columns and occurrences are returned in a single table Setting this value to 0 has no effect. VTB.OPTBFLAT and VTB.OPTBSUBT are mutually exclusive. |
Character, write |
| MODIFYTABLE.tablename | VTB.OPTBSUBT | Create subtables. Set this value to 1 to create subtables Columns
that are part of an occurs or occurs-depending-on are returned as
separate tables. Setting this value to 0 has no effect. VTB.OPTBFLAT and VTB.OPTBSUBT are mutually exclusive. |
Character, write |
| MODIFYTABLE.tablename | VTB.OPTBCLSQ | Clear sequential data set map related fields. Set this value
to 1 to clear the data set member name, pre-write exit name, and post
read exit name. Setting this value to 0 has no effect. The fields are cleared before any other variables are processed. |
Character, write |
| MODIFYTABLE.tablename | VTB.OPTBCLCI | Clear VSAMCICS map related fields. Set this value to 1 to clear
the pre-write exit name, post read exit name, CICS file control table
entry names, CICS connection name, and CICS transaction name fields. Setting this value to 0 has no effect. The fields are cleared before any other variables are processed. Clearing those fields cause a VSAMCICS file to be processed as a native VSAM file. |
Character, write |
| MODIFYTABLE.tablename | VTB.OPTBCLAD | Clear Adabas map related fields. Set this value to 1 to clear
the database ID, file number, and subsystem name fields. Setting this value to 0 has no effect. The fields are cleared before any other variables are processed. |
Character, write |
| MODIFYTABLE.tablename | VTB.OPTBCLD2 | Clear Db2 map related fields. Set this value to 1 to clear the table name, subsystem map
name, table creator name, plan name, and user ID fields. Setting this value to 0 has no effect. The fields are cleared before any other variables are processed. |
Character, write |
| MODIFYTABLE.tablename | VTB.OPTBCLIM | Clear IMS DB map related fields. Set this value to 1 to clear
the segment name, DBD name, and PSB name fields. Setting this value to 0 has no effect. The fields are cleared before any other variables are processed. |
Character, write |
| MODIFYTABLE.tablename | VTB.OPTBCLIV | Clear IMS view map related fields. Set this value to 1 to clear
the segment name, DBD name, and PSB name fields. Setting this value to 0 has no effect. The fields are cleared before any other variables are processed. |
Character, write |
| MODIFYTABLE.tablename | VTB.OPTBDSNA | Set the 1 to 44-character VSAM or sequential data set name. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBMEMA | Set the 1 to 8-character sequential data set member name. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBPRWR | Set the 1 to 8-character VSAM, VSAMCICS, or sequential data set pre-write exit name. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBPSRD | Set the 1 to 8-character VSAM, VSAMCICS, or sequential data set post read exit name. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBVSBF | Set the 1 to 8-character CICS file control table entry name for the base file. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBCONN | Set the 1 to 4-character CICS connection name. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBCITR | Set the 1 to 4-character CICS transaction name. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBADBI | Set the Adabas database ID (DBID) number. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBAFNR | Set the Adabas file number. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBSUBS | Set the 1 to 4-character Adabas subsystem name. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBD2TN | Set the 1 to 128-character Db2 table name. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBD2SN | Set the 1 to 50-character Db2 subsystem map name. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBD2TC | Set the 1 to 8-character Db2 table creator ID. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBD2PN | Set the 1 to 8-character Db2 plan name. | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBIMSN | Set the 1 to 8-character IMS DB segment name | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBIMDN | Set the 1 to 8-character IMS DB DBD name | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBPSB | Set the 1 to 8-character IMS DB PSB name | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBIVSG | Set the 1 to 8-character IMS view segment name | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBIVDB | Set the 1 to 8-character IMS view DBD name | Character, write |
| MODIFYTABLE.tablename | VTB.OPTBIVPS | Set the 1 to 8-character IMS view PSB name | Character, write |
| GETALIASES.tablename | VTB.OPTBLIST | Set a list of 1 to 50-character table names that are the aliases
of map tablename. There is room for up to 637, 50-character alias names that are separated by a blank. More alias names are possible if they are shorter. |
Character, write |