SQL code 437 in DB2 V9.5
in [DB2]
|
Prev: if any - sql
Next: logbufsz sizing
From: Damir on 5 Jan 2010 06:18 Hi and happy NY2010 to all, 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
From: Serge Rielau on 6 Jan 2010 07:46 On 1/5/2010 6:18 AM, Damir wrote: > Hi and happy NY2010 to all, > 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? The UDF in Db2 9.5 is inline (liek a view). 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
From: Damir on 7 Jan 2010 03:15 Hi Serge, 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" <srielau(a)ca.ibm.com> wrote in message news:7qjf0bFkjhU1(a)mid.individual.net... > The UDF in Db2 9.5 is inline (liek a view). > 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 >
From: Tonkuma on 7 Jan 2010 06:24 > 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 "_". This may save the heap size. 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!#$%&()* +,-./:;<=>?_''"') , '_' ) ;
From: Lennart on 7 Jan 2010 06:43
On 7 Jan, 09:15, "Damir" <damirwil...(a)yahoo.com> wrote: > Hi Serge, > 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 "_". > You might want to have a look at the translate function: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com..ibm.db2.luw.sql.ref.doc/doc/r0000862.html Example: db2 "values translate('abcd#$%efghABC', '', '#$%@[', '_')" 1 -------------- abcd___efghABC /Lennart |