RE: DB2/400 JOIN queries, ODBC

[mbreit@xxxxxxxxxx]

George,

I would guess that the error you are getting when running large join
queries is a timeout from the OS/400 query optimizer governor.   The query
optimizer basically looks at your query, predictes how long it will take to
run, and then either runs it or fails it based on a maximum query time
limit.  The query time limit is specified as a query attribute for each
job.  The command Change Query Attributes (CHGQRYA) will show you the
current values and allow them to be changed.  By default the query time
limit attribute is controlled by the system value QQRYTIMLMT (query
processing time limit).  You may want to check what value this system value
has on your system, as well as checking what value your jobs are running
with.

As for the performance of your join queries in general, how you write the
query can make a difference.  In particular, you can specify a join using
either the JOIN syntax or using the WHERE clause.  If you use the JOIN
syntax then the DB2 query optimizer will not attempt to re-order the join
order and will use the order you specify on the JOIN.  If you use the WHERE
clause syntax then the optimizer may re-order the join order.  By running
the query with debug turned on (STRDBG) you will generally get messages
from the optimizer telling you what join order is being used and whether it
is re-ordering things.  By looking at these messages you may find either a
more efficient join order or find some indexes over the joined columns that
will speed up processing.  The DB2 for AS/400 SQL Programming book offers
some other tips for running join queries, and you can find information
online at http://www.as400.ibm.com/db2/db2main.htm (DB2 for AS/400
homepage).

Finally, see the following link for some information on what can be done
about securing your system for ODBC access.  The only way to fully control
access to DB2 tables (files) is via OS/400 object level security.  But
depending what ODBC drivers are being used to access your system there may
be some exit programs you can use to restrict ODBC access.
http://www.as400.ibm.com/db2/odbcsec.htm

Mike Breitbach  -  Software Engineer
IBM Rochester,  AS/400 ERP Development
mbreit@us.ibm.com   /   (507)253-1031


+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---