When you are sure that these remotes must be re-extracted, you can cleanly shut them down and just copy and use the .db files to rebuild your consolidated.

I presume any record that has a autoincrement value will keep the value from the original store data ?

Yes, as these rows have already got their autoincrement value, that value will be kept. (Aside: DBUNLOAD uses LOAD TABLE with the default clause DEFAULTS OFF, so a new default value would not be set even if the according column would be missing within the optional LOAD TABLE column list.)

I presume I can run the new version on the consolidated with DBEng so no one can connect to it while we re-build ?

Can i just delete the old log files before we start re-build ?

Mark

so no one can connect to it

No one except from the same machine...

Can i just delete the old log files before we start re-build ?

What files do you mean?

The old consolidated .log files

Well, if you build a new consolidated database, it will have its own series of transaction logs and won't be able to use older logs, so for replication purposes they are no longer needed.

However, in case your consolidated is not just a collector of remote data but does also have "its own data", you might check whether the old logs contain data that is not yet part of your new consolidated. For that purpose you might translate the old logs (possibly with the DBTRAN "-is Local" option) to check whether there are usable DML statements to run against the new database.

That being said, I would generally not immediately delete old logs but move them to a secure place, just in case...

Just my two cents, anyway.

Hi Volker

I have extracted first remote site and at remote site when I try to connect it comes up with message below ? We extracted the DB on a 17.0.7.3390 build - the remote site is 17.0.9.4803 ?

Thanks

[SAP][ODBC Driver][SQL Anywhere]Unable to start specified database: Server must be upgraded to start database C:\sdata\store08.db SQLCODE=-934 SQLSTATE=08001

Hm, I would ask that as a new question...

FWIW, I would expect that error message under switched conditions, i.e. when the database file version is newer than the database server version.

You may have a look at the SYSHISTORY entries for that database (checked on a database server that can run it, apparently:)...).

Okay, solved this, it was a PATH issue - we upgraded to 64 bit version from .0.7 -> .0.9. and it was still looking at the old (x86) install.

If I'm running dbeng and no one is connected can I run extract users at a level lower than 3 ?

Hi Volker

We have now extracted all users (apart for some less urgent ones) However when I fire up the SQL Remote service on the consolidated I get an error.

Not sure what to do here ? All the reloads and extractions seem to work fine and the remote sites are all working (including their SQL Remote services)

HELP

Mark

I. 2018-11-10 08:36:44. 
I. 2018-11-10 08:36:45. Scanning logs starting at offset 01178032419905
I. 2018-11-10 08:36:45. Transaction log "c:/sdata_log/consol.log" starts at offset 01178199173531
I. 2018-11-10 08:36:45. Processing transaction logs from directory "c:\sdata_log"
I. 2018-11-10 08:36:45. Retrieving offline transaction logs through the engine from "c:/sdata_log/"
E. 2018-11-10 08:36:45. No off-line transaction log file found and on-line transaction log starts at offset 01178199173531.
I. 2018-11-10 08:36:45. We are looking for a transaction log file starting at offset 01178032419905.
I. 2018-11-10 08:36:46. Execution completed

I think I have made a mistake (see post a bit further on) - I cleared the old consol db of all data, did a dbreload and then deleted the old log files by mistake. I then continued reloading data into the consolidated, then extracted all users.

However when I now try and start dbremote it is giving me an error, I guess it's still looking for the old files. Is there any way I can stop it looking for the older log files and just use the current one ?

Otherwise I might have to start all over again ?

I. 2018-11-10 08:36:44. 
I. 2018-11-10 08:36:45. Scanning logs starting at offset 01178032419905
I. 2018-11-10 08:36:45. Transaction log "c:/sdata_log/consol.log" starts at offset 01178199173531
I. 2018-11-10 08:36:45. Processing transaction logs from directory "c:\sdata_log"
I. 2018-11-10 08:36:45. Retrieving offline transaction logs through the engine from "c:/sdata_log/"
E. 2018-11-10 08:36:45. No off-line transaction log file found and on-line transaction log starts at offset 01178199173531.
I. 2018-11-10 08:36:45. We are looking for a transaction log file starting at offset 01178032419905.
I. 2018-11-10 08:36:46. Execution completed

I cleared the old consol db of all data, did a dbreload and then deleted the old log files by mistake. I then continued reloading data into the consolidated, then extracted all users.

