Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to handle NULL in COBOL program

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 123
Location: Hyderabad

PostPosted: Mon Oct 07, 2019 3:54 pm    Post subject: How to handle NULL in COBOL program
Reply with quote

Hi,

I am creating output file using COBOL program (Non-DB2) that will be used as input to load to DB2 Table using IBM Utility in next step.

I wanted to know how to handle NULL in my COBOL program.

Lets say, I have field in table INTEREST defined as given below
Code:

INTEREST                  POSITION(26)
SMALLINT
     NULLIF(28)='?'


In my COBOL program I defined WS-INTEREST in my output copybook as S9(4) COMP.
How to define NULL in my output copybook? So that either the VALUE or NULL will be inserted while loading to table using DB2 Utility.
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2230
Location: Hampshire, UK

PostPosted: Mon Oct 07, 2019 4:19 pm    Post subject: Reply to: How to handle NULL in COBOL program
Reply with quote

Read the 'Application Programming and SQL Guide' especially the chapter on 'Coding SQL statement in COBOL application programs' and the section 'Declaring host variables and indicator variables in COBOL'
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2262
Location: NY,USA

PostPosted: Mon Oct 07, 2019 6:48 pm    Post subject:
Reply with quote

Nic, OP has Non-Db2 program so what you suggest will not be an option here since the program just creating a load ds.

Bhaskar, NULL is a database term/concept. Usually low-values or x’00’ or x’FF’ are check for bad values which you can say NULL values. But again there is no standard how you handle with what values.
One way to achieve what you want is using NULLIF while you do INSERT and not in COBOL program.
Back to top
View user's profile Send private message
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 123
Location: Hyderabad

PostPosted: Mon Oct 07, 2019 8:28 pm    Post subject: Reply to: How to handle NULL in COBOL program
Reply with quote

Hi Rohit and Nic,

Thanks for the details.

I found below IBM link and made changes in my COBOL Program.
https://www.ibm.com/support/knowledgecenter/en/SSXJAV_13.1.0/com.ibm.filemanager.doc_13.1/db2/motnib.html

I defined INTEREST field in my output copybook like given below:
Code:

05  WS-INTEREST.
      10 WS-INTEREST-NULL X(01).
      10 WS-INTEREST-VAL   S9(04) COMP.


I defined NULL indicator in my output as X(01) (as in DB2 table the NULL indicator NULLIF is of 1 byte).

Then moved X'00' for valid input value and X'FF' for null in COBOL program.

I will test the DB2 Utility with above output and post the outcome.

Please correct me if i am wrong.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2262
Location: NY,USA

PostPosted: Mon Oct 07, 2019 8:48 pm    Post subject:
Reply with quote

I don't see why that's a problem and at the same time also you can think if using NULLIF during INSERT.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2230
Location: Hampshire, UK

PostPosted: Mon Oct 07, 2019 10:44 pm    Post subject: Reply to: How to handle NULL in COBOL program
Reply with quote

Rohit, he is creating file to be loaded by a DB2 utility thus the need for null indicator.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2262
Location: NY,USA

PostPosted: Mon Oct 07, 2019 11:55 pm    Post subject:
Reply with quote

That's correct Nic. But he don't need 'Coding SQL statement in COBOL application programs' since its purely COBOL program. That was my point.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2230
Location: Hampshire, UK

PostPosted: Tue Oct 08, 2019 1:07 am    Post subject: Reply to: How to handle NULL in COBOL program
Reply with quote

The point is that that part of the manual explains what is required.

Edit: OK. I probably should have started my search at the Index instead of the Contents. From the contents I went straight to the COBOL chapter (chapter 6) whereas, if I had started in the Index I would have gone to chapter 3 (Coding SQL statements in application programs: General information) where Indicator Variables are explained.

All references are to the version of the manual that I have: SC19-4051-01
Back to top
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 552
Location: Maryland

PostPosted: Thu Oct 10, 2019 10:53 pm    Post subject: Re: How to handle NULL in COBOL program
Reply with quote

bhaskar_kanteti wrote:
I am creating output file using COBOL program (Non-DB2) that will be used as input to load to DB2 Table using IBM Utility in next step.

