Funny SQL I Have Seen Lately

Posted by

I’ve run across a few strange SQL statements lately, and while the mistakes they make are not the most impactful, I thought I’d share how I think they could be better written.

Unnecessary use of a Common Table Expression

This query isn’t really wrong, it just doesn’t make much sense. Here it is as I discovered it:

with sortsnap as
    float(commit_sql_stmts) as com_stmt,
    float(rollback_sql_stmts) as roll_stmt,
    float(total_sorts) as t_sorts,
    float(sort_overflows) as s_over,
    float(sort_heap_allocated) as s_heap_alloc,
    float(total_sort_time) as t_sort_time,
    float(active_sorts) as a_sorts

bigint(s_heap_alloc)  ,  
bigint(t_sorts)  ,  
bigint(t_sort_time)  ,  
bigint(s_over) ,  
bigint(a_sorts) ,
bigint(com_stmt)  ,  
bigint(roll_stmt)  ,  
from sortsnap;

In my opinion, the Common Table Expression used here is completely unneeded – we can easily perform all the work in a single query. It is also querying the table function SNAPSHOT_DATABASE, which is no longer available starting in DB2 10.5. I’m not going to change that part, as I don’t need to for the use I have for this statement. I also find the aliases used for the columns unneeded and confusing. I also don’t like the form used for verifying division by zero is not attempted.

Here’s how I’ve re-written that SQL:

select     bigint(sort_heap_allocated)
    , bigint(total_sorts)  
    , bigint(total_sort_time)  
    , bigint(sort_overflows) 
    , bigint(active_sorts) 
    , bigint(commit_sql_stmts)  
    , bigint(rollback_sql_stmts)  
    , bigint(rollback_sql_stmts+rollback_sql_stmts)
    , case when commit_sql_stmts + rollback_sql_stmts > 0 
        then decimal((float(total_sorts)/(float(commit_sql_stmts)+float(rollback_sql_stmts))),21,2)
        else 0
    , case when total_sorts > 0 
        then decimal((float(sort_overflows)/float(total_sorts)),21,2)
        else 0

I added case statements to verify that I’m never dividing by 0. There are a number of ways to do this – the previous one added one to each side to accomplish the same thing – a trick I’m not fond of. I also changed the last two returns to decimals, since that makes more sense than integers for where this data is going and what it is saying.

Creating Views Instead of using a Common Table Expression

While the last one used a Common Table Expression where it was absolutely not needed, this example instead created and dropped views for apparently no purpose:

create view DB2_MON_APPL as select * from table(snapshot_appl('',-1)) as sntable;
create view DB2_MON_APPL_INFO as select * from table(snapshot_appl_info('',-1)) as sntable;

select  ai.appl_id,
from    db2_mon_appl ap, db2_mon_appl_info ai
where   ap.agent_id = ai.agent_id
drop view DB2_MON_APPL;
drop view DB2_MON_APPL_INFO;

Perhaps the person writing this one didn’t know you could join two table functions? I’m perplexed. I re-wrote this one as:

select ai.appl_id
    , ai.appl_name
    , ai.appl_status
    , ap.locks_held
    , ap.lock_wait_time

from    table(snapshot_appl('',-1)) as ap
  join table(snapshot_appl_info('',-1)) ai on ap.agent_id = ai.agent_id;

I’d love to hear what completely off-the wall SQL you’ve found that just defies logic – please share in the comments!

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.