Mark, so you did not create a new consolidated database but just deleted old data within the same database and entered new data? (If so, that's not commonly called a "reload" - A "reload" usually means you create a new database and fill it with schema and data of the previous one via dbunload.)

Well, if you build a new consolidated database, it will have its own series of transaction logs and won't be able to use older logs, so for replication purposes they are no longer needed.

That's what I had written some comments ago, clearly relating to two separate database instances, an old and a new one. By that I meant the new one had been freshly created and therefore has its "own" series of log files.

In case you simply keep using the original database with freshly reloaded data, then you need to retain the old logs until they are no longer needed by DBREMOTE. A comparison of the contents of the SYSREMOTEUSER table and the starting and ending offsets of you old and current log files should help to ensure whether all needed logs are available.

Aside: I guess it would be way more helpful to contact technical support for further questions.

Yes, I kept the original database. Is there any way I can get dbremote to ignore the old log files and just use the current one ?

Well, if DEBREMOTE asks for a particular old log file, it usually needs them because the log offsets from the old file have not yet been confirmed by all remotes.

As stated, you should check the contents of SYSREMOTEUSER. I'm quite sure the lowest value for "confirm_sent" is below the start offset of your current log (01178199173531 as to your snippet). If so, DBREMOTE will need an older log with that offset.

If that is the case and you don't have the older log(s) available (note: I recommended to "move them", not to delete them...), you might get around the situation if you re-extract those remotes with a "confim_sent" value that is below that from the current log.

That being said, as my help seems to have been misleading based on the flow of the discussion here, I would again recommend to call support - they can help you based on your actual data and situation, whereas within this forum, we can only answer what you have asked, without knowing all important details.

Hi Volker

I thnk what I need to do is another re-build. With the correct dbunload. If I use dbunload-an this would create me a new database with it's own new fresh logs. I could then re-extract from that -is that correct.

Mark

Hm, just re-extracting all remotes should do the trick with the current database. However, what does the mentioned system table reveal?

It looks like the remote users I have not yet extracted have these old log offsets. If I re-extract these will it stop looking for these offsets ? (so store04,store02,store20 and store 99

user_id,consolidate,type_id,address,frequency,send_time,log_send,time_sent,log_sent,confirm_sent,send_count,resend_count,time_received,log_received,confirm_received,receive_count,rereceive_count,time_sent_utc,time_received_utc
102,'N',1,STORE01,'P',00:15:00,0,,1178224106971,1178224106971,0,0,,0,0,0,0,,
107,'N',1,STORE02,'P',00:15:00,0,,1178032419905,1178031277857,126250,0,,949831462,949831462,137142,0,,
108,'N',1,STORE04,'P',00:15:00,0,,1178032419905,1178031277857,112729,0,,283774642,283774642,108462,1,,
112,'N',1,STORE05,'P',00:15:00,0,,1178224192871,1178224192871,0,0,,0,0,0,0,,
103,'N',1,STORE08,'P',00:15:00,0,,1178199246896,1178199246896,0,0,,0,0,0,0,,
105,'N',1,STORE11,'P',00:15:00,0,,1178224019173,1178224019173,0,0,,0,0,0,0,,
110,'N',1,STORE14,'P',00:15:00,0,,1178207233823,1178207233823,0,0,,0,0,0,0,,
111,'N',1,STORE15,'P',00:15:00,0,,1178224019282,1178224019282,0,0,,0,0,0,0,,
104,'N',1,STORE17,'P',00:15:00,0,,1178224062475,1178224062475,0,0,,0,0,0,0,,
106,'N',1,STORE18,'P',00:15:00,0,,1178224293606,1178224293606,0,0,,0,0,0,0,,
109,'N',1,STORE19,'P',00:15:00,0,,1178224237359,1178224237359,0,0,,0,0,0,0,,
114,'N',1,STORE20,'P',00:15:00,0,,1178032419905,1178031277857,106792,0,,3645573912,3645573912,146892,1,,
115,'N',1,STORE21,'P',00:15:00,0,,1178224150547,1178224150547,0,0,,0,0,0,0,,
113,'N',1,STORE99,'P',00:15:00,0,,1178032419905,1178031277857,120104,0,,501524808,501524808,126738,5,,

Yes, that did the job !

Many thanks for your help in this matter.

Mark