Results 1 to 11 of 11

Thread: db2 interview

  1. #1
    Join Date
    Mar 2005
    Posts
    73

    Unanswered: db2 interview

    All-

    I have faced an interview yesterday .i was not able to answer few questions. Its db2 V9 interview ..

    1) Your app team complains a slow performance of application. What do you do as a DBA. Tell me the sequence of steps that you perform.

    2) I want to load millions of rows into the database ..how do i load them.give me the best ways to load them.

    3) I want to delete a huge table which has millions of rows ...How do you do it quickly...?

    4) In a partition environment how do you choose which ones are good candidates for the partition key ?
    He means before creating tables how do you decide which ones are the best.

  2. #2
    Join Date
    Mar 2005
    Posts
    73
    I was not able to answer them correctly...
    can the experts tell me the correct answers for the above questions.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Some of the answers depend on the platform on which DB2 is used.
    Are these questions about DB2 for z/OS? or iSeries? or Unix? or ...?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Apr 2008
    Posts
    39
    1) Few points we can check for performance
    1) check whether indexes are being used or not using query access plans
    2) collect statistics regularly/more frequently(RUNSTATS)
    3) perform REORGCHK and see if reorganization of tables/indexes is required.
    4) REBINDING SQL statements if required
    5) Use tools like design advisor/configuration assistant

    2) LOAD utility
    4) partitioning key, generally we decide based on data which we want to partition. For eg; If I want to store my quarterly data in seperate partitions and if my queries are based on ranges (like monthly/weekly reports), then I chose date as my partition key.

  5. #5
    Join Date
    Apr 2008
    Posts
    39
    1) Few points we can check for performance
    1) check whether indexes are being used or not using query access plans
    2) collect statistics regularly/more frequently(RUNSTATS)
    3) perform REORGCHK and see if reorganization of tables/indexes is required.
    4) REBINDING SQL statements if required
    5) Use tools like design advisor/configuration assistant

    2) LOAD utility
    4) partitioning key, generally we decide based on data which we want to partition. For eg; If I want to store my quarterly data in seperate partitions and if my queries are based on ranges (like monthly/weekly reports), then I chose date as my partition key.

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Would be nice to hear what you have to say first? What is your experience?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    3) use REORG with DISCARD (on z/OS)
    or
    use LOAD REPLACE with empty input data (on LUW)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Other options for 3 :

    -- IMPORT FROM empty input data
    -- ALTER TABLE NOT LOGGED INITIALLY WITH EMPTY TABLE


    Quote Originally Posted by Peter.Vanroose
    3) use REORG with DISCARD (on z/OS)
    or
    use LOAD REPLACE with empty input data (on LUW)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Mar 2005
    Posts
    73
    Thanks for the reply.

    This is for LUW environment.

    For the slow performance i have answered in the following way.

    1) I will check the OS level by using vmstat , top , sar command and try to see if any process is taking too much of CPU utilitization and see if disk I/O is happening.

    2) Then i will see if applications are getting locked , i mean if the applications are in Lock-Wait state because one application holding an exlusive lock on a table.

    i have seen No.2 for different tables at the same time.

    3) Will the check for runstats , if they are current .
    4) Do reorg chck and find out if Reorg is needed.
    5) Will take a snapshot of dynamic sql and get the long rnning sqls to tune them

    6) and also since overall system is effecting the peformance , i will involve network team and OS team in the analysis .

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    For slow performance I would personally check if the SQL is written correctly first. many times I have seen lack of joins.

    Before doing any reorg or stats I would run an explain on the SQL and see what it is doing? Is it using correct indexes, is it using indexes at all and why not.

    You might have locks, but if they are not effecting this particular SQL I would not worry about them this minute. Locks are a natural thing that have to happen sometimes.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Mar 2005
    Posts
    73
    Quote Originally Posted by Cougar8000
    For slow performance I would personally check if the SQL is written correctly first. many times I have seen lack of joins.

    Before doing any reorg or stats I would run an explain on the SQL and see what it is doing? Is it using correct indexes, is it using indexes at all and why not.

    You might have locks, but if they are not effecting this particular SQL I would not worry about them this minute. Locks are a natural thing that have to happen sometimes.

    Cougar ..you are right...the questions is the whole application is responding slow ..not a particular piece of it . ..In that case how do we decided a particular sql is performing slower.

Posting Permissions

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