Syntax checker incomplete / is_Open function   

Hello everybody,

I have already asked that question within the Optim Dev. Studio forum, but without success. Because the behaviour is the same in DataStudio 2.x I try it once more :-)
The question is partially copied out of the Optim forum.

Here is the question:

At first the comment to the response within Optim forum:
The problem isn't a compatibility with PL/SQL. The database is set to ORA, and already existing PL/SQL packages are partially ported to DB2. I won't make a long story about that compatibility. There are some hints about that in my Blog (http://blog.trivadis.com/blogs/imsibm/default.aspx); 'unfortunately' in German :-()

And here the described problems:
Example1:

* Create an Array e.g. TABARRAY with 20 slots of VARCHAR(20)
* Create the procedure ()

P1: BEGIN
DECLARE n_list TABARRAY;
DECLARE counter INTEGER;
.....
a loop incrementing a counter by 1
.....
SET n_listcounter = something i.e. a line of a file
--> on this line Optim Dev. is asking for an 'unsigned int' and marks is as a syntax error
if you replace the variable by a fixed value i.e. SET n_list[1] = something the code passes.
end loop
.....
END P1

OR

P1: BEGIN
DECLARE n_list TABARRAY;
.....
loop1: LOOP
CALL UTL_FILE.GET_LINE(v_filehandle, v_line);
IF SQLSTATE1 = '02000'
THEN
LEAVE loop1;
END IF;
INSERT INTO SESSION.tmptabs VALUES (v_line);
END LOOP;
CALL UTL_FILE.FCLOSE(v_filehandle);

.....
SET n_listcounter = ARRAYhttp://SELECT one_column FROM SESSION.tmptabs
--> on this line Optim Dev. is complaining the tablename (but the DGTT is filled with varchar values, so the second "error" with the 'counter'-variable isn't detected :-(
.....
END P1

Both constructs cannot be deployed within Optim/DataStudio because of the "syntax error". If you "deploy" it on the command line by using the DB2 command window, everything works fine and both concepts are callable and return the correct values. I have also build up a module on command line with the procedures and it works. So the behaviour of DataStudio Dev. and Optim Dev. is obviously wrong --> a bug within the tool :-)

I like that self-destructing language functionality :-)

the "readable" syntax of theabove mentioned problem:

Example2:
SET n_list squared_bracket_open counter squared_bracket_closed = something e.g. a line of a file

and

SET n_list squared_bracket_open counter squared_bracket_closed =
ARRAY squared_bracket_open the_sqlselect_statement squared_bracket_closed

Example2:
You use the is_Open()function within UTL_FILE package/module
This function should return an integer (0 or 1) to detect whether a file is opened or not.
During compiliation in Optim/DataStudio Developer and also on command line you receive an error, telling you that the return value doesn't fit the variable you want to write to
SET an_integer_var = UTL_FILE.isOpen(filehandle)

Does the function point to another package, internally? More a bug of DB2 V9.7 GA; obviously not a bug in Optim/DataStudio Dev.

Example1 is A NOGO for writing SP using Optim/DataStudio Developer 2.2 and arrays because debugging is impossible. Same behaviour also in V2.1

Example2 would be (really) nice to have. At the moment also a NOGO for porting Oracle PL/SQL packages and/or SPs using that function - which often occurs because Oracle was able to write into/read from files in the past -

Anyone with the same experiences?

Thank you for any response
Juergen