On Aug 14, 8:15 am, Serge Rielau <srie...@ca.ibm.com> wrote:
> Rahul B wrote:
> > On Aug 13, 7:44 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> >> Rahul B wrote:
> >>> Hi,
> >>> I have very little knowledge about creating Procedures/functions in
> >>> DB2.
> >>> When i tried to create the test function like
> >>> CREATE FUNCTION GET_TEST
> >>> (P_TEST_ID INTEGER,
> >>> P_SEL_OR_SORT INTEGER,
> >>> P_TEST VARCHAR(2)
> >>> )
> >>> RETURNS VARCHAR(1000)
> >>> SPECIFIC GET_RULE
> >>> LANGUAGE SQL
> >>> NOT DETERMINISTIC
> >>> READS SQL DATA
> >>> STATIC DISPATCH
> >>> CALLED ON NULL INPUT
> >>> EXTERNAL ACTION
> >>> INHERIT SPECIAL REGISTERS
> >>> BEGIN ATOMIC
> >>> DECLARE v_TEST VARCHAR(4000);
> >>> DECLARE v_TEST_Select VARCHAR(4000);
> >>> DECLARE v_TEST_Sort VARCHAR(1000);
> >>> DECLARE GLOBAL TEMPORARY TABLE TEMP_TEST
> >>> (id INTEGER
> >>> )
> >>> ON COMMIT PRESERVE ROWS;
> >>> SET v_TEST_Select = NULL;
> >>> SET v_TEST_Sort = NULL;
> >>> RETURN(v_TEST);
> >>> END;
> >>> It give an error SQL 0104N "An unexpected token "Table" was found
> >>> following "..are Global Temporary".
> >>> What is the problem in that?
> >> Rahul,
>
> >> In DB2 for LUW SQL Functions are macros. As such they cannot do DDL,
> >> open cursors, etc.
> >> If you have complex SQL PL inside of a function it is advisable to write
> >> a stored procedure and then CALL that procedure from the UDF.
> >> Either way DDL in a function? That's a really bad idea. It would be slow
> >> and eat a lot of CPU.
> >> typically temp tables are declared much higher up. E.g. right after the
> >> connection. Constant DECLARE and DROP of a temp table causes
> >> recompilation of the statements using them which will eat at your CPU.
>
> >> For a list of the legal statements in an SQL Function is here:http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....
>
> >> Cheers
> >> Serge
> >> --
> >> Serge Rielau
> >> DB2 Solutions Development
> >> IBM Toronto Lab- Hide quoted text -
>
> >> - Show quoted text -
>
> > Serge,
>
> > I am trying to create the temporary table inside the procedure because
> > i have to use an array of varchars (the code written is only a portion
> > of the function which i was trying to convert from Oracle to DB2, so i
> > started with the smallest and basic chunk first), and you have said in
> > a thread in this group that we have to use a temporary table since DB2
> > doesn't support arrays.
>
> > I will try to write a proc and call it from the function, but still
> > why should the error come("Unexpected toke Table.....")...
> > Leaving performance issue, is there a synactical error?
>
> Yes it is a syntax error. If you check out teh link I posted to:
> Compound Statement (dynamic) you will find that DECLARE GLOBAL TEMPORARY
> TABLE is not in the syntax diagram, hence a -104.
> Using a temp table to pass around ARRAYS between PROCEDURES using tenmp
> tables has no relevance on DECLARING temps in FUNCTIONs.
> DB2 (unlike Oracle) clearly distinguishes between functions and procedures.
> Functions extend SQL (either expressions or relational operations)
> Procedures encapsulate procedural logic.
> In general do not DECLARE TEMP tables in the procedures that use them.
> Declare them at the beginning of your connections.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

Thanks a lot Serge,

Can you please explain what you mean by
"Declare them(temp tables) at the beginning of your connections."
and
" Functions extend SQL (either expressions or relational operations)"

We have a scenario where application code calls a procedure or a
function, and we support both Oracle and DB2.
Further, we don't want the application code to call different
procedures/functions if the databases are different(atleast the name
should be same).
Now Oracle uses arrays in its function, i have to use Temp tables in
DB2 function, which cannot be done.

In the previous post(2nd last), you said, "If you have complex SQL PL
inside of a function it is advisable to write
a stored procedure and then CALL that procedure from the UDF. ".
So i thought of writing a procedure (and create temp table there) and
call that proc from my function.
However, you also said,
"In general do not DECLARE TEMP tables in the procedures that use
them."

In this context, i could not get what you mean by "beginning of
connection"?

Please don't mind any questions that may seem "silly" to you, since i
am an amateur to the Databases.

Thanks again.