Load Ingest Data
Results 1 to 10 of 10

Thread: Using the firstTreeExtensions parameter

  1. #1
    Boaz Newman is offline Support
    Join Date
    Aug 2006
    Posts
    30
    Rep Power
    0

    Using the firstTreeExtensions parameter

    Introduction:
    The parameter "firstTreeExtensions" appears in the environment section of a binding, under the queryProcessor category.
    This parameter is used by the query processor to determine the size of an internal buffer used for a single query.
    The defualt size of this parameter is set to 150 measured in units of kilobytes.
    The actual size of the buffer used for a single query can be larger than the defined size, for example, if a query contains 3 sub queries than the size used will be 600kb, 150 for the main query and 150*3=450kb for the 3 sub-queries.
    The actual size (determined by this parameter) used by a single attunity server can be larger if the server is having a few queries open simultaneously.

    Handling errors:
    There are 2 types of errors involved with this parameter:
    1) The paremeter is too small:
    The error message typically seen in the server log, is:
    "[A048] The execution tree of this sql text is too big. Try to increase the variable firstTreeExtensions' in your environment file."
    AIS is trying to use the value of the parameter as set in the environment, if it fails, than it tries with a double of the value, and if it fails again then it tries again after doubling the value one more time. All together there are 3 tries.
    For example if the value is set to 150 than there will be a maximum of 3 tries of execution with the values 150kb, 300kb and 600kb. Each failure of execution will create one error message in the log file, if there are 1 or 2 error messages in the log, it means that the third value was sufficient. In this case it is a good practice to increase the value of the parameter to avoid the time consuming retries made by AIS, if the error appears once, then double the value, and if it occurs twice then multiply the value by 4. If the error message appears 3 times, it means that 3 attempts failed and the parameter must be increased to allow for a successful execution of the statement involved.
    2) The parameter is too big:
    Increasing the size of this parameter too much will cause high memory consumption even to a point where memory allocation errors appear in the server log, without any indication that the value of this parameter is causing the problem. In that case the firstTreeExtensions parameter is one of the first things to check.
    Last edited by Boaz Newman; 09-06-2007 at 06:40 AM.
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  2. #2
    Yossi Shitrit is offline US Technical Team
    Join Date
    Sep 2006
    Posts
    38
    Rep Power
    0

    Additional Information

    The same behavior of automatic multiplying the parameter value is valid also for these two query processor parameters:
    1. parserDepth (defaults to 500)
    2. maxColumnsInParsing (defaults to 500)

    For best performance, if the log file contains errors regarding these parameters, it is recommended to enlarge these values even if the query succeeded.
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    i5mail is offline Junior Member
    Join Date
    May 2012
    Posts
    8
    Rep Power
    0

    firstTreeExtensions Problem

    Hi,

    I am working on Attunity (Connect) Server and Attunity (Connect) Studio and I have a problem about firstTreeExtensions.

    First of all, I want to describe my environment;

    Attunity (Connect) Server on IBM z/OS to connect VSAM and QSAM Datasets.
    Oracle10g (32 bit) Enterprise Edition on Windows 2008.
    I am using HSODBC to connect Attunity (Connect) Server.

    When I was working with Vsam, everything worked properly.

    But there was a proplem about connecting QSAM files.I got the error "[A048] The execution tree of this sql text is too big. Try to increase the variable firstTreeExtensions in your environment file".

    The difference between VSAM and QSAM, QSAM has too many columns (approximately 1500 columns).Select query is very simple like "select * from table1".

    So, I read your post and increased the firstTreeExtensions parameter in init[MyConn].ora step by step.
    Every time I am stoping and starting the listener again, and try to connect but there is no change.
    Now, firstTreeExtensions parameter's value is 100000 and still getting the error [A048].

    Do you have any suggestion about this situation?

    Thanks for your help.

    Regards,

    ismail









    ame behavior of automatic multiplying the parameter value is valid also for these two query processor parameters:
    1. parserDepth (defaults to 500)
    2. maxColumnsInParsing (defaults to 500)

    For best performance, if the log file contains errors regarding these parameters, it is recommended to enlarge these values even if the query succeeded.[/QUOTE]

  4. #4
    Yossi Shitrit is offline US Technical Team
    Join Date
    Sep 2006
    Posts
    38
    Rep Power
    0
    Hi Ismail,

    Have you tried also increasing the maxColumnsInParsing parameter?
    Can you post your init file so I can verify it looks ok?

    Thanks,
    Yossi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  5. #5
    i5mail is offline Junior Member
    Join Date
    May 2012
    Posts
    8
    Rep Power
    0
    Hi Yossi,

    I have tried maxColumnsInParsing parameter but nothing changed.

    I have noticed that ; When I was trying to select QSAM data like this from sqlplus "select * from qsam_table@db_link;" , it's working properly. But If I want to select like this - "select * from qsam_table @ db_link where rownum<10; " or "select * from qsam_table @ db_link where field1='A'; ".I got the same A048 error.

    I am sending my initOra file Below.

    Thanks for your help,

    ismail

    -----------------------------------------------

    HS_FDS_CONNECT_INFO=REMOTE
    HS_FDS_CONNECT_STRING="<NAVOBJ><BINDING><ENVIRONME NT>
    <QUERYPROCESSOR firstTreeExtensions='2000' maxColumnsInParsing='2000' parserDepth = '2000' ></QUERYPROCESSOR>
    </ENVIRONMENT><DATASOURCES><DATASOURCE type='REMOTE' connect='xxx.xxx.xxx.xxx' name='STACT06' /></DATASOURCES><REMOTEMACHINES><REMOTEMACHINE name='xxx.xxx.xxx.xxx' workspace='NAVIGATOR' port='2551' address='xxx.xxx.xxx.xxx' /></REMOTEMACHINES></BINDING></NAVOBJ>"

  6. #6
    Yossi Shitrit is offline US Technical Team
    Join Date
    Sep 2006
    Posts
    38
    Rep Power
    0
    Hi Ismail,

    Check the Attunity Server log on the z/OS machine and see if the error is coming from there.
    If yes, you may need to set the same properties also on the z/OS side.

    Thanks,
    Yossi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  7. #7
    i5mail is offline Junior Member
    Join Date
    May 2012
    Posts
    8
    Rep Power
    0
    Hi Yossi,

    We checked the log on z/OS machine, there is not an error about this problem.

    I think, error occurs at Oracle side.

    Do you have any other suggestion about this problem ?

    Regards,

    ismail
    Last edited by i5mail; 05-30-2012 at 08:17 AM.

  8. #8
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    Hi Ismail,

    On z/OS, the Attunity Server log is written to the SYSPRINT.


    Regards,

    Gadi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  9. #9
    i5mail is offline Junior Member
    Join Date
    May 2012
    Posts
    8
    Rep Power
    0
    Hi Yossi,

    We have checked SYSPRINT on z/OS machine again, there is not an error about this problem.

    I attach the SYSPRINT result, below.

    I think, error occurs at Oracle side.

    Do you have any other suggestion about this problem ?

    Regards,

    ismail

    ------------------------------------------------------------------------------

    2012-05-30T11:31:55.707810 ; Optim Connect Server Log (V5.3.2.10, MVS 32-bit) S
    2012-05-30T11:31:55.708609 ; Reusable server for workspace NAVIGATOR (PID UNKNO
    2012-05-30T11:31:55.708618 ; Using binding 'NAV', workspace database ''
    2012-05-30T11:31:55.708623 ; Temporary license issued by IBM on 09-Mar-2012 (13
    icensed to OptimDemo for *,* on <all machines> (<all platforms>)
    unning on host , IP address 172.31.5.2
    2012-05-30T11:31:55.715842 ; Info: Language tables for 'tur' had been read
    2012-05-30T11:32:34.156535 ; Client 172.21.131.193:23759 (No. 1, client ID 3190
    2012-05-30T11:32:34.165781 ; Client 172.21.131.193:23759 (id is 31901) has dis
    2012-05-30T11:32:34.173359 ; Client 172.21.131.193:23761 (No. 2, client ID 1615
    2012-05-30T11:32:34.182068 ; Client 172.21.131.193:23761 (id is 16156) has dis
    2012-05-30T11:33:28.285786 ; Client 172.21.131.193:23777 (No. 3, client ID 2431
    2012-05-30T14:52:34.842378 ; Client 172.21.131.193:23777 (id is 24319) has dis
    2012-05-30T14:52:59.518877 ; Client 172.21.131.193:24121 (No. 4, client ID 9345
    2012-05-30T14:55:47.706642 ; Client 172.21.131.193:24121 (id is 9345) has disc
    2012-05-30T14:56:31.922959 ; Client 172.21.131.193:24125 (No. 5, client ID 2702
    2012-05-30T15:01:36.612326 ; Client 172.21.131.193:24125 (id is 27028) has dis

    g (V5.3.2.10, MVS 32-bit) Started at 2012-05-30T11:31:55
    kspace NAVIGATOR (PID UNKNOWN)
    rkspace database ''
    d by IBM on 09-Mar-2012 (1331305840593)
    platforms>)
    .31.5.2
    or 'tur' had been read
    3759 (No. 1, client ID 31901) has connected
    3759 (id is 31901) has disconnected
    3761 (No. 2, client ID 16156) has connected
    3761 (id is 16156) has disconnected
    3777 (No. 3, client ID 24319) has connected
    3777 (id is 24319) has disconnected
    4121 (No. 4, client ID 9345) has connected
    4121 (id is 9345) has disconnected
    4125 (No. 5, client ID 27028) has connected
    4125 (id is 27028) has disconnected

  10. #10
    i5mail is offline Junior Member
    Join Date
    May 2012
    Posts
    8
    Rep Power
    0
    Hi,

    I have solved my problem.

    Thanks for your support,

    Regards,

    ismail

    Quote Originally Posted by i5mail View Post
    Hi Yossi,

    We have checked SYSPRINT on z/OS machine again, there is not an error about this problem.

    I attach the SYSPRINT result, below.

    I think, error occurs at Oracle side.

    Do you have any other suggestion about this problem ?

    Regards,

    ismail

    ------------------------------------------------------------------------------

    2012-05-30T11:31:55.707810 ; Optim Connect Server Log (V5.3.2.10, MVS 32-bit) S
    2012-05-30T11:31:55.708609 ; Reusable server for workspace NAVIGATOR (PID UNKNO
    2012-05-30T11:31:55.708618 ; Using binding 'NAV', workspace database ''
    2012-05-30T11:31:55.708623 ; Temporary license issued by IBM on 09-Mar-2012 (13
    icensed to OptimDemo for *,* on <all machines> (<all platforms>)
    unning on host , IP address 172.31.5.2
    2012-05-30T11:31:55.715842 ; Info: Language tables for 'tur' had been read
    2012-05-30T11:32:34.156535 ; Client 172.21.131.193:23759 (No. 1, client ID 3190
    2012-05-30T11:32:34.165781 ; Client 172.21.131.193:23759 (id is 31901) has dis
    2012-05-30T11:32:34.173359 ; Client 172.21.131.193:23761 (No. 2, client ID 1615
    2012-05-30T11:32:34.182068 ; Client 172.21.131.193:23761 (id is 16156) has dis
    2012-05-30T11:33:28.285786 ; Client 172.21.131.193:23777 (No. 3, client ID 2431
    2012-05-30T14:52:34.842378 ; Client 172.21.131.193:23777 (id is 24319) has dis
    2012-05-30T14:52:59.518877 ; Client 172.21.131.193:24121 (No. 4, client ID 9345
    2012-05-30T14:55:47.706642 ; Client 172.21.131.193:24121 (id is 9345) has disc
    2012-05-30T14:56:31.922959 ; Client 172.21.131.193:24125 (No. 5, client ID 2702
    2012-05-30T15:01:36.612326 ; Client 172.21.131.193:24125 (id is 27028) has dis

    g (V5.3.2.10, MVS 32-bit) Started at 2012-05-30T11:31:55
    kspace NAVIGATOR (PID UNKNOWN)
    rkspace database ''
    d by IBM on 09-Mar-2012 (1331305840593)
    platforms>)
    .31.5.2
    or 'tur' had been read
    3759 (No. 1, client ID 31901) has connected
    3759 (id is 31901) has disconnected
    3761 (No. 2, client ID 16156) has connected
    3761 (id is 16156) has disconnected
    3777 (No. 3, client ID 24319) has connected
    3777 (id is 24319) has disconnected
    4121 (No. 4, client ID 9345) has connected
    4121 (id is 9345) has disconnected
    4125 (No. 5, client ID 27028) has connected
    4125 (id is 27028) has disconnected

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •