Toad World® Forums

UDF's do not display correctly if "in" used in parameter definition


#1

Hi,

Take the following UDF:
CREATE or replace FUNCTION “PXX”.“PFLFJE” (
“FTG” CHARACTER(2),
“LAGARTNR” CHARACTER(15),
“TYP” CHARACTER(2) )
RETURNS CHARACTER(15)
SPECIFIC “PFLFJE”
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
BEGIN
return (values( select ‘1’
from syscat.routines
fetch first 1 row only ));
END;

Compile in Toad - works fine.

Then open the udf from the explorer into an editor window. The udf shows as

CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” (
IN “FTG” CHARACTER(2),
IN “LAGARTNR” CHARACTER(15),
IN “TYP” CHARACTER(2) )
RETURNS CHARACTER(15)
SPECIFIC “PFLFJE”
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
BEGIN
return (values( select ‘1’
from syscat.routines
fetch first 1 row only ));
END;

Note Toad has appended “in” in front of the parameters.

Compile - works ok.

Refresh the object in the explorer and then send to editor. Now the udf looks like:

CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” (
IN “FTG” CHARACTER(2),
IN “LAGARTNR” CHARACTER(15),
IN “TYP” CHARACTER(2) )
RETURNS CHARACTER(15)
SPECIFIC “PFLFJE”
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATASET SCHEMA = ‘SYSIBM’;

The body is missing - not good !

We are at db2 9.7 fp 2 on zlinux.

Regards,

John.


#2

Message from: jpositano_718

John -

Which version of Toad for DB2 are you using? I tried thison the latest and the
script looks correct (w/body) after sending it tothe editor.

Thanks
Jeff


Historical Messages

Author: Jeff Positano
Date: Fri Aug 27 06:35:06 PDT 2010
John -

Which version of Toad for DB2 are you using? I tried thison the latest and the
script looks correct (w/body) after sending it tothe editor.

Thanks
Jeff
__

