IBM i Global

 View Only
Expand all | Collapse all

Is it possible to use three-part name in SQL UDTF?

  • 1.  Is it possible to use three-part name in SQL UDTF?

    Posted Thu August 05, 2021 04:08 PM
    Hello,

    I'm trying to access another IBM i box (partition) from SQL UDTF by referring to "Accessing Multiple DB2 Relational Databases In A Single Query" article.
    But I'm getting "CPF503E User-defined function error on member QSQPTABL. The error code is 1."

    • I can browse remote table from STRSQL by "SELECT * FROM remoteDB/QUSRSYS/QATOCSTART".
    • Below is test UDTF which accesses to remoteDB.
    CREATE OR REPLACE FUNCTION TESTLIB/TCPSTR()
  RETURNS TABLE (                          
    SERVER CHAR(30),                       
    AUTOSTART CHAR(4),                     
    LIBRARY CHAR(10),                      
    PROGRAM CHAR(10)                       
  )                                        
  LANGUAGE SQL                             
  DISALLOW PARALLEL                        
  CARDINALITY 150                          
  SET OPTION COMMIT = *NONE                
  RETURN (SELECT SERVER,                   
                AUTOSTART,                 
                LIBRARY,                   
                PROGRAM                    
      FROM remteDB/QUSRSYS/QATOCSTART)​
    • I get CPF503E when I call the UDTF from STRSQL by "SELECT * FROM TABLE(TESTLIB/TCPSTR()) A"
    • If I remove "remoteDB" from the source code, the UDTF runs fine for local DB.
    • I'm on IBM i 7.4, Db2 group PTF level.12. 
    Anybody has an idea to resolve the issue?
    Thanks!

    ------------------------------
    英幸 矢作
    ------------------------------


  • 2.  RE: Is it possible to use three-part name in SQL UDTF?

    IBM Champion
    Posted Fri August 06, 2021 08:02 AM
    Edited by Rudi Van Helvoirt Fri August 06, 2021 08:02 AM

    Hello,

    If I remember correctly, there is an RFE opened to handle to support this. Currently what you are asking for is not possible. 

    Did give the RFE website a look to find this RFE, but I was unable to find. I still know it is there, so I am much good in finding. 

    Greeting Rudi



    ------------------------------
    Rudi Van Helvoirt
    ------------------------------

    Original Message


  • 3.  RE: Is it possible to use three-part name in SQL UDTF?

    IBM Champion
    Posted Fri August 06, 2021 08:28 AM

    Hello,

    Just when you decided to give up, I had one last look to find the RFE, I did mention in my previous post. This time with success it is called: "Allow three part naming on table functions" 
    Please find below that link:
    http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=129788

    I does not completely match with your issue, but there are similarities. 

    I suggest you vote for it ;-)

    Greetings Rudi



    ------------------------------
    Rudi Van Helvoirt
    ------------------------------

    Original Message


  • 4.  RE: Is it possible to use three-part name in SQL UDTF?

    Posted Mon August 09, 2021 08:40 PM
    Hello, Rudi

    Thank you for you reply and suggestion.
    I checked RFE 113689 "allow three part names in db2 for i services" and found that it had been declined.
    The update states that "This request has a low priority since there is a work-around.".

    I may post new RFE when necessary, like "Allow to use tables from multiple databases in a SQL statement" or such.

    -----------------------------------
    Hideyuki Yahagi @ Japan
    ​

    ------------------------------
    英幸 矢作
    ------------------------------

    Original Message


  • 5.  RE: Is it possible to use three-part name in SQL UDTF?

    Posted Fri August 06, 2021 08:14 AM
    You can do that today using one of three existing methods.

    Create a VIEW over the remote table.
    A Global Temp Table and pull in the data.
    Add a WHERE clause tot the existing SQL statement in the UDTF with the 3-level name on it, as shown below.
    -Bob Cozzi

    RETURN (SELECT SERVER,                   
                AUTOSTART,                 
                LIBRARY,                   
                PROGRAM                    
      FROM remteDB/QUSRSYS/QATOCSTART
     WHERE EXISTS (Select * from REMOTEDB.SYSIBM.SYSDUMMY1) );​​


    ------------------------------
    Robert Cozzi
    ------------------------------

    Original Message


  • 6.  RE: Is it possible to use three-part name in SQL UDTF?

    IBM Champion
    Posted Fri August 06, 2021 01:08 PM
    Bob,
    I think when you use the "where exists" you only specify the REMOTEDB in the where exists clause and not also in the FROM clause.

    ------------------------------
    Robert Berendt
    ------------------------------

    Original Message


  • 7.  RE: Is it possible to use three-part name in SQL UDTF?

    Posted Fri August 06, 2021 03:38 PM
    That would be right, and it forces the query to use the 3-level name to request the information and effectively routes it to the remote. It is a "trick" and the only way to do it today when the FROM is a UDTF.

    ------------------------------
    Robert Cozzi
    ------------------------------

    Original Message


  • 8.  RE: Is it possible to use three-part name in SQL UDTF?

    Posted Mon August 09, 2021 09:18 AM
    As Rob pointed out, I had a typo in my statement due to copy/paste and not editing it.
    I've corrected it here. Basically, until IBM supports 3-level names, natively for UDTF, then using a "dummy" WHERE clause (as shown) will force it to use the remote server as desired. In this example, I'm using the "Get call stack" Table Function to prove it works. Replace REMOTEDB with your remote server identity. Note it is also wrapped in the RETURN (...); as shown in the Table Function of the OP. But to test it with ACS RUN SQL Scripts or SQL iQuery, just copy the SELECT statement within the outer parens and paste it into the desired location.
    RETURN (
SELECT program_name,program_library_name,module_name,procedure_name,activation_group_name
FROM TABLE(qsys2.stack_info()) cs 
WHERE EXISTS (Select * from REMOTEDB.SYSIBM.SYSDUMMY1) 
);​​​


    ------------------------------
    Robert Cozzi
    ------------------------------

    Original Message


  • 9.  RE: Is it possible to use three-part name in SQL UDTF?

    Posted Mon August 09, 2021 09:24 PM
    I modified the code as follows and still got CPF503E.
    CREATE OR REPLACE FUNCTION TESTLIB/TCPSTR()
  RETURNS TABLE (                          
    SERVER CHAR(30),                       
    AUTOSTART CHAR(4),                     
    LIBRARY CHAR(10),                      
    PROGRAM CHAR(10)                       
  )                                        
  LANGUAGE SQL                             
  DISALLOW PARALLEL                        
  CARDINALITY 150                                          
  SET OPTION COMMIT = *NONE                                
  RETURN (SELECT SERVER, AUTOSTART, LIBRARY, PROGRAM       
      FROM QUSRSYS/QATOCSTART                              
      WHERE EXISTS (SELECT * FROM remoteDB/SYSIBM/SYSDUMMY1)
  )                                                        
