SQL Anywhere Mobile - Without memory when synchronizing

Author

Without memory when synchronizing

OMTECH

2005-06-29, 8:25 pm

After synchronizing the first time I have problems with the
memory of the pocket, because each synchronization occupies
more space in the memory.

What can I make so that when synchronizing it doesn't occupy
so much memory?, What can I make so that my log doesn't grow
too much?

Here attached the log with the error of the synchronization:
---------------------------------------------------------------------------
5796K of memory used for caching
Using a maximum page size of 2048 bytes
Database server started at Tue Sep 04 2001 16:39
Trying to start SharedMemory link ...

SharedMemory link started successfully

Now accepting requests
Starting database "salestrack" (\Program
Files\Sybase\ASA9\sa
lestrack.db) at Tue Sep 04 2001 16:39
Transaction log: SalesTrack.log
Starting checkpoint of "salestrack" (salestrack.db) at Tue
Sep 04 2001 16:39
Finished checkpoint of "salestrack" (salestrack.db) at Tue
Sep 04 2001 16:39
Database "salestrack" (salestrack.db) started at Tue Sep 04
2001 16:39
*** ERROR *** Assertion failed: 201847 (9.0.2.2451)
Checkpoint log: write to \Program
Files\Sybase\ASA9\sa
lestrack.db failed with status 2:
filesystem may be full

Connection terminated abnormally

Disconnected SharedMemory client's AppInfo:
HOST=Pocket_PC;OS=Wi
ndows CE 3.0 Build 11171
;PID=0x8f67818a;THRE
AD=0xe63bfce;EXE=\Pr
ogram
Files\Sybase\ASA9\db
mlsync.exe;VERSION=9.0.2. 2451;API=DBLIB;TIMEZ
ONEADJUSTMENT=-300

Connection terminated abnormally

Disconnected SharedMemory client's AppInfo:
HOST=Pocket_PC;OS=Wi
ndows CE 3.0 Build 11171
;PID=0x8f67818a;THRE
AD=0xe63bfce;EXE=\Pr
ogram
Files\Sybase\ASA9\db
mlsync.exe;VERSION=9.0.2. 2451;API=DBLIB;TIMEZ
ONEADJUSTMENT=-300

Connection terminated abnormally

Disconnected SharedMemory client's AppInfo:
HOST=Pocket_PC;OS=Wi
ndows CE 3.0 Build 11171
;PID=0xae63bbee;THRE
AD=0xfb975d6;EXE=\Pr
ogram
Files\MOBILINK.exe;VERSION=9.0.2. 2451;API=ODBC;TIMEZO
NEADJUSTMENT=-300
---------------------------------------------------------------------------

This is another error that usually happens:
---------------------------------------------------------------------------
Performance warning: Server cache size is too small for
database "salestrack"
Database server started at Tue Sep 04 2001 16:08
Trying to start SharedMemory link ...

SharedMemory link started successfully

Now accepting requests
Fatal error: memory exhausted

Connection terminated abnormally

Disconnected SharedMemory client's AppInfo:
HOST=Pocket_PC;OS=Wi
ndows CE 3.0 Build 11171
;PID=0x8e68ff9a;THRE
AD=0xee20c96;EXE=\Pr
ogram
Files\Sybase\ASA9\db
mlsync.exe;VERSION=9.0.2. 2451;API=DBLIB;TIMEZ
ONEADJUSTMENT=-300

Connection terminated abnormally

Disconnected SharedMemory client's AppInfo:
HOST=Pocket_PC;OS=Wi
ndows CE 3.0 Build 11171
;PID=0xaf411a5e;THRE
AD=0xf5796ea;EXE=\Pr
ogram
Files\MOBILINK.exe;VERSION=9.0.2. 2451;API=ODBC;TIMEZO
NEADJUSTMENT=-300
---------------------------------------------------------------------------

Greg Fenton

2005-06-30, 3:24 am

OMTECH wrote:
> *** ERROR *** Assertion failed: 201847 (9.0.2.2451)
> Checkpoint log: write to \Program
> Files\Sybase\ASA9\sa
lestrack.db failed with status 2:
> filesystem may be full
>

Is the filesystem truly full (as the error indicates)?
Are you doing any type of transaction log management?

If not, take a look at the DELETE_OLD_LOGS database option in the online
docs.

For now, you will need to do something to free up disk space so that you
can open the database successfully. Are there any extra files or
programs that you can remove from the Pocket PC device?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

OMTECH

2005-07-01, 1:24 pm

greg.fenton wrote:

