UDTF Archives - IT Jungle

Database Enhancements Galore In Technology Refresh

April 20, 2020 Alex Woodie

Db2 for i is the beating heart of the IBM i server. There’s no doubt about that. So when IBM delivers the next set of Technology Refreshes (TRs) for IBM i 7.3 and 7.4 next month, you can bet that they’ll be loaded with new and improved features to keep the database humming.

To get up to speed with the database-related enhancements in IBM i 7.4 TR2 and IBM i 7.3 TR8, IT Jungle caught up to Scott Forstie, a senior technical staff member at IBM’s lab in Rochester, Minnesota, and the database architect for Db2 for i.

There’s …
Read more
Guru: Read a Data Area As a One-Row Table with SQL, Take Two

August 26, 2019 Ted Holt

Fifteen years ago, reader W.G. asked me about the possibility of treating a data area as a one-row table (a physical file with one record) in an SQL query. The question intrigued me because in my System/36 days, I had often wished that I could access the local data area (LDA) as a one-record data file in a query.

Today, thanks to Scott Forstie and his team at IBM, I update my response to W.G. with more information. It’s not that the technique I presented in 2004 is outdated — it’s as relevant as ever — but that the fine …
Read more
Guru: Create A UDTF Wrapper For A Stored Procedure Result Set

February 4, 2019 Michael Sansoterra

In the tip, Arranging Query Logic in DB2 for i Routines, I addressed a reader’s question about how to create a user-defined table function (UDTF) in DB2 for i that would return the same result set as an existing stored procedure. The purpose of having the UDTF would be to do additional processing on a result set, such as joining the result set with another query or dumping the result set to a temporary table for analysis. To accomplish this, I suggested moving the stored procedure query logic into a UDTF and then replace the query within the stored …
Read more
Guru: Arranging Query Logic in DB2 for i Routines

January 7, 2019 Michael Sansoterra

Hey, Mike:

We use a stored procedure to return the result set to Java and display the results to a screen. I would like to make this stored procedure put the result set into a temporary table and then do some processing on the temporary table. Is this possible and if so how can I achieve this? Thanks.

Your dilemma is understandable: stored procedure result sets are great when data needs to be returned to a client. However, once generated, the result set cannot be joined, sorted, or stored in a temporary table. Thankfully, a user-defined table function (UDTF) allows …
Read more

Content archive

Recent Posts

Subscribe

Pages

Search

Database Enhancements Galore In Technology Refresh

Guru: Read a Data Area As a One-Row Table with SQL, Take Two

Guru: Create A UDTF Wrapper For A Stored Procedure Result Set

Guru: Arranging Query Logic in DB2 for i Routines

Content archive

Recent Posts

Subscribe

Pages

Search