Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

Executing SQL queries against OpenEdge 10.1x 64-bit database

Queries fails with errors running via JDBC or ODBC

JDBC error generated when executing query

=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-211023
Server Error -211023. No message from server.

=== SQL Exception 1 ===
SQLState=null
ErrorCode=-211023
null

ODBC error generated when executing query

[DataDirect][ODBC OPENEDGE driver]0

Problem does not affect all tables

SQL query can run correctly if amount of data returned is reduced, for example:

SELECT <field1> FROM pub.<table1>

Rather than:

SELECT * FROM pub.<table1>

SQL-Width settings for some fields is set to a high value (31955)

SQL queries run correctly when executed against OpenEdge 10.1x 32-bit database

SQL queries worked correctly with 10.0B ODBC driver

FACT(s) (Environment):

OpenEdge 10.1A
OpenEdge 10.1B
IBM AIX POWER 5.3 64-bit

CHANGE:

Upgraded from 10.0B

CAUSE:

The issue is related to the O/S data segment size for memory. The problem occurs when trying to allocate more than 256MB (the segment size limit for the process) when the number of columns referenced that have a SQLWidth of 31955.

This problem does not exist for IBM AIX 32- bit platforms because the server is linked with a maxdata setting that allows up to ~768MB per process/server. It is limited to this due to the need to attach to shared memory addresses, in our case the database buffer pool, and there is a limit of 16 segments per process.

FIX:

Option #1
Upgrade to OpenEdge 10.1C+ where the _SQLSRV2 executable can now address 4GB of heap stack because IBM AIX 64-bit provides more than 16 x 256MB segments for use. Please note that whilst this default setting can alleviate the problem, it is still possible for the limit to be reached on IBM AIX 64-bit (especially in environments with high-throughput SQL clients) when the server eventually runs out of memory. Further tuning of AIX's memory settings (i.e. via LDR_CNTRL=MAXDATA=<value>) may be required.

Option #2
Use the LDR_CNTRL environment variable to set MAXDATA prior to starting the database. This controls the process memory limit. For example, the following would set MAXDATA to its maximum setting (8GB):

export LDR_CNTRL=MAXDATA=0x80000000

MAXDATA should be tuned to a value appropriate for the server running the database. Refer to IBM AIX 64-bit documentation or contact IBM Technical Support for more information about LDR_CNTRL=MAXDATA=<value> and considerations for setting an appropriate value.

Option #3
Reduce the Fetch Array Size of the ODBC DSN to a value lower than the default (50). The Fetch Array Size setting is located on the Advanced tab.

Option #4
Lower the SQL-WIDTH setting of the fields which have very high SQL-WIDTH values (manually if few fields or programmatically if many).
Run DBTOOL against the database, selecting option #2, to automatically reset the SQL-WIDTH to a working value. This will compensate for any fields that do contains more data than the current SQL-WIDTH allows by automatically raising the SQL-WIDTH to a workable value.