Toad World® Forums

Compared Stored Procedure does not match


#1

This one is weird and I suspect it’s due to the fact that the SP on the
left was compiled as Native SP in my Q environment vs compiled as a regular SP
in my P environments. The screen shots below are from the compare I ran today.
The compare shows some differences between the SP’s, specifically in the
SP header (native vs regular), but the SP also has changes in the body of the
SP, which are not visible in the compare panels.

The 1st 2 screen shots are the compare, the second the SP as seen by Visual
Studio (Note the highlighted lines, they are not in the left hand side compare
panel).

image001.jpeg

image002.jpeg

image003.jpeg
image001.jpeg


#2

Hi Mark,

The first issue, the extra “set schema” and “set sqlid”
in the SP body, is caused by the different ways we generate text for native and
regular SPs. There may be something we can do to fix that. I’ll open an
issue.

The second issue, the missing columns in the select, is less clear. Can you send
me the source for both SPs so I can try to reproduce the problem?

Adam
image002.jpeg


#3

Hi Mark,

The first issue, the extra “set schema” and “set sqlid”
in the SP body, is caused by the different ways we generate text for native and
regular SPs. There may be something we can do to fix that. I’ll open an
issue.

The second issue, the missing columns in the select, is less clear. Can you send
me the source for both SPs so I can try to reproduce the problem?

Adam
image003.jpeg


#4

Hi Mark,

The first issue, the extra “set schema” and “set sqlid”
in the SP body, is caused by the different ways we generate text for native and
regular SPs. There may be something we can do to fix that. I’ll open an
issue.

The second issue, the missing columns in the select, is less clear. Can you send
me the source for both SPs so I can try to reproduce the problem?

Adam
image001.jpeg


#5

Adam, sending you the most current source from both sides of the compare is not
going to help you, because I will be sending you the source with the actual
additional columns, which is not represented in the left hand compare window.
(Source code is provided below, I used Toad to produce this to be consistent).

I was giving this some thought this morning and I suspect what’s really
happening is that versioning / native vs External is somehow confusing things
and the mechanism that retrieves the SQL is grabbing the source of a prior
version, or possibly a version of the SP that is still in the catalog in the non
native form.

Adam, on a more critical note, the compare really needs some improvement in how
it identifies the changed elements of the SP, honestly highlighting the entire
SQL block is less than helpful when trying to find subtle differences. I made
this observation two beta cycles back and still no improvements in the tool.

Left hand side Native compiled QA environment:

SET SCHEMA = ‘MARK’ ;

SET CURRENT SQLID = ‘MARK’ ;

CREATE PROCEDURE “FDBMD”.“RETRIEVE_PROCEDURES _ACTIVE” ( IN “VPLAN_TYPE” VARCHAR
( 5 ) FOR SBCS DATA CCSID EBCDIC )

LANGUAGE SQL

PARAMETER CCSID EBCDIC

DYNAMIC RESULT SETS 1

WLM ENVIRONMENT FOR DEBUG MODE WLMQENV1

ASUTIME NO LIMIT

NOT DETERMINISTIC

MODIFIES SQL DATA

CALLED ON NULL INPUT

INHERIT SPECIAL REGISTERS

COMMIT ON RETURN NO

–WLM ENVIRONMENT WLMDENV4

STAY RESIDENT YES

RUN OPTIONS ‘’



– DB2 SQL procedure



P1: BEGIN

– Declare variables

DECLARE MAXSEQNBR INTEGER ;

– Declare cursors

DECLARE ADOCURSOR CURSOR WITH RETURN FOR

SELECT PROCEDURE_ACTIVE. PROC_ID, PROCEDURE_ACTIVE. OCC_FROM_ DATE,
PROCEDURE_ACTIVE. OCC_TO_DATE, PROCEDURE_ACTIVE. PLAN_TYPE,

PROCEDURE_ACTIVE. PROC_CODE, PROCEDURE_ACTIVE. PROVIDER, PROCEDURE_ACTIVE.
MODIFIER, PROCEDURE_ACTIVE. PLACE_OF_ SERV,