> Is the filesystem truly full (ace the error indicates)?
> Do plow you doing any type of transaction log management?

> If not, take to look at the DELETE_OLD_LOGS database
option in online the
> docs.

Which is the exact name of the filesystem or how you which
the filesystem is?,
Is there some command of the option DELETE_OLD_LOGS that I
can put in the dsn of the pocket or in the dbmlsync??

Thank you

> OMTECH wrote:
>
> Is the filesystem truly full (as the error indicates)?
> Are you doing any type of transaction log management?
>
> If not, take a look at the DELETE_OLD_LOGS database option
> in the online docs.
>
> For now, you will need to do something to free up disk
> space so that you can open the database successfully.
> Are there any extra files or programs that you can remove
> from the Pocket PC device?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Greg Fenton

2005-07-02, 3:26 am

OMTECH wrote:
>
> Which is the exact name of the filesystem or how you which
> the filesystem is?,

You need to take a look at they filesystem of the Pocket PC device to
see if it is full. I don't remember offhand how to do this, but it is
likely available from the System folder (Start->System ??)

> Is there some command of the option DELETE_OLD_LOGS that I
> can put in the dsn of the pocket or in the dbmlsync??

DELETE_OLD_LOGS is a database option you set in your ASA database. See
the online docs for more details.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

OMTECH

2005-07-15, 8:25 pm

> OMTECH wrote:
>
> You need to take a look at they filesystem of the Pocket
> PC device to see if it is full. I don't remember offhand
> how to do this, but it is likely available from the
> System folder (Start->System ??)
>
>
>
> DELETE_OLD_LOGS is a database option you set in your ASA
> database. See the online docs for more details.
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

In my pocket I have Sybase 9.0.2. and with what I have made
tests it is the following thing: in the remote base the
option Delete_old_logs = ON, and in the dbmlsync use it
lines her of command - x 3M, this makes that the file log is
divided in several with the format YYMMDDXX, but in any
moment it eliminates some of the logs.

In each synchronization I have left less space in the
memory, please, I need a help... Thank you

Greg Fenton

2005-07-15, 8:25 pm

OMTECH wrote:
> In my pocket I have Sybase 9.0.2. and with what I have made
> tests it is the following thing: in the remote base the
> option Delete_old_logs = ON, and in the dbmlsync use it
> lines her of command - x 3M, this makes that the file log is
> divided in several with the format YYMMDDXX, but in any
> moment it eliminates some of the logs.

How many publications and synch subscriptions do you have in your remote
database?

Are you synchronizing them all?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Breck Carter [TeamSybase]

2005-07-16, 7:23 am

On 15 Jul 2005 14:37:22 -0700, OMTECH wrote:
>In my pocket I have Sybase 9.0.2. and with what I have made
>tests it is the following thing: in the remote base the
>option Delete_old_logs = ON, and in the dbmlsync use it
>lines her of command - x 3M, this makes that the file log is
>divided in several with the format YYMMDDXX, but in any
>moment it eliminates some of the logs.
>
>In each synchronization I have left less space in the
>memory, please, I need a help... Thank you

Is this continuing forever, or does it stop when you lose between 6M
and 12M?

AFAIK with DELETE_OLD_LOGS = 'ON' and dbmlsync -x, you will eventually
have at least two or three yymmddxx.LOG files hanging around. With -x
3M you have asked that they all be at least 3M large before they are
created.

Try removing the 3M from the -x so that dbmlsync.exe can restart the
log file *every* time it runs instead of waiting until the log grows
that big.

Note that if you repeatedly synchronize during one day, old "xx"
values in the yymmddxx.LOG file names will be reused; in other words,
when "AA" is deleted, it will be immediately reused as a new "AA"...
this means you cannot go by the "xx" values to determine if old files
have actually been deleted... you have to look at the file timestamps.

Breck

Breck

--
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

OMTECH

2005-07-18, 11:24 am

> OMTECH wrote:
>
> How many publications and synch subscriptions do you have
> in your remote database?
>
> Are you synchronizing them all?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

I have a publication and for the time being 4 subscriptions
Now alone I am testing with a sync synch subscriptions

OMTECH

2005-07-18, 11:24 am

Greg Fenton

2005-07-18, 11:24 am

OMTECH wrote:
>
> I have a publication and for the time being 4 subscriptions
> Now alone I am testing with a sync synch subscriptions

If you have multiple subscriptions, then dbmlsync cannot delete the old
logs until *all* of the subscriptions have synchronized. If it deleted
the log after the first synchronization, then how would it synchronize
the other subscriptions when they go to do their synch?

