StarQuest Technical Documents

StarSQL Jumbo Packages

Last Update: 28 June 2021
Product: StarAdmin
Version: 5.5x or later
Article ID: SQV00SQ052

Overview

The technical documentation Binding StarSQL Packages Using StarAdmin describes using StarAdmin to bind the host SQL packages needed to support StarSQL

A SQL package is an object that Db2 uses to process a SQL statement. Different packages are required to execute dynamic SQL, static SQL, and ODBC catalog functions. The process of binding packages only needs to be done once per Db2 instance if all StarSQL users share the same set of packages.

In some cases you may want to use jumbo packages, which allow StarSQL to set the maximum active statement handle limit to 1,314 rather than the default of 64. Each handle can each be used by one ODBC statement at a time.

To take full advantage of this option you must enable the UseJumboPackages Advanced Setting in StarAdmin before you bind packages, and it must be enabled in the StarSQL DSN or connection string or StarSQL for Java URL that is used to connect to the database.

If a dynamic package bound with 64 sections (UseJumboPackages=No) receives 65 or more active statements because the StarSQL DSN is configured to use jumbo packages, DB2 may report an error that the sections cannot be found. If packages are bound with the jumbo option enabled (UseJumboPackages=Yes), users can connect to DB2 using a StarSQL DSN that is configured to use either the typical (64 statement handles) or jumbo-sized (1,314 statement handles) packages.

Impact

Using jumbo packages may increase the RAM requirements of the ODBC or JDBC application (slightly) and disk requirements on the Db2 system (very slightly). However, this should not be an issue for modern computer systems.

Application Notes

As of SQDR Plus 5.23, the packages in the staging databases (SQDRPn) are bound as jumbo packages. The Launch Agent will automatically rebind packages in existing staging databases as jumbo, and the useJumboPackages=Yes is automatically added to Client ODBC connection string. This allows the use of Sequential Apply (available in SQDR 5.23 & later) to use an incremental group containing more than 64 tables.

Examining Existing Packages

To determine whether an existing StarSQL package was bound as a jumbo package, use the SQL below appropriate for your Db2 platform and package collection ID. The value returned for a default package will be in the range of 78-86; the value returned for a jumbo package will be in the range of 1328-1349.

Db2 for LUW:

select pkgschema,pkgname,total_sect from syscat.packages where pkgschema='STARSQL'

Db2 for i:

select count(*) from QSYS2.PKGSTMSTAT where PACKAGE_SCHEMA='STARSQL' and PACKAGE_NAME='SWRC0000'

Db2 for z/OS:

SELECT COUNT(*)-1 FROM SYSIBM.SYSPACKSTMT WHERE COLLID='STARSQL' AND NAME='SWRC4000'

 

 

Additional References

StarAdmin Release Notes
StarAdmin Online Help (accessible by clicking the "?" icon in the StarAdmin application)