​

    I ran "SELECT * FROM TABLE(TESTLIB/TCPSTR()) A" from STRSQL.
    In the same STRSQL session, "SELECT * FROM remoteDB/QUSRSYS/QATOCSTART" works fine.

    I don't know why the trick does not work...

    ------------------------------
    英幸 矢作
    ------------------------------

    Original Message


  • 10.  RE: Is it possible to use three-part name in SQL UDTF?

    IBM Champion
    Posted Fri August 06, 2021 01:02 PM
    See the following article:
    https://www.linkedin.com/pulse/faking-3-part-naming-when-using-ibm-i-services-rob-berendt/

    ------------------------------
    Robert Berendt
    ------------------------------

    Original Message


  • 11.  RE: Is it possible to use three-part name in SQL UDTF?

    Posted Fri August 06, 2021 02:31 PM
    For what its worth.  

    I ran:
    CREATE OR REPLACE FUNCTION MYLIB . MYTBLFUNC()
    RETURNS TABLE( SPECIFIC_SCHEMA VARCHAR( 100 )
    , SPECIFIC_NAME VARCHAR( 100 ) )
    LANGUAGE SQL
    DISALLOW PARALLEL
    CARDINALITY 150
    SET OPTION COMMIT = *NONE
    RETURN( SELECT SPECIFIC_SCHEMA
    , SPECIFIC_NAME
    FROM OTHERPART . QSYS2 . SYSFUNCS ) ;

    SELECT *
    FROM TABLE ( MYLIB.MYTBLFUNC() ) X ;

    This seems to work fine.  So my wondering would be, is there a setting different on your other partition?  Can you run the Select statement interactively by itself?  If not, maybe there are authorization issues?  I am on a 7.4 machine that is at DB2 group PTF level 11.

    ------------------------------
    Mark Soulje
    ------------------------------

    Original Message


  • 12.  RE: Is it possible to use three-part name in SQL UDTF?

    Posted Mon August 09, 2021 09:33 PM
    Hello Soulje,

    Thank you for your test.
    It seems like something wrong in my code or system...

    > ...is there a setting different on your other partition? 
    I'm not sure which setting you are wondering.
    I think DRDA is working because I can access to table in "remoteDB" by three-part table name from STRSQL. 

    > Can you run the Select statement interactively by itself? 
    > If not, maybe there are authorization issues?
    If you mean select tables in other system (partition) by three-part naming, yes.

    >  I am on a 7.4 machine that is at DB2 group PTF level 11.
    I'm on 7.4, Db2 12.



    ------------------------------
    英幸 矢作
    ------------------------------

    Original Message


  • 13.  RE: Is it possible to use three-part name in SQL UDTF?

    Posted Tue August 10, 2021 08:21 AM
    Again, to access a 3-part name when using a UDTF or in your RETURN statement
    1. DO NOT use the 3-part name in the main SQL statement
      • Do not use it on the FROM TABLE clause at all
    2. Add to your statement a WHERE clause with the 3-part name.
    WHERE EXISTS (Select * from REMOTEDB.SYSIBM.SYSDUMMY1) ​
    The WHERE clause is the only way to make this work today.


    ------------------------------
    Robert Cozzi
    ------------------------------

    Original Message


  • 14.  RE: Is it possible to use three-part name in SQL UDTF?

    IBM Champion
    Posted Fri August 06, 2021 02:52 PM
    I took another look at your post.
    I ran these on my system:
    CREATE OR REPLACE FUNCTION rob.TCPSTR()
    RETURNS TABLE (
    SERVER CHAR(30),
    AUTOSTART CHAR(4),
    LIBRARY CHAR(10),
    PROGRAM CHAR(10) )
    LANGUAGE SQL
    DISALLOW PARALLEL
    CARDINALITY 150
    SET OPTION COMMIT = *NONE
    RETURN (
    SELECT SERVER, AUTOSTART, LIBRARY, PROGRAM
    FROM gdihq.QUSRSYS.QATOCSTART)
    ;
    SELECT * FROM TABLE(rob.TCPSTR()) A;
    They work fine in both iACS Run SQL Scripts and in STRSQL.
    The only things I changed from your example were:
    1 - Changed from system naming to sql naming just by using the period instead of the slash.
    2 - Put rob as a library name instead of testlib.
    3 - Put the actual remote database name of gdihq instead of remteDB.

    I'm a little newer on PTF's.
    PTF Group Level
    SF99741 8
    SF99740 21091
    SF99739 51
    SF99738 20
    SF99737 4
    SF99736 1
    SF99704 13
    SF99675 2
    SF99668 10
    SF99667 2
    SF99666 6
    SF99665 10
    SF99664 20
    SF99663 7
    SF99662 11
    SF99661 5

    ------------------------------
    Robert Berendt
    ------------------------------

    Original Message


  • 15.  RE: Is it possible to use three-part name in SQL UDTF?

    IBM Champion
    Posted Fri August 06, 2021 02:55 PM
    Make sure that the real value you have for remteDB shows up in WRKRDBDIRE.

    ------------------------------
    Robert Berendt
    ------------------------------

    Original Message


  • 16.  RE: Is it possible to use three-part name in SQL UDTF?

    Posted Mon August 09, 2021 09:42 PM
    Edited by 英幸 矢作 Tue August 10, 2021 05:32 AM
    Hello Berendt,

    It's good to know that three-part table name can be used without "trick" in UDTF.
    As your Db2 PTF group (SF99704 13) is one level newer than that in my system, I'll update SF99704 when possible.

    > They work fine in both iACS Run SQL Scripts and in STRSQL.
    At this time, I use only STRSQL because it seems that SQL script runs differently from STRSQL as for three-part table name.



    ------------------------------
    英幸 矢作
    ------------------------------

    Original Message