Some noob DB2 questions

[RhythmAddict112]

Hi all,
Working on a new project here at work that is DB2 based...I'm a bit more accustomed to Oracle - at least for now. Anyway, firstly...what tool do most of you use to run ad-hoc queries against db2? Using the "command center" is making me want to pull my hair out. I can't even put queries on mutliple lines...Is this a setting I'm not seeing or something? I tried using the freeware version of TOAD but it won't work - requires me to have version 8.1.6 installed or later - I have v7 I believe.

Secondly, I'm tryin to figure out why my subselect won't work...
here is the SQL

select department from 
(select  dept.description department, papp.appestimate, papp.applicationCd, stat.description, appLoe.loe ,case when stat.description = 'Analysis' then papp.appestimate*.20 when stat.description = 'Design' then papp.appestimate  *.10 when stat.description = 'Code' then papp.appestimate *.40 when stat.description = 'Test' then papp.appestimate *.05 when stat.description = 'UAT' then papp.appestimate *.10 when stat.description = 'Completed' then papp.appestimate *.05 when stat.description = 'Requirements' then papp.appestimate *.20 when stat.description = 'Estimates Provided' then papp.appestimate*.10 end ActualWeighted from prtadmin.projectapp papp, prtadmin.applicationloe appLoe, projectmain pmain, prtadmin.status stat, prtadmin.applicationgrp appgrp, prtadmin.application app, departmentGrp dept 
where papp.applicationcd = appLoe.applicationCd 
and appLoe.year = 2006 
and papp.projectid = pmain.projectid 
and papp.appstatuscd = stat.statuscd 
and appgrp.groupcd = app.applicationgrpcd 
and papp.applicationcd = app.applicationcd 
and appgrp.deptgroupcd = dept.deptgroupcd  
and pmain.overalltargetyear = 2006) 
order by department;

It gives me the following error: SQL0104N An unexpected token ";" was found following "order by department".

Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601

Thanks for your help

All hail the INTERWEB!

 

[ddiamond]

Have you tried the command editor? That should allow you to enter your query on multiple lines. It is not as nice as Toad or quest's SQL Navigator for oracle, but it should work better than what you've described. Also, I believe you need to add an alias to your sub-query for this to work.

select department from 
(select  dept.description department, papp.appestimate, papp.applicationCd, stat.description, appLoe.loe ,case when stat.description = 'Analysis' then papp.appestimate*.20 when stat.description = 'Design' then papp.appestimate  *.10 when stat.description = 'Code' then papp.appestimate *.40 when stat.description = 'Test' then papp.appestimate *.05 when stat.description = 'UAT' then papp.appestimate *.10 when stat.description = 'Completed' then papp.appestimate *.05 when stat.description = 'Requirements' then papp.appestimate *.20 when stat.description = 'Estimates Provided' then papp.appestimate*.10 end ActualWeighted from prtadmin.projectapp papp, prtadmin.applicationloe appLoe, projectmain pmain, prtadmin.status stat, prtadmin.applicationgrp appgrp, prtadmin.application app, departmentGrp dept 
where papp.applicationcd = appLoe.applicationCd 
and appLoe.year = 2006 
and papp.projectid = pmain.projectid 
and papp.appstatuscd = stat.statuscd 
and appgrp.groupcd = app.applicationgrpcd 
and papp.applicationcd = app.applicationcd 
and appgrp.deptgroupcd = dept.deptgroupcd  
[blue]and pmain.overalltargetyear = 2006) xyz[/blue]
order by department;

 

[BrianTyler]

To run ad-hoc queries, I tend to use an editor (normally ULTRAEDIT) to create the query, and save it to a file. I then invoke the file using:

db2 -tvf mysql.sql > mysql.txt

On AIX, if I put an SQL job into execution I put it into a script:

JOBNAME=QUERY1
DATE=`date +"%Y%m%d_%H%M%S"`
LOGFILE=$LOGDIR/$JOBNAME"_"$DATE.log"
REPFILE=$DATADIR/$JOBNAME"_"$DATE$1".txt"
print "** STARTING $JOBNAME - $DATE ** " > $LOGFILE
echo $LOGFILE
echo $REPFILE
db2 -t -s +v +p connect to $DB2DBDFT;
db2 -t -s +v -z$REPFILE +p <<SQLFLAG >>$LOGFILE;
select
abc
,def
from
tab1
where
abc = $1
;
SQLFLAG
exit

 

[ddiamond]

Is SQLFLAG standard unix KSH, or is it AIX specific?

 

[BrianTyler]

SQLFLAG is any string of characters, it could have said FRED.

The db2 command pipes in everything from the <<SQLFLAG to the line containing ONLY SQLFLAG. It's really a form of reading Standard Input.

The big advantage in this method is that Parameters as placeholders can be used when the script is run, and will be placed in the SQL before it is evaluated, whereas parameters are not available in simple interactive DB2.

If you need more help in this method, please feel free to ask for a more detailed explanation, though the only UNIX experience I have is in AIX.