Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

too many temporary tables in connection

6 posts in General Discussion Last posting was on 2008-08-29 15:12:09.0Z
Dave Westphal Posted on 2008-08-28 12:36:37.0Z
From: "Dave Westphal" <Dave_Westphal@aal.org>
Newsgroups: ianywhere.public.general
Subject: too many temporary tables in connection
Lines: 16
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48b69bd5@forums-1-dub>
Date: 28 Aug 2008 05:36:37 -0700
X-Trace: forums-1-dub 1219926997 10.22.241.152 (28 Aug 2008 05:36:37 -0700)
X-Original-Trace: 28 Aug 2008 05:36:37 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7065
Article PK: 5287

I am trying to load data into tables using the "input into" statement.
The script file is generated by dbextract. This process has worked for
years.

I am using version 9.02.3375 and have for some time now. I have tried
loading the data to another database with the same schema and it is working
fine. I have tried unloading the database into another database and the
same error occurs in the rebuilt database.

Is there anything I can check for in the database that is failing with the
"Too many temporary tables in connection"?

Thanks in advance,
Dave


"Nick Elson" < Posted on 2008-08-28 15:22:05.0Z
From: "Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@>
Newsgroups: ianywhere.public.general
References: <48b69bd5@forums-1-dub>
Subject: Re: too many temporary tables in connection
Lines: 48
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48b6c29d$1@forums-1-dub>
Date: 28 Aug 2008 08:22:05 -0700
X-Trace: forums-1-dub 1219936925 10.22.241.152 (28 Aug 2008 08:22:05 -0700)
X-Original-Trace: 28 Aug 2008 08:22:05 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7066
Article PK: 5286

The DBIsql INPUT statement issues inserts against
the table. Those fire triggers and those can call
or reference procedures and functions.

Plenty of opportunities to create temporary tables
[statement level triggers, tsql triggers, ....]

Using LOAD table avoids all that; especially if you
do not require the firing of those triggers (?again?
since the data came from a database, I presume).

Things to check for?

- said triggers and possible differences in those
across the different systems you've tested

- ditto on any functions or procedures

- if the volume of data (either in the load data
files or in the tables the triggers affect)

- also watch out for cascade actions that
may differ

I don't know if this is a directly contributing factor,
but available disk space for the temporary database
file may play a role, but I actually expect a different
error/assertion in that scenario.

"Dave Westphal" <Dave_Westphal@aal.org> wrote in message
news:48b69bd5@forums-1-dub...
>I am trying to load data into tables using the "input into" statement.
> The script file is generated by dbextract. This process has worked for
> years.
>
> I am using version 9.02.3375 and have for some time now. I have tried
> loading the data to another database with the same schema and it is
> working fine. I have tried unloading the database into another database
> and the same error occurs in the rebuilt database.
>
> Is there anything I can check for in the database that is failing with the
> "Too many temporary tables in connection"?
>
> Thanks in advance,
> Dave
>


Dave Westphal Posted on 2008-08-29 14:58:38.0Z
From: "Dave Westphal" <Dave_Westphal@aal.org>
Newsgroups: ianywhere.public.general
References: <48b69bd5@forums-1-dub> <48b6c29d$1@forums-1-dub>
Subject: Re: too many temporary tables in connection
Lines: 61
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48b80e9e$1@forums-1-dub>
Date: 29 Aug 2008 07:58:38 -0700
X-Trace: forums-1-dub 1220021918 10.22.241.152 (29 Aug 2008 07:58:38 -0700)
X-Original-Trace: 29 Aug 2008 07:58:38 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7068
Article PK: 5288

I added the -zr all -zo option to my dbsrv9 command line to see if the
temporary tables would be listed in the output.
I searched the log file generated for the word "temporary" and searched that
result for the word table, the only reference I got to temprary table was
the error message.

Anything else that would constitute a temporary table that I should search
for in the log file?

"Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@> wrote in message
news:48b6c29d$1@forums-1-dub...
> The DBIsql INPUT statement issues inserts against
> the table. Those fire triggers and those can call
> or reference procedures and functions.
>
> Plenty of opportunities to create temporary tables
> [statement level triggers, tsql triggers, ....]
>
> Using LOAD table avoids all that; especially if you
> do not require the firing of those triggers (?again?
> since the data came from a database, I presume).
>
> Things to check for?
>
> - said triggers and possible differences in those
> across the different systems you've tested
>
> - ditto on any functions or procedures
>
> - if the volume of data (either in the load data
> files or in the tables the triggers affect)
>
> - also watch out for cascade actions that
> may differ
>
> I don't know if this is a directly contributing factor,
> but available disk space for the temporary database
> file may play a role, but I actually expect a different
> error/assertion in that scenario.
>
> "Dave Westphal" <Dave_Westphal@aal.org> wrote in message
> news:48b69bd5@forums-1-dub...
>>I am trying to load data into tables using the "input into" statement.
>> The script file is generated by dbextract. This process has worked for
>> years.
>>
>> I am using version 9.02.3375 and have for some time now. I have tried
>> loading the data to another database with the same schema and it is
>> working fine. I have tried unloading the database into another database
>> and the same error occurs in the rebuilt database.
>>
>> Is there anything I can check for in the database that is failing with
>> the "Too many temporary tables in connection"?
>>
>> Thanks in advance,
>> Dave
>>
>
>


"Nick Elson" < Posted on 2008-08-29 15:12:09.0Z
From: "Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@>
Newsgroups: ianywhere.public.general
References: <48b69bd5@forums-1-dub> <48b6c29d$1@forums-1-dub> <48b80e9e$1@forums-1-dub>
Subject: Re: too many temporary tables in connection
Lines: 69
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48b811c9$1@forums-1-dub>
Date: 29 Aug 2008 08:12:09 -0700
X-Trace: forums-1-dub 1220022729 10.22.241.152 (29 Aug 2008 08:12:09 -0700)
X-Original-Trace: 29 Aug 2008 08:12:09 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7070
Article PK: 5292

Unfortunately that would only show temporary objects
defined in the application code. See my profiling suggestion
as a possible avenue of investigation.

"Dave Westphal" <Dave_Westphal@aal.org> wrote in message
news:48b80e9e$1@forums-1-dub...
>I added the -zr all -zo option to my dbsrv9 command line to see if the
>temporary tables would be listed in the output.
> I searched the log file generated for the word "temporary" and searched
> that result for the word table, the only reference I got to temprary table
> was the error message.
>
> Anything else that would constitute a temporary table that I should search
> for in the log file?
>
> "Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@> wrote in message
> news:48b6c29d$1@forums-1-dub...
>> The DBIsql INPUT statement issues inserts against
>> the table. Those fire triggers and those can call
>> or reference procedures and functions.
>>
>> Plenty of opportunities to create temporary tables
>> [statement level triggers, tsql triggers, ....]
>>
>> Using LOAD table avoids all that; especially if you
>> do not require the firing of those triggers (?again?
>> since the data came from a database, I presume).
>>
>> Things to check for?
>>
>> - said triggers and possible differences in those
>> across the different systems you've tested
>>
>> - ditto on any functions or procedures
>>
>> - if the volume of data (either in the load data
>> files or in the tables the triggers affect)
>>
>> - also watch out for cascade actions that
>> may differ
>>
>> I don't know if this is a directly contributing factor,
>> but available disk space for the temporary database
>> file may play a role, but I actually expect a different
>> error/assertion in that scenario.
>>
>> "Dave Westphal" <Dave_Westphal@aal.org> wrote in message
>> news:48b69bd5@forums-1-dub...
>>>I am trying to load data into tables using the "input into" statement.
>>> The script file is generated by dbextract. This process has worked for
>>> years.
>>>
>>> I am using version 9.02.3375 and have for some time now. I have tried
>>> loading the data to another database with the same schema and it is
>>> working fine. I have tried unloading the database into another database
>>> and the same error occurs in the rebuilt database.
>>>
>>> Is there anything I can check for in the database that is failing with
>>> the "Too many temporary tables in connection"?
>>>
>>> Thanks in advance,
>>> Dave
>>>
>>
>>
>
>


