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: 373

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

Events

Latest Activity

An event by Annette Zawacki was featured
Thumbnail

IBM Db2 Analytics Accelerator Trends and Directions at ET

February 16, 2021 from 11am to 12pm
Register Today! Whether you are an existing Db2 Analytics Accelerator user or considering becoming one, you won’t want to miss this webcast! Join Daniel Martin, STSM, IBM Db2 Analytics Accelerator Development to hear details on the newest Accelerator capabilities that drive even deeper integration with Db2 for z/OS. …See More
50 minutes ago
Annette Zawacki posted an event
Thumbnail

IBM Db2 Analytics Accelerator Trends and Directions at ET

February 16, 2021 from 11am to 12pm
Register Today! Whether you are an existing Db2 Analytics Accelerator user or considering becoming one, you won’t want to miss this webcast! Join Daniel Martin, STSM, IBM Db2 Analytics Accelerator Development to hear details on the newest Accelerator capabilities that drive even deeper integration with Db2 for z/OS. …See More
50 minutes ago
2 events by Surekha Parekh were featured
Jan 12
Surekha Parekh updated an event

Modernize IBM Z Apps for Hybrid Cloud using APIs at webcast

January 11, 2021 all day
Available on-demand now, this webinar will show you how to provide access to curated, proven and high-performance applications by turning your existing IBM Z® assets into security-rich, consumable and managed APIs. You will also learn some key considerations on how to choose among the numerous API and integration solutions available. Click the link to watch:…See More
Jan 11
francisco venegas liked Surekha Parekh's event Db2 12 Exploiting new functions and Migration Planning PART 1
Jan 11
Mark liked Surekha Parekh's video
Dec 17, 2020
Judith Talavera posted an event

Modernize IBM Z Apps for Hybrid Cloud using APIs at webcast

December 16, 2020 to February 16, 2021
Available on-demand now, this webinar will show you how to provide access to curated, proven and high-performance applications by turning your existing IBM Z® assets into security-rich, consumable and managed APIs. You will also learn some key considerations on how to choose among the numerous API and integration solutions available. Click the link to watch:…See More
Dec 17, 2020
An event by Janttu Lindroos was featured

Finland Db2 User Group (FiDUG) Seminar at IBM Finland

December 10, 2020 from 9am to 5pm
More info will be available later on. Stay tuned.See More
Dec 4, 2020
An event by Michael Strelczuk was featured
Thumbnail

MDUG - Michigan Db2 User Group Seminar at Four Points by Sheraton, Novi

December 9, 2020 from 8:15am to 4pm
Check out our MDUG web site for upcoming Seminar details and past Seminar presentations, etc.:http://www.mdug.org/New to MDUG?Please see content underHome About MDUGFor Seminar Reservations, please review the MDUG site content under…See More
Dec 4, 2020
An event by Surekha was featured
Thumbnail

Surprise in store! Join the Db2 for z/OS Spotlight session for REALLY new news from the lab at http://ibm.biz/MoTownsend_IBM2020

December 7, 2020 from 11am to 12pm
Surprise in store! Join the Db2 for z/OS Spotlight session for REALLY new news from the labIDUG EMEA 2020 Spotlight session Available today on replay  http://ibm.biz/MoTownsend_IBM2020 Db2 12 for z/OS® took Db2 to a new level, by extending the core capabilities and empowering the future. IBM extended the core with new…See More
Dec 4, 2020
Jonathan Sloan posted an event

IBM Z and IBM Cloud Pak for Data - Better Together at Webcast - East Coast U.S. time zone

December 3, 2020 from 11am to 12pm
Register at http://ibm.biz/IBMZandBetterTogetherWoD.IBM Cloud Pak for Data is a fully integrated data and AI platform that modernizes how businesses collect, organize and analyze data and infuse AI throughout their organization. And now IBM has introduced IBM Cloud Pak for Data on IBM Z, a highly secure private cloud for your enterprise data that allows you to adapt, transform and compete more effectively. Join this webcast to learn how IBM…See More
Dec 4, 2020
Surekha posted an event
Thumbnail

Surprise in store! Join the Db2 for z/OS Spotlight session for REALLY new news from the lab at http://ibm.biz/MoTownsend_IBM2020

December 7, 2020 from 11am to 12pm
Surprise in store! Join the Db2 for z/OS Spotlight session for REALLY new news from the labIDUG EMEA 2020 Spotlight session Available today on replay  http://ibm.biz/MoTownsend_IBM2020 Db2 12 for z/OS® took Db2 to a new level, by extending the core capabilities and empowering the future. IBM extended the core with new…See More
Dec 4, 2020
Dragica Smintic liked Mateja Jankovič's event SQLAdria Virtual Event
Nov 25, 2020
Jonathan Sloan posted an event

IBM Z and IBM Cloud Pak for Data - Better Together at Webcast - East Coast U.S. time zone

December 3, 2020 from 11am to 12pm
Register at http://ibm.biz/IBMZandBetterTogetherWoD.IBM Cloud Pak for Data is a fully integrated data and AI platform that modernizes how businesses collect, organize and analyze data and infuse AI throughout their organization. And now IBM has introduced IBM Cloud Pak for Data on IBM Z, a highly secure private cloud for your enterprise data that allows you to adapt, transform and compete more effectively. Join this webcast to learn how IBM…See More
Nov 23, 2020
Massimo Verzì liked Adrian Collett's group DUGI - Db2 User Group Italia
Nov 20, 2020
Profile Icon via Twitter
Enjoy complimentary visit #IBM Expo at #IDUGDb2 EMEA 2020. Virtual Expo is available 24x7 - check out our compli… https://t.co/pAqtzMLvfm
TwitterNov 19, 2020 · Reply · Retweet

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

Forum

Replaying past database activities

Started by Shaun Hide in Security and DB2. Last reply by Alexander Dashevsky Nov 17, 2020. 1 Reply

I would need to know who modified table data on a certain day.I do have my backups and database logs available.DB2audit was however not activated at this point.How would i  be able to access this information?Continue

Tags: #DataAccess

Db2 is developer inclusive and preview of Db2 11.5.5

Started by Kip in What's hot ? Nov 4, 2020. 0 Replies

This digital event examines how enterprises can become smarter with a data management system that is developer-inclusive. During this session, participants will get to learn how Db2 integrates with a broad array of programming languages, employs…Continue

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service