Previous Topic

Next Topic

Book Contents

Book Index

DB2

Uncommitted Read

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.

More on Uncommitted Read

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).

Optimize Clause

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.

With UR

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.

Displaying LOBs

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.

EBCDIC-ASCII Conversion

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:

Collation Order for DB2 z/OS

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.

Resource Limit Warning for DB2 z/OS

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:

Limitation using the Merant ODBC Driver

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

Limitation using the NEON ODBC Driver

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.

Updating DB2 for iSeries Data

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.

Stored Procedure - Table Dependencies

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.

Using *SYS Naming Convention with DB2 for iSeries

AQT supports the use of the System Naming Convention (*SYS). For more on this, see Options > Table Information.

Advanced Query Tool
https://www.querytool.com
© 2023 Cardett Associates Ltd. All rights reserved.