Scribd Logo
Upload

Welcome to Scribd!

  • 281 views

    DB2 Batch

    Uploaded by

    prakash_6849
    db2batch is a benchmark tool that measures the time it takes for SQL statements to execute. It breaks the execution time into three phases - prepare, execute, and fetch. This helps identify where bottlenecks may exist, such as whether a statement has high preparation time or high fetch time. The tool runs SQL statements from a file against a database and outputs the timing results in a graph to analyze where each statement spends its time. It can be used to troubleshoot slow SQL and identify which statements or phases to optimize for better performance.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    DB2 Batch

    Uploaded by

    prakash_6849
    281 views13 pages
    db2batch is a benchmark tool that measures the time it takes for SQL statements to execute. It breaks the execution time into three phases - prepare, execute, and fetch. This helps identify where bottlenecks may exist, such as whether a statement has high preparation time or high fetch time. The tool runs SQL statements from a file against a database and outputs the timing results in a graph to analyze where each statement spends its time. It can be used to troubleshoot slow SQL and identify which statements or phases to optimize for better performance.

    Original Description:

    DB2Batch

    Original Title

    DB2Batch

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    db2batch is a benchmark tool that measures the time it takes for SQL statements to execute. It breaks the execution time into three phases - prepare, execute, and fetch. This helps identify where bottlenecks may exist, such as whether a statement has high preparation time or high fetch time. The tool runs SQL statements from a file against a database and outputs the timing results in a graph to analyze where each statement spends its time. It can be used to troubleshoot slow SQL and identify which statements or phases to optimize for better performance.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    281 views13 pages

    DB2 Batch

    Uploaded by

    prakash_6849
    db2batch is a benchmark tool that measures the time it takes for SQL statements to execute. It breaks the execution time into three phases - prepare, execute, and fetch. This helps identify where bottlenecks may exist, such as whether a statement has high preparation time or high fetch time. The tool runs SQL statements from a file against a database and outputs the timing results in a graph to analyze where each statement spends its time. It can be used to troubleshoot slow SQL and identify which statements or phases to optimize for better performance.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 13

    db2batch - Benchmark Tool

    ric Lopes de Castro


    DBA DB2 at Perallis
    [email protected]

    Perallis IT Innovation (www.perallis.com)

    Where is the
    bottleneck in my SQL
    DML ?

    db2batch can
    measure the time
    to answer all these
    questions!
    How long
    does my SQL
    take ?

    Perallis IT Innovation (www.perallis.com)

    Why is it so
    slow today?

    Social Media Example


    FRIEND
    -------------------id1
    id2

    PERSON
    -------------------id
    name
    lastname
    country
    email

    LIKES
    -------------------id1
    id2

    Perallis IT Innovation (www.perallis.com)

    Statements have 3 phases


    PREPARE

    EXECUTE

    FETCH

    DB2 creates
    the access plan

    DB2 finds the


    rows to be
    returned

    DB2 returns the


    result

    Perallis IT Innovation (www.perallis.com)

    high prepare, execution and fetch

    high prepare and execution


    low fetch

    low prepare and execution


    high fetch
    low prepare, execution and fetch

    Perallis IT Innovation (www.perallis.com)

    db2batch main options


    -d | database
    -f | file with the sql
    statements
    -iso | isolation level: CS

    -o | options
    f row fetch
    r rows out
    p perfil detail
    o query
    optimization class

    -i | format of measured
    elapsed times: Complete

    Perallis IT Innovation (www.perallis.com)

    Statements example
    Find the names of all who are friends with someone named
    Gabriel.
    Find the names who lives in US

    Who has more friends worldwide ?

    Find all the information about person who has the ID 5239306

    Perallis IT Innovation (www.perallis.com)

    Statements example
    select name from person where id in (select id2 from person
    inner join friend on person.id=friend.id1 where name =
    'GABRIEL');

    select name from person where country = 'US'


    select max(n) from (select count(*) as n,id from (select p1.id
    from person p1,friend f1,person p2 where p1.id=f1.id1 and
    f1.id2=p2.id and exists (select * from person p3 where
    p3.country <> p2.country and p1.country=p2.country)) group
    by id)
    select * from person where id = 5239306

    Perallis IT Innovation (www.perallis.com)

    db2batch file example


    --#SET ROWS_OUT 0
    --#SET PERF_DETAIL 1

    select name from person where id in (select id2 from person inner join
    friend on person.id=friend.id1 where name = 'GABRIEL');

    select name from person where country = 'US';


    select max(n) from (select count(*) as n,id from (select p1.id from person
    p1,friend f1,person p2 where p1.id=f1.id1 and f1.id2=p2.id and exists
    (select * from person p3 where p3.country <> p2.country and
    p1.country=p2.country)) group by id;
    select * from person where id = 5239306;

    $ db2batch -d social -f social.sql -iso CS -i complete o o 9


    Perallis IT Innovation (www.perallis.com)

    db2batch output

    Perallis IT Innovation (www.perallis.com)

    Output Graphic
    5,0000000000
    4,5000000000
    4,0000000000
    3,5000000000
    3,0000000000
    PREPARE
    EXECUTE

    2,5000000000

    FETCH
    2,0000000000

    TOTAL

    1,5000000000
    1,0000000000
    0,5000000000
    0,0000000000
    SQL1

    SQL2

    SQL3

    Perallis IT Innovation (www.perallis.com)

    SQL4

    When can you use db2batch?


    Answer precision time of SQL
    Show to the users if the bottleneck is on
    database or not
    Attack the right SQL phase to tune your SQL

    Perallis IT Innovation (www.perallis.com)

    If you enjoyed and learned!!


    vote ric !!
    Thank you!
    ric Lopes de Castro
    [email protected]
    mobile: +55 15 8114-8605

    Perallis IT Innovation (www.perallis.com)

    You might also like