Call to SP in partitioned env. not allowed

[PaulR]

Hi,
(Infosphere Warehouse v9.5.1)

Encountered a problem migrating a single partition to multi-partition
Database.

Got a SQL error (don't have the exact code but it was something like
SQL2740 reason code 71) when
trying to create a scalar SQL function that has a CALL to a SP. error
message said "this is not allowed
in a partitioned environment".

The call to the SP is only used as a mechanism to eat the SQL
exception when trying to convert a string
that contains non numeric characters to a float, and instead simply
returns Null rather than throwing a SQL
error i.e ignore value (data can be dirty).

An alternative would be to convert(translate) all non numeric chars in
the string first, but this is clumsy as a list of all possible
non numeric characters would be needed.
Another alternative would be to check that the string only contains
numeric characters, but we can't think of a way of doing this that
wouldn't throw an error or incur siginificant overhead checking each
character individually.

any thoughts welcomed...

[Serge Rielau]

PaulR wrote:

Hi,
(Infosphere Warehouse v9.5.1)

Encountered a problem migrating a single partition to multi-partition
Database.

Got a SQL error (don't have the exact code but it was something like
SQL2740 reason code 71) when
trying to create a scalar SQL function that has a CALL to a SP. error
message said "this is not allowed
in a partitioned environment".

The call to the SP is only used as a mechanism to eat the SQL
exception when trying to convert a string
that contains non numeric characters to a float, and instead simply
returns Null rather than throwing a SQL
error i.e ignore value (data can be dirty).

An alternative would be to convert(translate) all non numeric chars in
the string first, but this is clumsy as a list of all possible
non numeric characters would be needed.
Another alternative would be to check that the string only contains
numeric characters, but we can't think of a way of doing this that
wouldn't throw an error or incur siginificant overhead checking each
character individually.

any thoughts welcomed...

1. Write a Java (or whatever) UDF that does the conversion for you
2. Instead of converting all non-numeric characters convert all numeric
characters fro the test. E.g. if you replace all digits with an empty
string the result should be empty. (Or if you replace all digidt with a
space and trim both sides the result should be empty or a '.'...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[PaulR]

On 31 Jul, 12:08, Serge Rielau <[e-mail]:

PaulR wrote:

Hi,
(Infosphere Warehouse v9.5.1)

Encountered a problem migrating a single partition to multi-partition
Database.

Got a SQL error (don't have the exact code but it was something like
SQL2740 reason code 71) when
trying to create a scalar SQL function that has a CALL to a SP. error
message said "this is not allowed
in a partitioned environment".

The call to the SP is only used as a mechanism to eat the SQL
exception when trying to convert a string
that contains non numeric characters to a float, and instead simply
returns Null rather than throwing a SQL
error i.e ignore value (data can be dirty).

An alternative would be to convert(translate) all non numeric chars in
the string first, but this is clumsy as a list of all possible
non numeric characters would be needed.
Another alternative would be to check that the string only contains
numeric characters, but we can't think of a way of doing this that
wouldn't throw an error or incur siginificant overhead checking each
character individually.

any thoughts welcomed...

1. Write a Java (or whatever) UDF that does the conversion for you
2. Instead of converting all non-numeric characters convert all numeric
characters fro the test. E.g. if you replace all digits with an empty
string the result should be empty. (Or if you replace all digidt with a
space and trim both sides the result should be empty or a '.'...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

OK, Thanks.
I was also interested in the wider nature of the restriction ...

Is this restriction likely to remain for partitioned envs.?

PS. SQLCODE - SQL0270.

[Serge Rielau]

PaulR wrote:

Is this restriction likely to remain for partitioned envs.?

For the time being yes. Lifting the restriction is a significant effort
and is not going to happen as an APAR.
So if you need it: Complain - loudly - to your IBM rep.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab