SQL Anywhere database - Assertion failed 201501

Author

Assertion failed 201501

Peter W. Barth

2005-05-11, 9:23 am

Help needed:

We are running 9.0.2.3044 under W2K
Since we are reorganizing tables on the fly by executing a PROCEDURE, we
have sever assertation problems (see core statements below).

As you can see, we are already clutching straws by using the statements
COMMIT and CHECKPOINT.

When updating thereafter large number of rows on the server, we get
regularly error message 201501 assertion failed - can not find page. The
server engine aborts. If we restart everything will run OK.

If we do not reorganize, we are executing OK - but at a terrible slow speed.

The core statements of a reorganization PROCEDURE are:

-- ALTER TABLE table_name ADD PCTFREE 25;
EXECUTE IMMEDIATE WITH ESCAPES OFF
STRING('ALTER TABLE ', RTRIM(@in_table_name
), ' ADD PCTFREE ', 25,';');

EXECUTE IMMEDIATE WITH ESCAPES OFF
STRING('REORGANIZE TABLE ', RTRIM(@in_table_name
), ';');

EXECUTE IMMEDIATE WITH ESCAPES OFF
STRING('COMMIT; CHECKPOINT;');

Regards

Peter W. Barth

Glenn Paulley

2005-05-11, 11:23 am

Please consider opening a case with Technical Support. The engine team
would like to get your database in-house so that we can determine the
cause of the problem, and fix it.

Glenn

"Peter W. Barth" <Peter.W.Barth@business-model.com> wrote in
news:428213e8@forums
-2-dub:

> Help needed:
>
> We are running 9.0.2.3044 under W2K
> Since we are reorganizing tables on the fly by executing a PROCEDURE,
> we have sever assertation problems (see core statements below).
>
> As you can see, we are already clutching straws by using the
> statements COMMIT and CHECKPOINT.
>
> When updating thereafter large number of rows on the server, we get
> regularly error message 201501 assertion failed - can not find page.
> The server engine aborts. If we restart everything will run OK.
>
> If we do not reorganize, we are executing OK - but at a terrible slow
> speed.
>
>
> The core statements of a reorganization PROCEDURE are:
>
> -- ALTER TABLE table_name ADD PCTFREE 25;
> EXECUTE IMMEDIATE WITH ESCAPES OFF
> STRING('ALTER TABLE ', RTRIM(@in_table_name
), ' ADD PCTFREE ',
> 25,';');
>
> EXECUTE IMMEDIATE WITH ESCAPES OFF
> STRING('REORGANIZE TABLE ', RTRIM(@in_table_name
), ';');
>
> EXECUTE IMMEDIATE WITH ESCAPES OFF
> STRING('COMMIT; CHECKPOINT;');
>
> Regards
>
> Peter W. Barth
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Peter W. Barth

2005-05-11, 11:23 am

Glenn:

I have no problem to give a copy (300 MB) of the database to Sybase's
Engineers, but if it comes to fork-out ?250 to open a case with Technical
Support, the answer is NO.

On two occasions, we paid and the results were in our view not Ok. Sorry, do
not take it personal.

If you want the DB anyhow, pls. let me know.

Kind regards

Peter Barth

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns96536EA2461B
2paulleyianywherecom
@10.22.241.106...
> Please consider opening a case with Technical Support. The engine team
> would like to get your database in-house so that we can determine the
> cause of the problem, and fix it.
>
> Glenn
>
> "Peter W. Barth" <Peter.W.Barth@business-model.com> wrote in
> news:428213e8@forums
-2-dub:
>
>
>
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288

Reg Domaratzki $iAnywhere Solutions$

2005-05-11, 11:23 am

You can always report a bug for free by visiting CaseXpress at
http://casexpress.sybase.com/cx/cx.stm. When submitting calls
through CaseXpress, please make sure to give an accurate description
of the steps you take to reproduce the bug, and include any
files that may be needed (database, source code) to reproduce the
problem.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Peter W. Barth" <Peter.W.Barth@business-model.com> wrote in message
news:42822223$1@foru
ms-2-dub...
> Glenn:
>
> I have no problem to give a copy (300 MB) of the database to Sybase's
> Engineers, but if it comes to fork-out ?250 to open a case with Technical
> Support, the answer is NO.
>
> On two occasions, we paid and the results were in our view not Ok. Sorry,
do
> not take it personal.
>
> If you want the DB anyhow, pls. let me know.
>
> Kind regards
>
> Peter Barth
>
>
>
>
> "Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns96536EA2461B
2paulleyianywherecom
@10.22.241.106...
>
>

Peter W. Barth

2005-05-11, 11:23 am

CaseExpress does not even list SQL Anywhere Studio as a product.

Besides, to send a database with customer data into the Nirwana does not
agree with our company policy.

From the reports I read, the bug 201501 is not new to Sybase and gets
carried forward from one version to the next.

To summarize, statement REORGANIZE TABLE does not yet work properly -
perhaps in version 10 or so.

Meanwhile, we will have to take the database down at night and reorganize it
through UNLOAD utilities. Cost of ownership for SQL Anywhere Studio is
pretty high.

"Reg Domaratzki (iAnywhere Solutions)" < Spam_bad_rdomarat@ia
nywhere.com>
wrote in message news:42822405$1@foru
ms-1-dub...
> You can always report a bug for free by visiting CaseXpress at
> http://casexpress.sybase.com/cx/cx.stm. When submitting calls
> through CaseXpress, please make sure to give an accurate description
> of the steps you take to reproduce the bug, and include any
> files that may be needed (database, source code) to reproduce the
> problem.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> "Peter W. Barth" <Peter.W.Barth@business-model.com> wrote in message
> news:42822223$1@foru
ms-2-dub...
Technical[color=dark
red]
Sorry,[color=darkred
]
> do
PROCEDURE,[color=dar
kred]
slow[color=darkred]
>
>

