External User Defined Table Function Db2 for z/OS

Craig Mullins

External User Defined Table Function Db2 for z/OS

Has anybody written an external UDTF for Db2 for z/OS?

I am working to assemble some information on the topic and have found the stuff in the manuals, as well as the sample program (WEATHER) for an external UDTF. Just wondering if anybody can share their experience on how it went implementing one (several) and what the things to keep in mind should be.

Thanks,
Craig S. Mullins
Mullins Consulting, Inc.
http://www.mullinsconsulting.com

Bill Gallagher

External User Defined Table Function Db2 for z/OS
(in response to Craig Mullins)
Craig,

I wrote one (COBOL) many, many years ago at a previous job, just as a POC for myself to become familiar with the process. Both of them read data from a sequential file, populated a global temp table, and then returned the GTT as a table function.

I don’t recall having any issues with implementation. All I needed to do was make sure that the sequential file that I was reading from was defined in the JCL for the SPAS that the table UDF was defined to.

This was over 20 years ago.

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: Craig Mullins <[login to unmask email]>
Sent: Monday, February 25, 2019 3:38 PM
To: [login to unmask email]
Subject: [DB2-L] - External User Defined Table Function Db2 for z/OS


Has anybody written an external UDTF for Db2 for z/OS?

I am working to assemble some information on the topic and have found the stuff in the manuals, as well as the sample program (WEATHER) for an external UDTF. Just wondering if anybody can share their experience on how it went implementing one (several) and what the things to keep in mind should be.

Thanks,
Craig S. Mullins
Mullins Consulting, Inc.
http://www.mullinsconsulting.com

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

David Simpson

External User Defined Table Function Db2 for z/OS
(in response to Craig Mullins)
I think Dan Luksetich is the expert.

On Feb 25, 2019 2:37 PM, Craig Mullins <[login to unmask email]> wrote:

Has anybody written an external UDTF for Db2 for z/OS?

I am working to assemble some information on the topic and have found the stuff in the manuals, as well as the sample program (WEATHER) for an external UDTF. Just wondering if anybody can share their experience on how it went implementing one (several) and what the things to keep in mind should be.

Thanks,
Craig S. Mullins
Mullins Consulting, Inc.
http://www.mullinsconsulting.com

-----End Original Message-----

James Campbell

External User Defined Table Function Db2 for z/OS
(in response to Craig Mullins)
Many years ago. My recollection of the the details are rather vague.

I do recall that I had to take care of managing the scratchpad. But essentially, once I stuck
close to what the manuals said it all went well.

James Campbell

On 25 Feb 2019 at 13:37, Craig Mullins wrote:

>
> Has anybody written an external UDTF for Db2 for z/OS?
> I am working to assemble some information on the topic and have found the stuff in the manuals,
> as well as the sample program (WEATHER) for an external UDTF. Just wondering if anybody can
> share their experience on how it went implementing one (several) and what the things to keep in
> mind should be.
> Thanks,
> Craig S. Mullins
> Mullins Consulting, Inc.
> http://www.mullinsconsulting.com
>
>

---
This email has been checked for viruses by AVG.
https://www.avg.com

Daniel Luksetich

External User Defined Table Function Db2 for z/OS
(in response to David Simpson)
I have written them in C and COBOL. It was some time ago, but they appeared
to work really well. I am currently working with both external and SQLPL
UDFs (non-table), and they too are working really well. When making UDFTs
the interesting thing was that you had to sort of think of yourself, the
program writer, as the data server. For example, when you're done sending
the result set you need to return a SQLSTATE 2000 (EOF, SQLCODE 100) to Db2.
Pretty cool.



Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator - DB2 11 DBA for z/OS

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Application Developer - DB2 11 for z/OS

IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and
Windows



From: David Simpson <[login to unmask email]>
Sent: Monday, February 25, 2019 2:56 PM
To: [login to unmask email]
Cc: [login to unmask email]
Subject: [DB2-L] - RE: External User Defined Table Function Db2 for z/OS



I think Dan Luksetich is the expert.



On Feb 25, 2019 2:37 PM, Craig Mullins <[login to unmask email]
<mailto:[login to unmask email]> > wrote:

Has anybody written an external UDTF for Db2 for z/OS?

I am working to assemble some information on the topic and have found the
stuff in the manuals, as well as the sample program (WEATHER) for an
external UDTF. Just wondering if anybody can share their experience on how
it went implementing one (several) and what the things to keep in mind
should be.

Thanks,
Craig S. Mullins
Mullins Consulting, Inc.
http://www.mullinsconsulting.com



