|
|
|
|
|
It is recommended that you select Options > Technical Parameters > Uncommitted Read. This is particularly important if you are accessing a production DB2 environment.
When this option is set, AQT's queries will run even if a table is being updated by another DB2 process. AQT will not interfere with any other DB2 activity.
Uncommitted Read is also known as the Program Isolation.
Normally, when AQT runs a query, the DB2 Client will use package NULLID.SYSSH200, which has been bound with a Program Isolation of CS. When you select Uncommitted Read, AQT will set the SQL_TXN_ISOLATION attribute of the database connection to SQL_TXN_READ_UNCOMMITTED. DB2 will then run AQT's queries using package NULLID.SYSSH100 - this has been bound as UR.
When running in this mode, some locks are still taken, however AQT will not interfere with other updating / deleting activity within DB2.
Alternatively, you can:
If you do either of these, AQT will will not take any locks, or interfere with any other DB2 activity.
Note that the For Read Only clause doesn't have any effect on locking. This clause is used for performance reasons (it encourages DB2 to use block fetching).
When you run a query, the DB2 ODBC Driver will automatically add the following clause to the end of your SQL:
Fetch First x Rows Only Optimize For x Rows
where x is a very high value.
Because the value of x is high, this can result in poor performance. This is because DB2 uses the value of x to optimise the performance of the SQL and the network traffic; this unrealistically high value of x will cause DB2 to make poor optimization choices.
There appears to be no way of preventing the DB2 ODBC Driver from adding this, however AQT has a circumvention to this issue. If you specify Options > Technical Parameters > For DB2, add FETCH FIRST x ROWS clause, AQT will add its own optimize clause to your SQL. When this is done, DB2 will not add its optimize clause. The optimize clause that AQT adds is better than the DB2 one, as it has a sensible value for x (the value specified for Options > Display Limits > Max Rows Displayed). This can result in a significant performance improvement for some DB2 systems.
If you are using v7 of the DB2 Client, specifying the With UR (Uncommitted Read) clause at the end of your SQL will give you a syntax error. This is because DB2 and/or AQT will have added the optimize clause at the end of your SQL; this is invalid syntax as the With UR needs to at the end of the SQL.
To circumvent this, code your own optimize clause. When you do this, DB2/AQT will then not add their own. Example:
select * from department fetch first 100 rows only optimize for 100 rows with ur
Note that you do not actually need to code a With UR clause; specifying the Uncommitted Read option (as discussed above) will run all your queries in uncommitted read mode, which does the same thing.
This problem has been correct in v8 of the DB2 Client.
In order to display LOBs correctly, make sure you don't have the client setting LONGDATACOMPAT set. When this option is set, LOBs are reported to AQT as LONG data types - AQT will not recognise the columns as LOBs so may not display them in full.
In general, character data held in DB2 z/OS is encoded in EBCDIC format. AQT (along with all PC-based products) use ASCII encoding.
When AQT reads data from DB2 z/OS, the conversion from EBCDIC to ASCII is done by DB2/Connect. However, if you have defined your character data as FOR BIT DATA, the column is regarded as binary data, so no conversion is done by DB2/Connect. This means that if you have any normal text in these columns, the data will come to AQT in Ebcdic format, so will be unreadable.
This is a particular problem with DB2 z/OS v8, as in this version a number of columns in the catalog tables have changed from VARCHAR to VARCHAR FOR BIT DATA. To circumvent this problem:
Because DB2 z/OS (normally) uses EBCDIC, the collation order is different than that of non-mainframe databases (such as DB2/UDB). For example, if you run the following query:
Select name from sysibm.systables where creator = ? order by 1
Then
This is because, in EBCDIC, 1 is "lower" than A, whereas, in ASCII, A is "lower" than 1.
In most cases this is not a problem. However the exception is when you are using the Compare Objects function, and you are comparing a DB2 z/OS database to a non-mainframe system. When you use the Compare all objects in schema function, AQT runs a query against each system to get the list of tables in the schemas. Because of the difference in collation, the lists of tables will be returned to AQT in different orders. This will lead AQT to think that some of the tables are not present in the other system, when in fact they are.
At this stage, we do not have a resolution to this problem.
DB2 z/OS has a feature called the Resource Limit Facility (RLF). With this, you can configure two limits:
AQT supports the warning threshold. When it receives a +495 code (actually SQLState 01616) to the Prepare, AQT will prompt the user whether or not to proceed with the query. However in order to use this feature of AQT you must disable Deferred Prepare. This is done from Options > Technical Parameters:
If you are using this ODBC Driver, you may get the following message while using the Database Explorer against DB2 z/OS.
DSNT408I = -301, ERROR: THE VALUE OF INPUT HOST VARIABLE OR PARAMETER NUMER 001 CANNOT BE USED AS SPECIFIED BECAUSE OF ITS DATA TYPE
This is due to a limitation or bug with the way the Merant ODBC Driver processes parameterised queries.
This problem can be circumvented by setting Options > Table Information; for Parameter Usage select Use Substitution
This ODBC Driver appears to not support Unicode. You will not be able to display any information about your database (or even run any queries) unless you switch off Unicode mode. This is done with Options > Unicode Options, deselect Enable Unicode features.
When updating a DB2 for iSeries table you can get the error message: error SQL7008 -(table) in (file) not valid for the operation. This occurs because the table you are trying to update is not being journalled and AQT, by default, runs update of under commitment control.
To circumvent this problem, you need to switch off Safe Update Mode. When this is switched off, AQT will not run updates under commitment control.
The Database Explorer has functions Dependent Objects and Dependents of Dependent Packages that make it easy to see which tables are used by Stored Procedures. You can see the reverse relationship with (when viewing a table), Dependent Routines and Dependent Compiled Routines.
However, these functions do not work well if you are using temporary tables in your stored procedure. For instance, if you have the following statement in your procedure:
insert into session.tabcounts select count(*) from Employee
then you will not see Employee in the list of dependent objects for the procedure. Due to the use of the temporary table, this statement is compiled at run-time, not when the procedure is defined. Because of this, the dependency information will not be recorded in the DB2 catalog so will not be seen by AQT.
AQT supports the use of the System Naming Convention (*SYS). For more on this, see Options > Table Information.