Oracle to DB2 9.7 migration

Raul Baron

Oracle to DB2 9.7 migration
Hi list, we are currently facing an Oracle to DB2 migration project using
DB2 9.7 but we are finding certain issues that could make this impossible.

Here are some of them:

TYPE DECLARATION
================
TYPE declaration is not supported in Functions, Procedures, Triggers or
anonymous blocks. Does anybody know if this is going to be solved short or
medium term ?

FUNCTION DEFINITION
===================
In PACKAGE definitions, the DEFAULT clause is not supported when
defining a
Function.

Also, functions defined in Oracle as:

FUNCTION fu_comprueba_nif(p_nif IN OUT VARCHAR2,
p_tip IN VARCHAR2 DEFAULT NULL) RETURN
BOOLEAN;
produce 2 errors:

1) -142 (OUT not permitted)
2) -104 (DEFAULT not permitted)

and must be rewritten as:

FUNCTION fu_comprueba_nif(p_nif IN VARCHAR2,
p_tip IN VARCHAR2) RETURN BOOLEAN;

Should functions be taken out of the procedure definitions and taken into a
global definition outside? That can imply lots and lots of code touching.

PROCEDURE OVERLOAD
==================
Moreover Procedure Overload is not supported since DB2 doesn't check the
type
but only the number of parameters. Thus this situation:

PROCEDURE insertar_dato_parametro (
nombre IN VARCHAR2,
valor IN VARCHAR2,
tipo IN VARCHAR2,
tipo_dato IN VARCHAR2
);

PROCEDURE insertar_dato_parametro (
posicion IN NUMBER,
valor IN VARCHAR2,
tipo IN VARCHAR2,
tipo_dato IN VARCHAR2
);

is not permitted. (DB2 Error -454).
Is there any known workaround for this or should we manually modify the code
as well? It can be a lot of work.

RAISE_APPLICATION_ERROR
=======================
We have also seen the following problem regarding
RAISE_APPLICATION_ERROR
handling. This works:

RAISE_APPLICATION_ERROR(3,
'ACTUALIZAR_DATO_PARAMETRO: No se han encontrado parametros');

But this doesn't work:

mi_variable:=3;
RAISE_APPLICATION_ERROR(mi_variable,
'ACTUALIZAR_DATO_PARAMETRO: No se han encontrado parametros');

That is, neither variables nor constants seem to be admitted or resolverd,
generating DB2 error -104. It seems like only numbers -as such- can only be
entered in order to make it work. Is there any way to use variables and/or
constants as an argument to RAISE_APPLIACTION_ERROR?

USER NAMES
==========
The names of users that are to be connected to the database are limited to 8
characters.

e.g. user usrgimsc1 cannot connect to the database as himself. He can,
though
get connected to the database as e.g. db2inst1 or whatever name under a
length
of 8 chars.

Isn't there any way to overcome this limit ?

Thanks in advance.

BRGDS,

Raul Baron

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L