DB2 11 z/OS Table Function sample

Marcus Davage

DB2 11 z/OS Table Function sample
Classification: Public

Hi.

A stored procedure returns a result set. Does anyone have any sample DDL for a user-defined, native SQL, table function to call that stored procedure, retrieve the result set and output the result set as a table?

Regards,
Marcus Davage CEng CITP MBCS
IBM Certified DB2 Database Administrator
LinkedIn http://uk.linkedin.com/in/marcusdavage | IDUG http://www.idug.org/p/us/sn/uid=24500 | Twitter http://twitter.com/spufidoo | About.me http://about.me/spufidoo


Lloyds Banking Group plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC95000. Telephone: 0131 225 4555.

Lloyds Bank plc. Registered Office: 25 Gresham Street, London EC2V 7HN. Registered in England and Wales no. 2065. Telephone 0207626 1500.

Bank of Scotland plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC327000. Telephone: 03457 801 801.

Lloyds Bank plc, Bank of Scotland plc are authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and Prudential Regulation Authority.

Halifax is a division of Bank of Scotland plc.

HBOS plc. Registered Office: The Mound, Edinburgh EH1 1YZ. Registered in Scotland no. SC218813.

This e-mail (including any attachments) is private and confidential and may contain privileged material. If you have received this e-mail in error, please notify the sender and delete it (including any attachments) immediately. You must not copy, distribute, disclose or use any of the information in it or any attachments. Telephone calls may be monitored or recorded.

Michael Hannan

RE: DB2 11 z/OS Table Function sample
(in response to Marcus Davage)

In Reply to Marcus Davage:

A stored procedure returns a result set. Does anyone have any sample DDL for a user-defined, native SQL, table function to call that stored procedure, retrieve the result set and output the result set as a table?

Marcus,

As far as I understand, a compiled Scalar Function (User Defined) can have an SQL routine body using SQLPL statements, however the routine body for an SQL Table Function is restricted to a Return Statement that contains an SQL query. So you can't call a Stored procedure as far as I can see. An External Table Function does not have the same restriction but means compiling a program.   Perhaps there will be an extension to Table Functions to use SQLPL  full range of statements in the future.

Native SQL Table Functions should be thought of as being like a View, but having input parameters, giving more flexibility than a view. The performance characteristics of Table Functions have been improved so that predicates involving parameters are not Stage 2 any more (maybe exceptions for complex predicates). I have tried them out in DB2 z 11 and was generally happy with them. I would tend to use them over a View when predicates are not pushing down into the deeper levels of the View subqueries (complex SQLs in the Views), however take into account the advances in Optimizer ability to do predicate push down at every release.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 27, 2018 - 02:12 PM (Europe/Berlin)