I wanted to know how to handle NULL in my COBOL program.

Lets say, I have field in table INTEREST defined as given below
Code:

INTEREST                  POSITION(26)
SMALLINT
     NULLIF(28)='?'


In my COBOL program I defined WS-INTEREST in my output copybook as S9(4) COMP.
How to define NULL in my output copybook? So that either the VALUE or NULL will be inserted while loading to table using DB2 Utility.

There are two different entities fully mixed in this post, and in one's mind as well.

The given definition
Code:
. . . . . . . . . . . . . . . . .
INTEREST POSITION(26) SMALLINT
     NULLIF(28)='?'
. . . . . . . . . . . . . .

is in fact NOT DB2 TABLE FIELD DEFINITION, but definition of a flat file field used by DB2 LOAD/UNLOAD utilities to move data to/from DB2 tables.

I expect the original table definition (in DDL notation) looks like this:
Code:
 CREATE TABLE SAMPLE_TABLE (
   . . . . . . . . . . . . ,
   INTEREST              SMALLINT,     <-- NULLs are allowed!
   . . . . . . . . . . . . . .
) ;

The corresponding COBOL record layout supposed to handle this actual DB2 table in any COBOL program may look like this:
Code:
 . . . . . . . . .
      01 DB2-SAMPLE-TABLE.
         05 COBOL-FIELDS.
            . . . . . . . . . . .
            10 INTEREST              PIC 9(4) COMP.
            . . . . . . . . . . .

This record in COBOL code is populated from DB2 database table after some SQL has been executed; usually - FETCH … INTO …
Also the field values from this record inside COBOL code may be used to insert/update table rows in DB2 database, usually via INSERT/UPDATE SQL statements.

When this table from DB2 is being unloaded into a sequential dataset (newbies call them "flat files"), the typical Unload utility creates three entities:
1) a dataset with DSORG=PS,RECFM=FB,LRECL=calculated-by-utility)
2) a set of the utility control statements to be used for loading the unloaded table into a different (or into the same) DB2 database. It looks like
Code:
 . . . . . . . . . . . . . . .
LOAD DATA ... INTO "owner"."SAMPLE_TABLE"
(
   . . . . . . . . . . . . . . . .
   INTEREST        POSITION(26)   SMALLINT NULLIF(28) = '?' ,
   . . . . . . . . . . . . . . . .
) ;

3) (not always created automatically, but must be assumed) - the record layout of the created data in the unloaded dataset:
Code:
 . . . . . . . . . .
      01 DB2-UNLOADED-SAMPLE-TABLE.
         05 COBOL-DATA.
            . . . . . . . . . . . . . . . . . .
            10 INTEREST              PIC 9(4) COMP.
            10 INTEREST-IF           PIC X.
               88 INTEREST-NULL      VALUE '?'.
            . . . . . . . . . . . . . . . . . . . . . .
         

If you have to create a dataset to be used later by DB2 load utility, you need to handle the records of this file in accordance with the last example of record layout, but not as the layout of real DB2 table!

Please keep in mind that different DB2 Load/Unload utilities, and even the same utility with different run modes! might use different conventions on the format of unloaded records; one needs to verify this carefully before coding something in COBOL.
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 -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Running a REXX exec program using ZOSMF Sakthi344 CLIST & REXX 1 Tue Oct 15, 2019 3:13 pm
This topic is locked: you cannot edit posts or make replies. COBOL/CICS with real time MQ Series u... BPranav CICS 2 Mon Sep 16, 2019 9:19 pm
No new posts REXX - CLIST program to be invoked af... pkmurali CLIST & REXX 5 Wed Sep 04, 2019 4:31 pm
This topic is locked: you cannot edit posts or make replies. Compare two files logic in COBOL Nishant Singhal COBOL Programming 4 Sun Aug 25, 2019 2:25 am
This topic is locked: you cannot edit posts or make replies. Upgrade to Enterprise COBOL 6 JPVRoff COBOL Programming 4 Thu Aug 01, 2019 7:21 am

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