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
 

 

Impact of inserting a DB2 column to an existing table

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

New User


Joined: 28 Jan 2010
Posts: 47
Location: India

PostPosted: Thu May 03, 2012 12:24 pm    Post subject: Impact of inserting a DB2 column to an existing table
Reply with quote

Hi,

For one of our projects, we are planning to introduce a new column in an existing DB2 table. I would like to know if we really need to recompile all the cobol programs which uses the DB2 table or not.

Note - There are no SELECT * or INSERT queries without the column list in any of the copybooks which we use it in the cobol programs.

What will be the impact if we do not recompile the cobol programs which uses the DB2 Table.
Back to top
View user's profile Send private message

gopalkulkarni

New User


Joined: 13 Aug 2007
Posts: 12
Location: Pune, India

PostPosted: Thu May 03, 2012 1:00 pm    Post subject:
Reply with quote

Yes, you need to recompile all cobol programs. If you do not recompile them, it will not select values while generating output.
When you compile Cobol-Db2 program, it creates DB2 package & it should contain all field details which you need to fetch while running pogram.
Back to top
View user's profile Send private message
maki_psg

New User


Joined: 28 Jan 2010
Posts: 47
Location: India

PostPosted: Thu May 03, 2012 1:31 pm    Post subject:
Reply with quote

gopalkulkarni wrote:
Yes, you need to recompile all cobol programs. If you do not recompile them, it will not select values while generating output.
When you compile Cobol-Db2 program, it creates DB2 package & it should contain all field details which you need to fetch while running pogram.


Do you mean that I will not be able to select the values of existing columns if I do not recompile it? I thought that, if I do not recompile the cobol DB2 programs, it will use the old load which would not have the new column. Also, since we had not used SELECT * or INSERT INTO TABLE_NAME without column names anywhere, we would not face any issues. Is it right?

I will use the new column only in 1 program, which we will use to load the data into DB2 table. Apart from this 1 program, we might not have to use the new column anywhere else.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Thu May 03, 2012 1:46 pm    Post subject: Reply to: Impact of inserting a DB2 column to an existing ta
Reply with quote

I get the impression that gopalkulkarni didn't understand your question. I wouldn't place much value on the response.

I'm sure the manuals hold your answers. In your situation, I'd guess "no, I don't need to compile", discover what the manuals say, then test out what I understand from what the manuals say by actually compiling/running two small programs so that I know I won't get bitten later.
Back to top
View user's profile Send private message
gopalkulkarni

New User


Joined: 13 Aug 2007
Posts: 12
Location: Pune, India

PostPosted: Thu May 03, 2012 2:30 pm    Post subject: Reply to: Impact of inserting a DB2 column to an existing ta
Reply with quote

If you are not going to fetch data for new column & not increasing file length, no need to recompile all programs.
Only need to compile program which is using this field.
If you have increased file length, this will have junk values.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Thu May 03, 2012 7:41 pm    Post subject: Re: Reply to: Impact of inserting a DB2 column to an existin
Reply with quote

gopalkulkarni wrote:
If you are not going to fetch data for new column & not increasing file length, no need to recompile all programs.
Only need to compile program which is using this field.
If you have increased file length, this will have junk values.

Please explain what is "file length" of a DB2 table ! icon_eek.gif
Back to top
View user's profile Send private message
saiprasadh

Active User


Joined: 20 Sep 2006
Posts: 154
Location: US

PostPosted: Thu May 03, 2012 8:10 pm    Post subject:
Reply with quote

You have to recompile the program which are interacting with this new column.


Do consider the programs which are having SELECT * and INSERT into without column names.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu May 03, 2012 8:47 pm    Post subject:
Reply with quote

actually,
you have to rebind the programs that must be recompiled due to changes.

the DDL in your program that describes the Table,
is a comment to DB2.

Though it could be used by the pre-compiler,
the pre-compiler does not access the db2 catalogued tables,
were everything that counts is stored.

so if a table changes:
  • there is no need to recompile a program IF THE PROGRAM HAS NOT CHANGED
  • though it may behove you to REBIND due to new indexes, for instance
.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Fri May 04, 2012 12:42 pm    Post subject:
Reply with quote

It depends also on how the new column is defined.
Does it have a default value ?
Can it be NULL ?
Will it be indexed ?

When you SELECT specific columns from the table, DB2 doesn't care about other columns, new or not.
But when you INSERT a new row, all the columns need a value. If the program don't provide one, DB2 will act as defined (and succeed or fail).

If it fails, you will need to modify the definition or the program (which implies a BIND).
If the new column is part of an index, REBIND is recommended.

Anyway, this question is usually answered beforehand by the same DBA that does the modification.
There is a DBA, isn't there ?
Back to top
View user's profile Send private message
maki_psg

New User


Joined: 28 Jan 2010
Posts: 47
Location: India

PostPosted: Fri May 04, 2012 3:10 pm    Post subject:
Reply with quote

Marso wrote:
It depends also on how the new column is defined.
Does it have a default value ?
Can it be NULL ?
Will it be indexed ?

When you SELECT specific columns from the table, DB2 doesn't care about other columns, new or not.
But when you INSERT a new row, all the columns need a value. If the program don't provide one, DB2 will act as defined (and succeed or fail).

If it fails, you will need to modify the definition or the program (which implies a BIND).
If the new column is part of an index, REBIND is recommended.

Anyway, this question is usually answered beforehand by the same DBA that does the modification.
There is a DBA, isn't there ?


Hi Marso,

The new column is a 2 byte numeric data type and can be NULL. It is not part of an Index.

From Dick's comments, I think if there are no changes in the programs, which uses the DB2 table, I would not have to recompile the programs.

Also, I do not have to rebind the programs as the new column will not be part of an index.

Thank you everyone for your help
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri May 04, 2012 4:10 pm    Post subject:
Reply with quote

maki_psg wrote:
From Dick's comments, I think if there are no changes in the programs, which uses the DB2 table, I would not have to recompile the programs


as has been pointed out, my comment was incomplete

as Marso indicated:
All INSERTs need to be looked at.
most probably, all to be modified.
thus, recompile and rebind.

you never know for sure what db2 decides.
REBINDs should always be done.
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 SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm


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