Dave Westphal Posted on 2008-08-28 18:58:54.0Z
From: "Dave Westphal" <Dave_Westphal@aal.org>
Newsgroups: ianywhere.public.general
References: <48b69bd5@forums-1-dub> <48b6c29d$1@forums-1-dub>
Subject: Re: too many temporary tables in connection
Lines: 77
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48b6f56e$1@forums-1-dub>
Date: 28 Aug 2008 11:58:54 -0700
X-Trace: forums-1-dub 1219949934 10.22.241.152 (28 Aug 2008 11:58:54 -0700)
X-Original-Trace: 28 Aug 2008 11:58:54 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7067
Article PK: 5289

I tried substituting "LOAD TABLE" inplace of the "INPUT INTO" and received
other errors. I am expecting triggers to fire.
The data that I am including in the data files may or may not already be in
the database. I have had triggers in place for years that turn the insert
from the "INPUT INTO" into an update of the record. Last I checked "ON
EXISTING UPDATE" does not work during INPUT INTO.

I did find some Foreign keys that were declared a little differently in the
two databases that I compared. In one of the databases the "on delete
cascade" comes after the "on update cascade". This is not consistent on one
database or the other though.

ALTER TABLE "DBA"."tblProdFutChg"
ADD FOREIGN KEY "FK_TBLPRODF_PRODCOM_K_TBLPRODC"
("prod_dbas_inzt_nbr","prod_seq_nbr")
REFERENCES "DBA"."tblProdCom" ("prod_dbas_inzt_nbr","prod_seq_nbr") on
delete cascade on update cascade

Still going through differences in the two databases. Nothing jumps out at
me.

Is there away to see what the names of the temprary tables are to help me
see what would be acting different in the database that errors?
I don't think that I am creating temporary tables as a common practice.

"Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@> wrote in message
news:48b6c29d$1@forums-1-dub...
> The DBIsql INPUT statement issues inserts against
> the table. Those fire triggers and those can call
> or reference procedures and functions.
>
> Plenty of opportunities to create temporary tables
> [statement level triggers, tsql triggers, ....]
>
> Using LOAD table avoids all that; especially if you
> do not require the firing of those triggers (?again?
> since the data came from a database, I presume).
>
> Things to check for?
>
> - said triggers and possible differences in those
> across the different systems you've tested
>
> - ditto on any functions or procedures
>
> - if the volume of data (either in the load data
> files or in the tables the triggers affect)
>
> - also watch out for cascade actions that
> may differ
>
> I don't know if this is a directly contributing factor,
> but available disk space for the temporary database
> file may play a role, but I actually expect a different
> error/assertion in that scenario.
>
> "Dave Westphal" <Dave_Westphal@aal.org> wrote in message
> news:48b69bd5@forums-1-dub...
>>I am trying to load data into tables using the "input into" statement.
>> The script file is generated by dbextract. This process has worked for
>> years.
>>
>> I am using version 9.02.3375 and have for some time now. I have tried
>> loading the data to another database with the same schema and it is
>> working fine. I have tried unloading the database into another database
>> and the same error occurs in the rebuilt database.
>>
>> Is there anything I can check for in the database that is failing with
>> the "Too many temporary tables in connection"?
>>
>> Thanks in advance,
>> Dave
>>
>
>


"Nick Elson" < Posted on 2008-08-29 15:10:47.0Z
From: "Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@>
Newsgroups: ianywhere.public.general
References: <48b69bd5@forums-1-dub> <48b6c29d$1@forums-1-dub> <48b6f56e$1@forums-1-dub>
Subject: Re: too many temporary tables in connection
Lines: 124
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48b81177$1@forums-1-dub>
Date: 29 Aug 2008 08:10:47 -0700
X-Trace: forums-1-dub 1220022647 10.22.241.152 (29 Aug 2008 08:10:47 -0700)
X-Original-Trace: 29 Aug 2008 08:10:47 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7069
Article PK: 5291

Hello Dave,

A few things. It is not the fact you have triggers that concerns
me. It's if those are causing temporary tables to be spawn.

As to RI and Triggers, it is their actions (how many rows
affect what trigger/RI actions those have) as well as their
definition that need to be evaluated.

