The SQL Tab

This tab provides a means of setting defaults for various SQL operational parameters, including compatibility and sort sequence defaults.

The SQL tab contains the following settings:

SQL Compatibility Mode Used to set the SQL dialect that XDB Server uses. XDB Server permits you to specify which SQL implementation rules are to be enforced. Options are:
  • XDB
  • DB2
  • ANSI

The XDB mode option has extensions not found in DB2 SQL.

If you are running against an XDB Link gateway to access data on a mainframe or other database system, be sure to select the correct SQL Compatibility Mode. When SQL statements do not provide the results you expect, you should always check your SQL mode to make certain it is correct for your application.

Escape Character In many Micro Focus utilities and client applications, you can use an escape character before some special keys to instruct the system to treat the character as a literal. The default escape character is a backward quote ( ` ). You can redefine the key used as the escape character. For example, to insert a question mark into the database from the Data Entry utility (OS/2), you would type `?. You can also use the escape character in front of % or _ if you want these characters interpreted as literals rather than as pattern matching characters.
Note:

In Micro Focus documentation, the key you select for the escape character is referred to as <EscChar>. For example, the default <EscChar> = `
Embedded COBOL

Specify how you want to treat quoting characters around literals and SQL identifiers (such as column names and table names) when using precompiled COBOL with Embedded SQL.

  DEFAULT Behavior of quoting characters is determined by usage.
  APOST Apostrophe characters are used to quote string literals (and quotation marks are used as escape characters for SQL identifiers)
  QUOTE Quotation marks are used to quote string literals (and apostrophe characters are used as escape characters for SQL identifiers)
  By default, SQL Option for DB2 tries to determine the behavior of quoting characters (apostrophe or quotation mark) based upon their usage. In the default mode, either an apostrophe or quotation mark can be used as the string delimiter. If the usage of either quoting character is ambiguous, SQL Option for DB2 assumes that the quoting character is used as a string delimiter surrounding a string literal. Ambiguity typically arises when a one-part object name is quoted and used where either object-names or string literals can be present, as shown in the following example: 
SELECT D.'IBMREQD', 'IBMREQD' FROM SYSIBM.SYSDUMMY1 AS D;

In this query, SQL Option for DB2 assumes that the first set of apostrophe characters are being used as SQL escape characters and they surround a column name. This assumption is made because of the two-part dot notation. However, the second set of apostrophe characters are assumed to be string delimiters and the second IBMREQD is assumed to be a literal string.

This can lead to problems when one-part names for database objects are quoted and their usage is not explicit in the statement. For instance, a string literal cannot legally be used in the FROM clause of an SQL statement, so quoting characters in the FROM clause are always considered as escape characters surrounding SQL identifiers. However, as both string literals and SQL identifiers can be present in a SELECT clause, the usage can be ambiguous.

To illustrate this, the following query produces three different results in the three different modes:

SELECT 'IBMREQD', "IBMREQD" FROM SYSIBM.SYSDUMMY1

When using the default, the query returns the following:

-----------------
|IBMREQD|IBMREQD|
-----------------

When using APOST, the query returns the following:

-----------------
|IBMREQD|N | 
-----------------

When using QUOTE, the query returns the following:

----------------- 
|N |IBMREQD| 
-----------------
Default Sort Sequence Choose the default sort sequence to use when creating a location.
Note:

After you define a user defined sort sequence and use it for one or more locations, do not re-define it. The locations using the original sort sequence are likely to have index and other problems.

The sort sequence options are as follows. The first three are standard computer sort sequences.
  Case Insensitive ASCII (A=a, B=b, etc.)
  ASCII Standard ASCII sort
  EBCDIC Sensitive Standard EBCDIC sort where null values sort low
  User Defined If you select USER DEFINED, you must specify a sort sequence file that is named with the extension .SEQ. This file is used as the sort sequence for any location that is created with a user defined sort sequence.

To operate with a user-defined sort sequence, you must create a sort sequence file (use any ASCII text editor). The file can be up to 256 lines. Each line represents a rank in the sort sequence. On each line, enter an ASCII decimal code or a character, enclosed in double quotes. (See the US ASCII Table for a list of ASCII decimal codes.) If a rank is shared by multiple characters, enter them on the same line, separated by commas.

Below is a partial sort sequence file. In this example, the decimal code 0 (null) appears on line 1, and is the first element in the sort sequence. The letter A appears on line 2, and is therefore assigned rank 2. Because both upper and lower case A appear on the same line, A is not case-sensitive. The same is true of B, C, etc. The letter E was entered using ASCII decimal codes (69='E' and 101='e'). ASCII decimal codes and characters in quotes can be used interchangeably.

  • 0
  • "A","a"
  • "B","b"
  • "C","c"
  • "D","d"
  • 69,101
  • ...

If characters are missing from your sort sequence file, the missing characters will be treated as null by the system. Save the file with the name of your choosing, and the extension .SEQ.

  Classic EBCDIC Standard EBCDIC sort where null values sort high. This mimics the mainframe sort sequence.
  Language EBCDIC Includes French EBCDIC, Spanish EBCDIC, Italian EBCDIC, UK EBCDIC, Danish (Norwegian) EBCDIC, Swedish (Finnish) EBCDIC, German (Austrian) EBCDIC, US (Portuguese, Dutch) EBCDIC, International EBCDIC, and Turkish EBCDIC. Each sort sequence is the same as the corresponding character set, with null values sorting high.
SysAuthID These items allow SQLWizard to find the system catalog tables that contain the information to be displayed in the Catalog Browser.
SysXDB Specify the AuthID to be used when issuing queries against system catalog tables owned by SYSXDB. Often you will only have privileges on a view on the system catalog tables. Usually the view is created using the same table name, but a different AuthID than the underlying table. When running against XDB-Link to DB2, you will usually use SYSXDB=XDB instead of SYSXDB=SYSXDB.

If the SYSXDB= setting is incorrect and you get the message:

ISQL043: Cannot access SYSIBM tables using AuthID authid.

then you may not have privileges on the catalog tables. SQLWizard provides script files that grant access to the catalog tables. These scripts must be run by a Database Administrator, or by someone who has SYSADM authority. See your Setup Guide for information about these script files.

Parent topic: Options Utility