Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2009
    Posts
    22

    Unanswered:

    Hi,
    I am using Informix 10. We have created 10 temporary dbspaces. whenever i am running a query with left outer join, it is using only one temporary dbspaces then it is coming out with error. "Could not write to temporary file".

    Remaining 9 dbspaces was absolutely free. why the query is returing with error?

    Please reply me ASAP.

    Thanks in Advance,
    Senthil Kumar R V

  2. #2
    Join Date
    Feb 2009
    Posts
    51
    Quote Originally Posted by rvsenthil
    Hi,
    I am using Informix 10. We have created 10 temporary dbspaces. whenever i am running a query with left outer join, it is using only one temporary dbspaces then it is coming out with error. "Could not write to temporary file".

    Remaining 9 dbspaces was absolutely free. why the query is returing with error?

    Please reply me ASAP.

    Thanks in Advance,
    Senthil Kumar R V
    Please, post output
    onstat -d
    and
    cat onconfig.<your_servername> | grep DBSPACETEMP

  3. #3
    Join Date
    Mar 2009
    Posts
    22
    I am copied only root and temporary dbspace.
    Onstat -d Output:-

    c00000017d388e90 1 0x60001 1 1 2048 N B informix ddj_rootdbs
    c000000180173c00 2 0x40001 2 2 2048 N B informix ddj_logdbs1
    c000000180173da0 3 0x40001 4 2 2048 N B informix ddj_logdbs2
    c000000180176030 4 0x40001 6 1 2048 N B informix ddj_physdbs
    c0000001801761d0 5 0x42001 7 1 2048 N TB informix ddj_temp_dbs1
    c000000180176370 6 0x42001 8 1 2048 N TB informix ddj_temp_dbs2
    c000000180176510 7 0x42001 9 1 2048 N TB informix ddj_temp_dbs3
    c0000001801766b0 8 0x42001 10 1 2048 N TB informix ddj_temp_dbs4
    c000000180176850 9 0x42001 11 1 2048 N TB informix ddj_temp_dbs5
    c0000001801769f0 10 0x42001 12 1 2048 N TB informix ddj_temp_dbs6
    c000000180176b90 12 0x42001 14 1 2048 N TB informix ddj_temp_dbs8
    c000000180176d30 13 0x42001 15 1 2048 N TB informix ddj_temp_dbs9

    c000000180156d10 7 5 0 5242880 5124955 PO-B /opt/informix/ddj_temp_dbs1.1
    c000000180157030 8 6 0 5242880 5150083 PO-B /opt/informix/ddj_temp_dbs2.1
    c0000001801571e0 9 7 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs3.1
    c000000180157390 10 8 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs4.1
    c000000180157540 11 9 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs5.1
    c0000001801576f0 12 10 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs6.1
    c0000001801578a0 14 12 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs8.1
    c000000180157a50 15 13 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs9.1


    Onconfig for DBSPACETEMP:-

    DBSPACETEMP ddj_temp_dbs1,ddj_temp_dbs2,ddj_temp_dbs3,ddj_temp _dbs4,ddj_temp_dbs5,ddj_temp_dbs6,ddj_temp_dbs7,dd j_temp_dbs8,ddj_temp_dbs9,ddj_temp_dbs10

    Thanks for your reply,

    Senthil Kumar
    Last edited by rvsenthil; 03-31-09 at 13:43.

  4. #4
    Join Date
    Feb 2009
    Posts
    51
    1. Do you reboot server when you create temp dbspace?
    2. Post output env | grep TEMP.
    3. Which number of ISAM error when you get error "Could not write to temporary file"?
    4. Post onstat -d when you will get "Could not write to temporary file".
    5. Please post your onconfig.

  5. #5
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    I think your string of dbspaces should be a colon separated list, not comma separated. I have this comment line in my onconfig, OS is AIX:

    "OnLine equivalent of DBTEMP for SE. This is the list of dbspaces that the
    OnLine SQL Engine will use to create temp tables etc. If specified, it must be a colon separted list of dbspaces that exist when the OnLine system is brought online. If not specified, or if all dbspaces specified are invalid, various ad hoc queries will create temporary files in /tmp instead."

  6. #6
    Join Date
    Mar 2009
    Posts
    22
    My sincere thanks to all of you ...Here the output which you requested;

    1. Do you reboot server when you create temp dbspace?
    Yes, I have rebooted the server.
    2. Post output env | grep TEMP.
    No Reults. Nothing has been assigned. But DBTEMP is pointing to /tmp
    3. Which number of ISAM error when you get error "Could not write to temporary file"?
    264: Could not write to a temporary file.
    131: ISAM error: no free disk space
    Query has run for around 30 minutes

    4. Post onstat -d when you will get "Could not write to temporary file".
    Status of TEMPORARY CHUNK before running Query:-

    c00000017d389030 1 1 0 1048576 1044295 PO-B /opt/informix/ddj_rootdbs.1
    c000000180156d10 7 5 0 5242880 5124955 PO-B /opt/informix/ddj_temp_dbs1.1
    c000000180157030 8 6 0 5242880 5150083 PO-B /opt/informix/ddj_temp_dbs2.1
    c0000001801571e0 9 7 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs3.1
    c000000180157390 10 8 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs4.1
    c000000180157540 11 9 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs5.1
    c0000001801576f0 12 10 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs6.1
    c0000001801578a0 14 12 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs8.1
    c000000180157a50 15 13 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs9.1
    c00000018015a540 39 36 0 256000 255947 PO-B /opt/informix/ddj_ntemp_dbs10.1
    c00000018016a8a0 131 120 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs10_1

    Status of TEMPORARY CHUNK After running Query:-(When it throws Error)
    c00000017d389030 1 1 0 1048576 1044295 PO-B /opt/informix/devlinks/10/ddj_rootdbs.1
    c000000180156d10 7 5 0 5242880 475 PO-B /opt/informix/ddj_temp_dbs1.1
    c000000180157030 8 6 0 5242880 5150083 PO-B /opt/informix/ddj_temp_dbs2.1
    c0000001801571e0 9 7 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs3.1
    c000000180157390 10 8 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs4.1
    c000000180157540 11 9 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs5.1
    c0000001801576f0 12 10 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs6.1
    c0000001801578a0 14 12 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs8.1
    c000000180157a50 15 13 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs9.1
    c00000018015a540 39 36 0 256000 255947 PO-B /opt/informix/ddj_ntemp_dbs10.1
    c00000018016a8a0 131 120 0 256000 255947 PO-B /opt/informix/ddj_temp_dbs10_1


    5. Please post your onconfig.

    onconfig has been attached. File Name is onconfig.txt

    In Informix Document, we can use comma or colon to separate dbspaces name in DBSPACETEMP parameter.
    Any way, i will try to do those changes and check the output.
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2009
    Posts
    51
    I think that your server uses /tmp for temporary tables.
    I think that /tmp is full when you get error.
    Analyse it.
    Try to remove DBTEMP variable.

    And... from documentation.
    Important:
    The dbspaces that you list in the DBSPACETEMP configuration parameter must consist of chunks that are allocated as raw UNIX devices. On Windows, you can create temporary dbspaces in NTFS files.

    See here.

  8. #8
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    This could be nothing but your have a ddj_temp_dbs10 in your onconfig file and I don't see that dbspace included in the onstat -d output.

    Your onconfig includes the DBSPACETEMP comment that the list of dbspaces for DBSPACETEMP should be separated by colons, perhaps thats why it uses the first dbspace (because ddj_temp_dbs1 is first in the list and is valid) but ignores the rest
    Last edited by mjldba; 04-01-09 at 09:30.

  9. #9
    Join Date
    Mar 2009
    Posts
    22
    I have changed the parameter seggregation from comma to colon and the same error is coming. i am checking the /tmp directory. It is not getting filled up by the execution of the query.

    If i create a temp table with no log, it is using all the temporary tablespaces.
    Still, it is a mystery for us for that query...

  10. #10
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    Did you bounce the instance after changing from commas to colons? I don't know if that parameter is dynamic (like changing ltape) but I doubt that it is.

  11. #11
    Join Date
    Mar 2009
    Posts
    22
    After changing, I have restarted the informix server.

  12. #12
    Join Date
    Sep 2008
    Posts
    33
    Hi,

    Did u check the file system.......Is it free

  13. #13
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    First, probably your session are using only one DBSPACE because the SQL create a implicit temp table , and you don't have a PDQ priority active.
    Before execute your SQL, in the same session, execute:
    Code:
    set pdqpriority 1;
    Copied from Performance Guide:
    Use of parallel sorts into the temporary files (to process query clauses such as ORDER BY or GROUP BY, or to sort index keys when you execute CREATE
    INDEX) when you specify more than one dbspace for temporary tables and
    PDQ priority is set to greater than 0.

    To get the problem exactly at the time they occur , execute the commands below.
    But, if this is production server, be careful with this suggestion because they can "freeze" temporarily your server :

    1) disable any Shared memory Dump: parameters DUMP* on your onconfig and restart the server
    2) add a trap : onmode -I 264
    See this documentation
    How to trap an Informix error to collect additional information for diagnostics
    3) execute the SQL and wait for the error.
    4) After the error, disable the trap : onmode -I
    5) look your log (onstat -m) , a AF file is created with a picture at the moment of the error, this way you can see exactly what dbspace are full.
    If have doubt to identify the problem, copy the AF file here...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  14. #14
    Join Date
    Mar 2009
    Posts
    22
    This problem got solved. Earlier I used Postjoin in the query then I have changed the query from ansi method. It is working fine.

    Earlier view query written like this:-

    ("informix".ddj_task x1 left join "informix".user x2 on (x1.username = x2.username ) )

    New View query:-

    "informix".ddj_task x1, outer "informix".user x2
    where (x1.username = x2.username )

    Once changed the query, It has been solved. It is using all temporary dbspaces.

    Thanks,
    Senthil

  15. #15
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Funny,
    The last week I tunning a query from a developer exactly on the same situation, they use ANSI SQL with a lot of LEFT OUTER JOIN and the performance are terrible returning in 3-4 minutes (but don't have problem with temp space), the database execute a lot of sequencial scans , after change to non-ansi the query returns in 2 seconds.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

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