As to definitions, Transact-SQL style triggers and FOR EACH
STATEMENT triggers cause temporary tables to be spawned,
so it may just be the size of the load that matters. Even if the
size of the source data may noit be nonimally bigger than
earlier runs, the relationship with existing data may be such
that it is causing actions to occur that do no occur in you other
systems.

As an example, if this database (the one having the issue) has
most of the same primary keys, then your insert trigger
would mostly be causing updates to occur. Updates themselves
cause triggers to fire and if those upd triggers are statement
level triggers then they could be spawing 2 temp tables for
each row! Any other side effects after that (on update cascade
for example) could amplify any downstream use of temp tables.

For what it is worth, I do not expect the order of the ref-actions
entries in the catalog will make any real difference unless they
reflect a change/difference in those definitions across the
various systems you have.

One obvious option, for the short term, could be to simply
break this up into smaller batches.

> Is there away to see what the names of the temprary tables are to help me
> see what would be acting different in the database that errors?
> I don't think that I am creating temporary tables as a common practice.

Not that I am aware of (other than using the profiling an guessing
from statement execution counts etc determing that) with SQL
Anywhere. We do not offer the tempdb style of temporary database
that ASE or MSSQL would so there is no query-based way to do that.


"Dave Westphal" <Dave_Westphal@aal.org> wrote in message
news:48b6f56e$1@forums-1-dub...
>I tried substituting "LOAD TABLE" inplace of the "INPUT INTO" and received
>other errors. I am expecting triggers to fire.
> The data that I am including in the data files may or may not already be
> in the database. I have had triggers in place for years that turn the
> insert from the "INPUT INTO" into an update of the record. Last I checked
> "ON EXISTING UPDATE" does not work during INPUT INTO.
>
> I did find some Foreign keys that were declared a little differently in
> the two databases that I compared. In one of the databases the "on delete
> cascade" comes after the "on update cascade". This is not consistent on
> one database or the other though.
>
> ALTER TABLE "DBA"."tblProdFutChg"
> ADD FOREIGN KEY "FK_TBLPRODF_PRODCOM_K_TBLPRODC"
> ("prod_dbas_inzt_nbr","prod_seq_nbr")
> REFERENCES "DBA"."tblProdCom" ("prod_dbas_inzt_nbr","prod_seq_nbr") on
> delete cascade on update cascade
>
> Still going through differences in the two databases. Nothing jumps out at
> me.
>
> Is there away to see what the names of the temprary tables are to help me
> see what would be acting different in the database that errors?
> I don't think that I am creating temporary tables as a common practice.
>
> "Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@> wrote in message
> news:48b6c29d$1@forums-1-dub...
>> The DBIsql INPUT statement issues inserts against
>> the table. Those fire triggers and those can call
>> or reference procedures and functions.
>>
>> Plenty of opportunities to create temporary tables
>> [statement level triggers, tsql triggers, ....]
>>
>> Using LOAD table avoids all that; especially if you
>> do not require the firing of those triggers (?again?
>> since the data came from a database, I presume).
>>
>> Things to check for?
>>
>> - said triggers and possible differences in those
>> across the different systems you've tested
>>
>> - ditto on any functions or procedures
>>
>> - if the volume of data (either in the load data
>> files or in the tables the triggers affect)
>>
>> - also watch out for cascade actions that
>> may differ
>>
>> I don't know if this is a directly contributing factor,
>> but available disk space for the temporary database
>> file may play a role, but I actually expect a different
>> error/assertion in that scenario.
>>
>> "Dave Westphal" <Dave_Westphal@aal.org> wrote in message
>> news:48b69bd5@forums-1-dub...
>>>I am trying to load data into tables using the "input into" statement.
>>> The script file is generated by dbextract. This process has worked for
>>> years.
>>>
>>> I am using version 9.02.3375 and have for some time now. I have tried
>>> loading the data to another database with the same schema and it is
>>> working fine. I have tried unloading the database into another database
>>> and the same error occurs in the rebuilt database.
>>>
>>> Is there anything I can check for in the database that is failing with
>>> the "Too many temporary tables in connection"?
>>>
>>> Thanks in advance,
>>> Dave
>>>
>>
>>
>
>