Upload Update Not Working

[Former Member]

Hi Experts,

I have written the below script to update a field in the consolidated database table FT_RemoteStopTransaction

with the data on the edge device. But the script is not working and neither is giving any error.

UPDATE "ABC.XYZ"."ABC_XYZ_PRJ.TABLES.FACT::FT_RemoteStopTransaction"

  SET   REMOTE_STOP_TRANSACTION_STATUS = {ml r.REMOTE_STOP_TRANSACTION_STATUS}

  WHERE             CHARGEBOX_IDENTITY = {ml r.CHARGEBOX_IDENTITY}

  AND                 TIMESTAMPS = {ml r.TIMESTAMPS' );

My consolidated and edge tables has CHARGEBOX_IDENTITY & TIMESTAMPS as primary key.

Thanks

Madhusudan

[Former Member]

Hi Chris,

Its a preproduction system.

I have tried the below codes to set the timestamp format in sql anywhere but this seems to have no affect on the output.

SET OPTION TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:NN:SS[.sss]';

SET OPTION TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:NN:SS.sss AA'

In SQL_ANYWHERE:

As you can see the seconds and micro seconds part is always missing.

In HANA:

The field highlighted in yellow in the above screen shot is what i am trying to sync from remote database.

Thanks

Madhusudan

[chris_keating]

The TIMESTAMP_FORMAT option sets the format retrieved from the database. The tool used to display the results may format that data in a different way.

In DBISQL 17, the default is to display the date using the OS regional settings. To display the settings as defined on the server, you have to:

1. go to Tools | Options

2. select the SQL Anywhere category

3. select "on database server"  from the Format Dates and Times dropdown.

This format setting does not change the underlying data stored just how it gets displayed.

[Former Member]

Hi Chris,

I can check the log in HCP for the Mobilink server.

Please find it below and check if it helps and also check the log marked bold is what you have asked me to do.

========================================================================

I. 2016-03-06 16:42:14. SQL Anywhere MobiLink Server Version 16.0.0.2184

I. 2016-03-06 16:42:14.

I. 2016-03-06 16:42:14. Copyright (c) 2015 SAP SE or an SAP affiliate company.

I. 2016-03-06 16:42:14. All rights reserved.

I. 2016-03-06 16:42:14. Use of this software is governed by the Sybase License Agreement.

I. 2016-03-06 16:42:14. Refer to http://www.sybase.com/softwarelicenses.

I. 2016-03-06 16:42:14.

I. 2016-03-06 16:42:14. This server is licensed to:

I. 2016-03-06 16:42:14. HCP

I. 2016-03-06 16:42:14. SAP

I. 2016-03-06 16:42:14. Running Linux 3.0.101-0.47.71-xen #1 SMP Thu Nov 12 12:22:22 UTC 2015 (b5b212e) on X86_64

I. 2016-03-06 16:42:14. Server built for X86_64 processor architecture

I. 2016-03-06 16:42:14. <Main> Local file for remote synchronization logs: 'mlsrv.mle'

I. 2016-03-06 16:42:15. <Main> MobiLink server started

Press 'q' to quit MobiLink server

W. 2016-03-06 16:59:06. <1> [10017] The consolidated and remote databases have different timestamp precisions. Consolidated database timestamps are precise to 0 digit(s) in the fractional second while the remote database timestamps are precise to 6 digit(s)

W. 2016-03-06 16:59:06. <1> [10018] Resolve the timestamp precision mismatch by setting the SQL Anywhere DEFAULT_TIMESTAMP_INCREMENT option on the remote database to 1000000 and TRUNCATE_TIMESTAMP_VALUES to 'On'. UltraLite remotes must set the TIMESTAMP_INCREMENT option to 1000000

W. 2016-03-06 16:59:06. <1> [10020] The timestamp precision mismatch may affect upload conflict detection. Use the -zp option to cause the MobiLink server to use the lowest timestamp precision for conflict detection purposes

W. 2016-03-06 16:59:06. <1> [10015] Table 'FT_RemoteStopTransaction' has at least one timestamp column. Due to a timestamp precision mismatch, uploaded timestamps can lose precision, defeating download filtering

W. 2016-03-06 16:59:06. <1> [10015] Table 'FT_RemoteStartTransaction' has at least one timestamp column. Due to a timestamp precision mismatch, uploaded timestamps can lose precision, defeating download filtering

W. 2016-03-06 17:00:44. <2> [10017] The consolidated and remote databases have different timestamp precisions. Consolidated database timestamps are precise to 0 digit(s) in the fractional second while the remote database timestamps are precise to 6 digit(s)

W. 2016-03-06 17:00:44. <2> [10018] Resolve the timestamp precision mismatch by setting the SQL Anywhere DEFAULT_TIMESTAMP_INCREMENT option on the remote database to 1000000 and TRUNCATE_TIMESTAMP_VALUES to 'On'. UltraLite remotes must set the TIMESTAMP_INCREMENT option to 1000000

W. 2016-03-06 17:00:44. <2> [10020] The timestamp precision mismatch may affect upload conflict detection. Use the -zp option to cause the MobiLink server to use the lowest timestamp precision for conflict detection purposes

W. 2016-03-06 17:00:44. <2> [10015] Table 'FT_RemoteStopTransaction' has at least one timestamp column. Due to a timestamp precision mismatch, uploaded timestamps can lose precision, defeating download filtering

W. 2016-03-06 17:00:44. <2> [10015] Table 'FT_RemoteStartTransaction' has at least one timestamp column. Due to a timestamp precision mismatch, uploaded timestamps can lose precision, defeating download filtering

Thanks

Madhusudan

[chris_keating]

That is a likely explanation if the problem rows were created on the remote.

If this is preproduction (development), I would recommend configurating HANA to match the 6 digit microsecond precision used by SQL Anywhere (or at least to increase the precision so that timestamps are not "to the second").  If you cannot do this or if this is already production data, you can use SET OPTION to set the options to the suggested values in the log. To learn more, search for SET OPTION -and- the options indentified in the product docs found at dcx.sap.com

[former_member188493]

Chris, does mlsrv17 delivered with SQL Anywhere 17 work at all with a HANA consolidated database?

The SA17 docs have been butchered to expunge HANA.

[chris_keating]

HANA Remote Sync is supported in v17. If MobiLink is not used in this context, an error is reported.

[Former Member]

Hi Chris,

As i said i just started working on sql anywhere so not not very clear with the command you have mentioned.

FYI I start the mobilink server by logging into HCP -> java application -> mobilink_server_name-> Start OR Stop.

I am not using console client to start the server. I have used console client only to deploy the server.

I have used the below link to deploy the moblink server on to compute unit on HCP.

SAP HANA Cloud Platform

Thanks & Regards

Madhusudan.

[chris_keating]

I have no knowledge of HCP. Perhaps you can reach out to someone who knows HCP and ask how to add additional logging to MobiLink server.

[Former Member]

Hi Chris,

I am running SQL Anywhere 17 on windows 7 now for testing. Can you please tell me how do the same in windows.

Apologies for the inconvenience.

Thanks & Regards

Madhusudan

[chris_keating]

mlsrv17 -vcinF -o <filespec>  <the existing mobilink startup options>

If you are already using -o, you do not need to duplicate that option.

[chris_keating]

Define what you mean by not work?

MobiLink will only process rows that are identified as updated from the remote in this script and using the update script you supply to update the consolidated.

What consolidated are you running? What remote are you running?

[Former Member]

Hi Chris,

Consolidated Database = HANA on HCP

Remote Database        = SQL Anywhere 17 on AWS (middleware) running on UBUNTU linux

I am downloading(download_cursor) 4 of the 5 fields in the table from the consolidated database(HANA) to remote database

.

select CHARGEBOX_IDENTITY, REMOTE_STOP_TRANSACTION_ID, REMOTE_STOP_TRANSACTION_STATUS, LAST_MODIFIED

   from   "ABC_XYZ"."ABC_XYZ_PRJ.TABLES.FACT::FT_RemoteStopTransaction"

   where  LAST_MODIFIED >= {ml s.last_table_download}'

After sometime the 5th field gets updated in the remote database which i need to synchronize(upload_update) with the consolidate database(HANA).

For the above scenario i have written the script which i have mentioned earlier.

Now while running the sychronization i am not getting any error, but the 5th field is not update in the consolidated database(HANA).

I just started working on SQL Anywhere so dont have much idea how to work on it.

Please let me know if you require more information

Thanks & Regards

Madhusudan

[chris_keating]

Can you add logging

            -vcinF -o <filespec>

and capture a log that captures this issue. We can then look at the log and try to understand the behavuour.