IBM i SQL Services information

1. Limiting the number of columns returned in a query's select list does not improve overall performance. The information for all the columns in each row is still gathered.  Only the final projection of the column values in the result set is affected.
   For example, the following queries will have similar performance since they both request information about all programs in library TESTLIB.  Only the list of columns returned is different.

   SELECT * 
     FROM qsys2.program_info WHERE program_library = 'TESTLIB';
   
   SELECT program_name, object_type, text_description  
     FROM qsys2.program_info WHERE program_library = 'TESTLIB';

2. The QSYS2.OBJECT_STATISTICS table function returns information about objects. If you are looking for a list of objects, request only the names, not the detailed attributes.
   • To get a list of libraries, use this query.

     SELECT * 
       FROM TABLE (qsys2.object_statistics('*ALLSIMPLE', '*LIB')); 

   • To get a list of *PGM objects in a specific library, use this query.

     SELECT * 
       FROM TABLE (qsys2.object_statistics('TESTLIB', '*PGM', '*ALLSIMPLE')); 

3. To provide a performance boost, several views provide a limited data set.  These have _BASIC in the name.  IBM suggests you use the basic version of a view whenever possible. For example, USER_INFO and USER_INFO_BASIC provide very similar information.  Some columns returned by USER_INFO require extra overhead to access.  If you do not need these columns, use USER_INFO_BASIC for improved performance.

   SELECT authorization_name, status, password_change_date 
     FROM qsys2.user_info_basic;

4. Some table functions have parameters to limit the columns that return data values.  ACTIVE_JOB_INFO is an example of a table function that has a parameter that lets you select the level of detail to return. A value of NONE for the DETAILED_INFO parameter returns basic information for all jobs. A value of ALL returns all the available information.  Additional values provide intermediate subsets of information.  Based on the details you need, select the lowest level value that satisfies your requirements.  The columns that are not included in the detail that is selected will return the null value.

   SELECT * FROM TABLE (qsys2.active_job_info (detailed_info => 'NONE'));

5. Some table functions have parameters that filter the data that is returned. Using a filter parameter to limit the result set has better performance than not providing a filter and adding a WHERE clause to eliminate some rows. In the first case, the rows that do not match the filter are never seen by the SQL interface. In the second case, all the rows are generated by the table function and then removed while processing the predicates.
   For example, IFS_OBJECT_STATISTICS has a filter for the types of objects to be returned. If you only want stream files (*STMF) to be returned by the function, use the  filter to avoid any processing for other types of objects.

   SELECT * FROM TABLE(qsys2.ifs_object_statistics('/home/mystuff', object_type_list => '*STMF'));

   Writing the same query with the filtering done by the WHERE clause returns the same results. This is much less efficient and should be avoided.

   -- Do not filter this way!
   SELECT * FROM TABLE(qsys2.ifs_object_statistics('/home/mystuff')) WHERE object_type = '*STMF'; 

6. When using a view to return a list of objects, add predicates to limit the search for object.
   For example, querying the OBJECT_LOCK_INFO view with no predicates will look at EVERY object in EVERY library on the system to see if it is locked. This will be very slow.

   SELECT * FROM qsys2.object_lock_info;

   If you add a predicate for a specific library, only objects in that library will be considered. 

   SELECT * FROM qsys2.object_lock_info WHERE object_schema = 'MYLIB';

   If you add a predicate with only an object name, every library will be examined to look for all objects of any object type with that object name.

   SELECT * FROM qsys2.object_lock_info WHERE object_name = 'MYFILE';

   Narrowing the search to the exact library, exact object type, and exact object name has the best performance.

   SELECT * FROM qsys2.object_lock_info 
     WHERE object_schema = 'MYLIB' AND object_type = '*FILE' AND object_name = 'MYFILE';

7. If you need to work with large sets of information that are slow to access and your application can tolerate working with data that is not current, consider establishing a data mart with a snapshot of the data.  By capturing a version of the information, the data is quick to access.
   For example, establish a table that contains a list of all the bound modules on the system that are not in libraries that start with 'Q'.

   CREATE OR REPLACE TABLE mylib.bound_module_list AS 
   (SELECT * FROM qsys2.bound_module_info 
     WHERE program_library NOT LIKE 'Q%') WITH DATA
     ON REPLACE DELETE ROWS;

   Once this table is built and populated, multiple queries can be run against the static data without incurring the cost of gathering information about all the programs. A data mart table could be generated in a batch job that is submitted on a regular basis.

Examples

Working examples using SQL services can be found in the following places:

• Access Client Solutions (ACS) Run SQL Scripts by selecting Insert from Examples. 
  
• IBM i Tutorials, Demos, and SQL examples 

Documentation

Several landing pages document the SQL services that are available on IBM i. The following list describes the landing pages for SQL services.