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
 
How to define a NULL indicator for a DB2 host variable

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Fri Jul 01, 2011 3:12 pm    Post subject: How to define a NULL indicator for a DB2 host variable
Reply with quote

I want to check a field value is NULL or not when I fetch the DB2 table
I read [ DB2 for OS/390 REXX Language Support.pdf ] like below

but I can understand how to define a NULL indicator for a DB2 host variable , just after the host variable or must start with IND ?


Code:
In the following example, the phone number for employee Haas is selected into
variable HVPhone. After the SELECT statement executes, if no phone number for
employee Haas is found, indicator variable INDPhone contains -1.
SQLSTMT = ,
"SELECT PHONENO WHERE LASTNAME=©HAAS©"
"EXECSQL PREPARE S1 FROM :SQLSTMT"
"EXECSQL DECLARE C1 CURSOR FOR S1"
"EXECSQL OPEN C1"
"EXECSQL FETCH C1 INTO :HVPhone :INDPhone"
If INDPhone < 0 Then ,
Say 'Phone number for Haas is null.'
Back to top
View user's profile Send private message

yuvrajdutta

New User


Joined: 13 Jul 2009
Posts: 40
Location: India

PostPosted: Sun Jul 10, 2011 12:33 pm    Post subject: Reply to: How to define a NULL indicator for a DB2 host vari
Reply with quote

This is what works for me:

Code:

/* REXX */                                     
ADDRESS TSO                                     
"SUBCOM DSNREXX"                               
IF RC = 1 THEN DO                               
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')     
END                                             
ADDRESS DSNREXX                                 
"CONNECT DB2R"                                 
QRY="SELECT EMP_ID,",                           
    "FIRST_NAME,",                             
    "MIDDLE_NAME,",                             
    "LAST_NAME",                               
    "FROM IBMGRP.EMP"                           
SAY QRY                                         
"EXECSQL DECLARE C1 CURSOR FOR S1"             
IF SQLCODE /= 0 THEN CALL SQLCA                 
'EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :QRY'   
IF SQLCODE /= 0 THEN CALL SQLCA                 
"EXECSQL OPEN C1"                               
IF SQLCODE /= 0 THEN CALL SQLCA                 
DO UNTIL(SQLCODE /= 0)                         
   "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
   IF SQLCODE /= 0 THEN                         
      CALL SQLCA                               
   ELSE                                         
   DO                                           
      DO I=1 TO OUTSQLDA.SQLD                   
         SAY "COLUMN NAME: "OUTSQLDA.I.SQLNAME   
         SAY "COLUMN TYPE: "OUTSQLDA.I.SQLTYPE   
         SAY "COLUMN LENG: "OUTSQLDA.I.SQLLEN   
         SAY "COLUMN INDC: "OUTSQLDA.I.SQLIND   
         IF OUTSQLDA.I.SQLIND >= 0 THEN         
            SAY "COLUMN DATA: "OUTSQLDA.I.SQLDATA
         ELSE                                   
            SAY "NULL DATA RETRIEVED"           
      END I                                     
      SAY                                       
      SAY "END OF RECORD"                       
      SAY                                       
   END                                           
END                                             
"EXECSQL CLOSE C1"                               
IF SQLCODE /= 0 THEN CALL SQLCA                 
"DISCONNECT"                                     
IF SQLCODE /= 0 THEN CALL SQLCA                 
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')   
SQLCA:                                           
   SAY "SQLCODE  = " SQLCODE                     
   SAY "SQLSTATE = " SQLSTATE                   
   SAY "SQLERRMC = " SQLERRMC                   
EXIT                                             
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1894
Location: UK

PostPosted: Sun Jul 10, 2011 3:16 pm    Post subject:
Reply with quote

Dave - your link was to a Linux/Unix/Windows document not mainframe.
Chaoj - just code your indicator variable immediately after the variable as per the example you posted. The indicator variable does not have to begin IND.
Back to top
View user's profile Send private message
chaoj

Active User


Joined: 03 Jun 2010
Posts: 103
Location: Dalian

PostPosted: Mon Jul 11, 2011 8:17 am    Post subject:
Reply with quote

Nic Clouston wrote:
Dave - your link was to a Linux/Unix/Windows document not mainframe.
Chaoj - just code your indicator variable immediately after the variable as per the example you posted. The indicator variable does not have to begin IND.



Thank you !!! It works !!
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 NULL Values display V S Amarendra Reddy DB2 3 Thu Sep 07, 2017 6:59 pm
No new posts Moving values to a variable of copybo... Vignesh Sid COBOL Programming 6 Wed Sep 06, 2017 1:04 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts Move from Comp3 variable to Edited Va... sreekusr COBOL Programming 8 Thu Aug 10, 2017 4:20 pm
No new posts Can I traverse a STEM variable? chong.zhou CLIST & REXX 5 Wed May 31, 2017 3:54 pm

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