Writing Efficient SQL Queries
 

GENERAL

·         Keep all SQLs as simple as possible.

 ·        Always use static SQL.

 ·        Only use views in your program. Never refer directly to the base tables.

 ·        Always use unqualified view names in your SQL. This ensures that your program can be easily moved between test levels and into production, and between markets, without any source code changes. For example, use

SELECT A,

       B

FROM TABLE1

 

rather than

 

SELECT A,

       B

FROM OWNER1.TABLE1

 

·         Make sure you know what indexes are present on your tables.  Also check which is your clustering index  and which columns are in ASCending and which are in DESCending order

 ·         If possible, write SQL that will use only index columns, unless other columns are wanted  for processing. This avoids the need to access the data.  If possible use the columns on your primary key or clustering index, rather than other index columns.

 ·         Always read DB2 columns into their respective DCLGEN variables. Its good practice.

 

Home

Cobol Questions

DB2 Questions

JCL Questions

IMS DB Questions

Focus / Webfocus