[Ramesh]

Hi All,

I have two questions :

1. I created a temporary DBSpace through ONMONITOR and
marked 'Y' for TEMP.

I added the same DBSPACE in ONCONFIG as DBSPACETEMP.
But the application is never using this dbspace for temporary
tables. It is using rootdbs instead. What could be the problem?

2. The second problem I have is :

The LOGS are full as given below :

INDIVIDUAL LOG FILES:
Number Flags Uniqid Dbspace Pages Used % Used
1 U-B---- 616 rootdbs 1000 1000 100.00
11 U-B---- 617 rootdbs 1000 1000 100.00
14 U-B---- 618 rootdbs 1000 1000 100.00
15 U-B---- 619 rootdbs 1000 1000 100.00

But so far this has not caused any problem to the working of the instance. Is this a problem?


Advance thanks for any inputs

Regards,
Ramesh

[Roelwe]

1; How do you know for sure that tempdbs is not used?
Did you try SET EXPLAIN? Did you check with onstat -D?
onmonitor is no longer supported by informix. If you want to create a new dbspace, you should use onspaces. Did you rebounce your instance?

2;
You are not displaying all the logical logs. What log has flag C and flag L? Do you use logged databases?

[Ramesh]

Thanks for the reply.

1. The method I used was this :
I PARALLELY was running onstat -d when the program was running.
(The program inserts approx 100000 rows into a temporary table)

Here I found out that space of rootdbs was decreasing rather than of
temp dbspace

I am working with IDS 9.3.

2. The database is a logged database.

Ramesh

[Roelwe]

1. Did you restart the engine? In order to get the DBSPACTEMP variable accepted, you need to restart the engine. Once restarted, you will get a message in the online.log that the variable has changed.
If you restarted the engine, try with "SET EXPLAIN ON;" before executing the qry. You will get the qryplan, and it should include your temp dbspace.
If you display 'onstat -d', does your temporary dbspace contain a T flag?

2. Even with logged databases, IDS 9.30 has dynamic logging. It will create new log files on the fly, but only when needed. You don't know what log contains the oldest open transaction. The C flag will show you in what log it is currently writing. The L flag will show you in what log the latest checkpoint occurred.

[Ramesh]

1. Yes I have restarted the engine, and I got the message that the variable has been changed in the log.

onstat -d does not show the T flag.

[Roelwe]

In that case you didn't create a temporary dbspace.
try to creaet one with onspaces
for the syntax use 'onspaces --'

the -t flag stands for temporary in the command

onspaces -c -d dbspace -t -p path -s sizeinbytes -o offset

[Ramesh]

The problem lies precisely here!!

I recreated the dbspace WITHOUT mentioning that it is a temporary dbspace but added this in the onconfig as DBSPACETEMP and restarted the server. THEN THE PROCESS BEGAN TO USE THE TEMP DBSPACE for creating temporary tables.

But WHEN IT WAS CREATED AS A TEMPORARY DBSPACE ( and added this as a DBSPACETEMP in ONCONFIG) , the temporary tables did NOT use this dbspace.

What have I done wrong?

[eherber]

Temporary tables will only be created in a temporary
dbspaces if they have been created with the extension
"with no log".

Temporary tables created without "with no log" will be
created in regular dbspaces like the 'rootdbs'.

HTH.

Best regards

Eric
--
IT-Consulting Herber
WWW: http://www.herber-consulting.de
Email: eric@herber-consulting.de

***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

[Ramesh]

Thankyou Roelwe and Eric. Your help has been invaluable to me.

Thanks & regards,
Ramesh