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

Impact of inserting a DB2 column to an existing table


IBM Mainframe Forums -> COBOL Programming
Post new topic   Reply to topic
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
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
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
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu May 03, 2012 1:46 pm
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
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: 1353
Location: Israel

PostPosted: Thu May 03, 2012 7:41 pm
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
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
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: 1353
Location: Israel

PostPosted: Fri May 04, 2012 12:42 pm
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> COBOL Programming

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts How to load to DB2 with column level ... DB2 6
No new posts RC query -Time column CA Products 3
No new posts Multiple table unload using INZUTILB DB2 2
Search our Forums:

Back to Top