import export problem

[Former Member]

Hello, i am trying to do an export in a maxdb database in linux with version 7.6.03.07 to a maxdb database in windows with the same version, i have created a clean database instance in the windows target, in linux i do the export with this:

loadercli -d oteros -u dbadmin,dbadmin

EXPORT DB CATALOG OUTSTREAM FILE 'oterosdb_ddl.catalog' DDL DATA OUTSTREAM FILE 'oterosdb_pages.data' PAGES PACKAGE OUTSTREAM FILE 'oterosdb_csv.package' CSV

this give me this output:

Loader protocol: '/home/skuda/sdb/skuda/loader/log/loader.log'

Loader packages: '/home/skuda/sdb/skuda/loader/packages'

User DBADMIN connected to database OTEROS schema DBADMIN on local host.

EXPORT DB CATALOG OUTSTREAM FILE 'oterosdb_ddl.catalog' DDL DATA OUTSTREAM FILE 'oterosdb_pages.data' PAGES PACKAGE OUTSTREAM FILE 'oterosdb_csv.package' CSV

Successfully executed:

---

Total number of tables (definition) exported: 169

Total number of tables (data) exported: 169 (excluded: 0, failed: 0)

and create the files: oterosdb_ddl.catalog, oterosdb_pages.data0000, oterosdb_pages.data0001 and oterosdb_csv.package.

I copy the files to the windows machine and do this:

loadercli -d TEST -u DBADMIN,DBADMIN

IMPORT DB CATALOG INSTREAM FILE 'oterosdb_ddl.catalog' DDL DATA INSTREAM FILE 'oterosdb_pages.data' PAGES

and i get this output:

ERR -25342

Error restoring table CAJA_MOVIMIENTOS; table exists but source table schema and target table schema are different

---

Total number of tables (definition) imported: 169

Total number of tables (data) imported: 1 (excluded: 0, failed: 1)

---

how can be different if i have exported it in the same command and 5 minutes ago?

[Former Member · Accepted solution]

Did you solve your problem? I would be interested in a solution because I had a similare problem.

thanks

[Former Member · Accepted solution]

Hello Christian,

i am working with steffen to detect the bug/problem, i have not solved it yet.

[steffen_schildberg · Accepted solution]

Hi Christian,

Soraya and I had some private email conversation to not bother the forum with sending single data files and so on. We found the reason for the problem and I can offer 3 alternatives to avoid it.

The problem arises because the catalog of the table that fails to import had been changed before exporting it by dropping one or more columns of that table without releasing the occupied space in the database.

Here is what I wrote Soraya concerning the error:

Let me explain the problem a bit more in detail.

When using the data file format PAGES the Loader saves the data as it is, i.e. exactly in this page structure as the database stores the data in it's volumes. To make sure the data fits to the table definition the Loader saves the table definition in binary format in the data file as well during export.

During import it compares the definition of the exported table with the one to

import. And here comes the problem in.

In case one drops a column of a table the data is not deleted for the sake of performance except you request it with the addition "RELEASE SPACE" in your drop statement. This means the data is still present on all pages that exists for that table up to the moment you drop the column. This is the reason why the database system does not update the column position information for the default meta information for columns with defaults defined. This meta information includes the column position. In your case the column positions in the default meta information are 5 and 6 for the columns with DATE and TIME default although after dropping one or more columns those columns are now at position 4 and 5. Ok, so far so good. The Loader exports the

table in format pages and stores the default meta information in the data file to be able to compare the definitions of source and target table during import. And it gets the wrong default meta information during export. This leads to the problem because during

import the table is created without all this history information and the columns with

DATE and TIME defaults have now the position 4 and 5 in the respective default meta information in the target system. The Loader compares both table definitions and finds the difference and eventually stops importing.

I can't offer a simple solution right now. I have to check some possible solutions with the kernel guys here to have the Loader solve those problems in the future.

But there are some alternatives to overcome the problem:

1.

You could export and import the data using the Loader proprietary data format RECORDS instead of PAGES. This will last a bit longer as the Loader in this case simply mass SELECTs during EXPORT and mass INSERTs during IMPORT. The problems encountered with the format PAGES should not happen here.

This is surely not an option if one has really much data to be transported.

Because you already have the catalog transported it is sufficient to export and import the data only.

2.

You could export and import the data in format PAGES and exclude the table in question and export and import this table only in format RECORDS.

3.

You could copy the table in question in your source system with SQL into

another table, delete the old one and rename the new to the old name and then do the export and import.

This is surely not applicable if you have lots of tables where you dropped columns.

And actually, I know, should the Loader handle this kind of problems. It will asap but not immediately.

I hope this helps a bit and I didn't confuse you to much.

Best regards,

Steffen

[Former Member]

hello,

please check if the database parameter

USECOLUMNCOMPRESSION is equal in the source and target system.

if not, please alter the parametervalue of the targetsystem to parametervalue

