SQL code 437 in DB2 V9.5
Damir
after upgrading from DB2 V9.1 to V9.5.4 (AIX), we started having a problem
with some SQL statements: certain (small) portion of (same) SQL statement
executions end up with SQL0437 errcode (or rather: a warning code), while
the rest (majority) of executions end normally.
All statements are executed dynamically.
The SQL code is trivial: all statements call a UDF (written in SQL-PL, so -
nothing external, nothing unfenced, etc; and it doesn't touch the data in
the database at all, but only does some string filtering), like for example:
SELECT SOME_UDF(input_param) FROM SYSIBM.SYSDUMMY1
The warning conditions occur intermitently, without any visible pattern.
I tried to extract the Reason Code from the SQLCA, but it gives no clues as
it seems to be empty:
sqlcaid=53514C4341202020
sqlcabc=00000088
sqlcode=00000000
sqlerrml=0000
sqlerrmc=%
Any ideas why this is happening?
(STMTHEAP is set to AUTOMATIC, so that should not be an issue)
Could we ignore the warning condition and proceed with the UOW?
Would switching from UDF to SP help?
Regards,
Damir
Serge Rielau
So if teh fucntion is complex (e.g. it calls other functions or has a
lot of logic) then this can cause the +437.
Now the warning itself is harmless as long as the query is working fat
enough for you.
If you are willing to share teh UDF source code we can take a look and
see if it can be simplified...
In DB2 9.7 any UDF defined with BEGIN (rather than BEGIN ATOMIC) will be
compiled (like a stored procedure). As such teh complexity of the UDF is
then irrelevant to the plan for the invoking query.
If you replace the body of the UDF with a CALL to a stored procedure you
achieve a similar effect in DB2 9.5
Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
Damir
this is the statement that produces the +437 code:
SELECT CLEAN_STRING(CAST(:IN_VAR1 AS VARCHAR(200) ) ),
CLEAN_STRING(CAST(:IN_VAR2 AS VARCHAR(200) ) ),
CLEAN_STRING(CAST(:IN_VAR3 AS VARCHAR(200) ) )
INTO :OUT_VAR1, :OUT_VAR2, :OUT_VAR3
FROM SYSIBM.SYSDUMMY1;
And this is the UDF source code:
CREATE FUNCTION DB2ADMIN.CLEAN_STRING(IN_STRING VARCHAR(200))
RETURNS VARCHAR(200)
SPECIFIC CLEAN_STRING
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN ATOMIC
DECLARE I SMALLINT;
DECLARE CURR_CHAR CHAR(1);
DECLARE OUT_STRING VARCHAR(200);
DECLARE SEARCH_STRING VARCHAR(200);
SET SEARCH_STRING =
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
!#$%&()*+,-./:;<=>?_' || chr(34) || chr(39);
SET I = 1;
SET OUT_STRING = '';
WHILE I <= LENGTH(IN_STRING) DO
SET CURR_CHAR = SUBSTR(IN_STRING, I, 1);
IF (LOCATE(CURR_CHAR, SEARCH_STRING) > 0) THEN
SET OUT_STRING = OUT_STRING || CURR_CHAR;
ELSE
SET OUT_STRING = OUT_STRING || '_';
END IF;
SET I = I + 1;
END WHILE;
RETURN OUT_STRING;
END@
What this UDF does is search the input string for any characters not in the
list of allowed characters (SEARCH_STRING) and replace them with "_".
Thank you for your help!
Damir
---
"Serge Rielau" <sri...@ca.ibm.com> wrote in message
news:7qjf0b...@mid.individual.net...
Tonkuma
> list of allowed characters (SEARCH_STRING) and replace them with "_".
Keep blanks as blanks.
CREATE FUNCTION DB2ADMIN.clean_string(in_string VARCHAR(200))
RETURNS VARCHAR(200)
SPECIFIC CLEAN_STRING
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
TRANSLATE(
in_string
, ' '
, ' ' ||
TRANSLATE(in_string , '' ,
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!#$%&()*
+,-./:;<=>?_''"')
, '_'
)
;
Lennart
You might want to have a look at the translate function:
Example:
db2 "values translate('abcd#$%efghABC', '', '#$%@[', '_')"
1
--------------
abcd___efghABC
/Lennart
Damir
around" to extract the unwanted chars as in your example.
Excellent idea, will try it out ASAP.
Thank you all on your suggestions!
Damir
---
"Tonkuma" <ton...@fiberbit.net> wrote in message
news:710d8a5d-058d-4593...@l30g2000yqb.googlegroups.com...