Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Thursday, July 14, 2011

Oracle Heterogeneous Services (HS) problem with tables having lots of columns

Today, our Oracle data warehouse team got an error while querying a table having around 650 columns in an AS/400 database by using Oracle Heterogeneous Services or HS for short(It is also called HSODBC).

HSODBC is a bundled component in Oracle database installation package. It enables yo to query any non-Oracle database tables from within an Oracle database as if the remote non-Oracle database is an Oracle database. I am thinking to publish another blog post in the future about how to setup HSODBC which I will not cover today.

In this post, I only want to talk about an error in HSODBC and its solution since I could not find so much information about the solution of this problem today. I want to help anybody who will have that same problem in their HSODBC configuration. The HSODBC version we use is Oracle 10gR2 HSODBC. HSODBC parameter in different Oracle versions are different so the following solution is especially for Oracle 10gR2 HSODBC configuration. In our case, Oracle HSODBC server is a separate Windows machine which runs only Oracle HSODBC on it.

The sql statements giving error were like below.
select col1,col2,...,col650 from owner.table_name@database_link_using_hsodbc_to_as400_db;
or the following sql statement got also the same error
select * from owner.table_name@database_link_using_hsodbc_to_as400_db;

Error :
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][A048] The execution tree of this sql text is too big. Try to increase the variable 'firstTreeExtensions' in your environment file.
ORA-02063: preceding line from database_link_using_hsodbc_to_as400_db

The above error indicates that Oracle HSODBC has an internal limitation about the length of the selected data(consisting of column values in select statement) of a table coming from the non-Oracle database. We tried to select around 520 column from that table without getting this error but after adding some more columns to this list select, we started to get this error because we already reached that internal limitation of selected row size. In another document, we found that this internal default max size limit of selected row size, you can select from the non-Oracle database, is 150 Kilobytes.

So we started to find how to increase that parameter and after trying different configurations we were able to increase the value of this parameter in the configuration and got rid of the above error.

Solution :
Just add the following line to the "init<sid_name_of_as400_db_in_listener.ora_file>.ora" file in the "$ORACLE_HOME\hs\admin" folder. With this setting we increase the limit to 1000 KB.

HS_FDS_CONNECT_STRING="<navobj><binding><environment><queryprocessor firstTreeExtensions='1000'></queryProcessor></environment></binding></navobj>"

Then we need to reload the listener in HSODBC machine, like below so that our change in ".ora" configuration will take effect.

In a windows Command Prompt window in HSODBC machine do the following to reload the listener.

lsnrctl
LSNRCTL> reload


Now you need to create a new session to the Oracle database, since the connections connected before these changes made to the listener in HSODBC server, will not be effected by this change, because of that, it is very important to create a new session to Oracle database where we will execute our sql statement querying the big table in the non-Oracle database.
Then try to query all 650 columns of this table in non-Oracle database which is As/400 in our case.

select col1,col2,...,col650 from owner.table_name@database_link_using_hsodbc_to_as400_db;

Finally, you should successfully query all the 650 columns of this table.
I hope the same solution will help to your case also.

No comments: