View previous topic :: View next topic
|
Author |
Message |
bhaskar_kanteti
Active User
Joined: 01 Feb 2007 Posts: 123 Location: Hyderabad
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
bhaskar_kanteti
Active User
Joined: 01 Feb 2007 Posts: 123 Location: Hyderabad
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Rohit, he is creating file to be loaded by a DB2 utility thus the need for null indicator. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
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 |
|
|
|