Reg Domaratzki $iAnywhere Solutions$

2005-05-11, 1:23 pm

> CaseExpress does not even list SQL Anywhere Studio as a product.
You need to choose "Family 16 - iAnywhere/AvantGo" as the Product Family on
the first page.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Peter W. Barth" <Peter.W.Barth@business-model.com> wrote in message
news:42823636$1@foru
ms-2-dub...
> CaseExpress does not even list SQL Anywhere Studio as a product.
>
> Besides, to send a database with customer data into the Nirwana does not
> agree with our company policy.
>
> From the reports I read, the bug 201501 is not new to Sybase and gets
> carried forward from one version to the next.
>
> To summarize, statement REORGANIZE TABLE does not yet work properly -
> perhaps in version 10 or so.
>
> Meanwhile, we will have to take the database down at night and reorganize
it
> through UNLOAD utilities. Cost of ownership for SQL Anywhere Studio is
> pretty high.
>
>
>
> "Reg Domaratzki (iAnywhere Solutions)" < Spam_bad_rdomarat@ia
nywhere.com>
> wrote in message news:42822405$1@foru
ms-1-dub...
> http://www.ianywhere.com/developer/product_manuals
> Technical
> Sorry,
team[color=darkred]
the[color=darkred]
> PROCEDURE,
get[color=darkred]
page.[color=darkred]
> slow
>
>

Martin Baur

2005-05-11, 8:23 pm

> The core statements of a reorganization PROCEDURE are:
>
> -- ALTER TABLE table_name ADD PCTFREE 25;
> EXECUTE IMMEDIATE WITH ESCAPES OFF
> STRING('ALTER TABLE ', RTRIM(@in_table_name
), ' ADD PCTFREE ', 25,';');
>
> EXECUTE IMMEDIATE WITH ESCAPES OFF
> STRING('REORGANIZE TABLE ', RTRIM(@in_table_name
), ';');
>
> EXECUTE IMMEDIATE WITH ESCAPES OFF
> STRING('COMMIT; CHECKPOINT;');

Pardon me for asking ... I'd be glad to learn WHAT you solve by doing this?

I see and understand that you want to attach the PCTFREE constraint to a table. If this is understood right, why does it not help to set PCTFREE on CREATE TABLE? Afaik, PCTFREE is supposed to provide
(and guarantee) that the percentage per page is kept free. Therefore, I expect ASA to obey to this rule at any time during operation.

So, I don't see the need for your code at all. Of course, I might be much less experienced than you. Therefore I beg you to explain the purpose of your code.

In my understanding, you must have a very busy db with tables updated under heavy duty, mostly enlarging existing column contents ...

Thanks,

Martin

Breck Carter [TeamSybase]

2005-05-12, 7:23 am

AFAIK PCTFREE affects INSERTs, not UPDATEs. Rows that grow in size
because of updates will fill the page regardless of PCTFREE. Rows are
never moved to other pages during normal operations, and I do not
believe that PCTFREE will cause a row to be split earlier than normal,
since that would be a solution worse than the cure. Reorganization
will restore the effect of PCTFREE to accomodate future updates.

Breck

On 11 May 2005 15:45:16 -0700, Martin Baur <tinu@mindpower.com> wrote:

>
>
>Pardon me for asking ... I'd be glad to learn WHAT you solve by doing this?
>
>I see and understand that you want to attach the PCTFREE constraint to a table. If this is understood right, why does it not help to set PCTFREE on CREATE TABLE? Afaik, PCTFREE is supposed to provide
>(and guarantee) that the percentage per page is kept free. Therefore, I expect ASA to obey to this rule at any time during operation.
>
>So, I don't see the need for your code at all. Of course, I might be much less experienced than you. Therefore I beg you to explain the purpose of your code.
>
>In my understanding, you must have a very busy db with tables updated under heavy duty, mostly enlarging existing column contents ...
>
>Thanks,
>
>Martin

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Jason Noack

2005-05-13, 11:23 am

Have you validated your database as it may be corrupt? Does the assertion
always happen on the same table? Does rebuilding the database prevent the
problem from occurring?

-Jay
"Peter W. Barth" <Peter.W.Barth@business-model.com> wrote in message
news:428213e8@forums
-2-dub...
> Help needed:
>
> We are running 9.0.2.3044 under W2K
> Since we are reorganizing tables on the fly by executing a PROCEDURE, we
> have sever assertation problems (see core statements below).
>
> As you can see, we are already clutching straws by using the statements
> COMMIT and CHECKPOINT.
>
> When updating thereafter large number of rows on the server, we get
> regularly error message 201501 assertion failed - can not find page. The
> server engine aborts. If we restart everything will run OK.
>
> If we do not reorganize, we are executing OK - but at a terrible slow
speed.
>
>
> The core statements of a reorganization PROCEDURE are:
>
> -- ALTER TABLE table_name ADD PCTFREE 25;
> EXECUTE IMMEDIATE WITH ESCAPES OFF
> STRING('ALTER TABLE ', RTRIM(@in_table_name
), ' ADD PCTFREE ',
25,';');
>
> EXECUTE IMMEDIATE WITH ESCAPES OFF
> STRING('REORGANIZE TABLE ', RTRIM(@in_table_name
), ';');
>
> EXECUTE IMMEDIATE WITH ESCAPES OFF
> STRING('COMMIT; CHECKPOINT;');
>
> Regards
>
> Peter W. Barth
>
>

Sponsored Links