PROCEDURE_ACTIVE. BILL_TYPE, PROCEDURE_ACTIVE. SEQ_NBR, PROCEDURE_ACTIVE.
DIAGNOSIS, PROCEDURE_ACTIVE. GENDER,

PROCEDURE_ACTIVE. MONTHS_MIN, PROCEDURE_ACTIVE. MONTHS_MAX, PROC_TO_RULE_
SET_ACTIVE. RULE_SET_ TYPE, RULE_ACCUM_COND_ ACTIVE.ACCUM_ NAME,

RULE_SET_ACTIVE. RULE_SET_ ID, RULE_SET_ACTIVE. RULE_SET_ NAME, RULE_SET_ACTIVE.
HIDDEN_SW, RULE_ACTIVE. RULE_NAME, RULE_ACTIVE. RULE_TYPE,

RULE_ACCUM_COND_ ACTIVE.OPERAND, RULE_ACCUM_COND_ ACTIVE.DIRECTION ,
RULE_ACCUM_COND_ ACTIVE.DURATION,

RULE_ACCUM_COND_ ACTIVE.DURATION_ TYPE, RULE_ACCUM_COND_ ACTIVE.RULE_
ACCUM_COND_ ID, RULE_ACCUM_COND_ ACTIVE.CHECK_ FOR_HEADROOM

FROM FDBMD.RULE_ACTIVE AS RULE_ACTIVE INNER JOIN

FDBMD.RULE_ACCUM_ COND_ACTIVE AS RULE_ACCUM_COND_ ACTIVE ON RULE_ACTIVE. RULE_ID
= RULE_ACCUM_COND_ ACTIVE.RULE_ ID INNER JOIN

FDBMD.RULE_SET_ ACTIVE AS RULE_SET_ACTIVE ON RULE_ACTIVE. RULE_SET_ ID =
RULE_SET_ACTIVE. RULE_SET_ ID INNER JOIN

FDBMD.PROC_TO_ RULE_SET_ ACTIVE AS PROC_TO_RULE_ SET_ACTIVE INNER JOIN

FDBMD.PROCEDURE_ ACTIVE AS PROCEDURE_ACTIVE ON PROC_TO_RULE_ SET_ACTIVE. PROC_ID
= PROCEDURE_ACTIVE. PROC_ID ON RULE_SET_ACTIVE. RULE_SET_ ID = PROC_TO_RULE_
SET_ACTIVE. RULE_SET_ ID

WHERE PROCEDURE_ACTIVE. SEQ_NBR = ( SELECT MAX (SEQ_NBR)

FROM FDBMD.PLANS_ ACTIVE_HISTORY AS PLANS_ACTIVE

WHERE PLANS_ACTIVE. PLAN_TYPE = vPLAN_TYPE

AND CURRENT DATE BETWEEN PLANS_ACTIVE. OCC_FROM_ DATE AND PLANS_ACTIVE.
OCC_TO_DATE)

FOR READ ONLY WITH UR ;

– Cursor left open for client application.

OPEN ADOCURSOR ;

END P1 ;

GRANT EXECUTE ON PROCEDURE “FDBMD”.RETRIEVE_PROCEDURE S_ACTIVE TO PUBLIC ;



Right hand side External compiled Prod environment:

SET SCHEMA = ‘MARK’ ;

SET CURRENT SQLID = ‘MARK’ ;

CREATE PROCEDURE “FDBMD”.“RETRIEVE_PROCEDURES _ACTIVE” ( IN “PLAN_TYPE” VARCHAR
( 5 ) FOR SBCS DATA CCSID EBCDIC )

PARAMETER CCSID EBCDIC

LANGUAGE SQL

EXTERNAL NAME ‘SQL18240’

DYNAMIC RESULT SETS 1

COLLID FODBCOLL

WLM ENVIRONMENT WLMDENV4

ASUTIME NO LIMIT

PROGRAM TYPE MAIN

SECURITY DB2

NOT DETERMINISTIC

FENCED

MODIFIES SQL DATA

CALLED ON NULL INPUT

INHERIT SPECIAL REGISTERS

STAY RESIDENT YES

