DB2,UDF,modifies sql data

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • amoL
    #1

    DB2,UDF,modifies sql data

    when specifying modifies sql data option ...
    getting following error..

    SQL0104N An unexpected token "MODIFIES SQL DATA" was found following
    "".
    Expected tokens may include: "READS SQL DATA". LINE NUMBER=13.
    SQLSTATE=42601

    where I have some code in external UDF that creates a table in
    database.

    when specifying READS SQL DATA getting SQLCODE 577.

    So cant we modifies data through an UDF ( external UDF) ?
    - amoLpujari

    Tags: None
    • Sean McKeough
      #2
      Re: DB2,UDF,modifie s sql data

      Modifies is not supported EEE...is that the case here?

      amoL wrote:[color=blue]
      > when specifying modifies sql data option ...
      > getting following error..
      >
      > SQL0104N An unexpected token "MODIFIES SQL DATA" was found following
      > "".
      > Expected tokens may include: "READS SQL DATA". LINE NUMBER=13.
      > SQLSTATE=42601
      >
      > where I have some code in external UDF that creates a table in
      > database.
      >
      > when specifying READS SQL DATA getting SQLCODE 577.
      >
      > So cant we modifies data through an UDF ( external UDF) ?
      > - amoLpujari
      >[/color]

        Comment

        • Serge Rielau
          #3
          Re: DB2,UDF,modifie s sql data

          amoL wrote:[color=blue]
          > when specifying modifies sql data option ...
          > getting following error..
          >
          > SQL0104N An unexpected token "MODIFIES SQL DATA" was found following
          > "".
          > Expected tokens may include: "READS SQL DATA". LINE NUMBER=13.
          > SQLSTATE=42601
          >
          > where I have some code in external UDF that creates a table in
          > database.
          >
          > when specifying READS SQL DATA getting SQLCODE 577.
          >
          > So cant we modifies data through an UDF ( external UDF) ?
          > - amoLpujari
          >[/color]
          DB2 V8.1.4 for LUW supports SQL Table functions which MODIFY SQL DATA.
          Starting with DB2 V8.2 for LUW you could CALL a procedure within the
          table function. That procedure then, conceivably, could do DDL.
          However Doing DDL inside of an SQL statement is rather ... bizarre.
          Mind to explain the motivation?

          Cheers
          Serge

          --
          Serge Rielau
          DB2 SQL Compiler Development
          IBM Toronto Lab

            Comment

            • mailar@gmail.com
              #4
              Re: DB2,UDF,modifie s sql data

              Hey Serge,
              You mentioned, one can CALL a procedure but my doubt is ; can the
              procedure being called be a JAVA procedure?
              Also, the table function that can CALL another procedure need to be a
              SQL table function or it can be a external table function as well?

              mailar

              Serge Rielau wrote:[color=blue]
              > amoL wrote:[color=green]
              > > when specifying modifies sql data option ...
              > > getting following error..
              > >
              > > SQL0104N An unexpected token "MODIFIES SQL DATA" was found[/color][/color]
              following[color=blue][color=green]
              > > "".
              > > Expected tokens may include: "READS SQL DATA". LINE NUMBER=13.
              > > SQLSTATE=42601
              > >
              > > where I have some code in external UDF that creates a table in
              > > database.
              > >
              > > when specifying READS SQL DATA getting SQLCODE 577.
              > >
              > > So cant we modifies data through an UDF ( external UDF) ?
              > > - amoLpujari
              > >[/color]
              > DB2 V8.1.4 for LUW supports SQL Table functions which MODIFY SQL[/color]
              DATA.[color=blue]
              > Starting with DB2 V8.2 for LUW you could CALL a procedure within the
              > table function. That procedure then, conceivably, could do DDL.
              > However Doing DDL inside of an SQL statement is rather ... bizarre.
              > Mind to explain the motivation?
              >
              > Cheers
              > Serge
              >
              > --
              > Serge Rielau
              > DB2 SQL Compiler Development
              > IBM Toronto Lab[/color]

                Comment

                • Serge Rielau
                  #5
                  Re: DB2,UDF,modifie s sql data

                  mailar@gmail.co m wrote:[color=blue]
                  > Hey Serge,
                  > You mentioned, one can CALL a procedure but my doubt is ; can the
                  > procedure being called be a JAVA procedure?
                  > Also, the table function that can CALL another procedure need to be a
                  > SQL table function or it can be a external table function as well?
                  >
                  > mailar
                  >
                  > Serge Rielau wrote:
                  >[color=green]
                  >>amoL wrote:
                  >>[color=darkred]
                  >>>when specifying modifies sql data option ...
                  >>>getting following error..
                  >>>
                  >>>SQL0104N An unexpected token "MODIFIES SQL DATA" was found[/color][/color]
                  >
                  > following
                  >[color=green][color=darkred]
                  >>>"".
                  >>>Expected tokens may include: "READS SQL DATA". LINE NUMBER=13.
                  >>>SQLSTATE=426 01
                  >>>
                  >>>where I have some code in external UDF that creates a table in
                  >>>database.
                  >>>
                  >>>when specifying READS SQL DATA getting SQLCODE 577.
                  >>>
                  >>>So cant we modifies data through an UDF ( external UDF) ?
                  >>>- amoLpujari
                  >>>[/color]
                  >>
                  >>DB2 V8.1.4 for LUW supports SQL Table functions which MODIFY SQL[/color]
                  >
                  > DATA.
                  >[color=green]
                  >>Starting with DB2 V8.2 for LUW you could CALL a procedure within the
                  >>table function. That procedure then, conceivably, could do DDL.
                  >>However Doing DDL inside of an SQL statement is rather ... bizarre.
                  >>Mind to explain the motivation?
                  >>
                  >>Cheers
                  >>Serge
                  >>
                  >>--
                  >>Serge Rielau
                  >>DB2 SQL Compiler Development
                  >>IBM Toronto Lab[/color]
                  >[/color]
                  Today only SQL Table functions are allowed. In principle I don't think
                  anything speaks against allowing this for external table functions.
                  We'd have to destroy the pipelining though because the compiler cannot
                  assess read/write conflicts. I.e. in a FROM T, TABLE(foo(T.c1) ) AS F
                  You would see a TEMP above T and one above F (playing SQL by the rules).

                  There is no limitation on the language of procedures or functions called
                  by another function or procedure. For all DB2 cares you can call a CLR
                  routine.

                  Cheers
                  Serge


                  --
                  Serge Rielau
                  DB2 SQL Compiler Development
                  IBM Toronto Lab

                    Comment

                    Previous template Next
                    Working...