of the sourcesystem and try the import again.

regards

[Former Member]

i cant find USECOLUMNCOMPRESSION, i have found USEUNICODECOLUMNCOMPRESSION and i have the two databases in "NO", anyway the databases should have the same parameters because i have created it with the installer templates.

[Former Member]

Hello,

for some reason the loader means that the target table does not fit the extracted data.

Could you please check the database parameters for different Unicode parameters?

Could you please check with the Database Studio or SQL Studio if the table definitions on the source and target database differ?

Could you try just to import the data as the catalog definition is already in the target database?

IMPORT DB DATA INSTREAM FILE 'oterosdb_pages.data' PAGES

Regards

Wolfgang

[Former Member]

i can find only the parameters "_unicode" and "useunicodecolumncompression" and they have the same value, i have checked table definitions and are exactly the same and i have tried to import before the catalog (deleting before the previously imported database) without errors and later import the pages with the same error than before import the data.

[Former Member]

can you please check if the kernelparameter COLUMNCOMPRESSION

is identical in the source and target system?

[former_member229109]

Hello,

-> What are the values of the database parameters DEFAULT_CODE, _UNICODE,

USEUNICODECOLUMNCOMPRESSION & COLUMNCOMPRESSION in the source

and target database instances?

If the source system has MaxDB Version 7.6 or higher, the

COLUMNCOMPRESSION parameter in the target system for the duration of

the import must be set to the same value as in the source system.

The COLUMNCOMPRESSION parameter can be changed ONLINE.

Check the <value> < YES or NO ? > of the COLUMNCOMPRESSION parameter in the source Database.

Please run on the target database server< the windows machine >:

You can carry out the change online:

dbmcli -d TEST -u <dbm-user>,<dbm-password>

dbmcli on TEST>param_put -running -permanent COLUMNCOMPRESSION <value>

dbmcli on TEST>param_checkall

dbmcli on TEST>exit

Check if your test will be working after that on the windows machine

&& update with the loader.log file.

-> Please update with output of the SQL statement:

select * from users

when you connected to your databases as DBA user.

< In your case, from the information above, you have the DBADMIN

user as DBA user. Correct? >

-> Please run 'loadercli -V' on the source server.

From the log file above I see that you have "Loader 7.6.03 Build 007-123-157-515"

on the target server.

Thank you and best regards, Natalia Khlopina

[Former Member]

Hello, the parameter was the same in the two databases, anyway i have setted it in the target database (to se same value) other time, after that i have tried and i have the same error so here you have the information you asked me.

source:

DBM DBM ADMIN MULTIPLE ? ? ? DEFAULT 2007-11-15 15:28:27 2007-11-15 15:28:27 2007-11-15 15:28:27 OTEROS skuda 0 NO ?

DBADMIN REPLER STANDARD MULTIPLE ? ? ? DEFAULT 2007-12-04 18:45:02 2007-12-04 18:45:02 2007-12-04 18:45:02 OTEROS skuda 6 YES ?

DBADMIN DBADMIN SYSDBA MULTIPLE ? ? ? DEFAULT 2007-11-15 15:28:27 2007-11-15 16:59:33 2007-11-15 16:26:45 OTEROS skuda 10 NO ?

DBADMIN SKUDA DBA MULTIPLE ? ? ? DEFAULT 2007-11-17 12:40:20 2007-11-17 12:40:20 2007-11-17 12:40:20 OTEROS skuda 175 NO ?

Server version information:

version, os, dbroot, swap

"7.6.03.07", "UNIX", "", "full"

target:

DBM DBM ADMIN MULTIPLE ? ? ? DEFAULT 2007-11-15 15:28:27 2007-11-15 15:28:27 2007-11-15 15:28:27 TEST desktop 0 NO ?

DBADMIN REPLER STANDARD MULTIPLE ? ? ? DEFAULT 2007-12-04 18:45:02 2007-12-04 18:45:02 2007-12-04 18:45:02 TEST desktop 6 YES ?

DBADMIN DBADMIN SYSDBA MULTIPLE ? ? ? DEFAULT 2007-11-15 15:28:27 2007-11-15 16:59:33 2007-11-15 16:26:45 TEST desktop 10 NO ?

DBADMIN SKUDA DBA MULTIPLE ? ? ? DEFAULT 2007-12-12 09:23:40 2007-12-12 09:23:40 2007-12-12 09:23:40 TEST desktop 194 NO ?

Server version information:

version, os, dbroot, swap

"7.6.03.07", "WIN32", "", "full"

loader.log:

// *

// M START 20071212 00092232

// *

USE USER "DBADMIN" * SCHEMA DBADMIN SERVERDB "TEST"

// *

// M USER: 'DBADMIN' on DB: 'TEST' ISOLATION LEVEL: 3

// *

// M Loader 7.6.03 Build 007-123-157-515

// *

// M Module: C:\Archivos de programa\sdb\programs\bin\loadercli.exe

