Replies to This Discussion

Permalink Reply by Douglas J. Partch, Jr. on January 6, 2020 at 16:28

• ▶ Reply

Permalink Reply by Thiru swamy on February 11, 2020 at 22:56

Hi Gagan,

Not sure you got or not.  If not,

As DB2 UDB supports SQL Pl and Oracle PL/SQL procedures. First set compatibility to "ORACLE"

1. db2set  DB2_COMPATIBILITY_VECTOR=ORA.

2. stop db2 instance db2stop

3. restart db2 instance db2start

4.  Inform db2 instance, that your input file is located T below location using the below procedure.

connect to sample;
CALL SYSIBMADM.UTL_DIR.CREATE_OR_REPLACE_DIRECTORY('empdir', 'C:\temp');

and compile your procedure again with 

DECLARE CONTINUE HANDLER FOR SQLSTATE 'ORANF' SET SQLSTATE1 = SQLSTATE;

--SET SERVEROUTPUT ON@

--#SET TERMINATOR @
--set schema - THIRU'@
CREATE or replace PROCEDURE thiru.proc3()
BEGIN
  DECLARE    v_empfile       UTL_FILE.FILE_TYPE;
  DECLARE    v_dirAlias      VARCHAR(50) DEFAULT 'empdir';
  DECLARE    v_filename      VARCHAR(20) DEFAULT 'empfile.csv';
  DECLARE    v_empline       VARCHAR(200);
  DECLARE    v_count         INTEGER DEFAULT 0;
  DECLARE SQLCODE INTEGER DEFAULT 0;
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE SQLSTATE1 CHAR(5) DEFAULT '00000';
  DECLARE CONTINUE HANDLER FOR SQLSTATE 'ORANF' SET SQLSTATE1 = SQLSTATE;

  SET v_empfile = UTL_FILE.FOPEN(v_dirAlias,v_filename,'r');

  loop1: LOOP
    CALL UTL_FILE.GET_LINE(v_empfile, v_empline);
    IF SQLSTATE1 = 'ORANF' THEN -- NO DATA FOUND
      LEAVE loop1;
    END IF;
    CALL DBMS_OUTPUT.PUT_LINE(v_empline);
    SET v_count = v_count + 1;
  END LOOP;
  CALL DBMS_OUTPUT.PUT_LINE('End of file ' || v_filename || ' - ' || v_count
        || ' records retrieved');
  CALL UTL_FILE.FCLOSE(v_empfile);
END@
--#SET TERMINATOR ;
call  thiru.proc3 ;

After this, hope you will get return code zero.

Return Status = 0

If any questions  let me know.

Rgds,

Thiru Swamy

thiruswamy@gmail.com

• ▶ Reply

Permalink Reply by Gagan Kakkar on February 12, 2020 at 8:38

Hi Thiru,
Thank you for your reply.

Well, I sorted this one out a while back just by replacing  SQLSTATE '02000' with SQLSTATE 'ORANF' as mentioned in the Version 9.7 example.
I already have DB2_COMPATIBILITY_VECTOR set to 'ORA'.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm...

Regards,
Gagan

• ▶ Reply