Do you have a reason for multiple subscriptions in one database to the
same publication?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

OMTECH

2005-07-18, 8:24 pm

> OMTECH wrote:
> subscriptions
>
> If you have multiple subscriptions, then dbmlsync cannot
> delete the old logs until *all* of the subscriptions have
> synchronized. If it deleted the log after the first
> synchronization, then how would it synchronize the other
> subscriptions when they go to do their synch?
>
> Do you have a reason for multiple subscriptions in one
> database to the same publication?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

I have multiple subscriptions because I have more than a
mobilink users and I have many mobilink users because each
pocket is for a different user and therefore the information
that is loaded is also different for each one.

If the problem is that I have multiple susprpciones, then:
Does some other solution exist to delete the transaction log
or some other form of saving memory?

Greg Fenton

2005-07-18, 8:24 pm

OMTECH wrote:
>
> I have multiple subscriptions because I have more than a
> mobilink users and I have many mobilink users because each
> pocket is for a different user and therefore the information
> that is loaded is also different for each one.
>

Maybe I've misread your response, but let me ask this:

Do you have multiple SYNCHRONIZATION SUBSCRIPTIONS in a single remote
database? If so, why?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

David Fishburn

2005-07-19, 1:24 pm

OMTECH wrote in news:42dc37fe.3aa3.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:

....
O>> Do you have a reason for multiple subscriptions in one
O>> database to the same publication?
....
O> I have multiple subscriptions because I have more than a
O> mobilink users and I have many mobilink users because each
O> pocket is for a different user and therefore the information
O> that is loaded is also different for each one.

The problem here is you have multiple subscriptions, but it does not
appear that you run dbmlsync once for *each* subscription.

To get dbmlsync to delete the old logs you would have to do something
like this:
dbmlsync ... (the usual) -u user1
dbmlsync ... (the usual) -u user2
dbmlsync ... (the usual) -u user3
dbmlsync ... (the usual) -u user4

That should advance the log offsets so old logs can be deleted.

That is of course if you have one and *only* one publication in your
remote database. If you have more than 1 publication you will also have
to run the above for each publication, as well as each user.

O> If the problem is that I have multiple susprpciones, then:
O> Does some other solution exist to delete the transaction log
O> or some other form of saving memory?

YES.

I suspect the reason you have 4 remote users on the device is you have
written your synchronization scripts like this:

