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

How to handle NULL in COBOL program


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
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
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: 2455
Location: Hampshire, UK

PostPosted: Mon Oct 07, 2019 4:19 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2672
Location: NYC,USA

PostPosted: Mon Oct 07, 2019 6:48 pm
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
Reply with quote

Hi Rohit and Nic,

Thanks for the details.

I found below IBM link and made changes in my COBOL Program.
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2672
Location: NYC,USA

PostPosted: Mon Oct 07, 2019 8:48 pm
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: 2455
Location: Hampshire, UK

PostPosted: Mon Oct 07, 2019 10:44 pm
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

Global Moderator


Joined: 21 Sep 2010
Posts: 2672
Location: NYC,USA

PostPosted: Mon Oct 07, 2019 11:55 pm
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: 2455
Location: Hampshire, UK

PostPosted: Tue Oct 08, 2019 1:07 am
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
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1080

PostPosted: Thu Oct 10, 2019 10:53 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> COBOL Programming

 


Similar Topics
Topic Forum Replies
No new posts COBOL Table data to a flat file with ... COBOL Programming 5
No new posts Program level statistics CICS 6
No new posts Parameter description for Beta92 prog... Compuware & Other Tools 1
No new posts Read UTF-8 file in Cobol COBOL Programming 5
No new posts Uploading from desktop, a CSV file, s... DB2 2
Search our Forums:

Back to Top