IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 3 replies
  • Latest Post - ‏2019-02-09T23:36:59Z by Jim_IT
robberendt
robberendt
84 Posts

Pinned topic Faking 3-part naming on table functions

‏2019-02-08T15:19:55Z | #db2fori #sql

3-part naming is often not supported when using IBM i Services. Especially those dealing with table functions. So you cannot do  

SELECT x.*
FROM
TABLE (

rmtdb.qsys2.history_log_info()

) x

 

But, by doing a WHERE clause it will associate that table function with the system noted in the where clause. For example:

SELECT x.*
FROM
TABLE (
qsys2.history_log_info()
) x
WHERE 1 IN (select 1 from rmtdb.sysibm.sysdummy1);

 

This will retrieve the QHST information from your rmtdb system.

 

And, if you look at this statement the value of current server will now be rmtdb instead of the local system you ran this command from

SELECT current server, x.*
FROM
TABLE (
qsys2.history_log_info()
) x
WHERE 1 IN (select 1 from rmtdb.sysibm.sysdummy1);

 

Very useful in prepping your dashboard combining data from multiple systems.

 

I've been told that 3-part naming on UDTF will make it here, someday.

https://twitter.com/Forstie_IBMi/status/1021412246205763585

  • Jim_IT
    Jim_IT
    20 Posts

    Re: Faking 3-part naming on table functions

    ‏2019-02-08T23:22:31Z  

    Rob,

    Where in (Select Statement) is fired for each row returned.

     

    Better is:

     

    SELECT x.*
    FROM
    TABLE (
    qsys2.history_log_info()
    ) x
    Cross Join
    rmtdb.sysibm.sysdummy1;

     

    Jim

     

  • robberendt
    robberendt
    84 Posts

    Re: Faking 3-part naming on table functions

    ‏2019-02-09T13:07:48Z  
    • Jim_IT
    • ‏2019-02-08T23:22:31Z

    Rob,

    Where in (Select Statement) is fired for each row returned.

     

    Better is:

     

    SELECT x.*
    FROM
    TABLE (
    qsys2.history_log_info()
    ) x
    Cross Join
    rmtdb.sysibm.sysdummy1;

     

    Jim

     

    Thank you. I prefer this to using where to do a join.

  • Jim_IT
    Jim_IT
    20 Posts

    Re: Faking 3-part naming on table functions

    ‏2019-02-09T23:36:59Z  

    Our 3rd party application has an option to create test environments from production data using an input file. The program uses an insert into table where exists in select from input file statement where customer is equal. I changed the program to just use an inner join and it runs significantly faster.

     

    Scott F. and I discussed three part naming for functions months ago. I'm sure it will get here.

     

    Jim