Hi,
In DB2 on Z/OS, suppose i have a following Table...
Create Table TB1(col1 char(10) not null, col2 char(10) not null with default 'SAM')......

while inserting the records, if i do not have any value for column col2, which of the following performs better...

1. Insert the record by just providing col1 value. As i am not providing any value col2, DB2 determines the default value for col2 and inserts as 'SAM' for col2.

2.Since i am aware of default value for col2, i can write as
insert into tb1 values('WELLS','SAM')...In this case db2 do not require to determine the default value for col2.

Which of the above two options provides good performance of insert in heavy batch insert programs. I think it is the second option.

Can somebody validate the same....

Thanks
Ramesh

Most likely the first approach (insert the record by just providing col1 value) performs better since (1) it requires less data traffic from application to DB2, and (2) it requires less data access (reading the passed value) by DB2: DB2 just needs to read the default value once in order to insert it lots of times.

I would drive something like that not through the applications simply for semantical reasons. How would your application know that the default is 'SAM'? The only way is to read the default from the system catalog tables. And in order to make sure things won't become inconsistent if someone does an ALTER TABLE, you will have to read the default value in each transaction again. That's a lot of overhead for no real value as Peter explained.

You are worrying about something that is trivial. The difference is probably not measureable.

I did run a test, actually. I don't have a Z/OS system to play with, but on a Windows box with a local connection 1 million inserts that don't provide the default value consistently execute about 1% faster. It is measurable, but performance would not be the main reason if I were to make a decision.

So if an insert normally takes .005 seconds, it would take 0.00505 seconds instead (about 1 % difference).