Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
on what basis we will say Badly constructed SQL,

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sudheerkmf

New User


Joined: 24 Nov 2006
Posts: 2
Location: Pune

PostPosted: Mon Dec 11, 2006 5:06 pm    Post subject: on what basis we will say Badly constructed SQL,
Reply with quote

for finding the poor performance.

what basis we will say badly construted Sql
Back to top
View user's profile Send private message

hariavinash

New User


Joined: 04 Jan 2006
Posts: 64

PostPosted: Mon Dec 11, 2006 10:33 pm    Post subject:
Reply with quote

query not optimized based on keys..
table scan..
cartesian product.. are few to start with.

cheers
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Tue Dec 12, 2006 7:23 pm    Post subject: Re: on what basis we will say Badly constructed SQL,
Reply with quote

Response Time of the query.

have look ino the Plan table if you have access,check for the attributes like Type of scan,match columns,list prefetch.JOIN METHOD...
sorting requirements.............


Thx,
Ravi.
Back to top
View user's profile Send private message
sudheerkmf

New User


Joined: 24 Nov 2006
Posts: 2
Location: Pune

PostPosted: Wed Dec 13, 2006 4:07 pm    Post subject: what basis we will say badly construted Sql
Reply with quote

ravi

how we can look into plan table,
could you please expalin the procedure.

we alreday exected queries in production like withh order, with out order

but we didnt get any differnce in the sql execution time.


thx
sudheer
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Fri Dec 15, 2006 5:36 pm    Post subject: Re: on what basis we will say Badly constructed SQL,
Reply with quote

When your are binding the program
will u give an option EXPLAIN(YES)
then thie will populate the values into the plan table.

normally plantable will will be named as

PLAN_TABLE with ur qualified object owner

XXXXXX.PLAN_TABLE
following checklist to see that you have not overlooked some of the basics:
?Is the query coded as simply as possible??
?Are all predicates coded correctly??
?Are there subqueries in your query??
?Does your query involve aggregate functions??
?Do you have an input variable in the predicate of an SQL query??
?Do you have a problem with column correlation??
?Can your query be written to use a noncolumn expression??



Post the query.........Somebody can help you........
Or contact your DBA's
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL SORT - GROUP Select on the basis ... srinathds83 JCL & VSAM 15 Mon Feb 24, 2014 1:01 pm
No new posts Merging 2 lines in one on conditional... abdul.faras DFSORT/ICETOOL 11 Tue Jun 25, 2013 11:00 pm
No new posts I need to split the file on the basis... Pandora-Box DFSORT/ICETOOL 4 Fri Jan 25, 2013 7:38 pm
No new posts Need to generate a list of datasets i... Sam.B CLIST & REXX 6 Wed Jan 16, 2013 10:48 am
No new posts Syncsort Splitting file on certain basis narasimha.g JCL & VSAM 10 Fri Sep 02, 2011 8:57 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us