Author: john.enevoldson_671
Date: Wed Aug 25 23:57:09 PDT 2010
Hi, Take the following UDF: CREATE or replace FUNCTION “PXX”.“PFLFJE” ( “FTG”
CHARACTER(2), “LAGARTNR” CHARACTER(15), “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATA BEGIN return (values( select ‘1’ from syscat.routines fetch first
1 row only )); END; Compile in Toad - works fine. Then open the udf from the
explorer into an editor window. The udf shows as CREATE OR REPLACE FUNCTION
“PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP”
CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN return (values( select ‘1’
from syscat.routines fetch first 1 row only )); END; Note Toad has appended “in”
in front of the parameters. Compile - works ok. Refresh the object in the
explorer and then send to editor. Now the udf looks like: CREATE OR REPLACE
FUNCTION “PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN
“TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATASET SCHEMA = ‘SYSIBM’; The body is
missing - not good ! We are at db2 9.7 fp 2 on zlinux. Regards, John.
__



#3

John -

Which version of Toad for DB2 are you using? I tried thison the latest and the script looks correct (w/body) after sending it tothe editor.

Thanks
Jeff


#4

Message from: john.enevoldson_671

Hi, The latest beta drop and also 4.6. Regards, John


Historical Messages

Author: john.enevoldson_671
Date: Fri Aug 27 09:20:44 PDT 2010
Hi, The latest beta drop and also 4.6. Regards, John
__

Author: Jeff Positano
Date: Fri Aug 27 06:35:06 PDT 2010
John -

Which version of Toad for DB2 are you using? I tried thison the latest and the
script looks correct (w/body) after sending it tothe editor.

Thanks
Jeff
__

Author: Jeff Positano
Date: Fri Aug 27 06:35:06 PDT 2010
Message from: jpositano_718 John - Which version of Toad for DB2 are you using?
I tried thison the latest and the script looks correct (w/body) after sending it
tothe editor. Thanks Jeff _______________________________________ Historical
Messages Author: Jeff Positano Date: Fri Aug 27 06:35:06 PDT 2010 John - Which
version of Toad for DB2 are you using? I tried thison the latest and the script
looks correct (w/body) after sending it tothe editor. Thanks Jeff __ Author:
john.enevoldson_671 Date: Wed Aug 25 23:57:09 PDT 2010 Hi, Take the following
UDF: CREATE or replace FUNCTION “PXX”.“PFLFJE” ( “FTG” CHARACTER(2), “LAGARTNR”
CHARACTER(15), “TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE”
LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN return
(values( select ‘1’ from syscat.routines fetch first 1 row only )); END; Compile
in Toad - works fine. Then open the udf from the explorer into an editor window.
The udf shows as CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG”
CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATA BEGIN return (values( select ‘1’ from syscat.routines fetch first
1 row only )); END; Note Toad has appended “in” in front of the parameters.
Compile - works ok. Refresh the object in the explorer and then send to editor.
Now the udf looks like: CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG”
CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATASET SCHEMA = ‘SYSIBM’; The body is missing - not good ! We are at
db2 9.7 fp 2 on zlinux. Regards, John. __


__

Author: john.enevoldson_671
Date: Wed Aug 25 23:57:09 PDT 2010
Hi, Take the following UDF: CREATE or replace FUNCTION “PXX”.“PFLFJE” ( “FTG”
CHARACTER(2), “LAGARTNR” CHARACTER(15), “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATA BEGIN return (values( select ‘1’ from syscat.routines fetch first
1 row only )); END; Compile in Toad - works fine. Then open the udf from the
explorer into an editor window. The udf shows as CREATE OR REPLACE FUNCTION
“PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP”
CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN return (values( select ‘1’
from syscat.routines fetch first 1 row only )); END; Note Toad has appended “in”
in front of the parameters. Compile - works ok. Refresh the object in the
explorer and then send to editor. Now the udf looks like: CREATE OR REPLACE
FUNCTION “PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN
“TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATASET SCHEMA = ‘SYSIBM’; The body is
missing - not good ! We are at db2 9.7 fp 2 on zlinux. Regards, John.
__



#5

Hi,

The latest beta drop and also 4.6.

Regards,

John


#6

Message from: jpositano_718

John -

I found an issue that was fixed recently that looks like it will address this
problem. It is on the next beta, which should be out early next week. Please
download the beta when it is available and let us know if it corrects this
issue.

Thanks
Jeff


Historical Messages

Author: Jeff Positano
Date: Fri Aug 27 13:25:13 PDT 2010
John -

I found an issue that was fixed recently that looks like it will address this
problem. It is on the next beta, which should be out early next week. Please
download the beta when it is available and let us know if it corrects this
issue.

Thanks
Jeff

__

Author: john.enevoldson_671
Date: Fri Aug 27 09:20:44 PDT 2010
Hi, The latest beta drop and also 4.6. Regards, John
__

Author: john.enevoldson_671
Date: Fri Aug 27 09:20:44 PDT 2010
Message from: john.enevoldson_671 Hi, The latest beta drop and also 4.6.
Regards, John _______________________________________ Historical Messages
Author: john.enevoldson_671 Date: Fri Aug 27 09:20:44 PDT 2010 Hi, The latest
beta drop and also 4.6. Regards, John __ Author: Jeff Positano Date: Fri Aug 27
06:35:06 PDT 2010 John - Which version of Toad for DB2 are you using? I tried
thison the latest and the script looks correct (w/body) after sending it tothe
editor. Thanks Jeff __ Author: Jeff Positano Date: Fri Aug 27 06:35:06 PDT 2010
Message from: jpositano_718 John - Which version of Toad for DB2 are you using?
I tried thison the latest and the script looks correct (w/body) after sending it
tothe editor. Thanks Jeff _______________________________________ Historical
Messages Author: Jeff Positano Date: Fri Aug 27 06:35:06 PDT 2010 John - Which
version of Toad for DB2 are you using? I tried thison the latest and the script
looks correct (w/body) after sending it tothe editor. Thanks Jeff __ Author:
john.enevoldson_671 Date: Wed Aug 25 23:57:09 PDT 2010 Hi, Take the following
UDF: CREATE or replace FUNCTION “PXX”.“PFLFJE” ( “FTG” CHARACTER(2), “LAGARTNR”
CHARACTER(15), “TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE”
LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN return
(values( select ‘1’ from syscat.routines fetch first 1 row only )); END; Compile
in Toad - works fine. Then open the udf from the explorer into an editor window.
The udf shows as CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG”
CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATA BEGIN return (values( select ‘1’ from syscat.routines fetch first
1 row only )); END; Note Toad has appended “in” in front of the parameters.
Compile - works ok. Refresh the object in the explorer and then send to editor.
Now the udf looks like: CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG”
CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATASET SCHEMA = ‘SYSIBM’; The body is missing - not good ! We are at
db2 9.7 fp 2 on zlinux. Regards, John. __
_______________________________________ __ Author: john.enevoldson_671 Date: Wed
Aug 25 23:57:09 PDT 2010 Hi, Take the following UDF: CREATE or replace FUNCTION
“PXX”.“PFLFJE” ( “FTG” CHARACTER(2), “LAGARTNR” CHARACTER(15), “TYP”
CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN return (values( select ‘1’
from syscat.routines fetch first 1 row only )); END; Compile in Toad - works
fine. Then open the udf from the explorer into an editor window. The udf shows
as CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN
“LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC
“PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN
return (values( select ‘1’ from syscat.routines fetch first 1 row only )); END;
Note Toad has appended “in” in front of the parameters. Compile - works ok.
Refresh the object in the explorer and then send to editor. Now the udf looks
like: CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN
“LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC
“PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION READS SQL DATASET SCHEMA
= ‘SYSIBM’; The body is missing - not good ! We are at db2 9.7 fp 2 on zlinux.
Regards, John. __ _______________________________________
__

Author: Jeff Positano
Date: Fri Aug 27 06:35:06 PDT 2010
John -

Which version of Toad for DB2 are you using? I tried thison the latest and the
script looks correct (w/body) after sending it tothe editor.

Thanks
Jeff
__

Author: Jeff Positano
Date: Fri Aug 27 06:35:06 PDT 2010
Message from: jpositano_718 John - Which version of Toad for DB2 are you using?
I tried thison the latest and the script looks correct (w/body) after sending it
tothe editor. Thanks Jeff _______________________________________ Historical
Messages Author: Jeff Positano Date: Fri Aug 27 06:35:06 PDT 2010 John - Which
version of Toad for DB2 are you using? I tried thison the latest and the script
looks correct (w/body) after sending it tothe editor. Thanks Jeff __ Author:
john.enevoldson_671 Date: Wed Aug 25 23:57:09 PDT 2010 Hi, Take the following
UDF: CREATE or replace FUNCTION “PXX”.“PFLFJE” ( “FTG” CHARACTER(2), “LAGARTNR”
CHARACTER(15), “TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE”
LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN return
(values( select ‘1’ from syscat.routines fetch first 1 row only )); END; Compile
in Toad - works fine. Then open the udf from the explorer into an editor window.
The udf shows as CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG”
CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATA BEGIN return (values( select ‘1’ from syscat.routines fetch first
1 row only )); END; Note Toad has appended “in” in front of the parameters.
Compile - works ok. Refresh the object in the explorer and then send to editor.
Now the udf looks like: CREATE OR REPLACE FUNCTION “PXX”.“PFLFJE” ( IN “FTG”
CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATASET SCHEMA = ‘SYSIBM’; The body is missing - not good ! We are at
db2 9.7 fp 2 on zlinux. Regards, John. __


__

Author: john.enevoldson_671
Date: Wed Aug 25 23:57:09 PDT 2010
Hi, Take the following UDF: CREATE or replace FUNCTION “PXX”.“PFLFJE” ( “FTG”
CHARACTER(2), “LAGARTNR” CHARACTER(15), “TYP” CHARACTER(2) ) RETURNS
CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION
READS SQL DATA BEGIN return (values( select ‘1’ from syscat.routines fetch first
1 row only )); END; Compile in Toad - works fine. Then open the udf from the
explorer into an editor window. The udf shows as CREATE OR REPLACE FUNCTION
“PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN “TYP”
CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATA BEGIN return (values( select ‘1’
from syscat.routines fetch first 1 row only )); END; Note Toad has appended “in”
in front of the parameters. Compile - works ok. Refresh the object in the
explorer and then send to editor. Now the udf looks like: CREATE OR REPLACE
FUNCTION “PXX”.“PFLFJE” ( IN “FTG” CHARACTER(2), IN “LAGARTNR” CHARACTER(15), IN
“TYP” CHARACTER(2) ) RETURNS CHARACTER(15) SPECIFIC “PFLFJE” LANGUAGE SQL NOT
DETERMINISTIC EXTERNAL ACTION READS SQL DATASET SCHEMA = ‘SYSIBM’; The body is
missing - not good ! We are at db2 9.7 fp 2 on zlinux. Regards, John.
__



#7

John -

I found an issue that was fixed recently that looks like it will address this problem. It is on the next beta, which should be out early next week. Please download the beta when it is available and let us know if it corrects this issue.

Thanks
Jeff