-----End Original Message-----



-----End Original Message-----

Peter Schwarcz

RE: External User Defined Table Function Db2 for z/OS
(in response to Craig Mullins)

Hello Craig,

As with everyone else it's been a while since I have written a mainframe cobol table UDF. 

One thing to note is that the vendor tools had issues trying to extract ddl to define the UDF to db2. As I remember the issue was that there was not enough information in the z/OS db2 catalog to reverse engineer the ddl.

Regard,

Peter Schwarcz

Michael Hannan

RE: External User Defined Table Function Db2 for z/OS
(in response to Daniel Luksetich)



In Reply to Daniel Luksetich:

I have written them in C and COBOL. It was some time ago, but they appeared
to work really well. I am currently working with both external and SQLPL
UDFs (non-table), and they too are working really well. When making UDFTs
the interesting thing was that you had to sort of think of yourself, the
program writer, as the data server. For example, when you're done sending
the result set you need to return a SQLSTATE 2000 (EOF, SQLCODE 100) to Db2.
Pretty cool.

Dan,

Do you have any rule of thumb for the overhead to call an external UDF? I always get concerned about DB2ers using functions inappropriately in huge volumes where the cost might be too high. Absolutely fine for low volumes, but where to draw the lie about what is sensible. 

I have seen queries using UDFs (not external) perform quite slowly when the actual task could be done in SQL without a UDF, knowing some SQL tricks. One was to convert a 4 byte string in COLGROUPCOLNO or similar to an Integer. In old fashion UDFs, it did not perform that well but O.K. if not called too often. I use plain SQL to do it fairly cheaply.

So UDFs that generate inline SQL seem like a good idea, if well done.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Daniel Luksetich

External User Defined Table Function Db2 for z/OS
(in response to Michael Hannan)
Michael,

There is really no performance advantage to user-defined functions, be they native or external. There is casting overhead for every parameter, and then there is the overhead of external procedures. Let’s also not forget the impact (good or bad) of non-deterministic functions on materialization of nested expressions! The advantage of UDFs is when you need to accomplish something in SQL that you otherwise cannot. For example, the legacy assembler program on z/OS that runs a complex process to calculate a value, which is suddenly needed by a web app. Calling it natively would require a jump through several layers, but if you embed it in an external UDF it can be available to that web app in a matter of minutes. So, if you put a performance spin on it then it is really a time to market issue. Just two weeks ago I rewrote an old non-reentrant assembler program as a SQLPL UDF. It is not the best solution, but it only took a week, and suddenly that functionality is easily available to our modern applications (making the assembler module an external UDF would have required NUMTCB=1, not good for millions of transactions). So, UDFs are really useful for enabling modern applications to access legacy functionality when high performance is not the primary concern. The situation is exaggerated for external table UDFs. While they are really cool, and allow you to convert a flat file into a table and then join it to other tables, they are quite expensive in that every record/row returned is it’s own external call. Therefore, the time to market thing is really exaggerated for table UDFs. Imagine you have to web enable a legacy flat file process and have only two weeks to do it. You could do it with table UDFs, and beat your competitors to market, and then immediately start on an improved version of the application because the execution of the UDFs will be prohibitively expensive. However, you’ve sold the solution so money is coming in while you build the new app.



So if it can be done, and you have the development time, then UDFs should be avoided. If given a short implementation time they can be a real advantage!



Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Michael Hannan <[login to unmask email]>
Sent: Monday, March 11, 2019 8:56 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: External User Defined Table Function Db2 for z/OS





In Reply to Daniel Luksetich:

I have written them in C and COBOL. It was some time ago, but they appeared
to work really well. I am currently working with both external and SQLPL
UDFs (non-table), and they too are working really well. When making UDFTs
the interesting thing was that you had to sort of think of yourself, the
program writer, as the data server. For example, when you're done sending
the result set you need to return a SQLSTATE 2000 (EOF, SQLCODE 100) to Db2.
Pretty cool.

Dan,

Do you have any rule of thumb for the overhead to call an external UDF? I always get concerned about DB2ers using functions inappropriately in huge volumes where the cost might be too high. Absolutely fine for low volumes, but where to draw the lie about what is sensible.

I have seen queries using UDFs (not external) perform quite slowly when the actual task could be done in SQL without a UDF, knowing some SQL tricks. One was to convert a 4 byte string in COLGROUPCOLNO or similar to an Integer. In old fashion UDFs, it did not perform that well but O.K. if not called too often. I use plain SQL to do it fairly cheaply.

So UDFs that generate inline SQL seem like a good idea, if well done.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



-----End Original Message-----