Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Mar 2013
    Posts
    12

    Lightbulb Unanswered: DB2 9.7 Function + NickName + Dynamic=> Problem

    Good day! Sorry for my bad English ! I am from Russia.

    I want to write scalar function (Return String).
    This Function work with dynamic scripts and use NickName.

    This SQL Procedure, get SQL QWERY and after dynamic work Return STRING:

    Code:
    CREATE OR REPLACE PROCEDURE TEMP.RETURN_STR
    (IN IN_STR   VARCHAR(32000), 
     OUT OUT_STR VARCHAR(32000)
    ) 
    LANGUAGE SQL
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    NO EXTERNAL ACTION
    OLD SAVEPOINT LEVEL
    READS SQL DATA
    INHERIT SPECIAL REGISTERS
    BEGIN
    DECLARE c1 CURSOR FOR s1;
    PREPARE s1 FROM IN_STR;
    OPEN c1;
    FETCH c1 INTO OUT_STR;
    CLOSE c1;
    RETURN;
    END;
    This is function, which use Procedure(TEMP.RETURN_STR)
    and Nickname (FORM.FUND).

    Code:
    CREATE OR REPLACE FUNCTION TEMP.TESTSC ()
      RETURNS VARCHAR(32000)
    LANGUAGE SQL
    NOT DETERMINISTIC
    READS SQL DATA
    STATIC DISPATCH
    CALLED ON NULL INPUT
    NO EXTERNAL ACTION
    INHERIT SPECIAL REGISTERS
    BEGIN ATOMIC  
    DECLARE STR_RESULT VARCHAR(32000);
    DECLARE STR_EXEC   VARCHAR(32000);
    
    SET STR_EXEC =  'select MAX(ID_PERSON) from FORM.FUND where ID_PERSON_FUND_PARENT  <50';
    CALL TEMP.RETURN_STR (STR_EXEC,STR_RESULT);
    RETURN cast(STR_RESULT as VARCHAR(32000));
    END;
    When i execute , i get error:
    Code:
    select TEMP.TESTSC() from SYSIBM.SYSDUMMY1;
    Lookup Error - DB2 Database Error: ERROR [55047] [IBM][DB2/NT64] SQL20136N Routine "TEMP.RETURN_STR" (specific name "SQL130322110434800") attempted to access a federated object.

    Please, Help Me!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    In your Stored Procedure, try changing "NO EXTERNAL ACTION" to "EXTERNAL ACTION".

    Andy

  3. #3
    Join Date
    Mar 2013
    Posts
    12
    Thanks! I try to do this in monday, and after that write about result

  4. #4
    Join Date
    Mar 2013
    Posts
    12
    >>In your Stored Procedure, try changing "NO EXTERNAL ACTION" to "EXTERNAL ACTION".

    Hi!
    I do this, but mistake stay.
    I thinck about special grants on table, which is sourse for NICKNAME.
    But i do'nt know about need grants or rights!

  5. #5
    Join Date
    Mar 2013
    Posts
    12
    Up!
    Hi people - you ignore me? O my qwestion wery dificult or you all sleeping

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I have no idea, but maybe this is related to:

    Code:
    If the function allows SQL, the external program must not attempt to access any federated objects (SQLSTATE 55047).
    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

  7. #7
    Join Date
    Mar 2013
    Posts
    12

    Lightbulb

    OK! Tnanks for exile
    I tried select * from inline function with nickname - no problem. I tried dinamic in function - no problem. But , when i use NickName + Dynamic in SQL function i get problem. Why?

    If You say about - java or C external function - i tried, but i get identical error. And my boss against external function.
    If undestand, what i can use MQT - but it is not good for us.
    MQT - is evil, on big table we have long refresh and deadlock.

    Thats code not work (with nickname, dynamic)
    Code:
    CREATE OR REPLACE FUNCTION TEMP.TESTSC ()
      RETURNS VARCHAR(32000)
      LANGUAGE SQL
      NOT DETERMINISTIC
      READS SQL DATA
      EXTERNAL ACTION
      INHERIT SPECIAL REGISTERS
    BEGIN   
    DECLARE STR_RESULT VARCHAR(32000);
    DECLARE STR_EXEC   VARCHAR(32000);
    DECLARE c1 CURSOR FOR s1;
    
    SET STR_EXEC =  'select MAX(ID_PERSON) from FORM.NICK_FUND where ID_PERSON_FUND_PARENT  <50';
    PREPARE s1 FROM STR_EXEC;
    OPEN c1;
    FETCH c1 INTO STR_RESULT;
    CLOSE c1;
    RETURN cast(STR_RESULT as VARCHAR(32000));
    END;
    select TEMP.TESTSC() from SYSIBM.SYSDUMMY1;
    This code work (with nickname in inline function, with out dynamic)
    Code:
    CREATE OR REPLACE FUNCTION TEMP.TESTSG ()
      RETURNS VARCHAR(32000)
      LANGUAGE SQL
      NOT DETERMINISTIC
      READS SQL DATA
      EXTERNAL ACTION
      INHERIT SPECIAL REGISTERS
    BEGIN  ATOMIC 
    DECLARE STR_RESULT VARCHAR(32000);
    DECLARE STR_EXEC   VARCHAR(32000);
    
    SET STR_RESULT = cast((select MAX(ID_PERSON) from FORM.NICK_FUND where ID_PERSON_FUND_PARENT  <50)as VARCHAR(32000));
    RETURN cast(STR_RESULT as VARCHAR(32000));
    END;
    select TEMP.TESTSG() from SYSIBM.SYSDUMMY1;

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Obviously there is something you are holding back. If the static Stored Procedure works, than why not use it. Why are you trying to make it dynamic?

    Andy

  9. #9
    Join Date
    Mar 2013
    Posts
    12

    Exclamation

    Thanks, for good qwestions.
    1. It is not stored procedure - it must to be SQL function. Why function - becose i will use this function in construction from table in future. Now we have external function on java, but it not work with Nickname. We have problem with MQT, long refresh and deadloks. We want change MQT on Nickname in all programm.
    2. Why dinamyc -becose now external function use dinamic scripts in thick klients, big inside programm.
    3. It is not my idea, but my boss say, that i must to do this task, else
    PEOPLE HELP ME PLEASE! SOS!

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can write the code as a Stored Procedure, then wrap a SQL Function around it.

    I still do not get the dynamic / static issue. Database functions are called by applications and scripts, not the other way around.

    You either need to justify why it must be dynamic, or give me a better scenario of the situation. The function you presented makes no definitive argument for being dynamic since it has no parameters. And the reason "My boss says so" is insufficient. Why does he/she say so?

    Andy

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by medoed View Post
    We have problem with MQT, long refresh
    How long does refresh take? How long does the MQT's underlying select take (when you execute select statement from the command line)?
    I think refresh doesn't use SMP parallelism.

    Have you tried to disable logging for refresh to see if this helps with the performance?

  12. #12
    Join Date
    Mar 2013
    Posts
    12

    Post

    Quote Originally Posted by ARWinner View Post
    You can write the code as a Stored Procedure, then wrap a SQL Function around it.

    I still do not get the dynamic / static issue. Database functions are called by applications and scripts, not the other way around.

    You either need to justify why it must be dynamic, or give me a better scenario of the situation. The function you presented makes no definitive argument for being dynamic since it has no parameters. And the reason "My boss says so" is insufficient. Why does he/she say so?

    Andy
    Thanks, this function will be use in 200 or 300 places in our big programm. If i write procedure and wrap it in inline sql function, i get identical error about federated objects. You can try someself this and see that problem stay. I presented simple example, but realy argument in this function can be dynamic script, in function i must get result of dynamic select, and after with listagg concatenate in big string(column in string). My boss claver man:-) Hi wants with small blad change architect result and use nickname maximum, if we join Nickname and this function, i will happy
    P.S. I have small expirience with DB2, yearly i worked with MSSQL 7 year, that is why DB2 very dificult for me!

  13. #13
    Join Date
    Mar 2013
    Posts
    12

    Cool

    Quote Originally Posted by db2girl View Post
    How long does refresh take? How long does the MQT's underlying select take (when you execute select statement from the command line)?
    I think refresh doesn't use SMP parallelism.

    Have you tried to disable logging for refresh to see if this helps with the performance?
    Hi! Thanks for you intrest. If in table 10 000 000 record and we have lock andvdeadlock on table we can speak about 3 or 5 minits and 100% load cpu. Yes i do'nt nou about SMP parallelism, i have 3 month experience with db2 and i am not database admin. Inow, that great db administrator tried improve speed work with MQT, but problem is stay! Ithink, that to do function, what i describe - is realy, but my small expirience prevents!

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I still do not understand the requirements you have for this function. Please define the inputs, what the function should do with the inputs and what it should produce as an output.

    Andy

  15. #15
    Join Date
    Mar 2013
    Posts
    12

    Lightbulb

    Quote Originally Posted by ARWinner View Post
    I still do not understand the requirements you have for this function. Please define the inputs, what the function should do with the inputs and what it should produce as an output.

    Andy
    Ok! I satisfy you wish, but do'nt cursing my.

    /*Prepare data */

    Code:
    create table TEMP.FAMILY(id int, name varchar(100));
    insert into TEMP.FAMILY(id,name) VALUES(1,'MOTHER');
    insert into TEMP.FAMILY(id,name) VALUES(2,'FATHER');
    insert into TEMP.FAMILY(id,name) VALUES(3,'BROTHER');
    
    VALUES(TOOLS.JOIN('select name from TEMP.FAMILY',';'))
    -- Result = MOTHER;FATHER;BROTHER

    That is real function:
    Code:
    CREATE OR REPLACE FUNCTION TOOLS.JOIN (
        STR_FROM	VARCHAR(32000),
        SIMB	VARCHAR(100) )
      RETURNS VARCHAR(32000)
      LANGUAGE SQL
      NOT DETERMINISTIC
      EXTERNAL ACTION
      READS SQL DATA
      INHERIT SPECIAL REGISTERS
    BEGIN 
               DECLARE STR_EXEC   VARCHAR(32000); 
               DECLARE STR_RESULT VARCHAR(32000);
               DECLARE SQLSTATE CHAR(5);
               DECLARE WITH_STR INTEGER ;
               DECLARE POS_SELECT INTEGER; 
               DECLARE c1 CURSOR FOR s1 ; -- cursor
               
               -- exists or no with
              SET  WITH_STR = 
              case when   locate(UPPER('with'),UPPER(STR_FROM)) >0  THEN 
              1  else  0 end ;
              
    
              IF(WITH_STR = 0) THEN
                -- operator with not exists it is simple variant
                set STR_EXEC  = 'with  ttt(COL1) as (' || STR_FROM || ' ) ' 
                || ' select ' || ' listagg( ' || 'CAST(COL1 as VARCHAR(32000)) ,' || '''' || SIMB || '''' ||  ') from ttt ';
                
              ELSE
                -- difine  position last select or tceles after reverse
                set POS_SELECT = length(STR_FROM, CODEUNITS16) - locate(UPPER('tceles'),TOOLS.REVERSE(UPPER(STR_FROM))) - 6 ;
              
                -- with exists in  dynamic construction
                set STR_EXEC  =  SUBSTRING(STR_FROM,1,POS_SELECT,CODEUNITS16) 
                || ', ttt(COL1) as ( ' 
                || SUBSTRING(STR_FROM,POS_SELECT + 1 , locate(UPPER('tceles'),TOOLS.REVERSE(UPPER(STR_FROM))) + 6,  CODEUNITS16)
                || ' )'
                || ' select ' || ' listagg( ' || 'CAST(COL1 as VARCHAR(32000)) , ' || '''' || SIMB || '''' ||  ') from ttt ';
                
              END IF;
              
              -- execute in dynamic   
               PREPARE s1 FROM  STR_EXEC ; 
               OPEN c1;
                        WHILE SQLSTATE = '00000' DO 
                        FETCH  c1  INTO STR_RESULT;
                        END WHILE;
               CLOSE c1;
               -- return result String data 
               RETURN cast(STR_RESULT as VARCHAR(32000));
          END;
    Now you undestand, what i want? with table and MQT in dynamic request i have no problem, but when i use NickName in function - i get error, what i write over...

    P.S. If i do'nt resolve this problem, i can be spare

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •