UDTF Schema question

[Trentation]

Help! I'm trying to create a UDTF and trying to set the schema it targets with a parameter. I've tried the google and spent some time on trial and error but haven't yet found a solution. I've tried to implement SET PATH and SET SCHEMA and have had no luck. Is this possible?

I'm would like to be able to use the same UDTF on month end or production data, determined by a parameter I would pass.

I'm working in Data Studio 4.1 and I have mixed feelings about that, but i'm trying it out and that's for another post.

[kitvb1]

Re: UDTF Schema question

IIRC, when you declare your cursor, just use a host variable for the file name.
(I am not at my box at the moment but can post it for you later).

[B.Hauser]

Re: UDTF Schema question

Kiiiiiiiiiiiit!
Filenames and Libraries cannot be specified as host variables!

@Trentation
I depends on the naming conventions used at compile time and whether static or dynamic SQL was used.

If System Naming convantions were used the object libraries are not resolved at compile time. At runtime the current library is searched.
When using SQL Naming conventions the object libraries for static SQL are resolved at compile time, so setting a current schema at runtime will not affect the data access.
When using SQL Naming conventions and dynamic SQL (without explicitly qualifiing the database object) the CURRENT SCHEMA at runtime is used (per default).

For more information you may read the following articles:
http://www.ibm.com/developerworks/ib...ing/index.html
http://www.ibm.com/developerworks/ib...ql2/index.html

Birgitta

[kitvb1]

Re: UDTF Schema question

Hi Biggie

Maybe my terminology is wrong... here is an example of what I mean.

 EXEC SQL                                          
        Declare IFS_Csr Cursor for                   
        Select filename, change_time, Owner     
           from TABLE(IFS01F(:Hsqlstmt)) as t   
        where type <> '*DIR'                    
        order by filename, change_time;         

 Hsqlstmt = '/ChangefIT/' + %trim(envnpr) +            
             '/' + %trim(pnumpr) + '/' + %editc(task07: 'X') ;

I then close the cursor, change my statement to a different path (below), open the cursor, and fetch:

Hsqlstmt = '/ChangefIT/' + %trim(envnpr) + 
             '/' + %trim(pnumpr) + '/' ;

[B.Hauser]

Re: UDTF Schema question

Kit,
you are talking about a User Defined Table Function (UDTF) IFS01F which is specified in the FROM clause in conjunction the the TABLE keywored where the IFS file name is passed as parameter value.
But an UDTF is not a Table or View (both cannot be specified dynamically, i.e. with a host variable), even though it must be specified in the FROM clause and can be used like any table or view.

If I understood the original question correctly, the poster wanted to execute his SELECT statement within the UDTF a based of tables within different schemas and the schema is passed as parameter.

[Scott Klement]

Re: UDTF Schema question

This could be done w/dynamic SQL.

[Trentation]

Re: UDTF Schema question

Birgitta,
I believe that I have found a good answer in your articles that you linked in your first reply. I'm working my way through it and will post back when/if I've got it working.

Thanks all.

[B.Hauser]

Re: UDTF Schema question

Tentation,

recently I've also written an article about UDTFs (even thought I think it does not cover the problem of this thread, it may help)
Here is the link:
The Power of User-Defined Table Functions/

Birgitta

[Trentation]

Re: UDTF Schema question

Birgitta,
Thanks for your posts, and your articles. I also have some of your slides from COMMON in Austin this year that I use as reference material.

I did get this to work with a dynamic statement. I had to populate a temporary table from the dynamic statement so that I could then return the table. I couldn't see any other way to execute the dynamic statement and return the table, is there something I'm missing?

Trent.

[Scott Klement]

Re: UDTF Schema question

I did get this to work with a dynamic statement. I had to populate a temporary table from the dynamic statement so that I could then return the table. I couldn't see any other way to execute the dynamic statement and return the table, is there something I'm missing?

I don't understand why that would be. Can you explain how you coded this? Is this an SQL table function, or an external table function? Are you calculating the values of the columns yourself, or just drawing them from something like a cursor created for a SELECT statement?

[Trentation]

Re: UDTF Schema question

Scott,
Here's what it looks like minus the large select statement with concatenations of the library name into it so I can use production or month end data.

CREATE FUNCTION EXPLODED_RAW_MATERIAL_INVENTORY(
													@CON DECIMAL(3,0),
													@SCHEMA_PARM DECIMAL(1)
													)

	RETURNS TABLE (
						CON DECIMAL(3,0),
						BRN DECIMAL(2,0),
						ITM CHAR(15),
						RAW_MAT_DESC CHAR(30),
						TITM CHAR(15),
						TOP_LVL_DESC CHAR(30),
						QTY DECIMAL(11,5),
						PQTY DECIMAL(11,5),
						IBTQOH DECIMAL(10,3),
						ONHAND DECIMAL(10,3)
					)	
					
  	
        NOT DETERMINISTIC
	NO EXTERNAL ACTION
	LANGUAGE SQL
	MODIFIES SQL DATA
	

F1: BEGIN
	DECLARE DYN_STRING VARCHAR(20000);
	DECLARE @SCHEMA CHAR(10);
	SET @SCHEMA = CASE WHEN @SCHEMA_PARM = 1 THEN 'this' ELSE 'that' END;
		
	DECLARE GLOBAL TEMPORARY TABLE @RESULT_TABLE (
						CON DECIMAL(3,0),
						BRN DECIMAL(2,0),
						ITM CHAR(15),
						RAW_MAT_DESC CHAR(30),
						TITM CHAR(15),
						TOP_LVL_DESC CHAR(30),
						QTY DECIMAL(11,5),
						PQTY DECIMAL(11,5),
						IBTQOH DECIMAL(10,3),
						ONHAND DECIMAL(10,3)
					 		) WITH REPLACE;
					 		
	SET DYN_STRING = 'INSERT INTO QTEMP.@RESULT_TABLE.......
	....    
            
	PREPARE DYNSQL FROM DYN_STRING;
        EXECUTE DYNSQL; 
	RETURN SELECT * FROM QTEMP.@RESULT_TABLE;   
	

END

[Scott Klement]

Re: UDTF Schema question

I don't understand your example? You are inserting data into a temp table, and returning a cursor to a SELECT from that table -- but how did the data get there to begin with?

I guess my question is... where is the data coming from to start with? If you're getting the data in your UDTF from other tables, then why can't you just return the SELECT that reads those tables (instead of one that reads the temp table)?

If you're calculating the data somehow, then I guess you'll have to use a temp table if you must write the UDTF in SQL. But, if you write it in an HLL (like RPG) then you should have no trouble returning it directly without the temporary table,

[Trentation]

Re: UDTF Schema question

Scott,
The data comes from several tables, with some inventory on hand calculations. The function worked great when first created but then the request came to be able to use it over month end data or production data by accepting a parameter, hence my original question and the dynamically prepared statement. But then I really couldn't get my head around how to return the data from the function using the dynamic statement. So through trial and error and lots of google-ing I got something that "works" but doesn't feel that smooth.

[Scott Klement]

Re: UDTF Schema question

Sorry, I always use external UDTFs... but, can't you just declare a cursor to your dynamic statement, and return the open cursor? Or doesn't that work?

[B.Hauser]

Re: UDTF Schema question

The problem is you want to set the (data) library on the fly based on the value passed as parameter.
Since neither schemas nor tables/views can be managed using host variables, either overrides must be performed or dynamic SQL is required.
If dynamic SQL is used, a temporary table must be created and used, because the RETURN statement or better the SQL statement in conjunction with the RETURN statement cannot be used dynamically.
But instead of executing a SET SCHEMA statement I'd include the schema name in the dynamically generated string.
Something like this:

CREATE FUNCTION EXPLODED_RAW_MATERIAL_INVENTORY(ParCON    DECIMAL(3, 0),
                                                ParSCHEMA DECIMAL(1, 0))

	RETURNS TABLE (CON DECIMAL(3, 0),
                       BRN DECIMAL(2, 0),
                       ITM CHAR(15),
                       RAW_MAT_DESC CHAR(30),
                       TITM CHAR(15),
                       TOP_LVL_DESC CHAR(30),
                       QTY DECIMAL(11, 5),
                       PQTY DECIMAL(11, 5),
                       IBTQOH DECIMAL(10, 3),
                       ONHAND DECIMAL(10, 3))
        NOT DETERMINISTIC
	NO EXTERNAL ACTION
	LANGUAGE SQL
	MODIFIES SQL DATA

F1: BEGIN
    DECLARE LocSTRING VARCHAR(20000);
    DECLARE [B][COLOR="#0000CD"]LocSchema[/COLOR][/B] VarCHAR(10);

    [B][COLOR="#0000CD"]Set LocSchema = Case When ParSchema = 1 Then 'THIS' Else 'THAT' End;[/COLOR][/B]
		
    LocString = 'DECLARE GLOBAL TEMPORARY TABLE RtnRESULT_TABLE 
                      as Select .....                           
                            From ' concat [B][COLOR="#0000CD"]LocSchema[/COLOR] [/B]concat '.YourTable 
                            ... 
                 With Replace';
    Execute Immediate LocString;
    RETURN SELECT * FROM RtnRESULT_TABLE;
END;

It may work in this but in either way you should rethink your design.
The problem might be solved by generating views and/or aliases that can be accessed directly without implementing an additional UDTF.

Just as an aside, never use @ and other special characters in your source code, those special characters are NOT international.
For example I cannot use the @ character in my German source code and have to use § instead.

Birgitta