Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to Insert NULL value into Db2 table Thru Cobol-Db2 PGm

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

New User


Joined: 18 Apr 2006
Posts: 11
Location: pune

PostPosted: Fri Nov 02, 2007 6:25 pm    Post subject: How to Insert NULL value into Db2 table Thru Cobol-Db2 PGm
Reply with quote

Hi,
Please help me to solve this.
I want to INSERT null value into DB2 Table for specified column Thru Cobol-Db2 program, and not for all row of that table, only some specific rows need to insert with null...
Back to top
View user's profile Send private message

rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Fri Nov 02, 2007 6:32 pm    Post subject:
Reply with quote

Code:
INSERT INTO table-name
                     (column-name1,
                      column-name-nulls, ....)
          VALUES (:host-var,
                        NULL, ....)
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Nov 02, 2007 9:27 pm    Post subject:
Reply with quote

do any of you guys test your suggestions or do you just type this garbage in to up your post count?

abhiyanta,


use the null indicator syntax within your values clause of the INSERT statement.

:host-variable :null-indicator-variable

no comma, only a space between the capatible host variable for the column and the host variable used as the null-indicator-variable. The null indicator variable should be defined as binary, s9(4), with a value of -1.
Back to top
View user's profile Send private message
rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Fri Nov 02, 2007 10:22 pm    Post subject:
Reply with quote

Wow ... you just impressed the crap out of me!!! BTW ... before you jump on somebody ... you need to read up on YOUR facts ... I might be a jeep on this site, but not in this business. It was not intended to be the EXACT code, just an EXAMPLE ... something that MIGHT help. Just because it does not agree with your style of coding doesn't make it wrong or give you the right to disrespect someone you don't know.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri Nov 02, 2007 10:40 pm    Post subject:
Reply with quote

rpuhlman wrote:
Wow ... you just impressed the crap out of me!!! BTW ... before you jump on somebody ... you need to read up on YOUR facts ... I might be a jeep on this site, but not in this business. It was not intended to be the EXACT code, just an EXAMPLE ... something that MIGHT help. Just because it does not agree with your style of coding doesn't make it wrong or give you the right to disrespect someone you don't know.

I have to agree with dbzTHEdinosauer, your example isn't a good example of anything.
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 964
Location: Richmond, Virginia

PostPosted: Fri Nov 02, 2007 11:19 pm    Post subject:
Reply with quote

Wasn't the example just trying to say that the token NULL will represent a null value?

It's an insert, so just listing values is OK. Why need a var and an ind-var?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Sat Nov 03, 2007 4:12 pm    Post subject:
Reply with quote

I apologize to rpuhlman. His post is ok and SQL is accurate.
my mouth overloaded my ass. icon_redface.gif


In my attempt to provide flexible code I overlooked the fact that using NULL in the VALUE clause is legal, ok and will work.

With imbedded SQL you need to use the host-var ind-var syntax if you ever want to enter a value for that column.

Setting the ind-var to -1 will set the Column to NULL (the value of the host-var has no effect and is ignored)
and
setting the ind-var to zero and populating the host-var and allows you to populate the column with the value contained in the host-var.
Back to top
View user's profile Send private message
dr_te_z

New User


Joined: 08 Jun 2007
Posts: 71
Location: Zoetermeer, the Netherlands

PostPosted: Mon Nov 05, 2007 2:12 pm    Post subject:
Reply with quote

Don't mention! Really, don't mention.
Just insert your row only specifying only those columns which are not nulls allowed (typical primary key) and all the remaining columns get a null-value, exactly as topicstarter requested. All the other answers might be correct but just too much
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Mon Nov 05, 2007 3:49 pm    Post subject:
Reply with quote

Just to add some thoughts on above,

we can INSERT null values in diffrent ways,

1) As suggested by rpuhlman

Code:
         INSERT INTO table-name
                     (column-name1,
                      column-name-nulls, ....)
          VALUES (:host-var,
                        NULL, ....)


2) Use only the columns to which we have to populate information

Ex;

Consider a TABLE A having 3 Columns COL1, COL2, COL3

To INSERT NULL Values for COL2(Don't include COL2 in a query)

Use following Query

Code:
          INSERT INTO table-name
                     (COL1,COL3)
          VALUES (:host-var1,:host-var3)


3) Use Comma(,) in VALUE Clause like

Code:
          INSERT INTO table-name
                     (COL1,COL2,COL3)
          VALUES (:host-var1, ,:host-var3)


Any suggestion is welcomed

Regards,
Raghavendra
Back to top
View user's profile Send private message
rpuhlman

New User


Joined: 11 Jun 2007
Posts: 80
Location: Columbus, Ohio

PostPosted: Mon Nov 05, 2007 4:49 pm    Post subject:
Reply with quote

Dick,

Forget about it. Your are obviously very knowledgeable. I have read many of your posts and know you have a great deal to offer. I would have preferred that my response to you be in private, but have no way of contacting you. Anyway, I will continue to point many of my peers in your direction ... Thank you for all your time and effort on this site.

Thanks again,
Rick
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 Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts XML Parse for COBOL 5.2 Bhanu Praveen COBOL Programming 1 Sat Jul 08, 2017 8:58 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm
No new posts Search utility(3.14) using JCl and Cobol RKS3 COBOL Programming 12 Sat Jun 10, 2017 10:24 pm
No new posts COBOL DB2 program - zIIP eligible vasanthz COBOL Programming 7 Wed May 31, 2017 5:12 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us