IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Question regarding LOB data retrieval from SUBSTR


IBM Mainframe Forums -> HomeWorks & Requests
Post new topic   Reply to topic
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
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: 792
Location: Pennsylvania

PostPosted: Tue Nov 20, 2012 12:28 am
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
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Nov 20, 2012 12:26 pm
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> HomeWorks & Requests

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
Search our Forums:

Back to Top