// *

// M Loader instance started at DESKTOP for database TEST

// *

// M Starting protocol at 2007121200092325

// *

// M START 20071212 00092325

// *

SET

// *

// M START 20071212 00092325

// *

USE USER "DBADMIN" * SCHEMA DBADMIN SERVERDB "TEST"

// *

// M USER: 'DBADMIN' on DB: 'TEST' ISOLATION LEVEL: 3

// *

// M START 20071212 00092340

// *

IMPORT DB CATALOG INSTREAM FILE 'oterosdb_ddl.catalog' DDL DATA INSTREAM FILE 'oterosdb_pages.data' PAGES

// *

// M Execute PACKAGE to transform CATALOG

/ *

// M Import PACKAGE x'010000008C2B70FD9C0D0000E04F0000A0347F34D83C9090'

// *

// M Number of TABLES transformed : 169

// *

// M Execute PACKAGE to transform DATA

// *

// M Number of TABLES to transform: 0

// *

// E -25345: Definitions of source and target table CAJA_MOVIMIENTOS do not match; default definitions differ.

// E -25342: Error restoring table CAJA_MOVIMIENTOS; table exists but source table schema and target table schema are different

// M Loader 7.6.03 Build 007-123-157-515

// *

// M Module: C:\Archivos de programa\sdb\programs\bin\loadercli.exe

// *

// M Loader instance started at DESKTOP

// *

// M Starting protocol at 2007121200092621

// *

// M START 20071212 00092621

// *

[Former Member]

Here i paste two attempts, i see little information, do i have to activate any debug mode?

SET

// *

// M START 20071208 00112719

// *

USE USER "DBADMIN" * SCHEMA DBADMIN SERVERDB "TEST"

// *

// M USER: 'DBADMIN' on DB: 'TEST' ISOLATION LEVEL: 3

// *

// M START 20071208 00112720

// *

IMPORT DB CATALOG INSTREAM FILE 'oterosdb_ddl.catalog' DDL DATA INSTREAM FILE 'oterosdb_pages.data' PAGES

// *

// M Execute PACKAGE to transform CATALOG

/ *

// M Import PACKAGE x'0100000088026BFD28050000EE400000B8716045DBEA4835'

// *

// M Number of TABLES transformed : 169

// *

// M Execute PACKAGE to transform DATA

// *

// M Number of TABLES to transform: 0

// *

// E -25345: Definitions of source and target table CAJA_MOVIMIENTOS do not match; default definitions differ.

// E -25342: Error restoring table CAJA_MOVIMIENTOS; table exists but source table schema and target table schema are different

// M Loader 7.6.03 Build 007-123-157-515

// *

// M Module: C:\Archivos de programa\sdb\programs\bin\loadercli.exe

// *

// M Loader instance started at DESKTOP for database TEST

// *

// M Starting protocol at 2007120800161604

// *

// M START 20071208 00161604

// *

SET

// *

// M START 20071208 00161604

// *

USE USER "DBADMIN" * SCHEMA DBADMIN SERVERDB "TEST"

// *

// M USER: 'DBADMIN' on DB: 'TEST' ISOLATION LEVEL: 3

// *

// M START 20071208 00161615

// *

IMPORT DB CATALOG INSTREAM FILE 'oterosdb_ddl.catalog' DDL DATA INSTREAM FILE 'oterosdb_pages.data' PAGES

// *

// M Execute PACKAGE to transform CATALOG

/ *

// M Import PACKAGE x'010000003F466BFD240E000033690000970E05EAE2FD57EF'

// *

// M Number of TABLES transformed : 169

// *

// M Execute PACKAGE to transform DATA

// *

// M Number of TABLES to transform: 0

// *

// E -25345: Definitions of source and target table CAJA_MOVIMIENTOS do not match; default definitions differ.

// E -25342: Error restoring table CAJA_MOVIMIENTOS; table exists but source table schema and target table schema are different

[steffen_schildberg]

Hi Soraya,

would you mind to send me the catalog file or if this is impossible the catalog definition of the table that fails to load. This way I could try to reproduce it here in the Labs. To me it looks like a problem in the Loader directly. It is surely not a problem of the parameter settings in the target database.

Sorry for the inconveniences.

Best regards,

Steffen

[Former Member]

Hi steffen,

what it is your email? i dont want to post here the complete catalog and i cant find in the forum how to send a private message.

[steffen_schildberg]

Hi Soraya,

well, I found my business card is not up to date and I can't update it now. So here is my email: steffen.schildberg@sap.com.

Thx for your patience,

Steffen

[Former Member]

Hello,

can you provide the content of the sdb/loader/log/loader.log file.

There should be more information.

Regards

Wolfgang

[markus_doehr2]

There may be a difference between DBADMIN and dbadmin (capital vs. non-captial).

What happens if you enter them the same way?

--

Markus

[Former Member]

I have the same results.