Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Question regarding LOB data retrieval from SUBSTR

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> HomeWorks & Requests
View previous topic :: :: View next topic  
Author Message
Bang_1

New User


Joined: 08 May 2009
Posts: 39
Location: Bangalore

PostPosted: Tue Nov 20, 2012 12:09 am    Post subject: Question regarding LOB data retrieval from SUBSTR
Reply with quote

Hi,
I have a query regarding DB2 CLOB column. Trying to understand processing of CLOB data. DB2 version is 9.
Below is the code for CURSOR declaration, open and fetch

Code:
EXEC SQL                                   
  DECLARE GTPHOTO CURSOR FOR               
      SELECT  RESUME                       
      FROM    DSN8910.EMP_PHOTO_RESUME     
      WHERE   EMPNO = :HST-EMPNO           
END-EXEC.                                 

PROCEDURE DIVISION. 
MOVE '000130' TO HST-EMPNO.
EXEC SQL                   
   OPEN GTPHOTO           
END-EXEC.                 
..... evaluating SQL code .....
EXEC SQL                   
   FETCH FROM GTPHOTO INTO
         :HST-RESUME       
END-EXEC.                 
..... evaluating SQL code .....
MOVE 'Resume:' TO WS-CLOB-POSSTR1.
EXEC SQL                                                     
   SET :WS-CLOB-DATA-LENGTH = POSSTR(:HST-RESUME,           
                                     :WS-CLOB-POSSTR1)       
END-EXEC.


I got the starting position as 5 , which is correct ...
But when I used below SUBSTR to get the sub-string from 1 position with 5-bytes of length, I am getting SQLCODE of -84 in the precompilation
with error message - UNDEFINED OR UNUSABLE HOST VARIABLE "WS-CLOB-DATA"

Code:

MOVE 1 to WS-CLOB-START
MOVE WS-CLOB-DATA-LENGTH  to WS-CLOB-END
EXEC SQL                         
   SET :WS-CLOB-DATA = SUBSTR (:HST-RESUME,       
                               :WS-CLOB-START,   
                               :WS-CLOB-END)     
END-EXEC.


Did I miss anything in while getting data from SUBSTR.
Back to top
View user's profile Send private message

daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 688
Location: Pennsylvania

PostPosted: Tue Nov 20, 2012 12:28 am    Post subject:
Reply with quote

Where (which SECTION) in your program is WS-CLOB-DATA defined?
Back to top
View user's profile Send private message
Bang_1

New User


Joined: 08 May 2009
Posts: 39
Location: Bangalore

PostPosted: Tue Nov 20, 2012 10:58 am    Post subject:
Reply with quote

They are created in a copybook (manually, not by DCLGEN) , that is included in working storage section.

This is how compiler listing of that copybook...
Code:
*****EXEC SQL                             
*****  INCLUDE HOSTCLOB                   
*****END-EXEC.                             
 01  WS-CLOB-DATA.                         
  02  WS-CLOB-DATA-LENGTH  PIC S9(9) COMP.
  02  WS-CLOB-DATA-DATA.                   
       49 FILLER         PIC X(32767).     
........... after  159 repeats of the above line ....
       49 FILLER         PIC X(160).         
 01  WS-CLOB-START       PIC S9(4) COMP.     
 01  WS-CLOB-END         PIC S9(4) COMP.     
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7314

PostPosted: Tue Nov 20, 2012 12:26 pm    Post subject: Reply to: Question regarding LOB data retrieval from SUBSTR
Reply with quote

Code:
01  WS-CLOB-START       PIC S9(4) COMP.     
 01  WS-CLOB-END         PIC S9(4) COMP.     


These are a bit small...
Back to top
View user's profile Send private message
Bang_1

New User


Joined: 08 May 2009
Posts: 39
Location: Bangalore

PostPosted: Tue Nov 20, 2012 1:29 pm    Post subject:
Reply with quote

Changed those two copy book fields to use S9(9) comp. same -84 is showing up.
Code:
*****EXEC SQL                                           
*****   SET :WS-CLOB-DATA = SUBSTR (:HST-RESUME,         
*****                               :WS-CLOB-START,     
*****                               :WS-CLOB-END)       
*****END-EXEC.                                           
     MOVE -84 TO SQLCODE.                               
     MOVE "42612" TO SQLSTATE.                           
Back to top
View user's profile Send private message
Bang_1

New User


Joined: 08 May 2009
Posts: 39
Location: Bangalore

PostPosted: Tue Nov 20, 2012 1:50 pm    Post subject:
Reply with quote

I just removed COPYBOOK include and added the following declaration at the begining of working-storage

Code:
WORKING-STORAGE SECTION.                                       
01  WS-CLOB-DATA   USAGE SQL TYPE IS CLOB(32767).             
01  WS-CLOB-START       PIC S9(9) COMP.                       
01  WS-CLOB-END         PIC S9(9) COMP.                       


it worked, i got the sysout for CLOB-DATA with first 50 chars
Code:
CLOB-DATA       :         Resume:  Bruce Adamson    Personal Information       

So, looks like CLOB field has to be expanded by precompiler rather than adding them manually.

I do have one more question while testing yesterday - if SELECT query's WHERE clause has a literal in it (in CURSOR below cursor) , SQLCODE -84 was coming. If I replace that with a host-var, then it is not showing any error.
Code:
DECLARE GTPHOTO CURSOR FOR           
    SELECT  RESUME                   
    FROM    DSN8910.EMP_PHOTO_RESUME 
    WHERE   EMPNO = :HST-EMPNO       

Does it has to do anythng with BIND options.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> HomeWorks & Requests All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Extract Record using range of Data scorp_rahul23 DFSORT/ICETOOL 3 Wed Nov 15, 2017 11:54 pm
No new posts Updating data from one file to other!!! Vignesh Sid SYNCSORT 1 Mon Nov 06, 2017 2:42 pm
This topic is locked: you cannot edit posts or make replies. Can a alphanumeric data be moved to a... nico_neoz COBOL Programming 10 Mon Oct 30, 2017 8:28 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us