My first suggestion is one I was told a while ago in a performance class, always use real values when you can. For instance, validate and convert you dates BEFORE adding the them to the merge statement. It looks like you are injecting host language variables into your dynamic merge statement so I see no reason to perform date validation in the dynamic SQL MERGE statement.
Secondly, some date conversions will cause an error but the end results will end up being a null. For instance we use this statement a date conversion function,
<sql>
CREATE FUNCTION YYMDTODATE (
DATENUM DECIMAL(8, 0)
)
RETURNS DATE
SPECIFIC DATEUTLF14
DETERMINISTIC
CONTAINS SQL
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
DATFMT = *ISO,
COMMIT = *NONE ,
DBGVIEW = *SOURCE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
RETURN case when datenum > 0 then
DATE ( DIGITS ( DEC ( DATENUM , 8 , 0 ) ) || '000000' )
end
;
</sql>
With this you will need to be able to handle null return values in your outer statement. It used to have error monitoring. That made the function complicated and did not perform any better (in regards to speed and job log entries).
-Matt
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Jay Vaughn
Sent: Monday, April 29, 2019 12:39 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: need to prevent sql 20226 (maximum number of stacked diagnostics areas has been exeeded)
so... i have a merge statement that is constructed dynamically from a mapping file that contains source and target columns with "user supplied" sql statements as needed for each source/target column handling.
Essentially i am left with a fairly large merge statement structured as basic merge statement... (not actual statement below - just to illustrate of the merge I'm doing)
MERGE INTO RECORDS AR
USING (VALUES (:hv_activity, :hv_description)
FOR :hv_nrows ROWS)
AS AC (ACTIVITY, DESCRIPTION)
ON (AR.ACTIVITY = AC.ACTIVITY)
WHEN MATCHED THEN UPDATE SET DESCRIPTION = AC.DESCRIPTION
WHEN NOT MATCHED THEN INSERT (ACTIVITY, DESCRIPTION)
VALUES (AC.ACTIVITY, AC.DESCRIPTION);
nonetheless, this all works great... until I throw in some "user supplied" sql that utilizes a UDF to handle data manipulation on the source date columns
I can assure you the merge statement and UDF are working correctly...
however my UDF has a "declare continue handler for sqlexception" in which I believe that once it comes across so many bad source dates and the exit handler is executed so many times, it is throwing the sql
20226 max number of stacked diagnostics areas has been exceeded, and causing my merge statement to end prematurely. Though I do not want to add "NOT ATOMIC CONTINUE ON SQLEXCEPTION" on my merge statement because I do indeed want it to terminate with exception if there is truly an error i need to know about.
The udf works great but not under this capacity... my gut is to make changes to the UDF, but not really sure that is necessary or the best place to start... below is the udf code...
Anyone got any suggestions?
create or replace function
lscnvtools/IsValidDate (i_dateField varchar(10)
,i_dateValue varchar(10)
,i_rowKey varchar(10)
,i_rowVal varchar(50)
)
returns char (1)
language sql
returns null on null input
modifies sql data
set option commit=*none, datfmt=*iso
begin
declare g_charDate date;
declare continue handler for sqlexception begin
insert into cnverr (erpgmid
,errowkey
,errowval
,ersrccol
,ermsgtxt)
values ('CNVPFSQLR'
,i_rowKey
,i_rowVal
,i_dateField concat ' ' concat i_dateValue
,'Invalid Date Value');
return 'N';
end;
if i_dateValue is null then
return 'N';
end if;
set g_charDate = Date(i_dateValue);
return 'Y';
end
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=JFP8XVJVceP-8al1CsSqlaVYPrhsHtXGpG2hdbOno5s&s=yy23cdD90PaVgQb_u43uK4nZ7Sx15r0xdKVEhC3mYC8&e=
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=JFP8XVJVceP-8al1CsSqlaVYPrhsHtXGpG2hdbOno5s&s=MO35nYYAcpP9FmokvFJnNc7bS-JUrUY9DsA-9qTDzdg&e=.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=JFP8XVJVceP-8al1CsSqlaVYPrhsHtXGpG2hdbOno5s&s=X6Xs_doetdTF2iMhaffKgepNuHgxYBh0jviXF7a7oBw&e=
midrange.com