COMMIT ON RETURN NO

STOP AFTER SYSTEM DEFAULT FAILURES



– DB2 SQL procedure



P1: BEGIN

– Declare variables

DECLARE MAXSEQNBR INTEGER ;

– Declare cursors

DECLARE ADOCURSOR CURSOR WITH RETURN FOR

SELECT PROCEDURE_ACTIVE. PROC_ID, PROCEDURE_ACTIVE. OCC_FROM_ DATE,
PROCEDURE_ACTIVE. OCC_TO_DATE, PROCEDURE_ACTIVE. PLAN_TYPE,

PROCEDURE_ACTIVE. PROC_CODE, PROCEDURE_ACTIVE. PROVIDER, PROCEDURE_ACTIVE.
MODIFIER, PROCEDURE_ACTIVE. PLACE_OF_ SERV,

PROCEDURE_ACTIVE. BILL_TYPE, PROCEDURE_ACTIVE. SEQ_NBR, PROCEDURE_ACTIVE.
DIAGNOSIS, PROC_TO_RULE_ SET_ACTIVE. RULE_SET_ TYPE, RULE_ACCUM_COND_
ACTIVE.ACCUM_ NAME,

RULE_SET_ACTIVE. RULE_SET_ ID, RULE_SET_ACTIVE. RULE_SET_ NAME, RULE_SET_ACTIVE.
HIDDEN_SW, RULE_ACTIVE. RULE_NAME, RULE_ACTIVE. RULE_TYPE,

RULE_ACCUM_COND_ ACTIVE.OPERAND, RULE_ACCUM_COND_ ACTIVE.DIRECTION ,
RULE_ACCUM_COND_ ACTIVE.DURATION,

RULE_ACCUM_COND_ ACTIVE.DURATION_ TYPE, RULE_ACCUM_COND_ ACTIVE.RULE_
ACCUM_COND_ ID, RULE_ACCUM_COND_ ACTIVE.CHECK_ FOR_HEADROOM

FROM FDBMD.RULE_ACTIVE AS RULE_ACTIVE INNER JOIN

FDBMD.RULE_ACCUM_ COND_ACTIVE AS RULE_ACCUM_COND_ ACTIVE ON RULE_ACTIVE. RULE_ID
= RULE_ACCUM_COND_ ACTIVE.RULE_ ID INNER JOIN

FDBMD.RULE_SET_ ACTIVE AS RULE_SET_ACTIVE ON RULE_ACTIVE. RULE_SET_ ID =
RULE_SET_ACTIVE. RULE_SET_ ID INNER JOIN

FDBMD.PROC_TO_ RULE_SET_ ACTIVE AS PROC_TO_RULE_ SET_ACTIVE INNER JOIN

FDBMD.PROCEDURE_ ACTIVE AS PROCEDURE_ACTIVE ON PROC_TO_RULE_ SET_ACTIVE. PROC_ID
= PROCEDURE_ACTIVE. PROC_ID ON RULE_SET_ACTIVE. RULE_SET_ ID = PROC_TO_RULE_
SET_ACTIVE. RULE_SET_ ID

WHERE SEQ_NBR = ( SELECT MAX (SEQ_NBR)

FROM FDBMD.PLANS_ ACTIVE_HISTORY AS PLANS_ACTIVE

WHERE PLANS_ACTIVE. PLAN_TYPE = PLAN_TYPE

AND CURRENT DATE BETWEEN OCC_FROM_DATE AND OCC_TO_DATE)

FOR READ ONLY ;

– Cursor left open for client application.

OPEN ADOCURSOR ;

END P1 ;

GRANT EXECUTE ON PROCEDURE “FDBMD”.RETRIEVE_PROCEDURE S_ACTIVE TO PUBLIC ;
image002.jpeg


#6

Adam, sending you the most current source from both sides of the compare is not
going to help you, because I will be sending you the source with the actual
additional columns, which is not represented in the left hand compare window.
(Source code is provided below, I used Toad to produce this to be consistent).

I was giving this some thought this morning and I suspect what’s really
happening is that versioning / native vs External is somehow confusing things
and the mechanism that retrieves the SQL is grabbing the source of a prior
version, or possibly a version of the SP that is still in the catalog in the non
native form.

Adam, on a more critical note, the compare really needs some improvement in how
it identifies the changed elements of the SP, honestly highlighting the entire
SQL block is less than helpful when trying to find subtle differences. I made
this observation two beta cycles back and still no improvements in the tool.

Left hand side Native compiled QA environment:

SET SCHEMA = ‘MARK’ ;

SET CURRENT SQLID = ‘MARK’ ;

CREATE PROCEDURE “FDBMD”.“RETRIEVE_PROCEDURES _ACTIVE” ( IN “VPLAN_TYPE” VARCHAR
( 5 ) FOR SBCS DATA CCSID EBCDIC )

LANGUAGE SQL

PARAMETER CCSID EBCDIC

DYNAMIC RESULT SETS 1

WLM ENVIRONMENT FOR DEBUG MODE WLMQENV1

ASUTIME NO LIMIT

NOT DETERMINISTIC

MODIFIES SQL DATA

CALLED ON NULL INPUT

INHERIT SPECIAL REGISTERS

COMMIT ON RETURN NO

–WLM ENVIRONMENT WLMDENV4

STAY RESIDENT YES

RUN OPTIONS ‘’



– DB2 SQL procedure



P1: BEGIN

– Declare variables

DECLARE MAXSEQNBR INTEGER ;

– Declare cursors

DECLARE ADOCURSOR CURSOR WITH RETURN FOR

SELECT PROCEDURE_ACTIVE. PROC_ID, PROCEDURE_ACTIVE. OCC_FROM_ DATE,
PROCEDURE_ACTIVE. OCC_TO_DATE, PROCEDURE_ACTIVE. PLAN_TYPE,

PROCEDURE_ACTIVE. PROC_CODE, PROCEDURE_ACTIVE. PROVIDER, PROCEDURE_ACTIVE.
MODIFIER, PROCEDURE_ACTIVE. PLACE_OF_ SERV,

PROCEDURE_ACTIVE. BILL_TYPE, PROCEDURE_ACTIVE. SEQ_NBR, PROCEDURE_ACTIVE.
DIAGNOSIS, PROCEDURE_ACTIVE. GENDER,

PROCEDURE_ACTIVE. MONTHS_MIN, PROCEDURE_ACTIVE. MONTHS_MAX, PROC_TO_RULE_
SET_ACTIVE. RULE_SET_ TYPE, RULE_ACCUM_COND_ ACTIVE.ACCUM_ NAME,

RULE_SET_ACTIVE. RULE_SET_ ID, RULE_SET_ACTIVE. RULE_SET_ NAME, RULE_SET_ACTIVE.
HIDDEN_SW, RULE_ACTIVE. RULE_NAME, RULE_ACTIVE. RULE_TYPE,

RULE_ACCUM_COND_ ACTIVE.OPERAND, RULE_ACCUM_COND_ ACTIVE.DIRECTION ,
RULE_ACCUM_COND_ ACTIVE.DURATION,

RULE_ACCUM_COND_ ACTIVE.DURATION_ TYPE, RULE_ACCUM_COND_ ACTIVE.RULE_
ACCUM_COND_ ID, RULE_ACCUM_COND_ ACTIVE.CHECK_ FOR_HEADROOM

FROM FDBMD.RULE_ACTIVE AS RULE_ACTIVE INNER JOIN

FDBMD.RULE_ACCUM_ COND_ACTIVE AS RULE_ACCUM_COND_ ACTIVE ON RULE_ACTIVE. RULE_ID
= RULE_ACCUM_COND_ ACTIVE.RULE_ ID INNER JOIN

FDBMD.RULE_SET_ ACTIVE AS RULE_SET_ACTIVE ON RULE_ACTIVE. RULE_SET_ ID =
RULE_SET_ACTIVE. RULE_SET_ ID INNER JOIN

FDBMD.PROC_TO_ RULE_SET_ ACTIVE AS PROC_TO_RULE_ SET_ACTIVE INNER JOIN

