|
|
|
|
|
This is a technical parameter that specifies how your queries interact with other transactions being run on your database server. This parameter is relevant only if you are querying a table that is being updated at the same time you are running queries. Committed Read (the default) means you see only rows that have been committed. UnCommitted Read means you see rows even if they have not been committed.
For some databases (such as DB2), when Committed Read is used, a query will take “read locks” and can hang while running against a table which is being updated. In this type of environment, it is recommended that Uncommitted Read is used.
Uncommitted Read is not available on all databases, and if that is the case Uncommitted Read will be ignored if specified.
When this option is specified, AQT will automatically close a database connection if you have not used it for the specified period. Your system administrator may ask you to set this option, as it will prevent idle connections from holding resources on your database server.
You specify the disconnect period in minutes. Setting the period to zero will disable this feature (sessions will not time out).
If you change this option during an AQT session, the value will come into effect immediately except when changing it from zero to a non-zero amount (this will only come into effect the next time you sign onto a database).
AQT will not disconnect from the database if you are using Transactions and have uncommitted changes. It will only disconnect once you have Committed or Rolled Back your changes.
If AQT terminates your connection, you can reestablish it by (from the Database Explorer window) going File > Reconnect to <database> or using the Auto-Reconnect option.
Specifying both Disconnect if Idle and Auto-Reconnect will prevent idle sessions persisting on the database server, while having minimal impact on the user.
This is a technical parameter relating to the use of parameter markers in the queries that populate the Database Explorer window.
By default, this is set on. You should only consider changing this if you are having problems with the displays on the Database Explorer window. You may need to do this if your ODBC driver is having problems with the use of parameter markers in queries.
With this selected (the default), all SQL statements will be committed as soon as they have been run. If this option is set off, AQT will enter Transaction mode as soon as you have signed on to a database; changes will only be committed once you explicitly do a Commit.
This is discussed in more detail in Using Transactions.
This option is useful in some rare circumstances, where AQT is found to be holding locks after a query (or other SQL statement) has been run. When this is selected, once an SQL statement has run AQT will:
By default, when AQT gets an error fetching your data from the database, it will stop. In some circumstances you might wish it to ignore such errors and continue getting data from the database. This is useful if you have some bad data in a row (such as an invalid timestamp) that is preventing a table from being displayed.
When this options is specified, AQT will add the following clause to the end all Select statements run against a DB2 server. This applies for DB2/UDB, DB2 z/OS and DB2 for iSeries:
Fetch First x Rows Only Optimize For x Rows
where x is:
If this option is not selected, the DB2 ODBC Driver will add its own Fetch First x Rows Only Optimize For x Rows clause.
When AQT adds this clause, it is mixed sentence case (as above); when the DB2 ODBC Driver adds this clause it is in upper case. This helps you determine (If you are running a trace or system monitor) whether it is AQT or the ODBC Driver which has added this clause.
Specifying this option can significantly improve performance when running against DB2 z/OS.
This clause will not be added if:
In some circumstances, column and table names need to have double-quotes placed around them. This is needed either
By default, AQT will only place quotes around the name if it contains a blank or special character. However this isn’t completely full-proof – AQT checks for some but not all special characters, and it doesn’t check for reserved words.
If you set this option on, AQT will always put quotes around table and column names.
When this option is selected (which is the default), tables names in SQL statements are generated as Schema.Tablename (example: DEV.CUSTOMERS).
When this option is de-selected, table names are generated as Tablename only (example: CUSTOMERS). You might use this if the tables are within your own schema/user or you have a number of schemas specified in search_path (for Postgresql, Vertica and some other databases).
When this option is specified, AQT will issue a set rowcount command to limit the query results to the correct number of rows. This can give a significant performance improvement when accessing large tables. It is recommended that this option is selected. For more detail on this see Rowcount for SQL Server and Sybase.
For Sybase, this option will only work if the Sybase ODBC Datasource has been configured to have Select Method of 1-Direct.
AQT will not use Set Rowcount if you have specified Enable Get More Rows feature.
When this is specified, AQT will add a Limit x clause to the end all Select statements run against either a PostgreSQL, MySQL, MariaDB or Snowflake database (x is as described above). This is useful if you are accessing large tables in any of these databases.
AQT will not do this if:
When this option is specified, AQT will amend your query SQL to include the SAMPLE x clause, where x is the Max Rows Displayed value.
For example, if Max Rows Displayed is 1000 and you code:
select * from table
AQT will amend this to:
select * from table sample 1000
This option can't be used if your table has LOB columns. If you try this you will get the error:
LOBS are not allowed to be hashed
When this option is specified, AQT will amend your query SQL to include the TOP x clause, where x is the Max Rows Displayed value.
For example, if Max Rows Displayed is 1000 and you code:
select * from table
AQT will amend this to:
select top 1000 * from table
For Oracle, use TO_CHAR with NUMERIC cols defined with no precision/scaleThis option is used to fix a problem with the Oracle ODBC Driver.
A column may be defined as NUMERIC or DECIMAL, without a precision or scale being specified. The ODBC Driver treats these the same as a Floating Point data type, the result being that the precision is limited to 16 digits. Values with more than 16 digits precision will not be displayed correctly.
The solution is to use TO_CHAR with these columns. When this option is used, AQT will change the default query for the table from:
select * from table
to
select col1, col2, to_char(numeric_col) as numeric_col, col4 from table
The data will then display correctly.
If you run a query without using to_char, eg.
select col1, numeric_col from table
the data may not display correctly.
It is recommended that this option is selected. You should only de-select this in special circumstances, such as using the DB2 z/OS Resource Limit Facility.
This option will have no effect if you specify Use SQLExecDirect.
A technical description of this parameter is as follows. AQT runs a query by doing a Prepare then an Execute. The Prepare sends the query to the database for parsing; the Execute actually runs the query. To minimize the interaction with the database, many ODBC Drivers do not pass the Prepare to the database, but wait till the Execute is done; it then passes both the Prepare and Execute to the database in one interaction. This is known as Deferred Prepare. However, there are some occasions when you actually want the Prepare to be sent to the database; in order to do this one needs to disable Deferred Prepare. Functions which this applies to are:
We have sometimes found that Oracle gives an error when your SQL contains carriage-return + linefeed as your newline character. To deal with this, AQT replaces all occurrences of carriage-return + linefeed in your SQL with a linefeed.
This option is on be default. If you do not want AQT to do this replacement, de-select this option.
This specifies the amount of time a query can run before it is automatically canceled by the database. A value of zero means there is no query timeout and queries will run until either they complete or are canceled.
In general we do not recommend the use of this as it can interfere with the normal running of queries. However some databases have an inbuilt query timeout which can have a lower value than required. By specifying this option, you can increase the query timeout to a higher value than this default.
This option is implemented by AQT setting the QueryTimeout attribute on queries it runs against the database. Some databases not recognize this attribute; consequently for these databases this option will have no effect. Other databases vary in the degree to which they cancel long-running queries; we cannot guarantee how well this feature will work.
This specifies the Windows Locale to be used for the AQT session. By default this is blank, so AQT runs under the default Locale for the machine.
This value should only be changed if you are using a Locale that is causing problems with AQT. This can happen if the locale / language has non-ascii characters that causes problems with some functions of AQT.
Locale must be set to a valid locale - for instance en-US (for US english).
AQT must be restarted for this option to come into effect.
AQT has the ability to automatically reconnect to your database when it notices that the connection has been closed.
When you run a query and it returns an error message indicating that the connection has been closed, AQT will:
This option controls the reconnect behaviour:
For this feature to work, AQT needs to detect that an error message indicates that the connection has been closed. Every database has different error messages indicating this. Consequently, AQT needs to be configured for your database with these error codes. This has been done for all the major databases; if auto-reconnect is not working for your database please let us know and we'll configure it for your database.