Topic
  • 3 replies
  • Latest Post - ‏2013-03-14T14:22:37Z by nivanov1
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic problem with cursor code? (porting from Oracle to DB2)

‏2013-03-12T21:52:02Z |
Hello!

We're upgrading one of our applications from an Oracle database to a DB2 database, and I'm trying to modify one of my PL/SQL report scripts so that it will continue to work in DB2, but I'm running into problems.
Here's the watered-down code I'm using:
==========================================================
set serveroutput on size 1000000
--set verify off
set feedback off
set pagesize 2000
set linesize 300
--set trimspool on
set escape on
--spool &5

CURSOR DECLARATIONS
declare
cursor received_files (FI_ROUTER_MASK varchar) is
select substr(jpstats.ah_archive1,instr(jpstats.ah_archive1,'- ')+2),
jpstats.ah_idnr ,
to_char(
(jpstats.ah_timestamp1 + current timezone), 'MM/DD/YYYY HH24:MI:SS'),
(case
when jpstats.ah_status between 0 and 1799 then 'Active'
when jpstats.ah_status between 1800 and 1899 then 'Issue encountered'
else 'FILE RECEIVED'
end) status,
decode (callopstats.ah_name,'ZERO_BYTE_FILE_ALERT','Empty file',
decode (callopstats.ah_archive1,
'EB_DUP_CHECK','Duplicate file',
'EB_DMZ_TO_ARCHIVE','File not fully uploaded',
'EB_INCOMING_ROUTER','Unexpected filename',
'','',
'Other ('||callopstats.ah_archive1||')' )) note
from ah jpstats,
(select * from ah where ah_otype='CALL') callopstats
where jpstats.ah_name like 'EB_ARCHIVE_ROUTER%JP'
and jpstats.ah_status1910
and jpstats.ah_archive1 like FI_ROUTER_MASK
and date(jpstats.ah_timestamp1 + current timezone )
=trunc(current timestamp)
and jpstats.ah_idnr=callopstats.ah_parentact(+)
order by 1,3;

PROCEDURE DECLARATIONS
TZ varchar(25);
JP_MASK varchar(50);
ROUTING_TABLE varchar(128);
FI_ROUTER_MASK varchar(25);
filename varchar(128);
run_num varchar(12);
job_type varchar(25);
workflow_name varchar(128);
start_time varchar(25);
end_time varchar(25);
duration varchar(10);
alarm_time varchar(25);
status varchar(20);
notes varchar(60);
procedure show_received_files(FI_ROUTER_MASK varchar) is
begin
dbms_output.put_line('RECEIVED FILES');
dbms_output.put_line('FILENAME,RUN#,RECEIPT TIME,STATUS,NOTES');
open received_files(FI_ROUTER_MASK);
loop
fetch received_files into filename,
run_num,
end_time,
status,
notes;
EXIT WHEN received_files%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(filename||','||run_num||','||end_time||','
||status||','||notes);
END LOOP;
CLOSE received_files;
end;


MAIN
begin
TZ := '&1';
JP_MASK := '&2'||'_%_JP';
ROUTING_TABLE := '&3';
FI_ROUTER_MASK := '&4 -%';
show_received_files(FI_ROUTER_MASK);
end;
/
==========================================================
When I execute this, I'm getting no rows being returned from the cursor...or at least, nothing printed out from the fetch loop. However, I get several rows back when I run the cursor's query directly, replacing "FI_ROUTER_MASK" with an actual value (e.g., "'FI.TEST -%'"). I'm stumped.
I can't figure out if it's a problem (1) with the cursor definition, (2) the cursor's FI_ROUTER_MASK parameter, (3) a problem with the loop in the show_received_files procedure, or (4) a problem with how I'm building the FI_ROUTER_MASK variable.

Is anything jumping out at anyone here that's obviously wrong?

Thanks in advance!
Updated on 2013-03-14T14:22:37Z at 2013-03-14T14:22:37Z by nivanov1
  • nivanov1
    nivanov1
    231 Posts

    Re: problem with cursor code? (porting from Oracle to DB2)

    ‏2013-03-13T15:34:19Z  
    I would verify, if I were you, that the value of the parameter FI_ROUTER_MASK in the procedure is what you expect it to be.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: problem with cursor code? (porting from Oracle to DB2)

    ‏2013-03-13T21:02:50Z  
    • nivanov1
    • ‏2013-03-13T15:34:19Z
    I would verify, if I were you, that the value of the parameter FI_ROUTER_MASK in the procedure is what you expect it to be.
    Yes, I have done that. That's why you'll notice that 'set verify off' is commented out up at the top. It does appear to be passing the correct string.
  • nivanov1
    nivanov1
    231 Posts

    Re: problem with cursor code? (porting from Oracle to DB2)

    ‏2013-03-14T14:22:37Z  
    Yes, I have done that. That's why you'll notice that 'set verify off' is commented out up at the top. It does appear to be passing the correct string.
    > you'll notice that 'set verify off' is commented out

    I don't see how that tells you what the value is in the procedure. I thought along the lines of using dbms_output.put_line()...