Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2002
    Posts
    207

    Unanswered: DB2 query from 512 exam sample

    Group:

    Would appreciate your input on following query with reason.

    Given the successfully executed embedded SQL:
    INSERT INTO staff VALUES (1, 'Colbert','Dorchester', 1)
    COMMIT
    INSERT INTO staff VALUES (6, 'Anders', 'Cary', 6)
    INSERT INTO staff VALUES (3, 'Gaylord', 'Geneva', 8)
    ROLLBACK WORK

    Which of the following indicates the number of new rows that would be in the STAFF table?
    a. 0
    b. 1
    c. 2
    d. 3

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Posts
    188

    Re: DB2 query from 512 exam sample

    Originally posted by mkalsi
    Group:

    Would appreciate your input on following query with reason.

    Given the successfully executed embedded SQL:
    INSERT INTO staff VALUES (1, 'Colbert','Dorchester', 1)
    COMMIT
    INSERT INTO staff VALUES (6, 'Anders', 'Cary', 6)
    INSERT INTO staff VALUES (3, 'Gaylord', 'Geneva', 8)
    ROLLBACK WORK

    Which of the following indicates the number of new rows that would be in the STAFF table?
    a. 0
    b. 1
    c. 2
    d. 3

    Thanks.
    I would think is 1

  3. #3
    Join Date
    Nov 2002
    Posts
    207

    Red face

    why 1 - which ofcourse is a correct answer?
    Because if I run a small test it shows me all 3 in select statement. I am getting confused here as I am from Sybase world and trying to get into DB2. Anything I am missing here - like implicit commits, etc.

  4. #4
    Join Date
    Apr 2002
    Posts
    188
    Originally posted by mkalsi
    why 1 - which ofcourse is a correct answer?
    Because if I run a small test it shows me all 3 in select statement. I am getting confused here as I am from Sybase world and trying to get into DB2. Anything I am missing here - like implicit commits, etc.
    mkalsi,

    let me see if I can explain this correct ( and please feel free on correcting me ).

    I see two transactions going on here, the first one "commits" already one row to the table , this first transaction phase is done.

    Two more inserts are on a transactional mode not committed ( a new transaction has begun) then a ROLLBACK WORK is issued, then it
    will rollback all previous inserts and it will start a new transaction scope
    within the process.

    my 2 cents


    marcos oliva

  5. #5
    Join Date
    Nov 2002
    Posts
    207
    Agreed. But why does it show 3 rows in the table even if we rollback the transaction OR even if I log out and log in and do select on the table.

    Correct answer is 1.

    Appreciate your inputs.

  6. #6
    Join Date
    Apr 2002
    Posts
    188
    Originally posted by mkalsi
    Agreed. But why does it show 3 rows in the table even if we rollback the transaction OR even if I log out and log in and do select on the table.

    Correct answer is 1.

    Appreciate your inputs.
    I think you may have the autocommit = on , because I do not see
    any other way that the others two insert will into the table after the
    ROLLBACK WORK command.

    Unless someone else has a different idea on what is going on here

    marcos oliva

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I believe your program would have used autocommit , ie , a commit statement is issued implicityly after every SQL Statement ...

    To get it right in a db2 command window ,

    db2 +c INSERT INTO staff VALUES (1, 'Colbert','Dorchester', 1)
    db2 commit
    db2 select count(*) from staff
    db2 +c INSERT INTO staff VALUES (6, 'Anders', 'Cary', 6)
    db2 +c INSERT INTO staff VALUES (3, 'Gaylord', 'Geneva', 8)
    db2 ROLLBACK WORK

    The +c option in the command is to stop the CLP from issuing a commit explictly ...

    HTH

    Cheers

    sathyaram


    Originally posted by mkalsi
    why 1 - which ofcourse is a correct answer?
    Because if I run a small test it shows me all 3 in select statement. I am getting confused here as I am from Sybase world and trying to get into DB2. Anything I am missing here - like implicit commits, etc.

  8. #8
    Join Date
    Apr 2002
    Posts
    188
    Originally posted by sathyaram_s
    I believe your program would have used autocommit , ie , a commit statement is issued implicityly after every SQL Statement ...

    To get it right in a db2 command window ,

    db2 +c INSERT INTO staff VALUES (1, 'Colbert','Dorchester', 1)
    db2 commit
    db2 select count(*) from staff
    db2 +c INSERT INTO staff VALUES (6, 'Anders', 'Cary', 6)
    db2 +c INSERT INTO staff VALUES (3, 'Gaylord', 'Geneva', 8)
    db2 ROLLBACK WORK

    The +c option in the command is to stop the CLP from issuing a commit explictly ...

    HTH

    Cheers

    sathyaram
    Thanks sathyaram,, I did not know about the +c thing

    marcos

  9. #9
    Join Date
    Nov 2002
    Posts
    207
    Perfect. That explains it. Now the question is - Is this autocommit a default behavior of DB2, because this is a sample question from 512 exam and if it comes in actual exam, how should one answer it?

    I think one should answer it by considering default options.
    Suggestions pls.

    Thanks for all your inputs.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I believe that for embedded SQL autocommit off is the default ...

    Cheers

    Sathyaram

    Originally posted by mkalsi
    Perfect. That explains it. Now the question is - Is this autocommit a default behavior of DB2, because this is a sample question from 512 exam and if it comes in actual exam, how should one answer it?

    I think one should answer it by considering default options.
    Suggestions pls.

    Thanks for all your inputs.

  11. #11
    Join Date
    Jan 2002
    Location
    Manila, Philippines
    Posts
    71

    I think you're testing this in CLP

    where autocommit is ON by default, that is why you're seing 3 records, as suggested by Sathyaram, you need to use the +C option in CLP.

    Going back to the question, it says EMBEDDED SQL where the autocommit is OFF by default. Hence you need not worry on options, since it is given that these are EMBEDDED SQL (not executed in CLP but inside a program).

    HTH,
    Oliver

Posting Permissions

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