Creating Db2 user-defined table functions

Edit online

Use the New UDTF Definitions in DB2 Wizard to create user-defined table functions (UDTFs) in Db2 for z/OS for access to any supported data source type.

Before you begin

Db2 Virtualization (DB2V) is a feature that provides single-point access to various data source types. For additional information about configuring your system to use Db2 for z/OS as a primary access point, see Using Db2 for z/OS to access multiple data source types.

To use this wizard, virtual tables should already exist for your data sources. See Creating virtual tables.

About this task

Use the New UDTF Definitions in DB2 Wizard to create the necessary user-defined table functions and views in Db2 for z/OS for access to any supported data source type. For existing virtual tables, this wizard creates the necessary objects in a local Db2 subsystem so that Data Virtualization Manager data can be queried using Db2 clients.

Procedure

  1. Expand the SQL > Data > SSID node, where SSID is the name of your server.
  2. In the Virtual Tables node, right-click one or more virtual tables, and select Create UDTF Definitions in DB2.
  3. In the New UDTF Definitions in DB2 Wizard, on the Generate DDL with user-defined table functions page, complete the following fields:
    FieldAction
    General DB2 Settings Specify information about the Db2 subsystem where the UDTFs and views will be created.
    • Subsystem: Select the Db2 subsystem ID from the drop-down list.
    • Schema: Select the schema from the drop-down list, or enter a new schema name.
    • GRANT TO: Specify to whom privileges are granted for the generated UDTFs and views. Clear this field to not include the GRANT TO statement in the generated DDL.
    • UDTF Module: This value defaults to the UDTF module in use for your system. If you need to change this value, enter the name of another UDTF module. The following modules are available:
      • AVZUDT9N
      • AVZUDTAN
      • AVZUDTBN
      • AVZUDTCN
      For more information, see "Using Db2 for z/OS to access multiple data source types" in the Installation and Customization Guide.
    • WLM Environment: The address space Db2 starts to run user-defined functions. Leave this field blank to omit the WLM ENVIRONMENT clause in the generated DDL.
    "Generate" Actions Specify whether to execute or save the DDL, or both.
    • Execute generated DDL in DB2: Select this option to execute the generated DDL on the specified Db2 subsystem.
    • Save DDL to file: Optionally, enter a file name (with .sql extension) where to save the DDL. This step might be required if you do not have authorization to execute the DDL. Or, you might want to review the DDL in the SQL Editor first, before running it in Db2.
      • Append to file: Select to append the generated DDL to an existing file.
      • Open file in SQL Editor: Select to open the generated DDL in the SQL Editor.
    Naming Patterns Specify the format to use for the generated function and view names. Use the following variables to create naming patterns:
    • {Server}: Data Virtualization Manager server name
    • {Table}: Virtual table name
    The Function Name and View Name columns are editable in the table, so you can also customize the names on an individual basis.
    Note: If Views is blank, views will not be generated.
  4. Click Generate.

Results

The DDL for creating UDTFs and corresponding views is generated. For more information, see Generated DDL for UDTFs.

After the DDL is executed, a UDTF and a corresponding view are created for each selected virtual table. In the Server tab, locate the new objects by expanding SQL > Data > Other Subsystems > db2SSID > schema, and then the appropriate nodes, as follows:
  • The Db2 views are located in the Views node.
  • The Db2 UDTFs are located in the DB2V > User-Defined Table Functions node.

What to do next

After the Db2 views and UDTFs have been created, you can perform the following tasks:
  • Using the new Db2 views, compose and execute SQL queries to access the data. You can do this from the Data Virtualization Manager studio or from a Db2 client.
  • Using the new Db2 UDTFs, compose and execute SQL queries to select data from the virtual table. Queries can be generated for a specific UDTF function or for a subset of columns within a UDTF.
See Generating and executing SQL queries.