Autonomous Schema Update (ASU) and Authorized Data Truncation (ADT):ASU: Autonomous Schema Update (-SQLWidthUpdate) was added as a database feature in OpenEdge 11.6 to resolve SQL width problems that arise when SQL applications and SQL Tools are used alongside deployed ABL applications.
The ASU feature updates the SQL column width in the schema when
ADT: Authorized Data Truncation (-SQLTruncateTooLarge) occurs for data in a CHARACTER | VARCHAR column.
Unlike
ADT which truncates field data exceeding the column size in order that SQL operations do not fail, the
ASU feature does not update the schema immediately as soon as too-large field values are read. The
schema update occurs after completion of the Query which sees the too-large field entries when a user query experiences data truncation events (column value larger than defined size), and after a small amount of time needed to “send” the needed update to another server management thread that does the schema update. Waiting until query completion enables the SQL manager to evaluate at as many too-large field values as the query sees and then make one schema update without affecting the application’s transaction.
Where to enable the -SQLTruncateTooLarge and -SQLWidthUpdate features:Authorized Data Truncation (ADT) and
Autonomous Schema Update (ASU) can be enabled for whichever Login Broker it is needed. The only condition is that the Broker must accept SQL connections with a
-ServerType of either "Both" (which is default) or "SQL".
Example:
proserve <dbname> -S <port> -SQLTruncateTooLarge ON -SQLWidthUpdate ON -ServerType both
proserve <dbname> -m3 -S <port> -SQLTruncateTooLarge ON -SQLWidthUpdate ON -ServerType SQL
proserve <dbname> -m3 -S <port> -ServerType ABL
Make sure to use '-SQLTruncateTooLarge OUTPUT' instead of '-SQLTruncateTooLarge ON' or '-SQLTruncateTooLarge ALL' if using OpenEdge version 11.7 or higher. The 'OUTPUT' option will only truncate the result set level, but the SQL operations will internally use the actual data. If it is set to 'ON' or 'ALL', then the SQL operations with also occur on the truncated data.
When -SQLTruncateTooLarge and or -SQLWidthUpdate is enabled on a Secondary Broker (-m3) there is no message or status on the broker startup indicating it is enabled. However, for any SQL query run against a SQL broker where the -SQLTruncateTooLarge is enabled, when any data returned is truncated there is a truncation logging message written to the database lg file:
SQLSRV2 2: (-----) 13 authorized data truncation action(s) performed.
ADT (-SQLTruncateTooLarge), can be used on an OpenEdge Target Replication enabled database where truncated data are returned to the -ERO clients rather than throwing an error. But the ASU feature (-SQLWidthUpdate), cannot be used on target databases because it is a schema update and these are only propagated from the related source database to target database(s) through AI notes.
The
ADT feature can also be enabled or disabled for a specific ODBC or JDBC client session in the connection URL using the
truncateTooLarge parameter, which overrides the Server startup parameter
(-SQLTruncateTooLarge). The
ASU feature can only be enabled when the
Login Broker starts for the related SQL Server for client connections, not for client sessions specifically.
Example:
sqlexp -url "jdbc:datadirect:openedge://localhost:<port>;databasename=<databasename>;truncateTooLarge=output;"
For further information refer to Article
How to enable Authorized Data Truncation in a JDBC or ODBC connection.When using OE Management or OE Explorer,
ASU can be enabled by specifying the -SQLWidthUpdate parameter in the "Other arguments" field of the database configuration on the General page. In the conmgr.properties file it will look as follows:
[configuration.sports2000.defaultconfiguration]
afterimageprocess=false
asynchronouspagewriters=1
beforeimageprocess=true
database=sports2000
displayname=defaultConfiguration
monitored=true
otherargs=-SQLWidthUpdate ON
servergroups=sports2000.defaultconfiguration.defaultservergroup
watchdogprocess=true
When using OE Management or OE Explorer,
ADT can be enabled by selecting the value of "on", "output" or "all" in the "SQL Truncate Too Large (-SQLTruncateTooLarge)" field of the database configuration on the SQL Configuration page. In the conmgr.properties file it will look as follows:
[configuration.sports2000.defaultconfiguration]
afterimageprocess=false
asynchronouspagewriters=1
beforeimageprocess=true
database=sports2000
displayname=defaultConfiguration
monitored=true
otherargs=-SQLWidthUpdate ON
servergroups=sports2000.defaultconfiguration.defaultservergroup
sqltruncatetoolarge=on
watchdogprocess=true
ASU and ADT Server Startup Parameter Interconnection
- Enabling ASU by specifying "-SQLWidthUpdate ON", will explicitly turn ON ADT in order for the schema update to be able to use the information about the actual size of the columns involved that the user connection accumulated. If ADT is disabled, "SQLTruncateTooLarge OFF" no data exceeding the column size will be truncated.
- Enabling ADT by specifying "-SQLTruncateTooLarge ON", only authorized data truncation (ADT) will be performed. ASU is not enabled unless it is also enabled with "-SQLWidthUpdate ON".