call ml_add_table_script(
'v1.0', 'sales_order', 'download_cursor', '
SELECT order_id, cust_id, sales_rep
FROM sales_order
WHERE last_modified >= ?
AND sales_rep = ?
' );

That way, for each user synchronizing you will download *only* the data
for that user.

This is how we recommend you write your synchronization scripts ASSUMING
that each remote contains ONLY data for 1 user.

But you have decided to *share* 1 remote database between 4 different
users. There are different approaches available for these situations
when writing your synchronization scripts. I, personally, would rewrite
your scripts to do something like this:

In BOTH the consolidated and remote create a mapping table, something
like this:
CREATE TABLE db_2_remote_map (
rem_db_iden VARCHAR(128) NOT NULL,
sync_user VARCHAR(128) NOT NULL,
first_sync DATETIME NULL,
PRIMARY KEY( rem_db_iden, sync_user )
);

IN THE REMOTE DATABASE
----------------------

CREATE PUBLICATION "DBA"."routing"
(
TABLE customer,
TABLE site_options,
TABLE db_2_remote_map
);
CREATE SYNCHRONIZATION USER "common_db_2";
CREATE SYNCHRONIZATION SUBSCRIPTION TO "DBA"."routing"
FOR "common_db_2"
TYPE 'TCPIP'
ADDRESS 'host=localhost;port
=2439'
OPTION sv='v1.0';

Now, when you want to ADD a new remote user to a remote database:
You do NOT do this:
CREATE SYNCHRONIZATION USER "user2";
Instead you do this:
INSERT INTO db_2_remote_map( rem_db_iden, sync_user, first_sync )
VALUES( 'common_db_2', 'user2', '1900/1/1' );
COMMIT;

Now add another user to this remote:
INSERT INTO db_2_remote_map( rem_db_iden, sync_user, first_sync )
VALUES( 'common_db_2', 'user35', '1900/1/1' );
COMMIT;
INSERT INTO db_2_remote_map( rem_db_iden, sync_user, first_sync )
VALUES( 'common_db_2', 'user100', '1900/1/1' );
COMMIT;

IN THE CONSOLIDATED
-------------------

Instead of writing the synchronization scripts like this:
call ml_add_table_script(
'v1.0', 'sales_order', 'download_cursor', '
SELECT order_id, cust_id, sales_rep
FROM sales_order
WHERE last_modified >= ?
AND sales_rep = ?
' );

You would write them like this:
call ml_add_table_script(
'v1.0', 'sales_order', 'download_cursor', '
SELECT so.order_id, so.cust_id, so.sales_rep
FROM sales_order so, db_2_remote_map db2rem
WHERE so.sales_rep = db2rem.sync_user
AND so.last_modified >= ?
AND db2rem.rem_db_iden = ?
' );

Since you have inserted the following data into table db_2_remote_map:
rem_db_iden sync_user
----------- ---------
common_db_2 user2
common_db_2 user35
common_db_2 user100

When you synchronize this remote the above download cursor will execute
this SQL statement:
SELECT so.order_id, so.cust_id, so.sales_rep
FROM sales_order so, db_2_remote_map db2rem
WHERE so.sales_rep = db2rem.sync_user
AND so.last_modified >= '1900-01-01'
AND db2rem.rem_db_iden = 'common_db_2'

So in effect, you are downloading all the data for the 3 users you have
"ASSIGNED" to this remote database.

Now there is *no* need for:
1. Multiple subscriptions
2. Multiple synchronizations

Each time the remote syncs, you will get the data for all the people
assigned to this database.

There is 1 thing you must be aware of though. First time
synchronization can be an issue.

Assume you already have these 3 users synchronizing. Now you want to
add a "new" user. So lets add "user456". If we insert this user into
the db_2_remote_map table then the next time we sync, we only get the
data that has changed since we last synchronized. Unfortunately,
user456, is a new user, so in this special case we need to download all
this users information, and from then on, get only the data that has
changed for each sync.

This can be accomplished using the modify_last_download
_timestamp
connection event. This event fires between the upload and download
transactions. It allows our scripts to modify the timestamp value that
is passed to all the download events.

So this line in our download cursor:
WHERE last_modified >= ?
MobiLink replaces the ? with the value returned from the
modify_last_download
_timestamp event / stored procedure.

Here is an example of what we would do in this event:
call ml_add_connection_sc
ript( 'v1.0',
'modify_last_downloa
d_timestamp', '
{call sp_modify_last_downl
oad_timestamp(?,?,?)}
' );

We must also create this stored procedure. The procedure can follow any
naming
convention you want, I simply placed an "sp_" at the front of it:

-- This is a Transact-SQL procedure and works with:
-- Microsoft SQL Server
-- Sybase Adaptive Server Anywhere (ASA)
-- Sybase Adaptive Server Enterprise (ASE)
CREATE PROCEDURE sp_modify_last_downl
oad_timestamp
@nldts DATETIME OUTPUT,
@ldts DATETIME,
@sync_name VARCHAR( 128 )
AS
BEGIN
DECLARE @min_ldts DATETIME

-- Check if this is a first time synchronization
-- for any of the users of the database that
-- is currently synchronizing
SELECT @min_ldts = min(first_sync)
FROM db_2_remote_map
WHERE rem_db_iden = @sync_name

IF @min_ldts = '1900/1/1'
BEGIN
-- Good, we want to return this value
SELECT @nldts = @min_ldts
-- Now update these records so the next
-- time this user synchronizes, it will
-- not be treated as a first time sync.
UPDATE db_2_remote_map
SET first_sync = getdate()
WHERE rem_db_iden = @sync_name
END
ELSE
BEGIN
-- If no users are first time synchronizations
-- return the default value which MobiLink
-- passes in as a parameter to this procedure
SELECT @nldts = @ldts
END
END

So any time this remote syncs, it sends up "common_db_2". So we check this
table to see if any users have a first_sync of '1900/1/1'. If so, we send
all the data down to the device. Otherwise, we just send the data that
changed
since we last synchronized. It also resets this remote database so that
the
next time we sync, we only get changed data. To add new "users" to this
remote, simply perform the above insert using '1900/1/1' as the initial
first_sync value.

So the difference between what you are currently doing, and what I suggest
you
do is the following:
1. Do not create multiple users via the CREATE SYNCHRONIZATION USER
command.
2. Create this new table, db_2_remote_map.
3. At the remote (or the consolidated) "assign" users to various "remote"
databases by inserting into this table.
4. Create the modify_last_download
_timestamp event.
5. Create the sp_modify_last_downl
oad_timestamp stored procedure.
6. Modify the download_cursors to join to this table.

These changes are all very minimal.

What I have shown above can be optimized and refined. I tried to keep it
simple to help first time users.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Sponsored Links