When to use a UDTF and views./What is the business scenarios they are basically used.

[satya]

Hi Guys,

As the question suggests.also couldn't find any appropriate/proper answer in google.When does a programmer decide to go for a UDTF or views.

As far as usage goes i believe both can be used to return values/tables.

Also would like to know differences between them like provision of parameters in UDTF.

[DeadManWalks]

Re: When to use a UDTF and views./What is the business scenarios they are basically u

Good one. Image a third party that connects using odbc. By doing the view we give them the data with business logic included. There would be no way to do this other wise.

We also do this to make a black box approach for other SQL statements so they don't get too large.

[B.Hauser]

Re: When to use a UDTF and views./What is the business scenarios they are basically u

It's very simple:
1. If only a single SQL statement is necessary to accomplish the task, create and use a view. The SQL statement even can be rather complex, can include multiple joins or Common Table Expressions or Sub-Selects.
2. If multiple steps are necessary (for example you want to access data returned by a CL-command into an outfile), an UDTF must be used.
3. External UDTFs are very powerful, if you need to access internally described files or non-database objects, such as data areas or user spaces with SQL.

As for performance, views can be better optimized by the query optimizer than UDTFs. UDTFs are compiled objects where the data access is encapsulated, so it cannot be analyzed directly at runtime.

Before global Variables (CREATE VARIABLE) could be used, i.e. before release 6.1 we sometimes had to create UDTFs for complex SQL statements where information had to be set variably in a CTE or Sub-Select. For example for Recursions (the starting point must be set in the Recursive Common Table Expression).

For more information about UDTFs look at the following article:
The Power of User Defined Table Functions

Birgitta

[satya]

Re: When to use a UDTF and views./What is the business scenarios they are basically u

It's very simple:
1. If only a single SQL statement is necessary to accomplish the task, create and use a view. The SQL statement even can be rather complex, can include multiple joins or Common Table Expressions or Sub-Selects.
2. If multiple steps are necessary (for example you want to access data returned by a CL-command into an outfile), an UDTF must be used.
3. External UDTFs are very powerful, if you need to access internally described files or non-database objects, such as data areas or user spaces with SQL.

As for performance, views can be better optimized by the query optimizer than UDTFs. UDTFs are compiled objects where the data access is encapsulated, so it cannot be analyzed directly at runtime.

Before global Variables (CREATE VARIABLE) could be used, i.e. before release 6.1 we sometimes had to create UDTFs for complex SQL statements where information had to be set variably in a CTE or Sub-Select. For example for Recursions (the starting point must be set in the Recursive Common Table Expression).

For more information about UDTFs look at the following article:
The Power of User Defined Table Functions

Birgitta

Apologize for not being specific i wanted to know about UDTF written in SQL(suppose i don't want to do any operation on as400 related native objects like da,ifs etc) and views (i know that some of the work such as data area accesss can only be done in rpgle) .suppose some operation that is tobe done on tabels and return values.