Db2 LUW 11.1 - GET_LINE procedure - NO_DATA_FOUND exception SQLSTATE

Hi there,

I am testing this stored procedure using Db2 11.1 on Windows 10 64-bit.
Informational tokens are "DB2 v11.1.4050.859", "s1911120100",
"DYN1911120100WIN64", and Fix Pack "5".

For NO_DATA_FOUND exception I am getting SQLSTATE = 'ORANF' but in the example below its checking for '02000'.
Is this correct or am I missing something ?

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0053697.html

Thanks.

Regards,

Gagan

Views: 106

Reply to This

Replies to This Discussion

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

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

RSS

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

Db2 11.5 External Tables on Line Lab

Started by Phil Downey in What's hot ? Mar 26. 0 Replies

Hi All, George and I built a new hands on Lab for working with Db2 11.5 External tables... takes you through how to define them, query them , dump to them and overall work with them in different scenarios ... takes about 20 minutes. give a try !…Continue

Revoking Sysadm from DB2 Z/os

Started by Chinnapureddy Jaya Prakash in Security and DB2 Mar 4. 0 Replies

Hi, We are having DB2V12 Level 100.  We are looking for best approach for two things1). 'Revoke Sysadm' from existed userids'. 2). Databases are created by userid. so we need to revoke implicitly granted access (like CreateDB, Load, DBADM,..etc).Can…Continue

Tags: (implicit, grants), Database, and, SYSADM

© 2020   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service