FDBMD.PROCEDURE_ ACTIVE AS PROCEDURE_ACTIVE ON PROC_TO_RULE_ SET_ACTIVE. PROC_ID
= PROCEDURE_ACTIVE. PROC_ID ON RULE_SET_ACTIVE. RULE_SET_ ID = PROC_TO_RULE_
SET_ACTIVE. RULE_SET_ ID

WHERE PROCEDURE_ACTIVE. SEQ_NBR = ( SELECT MAX (SEQ_NBR)

FROM FDBMD.PLANS_ ACTIVE_HISTORY AS PLANS_ACTIVE

WHERE PLANS_ACTIVE. PLAN_TYPE = vPLAN_TYPE

AND CURRENT DATE BETWEEN PLANS_ACTIVE. OCC_FROM_ DATE AND PLANS_ACTIVE.
OCC_TO_DATE)

FOR READ ONLY WITH UR ;

– Cursor left open for client application.

OPEN ADOCURSOR ;

END P1 ;

GRANT EXECUTE ON PROCEDURE “FDBMD”.RETRIEVE_PROCEDURE S_ACTIVE TO PUBLIC ;



Right hand side External compiled Prod environment:

SET SCHEMA = ‘MARK’ ;

SET CURRENT SQLID = ‘MARK’ ;

CREATE PROCEDURE “FDBMD”.“RETRIEVE_PROCEDURES _ACTIVE” ( IN “PLAN_TYPE” VARCHAR
( 5 ) FOR SBCS DATA CCSID EBCDIC )

PARAMETER CCSID EBCDIC

LANGUAGE SQL

EXTERNAL NAME ‘SQL18240’

DYNAMIC RESULT SETS 1

COLLID FODBCOLL

WLM ENVIRONMENT WLMDENV4

ASUTIME NO LIMIT

PROGRAM TYPE MAIN

SECURITY DB2

NOT DETERMINISTIC

FENCED

MODIFIES SQL DATA

CALLED ON NULL INPUT

INHERIT SPECIAL REGISTERS

STAY RESIDENT YES

COMMIT ON RETURN NO

STOP AFTER SYSTEM DEFAULT FAILURES



– DB2 SQL procedure



P1: BEGIN

– Declare variables

DECLARE MAXSEQNBR INTEGER ;

– Declare cursors

DECLARE ADOCURSOR CURSOR WITH RETURN FOR

SELECT PROCEDURE_ACTIVE. PROC_ID, PROCEDURE_ACTIVE. OCC_FROM_ DATE,
PROCEDURE_ACTIVE. OCC_TO_DATE, PROCEDURE_ACTIVE. PLAN_TYPE,

PROCEDURE_ACTIVE. PROC_CODE, PROCEDURE_ACTIVE. PROVIDER, PROCEDURE_ACTIVE.
MODIFIER, PROCEDURE_ACTIVE. PLACE_OF_ SERV,

PROCEDURE_ACTIVE. BILL_TYPE, PROCEDURE_ACTIVE. SEQ_NBR, PROCEDURE_ACTIVE.
DIAGNOSIS, PROC_TO_RULE_ SET_ACTIVE. RULE_SET_ TYPE, RULE_ACCUM_COND_
ACTIVE.ACCUM_ NAME,

RULE_SET_ACTIVE. RULE_SET_ ID, RULE_SET_ACTIVE. RULE_SET_ NAME, RULE_SET_ACTIVE.
HIDDEN_SW, RULE_ACTIVE. RULE_NAME, RULE_ACTIVE. RULE_TYPE,

RULE_ACCUM_COND_ ACTIVE.OPERAND, RULE_ACCUM_COND_ ACTIVE.DIRECTION ,
RULE_ACCUM_COND_ ACTIVE.DURATION,

RULE_ACCUM_COND_ ACTIVE.DURATION_ TYPE, RULE_ACCUM_COND_ ACTIVE.RULE_
ACCUM_COND_ ID, RULE_ACCUM_COND_ ACTIVE.CHECK_ FOR_HEADROOM

FROM FDBMD.RULE_ACTIVE AS RULE_ACTIVE INNER JOIN

FDBMD.RULE_ACCUM_ COND_ACTIVE AS RULE_ACCUM_COND_ ACTIVE ON RULE_ACTIVE. RULE_ID
= RULE_ACCUM_COND_ ACTIVE.RULE_ ID INNER JOIN

FDBMD.RULE_SET_ ACTIVE AS RULE_SET_ACTIVE ON RULE_ACTIVE. RULE_SET_ ID =
RULE_SET_ACTIVE. RULE_SET_ ID INNER JOIN

FDBMD.PROC_TO_ RULE_SET_ ACTIVE AS PROC_TO_RULE_ SET_ACTIVE INNER JOIN

FDBMD.PROCEDURE_ ACTIVE AS PROCEDURE_ACTIVE ON PROC_TO_RULE_ SET_ACTIVE. PROC_ID
= PROCEDURE_ACTIVE. PROC_ID ON RULE_SET_ACTIVE. RULE_SET_ ID = PROC_TO_RULE_
SET_ACTIVE. RULE_SET_ ID

WHERE SEQ_NBR = ( SELECT MAX (SEQ_NBR)

FROM FDBMD.PLANS_ ACTIVE_HISTORY AS PLANS_ACTIVE

WHERE PLANS_ACTIVE. PLAN_TYPE = PLAN_TYPE

AND CURRENT DATE BETWEEN OCC_FROM_DATE AND OCC_TO_DATE)

FOR READ ONLY ;

– Cursor left open for client application.

OPEN ADOCURSOR ;

END P1 ;

GRANT EXECUTE ON PROCEDURE “FDBMD”.RETRIEVE_PROCEDURE S_ACTIVE TO PUBLIC ;
image003.jpeg


#7

Adam, sending you the most current source from both sides of the compare is not
going to help you, because I will be sending you the source with the actual
additional columns, which is not represented in the left hand compare window.
(Source code is provided below, I used Toad to produce this to be consistent).

I was giving this some thought this morning and I suspect what’s really
happening is that versioning / native vs External is somehow confusing things
and the mechanism that retrieves the SQL is grabbing the source of a prior
version, or possibly a version of the SP that is still in the catalog in the non
native form.

Adam, on a more critical note, the compare really needs some improvement in how
it identifies the changed elements of the SP, honestly highlighting the entire
SQL block is less than helpful when trying to find subtle differences. I made
this observation two beta cycles back and still no improvements in the tool.

Left hand side Native compiled QA environment:

SET SCHEMA = ‘MARK’ ;

SET CURRENT SQLID = ‘MARK’ ;

CREATE PROCEDURE “FDBMD”.“RETRIEVE_PROCEDURES _ACTIVE” ( IN “VPLAN_TYPE” VARCHAR
( 5 ) FOR SBCS DATA CCSID EBCDIC )

LANGUAGE SQL

PARAMETER CCSID EBCDIC

DYNAMIC RESULT SETS 1

WLM ENVIRONMENT FOR DEBUG MODE WLMQENV1

ASUTIME NO LIMIT

NOT DETERMINISTIC

MODIFIES SQL DATA

CALLED ON NULL INPUT

INHERIT SPECIAL REGISTERS

COMMIT ON RETURN NO

–WLM ENVIRONMENT WLMDENV4

STAY RESIDENT YES

RUN OPTIONS ‘’



– DB2 SQL procedure



P1: BEGIN

– Declare variables

DECLARE MAXSEQNBR INTEGER ;

– Declare cursors

DECLARE ADOCURSOR CURSOR WITH RETURN FOR

SELECT PROCEDURE_ACTIVE. PROC_ID, PROCEDURE_ACTIVE. OCC_FROM_ DATE,
PROCEDURE_ACTIVE. OCC_TO_DATE, PROCEDURE_ACTIVE. PLAN_TYPE,

PROCEDURE_ACTIVE. PROC_CODE, PROCEDURE_ACTIVE. PROVIDER, PROCEDURE_ACTIVE.
MODIFIER, PROCEDURE_ACTIVE. PLACE_OF_ SERV,

