Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    15

    Unanswered: Default value performance

    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

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You are worrying about something that is trivial. The difference is probably not measureable.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    So if an insert normally takes .005 seconds, it would take 0.00505 seconds instead (about 1 % difference).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •