ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

sql UDTF issue

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • sql UDTF issue

    Hi All,

    I have been trying to create a sql UDTF and trying to pass it a variable to make a dynamic query and run it after a long time.

    I want to see how the query is being prepatred .

    I am unable to debug it as a service program after running the sql .

    Also client access has some connectivity issue sconnecting to my server so no option.

    What can be the alternate on the green screen if i need to debug it.

  • #2
    So you have an external UDTF that runs a service program? Have you tried using a Service Entry Point in RDi? IIRC, the 5250 equivalent would be STRSRVJOB and then STRDBG.

    Comment


    • #3
      It's an SQL udtf that I made that runs a simple query based upon the input provided and return, no rpgle, nothing external.i have tried strsrvjob and dbg and then running SQL it doesn't come to the debug session. However the same works for pl SQL procedures.Need to check on how to do it on rdi. But in the meantime if it could have been done on green screen that would be great.Any suggestions.

      Comment


      • #4
        Have you read this?
        This document describes debug methods for SQL procedures, triggers, and UDFs.

        Comment


        • #5
          Thanks Scott I was able to finally get it working .Just added the set option =*print and it works .

          I have got stuck further

          Attaching my code here

          Code:
          CREATE or replace Function READLOG (@input char(8))
          returns table  (input varchar(10) ,
                           intime varchar(20),
                           outtime varchar(20),
                            diff char(10))
          
          NOT DETERMINISTIC
          NO EXTERNAL ACTION
          LANGUAGE SQL
          READS SQL DATA
          SPECIFIC READLOG
          set option dbgview = *source , output=*print
          
          begin
          declare @sql varchar(2500);
          declare quote char(1);
          set @sql=
          'WITH TEMP AS ( '   ||
          'SELECT RTRIM(fld001) concat'':'' concat substr(fld002,1,3) as time,fld002 '
           ||' FROM lib/a12  ' ||
           'WHERE fld002 like ' || quote ||'%workunit%' || @input ||'%'|| quote
           || ' or fld002 like' || quote ||'%'  || @input || '%collected%'||quote||'),'||
           'TEMP1 AS  ('  ||
           'select  fld002,'  ||
           '(substr(a.time,1,10) concat''-'' concat(substr(a.time,12,12))) as k'||
           ' from temp a  ),temp2 as ('                           ||
           'SELECT K,LEAD(K,1) OVER(order by k) as y ,fld002 from temp1'  ||
           ' fetch first row only),temp3 as ('                        ||
          'SELECT RTRIM(fld001) concat'':'' concat substr(fld002,1,3) as time,fld002 '
           ||' FROM lib/a12  ' ||
           'WHERE fld002 like ' || quote ||'%workunit%' || @trace ||'%'|| quote
           || ' or fld002 like' || quote ||'%'  || @lib || '%collected%'||quote||'),'||
           'TEMP1 AS  ('  ||
           'select  fld002,'  ||
           '(substr(a.time,1,10) concat''-'' concat(substr(a.time,12,12))) as k'||
           ' from temp a  ),temp2 as ('                           ||
           'SELECT K,LEAD(K,1) OVER(order by k) as y ,fld002 from temp1'  ||
           ' fetch first row only),temp3 as ('                        ||
           'select replace(k,'':'',''.'') as newk ,'                      ||
           'replace(y,'':'',''.'') as newy  ,fld002 '                     ||
           'from temp2)'                                                  ||
           'select substr(fld002,89,8) as input,'                      ||
                 'newk,newy,'                                             ||
           'timestampdiff(2,char((timestamp(newk)'                        ||
           '-timestamp(newy)))) as diff'                                  ||
           ' from temp3';
           execute immediate @sql;
          return select input,newk,newy,diff from temp3;
          end;
          The query works when i copy it from tehe variable and execute on sql session

          However when the execute immediate is run it gives an error sql0084 statement noe allowes and goes to the end

          Comment


          • #6
            I may be misunderstanding your code, but it appears to me you're trying to run a SELECT statement. You can't use EXECUTE IMMEDIATE to run a SELECT statement. The data has nowhere to go.

            EXECUTE IMMEDIATE combines the basic functions of the PREPARE and EXECUTE statements. It can be used to prepare and execute SQL statements that contain neither variables nor parameter markers.


            You'll have to put the SELECT inside some other statement, such as INSERT or CREATE TABLE.

            Comment


            • #7
              I just took another look at your code and it hit me that you're creating a table function. I had read the post too quickly.

              You don't need EXECUTE IMMEDIATE. Try something like this:

              Code:
              create or replace function thh.GetCustForState
                ( p_in_State   char(2))
                returns table
                   (Name varchar(16), CusNum numeric(6),
                    City varchar(16), State char(2))
                 not deterministic
                 no external action
                 language sql
                 reads sql data
                 specific StateCusts
                 set option dbgview = *source, output=*print
              
              begin
                return with temp as
                    (select init concat ' ' concat lstnam as Name,
                            cusnum, city, state from qiws.qcustcdt
                      where state = p_in_State)
                     select * from temp;
              end;
              Here's the call.

              Code:
              select * from table(thh.GetCustForState ('TX')) as x;

              Comment


              • #8
                I was able to get the output using the above suggestions Thanks Ted,Scott.
                However i have misunderstood the requirement .I am only passing one parameter ,where in real world 20k values needs to be passed .Now i have suggested to pass them in a single parameter,How do i go about substr each paratemter(parameter length is 8 ) and i guess i then have to a loop .

                Comment


                • #9
                  Originally posted by satya View Post
                  I was able to get the output using the above suggestions Thanks Ted,Scott.
                  However i have misunderstood the requirement .I am only passing one parameter ,where in real world 20k values needs to be passed .Now i have suggested to pass them in a single parameter,How do i go about substr each paratemter(parameter length is 8 ) and i guess i then have to a loop .
                  got passsed the loop and substr logic .Now i need to figure out the input parameter max length could be 20k parameter with each parameter legth of 8 equals 16600 +19999 commas .Varchar,char doesn't except such huge sixe

                  Do i have to use any other data types CLOB. ?

                  Comment


                  • #10
                    As long as you stay under 32740 Bytes you do not need a CLOB. If the parameter is greater, you have to pass a Large Object (or Large Object Locator).

                    ... and you are sure, you need 20000 parameters?!
                    ... and how do you concatenate these 20000 different values together before calling your UDTF?
                    I'd rethink my design!

                    Instead of passing 20000 different values as parameters I'd write these information into a (temporary) table and join the Table or read them from this table in the UDTF.
                    In either way it makes no sense to pass 20000 different values to a UDTF!

                    Birgitta

                    Comment


                    • #11
                      Originally posted by Birgitta Hauser View Post
                      As long as you stay under 32740 Bytes you do not need a CLOB. If the parameter is greater, you have to pass a Large Object (or Large Object Locator).

                      ... and you are sure, you need 20000 parameters?!
                      ... and how do you concatenate these 20000 different values together before calling your UDTF?
                      I'd rethink my design!

                      Instead of passing 20000 different values as parameters I'd write these information into a (temporary) table and join the Table or read them from this table in the UDTF.
                      In either way it makes no sense to pass 20000 different values to a UDTF!

                      Birgitta
                      Hi Birgitta the sql call to the udtf is is actually made via java tool and 20000 parameter as passed i don't know how ,but what i have done is ask them to modify their to pass them on a single parameter and then i internally loop and substr the parameter and write it to a file which gets returned.

                      I did try CLOB and it never gave me any error while executing as i said there would be 179000 characters in the parameters at max.Not sure how i test it now.

                      Comment

                      Working...
                      X