PROCEDURE_ACTIVE. BILL_TYPE, PROCEDURE_ACTIVE. SEQ_NBR, PROCEDURE_ACTIVE.
DIAGNOSIS, PROCEDURE_ACTIVE. GENDER,

PROCEDURE_ACTIVE. MONTHS_MIN, PROCEDURE_ACTIVE. MONTHS_MAX, PROC_TO_RULE_
SET_ACTIVE. RULE_SET_ TYPE, RULE_ACCUM_COND_ ACTIVE.ACCUM_ NAME,

RULE_SET_ACTIVE. RULE_SET_ ID, RULE_SET_ACTIVE. RULE_SET_ NAME, RULE_SET_ACTIVE.
HIDDEN_SW, RULE_ACTIVE. RULE_NAME, RULE_ACTIVE. RULE_TYPE,

RULE_ACCUM_COND_ ACTIVE.OPERAND, RULE_ACCUM_COND_ ACTIVE.DIRECTION ,
RULE_ACCUM_COND_ ACTIVE.DURATION,

RULE_ACCUM_COND_ ACTIVE.DURATION_ TYPE, RULE_ACCUM_COND_ ACTIVE.RULE_
ACCUM_COND_ ID, RULE_ACCUM_COND_ ACTIVE.CHECK_ FOR_HEADROOM

FROM FDBMD.RULE_ACTIVE AS RULE_ACTIVE INNER JOIN

FDBMD.RULE_ACCUM_ COND_ACTIVE AS RULE_ACCUM_COND_ ACTIVE ON RULE_ACTIVE. RULE_ID
= RULE_ACCUM_COND_ ACTIVE.RULE_ ID INNER JOIN

FDBMD.RULE_SET_ ACTIVE AS RULE_SET_ACTIVE ON RULE_ACTIVE. RULE_SET_ ID =
RULE_SET_ACTIVE. RULE_SET_ ID INNER JOIN

FDBMD.PROC_TO_ RULE_SET_ ACTIVE AS PROC_TO_RULE_ SET_ACTIVE INNER JOIN

FDBMD.PROCEDURE_ ACTIVE AS PROCEDURE_ACTIVE ON PROC_TO_RULE_ SET_ACTIVE. PROC_ID
= PROCEDURE_ACTIVE. PROC_ID ON RULE_SET_ACTIVE. RULE_SET_ ID = PROC_TO_RULE_
SET_ACTIVE. RULE_SET_ ID

WHERE PROCEDURE_ACTIVE. SEQ_NBR = ( SELECT MAX (SEQ_NBR)

FROM FDBMD.PLANS_ ACTIVE_HISTORY AS PLANS_ACTIVE

WHERE PLANS_ACTIVE. PLAN_TYPE = vPLAN_TYPE

AND CURRENT DATE BETWEEN PLANS_ACTIVE. OCC_FROM_ DATE AND PLANS_ACTIVE.
OCC_TO_DATE)

FOR READ ONLY WITH UR ;

– Cursor left open for client application.

OPEN ADOCURSOR ;

END P1 ;

GRANT EXECUTE ON PROCEDURE “FDBMD”.RETRIEVE_PROCEDURE S_ACTIVE TO PUBLIC ;



Right hand side External compiled Prod environment:

SET SCHEMA = ‘MARK’ ;

SET CURRENT SQLID = ‘MARK’ ;

CREATE PROCEDURE “FDBMD”.“RETRIEVE_PROCEDURES _ACTIVE” ( IN “PLAN_TYPE” VARCHAR
( 5 ) FOR SBCS DATA CCSID EBCDIC )

PARAMETER CCSID EBCDIC

LANGUAGE SQL

EXTERNAL NAME ‘SQL18240’

DYNAMIC RESULT SETS 1

COLLID FODBCOLL

WLM ENVIRONMENT WLMDENV4

ASUTIME NO LIMIT

PROGRAM TYPE MAIN

SECURITY DB2

NOT DETERMINISTIC

FENCED

MODIFIES SQL DATA

CALLED ON NULL INPUT

INHERIT SPECIAL REGISTERS

STAY RESIDENT YES

