Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration » Database options » Alphabetical list of database options

parameterization_level option

Controls automatic parameterization of client statements.

Allowed values

Off, Simple, Forced

Default

Simple

Scope
  PUBLIC role For current user For other users
Allowed to set permanently? Yes, with SET ANY PUBLIC OPTION Yes Yes, with SET ANY PUBLIC OPTION
Allowed to set temporarily? Yes, with SET ANY PUBLIC OPTION Yes (current connection only) No
Remarks

When a client application prepares a SQL statement, the database server may choose to replace constant literals within the SQL text with parameter placeholders prior to preparing the statement. The resulting SQL statement is more general. When subsequent SQL statements that differ only by values of the parameterized constants are executed, they can be matched with the parameterized statement. Generalized SQL statements enable two potential performance benefits:

  • If the parameterized statement is cached by the client connection, then the preparation of future SQL statements may be avoided.
  • If the database server caches an execution plan for the parameterized statement, then optimization of future SQL statements may be avoided.

The first benefit requires client statement caching to be enabled, while the second benefit requires both client statement caching and server plan caching to be enabled.

Parameterization is transparent to the client application, and automatically inserted parameters are not visible to a DESCRIBE of the statement or cursor.

Parameterization behavior is controlled by the following values for this option:

  • Off Statements are not parameterized. This setting corresponds to behavior in versions 16 and earlier of the software.
  • Simple The database server decides when to parameterize statements and which constants within each statement to parameterize.
  • Forced The database server parameterizes every statement as soon as possible and all eligible constants within each statement are replaced with placeholders. However, there are some statement types and query constructs for which parameterization is not supported either because it is syntactically invalid or because it is not generally useful, so constant literals within those contexts are not replaced.
You can override any temporary or PUBLIC settings for this option within individual INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements by including an OPTION clause in the statement.