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 ( select 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 from table (SNAPSHOT_DATABASE('',-1)) as SNAPSHOT_DATABASE ) select bigint(s_heap_alloc) , bigint(t_sorts) , bigint(t_sort_time) , bigint(s_over) , bigint(a_sorts) , bigint(com_stmt) , bigint(roll_stmt) , bigint(com_stmt+roll_stmt), bigint((t_sorts+1)/(com_stmt+roll_stmt+1)), bigint((s_over+1)/(t_sorts+1)) 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 end , case when total_sorts > 0 then decimal((float(sort_overflows)/float(total_sorts)),21,2) else 0 end from table (SNAPSHOT_DATABASE('',-1)) as SNAPSHOT_DATABASE ;
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, ai.appl_name, ai.appl_status, ap.locks_held, ap.lock_wait_time 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!