Troubleshooting
Problem
This document contains information on extended dynamic SQL packages.
Resolving The Problem
Frequently asked questions about SQL packages
Q: What are SQL packages?
A: SQL packages are permanent objects used to store information related to prepared SQL statements. They can be used by the IBM i Access Client Solutions Windows Application Package ODBC driver and OLE DB provider and the IBM Toolbox for Java JDBC driver. SQL packages are also used by applications that use the QSQPRCED (SQL Process Extended Dynamic) API interface. The SQL packages created by ODBC and QSQPRCED are called extended dynamic SQL packages and are the subject of the following questions and answers. IBM® DRDA® also uses SQL package objects but they are considerably different in behavior and are not covered in this document.
Q: When do SQL packages get created?
A: ODBC, OLE DB, and JDBC connections check for the existence of the package when the client application issues the first prepare of an SQL Statement. If the package does not exist, it is created then (even though it may not yet contain any SQL statements). With QSQPRCED, creation of the package occurs when the application calls QSQPRCED specifying function '1'.
Q: How are SQL packages named?
A: SQL packages for ODBC are named by taking the application name specified in the data source configuration and appending three letters that are an encoded set of the package configuration attributes. The JDBC driver and OLE DB provider also have connection properties to specify the library and package name and how the package is to be used. For QSQPRCED, the name of the package is provided by the application.
Q: What library does the SQL package go into? Is there a preferred library for SQL packages?
A: For ODBC, this value is part of the data source configuration. For QSQPRCED, OLE DB and JDBC, the library is provided by the application. There is no preferred library for SQL packages. There are not any functional or performance differences based on the containing library. This choice can be made strictly on ease of management for the application.
Q: What are the advantages of using SQL packages?
A: Because SQL packages are a shared resource, the information built by preparing a statement is available to all the users of the package. This sharing saves processing time, especially in an environment when many users are using the same or similar statements. Because SQL packages are permanent, this information is also saved across job initiation and termination and across IPLs. In fact, SQL packages can be saved and restored on other systems. By comparison, dynamic SQL requires that each user to go through the prepare processing for a particular statement every time the user starts the application.
SQL packages also allow the system to accumulate statistical information about the SQL statements. That information results in better decisions about how long to keep cursors open internally and how to best process the data needed for the query. As mentioned earlier, this information is shared across users and retained for future use. For dynamic SQL, this information must be relearned by every job and every user.
Q: Do all prepared statements go into an SQL package?
A: Only specific SQL statements can be prepared. They are:
For ODBC applications, IBM i Access normally makes some further restrictions about the statements that can go into a package. This filtering by the ODBC driver can be overridden with ODBC by setting a special debug keyword in the data source, see document 10282349 or contact IBM support for details. An SQL statement goes into the package only if one of the following is true: (This Information is subject to change without prior notice):
Q: What are SQL packages?
A: SQL packages are permanent objects used to store information related to prepared SQL statements. They can be used by the IBM i Access Client Solutions Windows Application Package ODBC driver and OLE DB provider and the IBM Toolbox for Java JDBC driver. SQL packages are also used by applications that use the QSQPRCED (SQL Process Extended Dynamic) API interface. The SQL packages created by ODBC and QSQPRCED are called extended dynamic SQL packages and are the subject of the following questions and answers. IBM® DRDA® also uses SQL package objects but they are considerably different in behavior and are not covered in this document.
Q: When do SQL packages get created?
A: ODBC, OLE DB, and JDBC connections check for the existence of the package when the client application issues the first prepare of an SQL Statement. If the package does not exist, it is created then (even though it may not yet contain any SQL statements). With QSQPRCED, creation of the package occurs when the application calls QSQPRCED specifying function '1'.
Q: How are SQL packages named?
A: SQL packages for ODBC are named by taking the application name specified in the data source configuration and appending three letters that are an encoded set of the package configuration attributes. The JDBC driver and OLE DB provider also have connection properties to specify the library and package name and how the package is to be used. For QSQPRCED, the name of the package is provided by the application.
Q: What library does the SQL package go into? Is there a preferred library for SQL packages?
A: For ODBC, this value is part of the data source configuration. For QSQPRCED, OLE DB and JDBC, the library is provided by the application. There is no preferred library for SQL packages. There are not any functional or performance differences based on the containing library. This choice can be made strictly on ease of management for the application.
Q: What are the advantages of using SQL packages?
A: Because SQL packages are a shared resource, the information built by preparing a statement is available to all the users of the package. This sharing saves processing time, especially in an environment when many users are using the same or similar statements. Because SQL packages are permanent, this information is also saved across job initiation and termination and across IPLs. In fact, SQL packages can be saved and restored on other systems. By comparison, dynamic SQL requires that each user to go through the prepare processing for a particular statement every time the user starts the application.
SQL packages also allow the system to accumulate statistical information about the SQL statements. That information results in better decisions about how long to keep cursors open internally and how to best process the data needed for the query. As mentioned earlier, this information is shared across users and retained for future use. For dynamic SQL, this information must be relearned by every job and every user.
Q: Do all prepared statements go into an SQL package?
A: Only specific SQL statements can be prepared. They are:
| ALTER TABLE | CREATE PROCEDURE | LOCK TABLE |
| CALL | CREATE VIEW | RENAME |
| COMMENT ON | DELETE | REVOKE |
| COMMIT | DROP | ROLLBACK |
| CREATE COLLECTION | GRANT | SELECT |
| CREATE INDEX | INSERT | SET TRANSACTION |
| CREATE TABLE | LABEL ON | UPDATE |
For ODBC applications, IBM i Access normally makes some further restrictions about the statements that can go into a package. This filtering by the ODBC driver can be overridden with ODBC by setting a special debug keyword in the data source, see document 10282349 or contact IBM support for details. An SQL statement goes into the package only if one of the following is true: (This Information is subject to change without prior notice):
- The statement contains parameter markers.
- It is an INSERT with subselect, for example INSERT INTO table1 SELECT FROM table2 WHERE ...
- It is a DECLARE PROCEDURE.
- It is a positioned UPDATE or DELETE.
QSQPRCED packages do not have these restrictions and contain any of the statements listed previously when they are prepared by using function '2' or '9'.
Starting with IBM i 6.1 PTF SI30855, a statement must be prepared 3 times before it is added to the SQL package. This change was made to prevent filling the package with statements that aren't used frequently.
Q: How can I tell what statements are in an SQL package?
A: The PRTSQLINF command can be used to produce a formatted report showing the SQL statement and information about the access plan used to access the data.
Q: Can the same statement appear multiple times in the same SQL package?
A: Every PREPARE operation checks to see whether there is already a prepared statement with all of the same statement text and attributes. If there is, a new statement name entry (about 80 bytes) is allocated but it just points to the corresponding duplicate information already in the package.
Q: How can I determine whether the SQL package is being used?
A: The database monitor can be used to log information about SQL processing on the system. It includes the name of the package in the SQL summary records. The following statement from interactive SQL shows the package, the SQL operation, and the statement text.
When you use ODBC and JDBC, you can also check for the warning or error message,
The QZDASOINIT job has a lock on the SQL package when it is active. Working with the job and displaying the locks it holds shows the package and library name.
Q: What data is stored in an SQL package?
A: The SQL package contains all the necessary information to execute the prepared statement. This information includes registry of the statement name, statement text, internal parse tree, definitions of all tables and fields involved, and the query access plan needed to access the tables at run time.
Q: How large is an SQL package?
A: In V4R3 and later, the maximum size of a package was increased to 16,384 statements or approximately 512MB, whichever comes first. Note, the SQL package must be created on V4R3 or later to be capable of this new larger size.
IBM i 5.2 with APAR SE09389 can support package sizes of up to 1Gigabyte by using a new QAQQINI SQL_INCREASE_PKG_LIMIT option. The default remains at 512MB.
Before V4R3, SQL packages were limited to 16MB. Furthermore, ODBC packages had more restrictions on the number of statements allowed.
Starting with IBM i 6.1 PTF SI30855, a statement must be prepared 3 times before it is added to the SQL package. This change was made to prevent filling the package with statements that aren't used frequently.
Q: How can I tell what statements are in an SQL package?
A: The PRTSQLINF command can be used to produce a formatted report showing the SQL statement and information about the access plan used to access the data.
Q: Can the same statement appear multiple times in the same SQL package?
A: Every PREPARE operation checks to see whether there is already a prepared statement with all of the same statement text and attributes. If there is, a new statement name entry (about 80 bytes) is allocated but it just points to the corresponding duplicate information already in the package.
Q: How can I determine whether the SQL package is being used?
A: The database monitor can be used to log information about SQL processing on the system. It includes the name of the package in the SQL summary records. The following statement from interactive SQL shows the package, the SQL operation, and the statement text.
SELECT qqc103 as "Package Name", qqc104 as "Package Lib",
qvc24 as "Access Plan Not Saved rc",
qqi4 as "Total Time ms",
qqc21 as "Stmt Operation", qq1000 as "Stmt Text"
from <db monitor file> where qqrid = 1000When you use ODBC and JDBC, you can also check for the warning or error message,
Extended Dynamic has been disabled, to determine whether the client was unable to use an SQL package. This message is generated when the first SQL statement is prepared. It is returned as a warning, an error, or not returned at all depending on the settings in the ODBC data source or JDBC connection string. Note, the message is not logged in the job log.The QZDASOINIT job has a lock on the SQL package when it is active. Working with the job and displaying the locks it holds shows the package and library name.
Q: What data is stored in an SQL package?
A: The SQL package contains all the necessary information to execute the prepared statement. This information includes registry of the statement name, statement text, internal parse tree, definitions of all tables and fields involved, and the query access plan needed to access the tables at run time.
Q: How large is an SQL package?
A: In V4R3 and later, the maximum size of a package was increased to 16,384 statements or approximately 512MB, whichever comes first. Note, the SQL package must be created on V4R3 or later to be capable of this new larger size.
IBM i 5.2 with APAR SE09389 can support package sizes of up to 1Gigabyte by using a new QAQQINI SQL_INCREASE_PKG_LIMIT option. The default remains at 512MB.
Before V4R3, SQL packages were limited to 16MB. Furthermore, ODBC packages had more restrictions on the number of statements allowed.
For more information, see Determining whether an SQL Package Is Full. A sample QAQQINI INSERT statement is provided.
Q: Are there any negative performance considerations for using large packages?
A: The V4R3 enhancements for bigger packages also include support for hash tables. This enhancement improves performance of searches by either statement name or searches by statement text. There are no significant performance impacts for using relatively large packages. There are performance impacts associated with many concurrent users (1,000 or more) adding new statements to a package. The impacts include many users running statements already in the package but whose SQL statements require new access plans.
Q: What happens when SQL packages get full?
A: For database host server (QZDASOINIT jobs), the database server attempts to detect the package full condition (SQL0904, reason code 7). When a database server job detects the package full condition, it sends one PWS0018 message to the job log but no warning or error to the client (ODBC or JDBC). The database server job transparently switches over to dynamic SQL for the newly prepared statements. Statements that were previously prepared in the SQL package continue to be used. This behavior is equivalent to switching to the odbc or jdbc package "use" setting as opposed to "use/add."
The package full condition is detected based on remaining space in the package and an estimate of the space the new statement requires. In rare cases, this estimate can be incorrect. Or a new plan for a statement can require more space than is available. In these rare cases, the application might receive an error message. The only recovery is to delete the package.
For QSQPRCED, an SQL code of -904 is returned in the SQLCA data structure and the application must decide how to proceed.
Q: Are there other times when an SQL package can become unusable?
A: SQL packages have some attributes that are stored at the package level and must be compatible with the application. For example, ODBC SQL packages allow specification of a default collection for unqualified table names. If the SQL package exists and its default collection does not match that of the client application, the package is not used and the user uses dynamic SQL. The same issue can occur when the job CCSID changes.
Q: When must I delete SQL packages?
A: Packages must be deleted whenever they become 'unusable' as defined previously. There are various problems related to SQL packages; they include a number of unusual errors: truncation, data-mapping errors, incorrect describe information, and so on.
Delete packages whenever significant changes are made to the database, operating system, or hardware (changes that might cause many access plan rebuilds). Whenever the hardware or operating system is upgraded it is a good idea to delete the SQL packages. Because extended dynamic SQL packages are re-created when the application is run, there is little harm in deleting them. The time it takes to debug a problem due to incorrect information in a package can be costly.
Q: How do I delete SQL packages?
A: Before you delete SQL packages, all applications that use the package must first be ended. To delete a specific SQL package, use the DLTSQLPKG command. To locate and delete SQL packages, use the WRKOBJ command and select Option 4 to delete them. The command syntax is as follows:
Only extended dynamic SQL packages are automatically re-created by the application. Do not delete IBM-supplied SQL packages: packages in QSYS, packages whose names start with Q, and DRDA packages. If you are unsure about whether a specific SQL package is used by DRDA, use the PRTSQLINF command and look at the first page of the report. DRDA packages contain an RDB keyword entry identifying the relational database for DRDA.
Q: How do I restore the IBM-supplied packages?
A: If either system package QSYS/QSQLPKG2 or QSYS/QSQXDPKG is accidentally deleted, they must be restored from a backup made from the same OS/400 or IBM® i5/OS® release. The QIWS/QZDAPKG package is re-created by restarting the database server - no restore is required. If a local backup of the QSYS packages is not available, IBM support provides a save file containing both packages for all supported releases at the following FTP site (anonymous login): public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/. This directory has a file, Index.txt that describes the contents of this directory, including these save files.
Q: Why can't I delete a package with a name of _CSETUP (or other nonstandard system object names)?
A: The DLTSQLPKG command accepts only SQL package names that conform to OS/400 or i5/OS system naming conventions. These conventions exclude special characters in the first position. To delete the package, refer to the instructions in APAR SA59057 or contact your support provider for assistance.
Q: What are the QSQLPKG2 and QSQXDPKG packages in QSYS for? Can they be deleted?
A: The QSQLPKG2 and QSQXDPKG packages in QSYS are system packages used as models to create application packages. Do not delete these packages. If they are accidentally deleted, they must be restored from backup. There might also be several system SQL packages in library QPGL that start with a Q. When these packages are deleted, they are re-created when the jobs that use them are restarted.
Q: What are the "best practices" for using packages?
A: Best practices include the following:
Q: Are there any negative performance considerations for using large packages?
A: The V4R3 enhancements for bigger packages also include support for hash tables. This enhancement improves performance of searches by either statement name or searches by statement text. There are no significant performance impacts for using relatively large packages. There are performance impacts associated with many concurrent users (1,000 or more) adding new statements to a package. The impacts include many users running statements already in the package but whose SQL statements require new access plans.
Q: What happens when SQL packages get full?
A: For database host server (QZDASOINIT jobs), the database server attempts to detect the package full condition (SQL0904, reason code 7). When a database server job detects the package full condition, it sends one PWS0018 message to the job log but no warning or error to the client (ODBC or JDBC). The database server job transparently switches over to dynamic SQL for the newly prepared statements. Statements that were previously prepared in the SQL package continue to be used. This behavior is equivalent to switching to the odbc or jdbc package "use" setting as opposed to "use/add."
The package full condition is detected based on remaining space in the package and an estimate of the space the new statement requires. In rare cases, this estimate can be incorrect. Or a new plan for a statement can require more space than is available. In these rare cases, the application might receive an error message. The only recovery is to delete the package.
For QSQPRCED, an SQL code of -904 is returned in the SQLCA data structure and the application must decide how to proceed.
Q: Are there other times when an SQL package can become unusable?
A: SQL packages have some attributes that are stored at the package level and must be compatible with the application. For example, ODBC SQL packages allow specification of a default collection for unqualified table names. If the SQL package exists and its default collection does not match that of the client application, the package is not used and the user uses dynamic SQL. The same issue can occur when the job CCSID changes.
Q: When must I delete SQL packages?
A: Packages must be deleted whenever they become 'unusable' as defined previously. There are various problems related to SQL packages; they include a number of unusual errors: truncation, data-mapping errors, incorrect describe information, and so on.
Delete packages whenever significant changes are made to the database, operating system, or hardware (changes that might cause many access plan rebuilds). Whenever the hardware or operating system is upgraded it is a good idea to delete the SQL packages. Because extended dynamic SQL packages are re-created when the application is run, there is little harm in deleting them. The time it takes to debug a problem due to incorrect information in a package can be costly.
Q: How do I delete SQL packages?
A: Before you delete SQL packages, all applications that use the package must first be ended. To delete a specific SQL package, use the DLTSQLPKG command. To locate and delete SQL packages, use the WRKOBJ command and select Option 4 to delete them. The command syntax is as follows:
WRKOBJ OBJ(*ALL/*ALL) OBJTYPE(*SQLPKG)Only extended dynamic SQL packages are automatically re-created by the application. Do not delete IBM-supplied SQL packages: packages in QSYS, packages whose names start with Q, and DRDA packages. If you are unsure about whether a specific SQL package is used by DRDA, use the PRTSQLINF command and look at the first page of the report. DRDA packages contain an RDB keyword entry identifying the relational database for DRDA.
Q: How do I restore the IBM-supplied packages?
A: If either system package QSYS/QSQLPKG2 or QSYS/QSQXDPKG is accidentally deleted, they must be restored from a backup made from the same OS/400 or IBM® i5/OS® release. The QIWS/QZDAPKG package is re-created by restarting the database server - no restore is required. If a local backup of the QSYS packages is not available, IBM support provides a save file containing both packages for all supported releases at the following FTP site (anonymous login): public.dhe.ibm.com/services/us/igsc/cs2/ApiSamples/. This directory has a file, Index.txt that describes the contents of this directory, including these save files.
Q: Why can't I delete a package with a name of _CSETUP (or other nonstandard system object names)?
A: The DLTSQLPKG command accepts only SQL package names that conform to OS/400 or i5/OS system naming conventions. These conventions exclude special characters in the first position. To delete the package, refer to the instructions in APAR SA59057 or contact your support provider for assistance.
Q: What are the QSQLPKG2 and QSQXDPKG packages in QSYS for? Can they be deleted?
A: The QSQLPKG2 and QSQXDPKG packages in QSYS are system packages used as models to create application packages. Do not delete these packages. If they are accidentally deleted, they must be restored from backup. There might also be several system SQL packages in library QPGL that start with a Q. When these packages are deleted, they are re-created when the jobs that use them are restarted.
Q: What are the "best practices" for using packages?
A: Best practices include the following:
- Use extended dynamic support with applications that tend to reuse the same SQL statement multiple times. Applications that generate, run once, and discard unique SQL statements receive little benefit in using extended dynamic support. Also, applications that create or alter database metadata used by a statement that can be placed in a package cannot use extended dynamic.
- Verify that the SQL statements used by the application can be placed in a package. For example, the SQL statement must use parameter markers to be placed in a package.
- Use a statement cache. A statement cache is a programming technique where an SQL Statement is prepared only once, the first time it is used, but it is run many times. The cache is typically scoped to one instance of an application or web server. There are several significant performance advantages in using a statement cache. With packages, the statement cache helps reduce duplicate entries by ensuring that multiple executions of the same SQL statement use the same statement name. This feature reduces duplicate entries in the package.
Using cache package local setting in JDBC and ODBC has similar benefits. Cache package local is not recommended for large packages (packages over 16Meg). - Consider creating a "package primer" application to prime the package with a "
package use/add" setting, while users run the application with the "package use" setting. This technique works well for applications that have many different statements. A package primer application with the package "use/add" setting is run to create and prime a package with the commonly used SQL Statements. Users then run the application with the package option of "use." The users get the advantage of running common SQL statements from a package. The users avoid issues such as lock or seize delays due to multiple concurrent users attempting to update or add to the package or the package first filling with less commonly used statements. - Delete extended dynamic SQL packages when you upgrade the system hardware or move to a new release.
This document describes how to find the extended dynamic SQL packages that can be deleted: Finding and Deleting Extended Dynamic SQL Packages Used By Access Client Solutions ODBC
This document describes how to delete the SQL package used by the database host server, which can affect almost every database client: Deleting SQL Package of the Database Host Server
[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Host Servers","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Historical Number
15484546
Was this topic helpful?
Document Information
Modified date:
22 June 2021
UID
nas8N1019633