COMMIT ON RETURN NO

STOP AFTER SYSTEM DEFAULT FAILURES



– DB2 SQL procedure



P1: BEGIN

– Declare variables

DECLARE MAXSEQNBR INTEGER ;

– Declare cursors

DECLARE ADOCURSOR CURSOR WITH RETURN FOR

SELECT PROCEDURE_ACTIVE. PROC_ID, PROCEDURE_ACTIVE. OCC_FROM_ DATE,
PROCEDURE_ACTIVE. OCC_TO_DATE, PROCEDURE_ACTIVE. PLAN_TYPE,

PROCEDURE_ACTIVE. PROC_CODE, PROCEDURE_ACTIVE. PROVIDER, PROCEDURE_ACTIVE.
MODIFIER, PROCEDURE_ACTIVE. PLACE_OF_ SERV,

PROCEDURE_ACTIVE. BILL_TYPE, PROCEDURE_ACTIVE. SEQ_NBR, PROCEDURE_ACTIVE.
DIAGNOSIS, PROC_TO_RULE_ SET_ACTIVE. RULE_SET_ TYPE, RULE_ACCUM_COND_
ACTIVE.ACCUM_ NAME,

RULE_SET_ACTIVE. RULE_SET_ ID, RULE_SET_ACTIVE. RULE_SET_ NAME, RULE_SET_ACTIVE.
HIDDEN_SW, RULE_ACTIVE. RULE_NAME, RULE_ACTIVE. RULE_TYPE,

RULE_ACCUM_COND_ ACTIVE.OPERAND, RULE_ACCUM_COND_ ACTIVE.DIRECTION ,
RULE_ACCUM_COND_ ACTIVE.DURATION,

RULE_ACCUM_COND_ ACTIVE.DURATION_ TYPE, RULE_ACCUM_COND_ ACTIVE.RULE_
ACCUM_COND_ ID, RULE_ACCUM_COND_ ACTIVE.CHECK_ FOR_HEADROOM

FROM FDBMD.RULE_ACTIVE AS RULE_ACTIVE INNER JOIN

FDBMD.RULE_ACCUM_ COND_ACTIVE AS RULE_ACCUM_COND_ ACTIVE ON RULE_ACTIVE. RULE_ID
= RULE_ACCUM_COND_ ACTIVE.RULE_ ID INNER JOIN

FDBMD.RULE_SET_ ACTIVE AS RULE_SET_ACTIVE ON RULE_ACTIVE. RULE_SET_ ID =
RULE_SET_ACTIVE. RULE_SET_ ID INNER JOIN

FDBMD.PROC_TO_ RULE_SET_ ACTIVE AS PROC_TO_RULE_ SET_ACTIVE INNER JOIN

FDBMD.PROCEDURE_ ACTIVE AS PROCEDURE_ACTIVE ON PROC_TO_RULE_ SET_ACTIVE. PROC_ID
= PROCEDURE_ACTIVE. PROC_ID ON RULE_SET_ACTIVE. RULE_SET_ ID = PROC_TO_RULE_
SET_ACTIVE. RULE_SET_ ID

WHERE SEQ_NBR = ( SELECT MAX (SEQ_NBR)

FROM FDBMD.PLANS_ ACTIVE_HISTORY AS PLANS_ACTIVE

WHERE PLANS_ACTIVE. PLAN_TYPE = PLAN_TYPE

AND CURRENT DATE BETWEEN OCC_FROM_DATE AND OCC_TO_DATE)

FOR READ ONLY ;

– Cursor left open for client application.

OPEN ADOCURSOR ;

END P1 ;

GRANT EXECUTE ON PROCEDURE “FDBMD”.RETRIEVE_PROCEDURE S_ACTIVE TO PUBLIC ;
image001.jpeg


#8

Hi Mark,

I think your observation about fetching the wrong SP version is spot on.
Hopefully, I can reproduce your issue with the info you sent.

Also, I agree with your assessment of the schema compare side-by-side viewer.
I’ll update issue 70,668 with your comments, and investigate the schema
compare options to see if there’s a way to fine-tune the display.

Thanks,

Adam