Options - Display Format

These options govern how data is to be displayed when you run a query or display a table.

Show Nulls as

This specifies how null values are displayed.

Format of Date/Time Columns

These options specify how Date, Time, DateTime and Timestamp columns are displayed. Enter a format using a combination of the letters: y (year), m (month), d (day), h (hour), n (minute) and s (second) plus any delimiting characters (such as -,: or /).

Examples:

• mm-dd-yyyy will display as 12-31-2001
• dd/mmm/yyyy will display as 31/Dec/2001
• hh.nn.ss will display as 15.32.58

Timestamps and Datetime columns display as: Date Format + " " + Time Format.

You can read a more detailed discussion about Date and Time columns.

The date format MUST include the following specifications: yyyy, mm or mmm, dd.

The time format MUST include the following specifications: hh, nn, ss.

For Oracle, the NLS_DATE_FORMAT will be changed to what you specify for the Date Format.

Format of Informix Literals

These options specify how Informix date / time literals are to be specified. This must match the format specified in the DBDATE or GLS_DATE environment variables. If AQT were smarter, it would be able to automatically pick up the DBDATE / GLS_DATE values; however it cannot do this, so this literal format need to be specified here.

These options need to be specified as described in Format of Date/Time Columns, except that the mmm specification is not valid.

These options only need to be specified for Informix. For all other databases, the format of the date / time literals are hard-coded in AQT (see Date and Time columns for more on this). For Informix, the way in which date and time literals are specified is not fixed, but depends on the DBDATE or GLS_DATE environment variables.

How to display Binary Columns

Binary Columns are columns which can contain non-ASCII data (in DB2 these are defined as CHAR FOR BIT DATA, in Oracle they are defined as RAW, in Sybase and SQL Server they are defined as BINARY). This also includes BLOB columns.

AQT can display binary data in two formats:

• ascii – the ascii representation of the data is displayed. This may well give unreadable values.
• hex – the data is displayed in hex mode. For DB2 hex data is displayed like x'44', other databases display hex data as 0x44.

The default mode for AQT is smart. In this mode, AQT will display the binary columns in either ASCII mode or hex mode depending on the contents of the column – eg. it is displayed in ASCII mode if the column contains only displayable characters, otherwise it is displayed in hex mode.

When columns are displayed in hex mode only the first 50 characters are shown.

Convert EBCDIC to ASCII

The option only applies to DB2 z/OS Binary columns. This includes character columns defined as FOR BIT DATA.

For normal character columns, Ebcdic - Ascii translation is done by DB2/Connect. However this is not done for columns defined as FOR BIT DATA, as these may contain binary information for which conversion is not required. However, if you have any normal text in these columns, they will display in AQT in Ebcdic format, and so will be unreadable.

When this option is selected, AQT will check whether binary columns contain Ebcdic data (it does this looking for Ebcdic characters in the first 20 bytes of the column). If so, the column value is converted from Ebcdic to Ascii, and so will display correctly in AQT.

Notes:

• If you wish to insert or update any Ebcdic column, you need to specify the text in Ebcdic rather than Ascii. You can convert from Ascii to Ebcdic using the Base64/Ebcdic Encoding tool (see Tools from the Database Explorer).
• the Data Compare tool will not convert data to Ascii
• the Data Loader will also not convert data to Ascii. The data will remain in Ebcdic - this enables it to be loaded correctly into another Ebcdic column.

Display x'00' characters as spaces

This option allows character columns that contain ascii 0 characters to be displayed fully in AQT. Normally, such strings are truncated at the ascii 0 character - this is because text-boxes (and other Windows components) interpret the ascii 0 as an end-of-string delimiter. To circumvent this, when this option is selected, AQT will scan all strings being displayed and replace any ascii 0 characters with spaces. This enables the full string values to be displayed.

This option also applies to data returned in OUT and INOUT parameters of Stored Procedures.

Automatically apply Layout to Queries

When you specify this option, every time you run a saved-query, AQT will check whether there is a layout-file for that query. If so, AQT will format the data as per the specifications saved in the layout-file. This is discussed in more detail in Saving / retrieving the display Layout.

Automatically apply Layout to Tables

When you specify this option, every time you display a table, AQT will check whether there is a layout-file for that table. If so, AQT will format the data as per the specifications saved in the layout-file. This is discussed in more detail in Saving / retrieving the display Layout.