Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    67

    Unanswered: help to right the query

    Hi There,

    db2 9.7
    aix 7.1

    I need your help to write a query with specific result.

    table A has columns
    1- timestamp
    2- userid
    3- stmttext (sql statement: update/UPDATE,delete/DELETE,insert/INSERT, select/SELECT e.g. substr(stmttest,1,6)='insert' I need check upercase as well due to some query start with UPDATE )

    I want to write query to read table and give me below result

    userid number_of_update number_of_insert number_of_delete number_of_select
    ------ ---------------- --------------- --------------- -----------------



    Just count the number of update/insert/delete/update for specified date e.g. timestamp > '2016-05-17'


    thank you in advance for your help

  2. #2
    Join Date
    Jan 2010
    Posts
    67
    Quote Originally Posted by mars View Post
    Hi There,

    db2 9.7
    aix 7.1

    I need your help to write a query with specific result.

    table A has columns
    1- timestamp
    2- userid
    3- stmttext (sql statement: update/UPDATE,delete/DELETE,insert/INSERT, select/SELECT e.g. substr(stmttest,1,6)='insert' I need check upercase as well due to some query start with UPDATE )

    I want to write query to read table and give me below result

    userid number_of_update number_of_insert number_of_delete number_of_select
    ------ ---------------- --------------- --------------- -----------------



    Just count the number of update/insert/delete/update for specified date e.g. timestamp > '2016-05-17'


    thank you in advance for your help

    could you please somebody look at this request. than you in advance for your help

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select
      userid
    , count(case lower(substr(stmttext,1,6)) when 'update' then 1 end) number_of_update
    , count(case lower(substr(stmttext,1,6)) when 'insert' then 1 end) number_of_insert
    , count(case lower(substr(stmttext,1,6)) when 'delete' then 1 end) number_of_delete
    , count(case when lower(substr(stmttext,1,4)) in ('sele', 'with') then 1 end) number_of_select
    from table(values
      (current timestamp, 'USER1', 'update ...')
    , (current timestamp, 'USER1', 'UPDATE ...')
    , (current timestamp, 'USER1', 'INSERT ...')
    , (current timestamp, 'USER1', 'delete ...')
    , (current timestamp, 'USER1', 'merge ...')
    , (current timestamp, 'USER1', 'with  ...')
    , (current timestamp, 'USER1', 'select ...')
    , (current timestamp, 'USER2', 'Select ...')
    ) t(timestamp, userid, stmttext) 
    where timestamp>timestamp('2016-05-17')
    group by userid;
    Regards,
    Mark.

  4. #4
    Join Date
    Jan 2010
    Posts
    67
    Quote Originally Posted by mark.b View Post
    Hi,

    try this:
    Code:
    select
      userid
    , count(case lower(substr(stmttext,1,6)) when 'update' then 1 end) number_of_update
    , count(case lower(substr(stmttext,1,6)) when 'insert' then 1 end) number_of_insert
    , count(case lower(substr(stmttext,1,6)) when 'delete' then 1 end) number_of_delete
    , count(case when lower(substr(stmttext,1,4)) in ('sele', 'with') then 1 end) number_of_select
    from table(values
      (current timestamp, 'USER1', 'update ...')
    , (current timestamp, 'USER1', 'UPDATE ...')
    , (current timestamp, 'USER1', 'INSERT ...')
    , (current timestamp, 'USER1', 'delete ...')
    , (current timestamp, 'USER1', 'merge ...')
    , (current timestamp, 'USER1', 'with  ...')
    , (current timestamp, 'USER1', 'select ...')
    , (current timestamp, 'USER2', 'Select ...')
    ) t(timestamp, userid, stmttext) 
    where timestamp>timestamp('2016-05-17')
    group by userid;
    Hi Mark,

    really appreciate for your help.
    I ran the query and I got error that complained about '(' before '(values'

    I removed all

    (values
    (current timestamp, 'USER1', 'update ...')
    , (current timestamp, 'USER1', 'UPDATE ...')
    , (current timestamp, 'USER1', 'INSERT ...')
    , (current timestamp, 'USER1', 'delete ...')
    , (current timestamp, 'USER1', 'merge ...')
    , (current timestamp, 'USER1', 'with ...')
    , (current timestamp, 'USER1', 'select ...')
    , (current timestamp, 'USER2', 'Select ...')
    ) t(timestamp, userid, stmttext)
    where timestamp>timestamp('2016-05-17')

    and just added 'where timestamp > '2016-05-17'

    it worked fine .

    could you please tell me what is for this part ?

    Thank you again for your help.

    Mars

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    This part is for test data since I don't have your table to query from. If you removed the part you mentioned, you have to add some table reference instead. It won't work otherwise.
    My example should work as is on DB2 for LUW.
    If you have DB2 for Z/OS, you must rewrite the query since it doesn't support values clause in a select statement.
    Regards,
    Mark.

  6. #6
    Join Date
    Jan 2010
    Posts
    67
    the db2 is luw

    I used the below query as part of your original script and it worked fine

    select
    userid
    , count(case lower(substr(stmttext,1,6)) when 'update' then 1 end) number_of_update
    , count(case lower(substr(stmttext,1,6)) when 'insert' then 1 end) number_of_insert
    , count(case lower(substr(stmttext,1,6)) when 'delete' then 1 end) number_of_delete
    , count(case when lower(substr(stmttext,1,4)) in ('sele', 'with') then 1 end) number_of_select
    from audit.execute
    where timestamp>'2016-05-'
    group by userid;

Posting Permissions

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