COBOL - DB2 Tables

Chetan Kumar · New User Joined: 03 Dec 2012 Posts: 46 Location: India

Hi All,

Four Cobol-Db2 programs are accessing a Table EMP-TABLE. Later there was a requirement to add 2 new columns to the table to use it in the 5th new program.

Scenario-01: When new 2 columns added at the end of the table.

Do we need to recompile all the programs or only the 5th one? and why?

Scenario-02: When new 2 columns added in between the table?
Then do we need to recompile all the programs or only the 5th one? and why?

Terry Heinze · Posted: Fri Jul 10, 2015 9:22 pm

It's been a while since I've worked with DB2, but if I remember correctly, new columns only affect programs that access those columns. If the 1st 4 programs do not access the new columns, compile only program 5. If any of the 4 do a SELECT * (which is not recommended), those programs would have to be changed and recompiled. As to your 2nd question, it doesn't matter where the new columns appear in the table -- beginning, middle, or end.

dbzTHEdinosauer · Posted: Wed Jul 15, 2015 5:37 pm

Well, you add columns to a row and not a table.
normally, and I apologize for exposing my ignorance here,
but how would one add new columns to the middle of a row?

Chetan Kumar · New User Joined: 03 Dec 2012 Posts: 46 Location: India

I don't understand your point dbzTHEDinosauer, it's not row it's the column we add to the table. Suppose there is a requirement to add two new columns in the 3rd and 4th position of the table which has 10 columns already. Then we need to add columns based on the order of columns as per the requirement.

Nic Clouston · Posted: Thu Jul 16, 2015 3:13 am

Columns belong to rows - you cannot have a column without a row. You cannot add columns 'between' other columns. You have to define a new table and probably write a program to migrate the data across.

GuyC · Posted: Thu Jul 16, 2015 7:56 pm

I disagree on this with the two moderators about semantics : columns belong to tables.
you can have columns (defined) without rows. you can not have rows without columns.
You do not ALTER ROW ADD COLUMN.

But On Topic :
(automatic) rebinds should suffice unless there is a "select *" or (an "insert" and the new columns are defined not null without default).

Adding columns in between requires dropping and recreating the table, but above rule about rebinds also applies.

Nic Clouston · Posted: Fri Jul 17, 2015 1:40 am

GuyC - I stand corrected but I was speaking from the data point of view - one cannot have actual data in a column without having a row - but I did not point it out as such.

Mickeydusaor · Posted: Fri Jul 17, 2015 2:21 am

if you drop a table its a good time to do a runstats

Rohit Umarjikar · Posted: Fri Jul 17, 2015 5:15 pm

1. You don't. during the insert query if you have not listed the columns names then that will fail and then you got to change them.
2. Your DBA at first place won't allow to add columns in between as it does not make difference to a select or update or insert as to where is a new column being added and not recommended . And answer remains same as 1.